Task - Subqueries - classicmodels DB

[akash-coded]

Database = classicmodels

Dump file: classicmodels.sql

DB Schema

Queries

1. Write a query to find customers whose payments are greater than the average payment using a subquery.
   

2. See the following customers and orders tables:
   
   Use a subquery with NOT IN operator to find the customers who have not placed any orders.
   

3. Write a subquery that finds the maximum, minimum, and average number of items in sale orders from orderdetails.
   

4. Unlike a standalone subquery, a correlated subquery is a subquery that uses the data from the outer query. In other words, a correlated subquery depends on the outer query. A correlated subquery is evaluated once for each row in the outer query.
   See the products table:
   
   Use a correlated subquery to select products whose buy prices are greater than the average buy price of all products in each product line.
   
   For each row in the products (or p1) table, the correlated subquery needs to execute once to get the average buy price of all products in the productline of that row.
   If the buy price of the current row is greater than the average buy price returned by the correlated subquery, the query includes the row in the result set.

5. Let’s take a look at the orders and orderdetails tables:
   
   Write a query that finds sales orders whose total values are greater than 60K as follows:
   

6. When a subquery is used with the EXISTS or NOT EXISTS operator, a subquery returns a Boolean value of TRUE or FALSE. The following query illustrates a subquery used with the EXISTS operator:

   SELECT 
       *
   FROM
       table_name
   WHERE
       EXISTS( subquery );

   In the query above, if the subquery returns any rows, EXISTS subquery returns TRUE, otherwise, it returns FALSE.
   Use the query in question no. 5 as a correlated subquery to find customers who placed at least one sales order with the total value greater than 60K by using the EXISTS operator:
   

7. Let's take a look at the orders and orderdetails table:
   
   Write a query that gets the top five products by sales revenue in 2003 from the orders and orderdetails tables as follows:
   

8. You can use the result of the previous query as a derived table called top5product2003 and join it with the products table using the productCode column.. Then, find out the productName and sales of the top 5 products in 2003.
   

9. Suppose you have to label the customers who bought products in 2003 into 3 groups: platinum, gold, and silver with the following conditions:

   • Platinum customers who have orders with the volume greater than 100K.
   • Gold customers who have orders with the volume between 10K and 100K.
   • Silver customers who have orders with the volume less than 10K.
     To form this query, you first need to put each customer into the respective group using CASE expression and GROUP BY to display the following:
     

10. Use the previous query as the derived table to know the number of customers in each group: platinum, gold, and silver.
    

[aish955]

1. select customerNumber,checkNumber,amount from payments where amount>(select avg(amount) from payments);
2. select customerName from customers where customerNumber not in(select customerNumber from orders);
3. select max(items),min(items),avg(items) from(select count(ordernumber)as items from orderdetails group by ordernumber) as derivedtable;
4. select productname,buyprice from products where buyprice>(select avg(buyprice) from products);
5. select ordernumber,sum(priceeach * quantityordered) total from orderdetails inner join orders using(ordernumber) group by ordernumber having sum(priceeach*quantityordered)>60000;
6. select customernumber, customername from customers where EXISTS(select ordernumber,sum(priceeach * quantityordered) from orderdetails inner join orders using(ordernumber) where customernumber=customers.customernumber group by ordernumber having sum(priceeach*quantityordered)>60000); 
7. select productcode,sum(quantityordered*priceeach)as sales from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by productcode order by sales desc limit 5;
8. select productname,sales from (select productcode,sum(quantityordered*priceeach)as sales from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by productcode order by sales desc limit 5) top5product2003 join products using(productcode);
9. select customernumber,sum(quantityordered*priceeach)as sales,(case when (sum(quantityordered*priceeach))>100000 then 'Platinum' when (sum(quantityordered*priceeach)) between 10000 and 100000 then 'Gold' when (sum(quantityordered*priceeach))<10000 then 'silver' end) as customerGroup from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by customernumber order by customernumber;
10. select customergroup,count(customergroup)as groupcount from (select customernumber,sum(quantityordered*priceeach)as sales,(case when (sum(quantityordered*priceeach))>100000 then 'Platinum' when (sum(quantityordered*priceeach)) between 10000 and 100000 then 'Gold' when (sum(quantityordered*priceeach))<10000 then 'silver' end) as customerGroup from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by customernumber order by customernumber)as derivedtable1 group by customergroup;

[Harshita-Joshi]

--1
SELECT `customerNumber`,`checkNumber`, `amount` FROM payments
WHERE `amount` > (SELECT AVG(`amount`) FROM payments);

--2
SELECT customerName FROM customers
WHERE customerNumber NOT IN (SELECT customerNumber FROM orders);

--3
SELECT MAX(items) , MIN(items) , FLOOR(AVG(items))
FROM  (SELECT COUNT(orderNumber) AS items FROM orderdetails GROUP BY orderNumber) AS derivedTable;  


--4
SELECT p.productName, p.buyPrice FROM products p
WHERE p.buyPrice > ( SELECT AVG(buyPrice) FROM products GROUP BY p.productLine);


--5
SELECT o.orderNumber, SUM(od.priceEach * od.quantityOrdered) AS total
FROM orders o, orderdetails od
WHERE o.orderNumber = od.orderNumber
GROUP BY od.orderNumber HAVING SUM(priceEach * quantityOrdered) > 60000;


--6
SELECT c.customerNumber, c.customerName 
FROM customers c
WHERE EXISTS (SELECT o.orderNumber, SUM(od.priceEach * od.quantityOrdered) AS total
FROM orders o, orderdetails od
WHERE o.orderNumber = od.orderNumber AND customerNumber = c.customerNumber
GROUP BY od.orderNumber HAVING SUM(priceEach * quantityOrdered) > 60000);

--7
SELECT productCode, FLOOR(SUM(quantityOrdered*priceEach)) AS sales
FROM orderdetails, orders
WHERE orderdetails.orderNumber = orders.orderNumber AND ( YEAR(orders.shippedDate) = 2003 OR YEAR(orders.requiredDate) = 2003)
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;

--8
SELECT productName, sales
FROM products, (SELECT productCode, FLOOR(SUM(quantityOrdered*priceEach)) AS sales
FROM orderdetails, orders
WHERE orderdetails.orderNumber = orders.orderNumber AND ( YEAR(orders.shippedDate) = 2003 OR YEAR(orders.requiredDate) = 2003)
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5) AS top5product2003
WHERE products.productCode = top5product2003.productCode
GROUP BY top5product2003.productCode;


--9
SELECT customerNumber, FLOOR(SUM(quantityOrdered*priceEach)) AS sales, 
(CASE 
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) > 100000 THEN 'Platinum'
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) < 10000 THEN 'Silver'
 END) AS customerGroup
FROM orderdetails, orders
WHERE orderdetails.orderNumber = orders.orderNumber AND ( YEAR(orders.shippedDate) = 2003 OR YEAR(orders.requiredDate) = 2003)
GROUP BY customerNumber
ORDER BY customerNumber;

--10
SELECT customerGroup, COUNT(customerGroup) AS groupCount 
FROM  (SELECT customerNumber, FLOOR(SUM(quantityOrdered*priceEach)) AS sales, 
(CASE 
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) > 100000 THEN 'Platinum'
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) < 10000 THEN 'Silver'
 END) AS customerGroup
FROM orderdetails, orders
WHERE orderdetails.orderNumber = orders.orderNumber AND ( YEAR(orders.shippedDate) = 2003 OR YEAR(orders.requiredDate) = 2003)
GROUP BY customerNumber
ORDER BY customerNumber) AS derivedTable
GROUP BY customerGroup;

[Harshita-Joshi]

Updated

[Sarfinaa]

1.)
select
  customernumber,
  checknumber,
  amount
from
  payments
where
  amount >(
    select
      avg(amount)
    from
      payments
  );

2.)
select
  customername
from
  customers
where
  customernumber not in (
    select
      customernumber
    from
      orders
  );
3.)
select
  max(items),
  min(items),
  floor(avg(items))
from
  (
    select
      count(ordernumber) items
    from
      orderdetails
    group by
      ordernumber
  ) as derived;
4.)
select
  productname,
  buyprice
from
  products
where
  buyprice >(
    select
      avg(buyprice)
    from
      products
  );
5.)
select
  ordernumber,
  sum(priceeach * quantityordered) total
from
  orderdetails
group by
  ordernumber
having
  total > 60000;
6.)
select
  customernumber,
  customername
from
  customers c
where
  EXISTS(
    select
      customernumber,
      sum(priceeach * quantityordered) as total
    from
      orderdetails
      inner join orders using(ordernumber)
    where
      customernumber = c.customernumber
    group by
      ordernumber
    having
      total > 60000
  );
7.)
select
  productCode,
  sum(priceeach * quantityordered) as sales
from
  orderdetails
where
  ordernumber in (
    select
      ordernumber
    from
      orders
    where
      year(orderdate) = 2003
  )
group by
  productCode
order by
  sales desc
limit
  5;
8.)
select
  p.productname,
  top5product2003.sales
from
  (
    select
      productCode,
      sum(priceeach * quantityordered) as sales
    from
      orderdetails
    where
      ordernumber in (
        select
          ordernumber
        from
          orders
        where
          year(orderdate) = 2003
      )
    group by
      productCode
    order by
      sales desc
    limit
      5
  ) top5product2003
  join products p using(productCode);
