SubQuery (extra-1)

1.WRITE a query TO find the name AND numbers OF ALL salesmen who had more than one customer.

SELECT name,salesman_id FROM salesman WHERE salesman_id IN
(SELECT salesman_id FROM Customer GROUP BY salesman_id HAVING COUNT(salesman_id)>1)


SELECT * FROM Customer

2.WRITE a query TO counts the customers WITH grades above NEW York''s average

SELECT COUNT(customer_id) FROM Customer WHERE
grade >(SELECT avg(grade) FROM Customer WHERE city= 'New York')


3.WRITE a query TO display ALL the orders which VALUES are greater than
the average ORDER VALUE FOR 10th October 2012

SELECT ord_no FROM Orders WHERE purch_amt>
(SELECT avg(purch_amt) FROM Orders WHERE ord_date ='2012-10-10')


4.WRITE a query TO find ALL orders attributed TO a salesman IN NEW york

SELECT ord_no FROM Orders WHERE salesman_id =
(SELECT salesman_id FROM salesman WHERE city = 'New York' )


5.WRITE a query TO find the sums OF the amounts FROM the orders TABLE, grouped BY DATE,
  eliminating ALL those dates WHERE the SUM was NOT at least 1000.00 above the maximum amount
  FOR that DATE.


SELECT SUM(purch_amt) AS Total FROM Orders
GROUP BY ord_date HAVING ord_date IN
(SELECT ord_date FROM Orders GROUP BY ord_date
HAVING SUM(purch_amt)-1000 > MAX(purch_amt))


---------------------------------------------------------------------

 SELECT ord_date , purch_amt FROM Orders

 SELECT ord_date,SUM(purch_amt) AS total FROM Orders GROUP BY ord_date

  SELECT ord_date,MAX(purch_amt) AS maximum FROM Orders GROUP BY ord_date

 (SELECT ord_date FROM Orders GROUP BY ord_date HAVING SUM(purch_amt) >