-- 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 salespersons and customer who located in 'London' city.
SELECT salesman_id "ID",name,'salesman' FROM salesman WHERE CITY='London' UNION (SELECT customer_id "ID",cust_name,'customer' FROM customer WHERE city='London');

-- From the following tables, write a SQL query to find distinct salesperson and their cities. Return salesperson ID and city.
SELECT salesman_id,city FROM customer UNION (SELECT salesman_id,city from salesman);
-- From the following tables, write a SQL query to find all those salespersons and customers who involved in inventory management system. Return salesperson ID, customer ID.
SELECT Salesman_id,customer_id FROM customer UNION (SELECT salesman_id,customer_id FROM orders);

-- From the following table, write a SQL query to find those salespersons generated the largest and smallest orders on each date. Return salesperson ID, name, order no., highest on/ lowest on, order date.
SELECT a.salesman_id, name, ord_no, 'highest on', ord_date FROM salesman a, orders b WHERE a.salesman_id =b.salesman_id AND b.purch_amt= (SELECT MAX (purch_amt) FROM orders c WHERE c.ord_date = b.ord_date) UNION (SELECT a.salesman_id, name, ord_no, 'lowest on', ord_date FROM salesman a, orders b WHERE a.salesman_id =b.salesman_id AND b.purch_amt= (SELECT MIN (purch_amt) FROM orders c WHERE c.ord_date = b.ord_date))

-- From the following tables, write a SQL query to find those salespersons who generated the largest and smallest orders on each date. Sort the result-set on 3rd field. Return salesperson ID, name, order no., highest on/lowest on, order date.
SELECT a.salesman_id, name, ord_no, 'highest on', ord_date
FROM salesman a, orders b
WHERE a.salesman_id =b.salesman_id
AND b.purch_amt=
	(SELECT MAX (purch_amt)
	FROM orders c
	WHERE c.ord_date = b.ord_date)
UNION
(SELECT a.salesman_id, name, ord_no, 'lowest on', ord_date
FROM salesman a, orders b
WHERE a.salesman_id =b.salesman_id
AND b.purch_amt=
	(SELECT MIN (purch_amt)
	FROM orders c
	WHERE c.ord_date = b.ord_date))
ORDER BY 3

-- From the following tables, write a SQL query to find those salespersons who have same cities where customer lives as well as do not have customers in their cities and indicate it by ‘NO MATCH’. Sort the result set on 2nd column (i.e. name) in descending order. Return salesperson ID, name, customer name, commission.
SELECT salesman.salesman_id, name, cust_name, commission
FROM salesman, customer
WHERE salesman.city = customer.city
UNION
(SELECT salesman_id, name, 'NO MATCH', commission
FROM salesman
WHERE NOT city = ANY
	(SELECT city
        FROM customer))
ORDER BY 2 DESC

-- From the following tables, write a SQL query that appends strings to the selected fields, indicating whether a specified city of any salesperson was matched to the city of any customer. Return salesperson ID, name, city, MATCHED/NO MATCH.
SELECT a.salesman_id, name, a.city, 'MATCHED'
FROM salesman a, customer b
WHERE a.city = b.city
UNION
(SELECT salesman_id, name, city, 'NO MATCH'
FROM salesman
WHERE NOT city = ANY
	(SELECT city
        FROM customer))
ORDER BY 2 DESC

-- From the following table, write a SQL query to create a union of two queries that shows the customer id, cities, and ratings of all customers. Those with a rating of 300 or greater will have the words 'High Rating', while the others will have the words 'Low Rating'.
SELECT customer_id, city, grade, 'High Rating'
FROM customer
WHERE grade >= 300
UNION
(SELECT customer_id, city, grade, 'Low Rating'
FROM customer
WHERE grade < 300)

--  From the following table, write a SQL query to find those salesperson and customer where more than one order executed. Sort the result-set on 2nd field. Return ID, name.
SELECT customer_id as “ID”,  cust_name as “NAME”
	FROM customer a
	WHERE 1<
		(SELECT COUNT (*)
		   FROM orders b
		   WHERE a.customer_id = b.customer_id)
UNION
(SELECT salesman_idas “ID”,   nameas “NAME”
	FROM salesman a
	WHERE 1 <
		(SELECT COUNT (*)
		 FROM orders b
		WHERE  a.salesman_id = b.salesman_id))
ORDER BY 2