9.)
select
  customerNumber,
  sales,
  case
    when sales > 100000 then "Platinum"
    when sales >= 10000
    and sales <= 100000 then "Gold"
    when sales < 10000 then "Silver"
  end customerGroup
from
  (
    select
      o.customerNumber as customerNumber,
      sum(d.priceeach * d.quantityordered) as sales
    from
      orderdetails d
      inner join orders o using(orderNumber)
    where
      year(shippeddate) = 2003
    group by
      o.customerNumber
  ) derived;
10.)
select
  customerGroup,
  count(customerGroup) AS groupCount
from(
    select
      customerNumber,
      sales,
      case
        when sales > 100000 then "Platinum"
        when sales between 10000
        and 100000 then "Gold"
        when sales < 10000 then "Silver"
      end customerGroup
    from
      (
        select
          o.customerNumber as customerNumber,
          sum(d.priceeach * d.quantityordered) as sales
        from
          orderdetails d
          inner join orders o using(orderNumber)
        where
          year(shippeddate) = 2003
        group by
          o.customerNumber
      ) derived
  ) sub
group by
  customerGroup;

[Gaurav-Joshi-31]

1) select * from payments where amount>(select avg(amount) from payments) limit 10;
2)select customerName from customers where customerNumber not in(select customerNumber from orders)limit 10;
3)select max(items),min(items),floor(avg(items)) from( select count(*) as items from orderDetails group by orderNumber) as t;
4) select productName,buyPrice from products where buyPrice>(select avg(buyPrice) from products) limit 10;
5)select orderNumber, total from(select orderNumber ,sum((quantityOrdered*priceEach)) as total from orderDetails group by orderNumber) as t where total>60000;
6) select c.customerNumber, c.customerName 
from customers c
where exists (select o.orderNumber, sum(od.priceEach * od.quantityOrdered) as total
fromorders o, orderdetails od
whereo.orderNumber = od.orderNumber AND customerNumber = c.customerNumber
group by od.orderNumber having sum(priceEach * quantityOrdered) > 60000);

7) select productCode,sum(quantityOrdered*priceEach) as sales from orders inner join orderDetails using(orderNumber) where orderDate between '2003-01-01' and '2003-12-31' group by productCode order by sales desc limit 5;

8) select productName,sales from products inner join (select productCode,sum(quantityOrdered*priceEach) as sales from orders inner join orderDetails using(orderNumber) where orderDate between '2003-01-01' and '2003-12-31' group by productCode order by sales desc limit 5) as top5product2003 using(productCode); 

9) select customerNumber,sum(quantityOrdered*priceEach) as sales,case
when sum(quantityOrdered*priceEach)> 100000 then 'Platinum'
when sum(quantityOrdered*priceEach)>10000 then 'Gold'
else 'Silver'
end as customerGroup
from orderDetails inner join orders using(orderNumber) where YEAR(orders.shippedDate) = 2003 OR YEAR(orders.requiredDate) = 2003 group by customerNumber order by customerNumber;


8) select customerGroup,count(customerGroup) as groupCount from(select customerNumber,sum(quantityOrdered*priceEach) as sales,case
when sum(quantityOrdered*priceEach)> 100000 then 'Platinum'
when sum(quantityOrdered*priceEach)>10000 then 'Gold'
else 'Silver'
end as customerGroup
from orderDetails inner join orders using(orderNumber) where YEAR(orders.shippedDate) = 2003 OR YEAR(orders.requiredDate) = 2003 group by customerNumber order by customerNumber) as t group by customerGroup;

[lalit-ky]

-- 1).
SELECT customerNumber,checkNumber,amount FROM payments 
    WHERE amount > (SELECT AVG(amount) FROM payments);

-- 2).
SELECT customerName FROM customers 
    WHERE customerNumber NOT IN (SELECT customerNumber FROM orders);

-- 3).

SELECT MAX(items),MIN(items),FLOOR(AVG(items)) 
    FROM (SELECT orderNumber,COUNT(orderNumber) AS items 
        FROM orderdetails 
        GROUP BY orderNumber) AS derivedTable;

--4).
SELECT p.productName,p.buyprice 
    FROM products p 
    WHERE p.buyprice > (SELECT AVG(buyprice) FROM products);

--5).
SELECT orderNumber,SUM(quantityOrdered*priceEach) As total 
    FROM orderdetails  
    GROUP BY orderNumber 
    HAVING SUM(quantityOrdered*priceEach) >60000; 

--6).
SELECT customerNumber,CustomerName FROM customers c
    WHERE EXISTS(SELECT o.orderNumber,SUM(od.quantityOrdered * od.priceEach) As total 
        FROM orders o,orderdetails od 
        WHERE o.orderNumber = od.orderNumber AND o.customerNumber = c.customerNumber
        GROUP BY orderNumber 
        HAVING SUM(quantityOrdered*priceEach) >60000);

-- 7).
SELECT productCode, FLOOR(sum(quantityOrdered * priceEach))AS sales
    FROM orderdetails INNER JOIN orders USING (orderNumber)
    WHERE year(orderDate) = '2003'
    GROUP BY (productCode)
    ORDER BY sales DESC
    LIMIT 5;

--8). 
SELECT productName,sales 
    FROM products INNER JOIN (SELECT productCode, FLOOR(sum(quantityOrdered * priceEach))AS sales
        FROM orderdetails INNER JOIN orders USING (orderNumber)
        WHERE year(orderDate) = '2003'
        GROUP BY (productCode)
        ORDER BY sales DESC
        LIMIT 5) AS top5product2003
    USING (productCode);

