solution_queries_Pious.md

Solutions: Pious

My solution for the advanced queries.

SQL questionnaire

Setup

Import this fake database content into your database.

Below you will find a list of questions.

Find out the answer to each question using the dummy data in your database.

Copy this file (see: copy raw content) and fill in your queries + answer on the given location in each question.

START !

1) How many customers do we have?

SELECT COUNT(*) 
FROM customers;

Solution: 122

2) What is the customer number of Mary Young?

SELECT customerNumber 
FROM customers 
WHERE contactFirstName = 'Mary' AND contactLastName = 'Young';

Solution: 219

3) What is the customer number of the person living at Magazinweg 7, Frankfurt 60528?

SELECT customerNumber 
FROM customers 
WHERE addressLine1 = 'Magazinweg 7';

Solution: 247

4) If you sort the employees on their last name, what is the email of the first employee?

SELECT email 
FROM employees 
ORDER BY lastName ASC 
LIMIT 1;

Solution: gbondur@classicmodelcars.com

5) If you sort the employees on their last name, what is the email of the last employee?

SELECT email 
FROM employees 
ORDER BY lastName DESC 
LIMIT 1;

Solution: gvanauf@classicmodelcars.com

6) What is first the product code of all the products from the line 'Trucks and Buses', sorted first by productscale, then by productname.

SELECT productCode 
FROM products 
WHERE productLine = 'Trucks and Buses' 
ORDER BY productscale, productName ASC 
LIMIT 1;

Solution: S12_4473

7) What is the email of the first employee, sorted on their last name that starts with a 't'?

SELECT email 
FROM employees 
WHERE lastName LIKE 't%' 
ORDER BY lastName ASC 
LIMIT 1;

Solution: lthompson@classicmodelcars.com

8) Which customer (give customer number) payed by check on 2004-01-19?

SELECT customerNumber 
FROM payments 
WHERE paymentDate = '2004-01-19';

Solution: 177

9) How many customers do we have living in the state Nevada or New York?

SELECT COUNT(*) 
FROM customers 
WHERE state = 'NY' OR state = 'NV';

Solution: 7

10) How many customers do we have living in the state Nevada or New York or outside the united states?

SELECT COUNT(*) 
FROM customers 
WHERE state = 'NY' OR state = 'NV' OR country != 'USA';

Solution: 93

11) How many customers do we have with the following conditions (only 1 query needed): - Living in the state Nevada or New York OR - Living outside the USA or the customers and with a credit limit above 1000 dollar?

SELECT COUNT(*) 
FROM customers 
WHERE (state = 'NY' OR state = 'NV') 
OR (country != 'USA' AND creditLimit > 1000);

Solution: 70

12) How many customers don't have an assigned sales representative?

SELECT COUNT(*) 
FROM customers 
WHERE salesRepEmployeeNumber IS NULL;

Solution: 22

13) How many orders have a comment?

SELECT COUNT(*) 
FROM orders 
WHERE comments IS NOT NULL;

Solution: 80

14) How many orders do we have where the comment mentions the word "caution"?

SELECT COUNT(*) 
FROM orders 
WHERE comments LIKE '%caution%';

Solution: 4

15) What is the average credit limit of our customers from the USA? (rounded)

SELECT ROUND(AVG(creditLimit), 0) 
FROM customers 
WHERE country = 'USA';

Note: Here the round function is used to round the result to the AVG function to 0 decimal places. The AVG is used to calculate the average value of the "creditLimit" column.

Solution: 78103

16) What is the most common last name from our customers?

SELECT contactLastName, COUNT(*) AS count 
FROM customers 
GROUP BY contactLastName 
ORDER BY count DESC 
LIMIT 1;

Solution: Young 4

17) What are valid statuses of the orders?

• Resolved

• Cancelled

• Broken

• On Hold

• Disputed

• In Process

• Processing

• Shipped

Note: This will return the unique status in the order tables.

SELECT DISTINCT status 
FROM orders;

