DBMS Prac4

SELECT * FROM OFFICES
SELECT * FROM SALESREPS
SELECT * FROM CUSTOMERS
SELECT * FROM ORDERS
SELECT * FROM PRODUCTS

-- 1.List the offices where the sales target for the office exceeds the sum of the individual salespeoples quotas.
SELECT OFFICE FROM OFFICES AS o WHERE TARG > (SELECT SUM(QUOTA) FROM SALESREPS WHERE SALESREPS.REP_OFFICE = o.OFFICE)

-- 2.   List the salespeople whose quotas are equal to or higher than the target of the Mumbai office. CHECK IT
SELECT ENAME FROM SALESREPS WHERE QUOTA >= any (SELECT TARG FROM OFFICES WHERE CITY = 'MUM')

-- 3.   List all customers served by Johns(Salesrep name)
SELECT COMPANY FROM CUSTOMERS WHERE CUST_REP = (SELECT EMPL_NUM FROM SALESREPS WHERE ENAME = 'TOM')

-- 4.   List all products from manufacturer ACI where the quantity on hand is above the quantity on hand of product ACI-04
SELECT * FROM PRODUCTS
    WHERE MRF_ID = '11' AND QTY_ON_HAND > (SELECT QTY_ON_HAND FROM PRODUCTS WHERE MRF_ID = 11 AND PRODUCT_ID = 11)

-- 5.   List the salespeople who work in offices that are over target.
SELECT ENAME FROM SALESREPS WHERE REP_OFFICE = (SELECT OFFICE FROM OFFICES WHERE TARG < SALES)

-- 6.   List the salespeople who do not work in office managed by Gom WRONG
SELECT ENAME FROM SALESREPS WHERE REP_OFFICE != (SELECT REP_OFFICE FROM SALESREPS WHERE ENAME = 'GOM')

-- 7.   List all the customers who have placed orders for ACI widgets
--      (manufacturer ACI and product numbers starting with 4100) between first quarter of 2020.
SELECT COMPANY FROM CUSTOMERS WHERE CUST_NUM IN (SELECT CUST FROM ORDERS WHERE MFR = 11 AND PRODUCT > 10)

-- 8.   List the product for which an order of 25000 or more has been received. WRONG. DO NOT COPY THIS. IT is WRONG
SELECT * FROM PRODUCTS, (SELECT MFR, PRODUCT FROM ORDERS WHERE AMOUNT > 10) AS o
    WHERE MRF_ID = o.MFR AND PRODUCT_ID = o.PRODUCT

-- 9.   List the names and ages of salespeople who work in offices in the western region.
SELECT ENAME, AGE FROM SALESREPS WHERE REP_OFFICE IN (SELECT OFFICE FROM OFFICES WHERE REGION = 'MAHA')

-- 10.  List the names and ages of salespeople who have above average quotas
SELECT ENAME, AGE FROM SALESREPS WHERE QUOTA > (SELECT avg(QUOTA) FROM SALESREPS)

-- 11.  List the customers whose salespeople are assigned to office in the eastern sales region
SELECT COMPANY FROM CUSTOMERS
    WHERE CUST_REP IN (SELECT EMPL_NUM FROM SALESREPS
        WHERE REP_OFFICE IN (SELECT OFFICE FROM OFFICES WHERE REGION = 'MAHA'))

-- 12.  List the salespeople who are over 40 and who manage a salesperson over quota.
SELECT * FROM SALESREPS WHERE AGE > 40 AND EMPL_NUM IN (SELECT MANAGER FROM SALESREPS WHERE SALES > QUOTA)

-- 13.  List the salespeople whose average order size for products manufactured by 11 is at
--  least as big as that salespeople overall average order size.
SELECT * FROM SALESREPS AS s
    WHERE EMPL_NUM IN (SELECT REP FROM ORDERS AS o
        GROUP BY REP HAVING avg(AMOUNT) >= (SELECT avg(AMOUNT) FROM ORDERS WHERE MFR = 11 AND REP = o.REP))

-- 14. Find the name of the youngest employee
SELECT ENAME FROM SALESREPS WHERE AGE = (SELECT MIN(AGE) FROM SALESREPS)

-- 15.  Find the name of the best performer in the month of July 2020.
SELECT * FROM SALESREPS
    WHERE EMPL_NUM = (SELECT REP FROM ORDERS AS o
        WHERE MONTH(ORDER_DATE) = 6 GROUP BY REP HAVING SUM(AMOUNT) =
            (SELECT MAX(y.SUM) FROM (SELECT SUM(AMOUNT) 'sum' FROM ORDERS
                WHERE MONTH(ORDER_DATE) = 6 GROUP BY REP) AS y))