DISTINCT Keyword

1. Find unique product lines:

SELECT DISTINCT productLine

FROM productlines;

2. Get a list of unique cities where offices are located:

SELECT DISTINCT city

FROM offices;

3. List unique job titles for employees:

SELECT DISTINCT jobTitle

FROM employees;

4. Find unique product codes for products that have been ordered:

SELECT DISTINCT productCode

FROM orderdetails;

5. Get a list of unique customer numbers who have placed orders:

SELECT DISTINCT customerNumber

FROM orders;

Groupby Keyword

1. Calculate the total quantity ordered for each product:

SELECT productCode,SUM(quantityOdered
FROM orderdetails
GROUP BY productCode;

SELECT productCode, SUM(quantityOrdered) AS TotalQuantityOrdered

FROM orderdetails
GROUP BY productCode;

2. Find the average credit limit for customers in each country:

SELECT country, AVG(creditLimit) AS AverageCreditLimit

FROM customers
GROUP BY country;

3. Count the number of orders for each status:

SELECT status, COUNT(*) AS OrderCount
FROM orders
GROUP BY status;

4. Calculate the total payments received for each customer:

SELECT customerNumber, SUM(amount) AS TotalPaymentsReceived

FROM payments
GROUP BY customerNumber;

5. Find the earliest and latest order date for each customer:

SELECT customerNumber, MIN(orderDate) AS EarliestOrderDate, MAX(orderDate) AS

LatestOrderDate
FROM orders
GROUP BY customerNumber;

1. Find the total number of customers:

SELECT COUNT(*) AS TotalCustomers

FROM customers;

2. Calculate the average credit limit of customers:

SELECT AVG(creditLimit) AS AverageCreditLimit

FROM customers;

3. Determine the highest credit limit among customers:

SELECT MAX(creditLimit) AS HighestCreditLimit

FROM customers;

4. Find the lowest quantity in stock for products:

SELECT MIN(quantityInStock) AS LowestQuantityInStock

FROM products;

5. Calculate the total amount of payments received:

SELECT SUM(amount) AS TotalPaymentsReceived

FROM payments;

6. Calculate the average order amount for all orders:

SELECT AVG(od.quantityOrdered * p.buyPrice) AS AverageOrderAmount

FROM orderdetails od
JOIN products p ON od.productCode = p.productCode;
7. Find the order with the highest order amount:

SELECT orderNumber, MAX(quantityOrdered * priceEach) AS HighestOrderAmount

FROM orderdetails;

8. Calculate the total quantity ordered for a specific product:

SELECT SUM(quantityOrdered) AS TotalQuantityOrdered

FROM orderdetails
WHERE productCode = 'S18_1749'; -- Replace with the desired product code

9. Which customers have placed orders, and what are their order numbers?

SELECT customers.customerName, orders.orderNumber

FROM customers, orders
WHERE customers.customerNumber = orders.customerNumber;

10. List customers who have made payments, along with the payment dates.

SELECT customers.customerName, payments.paymentDate

FROM customers, payments
WHERE customers.customerNumber = payments.customerNumber;

11. Order details of a particular customer ID

SELECT o.orderNumber, o.orderDate, o.requiredDate, o.shippedDate, o.status

FROM orders o
WHERE o.customerNumber = 114;

12. Order details of a particular customer Name

SELECT o.orderNumber, o.orderDate, o.requiredDate, o.shippedDate, o.status

FROM orders o
JOIN customers c ON o.customerNumber = c.customerNumber
WHERE c.contactLastName = 'King';

13. Question: What are the names and job titles of employees and their respective
managers?

SELECT e.firstName, e.lastName, e.jobTitle, m.firstName AS ManagerFirstName, m.lastName

AS ManagerLastName
FROM employees e, employees m
WHERE e.reportsTo = m.employeeNumber;

14. Question: List customers who have placed orders, along with their order numbers and
order dates.
SELECT c.customerName, o.orderNumber, o.orderDate
FROM customers c, orders o
WHERE c.customerNumber = o.customerNumber;

15. Question: Which products have been ordered, and what is the total quantity ordered
for each product?

SELECT p.productCode, p.productName, SUM(od.quantityOrdered) AS TotalQuantityOrdered

FROM products p, orderdetails od
WHERE p.productCode = od.productCode
GROUP BY p.productCode, p.productName;

16. Question: Show the names of customers and their sales representatives (if available).

SELECT c.customerName, c.salesRepEmployeeNumber, s.firstName AS SalesRepFirstName,

s.lastName AS SalesRepLastName
FROM customers c, employees s
WHERE c.salesRepEmployeeNumber = s.employeeNumber;

17. What are the names of products in the 'Classic Cars' product line, and what is their
product scale?

SELECT p.productName, p.productScale

FROM products p, productlines pl
WHERE p.productLine = pl.productLine AND pl.productLine = 'Classic Cars';

18. What are the names and contact information of customers who have placed orders?

SELECT c.customerName, c.contactFirstName, c.contactLastName, c.phone

FROM customers c
JOIN orders o ON c.customerNumber = o.customerNumber;

19. Which employees report to a specific manager, and what are their job titles?

Query:
SELECT e.firstName, e.lastName, e.jobTitle
FROM employees e
WHERE e.reportsTo = 1143; -- Replace with the manager's employeeNumber

20. Question: What are the names of products ordered by customers in a specific city?

SELECT p.productName
FROM products p
JOIN orderdetails od ON p.productCode = od.productCode
JOIN orders o ON od.orderNumber = o.orderNumber
JOIN customers c ON o.customerNumber = c.customerNumber
WHERE c.city = 'New York'; -- Replace with the desired city

21. Question: What are the names of customers who have made payments, and what is
the total amount paid by each customer?

SELECT c.customerName, SUM(p.amount) AS TotalAmountPaid

FROM customers c
JOIN payments p ON c.customerNumber = p.customerNumber
GROUP BY c.customerName;

22. Question: Which products have not been ordered yet?

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

23. Question: Which employees report to each manager, and what are their names?

SELECT m.firstName AS ManagerFirstName, m.lastName AS ManagerLastName, e.firstName

AS EmployeeFirstName, e.lastName AS EmployeeLastName
FROM employees e JOIN employees m ON e.reportsTo = m.employeeNumber;

24. Question: What are the names of customers who have made payments, and what is
the total amount each customer has paid?

SELECT c.customerName, SUM(p.amount) AS TotalAmountPaid

FROM customers c
JOIN payments p ON c.customerNumber = p.customerNumber
GROUP BY c.customerName;

25. Question: Which products were ordered by customers and how many of each product
were ordered?

SELECT o.customerNumber, o.orderNumber, od.productCode, p.productName,

SUM(od.quantityOrdered) AS TotalQuantityOrdered
FROM orders o
JOIN orderdetails od ON o.orderNumber = od.orderNumber
JOIN products p ON od.productCode = p.productCode
GROUP BY o.customerNumber, o.orderNumber, od.productCode, p.productName;
26. Question: What are the names and contact information of customers who have a sales
representative, and who are their respective sales representatives?

SELECT c.customerName, c.contactFirstName, c.contactLastName, c.phone AS

CustomerPhone, s.firstName AS SalesRepFirstName, s.lastName AS SalesRepLastName
FROM customers c
LEFT JOIN employees s ON c.salesRepEmployeeNumber = s.employeeNumber;

27. Question: What are the names of products in the 'Vintage Cars' product line, and what
is their stock quantity?

SELECT p.productCode, p.productName, p.quantityInStock

FROM products p
JOIN productlines pl ON p.productLine = pl.productLine
WHERE pl.productLine = 'Vintage Cars';