1. Write a query to display all information of Customers table
select * from customers;
2. Write a query to find out list of unique cities in customers table.
select distinct city from customers;
3. Display EmployeeID and Photo from Employees table.
select employeeID, photo from employees;
4. Write a query to show all information stored in employee table?
select * from employees;
5. Display Productname and price from products table?
select productname, price from products;
6. WAQ to list all customers from Germany?
https://www.youtube.com/watch?
select * from customers where country= ‘Germany’;
7. Display SQL to display all customers belong to UK and London? execute it and make sure you can get the results.
https://www.youtube.com/watch?
8. Develop SQL to display all customers which are order by in ascending order on city? Execute and make sure you can display results.
https://www.youtube.com/watch?
select * from customers order by city;
9. Develop sql to display first 5 records from products table. Execute and verify results.
https://www.youtube.com/watch?
select * from customers limit 5;
10. Develop sql to display all customers who belong to country which name starts with M. Execute and verify results.
select * from customers where country like ‘M%’;
11. Develop sql to display all customers who belong to country which name end with y.
https://www.youtube.com/watch?
select * from customers where country like ‘%y’;
12. Develop sql to display all customers who belong to country which name not start with M.
select * from customers where country not like ‘M%’;
13. Develop SQL which selects all customers with a Country containing the pattern “land”.
https://www.youtube.com/watch?
select * from customers where country like ‘%land%’;
14. Develop SQL to display all products between price 10 and 20. Execute and verify the results.
select * from products where price between 10 and 20;
15. Develop SQL to display all products not between price 10 and 20. Execute and verify the results.
select * from products where price not between 10 and 20;
16. Develop SQL to display all orders from orders table between ’04-July-1996′ and ’09-July-1996′
http://www.training.
17. Develop SQL query which explain how to use aliases to display column names?
http://www.training.
select count(*) as ‘Total_customers’ from customers;
18. Develop sql to display average value of price column from products table?
http://www.training.
select round(avg(price),2) from products;
19. Develop SQL query to return total number of records from customers table?
http://www.training.
select count(*) from customers;
20. Develop SQL to find the price of costliest and cheapest items from product table?
http://www.training.
http://www.training.
21. Using inner join, develop sql on customers and order table which display all customers who book orders?
http://www.training.
22. Using left join on customer and order table, develop sql which display all customers and whatever orders they have?
http://www.training.
23. Using right join on customer and order table, develop sql which display all customers and any order they have places?
http://www.training.
24. Develop SQL to display all customers group by different countries?
25. Using product table, develop SQL which display all products group by supplier id and price more than 18?
http://www.training.
26. Nested Query: List each product we have, which is more expensive than product named “’Pavlova’”
SELECT * FROM Products WHERE Price > (SELECT Price from Products WHERE ProductName==’Pavlova’ )
27. Multi Table Query: List down all customer names with the product they bought.
SELECT CustomerName, ProductName
FROM Orders, OrderDetails, Customers, Products
WHERE Orders.OrderID==OrderDetails.OrderID and
OrderDetails.ProductID==Products.ProductID and
Orders.CustomerID=Customers.CustomerID;
SELECT Country, count(*) as Total FROM Customers GROUP BY Country HAVING Total >=7
30. List down countries that the number of customers we have in is more than that we have in Germany
SELECT Country, count(*) as Total FROM Customers GROUP BY Country HAVING Total > (SELECT count(*) FROM Customers WHERE Country ==”Germany” )
31. Aggregation Using Multi Table: List down the amount of each product that we have sold yet. Include unit price, and total income that we have gotten on each price. Sort the result by amount from the maximum to minimum one.
SELECT ProductName, price as ‘Unit Price’, sum(Quantity) as “Amount Sold”,
sum(Quantity)* price as ‘Total Incomme’
FROM Products, OrderDetails
WHERE Products.ProductID=OrderDetails.ProductID
GROUP BY ProductName
ORDER BY ProductName
32. Using Date and Aggregation: List down the products that we have sold in days BETWEEN ‘1996-07-04’ and ‘1996-07-05’. I need you to show me also the product unit price, amount we sold and total income from that product.
SELECT ProductName, price as ‘Unit Price’, sum(Quantity) as “Amount Sold”,
sum(Quantity)* price as ‘Total Incomme’
FROM Products, OrderDetails, Orders
WHERE Products.ProductID=OrderDetails.ProductID AND
OrderDetails.OrderID=Orders.OrderID AND OrderDate BETWEEN ‘1996-07-04’ and
‘1996-07-05’
GROUP BY ProductName
ORDER BY ProductName