solution: Shipped, Resolved, Cancelled, On Hold, Disputed, In Process

Note: This will return a list of the statuses that are not present in the order table (from the list)

SELECT status
FROM (
    SELECT 'resolved' as status
    UNION ALL SELECT 'cancelled'
    UNION ALL SELECT 'broken'
    UNION ALL SELECT 'on hold'
    UNION ALL SELECT 'disputed'
    UNION ALL SELECT 'in process'
    UNION ALL SELECT 'processing'
    UNION ALL SELECT 'shipped'
) as statuses
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE statuses.status = orders.status);

18) In which countries don't we have any customers?

• Austria

• Canada

• China

• Germany

• Greece

• Japan

• Philippines

• South Korea

SELECT country
FROM (
SELECT 'Austria' AS country
UNION ALL SELECT 'Canada'
UNION ALL SELECT 'China'
UNION ALL SELECT 'Germany'
UNION ALL SELECT 'Greece'
UNION ALL SELECT 'Japan'
UNION ALL SELECT 'Philippines'
UNION ALL SELECT 'South Korea'
) AS countries
WHERE NOT EXISTS (SELECT 1 FROM customers WHERE countries.country = customers.country)
ORDER BY country;

Solution: China, Greece and South Korea

19) How many orders where never shipped?

SELECT COUNT(*) 
FROM orders 
WHERE status = 'In Process';

Solution: 6

20) How many customers does Steve Patterson have with a credit limit above 100 000 EUR?

SELECT COUNT(*) AS amount
FROM customers
INNER JOIN employees ON employeeNumber = salesRepEmployeeNumber
WHERE creditLimit > 100000 AND firstName = 'Steve' AND lastName = 'Patterson';

solution: 3

21) How many orders have been shipped to our customers?

SELECT COUNT(*) 
FROM orders 
WHERE status = 'Shipped';

Solution: 303

22) How much products does the biggest product line have? And which product line is that?

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

Solution: 38 - Classic Cars

23) How many products are low in stock? (below 100 pieces)

SELECT COUNT(*) 
FROM products 
WHERE quantityInStock < 100;

Solution: 2

24) How many products have more the 100 pieces in stock, but are below 500 pieces.

SELECT COUNT(*) 
FROM products 
WHERE quantityInStock > 100 AND quantityInStock < 500;

Solution: 3

25) How many orders did we ship between and including June 2004 & September 2004

SELECT COUNT(*) 
FROM orders 
WHERE status = 'Shipped' AND shippedDate BETWEEN '2004-06-01' AND '2004-09-30';

Solution: 42

26) How many customers share the same last name as an employee of ours?

SELECT COUNT(*) 
FROM customers 
WHERE contactLastName IN (SELECT lastName FROM employees);

Solution: 9

27) Give the product code for the most expensive product for the consumer?

SELECT productCode 
FROM products 
WHERE buyPrice = (SELECT MAX(buyPrice) FROM products);

Solution: S10_4962

28) What product (product code) offers us the largest profit margin (difference between buyPrice & MSRP).

SELECT productCode 
FROM products 
WHERE (msrp - buyPrice) = (SELECT MAX(msrp - buyPrice) FROM products);

Solution: S10_1949

29) How much profit (rounded) can the product with the largest profit margin (difference between buyPrice & MSRP) bring us.

SELECT ROUND(MSRP - buyPrice, 0) AS profit
FROM products
ORDER BY profit DESC
LIMIT 1;

Solution: 116

30) Given the product number of the products (separated with spaces) that have never been sold?

SELECT productCode 
FROM products 
WHERE productCode NOT IN (SELECT productCode FROM orderdetails);

Solution: S18_3233

31) How many products give us a profit margin below 30 dollar?

SELECT COUNT(*) 
FROM products 
WHERE (msrp - buyPrice) < 30;

Solution: 23

32) What is the product code of our most popular product (in number purchased)?

SELECT productCode 
FROM orderdetails GROUP BY productCode 
ORDER BY SUM(quantityOrdered) DESC 
LIMIT 1;

