DB_Lab5

1) Получить упорядоченный по возрастанию список идентификаторов поставщиков, поставляющих товары для горной линейки велосипедов

    SELECT DISTINCT VendorID
    FROM Purchasing.ProductVendor
    WHERE ProductID IN
        (SELECT PP.ProductID
        FROM Production.Product AS PP
        WHERE PP.ProductLine = 'M')

2) Получить упорядоченный по возрастанию список названий товаров, поставляемых поставщиком с ID

    SELECT Name
    FROM Production.Product
    WHERE ProductID IN
        (SELECT PV.ProductID
        FROM Purchasing.ProductVendor AS PV
        WHERE PV.VendorID IS NOT NULL)


3) Для каждого типа кредитного рейтинга посчитать количество поставщиков, которые находятся в городе ‘New York’

    SELECT CreditRating, COUNT(VendorID)
    FROM Purchasing.Vendor
    WHERE VendorID IN
        (SELECT VendorID
        FROM Purchasing.VendorAddress
        WHERE AddressID IN
            (SELECT AddressID
            FROM Person.Address
            WHERE City = 'New York'))
    GROUP BY CreditRating

4) Получить идентификационные номера товаров и рекламных акций для заказов, сделанных 27 июля 2004 года

    SELECT DISTINCT ProductID, SpecialOfferID
    FROM Sales.SalesOrderDetail
    WHERE SalesOrderID  IN
        (SELECT SOH.SalesOrderID
        FROM Sales.SalesOrderHeader AS SOH
        WHERE SOH.OrderDate = '27 Jul 2004')

5) Получить список идентификационных номеров сотрудников, чье количество дней отпуска по болезни меньше, чем среднее количество дней отпуска по болезни по занимаемой ими должности

    SELECT E1.EmployeeID
    FROM HumanResources.Employee AS E1
    WHERE E1.SickLeaveHours <
        (SELECT AVG(E2.SickLeaveHours)
        FROM HumanResources.Employee AS E2
        WHERE E2.Title = E1.Title)

6) Вычислить количество продуктов, для которых количества единиц товара в заказе больше среднего количества единиц товара в заказе для рекламной акции с ID = 1

    SELECT COUNT(SOD1.ProductID)
    FROM Sales.SalesOrderDetail AS SOD1
    WHERE SOD1.OrderQty >
        (SELECT AVG(SOD2.OrderQty)
        FROM Sales.SalesOrderDetail AS SOD2
        WHERE SpecialOfferID = 1)

7) Получить список городов, в которых есть хотя бы один поставщик с кредитным рейтингом 5

    SELECT DISTINCT A.City
    FROM Person.Address AS A
    WHERE A.AddressID IN
        (SELECT VA.AddressID
        FROM Purchasing.VendorAddress AS VA
        WHERE VA.VendorID IN
            (SELECT V.VendorID
            FROM Purchasing.Vendor AS V
            WHERE V.CreditRating = 5))

8) Получить список городов, в которых есть только один поставщик

    SELECT DISTINCT A.City
    FROM Person.Address AS A
    WHERE A.AddressID IN
        (SELECT VA.AddressID
        FROM Purchasing.VendorAddress AS VA
        WHERE VA.VendorID NOT IN
            (SELECT VA2.VendorID
            FROM Purchasing.VendorAddress AS VA2
            WHERE VA2.VendorID < VA.VendorID))

9) Получить данные (идентификационный номер) о поставщиках, поставляющих больше одного продукта для дорожной линейки велосипедов

    SELECT DISTINCT PV.VendorID
    FROM Purchasing.ProductVendor AS PV
    WHERE PV.ProductID IN
        (SELECT P.ProductID
        FROM Production.Product AS P
        WHERE P.ProductLine = 'R' AND
            PV.ProductID NOT IN
            (SELECT P2.ProductID
            FROM Production.Product AS P2
            WHERE P2.ProductID < P.ProductID AND P2.ProductLine = 'R'))

10) Получить список идентификационных номеров покупателей, для которых город в адресе выставления счета не соответствует городу в адресе доставки товара (без использования IN)

    SELECT SC.CustomerID
    FROM Sales.Customer AS SC
    WHERE SC.CustomerID = ANY
        (SELECT SOH.CustomerID
        FROM Sales.SalesOrderHeader AS SOH
        WHERE
            (SELECT PA1.City
            FROM Person.Address AS PA1
            WHERE SOH.BillToAddressID = PA1.AddressID)
            <>
            (SELECT PA2.City
            FROM Person.Address AS PA2
            WHERE SOH.ShipToAddressID = PA2.AddressID))

11) Получить данные (идентификационный номер) о заказчиках, для которых средняя стоимость заказа выше стоимости заказа от 27.07.2004

    SELECT SC.CustomerID
    FROM Sales.Customer AS SC
    WHERE CustomerID IN
        (SELECT SOH.CustomerID
        FROM Sales.SalesOrderHeader AS SOH
        GROUP BY SOH.CustomerID
        HAVING AVG(SOH.SubTotal) > ANY
            (SELECT SOH2.SubTotal
            FROM Sales.SalesOrderHeader AS SOH2
            WHERE SOH2.OrderDate = '27 Jul 2004' AND SOH2.CustomerID = SOH.CustomerID))

12) Найти тех производителей (идентификационный номер) товаров горной линейки, которые не производят товары дорожной линейки

    SELECT DISTINCT PV.VendorID
    FROM Purchasing.ProductVendor AS PV
    WHERE EXISTS
        (SELECT *
        FROM Production.Product AS P
        WHERE P.ProductLine = 'M' AND PV.ProductID = P.ProductID)
    AND NOT EXISTS
        (SELECT *
        FROM Production.Product AS P
        WHERE P.ProductLine = 'R' AND PV.ProductID = P.ProductID)


13) Получить информацию о количестве заказов на суммы больше 150000 для каждого заказчика (идентификационный номер)

    SELECT SOH.CustomerID, COUNT(
        CASE WHEN SOH.SubTotal > 150000
        THEN 1
        ELSE NULL
        END)
    FROM Sales.SalesOrderHeader AS SOH
    GROUP BY CustomerID