Untitled
unknown
sql
a year ago
8.6 kB
9
Indexable
-- Q1 solution
SELECT employee_id,
first_name,
last_name,
hire_date
FROM employees
WHERE hire_date < (SELECT MIN(hire_date)
FROM employees
WHERE EXTRACT(MONTH FROM hire_date) = 6
AND EXTRACT(YEAR FROM hire_date) = 2016)
AND hire_date > (SELECT Add_months(MAX(hire_date), 2)
FROM employees
WHERE EXTRACT(MONTH FROM hire_date) = 2
AND EXTRACT(YEAR FROM hire_date) = 2016)
ORDER BY hire_date,
employee_id;
/*
EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE
----------- ---------- --------- ---------
97 Harriet Ferguson 6-04-24
23 Jackson Coleman 6-05-01
75 Imogen Boyd 6-05-11
16 Alex Sanders 6-05-18
5 Nathan Cox 6-05-21
98 Amber Rose 16-05-23
79 Esme Warren 16-05-24
*/
-- Q2 solution
SELECT DISTINCT c.country_id "Country ID",
c.country_name "Country Name"
FROM countries c
JOIN locations l1
ON c.country_id = l1.country_id
JOIN locations l2
ON c.country_id = l2.country_id
WHERE l1.location_id <> l2.location_id
ORDER BY c.country_id;
/*
Country ID Country Name
---------- ------------
CA Canada
CH Switzerland
IT Italy
JP Japan
UK United Kingdom
US United States of America
*/
-- Q3 solution
SELECT country_id "Country ID",
country_name "Country Name"
FROM (SELECT c.country_id,
c.country_name
FROM countries c
JOIN locations l1
ON c.country_id = l1.country_id
JOIN locations l2
ON c.country_id = l2.country_id
WHERE l1.location_id = l2.location_id
MINUS
SELECT DISTINCT c.country_id,
c.country_name
FROM countries c
JOIN locations l1
ON c.country_id = l1.country_id
JOIN locations l2
ON c.country_id = l2.country_id
WHERE l1.location_id <> l2.location_id)
ORDER BY country_id;
/*
Country ID Country Name
---------- ------------
AU Australia
BR Brazil
CN China
DE Germany
IN India
MX Mexico
NL Netherlands
SG Singapore
*/
-- Q4 solution
SELECT customer_id AS "Customer ID",
TO_CHAR(order_date, 'Month') AS "Month",
COUNT(*) AS "Number of orders"
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2017
GROUP BY customer_id,
TO_CHAR(order_date, 'Month')
HAVING COUNT(*) > 1
ORDER BY TO_CHAR(order_date, 'Month'),
customer_id;
/*
Customer ID Month Number of orders
----------- --------- ----------------
5 April 2
48 March 2
*/
-- Q5 solution
SELECT c1.customer_id,
c2.name
FROM (SELECT o.customer_id
FROM order_items oi
LEFT JOIN orders o
ON o.order_id = oi.order_id
WHERE oi.product_id IN ( 20, 95 )
GROUP BY product_id,
customer_id) c1
LEFT JOIN customers c2
ON c1.customer_id = c2.customer_id
GROUP BY c1.customer_id,
c2.name
HAVING COUNT(c1.customer_id) = 2
ORDER BY c1.customer_id;
/*
CUSTOMER_ID NAME
----------- ----------------
3 US Foods Holding
16 Aflac
*/
-- Q6 solution
SELECT salesman_id "Employee ID",
COUNT(salesman_id) "Number of Orders"
FROM orders
HAVING COUNT(salesman_id) > (SELECT AVG(cnt)
FROM (SELECT salesman_id,
COUNT(salesman_id) cnt
FROM orders
GROUP BY salesman_id))
GROUP BY salesman_id
ORDER BY salesman_id;
/*
Employee ID Number of Orders
----------- ----------------
55 10
62 13
64 12
*/
-- Q7 solution
SELECT
EXTRACT(MONTH FROM o.order_date) AS "Month Number",
TO_CHAR(o.order_date, 'Month') AS "Month",
COUNT(DISTINCT o.customer_id) AS "Total Number of customers",
SUM(oi.quantity * oi.unit_price) AS "Sales Amount"
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE EXTRACT(YEAR FROM o.order_date) = 2017
GROUP BY
EXTRACT(MONTH FROM o.order_date),
TO_CHAR(o.order_date, 'Month')
ORDER BY "Month Number";
/*
Month Number Month Total Number of customers Sales Amount
------------ --------- ------------------------- ------------
1 January 5 2281459.09
2 February 13 7919446.52
3 March 3 2246625.47
4 April 1 609150.35
5 May 4 1367115.47
6 June 1 926416.51
8 August 5 2539537.86
9 September 4 1675983.52
10 October 2 2040864.95
11 November 1 307842.27
*/
-- Q8 solution
SELECT
EXTRACT(MONTH FROM o.order_date) AS "Month Number",
TO_CHAR(o.order_date, 'Month') AS "Month",
ROUND(AVG(oi.quantity * oi.unit_price), 2) AS "Average Sales Amount"
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE EXTRACT(YEAR FROM o.order_date) = 2017
GROUP BY
EXTRACT(MONTH FROM o.order_date),
TO_CHAR(o.order_date, 'Month')
HAVING AVG(oi.quantity * oi.unit_price) > (
SELECT ROUND(AVG(oi.quantity * oi.unit_price), 2)
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE EXTRACT(YEAR FROM o.order_date) = 2017
)
ORDER BY "Month Number";
/*
Month Number Month Average Sales Amount
------------ --------- --------------------
2 February 93169.96
3 March 89865.02
6 June 132345.22
10 October 92766.59
11 November 153921.14
*/
-- Q9 solution
SELECT first_name "First Name"
FROM EMPLOYEES
WHERE first_name LIKE 'B%'
AND first_name NOT IN (
SELECT first_name
FROM CONTACTS
)
ORDER BY first_name;
/*
First Name
----------
Bella
Blake
*/
-- Q10 solution
SELECT
'The number of employees with total order amount over average order amount: '
|| Count(*) AS "result"
FROM (SELECT oi.order_id,
SUM(oi.quantity * oi.unit_price) "amount",
o.salesman_id
FROM order_items oi
LEFT JOIN orders o
ON oi.order_id = o.order_id
WHERE o.salesman_id IS NOT NULL
GROUP BY oi.order_id,
o.salesman_id
HAVING SUM(oi.quantity * oi.unit_price) > (SELECT AVG(
oi.quantity * oi.unit_price)
FROM order_items oi)
ORDER BY oi.order_id)
UNION ALL
SELECT 'The number of employees with total number of orders greater than 10: '
|| COUNT(*)
FROM (SELECT COUNT(salesman_id)
FROM orders
GROUP BY salesman_id
HAVING COUNT(salesman_id) > 10)
UNION ALL
SELECT 'The number of employees with no order: '
|| COUNT(*)
FROM (SELECT employee_id
FROM employees
MINUS
SELECT salesman_id
FROM orders)
UNION ALL
SELECT 'The number of employees with orders: '
|| COUNT(DISTINCT salesman_id)
FROM orders;
/*
The number of employees with total order amount over average order amount: 60
The number of employees with total number of orders greater than 10: 2
The number of employees with no order: 98
The number of employees with orders: 9
*/Editor is loading...
Leave a Comment