-- 9). 
SELECT customerNumber, FLOOR(SUM(quantityOrdered*priceEach)) AS sales, 
    (CASE 
        WHEN FLOOR(SUM(quantityOrdered*priceEach)) > 100000 THEN 'Platinum'
        WHEN FLOOR(SUM(quantityOrdered*priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
        WHEN FLOOR(SUM(quantityOrdered*priceEach)) < 10000 THEN 'Silver'
    END) AS customerGroup
    FROM orders INNER JOIN orderdetails USING (orderNumber)
    WHERE year(orderDate) = '2003'
    Group BY customerNumber
    ORDER BY customerNumber;

-- 10).
SELECT customerGroup,count(customerNumber) AS groupCount FROM
    (SELECT customerNumber, FLOOR(SUM(quantityOrdered*priceEach)) AS sales, 
        (CASE 
            WHEN FLOOR(SUM(quantityOrdered*priceEach)) > 100000 THEN 'Platinum'
            WHEN FLOOR(SUM(quantityOrdered*priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
            WHEN FLOOR(SUM(quantityOrdered*priceEach)) < 10000 THEN 'Silver'
        END) AS customerGroup
        FROM orders INNER JOIN orderdetails USING (orderNumber)
        WHERE year(orderDate) = '2003'
        Group BY customerNumber) AS derivedTable
    Group BY customerGroup;

[lalit-ky]

updated

[Khyati-tripathi]

--subqueries
1.SELECT customerNumber,
    checkNumber,
    amount
from payments
where amount >(
        SELECT avg(amount)
        from payments
    );
2.SELECT customerName
from customers
where customerNumber not in (
        SELECT customerNumber
        from orders
    );
3. select max(items),
    min(items),
    floor(avg(items))
from(
        select count(quantityordered) as items
        from orderdetails
        GROUP BY orderNumber
    ) t;
4..select p.productName,
    p.buyPrice
from products p
where p.buyPrice >(
        select AVG(buyPrice)
        from products
        GROUP BY p.productLine
    );
5.. SELECT o.orderNumber,
    SUM(d.priceEach * d.quantityOrdered) AS total
FROM orders o
    inner join orderdetails d using(orderNumber)
GROUP BY d.orderNumber
HAVING total > 60000;
6. SELECT c.customerNumber,
    c.customerName
from customers c
where exists(
        SELECT o.orderNumber,
            SUM(d.priceEach * d.quantityOrdered) AS total
        FROM orders o
            inner join orderdetails d on o.orderNumber = d.orderNumber
            and c.customerNumber = o.customerNumber
        GROUP BY d.orderNumber
        HAVING total > 60000
    );
7. select d.productCode,
    floor(sum(d.quantityOrdered * d.priceEach)) as sales
from orderdetails d
    inner JOIN orders o using(orderNumber)
where YEAR(o.shippedDate) = 2003
group by d.productCode
ORDER BY sales DESC
LIMIT 5;
8. select productName,
    top5product2003.sales
from products
    inner join (
        select d.productCode,
            floor(sum(d.quantityOrdered * d.priceEach)) as sales
        from orderdetails d
            inner JOIN orders o using(orderNumber)
        where YEAR(o.shippedDate) = 2003
        group by d.productCode
        ORDER BY sales DESC
        LIMIT 5
    ) top5product2003 using(productCode);
9.select o.customerNumber,
    floor(sum(d.quantityOrdered * d.priceEach)) as sales,
    (
        CASE
            when floor(sum(d.quantityOrdered * d.priceEach)) > 100000 then 'Platinum'
            when floor(sum(d.quantityOrdered * d.priceEach)) between 10000 and 100000 then 'Gold'
            else 'Silver'
        end
    ) as customerGroup
from orderdetails d
    inner JOIN orders o using(orderNumber)
where YEAR(o.shippedDate) = 2003
group by o.customerNumber
ORDER BY o.customerNumber;
10. select customerGroup,
    count(customerGroup) as groupCount
from (
        select o.customerNumber,
            floor(sum(d.quantityOrdered * d.priceEach)) as sales,
            (
                CASE
                    when floor(sum(d.quantityOrdered * d.priceEach)) > 100000 then 'Platinum'
                    when floor(sum(d.quantityOrdered * d.priceEach)) between 10000 and 100000 then 'Gold'
                    else 'Silver'
                end
            ) as customerGroup
        from orderdetails d
            inner JOIN orders o using(orderNumber)
        where YEAR(o.shippedDate) = 2003
        group by o.customerNumber
        ORDER BY o.customerNumber
    ) t
group by customerGroup;

[mkaifahmed]

--Write a query to find customers whose payments are greater than the average payment using a subquery.

SELECT customerNumber,checkNumber,amount FROM payments
WHERE amount>(SELECT AVG(amount) FROM payments) LIMIT 9;

--See the following customers and orders tables: Use a subquery with NOT IN operator to find the customers who have not placed any orders.

SELECT customerName FROM customers WHERE customerNumber NOT IN (SELECT customerNumber FROM orders) LIMIT 9;

--Write a subquery that finds the maximum, minimum, and average number of items in sale orders from orderdetails.

SELECT MAX(items), MIN(items), FLOOR(AVG(items))
FROM (SELECT orderNumber, COUNT(orderNumber) AS items FROM orderdetails GROUP BY orderNumber) AS orderItems ;

--Unlike a standalone subquery, a correlated subquery is a subquery that uses the data from the outer query. In other words, a correlated subquery depends on the outer query. A correlated subquery is evaluated once for each row in the outer query.
--Use a correlated subquery to select products whose buy prices are greater than the average buy price of all products in each product line.
--For each row in the products (or p1) table, the correlated subquery needs to execute once to get the average buy price of all products in the productline of that row.
--If the buy price of the current row is greater than the average buy price returned by the correlated subquery, the query includes the row in the result set.

SELECT productname, buyprice FROM products p
WHERE buyprice > (SELECT AVG(buyprice) FROM products WHERE productline = p.productline) LIMIT 8;

--Let’s take a look at the orders and orderdetails tables:
--Write a query that finds sales orders whose total values are greater than 60K as follows:

SELECT orderNumber, SUM(priceEach * quantityOrdered) total FROM orderdetails
INNER JOIN orders USING (orderNumber) GROUP BY orderNumber HAVING SUM(priceEach * quantityOrdered) > 60000;


--When a subquery is used with the EXISTS or NOT EXISTS operator, a subquery returns a Boolean value of TRUE or FALSE. The following query illustrates a subquery used with the EXISTS operator:
SELECT 
    *
FROM
    table_name
WHERE
    EXISTS( subquery );

--In the query above, if the subquery returns any rows, EXISTS subquery returns TRUE, otherwise, it returns FALSE.
--Use the query in question no. 5 as a correlated subquery to find customers who placed at least one sales order with the total value greater than 60K by using the EXISTS operator:

SELECT customerNumber, customerName FROM customers
WHERE EXISTS( SELECT 
            orderNumber, SUM(priceEach * quantityOrdered)
        FROM
            orderdetails
                INNER JOIN
            orders USING (orderNumber)
        WHERE
            customerNumber = customers.customerNumber
        GROUP BY orderNumber
        HAVING SUM(priceEach * quantityOrdered) > 60000);
--Write a query that gets the top five products by sales revenue in 2003 from the orders and orderdetails tables as follows:

SELECT productCode, FLOOR(sum(quantityOrdered * priceEach))AS sales
    FROM orderdetails INNER JOIN orders USING (orderNumber)
    WHERE year(orderDate) = '2003'
    GROUP BY (productCode)
    ORDER BY sales DESC
    LIMIT 5;

--You can use the result of the previous query as a derived table called top5product2003 and join it with the products table using the productCode column.. 
--Then, find out the productName and sales of the top 5 products in 2003.

SELECT productName,sales 
    FROM products INNER JOIN (SELECT productCode, FLOOR(sum(quantityOrdered * priceEach))AS sales
        FROM orderdetails INNER JOIN orders USING (orderNumber)
        WHERE year(orderDate) = '2003'
        GROUP BY (productCode)
        ORDER BY sales DESC
        LIMIT 5) AS top5product2003
        USING (productCode);

[shreiyarandive]

1) select customerNumber, checkNumber, amount from payments where amount > (select avg(amount) from payments );

2) select customerName from customers where customerNumber not in (select customerNumber from orders);

3) select max(item), min(item), floor(avg(item)) from (select count(orderNumber)as item from orderdetails group by orderNumber) as table1;

4) select productName, buyPrice from products where buyPrice > (select avg(buyPrice) from products);

5) select orderNumber, sum(priceEach * quantityOrdered) as total from orderdetails group by orderNumber having sum(priceEach * quantityOrdered) > 60000;

6) select customerName, customerNumber from customers where exists(select o.orderNumber, sum(o.priceEach * o.quantityOrdered) as total from orderdetails o, orders c where o.orderNumber = c.orderNumber and c.customerNumber = customers.customerNumber group by orderNumber having sum(priceEach * quantityOrdered) > 60000);

7) select productcode, sum(quantityordered * priceeach) as sales from orderdetails join orders using(ordernumber) where year(shippeddate) = 2003 group by productcode order by sales desc limit 5;

8) select productname,sales from (select productcode,sum(quantityordered * priceeach)as sales from orderdetails inner join orders using(ordernumber) where year(shippeddate) = 2003 group by productcode order by sales desc limit 5) top5product2003 join products using(productcode);

9) select customernumber, sum(quantityordered * priceeach)as sales,(case when (sum(quantityordered*priceeach)) > 100000 then 'Platinum' when (sum(quantityordered * priceeach)) between 10000 and 100000 then 'Gold' when (sum(quantityordered*priceeach))<10000 then 'silver' end) as customerGroup from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by customernumber order by customernumber;

10) select customergroup, count(customergroup)as groupcount from (select customernumber,sum(quantityordered*priceeach)as sales,(case when (sum(quantityordered*priceeach))>100000 then 'Platinum' when (sum(quantityordered*priceeach)) between 10000 and 100000 then 'Gold' when (sum(quantityordered * priceeach)) < 10000 then 'silver' end) as customerGroup from orderdetails inner join orders using(ordernumber) where year(shippeddate) = 2003 group by customernumber order by customernumber) as DT1 group by customergroup;

[deepak775035]

-- Task 15 march 2022 Morning session

-- Qusetion 1:
SELECT customerNumber, checkNumber, amount FROM payments
WHERE amount > (SELECT AVG (amount) FROM payments);


-- Qusetion 2:  
SELECT customerName FROM customers WHERE customerNumber NOT IN (SELECT customerNumber FROM orders);


-- Qusetion 3:
SELECT MAX(items) ,MIN(items) ,FLOOR(AVG(items))
FROM (SELECT COUNT(orderNumber) AS items FROM orderdetails GROUP BY orderNumber) AS t


-- Qusetion 4: doubt(is Logic Correct)
SELECT productName, buyPrice FROM products outerp
WHERE buyPrice>(SELECT AVG(buyPrice) FROM products innerp WHERE innerp.productLine=outerp.productLine);


-- Qusetion 5:
SELECT * FROM 
(SELECT orderNumber, SUM(quantityOrdered * priceEach) AS total FROM orderdetails GROUP BY orderNumber) AS t
WHERE total>60000;


-- Qusetion 6:  NOT WORKING(doubt)
SELECT customerNumber, customerName FROM customers c
WHERE EXISTS

[ps030300]

1-
select customerNumber, checkNumber, amount from payments where amount>(select AVG(amount) from payments) limit 9;
2-
select A.customerName from customers A  where A.customerNumber NOT IN(select B.customerNumber from orders B) limit 9;
3-
 select MAX(items),MIN(items), AVG(items) from(select count(orderNumber) as items from orderDetails group by orderNumber) as o;
4-
select productName, buyprice from products where buyprice>(select avg(buyprice) from products)limit 8;

5-
select orderNumber,sum(priceEach*quantityOrdered) as total from orderdetails group by orderNumber having sum(priceEach*quantityOrdered)>60000;

7-
select productCode,sum(quantityOrdered*priceEach)as sales from orderDetails INNER JOIN orders using(orderNumber) where year(shippedDate)=2003 group by productCode order by sales DESC limit 5;
8-
select productname,sales from (select productcode,sum(quantityordered*priceeach)as sales from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by productcode order by sales desc limit 5) top5product2003 join products using(productcode);

[nithin1429]

1. select customerNumber,checkNumber,amount from payments where amount>(select avg(amount) from payments);
2. select customerName from customers where customerNumber NOT IN (select customerNumber from orders);
3. select max(items),min(items),floor(avg(items)) from (select orderNumber,count(orderNumber) as items from orderdetails group by orderNumber) as lineitems;
4. select productName,buyprice from products where buyprice > (select avg(buyprice) from products);
5. select orderNumber,sum(priceEachquantityOrdered) total from orderdetails INNER JOIN orders using (orderNumber) group by orderNumber having sum(priceEachquantityOrdered)>60000;
6. select customerNumber,customerName from customers where exists(select orderNumber,sum(priceEachquantityOrdered) from orderdetails inner join orders using (orderNumber) where customerNumber =customers.customerNumber group by orderNumber having sum(priceEachquantityOrdered)>60000);

[charani712]

