Exercise 2

1. Create a Database named northwind.

2. Download the SQL file from this link and run the code present in it inside the northwind database. (Make sure that the tables are created inside northwind database only)

3. Write an SQL query to display the ProductID, ProductName, UnitPrice from Products table in decreasing order of the UnitPrice.

4. Write an SQL query to display all the distinct CategoryName in Categories table.

5. Write an SQL query to count the total number of unique OrderId in Order Details table.

6. Write an SQL query to display the OrderID, Employee Full Name (example: Mr. John Doe), Customer Company Name and OrderDate in such a way that the Latest Order is displayed at the top.

7. Write an SQL query to display the most expensive UnitPrice in Products table for each CategoryName. Also, arrange it in ascending order of CategoryName.

8. Write an SQL query to display the OrderID, CustomerName, OrderDate and the total sales amount for each OrderId and Customer. (Total Sales Amount = (Quantity * UnitPrice) - ((Quantity * UnitPrice) * Discount)

9. Write an SQL query to display the CustomerName and the total sales amount for each OrderId and Customer. (Total Sales Amount = (Quantity * UnitPrice) - ((Quantity * UnitPrice) * Discount)

10. Write an SQL query to display the SupplierName and the total sales amount for each Supplier. (Total Sales Amount = (Quantity * UnitPrice) - ((Quantity * UnitPrice) * Discount)

11. Write an SQL query to display the ShipperName and the Total number of orders placed for each Shipper.

12. Write an SQL query to display the CustomerName and the Total number of orders placed for each Customer.

13. Write an SQL query to display the ProductName and the Total number of orders placed for each Product.

14. Write an SQL query to display the ProductName that has the most number of Orders Placed.

15. Write an SQL query to display the total Discount given out.

16. Write an SQL query to display OrderId, CustomerName, OrderDate, OrderMonth and OrderYear in increasing order of OrderDate. (Hint: use date time functions in SQL)

17. Write an SQL query to display Total Sales including discounts for each Month and Year.

18. Write an SQL query to find the month with the most number of orders placed.

19. Write an SQL query to find the year with the most number of orders placed.

20. Write an SQL query to display the TotalSales for each year.