Solution: S18_3232

33) How many of our popular product did we effectively ship?

SELECT productCode, SUM(quantityOrdered) AS total_quantity
FROM orderdetails
JOIN orders ON orderdetails.orderNumber = orders.orderNumber
WHERE orders.status = 'shipped'
GROUP BY productCode
ORDER BY total_quantity DESC
LIMIT 1;

Solution: productCode: S18_3232 and total_quantity(as sum of quantityOrdered) 1720

34) Which check number paid for order 10210. Tip: Pay close attention to the date fields on both tables to solve this.

SELECT checkNumber
FROM payments
INNER JOIN orders ON payments.customerNumber = orders.customerNumber
WHERE orders.orderNumber = 10210 AND payments.paymentDate =
    (SELECT MIN(paymentDate) FROM payments
    WHERE payments.customerNumber = orders.customerNumber
    AND payments.paymentDate >= orders.orderDate);

Solution: CI381435

35) Which order was paid by check CP804873?

SELECT orderNumber
FROM orders JOIN payments ON orders.customerNumber = payments.customerNumber
WHERE checkNumber = 'CP804873'
AND MONTH(paymentDate) = MONTH(orderDate)
AND YEAR(paymentDate) = YEAR(orderDate);

Solution: 10330

36) How many payments do we have above 5000 EUR and with a check number with a 'D' somewhere in the check number, ending the check number with the digit 9?

SELECT COUNT(*) 
FROM payments 
WHERE amount > 5000 AND checkNumber LIKE '%D%' AND checkNumber LIKE '%9';

Solution: 3

38) In which country do we have the most customers that we do not have an office in?

SELECT country 
FROM customers 
WHERE country NOT IN (SELECT country FROM offices) GROUP BY country ORDER BY COUNT(*) DESC 
LIMIT 1;

Solution: Germany

39) What city has our biggest office in terms of employees?

SELECT city, MAX(employees) as maxEmployees 
FROM offices JOIN (SELECT officeCode, COUNT(*) AS employees 
FROM employees GROUP BY officeCode) AS employees ON offices.officeCode = employees.officeCode;

Solution: San Francisco

40) How many employees does our largest office have, including leadership?

SELECT COUNT(*) 
FROM employees 
WHERE officeCode = (SELECT employees.officeCode FROM offices 
JOIN (SELECT officeCode, COUNT(*) AS employees FROM employees 
GROUP BY officeCode) AS employees ON offices.officeCode = employees.officeCode 
ORDER BY employees DESC LIMIT 1);

Solution: 6

41) How many employees do we have on average per country (rounded)?

SELECT ROUND(COUNT(employeeNumber) / COUNT(DISTINCT country)) AS average_employees_per_country
FROM classicmodels.employees e
INNER JOIN classicmodels.offices o ON e.officeCode = o.officeCode;

Solution: 5

Note: "e" and "o" are aliases for the tables "classicmodels.employees" and "classicmodels.offices", respectively.

42) What is the total value of all shipped & resolved sales ever combined?

SELECT SUM(od.quantityOrdered * od.priceEach) AS total_value
FROM classicmodels.orderdetails od
JOIN classicmodels.orders o ON od.orderNumber = o.orderNumber
WHERE o.shippedDate IS NOT NULL AND o.status = 'Resolved';

Solution: 134235.88

Note: The "od" and "o" are aliases for the table names "orderdetails" and "orders", respectively.

Given the question "What is the total value of all shipped & resolved sales ever combined?" I take into account both shipped and resolved orders, and calculates the total value of these orders by multiplying the quantity ordered by the priceEach.

43) What is the total value of all shipped & resolved sales in the year 2005 combined? (based on shipping date)

SELECT ROUND(SUM(priceEach * quantityOrdered), 0) AS price
FROM orderdetails
INNER JOIN orders ON orderdetails.orderNumber = orders.orderNumber
WHERE YEAR(shippedDate) = '2005' AND (orders.status = 'Shipped' OR orders.status = 'Resolved');

