SQL- Queries-Practise

SQL Interview Questions and Answers based on Sales ER Model

download sql dump

Basic SQL Questions (1–15)

1. Retrieve all customer names and their phone numbers.

SELECT customerName, phone FROM customers;

2. List all products with their product line.

SELECT productName, productLine FROM products;

3. Find all employees working in the "San Francisco" office.

SELECT e.firstName, e.lastName
FROM employees e
JOIN offices o ON e.officeCode = o.officeCode
WHERE o.city = 'San Francisco';

4. Show all orders placed by customer ‘Atelier graphique’.

SELECT o.orderNumber, o.orderDate, o.status
FROM orders o
JOIN customers c ON o.customerNumber = c.customerNumber
WHERE c.customerName = 'Atelier graphique';

5. Retrieve the details of payments made by customer number 103.

SELECT * FROM payments WHERE customerNumber = 103;

6. List all product names where quantity in stock < 500.

SELECT productName FROM products WHERE quantityInStock < 500;

7. Show all orders with status = "Shipped".

SELECT * FROM orders WHERE status = 'Shipped';

8. Display employees’ first and last names along with their office city.

SELECT e.firstName, e.lastName, o.city
FROM employees e
JOIN offices o ON e.officeCode = o.officeCode;

9. Find the customer name and sales representative (employee) who manages them.

SELECT c.customerName, e.firstName, e.lastName
FROM customers c
JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber;

10. Show all products along with their MSRP, sorted in descending order.

SELECT productName, MSRP
FROM products
ORDER BY MSRP DESC;

11. List the customers who are located in the USA.

SELECT customerName FROM customers WHERE country = 'USA';

12. Display all distinct product scales.

SELECT DISTINCT productScale FROM products;

13. Find orders placed between 2024-01-01 and 2024-03-31.

SELECT * FROM orders 
WHERE orderDate BETWEEN '2024-01-01' AND '2024-03-31';

14. List customers with credit limit above 100,000.

SELECT customerName, creditLimit 
FROM customers
WHERE creditLimit > 100000;

15. Retrieve employee names and their managers’ names (self-join).

SELECT e.firstName AS Employee, m.firstName AS Manager
FROM employees e
LEFT JOIN employees m ON e.reportsTo = m.employeeNumber;

Intermediate SQL Questions (16–35)

16. Find the total number of customers in each country.

SELECT country, COUNT(*) AS total_customers
FROM customers
GROUP BY country;

17. Show the top 5 customers by credit limit.

SELECT customerName, creditLimit
FROM customers
ORDER BY creditLimit DESC
LIMIT 5;

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

SELECT c.customerName, COUNT(o.orderNumber) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY c.customerName;

19. Retrieve all products that have never been ordered.

SELECT p.productName
FROM products p
LEFT JOIN orderdetails od ON p.productCode = od.productCode
WHERE od.productCode IS NULL;

20. List customers who have not made any payments.

SELECT c.customerName
FROM customers c
LEFT JOIN payments p ON c.customerNumber = p.customerNumber
WHERE p.customerNumber IS NULL;

21. Find the average payment amount made by each customer.

SELECT c.customerName, AVG(p.amount) AS avg_payment
FROM customers c
JOIN payments p ON c.customerNumber = p.customerNumber
GROUP BY c.customerName;

22. Show the total revenue (sum of amount) received from each customer.

SELECT c.customerName, SUM(p.amount) AS total_revenue
FROM customers c
JOIN payments p ON c.customerNumber = p.customerNumber
GROUP BY c.customerName;

23. Find all orders that include the product "1992 Ferrari 360 Spider red".

SELECT DISTINCT o.orderNumber, o.orderDate
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
JOIN products p ON od.productCode = p.productCode
WHERE p.productName = '1992 Ferrari 360 Spider red';

24. Display employees who do not manage any customers.

SELECT e.firstName, e.lastName
FROM employees e
LEFT JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber
WHERE c.customerNumber IS NULL;

25. Find the employee who reports directly to "Diane Murphy".

SELECT e.firstName, e.lastName
FROM employees e
JOIN employees m ON e.reportsTo = m.employeeNumber
WHERE m.firstName = 'Diane' AND m.lastName = 'Murphy';

26. List all products with their corresponding order quantities.

SELECT p.productName, SUM(od.quantityOrdered) AS total_ordered
FROM products p
JOIN orderdetails od ON p.productCode = od.productCode
GROUP BY p.productName;

27. Find orders where the total order value exceeds 100,000.

SELECT o.orderNumber, SUM(od.quantityOrdered * od.priceEach) AS order_value
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY o.orderNumber
HAVING order_value > 100000;

28. Retrieve the product line with the maximum number of products.

SELECT productLine, COUNT(*) AS total_products
FROM products
GROUP BY productLine
ORDER BY total_products DESC
LIMIT 1;

29. Show customers who placed orders but have never made a payment.

