-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL
);
-- insert some values
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
-- fetch some values
SELECT * FROM students WHERE gender = 'F';

-- From the following tables, write a SQL query to find all the orders issued by the salesman 'Paul Adam'. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
SELECT * FROM orders  WHERE salesman_id=(SELECT salesman_id FROM salesman WHERE name='Paul Adam');

--  From the following tables, write a SQL query to find all the orders, which are generated by those salespeople, who live in the city of London.Return ord_no, purch_amt, ord_date, customer_id, salesman_id.
those salespeople, who live in the city of London.Return ord_no, purch_amt, ord_date, customer_id, salesman_id.
SELECT * FROM orders WHERE salesman_id IN(SELECT salesman_id FROM salesman WHERE city='London');

-- From the following tables, write a SQL query to find the orders generated by the salespeople who works for customers whose id is 3007. Return ord_no, purch_amt, ord_date, customer_id, salesman_id. A customer can works only with a salespeople.

SELECT * FROM orders WHERE salesman_id = (SELECT 
 DISTINCT salesman_id FROM  orders WHERE customer_id=3007);

--  From the following tables, write a SQL query to find the order values greater than the average order value of 10th October 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id
SELECT *FROM orders WHERE purch_amt>(SELECT AVG(purch_amt)from orders WHERE ord_date='10/10/2012');

-- From the following tables, write a SQL query to find all the orders generated in New York city. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
SELECT *FROM orders WHERE salesman_id In(SELECT salesman_id FROM salesman WHERE city='New York' );

-- From the following tables, write a SQL query to find the commission of the salespeople work in Paris City. Return commission.
SELECT commission from salesman WHERE salesman_id IN (SELECT salesman_id from customer WHERE city='Paris' );

-- Write a query to display the customer whose id is 2001 bellow the salesman ID of Mc Lyon.
SELECT * FROM customer WHERE customer_id = (select salesman_id -2001 FROM salesman WHERE name = 'Mc Lyon');

-- From the following tables, write a SQL query to count number of customers with grades above the average grades of New York City. Return grade and count.
SELECT grade,count(*)  FROM customer GROUP BY grade HAVING grade >(select avg(grade) FROM customer WHERE city='New York');

-- From the following tables, write a SQL query to find those salespeople who earned the maximum commission. Return ord_no, purch_amt, ord_date, and salesman_id.
SELECT ord_no,purch_amt,ord_date,salesman_id FROM orders  WHERE salesman_id IN (Select salesman_id from salesman WHERE  Commission=(SELECT MAX(Commission) FROM salesman));

-- From the following tables, write a SQL query to find the customers whose orders issued on 17th August, 2012. Return ord_no, purch_amt, ord_date, customer_id, salesman_id and cust_name.
SELECT b.*, a.cust_name FROM orders b, customer a WHERE a.customer_id=b.customer_id AND b.ord_date='2012-08-17';

-- From the following tables, write a SQL query to find the salespeople who had more than one customer. Return salesman_id and name.
 SELECT salesman_id,name FROM salesman a WHERE 1<(SELECT count(*) FROM customer WHERE salesman_id=a.salesman_id); 

-- From the following tables, write a SQL query to find those orders, which are higher than average amount of the orders. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
SELECT * FROM orders a WHERE purch_amt > (SELECT AVG(purch_amt) FROM orders b WHERE b.customer_id = a.customer_id);

-- From the following tables, write a SQL query to find those orders, which are equal or higher than average amount of the orders. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
SELECT *FROM orders a WHERE purch_amt>= (SELECT AVG(purch_amt) FROM orders b WHERE a.customer_id=b.customer_id);

-- 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 order amount for that date.

SELECT ord_date,SUM(purch_amt) FROM orders a GROUP BY ord_date having SUM (purch_amt)>(SELECT 1000.0+MAX(purch_amt) FROM orders b WHERE a.ord_date=b.ord_date);

