Database Systems- Lab 10

Sadiqah Mushtaq - 07152

October 25, 2024

SQL Queries
1. Find the employee who processed the first order placed in year 1998.
SELECT TOP 1
Orders . EmployeeID
FROM
Orders
WHERE
Year ( Orders . OrderDate ) = ’1998 ’
ORDER BY
Orders . OrderDate ASC

2. Select all employees who work directly under the top manager of the com-
pany.
SELECT Employees . EmployeeID
FROM Employees
WHERE Employees . ReportsTo = (
SELECT
E . EmployeeID
FROM
Employees E

1
 WHERE
E . ReportsTo IS NULL
)

3. Select all employees who are assigned to territories in ‘Western’ and ‘East-
ern’ regions from Region Table.
SELECT
DISTINCT E m p lo y e eT e r ri t o ri e s . EmployeeID
FROM
E mp l o ye e T er r i to r i e s
WHERE E m pl o y e eT e r ri t o ri e s . TerritoryID IN (
SELECT TerritoryID
FROM Territories
WHERE Territories . RegionID IN (
SELECT Region . RegionID
FROM Region
WHERE Regio nDescr iption = ’ Western ’ OR
Regi onDes cripti on = ’ Eastern ’
)
)

2
4. Select all Customers and Suppliers belonging to ‘Germany’.
SELECT
Customers . ContactName
FROM
Customers
WHERE
Customers . Country = ’ Germany ’
UNION
SELECT
Suppliers . ContactName
FROM
Suppliers
WHERE
Suppliers . Country = ’ Germany ’

5. Find the 3rd most expensive product in the database.

SELECT Products . ProductName
FROM Products
WHERE Products . UnitPrice = (
SELECT TOP 1 P . UnitPrice
FROM Products P
WHERE P . UnitPrice < (
SELECT
MIN ( P1 . UnitPrice )
FROM
Products P1
WHERE P1 . UnitPrice IN (
SELECT
DISTINCT TOP 2 P2 . UnitPrice
FROM
Products P2

3
 ORDER BY
P2 . UnitPrice DESC
)
)
ORDER BY
P . UnitPrice DESC
)

6. Select all employees and their Seniority level.

SELECT
Employees . EmployeeID ,
CASE
WHEN DATEDIFF ( YEAR , Employees . HireDate ,
GETDATE () ) > 5 THEN 3
WHEN DATEDIFF ( YEAR , Employees . HireDate ,
GETDATE () ) > 3 AND DATEDIFF ( YEAR ,
Employees . HireDate , GETDATE () ) < 5 THEN
2
WHEN DATEDIFF ( YEAR , Employees . HireDate ,
GETDATE () ) < 3 THEN 1
END AS SeniorityLevel
FROM
Employees

4
7. List all products and their types.
SELECT
Products . ProductName ,
CASE
WHEN Products . UnitPrice > 80 THEN ’ Costly ’
WHEN Products . UnitPrice > 30 AND Products .
UnitPrice < 80 THEN ’ Economical ’
WHEN Products . UnitPrice < 30 THEN ’ Cheap ’
END AS [ Types ]
FROM
Products

8. List all products and their trends based on the number of orders placed
in the year 1997.
SELECT ProductName ,
CASE
WHEN ( SELECT COUNT ( OrderID )

5
 FROM Orders
WHERE YEAR ( OrderDate ) = 1997
AND OrderID IN ( SELECT OrderID
FROM [ Order Details ]
WHERE ProductID =
Products .
ProductID )
) >= 50 THEN ’ Customer favourite ’
WHEN ( SELECT COUNT ( OrderID )
FROM Orders
WHERE YEAR ( OrderDate ) = 1997
AND OrderID IN ( SELECT OrderID
FROM [ Order Details ]
WHERE ProductID =
Products .
ProductID )
) BETWEEN 30 AND 49 THEN ’ Trending ’
WHEN ( SELECT COUNT ( OrderID )
FROM Orders
WHERE YEAR ( OrderDate ) = 1997
AND OrderID IN ( SELECT OrderID
FROM [ Order Details ]
WHERE ProductID =
Products .
ProductID )
) BETWEEN 10 AND 29 THEN ’ On the Rise
’
ELSE ’ Not Popular ’
END AS Trend
FROM Products