use classicmodels;
select p.customerNumber,p.checkNumber,p.amount
from customers c inner join payments p
on c.customerNumber = p.customerNumber
where p.amount > (select avg(amount) from payments)
order by p.customerNumber;
2.
select customerName from customers
where customerNumber not in (select customerNumber from orders);
3.
select MAX(items),MIN(items),AVG(items)
from(select count(ordernumber)as items from orderdetails group by ordernumber) as derivedtable;
4.
select productName,buyprice from products p
where buyprice > (select avg(buyprice) from products);
5.
select orderNumber,SUM(quantityOrderedpriceEach) As total from orderdetails
group by orderNumber having SUM(quantityOrderedpriceEach) >60000;

7.use classicmodels;
select p.productName,sum(quantityOrdered*priceEach) as sales from orders o,orderdetails od,products p
where o.orderNumber=od.orderNumber
and od.productCode = p.productCode
AND ( YEAR(o.orderDate) = 2003 )
group by productCode
order by sales desc
Limit 5;

[SrideviYamala]

1. select customerNumber,checkNumber,amount from payments
where amount>(select avg(amount) from payments);

2. select customerName from customers
where customerNumber not in (select customerNumber from orders);

3. select MAX(items),MIN(items),FLOOR(AVG(items)) from 
(select count(*) as items from orderdetails GROUP BY orderNumber) T;

4. select productName,buyPrice from products
where buyPrice > (select avg(buyPrice) from products);

5. select orderNumber,sum(quantityOrdered*priceEach) as total from orderdetails 
GROUP BY orderNumber having total > 60000;

6. select customerNumber,customerName from customers c 
where exists(select o.orderNumber,sum(quantityOrdered*priceEach) as total 
from orderdetails od,orders o where o.orderNumber = od.orderNumber and 
c.customerNumber=customerNumber
GROUP BY orderNumber having total > 60000);

7. select productCode,floor(sum(quantityOrdered*priceEach)) as sales
from orderdetails where orderNumber in
(select orderNumber from orders where year(shippedDate)=2003)
GROUP BY productCode ORDER BY sales desc limit 5;

8. select productName,sales from 
(select productCode,floor(sum(quantityOrdered*priceEach)) as sales
from orderdetails where orderNumber in
(select orderNumber from orders where year(shippedDate)=2003)
GROUP BY productCode ORDER BY sales desc limit 5) top5product2003 
inner join products using (productCode);