--- Write a query to extract all data from the customer table if and only if one or more of the customers in the customer table are located in London.


SELECT customer_id,cust_name,city FROM customer WHERE EXISTS (SELECT* FROM customer WHERE city='London') ;

-- From the following tables, write a SQL query to find the salespeople who deal multiple customers. Return salesman_id, name, city and commission.SELECT * FROM salesman WHERE salesman_id IN(S
SELECT DISTICT salesman_id FROM  customer a WHERE EXISTS (SELECT *FROm customer b WHERE b.salesman_id=a,salesman_id) AND a.cust_name<> b.cust_name));

--From the following tables write a SQL query to find the salespeople who deal a single customer. Return salesman_id, name, city and commission.
SELECT * 
FROM salesman 
WHERE salesman_id IN (
   SELECT DISTINCT salesman_id 
   FROM customer a 
   WHERE NOT EXISTS (
      SELECT * FROM customer b 
      WHERE a.salesman_id=b.salesman_id 
      AND a.cust_name<>b.cust_name));
      
-- From the following tables write a SQL query to find the salespeople who deal a single customer. Return salesman_id, name, city and commission.


SELECT * FROM salesman a WHERE EXISTS (SELECT * FROM customer b WHERE a.salesman_id=b.salesman_id AND 1<(SELECT count(*) FROM orders where orders.customer_id=b.customer_id));

-- From the following tables write a SQL query to find the salespeople who deals those customers who live in the same city. Return salesman_id, name, city and commission.
SELECT * FROM salesman WHERE city= ANY(SELECT city FROM customer);
-- SELECT * FROM salesman WHERE city= ANY(SELECT city FROM customer);
SELECT * FROM salesman WHERE city IN(SELECT city FROM customer);

--  From the following tables, write a SQL query to find all those salespeople whose name exist alphabetically after the customer’s name. Return salesman_id, name, city, commission.
SELECT * FROM salesman a WHERE EXISTS (SELECT * FROM customer b WHERE a.name<b.cust_name);

-- write a SQL query to find all those customers who have a greater grade than any customer who belongs to the alphabetically lower than the city of New York. Return customer_id, cust_name, city, grade, salesman_id.
SELECT * FROM customer WHERE grade>any(SELECT grade FROM customer WHERE city<'New York');

-- From the following table, write a SQL query to find all those orders whose order amount greater than at least one of the orders of September 10th 2012. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
SELECT *  FROM orders WHERE purch_amt>ANY(SELECT purch_amt FROM orders WHERE ord_date='2012/09/10');

-- From the following tables, write a SQL query to find those orders where an order amount less than any order amount of a customer lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.

SELECT * FROM orders WHERE purch_amt<ANY(SELECT purch_amt FROM orders a,customer b WHERE a.customer_id=b.customer_id AND b.city='London');

-- From the following tables, write a SQL query to find those orders where every order amount less than the maximum order amount of a customer lives in London City. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
SELECT *
FROM orders
WHERE purch_amt < 
   (SELECT MAX (purch_amt)
	FROM orders a, customer b
	WHERE  a.customer_id=b.customer_id
	AND b.city='London');

-- From the following tables, write a SQL query to find those customers whose grade are higher than customers living in New York City. Return customer_id, cust_name, city, grade and salesman_id.
SELECT * FROM customer WHERE grade>ALL(SELECT grade FROM customer WHERE city='New York');

-- From the following tables, write a SQL query to calculate the total order amount generated by a salesman. The salesman should belong to the cities where any of the customer living. Return salesman name, city and total order amount
SELECT salesman.name, salesman.city, subquery1.total_amt FROM 
salesman, (SELECT salesman_id, SUM(orders.purch_amt) AS total_amt 
FROM orders GROUP BY salesman_id) subquery1 WHERE subquery1.salesman_id = salesman.salesman_id AND
salesman.city IN (SELECT DISTINCT city FROM customer);

