• Question 1: Write down a query to retrieve all records from a table named “Customers”.
• Answer: SELECT * FROM Customers;
• Question 2: Write down a query to retrieve the names of all customers from the “Customers” table.
• Answer: SELECT CustomerName FROM Customers;
• Question 3: Write down a query to retrieve the count of orders for each customer from the “Orders” table.
• Answer: SELECT CustomerID, COUNT(*) AS OrderCount FROM Orders GROUP BY CustomerID;
• Question 4: Write down a query to retrieve the highest price from the “Products” table.
• Answer: SELECT MAX(Price) AS HighestPrice FROM Products;
• Question 5: Write down a query to retrieve the average salary of employees from the “Employees” table
• Answer: SELECT AVG(Salary) AS AverageSalary FROM Employees;
• Question 6: Write down a query to retrieve the details of employees who earn more than $50000 from the “Employees” table.
• Answer: SELECT * FROM Employees WHERE Salary > 50000;
• Question 7: Write down a query to retrieve the names of customers who have placed orders from the “Customers” and “Orders” tables.
• Answer: SELECT Customers.CustomerName FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;
• Question 8: Write down a query to retrieve the total revenue generated by each product from the “OrderDetails” table.
• Answer: SELECT ProductID, SUM(Quantity * UnitPrice) AS TotalRevenue FROM OrderDetails GROUP BY ProductID;
• Question 9: Write down a query to retrieve the top 5 highest-earning employees from the “Employees” table.
• Answer: SELECT EmployeeName, Salary FROM Employees ORDER BY Salary DESC LIMIT 5;
• Question 10: Write down a query to retrieve the number of customers who have placed orders in each country from the “Customers” table.
• Answer: SELECT Country, COUNT(*) AS CustomerCount FROM Customers WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders) GROUP BY Country;
• Question 11: Write down a query to retrieve the order details for a specific order ID from the “Orders” and “OrderDetails” tables.
• Answer: SELECT Orders.OrderID, ProductName, Quantity, UnitPrice FROM Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID WHERE Orders.OrderID = 12345;
• Question 12: Write down a query to retrieve the names of employees who do not manage any other employees from the “Employees” table.
• Answer: SELECT EmployeeName FROM Employees WHERE EmployeeID NOT IN (SELECT ManagerID FROM Employees);
• Question 13: Write down a query to retrieve the customers who have made orders in the last 30 days along with the total amount they’ve spent. Order the results by total amount in descending order.
• Answer:
SELECT
Customers.CustomerName,
SUM(OrderDetails.Quantity * OrderDetails.UnitPrice) AS TotalAmount
FROM
Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE
Orders.OrderDate >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY
Customers.CustomerID
ORDER BY
TotalAmount DESC;
• Question 14: Write down a query to retrieve the names of employees along with the names of their managers. Include only employees who have managers.
• Answer:
SELECT
E1.EmployeeName AS Employee,
E2.EmployeeName AS Manager
FROM
Employees E1
INNER JOIN Employees E2 ON E1.ManagerID = E2.EmployeeID;
• Question 15: Write down a query to retrieve the most recent order for each customer along with the customer’s name.
• Answer:
SELECT
C.CustomerName,
O.OrderID,
O.OrderDate
FROM
Customers C
INNER JOIN (
SELECT
CustomerID,
MAX(OrderDate) AS LatestOrderDate
FROM
Orders
GROUP BY
CustomerID
) Subquery ON C.CustomerID = Subquery.CustomerID
INNER JOIN Orders O ON C.CustomerID = O.CustomerID AND Subquery.LatestOrderDate = O.OrderDate;
• Question 16: Write down a query to retrieve the top 3 products with the highest total revenue across all orders.
• Answer:
SELECT
P.ProductName,
SUM(OD.Quantity * OD.UnitPrice) AS TotalRevenue
FROM
Products P
INNER JOIN OrderDetails OD ON P.ProductID = OD.ProductID
GROUP BY
P.ProductID
ORDER BY
TotalRevenue DESC
LIMIT 3;
Question 17: Write down a query to retrieve the names of employees who earn more than the average salary of their respective departments.
• Answer:
SELECT
E.EmployeeName,
E.Salary,
D.DepartmentName,
D.AvgSalary
FROM
Employees E
INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID
WHERE
E.Salary > D.AvgSalary;
• Question 18: Write down a query to retrieve the products that have been ordered by all customers.
• Answer:
SELECT
P.ProductID,
P.ProductName
FROM
Products P
WHERE
NOT EXISTS (
SELECT
C.CustomerID
FROM
Customers C
WHERE
NOT EXISTS (
SELECT
OD.ProductID
FROM
OrderDetails OD
WHERE
OD.ProductID = P.ProductID AND OD.CustomerID = C.CustomerID
)
);
• Question 19: Write down a query to retrieve the countries where the total revenue from orders is greater than the average total revenue.
• Answer:
SELECT
C.Country,
SUM(OD.Quantity * OD.UnitPrice) AS TotalRevenue
FROM
Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails OD ON O.OrderID = OD.OrderID
GROUP BY
C.Country
HAVING
TotalRevenue > (
SELECT AVG(TotalRevenue)
FROM (
SELECT
C.Country,
SUM(OD.Quantity * OD.UnitPrice) AS TotalRevenue
FROM
Customers C
INNER JOIN Orders O ON C.CustomerID = O.CustomerID
INNER JOIN OrderDetails OD ON O.OrderID = OD.OrderID
GROUP BY
C.Country
) AvgSubquery
);
• Question 20: Write down a query to retrieve the customers who have placed orders for all available products.
• Answer:
SELECT
C.CustomerName
FROM
Customers C
WHERE
NOT EXISTS (
SELECT
P.ProductID
FROM
Products P
WHERE
NOT EXISTS (
SELECT
OD.ProductID
FROM
OrderDetails OD
INNER JOIN Orders O ON OD.OrderID = O.OrderID
WHERE
OD.ProductID = P.ProductID AND O.CustomerID = C.CustomerID
)
);
Leave a Reply