-- 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 the salesperson and customer who belongs to same city. Return Salesman, cust_name and city.
SELECT salesman.name AS "Salesman", customer.cust_name, customer.city FROM salesman,customer WHERE salesman.city=customer.city;


-- From the following tables write a SQL query to find those orders where order amount exists between 500 and 2000. Return ord_no, purch_amt, cust_name, city.
 SELECT a.ord_no,a.purch_amt,b.cust_name,b.city
 FROM orders a,customer b
 WHERE a.customer_id=b.customer_id
 AND a.purch_amt BETWEEN 500 AND 2000;
 
 -- From the following tables write a SQL query to find the salesperson(s) and the customer(s) he handle. Return Customer Name, city, Salesman, commission.
 SELECT a.cust_name as "Customer Name",a.city,b.name as "salesman",b.commission 
 FROM customer a INNER JOIN salesman b ON a.salesman_id=b.salesman_id;
 
 -- From the following tables write a SQL query to find those salespersons who received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, commission.
 SELECT a.cust_name as "Customer name",a.city,b.name as "salesman",b.commission
 FROM customer a INNER JOIN salesman B ON a.salesman_id=b.salesman_id
 WHERE b.commission >0.12;
 
 -- From the following tables write a SQL query to find those salespersons do not live in the same city where their customers live and received a commission from the company more than 12%. Return Customer Name, customer city, Salesman, salesman city, commission.
 SELECT a.cust_name as "Customer Name",a.city,b.name AS "salesman",b.city,b.commission
 FROM customer a INNER JOIN salesman b ON a.salesman_id=b.salesman_id
 WHERE b.commission>0.12 AND a.city <> b.city;
 
 -- From the following tables write a SQL query to find the details of an order. Return ord_no, ord_date, purch_amt, Customer Name, grade, Salesman, commission.
 SELECT a.ord_no,a.ord_date,a.purch_amt,b.cust_name as "Customer Name",b.grade,c.name as "Salesman",c.commission 
 FROM orders a INNER JOIN Customer b ON a.customer_id=b.customer_id INNER JOIN salesman c ON a.salesman_id=c.salesman_id;

-- Write a SQL statement to make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come.
SELECT * FROM orders NATURAL JOIN customer NATURAL JOIN salesman;

-- From the following tables write a SQL query to display the cust_name, customer city, grade, Salesman, salesman city. The result should be ordered by ascending on customer_id.
SELECT a.cust_name,a.city,a.grade,b.name as "salesman",b.cityFROM customer a LEFT JOIN salesman b ON a.salesman_id=b.salesman_id
order by a.customer_id;

-- From the following tables write a SQL query to find those customers whose grade less than 300. Return cust_name, customer city, grade, Salesman, saleman city. The result should be ordered by ascending customer_id.
SELECT a.cust_name,a.city,a.grade, 
b.name AS "Salesman", b.city 
FROM customer a 
LEFT OUTER JOIN salesman b 
ON a.salesman_id=b.salesman_id 
WHERE a.grade<300 
ORDER BY a.customer_id;

-- Write a SQL statement to make a report with customer name, city, order number, order date, and order amount in ascending order according to the order date to find that either any of the existing customers have placed no order or placed one or more orders.
SELECT a.cust_name,a.city, b.ord_no,
b.ord_date,b.purch_amt AS "Order Amount" 
FROM customer a 
LEFT OUTER JOIN orders b 
ON a.customer_id=b.customer_id 
order by b.ord_date;

-- Write a SQL statement to make a report with customer name, city, order number, order date, order amount salesman name and commission to find that either any of the existing customers have placed no order or placed one or more orders by their salesman or by own.
SELECT a.cust_name,a.city, b.ord_no, b.ord_date,b.purch_amt AS "Order Amount", c.name,c.commission FROM customer a LEFT OUTER JOIN orders b ON a.customer_id=b.customer_id 
LEFT OUTER JOIN salesman c ON c.salesman_id=b.salesman_id;

-- Write a SQL statement to make a list in ascending order for the salesmen who works either for one or more customer or not yet join under any of the customers.
SELECT a.cust_name,a.city,a.grade, b.name AS "Salesman", b.city FROM customer a
RIGHT OUTER JOIN salesman b ON b.salesman_id=a.salesman_id 
ORDER BY b.salesman_id;

-- salesmen who works either for one or more customer or not yet join under any of the customers who placed either one or more orders or no order to their supplier
SELECT a.cust_name,a.city,a.grade, 
b.name AS "Salesman", 
c.ord_no, c.ord_date, c.purch_amt 
FROM customer a 
RIGHT OUTER JOIN salesman b 
ON b.salesman_id=a.salesman_id 
RIGHT OUTER JOIN orders c 
ON c.customer_id=a.customer_id;