-- From the following tables, write a SQL query to find those customers whose grade doesn't same of those customers live in London City. Return customer_id, cust_name, city, grade and salesman_id.

SELECT * FROM customer WHERE grade <>ANY(SELECT grade from customer where city='London' AND NOT grade IS NULL);

-- From the following tables, write a SQL query to find those customers whose grade are not same of those customers living in Paris. Return customer_id, cust_name, city, grade and salesman_id.
SELECT * from customer WHERE grade NOT IN (SELECT grade FROM customer WHERE city='Paris');

-- From the following tables, write a SQL query to find all those customers who have different grade than any customer lives in Dallas City. Return customer_id, cust_name,city, grade and salesman_id.
SELECT * FROM customer WHERE NOT grade = ANY(SELECT grade from customer where city='Dallas');

-- From the following tables, write a SQL query to find the average price of each manufacturer's product along with their name. Return Average Price and Company
SELECT AVG(pro_price) AS "Average Price", 
   company_mast.com_name As "Company"
   FROM item_mast, company_mast
        WHERE item_mast.pro_com= company_mast.com_id
           GROUP BY company_mast.com_name;
-- From the following tables, write a SQL query to calculate the average price of the products and find price which are more than or equal to 350. Return Average Price and Company.
SELECT AVG(pro_price) AS "Average Price", 
   company_mast.com_name AS "Company"
      FROM item_mast, company_mast
         WHERE item_mast.pro_com= company_mast.com_id
           GROUP BY company_mast.com_name
   HAVING AVG(pro_price) >= 350;

-- From the following tables, write a SQL query to find the most expensive product of each company. Return Product Name, Price and Company.
SELECT P.pro_name AS "Product Name", 
       P.pro_price AS "Price", 
       C.com_name AS "Company"
   FROM item_mast P, company_mast C
   WHERE P.pro_com = C.com_id
     AND P.pro_price =
     (
       SELECT MAX(P.pro_price)
         FROM item_mast P
         WHERE P.pro_com = C.com_id
     );

-- From the following tables, write a SQL query to find those employees whose last name is ‘Gabriel’ or ‘Dosio’. Return emp_idno, emp_fname, emp_lname and emp_dept.
SELECT * FROM emp_details WHERE emp_lname IN('Gabriel,'Dosio');

-- From the following tables, write a SQL query to find the employees who work in department 89 or 63. Return emp_idno, emp_fname, emp_lname and emp_dept.
SELECT * FROM emp_details WHERE emp_dept IN (89,63);

-- From the following tables, write a SQL query to find those employees who work for the department where the department allotment amount is more than Rs. 50000. Return emp_fname and emp_lname.
SELECT emp_fname,emp_lname FROM emp_details WHERE emp_dept IN (SELECT dpt_code FROM emp_details WHERE dpt_allotmenr>50000);

--  From the following tables, write a SQL query to find the departments where the sanction amount is higher than the average sanction amount of all the departments. Return dpt_code, dpt_name and dpt_allotment.
SELECT * FROM emp_department WHERE dpt_allotment>(SELECT AVG(dpt_allotment) FROM emp_department);

-- From the following tables, write a SQL query to find the departments where more than two employees work. Return dpt_name.
SELECT dpt_name FROM emp_department WHERE dpt_code IN (SELECT emp_dept FROM emp_details GROUP BY emp_dept HAVING count (*)>2);

-- From the following tables, write a SQL query to find the departments where the sanction amount is second lowest. Return emp_fname and emp_lname.
SELECT emp_fname, emp_lname 
FROM emp_details 
WHERE emp_dept IN (
  SELECT dpt_code
  FROM emp_department 
  WHERE dpt_allotment= (
    SELECT MIN(dpt_allotment)
    FROM emp_department 
    WHERE dpt_allotment >
 (SELECT MIN(dpt_allotment) 
      FROM emp_department )));