9. select customerNumber,round(sum(quantityOrdered * priceEach)) as sales,
(CASE 
    WHEN floor(sum(quantityOrdered * priceEach)) > 100000 THEN 'Platinum'
    WHEN floor(sum(quantityOrdered * priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
    WHEN floor(sum(quantityOrdered * priceEach)) < 10000 THEN 'Silver'
 END) AS customerGroup from orderdetails
 inner join orders using(orderNumber) 
 inner join customers using(customerNumber)
 where year(shippedDate)=2003
 GROUP BY customerNumber order by customerNumber;

10. select customerGroup,count(customerGroup) from
 (select customerNumber,round(sum(quantityOrdered * priceEach)) as sales,
(CASE 
    WHEN floor(sum(quantityOrdered * priceEach)) > 100000 THEN 'Platinum'
    WHEN floor(sum(quantityOrdered * priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
    WHEN floor(sum(quantityOrdered * priceEach)) < 10000 THEN 'Silver'
 END) AS customerGroup from orderdetails
 inner join orders using(orderNumber) 
 inner join customers using(customerNumber)
 where year(shippedDate)=2003
 GROUP BY customerNumber order by customerNumber) T
 GROUP BY customerGroup;

[rakeshGit419]

use classicmodels;
-- 1
select p.customerNumber,p.checkNumber,p.amount from customers c join payments p
on c.customerNumber = p.customerNumber
where p.amount > (select avg(amount) from payments)
order by p.customerNumber;

-- 2
select customerName from customers 
where customerNumber not in
(select customerNumber from orders);

-- 3 issue
select max(item),min(item),floor(avg(item)) from
(select count(orderNumber) as item from orderdetails group by orderNumber) as d;

-- 4 
select productName,buyPrice from products
where buyPrice >
(select avg(buyPrice) from products);

-- 5 
select o.orderNumber,sum(o2.quantityOrdered*o2.priceEach) as total
from orders o
inner join orderdetails o2
on o.orderNumber = o2.orderNumber
group by o.orderNumber
having total>60000;

-- 6

select c.customerNumber,c.customerName from customers c
where exists

(select o.orderNumber,sum(o2.quantityOrdered*o2.priceEach) as total
from orders o
inner join orderdetails o2
on o.orderNumber = o2.orderNumber AND o.customerNumber = c.customerNumber
group by o.orderNumber
having total>60000);

select productcode,sum(quantityordered*priceeach) as sales from orderdetails inner join orders 
using(ordernumber) where year(shippedDate)='2003'
group by productcode 
order by sales desc
limit 5;

-- 8 
select productName,sales from 
(select productcode,sum(quantityordered*priceeach) as sales from orderdetails inner join orders 
using(ordernumber) where year(shippedDate)=2003 
group by productcode 
order by sales desc 
limit 5) as top5product2003 join products using(productCode);

[vysh675]

1.select customerNumber,checkNumber,amount from payments where amount>(select avg(amount) from payments);

2.select customername from customers where customerNumber not in(select customerNumber from orders);

3.select max(items),min(items),avg(items) from(select count(ordernumber) as items from orderdetails group by ordernumber) as derivedtable;

4. select productname,buyprice from products where buyprice>(select AVG(buyprice) from products);

5.select o.orderNumber, SUM(od.priceEach * od.quantityOrdered) as total from orders o, orderdetails od where o.orderNumber = od.orderNumber GROUP BY od.orderNumber HAVING SUM(priceEach * quantityOrdered) > 60000;

6.select customerNumber,customerName from customers where exists(select orderNumber,sum(priceEachquantityOrdered) from orderdetails inner join orders using (orderNumber) where customerNumber =customers.customerNumber group by orderNumber having sum(priceEachquantityOrdered)>60000);

[RohitNaikade1]

1.select customerNumber,checkNumber,amount from payments where amount >(select avg(amount) from payments);

2.select customerName from customers where customerNumber NOT IN (select customerNumber from orders);

3.select max(items),min(items),floor(avg(items)) from (select count(orderNumber) as items from orderdetails group by orderNumber) A;

4.select productName,buyPrice from products where buyPrice>(select avg(buyPrice) from products);

5.select orderNumber,total from (select orderNumber,sum(priceEach*quantityOrdered) as total from orderdetails group by orderNumber having sum(priceEach*quantityOrdered)>60000) A;

6.select customerNumber,customerName from customers where customerNumber in (select customerNumber from orders where orderNumber in (select orderNumber from orderdetails group by orderNumber having sum(priceEach*quantityOrdered)>60000)) and customerNumber is not null;

7.select productCode,floor(sum(orderdetails.quantityOrdered*orderdetails.priceEach)) as sales from orders inner join orderdetails on orders.orderNumber=orderdetails.orderNumber and 
( year(orders.shippedDate)=2003 )
group by productCode
order by sales desc
limit 5;

8.select p.productName,sales from products p inner join 
(select productCode,floor(sum(orderdetails.quantityOrdered*orderdetails.priceEach)) as sales from orders inner join orderdetails on orders.orderNumber=orderdetails.orderNumber and 
( year(orders.shippedDate)=2003 or year(orders.requiredDate)=2003 )
group by productCode
order by sales desc
limit 5) top5Products on p.productCode=top5Products.productCode;

9.
SELECT customerNumber, FLOOR(SUM(quantityOrdered*priceEach)) AS sales, 
(CASE 
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) > 100000 THEN 'Platinum'
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) < 10000 THEN 'Silver'
 END) AS customerGroup
FROM orderdetails inner join orders
WHERE orderdetails.orderNumber = orders.orderNumber AND ( YEAR(orders.shippedDate) = 2003)
GROUP BY customerNumber
ORDER BY customerNumber;

10.select customerGroup,count(customerGroup) from 
(SELECT customerNumber, FLOOR(SUM(quantityOrdered*priceEach)) AS sales, 
(CASE 
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) > 100000 THEN 'Platinum'
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
    WHEN FLOOR(SUM(quantityOrdered*priceEach)) < 10000 THEN 'Silver'
 END) AS customerGroup
FROM orderdetails inner join orders
WHERE orderdetails.orderNumber = orders.orderNumber AND ( YEAR(orders.shippedDate) = 2003)
GROUP BY customerNumber
ORDER BY customerNumber) A
group by customerGroup;

[Batchusatyasri]

--1st  query--
SELECT
  `customerNumber`,
  `checkNumber`,
  amount
FROM
  payments
WHERE
  amount > (
    SELECT
      avg(amount)
    FROM
      payments
  );
--2nd query--
SELECT
  customerName
FROM
  customers
WHERE
  customerNumber not in (
    SELECT
      customerNumber
    from
      orders
  );



--3rd query--
SELECT
  MAX(items),
  MIN(items),
  FLOOR(AVG(items))
FROM
  (
    SELECT
      count(orderNumber) as items
    from
      orderdetails
    group by
      orderNumber
  ) as tablename;



--4th query--
SELECT
  productName,
  buyPrice
FROM
  products
WHERE
  buyPrice >(
    SELECT
      avg(buyPrice)
    FROM
      products
  );


--5th query--
select
  ordernumber,
  sum(priceeach * quantityordered) total
from
  orderdetails
  inner join orders using(ordernumber)
group by
  ordernumber
having
  sum(priceeach * quantityordered) > 60000;



--6th query--
SELECT
  customerNumber,
  customerName
from
  customers
WHERE
  EXISTS(
    select
      ordernumber,
      sum(priceeach * quantityordered) total
    from
      orderdetails
      inner join orders using(ordernumber)
    group by
      ordernumber
    having
      sum(priceeach * quantityordered) > 60000
  );



--7th query--
SELECT
  productCode,
  sum(priceeach * quantityordered) as sales
from
  orders
  INNER JOIN
  orderdetails
where
  orderdetails.orderNumber = orders.orderNumber
  AND (
    YEAR(orders.shippedDate) = 2003
    OR YEAR(orders.requiredDate) = 2003
  )
group by
  productCode
order by
  sales desc
limit
  5;



--8th query--
SELECT
  productName,
  sales
from
  products,(
    SELECT
      productCode,
      sum(priceeach * quantityordered) as sales
    from
      orderdetails
      INNER JOIN
      orders
    where
      orderdetails.orderNumber = orders.orderNumber
      AND (
        YEAR(orders.shippedDate) = 2003
        OR YEAR(orders.requiredDate) = 2003
      )
    group by
      productCode
    order by
      sales desc
    limit
      5
  ) as top5product2003
WHERE
  products.productCode = top5product2003.productCode
GROUP BY
  top5product2003.productCode;


--9th query--
select
  customerNumber,
  FLOOR(sum(quantityordered * priceeach)) as sales,
  (
    CASE
      WHEN FLOOR(SUM(quantityOrdered * priceEach)) > 100000 THEN 'Platinum'
      WHEN FLOOR(SUM(quantityOrdered * priceEach)) BETWEEN 10000
      AND 100000 THEN 'Gold'
      WHEN FLOOR(SUM(quantityOrdered * priceEach)) < 10000 THEN 'Silver'
    END
  ) AS customerGroup
FROM
  orderdetails,
  orders
WHERE
  orderdetails.orderNumber = orders.orderNumber
  AND (
    YEAR(orders.shippedDate) = 2003
    OR YEAR(orders.requiredDate) = 2003
  )
GROUP BY
  customerNumber
ORDER BY
  customerNumber;



--10th query--
select
  customerGroup,
  COUNT(customerGroup) AS groupCount
FROM
  (
    select
      customerNumber,
      FLOOR(sum(quantityordered * priceeach)) as sales,
      (
        CASE
          WHEN FLOOR(SUM(quantityOrdered * priceEach)) > 100000 THEN 'Platinum'
          WHEN FLOOR(SUM(quantityOrdered * priceEach)) BETWEEN 10000
          AND 100000 THEN 'Gold'
          WHEN FLOOR(SUM(quantityOrdered * priceEach)) < 10000 THEN 'Silver'
        END
      ) AS customerGroup
    FROM
      orderdetails,
      orders
    WHERE
      orderdetails.orderNumber = orders.orderNumber
      AND (
        YEAR(orders.shippedDate) = 2003
        OR YEAR(orders.shippedDate) = 2003
      )
    GROUP BY
      customerNumber
    ORDER BY
      customerNumber
  ) as derivedtable
group by
  customerGroup;

[SabaMhabar]

SELECT customerNumber, checkNumber, amount
FROM payments
WHERE amount > (SELECT AVG(amount) FROM payments);

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

SELECT MAX(items), MIN(items), FLOOR(AVG(items))
FROM payments
(SELECT orderNumber, COUNT(orderNumber) AS items FROM orderdetails GROUP BY orderNumber);

SELECT productname, buyprice
FROM products p1
WHERE buyprice > (SELECT AVG(buyprice) FROM products WHERE productline = p1.productline);

SELECT orderNumber, SUM(priceEach * quantityOrdered) total
FROM orderdetails INNER JOIN orders USING (orderNumber)
GROUP BY orderNumber HAVING SUM(priceEach * quantityOrdered) > 60000;

SELECT customerNumber, customerName FROM customers WHERE EXISTS(
SELECT orderNumber, SUM(priceEach * quantityOrdered) FROM orderdetails INNER JOIN orders
USING (orderNumber) WHERE customerNumber = customers.customerNumber
GROUP BY orderNumber HAVING SUM(priceEach * quantityOrdered) > 60000);

SELECT productCode, Sum(priceEach * quantityOrdered) as sales
from orders USING (orderNumber) group by orderNumber limit 5;

SELECT productName, sales FROM (SELECT productCode, ROUND(SUM(quantityOrdered * priceEach)) sales
FROM orderdetails INNER JOIN orders USING (orderNumber) WHERE YEAR(shippedDate) = 2003
GROUP BY productCode ORDER BY sales DESC LIMIT 5) top5products2003
INNER JOIN products USING (productCode);

SELECT customerNumber, ROUND(SUM(quantityOrdered * priceEach)) sales,
(CASE WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum' END) customerGroup
FROM orderdetails INNER JOIN orders USING (orderNumber) WHERE YEAR(shippedDate) = 2003 GROUP BY customerNumber;

SELECT customerGroup, COUNT(cg.customerGroup) AS groupCount FROM
(SELECT customerNumber, ROUND(SUM(quantityOrdered * priceEach)) sales,
(CASE WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum' END) customerGroup
FROM orderdetails INNER JOIN orders USING (orderNumber) WHEREYEAR(shippedDate) = 2003 GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;

[PavanArvapally]

1. SELECT customerNumber, checkNumber, amount FROM payments WHERE amount > (SELECT AVG(amount) FROM payments);

2. SELECT customerName FROM customers WHERE customerNumber NOT IN (SELECT customerNumber FROM orders);

3. SELECT MAX(items), MIN(items), FLOOR(AVG(items)) FROM (SELECT COUNT(orderNumber) AS items FROM orderdetails GROUP BY orderNumber)  AS derivedTable;

4. SELECT productName, buyPrice FROM products WHERE buyPrice > (SELECT AVG(buyPrice) FROM products);

5. SELECT orderNumber, SUM(quantityOrdered*priceEach) AS total FROM orderdetails GROUP BY orderNumber HAVING SUM(quantityOrdered*priceEach) > 60000;

6. Select customerNumber,customerName from customers c where exists(Select orderNumber,sum(quantityOrdered*priceEach)as total
from orderdetails od inner join orders o using (orderNumber)
 where c.customerNumber=customerNumber group by orderNumber having total>60000
);

7.  SELECT productCode, SUM(quantityOrdered*priceEach) 
AS sales 
FROM orderdetails INNER JOIN orders USING (orderNumber) 
WHERE year(shippeddate)=2003 GROUP BY productCode ORDER BY sales DESC LIMIT 5;

8. SELECT productName, sales 
FROM products INNER JOIN (SELECT productCode, SUM(quantityOrdered*priceEach) AS sales
FROM orderdetails INNER JOIN orders USING (orderNumber) WHERE year(shippeddate)=2003
GROUP BY productCode ORDER BY sales DESC LIMIT 5) AS top5product2003 USING(productCode);

9. SELECT customerNumber, SUM(quantityOrdered*priceEach) AS sales,
(CASE
    WHEN SUM(quantityOrdered*priceEach) > 100000 THEN 'Platinum'
    WHEN SUM(quantityOrdered*priceEach) > 10000 THEN 'Gold'
    ELSE 'Silver'
END) AS customerGroup
FROM orders INNER JOIN orderdetails USING (orderNumber)
WHERE year(orderDate) = '2003'
GROUP by customerNumber
ORDER BY customerNumber;

[Mrafe]

-- 1).
SELECT customerNumber,checkNumber,amount FROM payments 
    WHERE amount > (SELECT AVG(amount) FROM payments);

-- 2).
SELECT customerName FROM customers 
    WHERE customerNumber NOT IN (SELECT customerNumber FROM orders);

-- 3).

SELECT MAX(items),MIN(items),FLOOR(AVG(items)) 
    FROM (SELECT orderNumber,COUNT(orderNumber) AS items 
        FROM orderdetails 
        GROUP BY orderNumber) AS derivedTable;

--4).
SELECT p.productName,p.buyprice 
    FROM products p 
    WHERE p.buyprice > (SELECT AVG(buyprice) FROM products);

--5).
SELECT orderNumber,SUM(quantityOrdered*priceEach) As total 
    FROM orderdetails  
    GROUP BY orderNumber 
    HAVING SUM(quantityOrdered*priceEach) >60000; 

--6).
SELECT customerNumber,CustomerName FROM customers c
    WHERE EXISTS(SELECT o.orderNumber,SUM(od.quantityOrdered * od.priceEach) As total 
        FROM orders o,orderdetails od 
        WHERE o.orderNumber = od.orderNumber AND o.customerNumber = c.customerNumber
        GROUP BY orderNumber 
        HAVING SUM(quantityOrdered*priceEach) >60000);

-- 7).
SELECT productCode, FLOOR(sum(quantityOrdered * priceEach))AS sales
    FROM orderdetails INNER JOIN orders USING (orderNumber)
    WHERE year(orderDate) = '2003'
    GROUP BY (productCode)
    ORDER BY sales DESC
    LIMIT 5;

--8). 
SELECT productName,sales 
    FROM products INNER JOIN (SELECT productCode, FLOOR(sum(quantityOrdered * priceEach))AS sales
        FROM orderdetails INNER JOIN orders USING (orderNumber)
        WHERE year(orderDate) = '2003'
        GROUP BY (productCode)
        ORDER BY sales DESC
        LIMIT 5) AS top5product2003
    USING (productCode);

