1.

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.
Sample table: Salesman, Orders
SELECT ord_no, purch_amt, ord_date, customer_id,salesman_id
FROM ORDERS
WHERE salesman_id= (SELECT salesman_id FROM SALESMAN WHERE NAME='Paul Adam');

2. From the following tables write a SQL query to find all orders generated by
London-based salespeople. 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');

3. From the following tables write a SQL query to find all orders generated by the
salespeople who may work for customers whose id is 3007. Return ord_no, purch_amt,
ord_date, customer_id, salesman_id.
SELECT * FROM orders
WHERE salesman_id IN
(SELECT DISTINCT salesman_id
FROM orders
WHERE customer_id = 3007);

4. 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 ord_no, purch_amt, ord_date, customer_id, salesman_id
FROM ORDERS
WHERE purch_amt> (SELECT AVG(purch_amt) FROM ORDERS WHERE ORD_DATE=TO_DATE('10-10-
2012','DD-MM-YYYY'));

5. 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 ord_no, purch_amt, ord_date, customer_id, salesman_id
FROM ORDERS
WHERE SALESMAN_ID IN (SELECT SALEsMAN_ID FROM salesman WHERE CITY='New York');

6. From the following tables write a SQL query to determine the commission of the
salespeople in Paris. Return commission.
select * from salesman
where city='Paris';-notmatch

7. Write a query to display all the customers whose ID is 2001 below the
salesperson ID of Mc Lyon.
select customer_id,cust_name from customer
where customer_id=(select salesman_id-2001 from salesman where name='Mc Lyon');

8. From the following tables write a SQL query to count the number of customers
with grades above the average in New York City. Return grade and count.

select grade,count(customer_id) from customer

where grade> (select avg(grade) from customer where city='New York')
group by grade;

9. 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 * from orders
where salesman_id in (select salesman_id from salesman where commission=(select
max(commission) from salesman));
10. From the following tables write SQL query to find the customers who placed
orders on 17th August 2012. Return ord_no, purch_amt, ord_date, customer_id,
salesman_id and cust_name.

select O.*,cust_name from orders O,customer C

where o.customer_id=c.customer_id
and ord_date='17-AUG-2012';

11. From the following tables write a SQL query to find salespeople who had more
than one customer. Return salesman_id and name.

elect salesman_id, name from salesman

where salesman_id in ( select salesman_id from customer
group by salesman_id
having count(*)>1);

SELECT salesman_id, name

FROM salesman a
WHERE 1 <
(SELECT COUNT(*)
FROM customer
WHERE salesman_id = a.salesman_id);

12. From the following tables write a SQL query to find those orders, which are
higher than the average amount of the orders. Return ord_no, purch_amt, ord_date,
customer_id and salesman_id.
select * from orders
where purch_amt>(select avg(purch_amt) from orders);

SELECT *
FROM orders a
-- Filtering the results based on the condition that 'purch_amt' is greater than
the average 'purch_amt' for the same 'customer_id'
WHERE purch_amt >
-- Subquery: Calculating the average 'purch_amt' from the 'orders' table
(aliased as 'b') for the same 'customer_id'
(SELECT AVG(purch_amt) FROM orders b
WHERE b.customer_id = a.customer_id);

**12.2)From the following tables write a SQL query to find those orders, which are
higher than the average amount of the orders per customer

13. From the following tables write a SQL query to find those orders that are equal
or higher than the average amount of the orders. Return ord_no, purch_amt,
ord_date, customer_id and salesman_id.
select * from orders
where purch_amt>=(select avg(purch_amt) from orders);

SELECT *
FROM orders a
-- Filtering the results based on the condition that 'purch_amt' is greater than
the average 'purch_amt' for the same 'customer_id'
WHERE purch_amt >=
-- Subquery: Calculating the average 'purch_amt' from the 'orders' table
(aliased as 'b') for the same 'customer_id'
(SELECT AVG(purch_amt) FROM orders b
WHERE b.customer_id = a.customer_id);
14. Write a query to find the sums of the amounts from the orders table, grouped by
date, and eliminate all 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
group by ord_date
having sum(purch_amt) >(select max(purch_amt)+1000 from orders);

SELECT ord_date, SUM(purch_amt)

FROM orders a
GROUP BY ord_date
-- Filtering the grouped results based on the condition that the sum of 'purch_amt'
is greater than a calculated threshold
HAVING SUM(purch_amt) >
-- Subquery: Calculating the threshold as 1000.00 plus the maximum 'purch_amt'
for the same 'ord_date'
(SELECT 1000.00 + MAX(purch_amt)
FROM orders b
WHERE a.ord_date = b.ord_date);

15. 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 * from customer
where 1<= (select count(*) from customer where city='London');

SELECT * FROM customer

-- Filtering the results based on the existence of records in a subquery
WHERE EXISTS
-- Subquery: Selecting any record from the 'customer' table where 'city' is
'London'
(SELECT *
FROM customer
WHERE city='London');

16. From the following tables write a SQL query to find salespeople who deal with
multiple customers. Return salesman_id, name, city and commission.
SELECT salesman_id, name, city, commission
FROM Salesman
WHERE salesman_id IN (
SELECT salesman_id
FROM Customer
GROUP BY salesman_id
HAVING COUNT(DISTINCT customer_id) > 1
);
17. From the following tables write a SQL query to find salespeople who deal with a
single customer. Return salesman_id, name, city and commission.
SELECT salesman_id, name, city, commission
FROM Salesman
WHERE salesman_id IN (
SELECT salesman_id
FROM Customer
GROUP BY salesman_id
HAVING COUNT(DISTINCT customer_id) = 1
);
18. From the following tables write a SQL query to find the salespeople who deal
the customers with more than one order. Return salesman_id, name, city and
commission.
SELECT salesman_id, name, city, commission
FROM Salesman
WHERE salesman_id IN (
SELECT salesman_id
FROM Customer
where customer_id in
(select customer_id from orders
group by customer_id
having count(*) >1));
19. From the following tables write a SQL query to find the salespeople who deal
with those customers who live in the same city. Return salesman_id, name, city and
commission.
select salesman_id, name, city,commission from salesman a
where city in (select city from customer b where a.salesman_id=b.salesman_id);

select salesman_id, name, city,commission from salesman a

where city in (select city from customer);
20. From the following tables write a SQL query to find salespeople whose place of
residence matches any city where customers live. Return salesman_id, name, city and
commission.

select salesman_id, name, city, commission from salesman

where city in (select city from customer);

21. From the following tables write a SQL query to find all those salespeople whose
names appear alphabetically lower than 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);

22. From the following table write a SQL query to find all those customers with a
higher grade than any customers alphabetically below the city of New York. Return
customer_id, cust_name, city, grade, salesman_id.

select * from customer

where grade >any(select grade from customer and city<'New York');

23. From the following table write a SQL query to find all those orders whose order
amount exceeds at least one of the orders placed on 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='10-SEP-2012');

24. From the following tables write a SQL query to find orders where the order
amount is less than the order amount of a customer residing in London City. Return
ord_no, purch_amt, ord_date, customer_id and salesman_id.

SELECT * FROM ORDERS a

WHERE purch_amt<any(SELECT purch_amt FROM CUSTOMER b where
a.customer_id=b.customer_id and b.city='London');