9. Find the total number of orders placed by each customer.

6
 SELECT
Customers . CustomerID ,
( SELECT COUNT ( Orders . OrderID )
FROM Orders
GROUP BY Orders . CustomerID
HAVING Orders . CustomerID = Customers .
CustomerID )
FROM Customers

10. Retrieve customers who have placed orders for products with a price higher
than the average price of all products.
SELECT
DISTINCT Orders . CustomerID
FROM
Orders
WHERE Orders . OrderID IN (
SELECT
[ Order Details ]. OrderID
FROM
[ Order Details ]
WHERE
[ Order Details ]. UnitPrice > (
SELECT
AVG ([ Order Details ]. UnitPrice )
FROM
[ Order Details ]
)
)

7
11. Find the customers who have placed orders for products from the same
category as ’Chai’.
SELECT
Customers . ContactName
FROM
Customers
WHERE
Customers . CustomerID IN (
SELECT Orders . CustomerID
FROM Orders
WHERE Orders . OrderID IN (
SELECT [ Order Details ]. OrderID
FROM [ Order Details ]
WHERE [ Order Details ]. ProductID IN (
SELECT Products . ProductID
FROM Products
WHERE Products . CategoryID = (
SELECT Products . CategoryID
FROM Products
WHERE ProductName = ’ Chai ’
)
)
)
)

8
12. Find the customer who has placed the highest total number of orders.
SELECT ContactName ,
( SELECT COUNT ( OrderId )
FROM Orders
WHERE Orders . CustomerID = Customers . CustomerID
) AS [ No of Products ]
FROM Customers
WHERE CustomerID = (
SELECT TOP 1 CustomerID
FROM Orders
GROUP BY CustomerID
ORDER BY COUNT ( OrderID ) DESC
)

13. List all the customers who have placed an order for the most expensive
product.
SELECT Customers . ContactName
FROM Customers

9
 WHERE Customers . CustomerID IN (
SELECT Orders . CustomerID
FROM Orders
WHERE Orders . OrderID IN (
SELECT [ Order Details ]. OrderID
FROM [ Order Details ]
WHERE [ Order Details ]. UnitPrice IN (
SELECT MAX ([ Order Details ]. UnitPrice )
FROM [ Order Details ]
)
)
)

14. Find the average number of products in each order.

SELECT AVG ([ No of Products ]) AS
AverageProductsPerOrder
FROM (
SELECT COUNT ( DISTINCT [ Order Details ].
ProductID ) AS [ No of Products ]
FROM [ Order Details ]
GROUP BY [ Order Details ]. OrderID
) AS Or de rP ro du ct Co un ts

10
15. Find the categories where the average product price is higher than the
overall average product price.
SELECT Categories . CategoryName
FROM Categories
WHERE Categories . CategoryID IN (
SELECT P1 . CategoryID
FROM Products P1
GROUP BY P1 . CategoryID
HAVING AVG ( P1 . UnitPrice ) > (
SELECT AVG ( P2 . UnitPrice )
FROM Products P2
)
)

16. Find the product which has the second highest price.
SELECT Products . ProductName , Products . UnitPrice
FROM Products
WHERE Products . UnitPrice = (

11
 SELECT DISTINCT TOP 1 P . UnitPrice
FROM Products P
WHERE P . UnitPrice < (
SELECT MAX ( UnitPrice ) FROM Products P2
)
ORDER BY P . UnitPrice DESC
)

17. Find the average order amount for customers from France.
SELECT AVG ( OrderAmount ) AS A ve ra ge Or de rA mo un t
FROM (
SELECT SUM ([ Order Details ]. UnitPrice * [ Order
Details ]. Quantity ) AS OrderAmount
FROM [ Order Details ]
WHERE [ Order Details ]. OrderID IN (
SELECT Orders . OrderID
FROM Orders
WHERE Orders . CustomerID IN (
SELECT Customers . CustomerID
FROM Customers
WHERE Customers . Country = ’ France ’
)
)
GROUP BY [ Order Details ]. OrderID
) AS NewTable

12
13