Untitled

mail@pastecode.io avatar
unknown
sql
2 months ago
8.6 kB
5
Indexable
Never
-- 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
*/
Leave a Comment