1) Кто из сотрудников родился позже, чем Simon Rapier (вывести фамилию, имя, должность, дату рождения)
SELECT FirstName, LastName, MiddleName, HumanResourses.Employee.Title, BirthDate
FROM
HumanResourses.Employee
JOIN
Pesron.Contact
ON
HumanResourses.Employee.ContactID=Person.Contact.ContactID
WHERE
HumanResourses.Employee.BirthDate >
(SELECT HumanResourses.Employee.BirthDate
FROM
HumanResourses.Employee
JOIN
Person.Contact
ON
HumanResourses.Employee.ContactID=Person.Contact.ContactID
WHERE
LastName=’Rapier’
AND FirstName=’Simon’)
8 строк, можно только подзапросами или подзапрос вместе с объединением, только объединением нельзя.
2) Найти пары сотрудников, проживающих в одном городе (вывести идентификационные номера сотрудников и название города, отсортировать по идентификационным номерам сотрудников)
SELECT A.EmployeeID, B.EmployeeID, C.City
FROM
(HumanResourses.Employee A
JOIN
HumanResourses.Employee EA1
ON
A.EmployeeID=EA1.EmployeeID
JOIN
Person.Address C
ON
EA1.AddressID=C.AddressID)
JOIN
(HumanResourses.Employee B
JOIN
HumanResourses.EmployeeAddress EA2
ON
B.EmployeeID=EA2.EmployeeID
JOIN
Person.Address D
ON
EA2.AddressID=D.AddressID)
ON
C.City=D.City
WHERE A.EmployeeID > B.EmployeeID
ORDER BY A.EmployeeID, B.EmployeeID
2994 строки, нельзя только подзапросом (можно некоторые объединения заменить подзапросами)
3) Найти сотрудников, имеющих хотя бы одного сверстника
СПОСОБ 1:
SELECT A.EmployeeID, A.BirthDate
FROM HumanResourses.Employee A
WHERE Exists
(SELECT (B.BirthDate)
FROM HumanResourses.Employee B
WHERE A.EmployeeID>B.EmployeeID
AND A.BirthDate=B.BirthDate)
СПОСОБ 2:
SELECT A.EmployeeID, A.BirthDate
FROM HumanResourses.Employee A
JOIN HumanResourses.Employee B
ON A.BirthDate=B.BirthDate
WHERE A.EmployeeID>B.EmployeeID
11 строк
4) Для заказов на общую сумму больше 200 000 получить список идентификационных номеров заказов, их стоимость, а так же фамилию, имя, отчество клиента и сотрудника, оформившего заказ
SELECT SalesOrderID, TotalDue, SP.FirstName, SP.MiddleName, SP.LastName, C.FirstName, C.MiddleName, C.LastName
FROM
Sales.SalesOrderHeader
JOIN
Person.Contact C
ON
C.ContactID=Sales.SalesOrderHeader.ContactID
JOIN
HumanResourses.Employee
ON Sales.SalesOrderHeader.SalesPersonID=HumanResourses.Employee.EmployeeID
JOIN
Person.Contact SP
ON
SP.ContactID= HumanResourses.Employee.ContactID
WHERE TotalDue > 200000
4 строки, только объединение
5) Каков диапазон планов объемов продаж для каждой территории региона US
СПОСОБ 1:
SELECT MIN(SalesQuota), MAX(Sales.Quota), Sales.SalesPerson.TerritoryID
FROM
Sales.SalesPerson
JOIN
Sales.SalesTerritory
ON
Sales.SalesPerson.TerritoryID=Sales.SalesTerritory.TerritoryID
WHERE CountryRegionCode=’US’
GROUP BY Sales.SalesPerson.TerritoryID
СПОСОБ 2:
SELECT MIN(SalesQuota), MAX(Sales.Quota), Sales.SalesPerson.TerritoryID
FROM
Sales.SalesPerson
WHERE Sales.SalesPerson.TerritoryID IN
(SELECT Sales.SalesTerritory.TerritoryID
FROM Sales.SalesTerritory
WHERE CountryRegionCode=’US’
GROUP BY Sales.SalesPerson.TerritoryID
5 строк
6) Вывести список названий товаров, цена которых превышает 10 000 или которых было заказано на сумму больше 25 000 в один заказ
СПОСОБ 1:
SELECT P.Name, P.ProductID
FROM Production.Product P
JOIN
Sales.SalesOrderDetale O
ON
P.ProductID=O.ProductID
WHERE UnitPrice > 10000 OR LineTotal > 25000
СПОСОБ 2:
SELECT P.Name, P.ProductID
FROM Production.Product P
WHERE P.ProductID IN
(SELECT O.ProductID
FROM Sales.SalesOrderDetail O
WHERE UnitPrice > 10000
UNION
SELECT O.ProductID
FROM
Sales.SalesOrderDetail O
WHERE LineTotal > 25000)
3 строки
7) Вывести список сотрудников, принявших заказ на сумму, составляющую 50% и более от их плана продаж
СПОСОБ 1:
SELECT DISTINCT C.ContactID, C.FirstName, C.MiddleName, C.LastName
FROM Person.Contact C
WHERE C.ContactID IN
(SELECT E.ContactID
FROM HumanResourses.Employee E
WHERE E.EmployeeID IN
(SELECT O.SalesPersonID
FROM Sales.SalesOrderHeader O
WHERE O.TotalDye >=0.5*
(SELECT SP.SalesPerson SP
WHERE SP.SalesPersonID=O.SalesPersonID)))
СПОСОБ 2:
SELECT DISTINCT C.ContactID, C.FirstName, C.MiddleName, C.LastName
FROM Person.Contact C
JOIN
HumanResourses.Employee E
ON
E.ContactID=C.ContactID
JOIN
Sales.SalesOrderHeader O
ON
O.SalesPersonID=E.EmployeeID
WHERE O.TotalDue >= 0.5*
(Select SP.SalesQuota
FROM
Sales.SalesPerson SP
WHERE SP.SalesPersonID=O.SalesPersonID)
11 строк, подзапросы или подзапросы+объединение, только объединением нельзя
8) Вывести список руководителей, родившихся после 01 января 1940 года, у которых есть служащие, опережающие личный план продаж
СПОСОБ 1:
SELECT DISTINCT B.EmployeeID
FROM
HumanResourses.Employee B
JOIN
HumanResourses.Employee E
ON
E.ManagerID=B.EmployeeID
JOIN
Sales.SalesPerson SP
ON
E.EmployeeID=SP.SalesPErsonID
WHERE E.BirthDate > ’01.01.1940’
AND SP.SalesYTD >=SP.SalesQuota
СПОСОБ 2:
SELECT DISTINCT B.EmployeeID
FROM
HumanResourses.Employee B
WHERE B.EmployeeID IN
(SELECT E.ManagerID
FROM HumanResourses.Employee E
WHERE E.EmployeeID IN
(SELECT SP.SalesPersonID
FROM Sales.SalesPerson SP
WHERE E.BirthDate > ’01.01.1940’
AND SP.SalesYTD >= SP.SalesQuota))
3 строки
9) Вывести список имеющихся товаров от компании Cruger Bike Company
СПОСОБ 1:
SELECT P.Product, P.Name
FROM Production.Product P
WHERE P.ProductID IN
(SELECT PV.ProductID
FROM Purchasing.ProductVendor PV
WHERE PV.VendorID IN
(SELECT V.VendorID
FROM Purchasing.Vendor V
WHERE V.Name = ‘Cruger Bike Company’
СПОСОБ 2:
SELECT P.Product, P.Name
FROM Production.Product P
JOIN
Purchasing.ProductVendor PV
ON
P.ProductID=PV.ProductID
JOIN
Purchasing.Vendor V
ON
PV.VendorID=V.VendorID
WHERE V.Name = ‘Cruger Bike Company’
23 строки
10) Определить, есть ли территория, суммарный план продаж сотрудников которой превышает 0,5% стоимости всех заказов
SELECT ST.TerritoryID
FROM Sales.SalesTerritory ST
WHERE Exists
(SELECT SUM(SalesQuota)
FROM Sales.SalesPerson SP
GROUP BY SP.TerritoryID
HAVING SUM(SalesQuota) > 0.005* (
SELECT SUM(TotalDue)
FROM Sales.SalesOrderHeader
)
AND ST.TerritoryID=SP.TerritoryID
)
1 строка, возможно только подзапросом
Уважаемый посетитель!
Чтобы распечатать файл, скачайте его (в формате Word).
Ссылка на скачивание - внизу страницы.