1
Exercise #15
Select the name of the products sold by all employees.
2
Exercise #16
Select the name of customers who bought all products purchased by the customer whose identifier is ‘LAZYK’.
3
Exercise #17
Select the name of customers who bought exactly the same products as the customer whose identifier is ‘LAZYK’.
Exercise #18
Select the average price of products by category.
4
Exercise #19
Given the name of the categories and the average price of products in each category.
Exercise #20
Select the identifier and the name of the companies that provide more than 3 products.
Exercise #21
Select the identifier, name, and number of orders of employees, ordered by the employee identifier.
5
Exercise #22
For each employee give the identifier, name, and the number of distinct products sold, ordered by the employee
identifier.
Exercise #23
Select the identifier, name, and total sales of employees, ordered by the employee identifier.
6
Exercise #24
Select the identifier, name, and total sales of employees, ordered by the employee identifier for employees who have
sold more than 70 different products.
Exercise #25
Select the names of employees who sell the products of more than 7 suppliers.
7
Exercise #26
Select the customer name and the product name such that the quantity of this product bought by the customer in a
single order is more than 5 times the average quantity of this product bought in a single order among all clients.
8
SECTION 1: SIMPLE SQL QUERIES
Q1: Get all columns from Customers, Orders, Suppliers
SELECT * FROM Customers;
SELECT * FROM Orders;
SELECT * FROM Suppliers;
Q2: Get all Customers alphabetically by Country and name
SELECT * FROM Customers
ORDER BY Country, ContactName;
Q3: Get all Orders by date
SELECT * FROM Orders
ORDER BY OrderDate;
Q4: Count of all Orders made during 1997
SELECT COUNT(*) AS TotalOrders1997
FROM Orders
WHERE YEAR(OrderDate) = 1997;
Q5: Contact persons who are managers (alphabetically)
SELECT ContactName
FROM Customers
WHERE ContactTitle LIKE '%Manager%'
ORDER BY ContactName;
Q6: Orders placed on 19th May 1997
SELECT *
9
FROM Orders
WHERE OrderDate = '1997-05-19';
SECTION 2: SQL QUERIES FOR JOINS
Q1: Orders of 1996 with Customers
SELECT Orders.*, Customers.*
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE YEAR(OrderDate) = 1996;
Q2: Employees and Customers count from each city that has employees
SELECT e.City,
COUNT(DISTINCT e.EmployeeID) AS EmployeeCount,
COUNT(DISTINCT c.CustomerID) AS CustomerCount
FROM Employees e
LEFT JOIN Customers c ON e.City = c.City
GROUP BY e.City;
SECTION 3: SQL QUERIES WITH GROUP BY & HAVING
Q1: Customers per country
SELECT Country, COUNT(*) AS CustomerCount
FROM Customers
GROUP BY Country;
Q2: Orders per country
SELECT c.Country, COUNT(o.OrderID) AS OrdersCount
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.Country;
Q3: Customers with more than 10 orders
SELECT c.CustomerID, COUNT(o.OrderID) AS OrdersCount
10
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerID
HAVING COUNT(o.OrderID) > 10;
SECTION 4: INSERTING RECORDS
Q1: Insert yourself into Employees
INSERT INTO Employees (LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, City, Region,
PostalCode, Country, HomePhone, ReportsTo)
VALUES ('Sharma', 'Aarushi', 'Software Developer', 'Ms.', '2000-01-01', '2025-05-04', 'Bhopal', 'MP', '462042', 'India',
'9876543210', 1);
Q2: Insert an Order for yourself
INSERT INTO Orders (CustomerID, EmployeeID, OrderDate, RequiredDate)
VALUES ('ALFKI', 10, '2025-05-04', '2025-05-10');
Q3: Insert into Order Details
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount)
VALUES (11078, 1, 18.00, 5, 0.0);
SECTION 5: UPDATING RECORDS
Q1: Update your phone number
UPDATE Employees
SET HomePhone = '9998887770'
WHERE FirstName = 'Aarushi' AND LastName = 'Sharma';
Q2: Double the quantity of the inserted order
UPDATE [Order Details]
SET Quantity = Quantity * 2
WHERE OrderID = 11078 AND ProductID = 1;
11
Q3: Double quantity for all your orders
UPDATE [Order Details]
SET Quantity = Quantity * 2
WHERE OrderID IN (
SELECT OrderID FROM Orders WHERE EmployeeID = 10
);
SECTION 6: DELETING RECORDS
Q1: Delete inserted Order Details
DELETE FROM [Order Details]
WHERE OrderID = 11078;
Q2: Delete inserted Order
DELETE FROM Orders
WHERE OrderID = 11078;
Q3: Delete inserted Employee
DELETE FROM Employees
WHERE FirstName = 'Aarushi' AND LastName = 'Sharma';
SECTION 7: ADVANCED SQL QUERIES
Q1: Total revenues in 1997 (should be 617085.27)
SELECT SUM(UnitPrice * Quantity * (1 - Discount)) AS TotalRevenue
FROM [Order Details] od
JOIN Orders o ON od.OrderID = o.OrderID
WHERE YEAR(o.OrderDate) = 1997;
Q2: Total amount each customer paid
SELECT c.CustomerID, c.CompanyName,
SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS TotalPaid
FROM Customers c
12
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN [Order Details] od ON o.OrderID = od.OrderID
GROUP BY c.CustomerID, c.CompanyName;
Q3: Top 10 selling products
SELECT TOP 10 p.ProductName,
SUM(od.Quantity) AS TotalSold
FROM Products p
JOIN [Order Details] od ON p.ProductID = od.ProductID
GROUP BY p.ProductName
ORDER BY TotalSold DESC;
Q4: View - total revenue per customer
CREATE VIEW CustomerTotalRevenues AS
SELECT c.CustomerID, c.CompanyName,
SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS TotalRevenue
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN [Order Details] od ON o.OrderID = od.OrderID
GROUP BY c.CustomerID, c.CompanyName;
Q5: UK customers who paid more than $1000
SELECT c.CustomerID, c.CompanyName,
SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) AS TotalPaid
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN [Order Details] od ON o.OrderID = od.OrderID
WHERE c.Country = 'UK'
GROUP BY c.CustomerID, c.CompanyName
HAVING SUM(od.UnitPrice * od.Quantity * (1 - od.Discount)) > 1000;