--  Prepare a list for the salesmen who either work for one or more customers or yet to join any of the customers. The customer, may have placed, either one or more orders on or above order amount 2000 and must have a grade, or he may not have placed any order to the associated supplier
SELECT a.cust_name,a.city,a.grade, 
b.name AS "Salesman", 
c.ord_no, c.ord_date, c.purch_amt 
FROM customer a 
RIGHT OUTER JOIN salesman b 
ON b.salesman_id=a.salesman_id 
LEFT OUTER JOIN orders c 
ON c.customer_id=a.customer_id 
WHERE c.purch_amt>=2000 
AND a.grade IS NOT NULL;

-- Write a SQL statement to make a report with customer name, city, order no., order date, purchase amount for those customers from the existing list who placed one or more orders or which order(s) have been placed by the customer who is not on the list.
SELECT a.cust_name,a.city,b.ord_no,b.ord_date,b.purch_amt AS "Order Amount"
FROM customer a LEFT OUTER JOIN orders b 
ON a.customer_id=b.customer_id;

-- Write a SQL statement to make a report with customer name, city, order no. order date, purchase amount for only those customers on the list who must have a grade and placed one or more orders or which order(s) have been placed by the customer who is neither in the list nor have a grade.
SELECT a.cust_name,a.city,b.ord_no,
b.ord_date,b.purch_amt AS "Order Amount" 
FROM customer a 
FULL OUTER JOIN orders b 
ON a.customer_id=b.customer_id 
WHERE a.grade IS NOT NULL;

-- Write a SQL query to combine each row of salesman table with each row of customer table.
SELECT * FROM salesman a CROSS JOIN customer b;

-- Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for that salesman who belongs to a city.
SELECT * FROM salesman a CROSS JOIN customer b;
WHERE a.city IS NOT NULL;

-- Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those salesmen who belongs to a city and the customers who must have a grade.
SELECT * FROM salesman a CROSS JOIN customer b
WHERE a.city IS NOT NULL AND b.grade IS NOT NULL;

-- Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those salesmen who must belong a city which is not the same as his customer and the customers should have an own grade.

SELECT * FROM salesman a CROSS JOIN customer b
WHERE a.city IS NOT NULL AND b.grade IS NOT NULL 
AND a.city<>b.city;

-- From the following tables write a SQL query to select all rows from both participating tables as long as there is a match between pro_com and com_id.
SELECT *
   FROM item_mast 
   INNER JOIN company_mast
   ON item_mast.pro_com= company_mast.com_id;

-- Write a SQL query to display the item name, price, and company name of all the products.
SELECT item_mast.pro_name, pro_price, company_mast.com_name
FROM item_mast 
INNER JOIN company_mast
ON item_mast.pro_com = company_mast.com_id;

-- From the following tables write a SQL query to calculate the average price of items of each company. Return average value and company name.
SELECT AVG(pro_price), company_mast.com_name
   FROM item_mast INNER 
     JOIN company_mast
        ON item_mast.pro_com= company_mast.com_id
           GROUP BY company_mast.com_name; 
-- From the following tables write a SQL query to calculate and find the average price of items of each company higher than or equal to Rs. 350. Return average value and company name.
 SELECT AVG(pro_price), company_mast.com_name
   FROM item_mast INNER JOIN company_mast
   ON item_mast.pro_com= company_mast.com_id
   GROUP BY company_mast.com_name
   HAVING AVG(pro_price) >= 350;
   
-- write a SQL query to find the most expensive product of each company. Return pro_name, pro_price and com_name
SELECT A.pro_name, A.pro_price, F.com_name
   FROM item_mast A INNER JOIN company_mast F
   ON A.pro_com = F.com_id
     AND A.pro_price =
     (
       SELECT MAX(A.pro_price)
         FROM item_mast A
         WHERE A.pro_com = F.com_id
     );
     
-- write a SQL query to display all the data of employees including their department.
SELECT emp_idno, A.emp_fname AS "First Name", emp_lname AS "Last Name",
    B.dpt_name AS "Department", emp_dept, dpt_code,  dpt_allotment
     FROM emp_details A 
      INNER JOIN emp_department B
        ON A.emp_dept = B.dpt_code;
-- write a SQL to display the first name and last name of each employee, along with the name and sanction amount for their department.

SELECT emp_details.emp_fname as "First Name",emp_lname as "Last Name",
emp_department.dpt_name as "Department",dpt_allotment as "Amount Alloted"
FROM emp_details INNER JOIN emp_department ON emp_details.emp_dept=emp_department.dpt_code;

-- write a SQL query to find the departments with a budget more than Rs. 50000 and display the first name and last name of employees.
SELECT emp_details.emp_fname AS "First Name", emp_lname AS "Last Name" FROM emp_details INNER JOIN emp_department ON emp_details.emp_dept = emp_department.dpt_code
    AND emp_department.dpt_allotment > 50000;

-- write a SQL query to find the names of departments where more than two employees are working. Return dpt_name.

SELECT emp_department.dpt_name FROM emp_details INNER JOIN emp_department ON emp_dept=dpt_code GROUP BY emp_department.dpt_name
HAVING COUNT(*)> 2;