Mastering SQL Through Practice: Exploring Sylvia Moestl Vasilik’s Exercises — Part 4
In this article, we will now answer questions 15–19. Visit the earlier blog to see how previous queries were answered.
Contents:
15. When was the first order taken?
16. Countries where there are customers of specific company.
17. Count of Contact titles for customers.
18. Show Products with associated supplier names.
19. Orders and the Shipper that was used.
Problem 15: Show the date of the first order ever made in the Orders table.
Description: There’s a aggregate function called Min that we have used in this problem.
Problem 16: Show a list of countries where the Northwind company has customers.
Description: Here we have used the Group By clause for this query.
Problem 17: Show a list of all the different values in the Customers table for ContactTitles. Also, include a count for each ContactTitle. This is similar in concept to the previous question “Countries where there are customers”, except we now want a count for each ContactTitle.
Description: The answer for this problem builds on multiple concepts introduced in previous problem, such as grouping, aggregate functions, and aliases.
Problem 18: We’d like to show, for each product, the associated Supplier. Show the ProductID, ProductName, and the CompanyName of the Supplier. Sort by ProductID. This question will introduce what may be a new concept, the Join clause in SQL. The Join clause is used to join two or more relational database tables together in a logical way. Here’s a data model of the relationship between Products and Suppliers.
Description: Just as a reference, here’s an example of what the syntax for the Join looks like, using different tables from the Northwind database. It will show all the products, with the associated CategoryName.
Select ProductID ,ProductName ,CategoryName From Products Join Categories on Products.CategoryID = Categories.CategoryID
Problem 19: We’d like to show a list of the Orders that were made, including the Shipper that was used. Show the OrderID, OrderDate (date only), and CompanyName of the Shipper, and sort by OrderID. In order to not show all the orders (there are more than 800), show only those rows with an OrderID of less than 10300.
Description: First of all we have created a SQL statement that shows only the rows and columns that we need from the Orders table.Then, we added the join to the Shipper table, and the necessary field from that table.