-- 9). 
SELECT customerNumber, FLOOR(SUM(quantityOrdered*priceEach)) AS sales, 
    (CASE 
        WHEN FLOOR(SUM(quantityOrdered*priceEach)) > 100000 THEN 'Platinum'
        WHEN FLOOR(SUM(quantityOrdered*priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
        WHEN FLOOR(SUM(quantityOrdered*priceEach)) < 10000 THEN 'Silver'
    END) AS customerGroup
    FROM orders INNER JOIN orderdetails USING (orderNumber)
    WHERE year(orderDate) = '2003'
    Group BY customerNumber
    ORDER BY customerNumber;

-- 10).
SELECT customerGroup,count(customerNumber) AS groupCount FROM
    (SELECT customerNumber, FLOOR(SUM(quantityOrdered*priceEach)) AS sales, 
        (CASE 
            WHEN FLOOR(SUM(quantityOrdered*priceEach)) > 100000 THEN 'Platinum'
            WHEN FLOOR(SUM(quantityOrdered*priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
            WHEN FLOOR(SUM(quantityOrdered*priceEach)) < 10000 THEN 'Silver'
        END) AS customerGroup
        FROM orders INNER JOIN orderdetails USING (orderNumber)
        WHERE year(orderDate) = '2003'
        Group BY customerNumber) AS derivedTable
    Group BY customerGroup;

[Prem-3]

-- 1
select p.customerNumber, p.checkNumber, p.amount from customers c join payments p
on c.customerNumber = p.customerNumber
where p.amount > (select avg(amount) from payments)
order by p.customerNumber
limit 25;

-- 2
select customerName from customers 
where customerNumber not in (select customerNumber from orders)
limit 25;

-- 3 issue
select max(item), min(item), floor(avg(item)) from
(select count(orderNumber) as item from orderdetails group by orderNumber) as d
limit 10;

-- 4 
select productName, buyPrice from products
where buyPrice > (select avg(buyPrice) from products)
limit 10;

-- 5 
select o.orderNumber, sum(o2.quantityOrdered*o2.priceEach) as total
from orders o
inner join orderdetails o2
on o.orderNumber = o2.orderNumber
group by o.orderNumber
having total>60000;

-- 6
select c.customerNumber, c.customerName from customers c
where exists
(select o.orderNumber, sum(o2.quantityOrdered*o2.priceEach) as total
from orders o
inner join orderdetails o2
on o.orderNumber = o2.orderNumber AND o.customerNumber = c.customerNumber
group by o.orderNumber
having total>60000);

--7
select productcode,sum(quantityordered*priceeach) as sales from orderdetails inner join orders 
using(ordernumber) where year(shippedDate)='2003'
group by productcode 
order by sales desc
limit 5;

-- 8 
select productName,sales from 
(select productcode,sum(quantityordered*priceeach) as sales from orderdetails inner join orders 
using(ordernumber) where year(shippedDate)=2003 
group by productcode 
order by sales desc 
limit 5) as top5product2003 join products using(productCode);

[VigneshVishwa]

SELECT customerNumber , checkNumber , amount FROM payments 
WHERE amount>(select(avg(amount))from payments);

SELECT customerName from customers 
where customerNumber NOT IN (select customerNumber from orders);

select max(items),min(items),avg(items) from(select count(ordernumber)as items
from orderdetails group by ordernumber) as derivedtable;

select orderNumber, sum(priceEach * quantityOrdered) as total from orderdetails
group by orderNumber having sum(priceEach * quantityOrdered) > 60000;

select c.customerNumber, c.customerName 
from customers c
where exists (select o.orderNumber, sum(od.priceEach * od.quantityOrdered) as total
from orders o, orderdetails od
where o.orderNumber = od.orderNumber and customerNumber = c.customerNumber
group by od.orderNumber having sum(priceEach * quantityOrdered) > 60000);

[Amrit-Raj22]

use classicmodels;
select customerNumber,checkNumber,amount from payments
where amount > (select AVG(amount) FROM payments);

select customerName FROM customers WHERE customerNumber NOT IN (select customerNumber from orders);

select MAX(items) , MIN(items) , FLOOR(AVG(items))
FROM (select COUNT(orderNumber) AS items FROM orderdetails GROUP BY orderNumber) AS derivedTable;

SELECT productName, buyPrice FROM products p
WHERE buyPrice > ( select AVG(buyPrice) FROM products GROUP BY p.productLine);

SELECT o.orderNumber, SUM(od.priceEach * od.quantityOrdered) AS total
FROM orders o, orderdetails od
WHERE o.orderNumber = od.orderNumber
GROUP BY od.orderNumber HAVING SUM(priceEach * quantityOrdered) > 60000;

SELECT c.customerNumber, c.customerName
FROM customers c
WHERE EXISTS (SELECT o.orderNumber, SUM(od.priceEach * od.quantityOrdered) AS total
FROM orders o, orderdetails od
WHERE o.orderNumber = od.orderNumber AND customerNumber = c.customerNumber
GROUP BY od.orderNumber HAVING SUM(priceEach * quantityOrdered) > 60000);

[KaminiJha]

-- based on classicmodels

SELECT * FROM customers;

--1
Select c.customerNumber , p.checkNumber ,p.amount 
FROM customers c, payments p
where c.customerNumber = p.customerNumber AND p.amount > (Select AVG(amount) from payments);
--2
Select c.customerName from  customers c where c.customerNumber NOT IN (Select o.customerNumber from orders o );
--3
Select MAX(items) , MIN(items),FLOOR(AVG(items)) from (Select COUNT(od.orderNumber) as items from orderdetails od GROUP BY orderNumber) as dt;
--4
Select p.productName ,p.buyPrice FROM products p
WHERE p.buyPrice > (Select AVG(pd.buyPrice) from products pd where  p.productLine=pd.productLine);
--5
Select od.orderNumber , sum(od.quantityOrdered * od.priceEach) as total from orderdetails od  GROUP BY od.orderNumber having sum(od.quantityOrdered * od.priceEach) > 60000;
--6
Select c.customerNumber ,c.customerName from customers c where EXISTS(Select o.orderNumber , sum(od.quantityOrdered * od.priceEach) as total 
from orderdetails od
INNER join orders o USING(orderNumber)
WHERE c.customerNumber=o.customerNumber GROUP BY od.orderNumber having sum(od.quantityOrdered * od.priceEach) > 60000) ;
--7
Select od.productCode,sum(od.quantityOrdered*od.priceEach) as sales 
from orderdetails od INNER JOIN orders o
using(orderNumber) where ( EXTRACT(YEAR FROM o.shippedDate) = 2003 OR EXTRACT(YEAR FROM o.requiredDate) = 2003)
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;
--8
Select p.productName,top5Product2003.sales from products p INNER JOIN (Select od.productCode,sum(od.quantityOrdered*od.priceEach) as sales from orderdetails od INNER JOIN orders o USING (orderNumber) WHERE ( EXTRACT(YEAR FROM o.shippedDate) = 2003 OR EXTRACT(YEAR FROM o.requiredDate) = 2003)GROUP BY productCode ORDER BY sales DESC LIMIT 5) as top5Product2003 
USING (productCode);
--9
Select customerNumber,  FLOOR(SUM(quantityOrdered*priceEach)) AS sales, 
(CASE
    when FLOOR(SUM(quantityOrdered*priceEach))>100000 THEN 'platinum'
    when FLOOR(SUM(quantityOrdered*priceEach)) BETWEEN 10000 AND 100000 THEN 'gold'
    when FLOOR(SUM(quantityOrdered*priceEach))<10000 THEN 'silver'
end) as customerGroup from orderdetails INNER JOIN orders USING(orderNumber)
WHERE YEAR(orderDate)=2003 GROUP BY customerNumber
ORDER BY customerNumber;
--10
Select customerGroup,COUNT(customerGroup) from (Select
(CASE
    when FLOOR(SUM(quantityOrdered*priceEach))>100000 THEN 'platinum'
    when FLOOR(SUM(quantityOrdered*priceEach)) BETWEEN 10000 AND 100000 THEN 'gold'
    when FLOOR(SUM(quantityOrdered*priceEach))<10000 THEN 'silver'
end) as customerGroup from orderdetails INNER JOIN orders USING(orderNumber)
WHERE YEAR(shippedDate)=2003 GROUP BY customerNumber
ORDER BY customerNumber) as derviedTable GROUP BY customerGroup ORDER BY customerGroup;

[DivikaAgrawal]

1. select customerNumber,checkNumber,amount from payments 
   where amount>(select avg(amount) from payments);

2. select customerName from customers 
   where customerNumber not in(select customerNumber from orders);

3. select max(items),min(items),avg(items) from(select count(ordernumber)as items 
   from orderdetails group by ordernumber) as derivedtable;

4. select productname,buyprice from products 
   where buyprice>(select avg(buyprice) from products);

5. select ordernumber,sum(priceeach * quantityordered) total 
   from orderdetails inner join orders using(ordernumber) 
   group by ordernumber having sum(priceeach*quantityordered)>60000;

6. select customernumber, customername from customers 
   where EXISTS(select ordernumber,sum(priceeach * quantityordered) 
   from orderdetails inner join orders using(ordernumber) 
   where customernumber=customers.customernumber 
   group by ordernumber having sum(priceeach*quantityordered)>60000); 

7. select productcode,sum(quantityordered*priceeach)as sales from orderdetails 
   inner join orders using(ordernumber) 
   where year(shippeddate)=2003
   group by productcode order by sales desc limit 5;

8. select productname,sales from (select productcode,sum(quantityordered*priceeach)as sales 
   from orderdetails inner join orders using(ordernumber) 
   where year(shippeddate)=2003 group by productcode
   order by sales desc limit 5) top5product2003
   join products using(productcode);

9. select customernumber,sum(quantityordered*priceeach)as sales,
   (case when (sum(quantityordered*priceeach))>100000 then 'Platinum' when (sum(quantityordered*priceeach)) between 10000 and 
  100000 then 'Gold' when (sum(quantityordered*priceeach))<10000 then 'silver' end)
   as customerGroup from orderdetails inner join orders using(ordernumber) 
   where year(shippeddate)=2003 group by customernumber order by customernumber;

10. select customergroup,count(customergroup)as groupcount 
    from (select customernumber,sum(quantityordered*priceeach)as sales,(case when (sum(quantityordered*priceeach))>100000 
    then 'Platinum' when (sum(quantityordered*priceeach)) between 10000 and 100000 then 'Gold' 
    when (sum(quantityordered*priceeach))<10000 then 'silver' end) as customerGroup from orderdetails inner join orders 
    using(ordernumber) 
    where year(shippeddate)=2003 group by customernumber order by customernumber)as derivedtable1 
    group by customergroup;

[ghost]

1. select customerNumber, checkNumber, amount from payments where amount > (select avg(amount) from payments );
2. select customerName from customers where customerNumber not in (select customerNumber from orders);
3. select max(item), min(item), floor(avg(item)) from (select count(orderNumber)as item from orderdetails group by orderNumber) as table1;
4. select productName, buyPrice from products where buyPrice > (select avg(buyPrice) from products);
5. select orderNumber, sum(priceEach * quantityOrdered) as total from orderdetails group by orderNumber having sum(priceEach * quantityOrdered) > 60000;
6. select customerName, customerNumber from customers where exists(select o.orderNumber, sum(o.priceEach * o.quantityOrdered) as total from orderdetails o, orders c where o.orderNumber = c.orderNumber and c.customerNumber = customers.customerNumber group by orderNumber having sum(priceEach * quantityOrdered) > 60000);
7. select productcode, sum(quantityordered * priceeach) as sales from orderdetails join orders using(ordernumber) where year(shippeddate) = 2003 group by productcode order by sales desc limit 5;
8. select productname,sales from (select productcode,sum(quantityordered * priceeach)as sales from orderdetails inner join orders using(ordernumber) where year(shippeddate) = 2003 group by productcode order by sales desc limit 5) top5product2003 join products using(productcode);
9. select customernumber, sum(quantityordered * priceeach)as sales,(case when (sum(quantityordered*priceeach)) > 100000 then 'Platinum' when (sum(quantityordered * priceeach)) between 10000 and 100000 then 'Gold' when (sum(quantityordered*priceeach))<10000 then 'silver' end) as customerGroup from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by customernumber order by customernumber;
10. select customergroup, count(customergroup)as groupcount from (select customernumber,sum(quantityordered*priceeach)as sales,(case when (sum(quantityordered*priceeach))>100000 then 'Platinum' when (sum(quantityordered*priceeach)) between 10000 and 100000 then 'Gold' when (sum(quantityordered * priceeach)) < 10000 then 'silver' end) as customerGroup from orderdetails inner join orders using(ordernumber) where year(shippeddate) = 2003 group by customernumber order by customernumber) as DT1 group by customergroup;

[vshvandana]

 select customerNumber,checkNumber,amount from payments where amount>(select avg(amount) from payments);

 select customerName from customers where customerNumber not in(select customerNumber from orders);

 select max(items),min(items),avg(items) from(select count(ordernumber)as items from orderdetails group by ordernumber) as derivedtable;

 select productname,buyprice from products where buyprice>(select avg(buyprice) from products);

 select ordernumber,sum(priceeach * quantityordered) total from orderdetails inner join orders using(ordernumber) group by ordernumber having sum(priceeach*quantityordered)>60000;

 select customernumber, customername from customers where EXISTS(select ordernumber,sum(priceeach * quantityordered) from orderdetails inner join orders using(ordernumber) where customernumber=customers.customernumber group by ordernumber having sum(priceeach*quantityordered)>60000); 

 select productcode,sum(quantityordered*priceeach)as sales from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by productcode order by sales desc limit 5;

 select productname,sales from (select productcode,sum(quantityordered*priceeach)as sales from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by productcode order by sales desc limit 5) top5product2003 join products using(productcode);

 select customernumber,sum(quantityordered*priceeach)as sales,(case when (sum(quantityordered*priceeach))>100000 then 'Platinum' when (sum(quantityordered*priceeach)) between 10000 and 100000 then 'Gold' when (sum(quantityordered*priceeach))<10000 then 'silver' end) as customerGroup from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by customernumber order by customernumber;

 select customergroup,count(customergroup)as groupcount from (select customernumber,sum(quantityordered*priceeach)as sales,(case when (sum(quantityordered*priceeach))>100000 then 'Platinum' when (sum(quantityordered*priceeach)) between 10000 and 100000 then 'Gold' when (sum(quantityordered*priceeach))<10000 then 'silver' end) as customerGroup from orderdetails inner join orders using(ordernumber) where year(shippeddate)=2003 group by customernumber order by customernumber)as derivedtable1 group by customergroup;

[meghvi-raj]

--SUBQUERIES

--Q1
select customerNumber,
    checkNumber,
    amount
from payments
where amount > (
        select avg(amount)
        from payments
    );

--Q2
select customerName
from customers
where customerNumber not in (
        select customerNumber
        from orders
    );

--Q3
SELECT MAX(items),
    MIN(items),
    FLOOR(AVG(items))
FROM (
        SELECT COUNT(orderNumber) AS items
        FROM orderdetails
        GROUP BY orderNumber
    ) AS derivedTable;

--Q4
select productName,
    buyPrice
from products
where buyPrice > (
        select avg(buyPrice)
        from products
    );

--Q5
SELECT o.orderNumber,
    SUM(od.priceEach * od.quantityOrdered) AS total
FROM orders o,
    orderdetails od
WHERE o.orderNumber = od.orderNumber
GROUP BY od.orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000;

--Q6
SELECT c.customerNumber,
    c.customerName
FROM customers c
WHERE EXISTS (
        SELECT o.orderNumber,
            SUM(od.priceEach * od.quantityOrdered) AS total
        FROM orders o,
            orderdetails od
        WHERE o.orderNumber = od.orderNumber
            AND customerNumber = c.customerNumber
        GROUP BY od.orderNumber
        HAVING SUM(priceEach * quantityOrdered) > 60000
    );

--Q7
SELECT productCode,
    FLOOR(SUM(quantityOrdered * priceEach)) AS sales
FROM orderdetails,
    orders
WHERE orderdetails.orderNumber = orders.orderNumber
    AND (
        YEAR(orders.shippedDate) = 2003
        OR YEAR(orders.requiredDate) = 2003
    )
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;

--Q8
SELECT productName,
    sales
FROM products,
    (
        SELECT productCode,
            FLOOR(SUM(quantityOrdered * priceEach)) AS sales
        FROM orderdetails,
            orders
        WHERE orderdetails.orderNumber = orders.orderNumber
            AND (
                YEAR(orders.shippedDate) = 2003
                OR YEAR(orders.requiredDate) = 2003
            )
        GROUP BY productCode
        ORDER BY sales DESC
        LIMIT 5
    ) AS top5product2003
WHERE products.productCode = top5product2003.productCode
GROUP BY top5product2003.productCode;

--Q9
SELECT customerNumber,
    FLOOR(SUM(quantityOrdered * priceEach)) AS sales,
    (
        CASE
            WHEN FLOOR(SUM(quantityOrdered * priceEach)) > 100000 THEN 'Platinum'
            WHEN FLOOR(SUM(quantityOrdered * priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
            WHEN FLOOR(SUM(quantityOrdered * priceEach)) < 10000 THEN 'Silver'
        END
    ) AS customerGroup
FROM orderdetails,
    orders
WHERE orderdetails.orderNumber = orders.orderNumber
    AND (
        YEAR(orders.shippedDate) = 2003
        OR YEAR(orders.requiredDate) = 2003
    )
GROUP BY customerNumber
ORDER BY customerNumber;

--Q10
SELECT customerGroup,
    COUNT(customerGroup) AS groupCount
FROM (
        SELECT customerNumber,
            FLOOR(SUM(quantityOrdered * priceEach)) AS sales,
            (
                CASE
                    WHEN FLOOR(SUM(quantityOrdered * priceEach)) > 100000 THEN 'Platinum'
                    WHEN FLOOR(SUM(quantityOrdered * priceEach)) BETWEEN 10000 AND 100000 THEN 'Gold'
                    WHEN FLOOR(SUM(quantityOrdered * priceEach)) < 10000 THEN 'Silver'
                END
            ) AS customerGroup
        FROM orderdetails,
            orders
        WHERE orderdetails.orderNumber = orders.orderNumber
            AND (
                YEAR(orders.shippedDate) = 2003
                OR YEAR(orders.requiredDate) = 2003
            )
        GROUP BY customerNumber
        ORDER BY customerNumber
    ) AS derivedTable
GROUP BY customerGroup;

[var5]

1. Write a query to find customers whose payments are greater than the average payment
   using a subquery.

select c.customerName,p.checkNumber,sum(p.amount) as total_amount
from customers c
join payments p
on c.customerNumber = p.customerNumber
group by c.customerName,p.checkNumber
having sum(p.amount) > (
select avg(amount)
from payments );

2. Use a subquery with NOT IN operator to find the customers who have not placed any orders.
   select c.customerName
   from customers c
   left join orders o
   on c.customerNumber=o.customerNumber
   where c.customerNumber not in
   (
   select distinct customerNumber
   from orders
   );

3. Write a subquery that finds the maximum, minimum, and average number of items in sale orders
   from order details.
   SELECT
   MAX(orderItemCount) AS maximum_items,
   MIN(orderItemCount) AS minimun_items,
   AVG(orderItemCount) AS average_items
   FROM
   (SELECT
   orderNumber,
   COUNT(*) AS orderItemCount
   FROM
   orderdetails
   GROUP BY
   orderNumber) AS orderItemsCount;

4. Unlike a standalone subquery, a correlated subquery is a subquery that uses the data from the
   outer query.
   In other words, a correlated subquery depends on the outer query. A correlated subquery is
   evaluated once for each row in the outer query.
   See the products table:
   Use a correlated subquery to select products whose buy prices are greater than the average buy
   price of all products in each product line.
   For each row in the products (or p1) table, the correlated subquery needs to execute once to get the
   average buy price of all products in the product line of that row.
   If the buy price of the current row is greater than the average buy price returned by the correlated
   subquery, the query includes the row in the result set.
   SELECT p1.productName,p1.buyPrice
   FROM products p1
   WHERE p1.buyPrice > (
   SELECT AVG(p2.buyPrice)
   FROM products p2
   WHERE p2.productLine = p1.productLine
   );

5 Write a query that finds sales orders whose total values are greater than 60K as follows:
select od.orderNumber,sum(od.quantityOrderedod.priceEach) as total
from orderdetails od
join orders o
on o.orderNumber =od.orderNumber
group by od.orderNumber
having sum(od.quantityOrderedod.priceEach)>60000;

6. When a subquery is used with the EXISTS or NOT EXISTS operator, a subquery returns a Boolean
   value of TRUE or FALSE. The following query illustrates a subquery used with the EXISTS operator:
   Use the query in question no. 5 as a correlated subquery to find customers who placed at least one
   sales order with the total value greater than 60K by using the EXISTS operator:
   SELECT DISTINCT c.customerNumber,c.customerName
   FROM customers c
   WHERE EXISTS (
   SELECT 1
   FROM orderdetails od
   JOIN orders o ON o.orderNumber = od.orderNumber
   WHERE o.customerNumber = c.customerNumber
   GROUP BY od.orderNumber
   HAVING SUM(od.quantityOrdered * od.priceEach) > 60000
   );

7. Write a query that gets the top five products by sales revenue in 2003 from the orders and order
   details tables as follows:
   select od.productCode,sum(od.quantityOrderedod.priceEach) as sales
   from orderdetails od
   join orders o
   on o.orderNumber =od.orderNumber
   where o.orderDate between '2003-01-01' and '2003-12-31'
   group by od.productCode
   order by sum(od.quantityOrderedod.priceEach) desc
   limit 5;

8. You can use the result of the previous query as a derived table called top5product2003 and join
   it with the products table using the productCode column. Then, find out the productName and sales
   of the top 5 products in 2003.
   SELECT p.productName, t.sales
   FROM products p
   JOIN (
   SELECT od.productCode, SUM(od.quantityOrdered * od.priceEach) AS
   sales
   FROM orderdetails od
   JOIN orders o ON o.orderNumber = od.orderNumber
   WHERE o.orderDate BETWEEN '2003-01-01' AND '2003-12-31'
   GROUP BY od.productCode
   ORDER BY sales DESC
   LIMIT 5
   ) AS t ON p.productCode = t.productCode;

9. Suppose you have to label the customers who bought products in 2003 into 3 groups: platinum,
   gold,
   and silver with the following conditions:
   • Platinum customers who have orders with the volume greater than 100K.
   • Gold customers who have orders with the volume between 10K and 100K.
   • Silver customers who have orders with the volume less than 10K.
   To form this query, you first need to put each customer into the respective group using CASE
   expression and GROUP BY to display the following:
   select c.customerNumber,sum(od.quantityOrderedod.priceEach) as sales,
   case
   when sum(od.quantityOrderedod.priceEach)<10000 then 'Silver'
   when (sum(od.quantityOrderedod.priceEach)>10000 and
   sum(od.quantityOrderedod.priceEach)<100000) then 'Gold'
   else 'Platinum'
   end as customerGroup
   from customers c
   join orders o
   on c.customerNumber= o.customerNumber
   join orderdetails od
   on o.orderNumber =od.orderNumber
   group by c.customerNumber;

10. Use the previous query as the derived table to know the number of customers in each group:
    platinum, gold, and silver.
    SELECT customerGroup, COUNT(customerNumber) AS groupCount
    FROM(
    select c.customerNumber,sum(od.quantityOrderedod.priceEach) as
    sales,
    case
    when sum(od.quantityOrderedod.priceEach)<10000 then 'Silver'
    when (sum(od.quantityOrderedod.priceEach)>10000 and
    sum(od.quantityOrderedod.priceEach)<100000) then 'Gold'
    else 'Platinum'
    end as customerGroup
    from customers c
    join orders o
    on c.customerNumber= o.customerNumber
    join orderdetails od
    on o.orderNumber =od.orderNumber
    group by c.customerNumber
    ) AS customerSalesGroup
    GROUP BY customerGroup;

[bijayjenafs]

-- 1.	Write a query to find customers whose payments are greater than the average payment using a subquery.

select customerNumber, checkNumber, amount from payments
where amount > (select avg(amount) from payments );

-- 2. Use a subquery with NOT IN operator to find the customers who have not placed any orders.

select customerName from customers where customerNumber not in (select customerNumber from orders);

-- 3. Write a subquery that finds the maximum, minimum, and average number of items in sale orders from orderdetails.

select MAX(quantityOrdered), MIN(quantityOrdered), FLOOR(AVG(quantityOrdered))
from orderdetails;

-- 4. 	Use a correlated subquery to select products whose buy prices are greater than the average buy price of all
-- 		products in each product line.
-- 		For each row in the products (or p1) table, the correlated subquery needs to execute once to get the average
-- 		buy price of all products in the productline of that row. If the buy price of the current row is greater than
-- 		the average buy price returned by the correlated subquery, the query includes the row in the result set.

SELECT p1.productName , p1.buyPrice 
FROM products p1
WHERE p1.buyPrice > (
    SELECT AVG(p2.buyPrice)
    FROM products p2
    WHERE p2.productline = p1.productline
);

-- 5. Write a query that finds sales orders whose total values are greater than 60K.

select orderNumber, sum(quantityOrdered * priceEach) as total
from orderdetails
group by orderNumber 
having total > 60000;

-- 8. In the query above, if the subquery returns any rows, EXISTS subquery returns TRUE, otherwise, it returns FALSE.
-- Use the query in question no. 5 as a correlated subquery to find customers who placed at least one sales order with
-- the total value greater than 60K by using the EXISTS operator

select customerNumber, customerName 
from customers
where exists (
	select orderNumber, sum(quantityOrdered * priceEach) as total
	from orderdetails
	group by orderNumber 
	having total > 60000
);

-- 9. Write a query that gets the top 5 products by sales revenue in 2003 from the orders & orderdetails tables.

select productCode, sum(quantityOrdered * priceEach) as sales
from orderdetails
where orderNumber in (
	select orderNumber from orders
	where year(orderDate) = year('2003-01-01')
)
group by productCode 
order by sales desc
limit 5;

-- 10. You can use the result of the previous query as a derived table called top5product2003 and join it with the
-- products table using the productCode column.. Then, find out the productName and sales of the top 5 products in 2003.

with top5product2003 as (
	select productCode, sum(quantityOrdered * priceEach) as sales
	from orderdetails
	where orderNumber in (
		select orderNumber from orders
		where year(orderDate) = year('2003-01-01')
	)
	group by productCode 
	order by sales desc
	limit 5)
select productName, sales
from top5product2003
join products
using (productCode);

-- 11. 	Suppose you have to label the customers who bought products in 2003 into 3 groups: platinum, gold, and silver
--	 	with the following conditions:
-- 		•	Platinum customers who have orders with the volume greater than 100K.
-- 		•	Gold customers who have orders with the volume between 10K and 100K.
-- 		•	Silver customers who have orders with the volume less than 10K.

select customerNumber, sum(quantityOrdered * priceEach) as sales,
	case
		when sum(quantityOrdered * priceEach) > 100000 then 'Platinum'
		when sum(quantityOrdered * priceEach) < 10000 then 'Silver'
		else 'Gold'
	end as customerGroup
from orderdetails
join orders
using (orderNumber)
group by customerNumber;

-- 12.	Use the previous query as the derived table to know the number of customers in each group: platinum, gold, and silver.

with customerGroups as (
	select customerNumber, sum(quantityOrdered * priceEach) as sales,
		case
			when sum(quantityOrdered * priceEach) > 100000 then 'Platinum'
			when sum(quantityOrdered * priceEach) < 10000 then 'Silver'
			else 'Gold'
		end as customerGroup
	from orderdetails
	join orders
	using (orderNumber)
	group by customerNumber
)
select customerGroup, count(customerNumber)
from customerGroups
group by customerGroup;