SELECT DISTINCT c.customerName
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
LEFT JOIN payments p ON c.customerNumber = p.customerNumber
WHERE p.customerNumber IS NULL;

30. Find all employees who do not report to anyone.

SELECT firstName, lastName
FROM employees
WHERE reportsTo IS NULL;

31. List offices with more than 5 employees.

SELECT o.city, COUNT(e.employeeNumber) AS total_employees
FROM offices o
JOIN employees e ON o.officeCode = e.officeCode
GROUP BY o.city
HAVING COUNT(e.employeeNumber) > 5;

32. Find customers who have placed more than 10 orders.

SELECT c.customerName, COUNT(o.orderNumber) AS order_count
FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber
GROUP BY c.customerName
HAVING order_count > 10;

33. Show the highest and lowest payment made by each customer.

SELECT c.customerName,
       MAX(p.amount) AS max_payment,
       MIN(p.amount) AS min_payment
FROM customers c
JOIN payments p ON c.customerNumber = p.customerNumber
GROUP BY c.customerName;

34. List all orders and calculate the total number of items in each order.

SELECT o.orderNumber, SUM(od.quantityOrdered) AS total_items
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY o.orderNumber;

35. Find all customers whose sales rep works in the "Boston" office.

SELECT c.customerName
FROM customers c
JOIN employees e ON c.salesRepEmployeeNumber = e.employeeNumber
JOIN offices o ON e.officeCode = o.officeCode
WHERE o.city = 'Boston';

Advanced SQL Questions (36–50)

36. Find the top 5 customers by total payment amount.

SELECT c.customerName, SUM(p.amount) AS totalPayments
FROM customers c
JOIN payments p ON c.customerNumber = p.customerNumber
GROUP BY c.customerName
ORDER BY totalPayments DESC
LIMIT 5;

37. List employees who do not manage anyone (no subordinates).

SELECT e.firstName, e.lastName
FROM employees e
LEFT JOIN employees m ON e.employeeNumber = m.reportsTo
WHERE m.employeeNumber IS NULL;

38. Find customers who have never placed an order.

SELECT c.customerName
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber
WHERE o.orderNumber IS NULL;

39. Show the product with the highest stock quantity.

SELECT productName, quantityInStock
FROM products
ORDER BY quantityInStock DESC
LIMIT 1;

40. Display customers whose total payments exceed their credit limit.

SELECT c.customerName, c.creditLimit, SUM(p.amount) AS totalPayments
FROM customers c
JOIN payments p ON c.customerNumber = p.customerNumber
GROUP BY c.customerName, c.creditLimit
HAVING SUM(p.amount) > c.creditLimit;

41. Retrieve the order number, customer, and total number of items for each order.

SELECT o.orderNumber, c.customerName, SUM(od.quantityOrdered) AS totalItems
FROM orders o
JOIN customers c ON o.customerNumber = c.customerNumber
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY o.orderNumber, c.customerName;

42. Find the average credit limit of customers grouped by country.

SELECT country, AVG(creditLimit) AS avgCreditLimit
FROM customers
GROUP BY country;

43. Display products that have never been ordered.

SELECT p.productName
FROM products p
LEFT JOIN orderdetails od ON p.productCode = od.productCode
WHERE od.productCode IS NULL;

44. Show employees along with the number of customers they manage.

SELECT e.firstName, e.lastName, COUNT(c.customerNumber) AS customersManaged
FROM employees e
LEFT JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber
GROUP BY e.firstName, e.lastName;

45. Find the order with the maximum total value.

SELECT o.orderNumber, SUM(od.quantityOrdered * od.priceEach) AS orderValue
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
GROUP BY o.orderNumber
ORDER BY orderValue DESC
LIMIT 1;

46. List employees who share the same job title.

SELECT jobTitle, GROUP_CONCAT(firstName, ' ', lastName) AS employees
FROM employees
GROUP BY jobTitle
HAVING COUNT(*) > 1;

47. Display the most frequently ordered product.

SELECT p.productName, SUM(od.quantityOrdered) AS totalOrdered
FROM products p
JOIN orderdetails od ON p.productCode = od.productCode
GROUP BY p.productName
ORDER BY totalOrdered DESC
LIMIT 1;

48. Find the average time taken to ship orders (orderDate to shippedDate).

SELECT AVG(DATEDIFF(shippedDate, orderDate)) AS avgShippingDays
FROM orders
WHERE shippedDate IS NOT NULL;

49. Show offices where no employees are assigned.

SELECT o.city, o.country
FROM offices o
LEFT JOIN employees e ON o.officeCode = e.officeCode
WHERE e.employeeNumber IS NULL;

50. Create a view showing customer, their total orders, and total payments.

CREATE VIEW customer_summary AS
SELECT c.customerName,
       COUNT(DISTINCT o.orderNumber) AS totalOrders,
       SUM(p.amount) AS totalPayments
FROM customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber
LEFT JOIN payments p ON c.customerNumber = p.customerNumber
GROUP BY c.customerName;