Solution: 1427945

44) What was our most profitable year ever (based on shipping date), considering all shipped & resolved orders?

SELECT YEAR(shippedDate) AS year, SUM(priceEach * quantityOrdered) AS profit
FROM orderdetails
INNER JOIN orders ON orderdetails.orderNumber = orders.orderNumber
WHERE orders.status = 'Shipped' OR orders.status = 'Resolved'
GROUP BY YEAR(shippedDate)
ORDER BY profit DESC
LIMIT 1;

Solution: 2004 (Profit: 4321167.85)

45) How much revenue did we make on in our most profitable year ever (based on shipping date), considering all shipped & resolved orders?

SELECT ROUND(SUM(priceEach * quantityOrdered), 0) AS revenue
FROM orderdetails
INNER JOIN orders ON orderdetails.orderNumber = orders.orderNumber
WHERE YEAR(shippedDate) = (
  SELECT YEAR(shippedDate)
  FROM orderdetails
  INNER JOIN orders ON orderdetails.orderNumber = orders.orderNumber
  WHERE orders.status = 'Shipped' OR orders.status = 'Resolved'
  GROUP BY YEAR(shippedDate)
  ORDER BY SUM(priceEach * quantityOrdered) DESC
  LIMIT 1
) AND (orders.status = 'Shipped' OR orders.status = 'Resolved');

Solution: 4321168

46) What is the name of our biggest customer in the USA of terms of revenue?

SELECT customerName, ROUND(SUM(priceEach * quantityOrdered), 0) AS revenue
FROM customers
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
INNER JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber
WHERE customers.country = 'USA' AND (orders.status = 'Shipped' OR orders.status = 'Resolved')
GROUP BY customers.customerName
ORDER BY revenue DESC
LIMIT 1;

Solution: Mini Gifts Distributors Ltd. (Revenue: 584188)

47) How much has our largest customer inside the USA ordered with us (total value)?

SELECT ROUND(SUM(priceEach * quantityOrdered), 0) AS total_value
FROM classicmodels.customers c
JOIN classicmodels.orders o ON c.customerNumber = o.customerNumber
JOIN classicmodels.orderdetails od ON o.orderNumber = od.orderNumber
WHERE c.country = 'USA'
GROUP BY customerName
ORDER BY total_value DESC
LIMIT 1;

Solution: 591827

48) How many customers do we have that never ordered anything?

SELECT COUNT(*)
FROM customers
WHERE customerNumber NOT IN (SELECT DISTINCT customerNumber FROM orders);

Solution: 24

49) What is the last name of our best employee in terms of revenue?

SELECT lastName, SUM(priceEach * quantityOrdered) AS revenue
FROM employees
INNER JOIN customers ON employees.employeeNumber = customers.salesRepEmployeeNumber
INNER JOIN orders ON customers.customerNumber = orders.customerNumber
INNER JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber
WHERE (orders.status = 'Shipped' OR orders.status = 'Resolved')
GROUP BY employees.employeeNumber
ORDER BY revenue DESC
LIMIT 1;

solution: Hernandez (Revenue: 1112004)

50) What is the office name of the least profitable office in the year 2004?

SELECT o.city AS office_name, ROUND(SUM(od.priceEach * od.quantityOrdered), 0) AS total_profit
FROM offices o
JOIN employees e ON o.officeCode = e.officeCode
JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber
JOIN orders ord ON c.customerNumber = ord.customerNumber
JOIN orderdetails od ON ord.orderNumber = od.orderNumber
WHERE YEAR(ord.orderDate) = 2004
GROUP BY o.city
ORDER BY total_profit ASC
LIMIT 1;

Solution: Tokyo (Revenue: 151761)

Note: There is no office name in any of the tables, so I assume that this is a typing error. However, there is an office code, which, when compared to Basile's solution, was 5, which is Tokyo (consult the offices table)