Design any two queries that apply inner join and left join depending on your relational schema.
Customers (id, name, shops_id)
Products (id, name, price, manufacturing_company)
Orders (id, customers_id, products_id, total_bill)
Employees (id, name, designation, current_salary)
Salaries (id, employees_id, salary, month)
Shops (id, name)
Branches(id, shops_id, branch_name, address, city)
Consider the above designed
questions:
• Write SQL query to create 2 instances of any table designed.
INSERT INTO Shops(name) VALUES("Mirpur Medical Store"),("F3 Medical Store");
• Name of customers along with their Ids who have placed order of amount more than 10000 to the
company.
SELECT distinct c.id,c.name FROM Customers AS c JOIN Orders AS o ON c.id = o.customers_id WHERE
o.total_bill > 10000;
• Give managers the 5% increase in salary.
UPDATE Employees SET current_salary = (current_salary + (current_salary * 5)) WHERE designation =
'manager';
• Three top customers (names) in terms of total purchase made by them. List is to be in descending
order in terms of total purchase amount.
SELECT c.name, sum(o.total_bill) as Purchase_Amount from Customers as c JOIN Orders as o on c.id =
o.customers_id group by c.id order by Purchase_Amount desc limit 3;
• How many products are sold out in May 2018 having price more than 100.
SELECT SUM(od.quantity) as Total_Sellings FROM Order_Details as od JOIN Orders as o on o.id =
od.orders_id WHERE o.date = 'MAY 2018' AND od.price > 100;
• Find minimum, maximum and average salaries of the managers.
SELECT MAX(current_salary) as Maximum_Salary, MIN(current_salary) as Minimum_Salary,
AVG(current_salary) as Average_Salary FROM Employees WHERE designation = 'manager' limit 1;
• Design any two queries that apply inner join and left join depending on your relational schema.
Step by step
Solved in 2 steps with 8 images
