Notes

Lab Sheet 7

Ques1. Consider a table

a) List the number of customers in each country. Only include countries with more than 10 customers.
b) List the number of customers in each country, except the USA, sorted high to low.
Only include countries with 9 or more customers.

Ques 2. Consider below two relations –
Employees (Id, Name, Department, Salary)
Order_Details(OrderId, Department, Sales)
a) List the departments with total sales greater than $1000.
b) List the departments with more than 10 employees whose salary > $25000 per year.
c) List those departments where the minimum salary is greater than $35,000.
d) List those departments whose maximum salary is less than $50,000.

Ques3. Consider below tables –
Customer(Customer_id, Customer_name, City, Grade, Salesman_id)
Salesman(Salesman_id, Name, City, Commission)
Orders(Ord_no, purch_amt, ord_date, customer_id, salesman_id)
a) Write a query to find those customers with their name and those salesmen with their name and city who lives in the same city.
b) Write a SQL statement to find the names of all customers along with the salesmen who works for them.
c) Write a SQL statement to display all those orders by the customers not located in the same cities where their salesmen live.
d) Write a SQL statement that finds out each order number followed by the name of the customers who made the order.
e) Write a SQL statement that sorts out the customer and their grade who made an order. Each of the customers must have a grade and served by at least a salesman, who belongs to a city.
f) Write a query that produces all customers with their name, city, salesman and commission, who served by a salesman and the salesman works at a rate of the commission within 12% to 14%.
g) Write a SQL statement that produces all orders with the order number, customer name, commission rate and earned commission amount for those customers who carry their grade more than 200 and served by an existing salesman.