Let’s see what is in the Adventureworks database:
Then take a look at the top 10 rows of each table:
Get some basic information about the purchase orders:
Looking at the data a few initial questions arise:
Get some basic metrics about the purchase orders:
Which tables in the purchasing schema might be able to join, and which columns might be keys to join on? It looks like all of the keys in this database might have id at the end of their name:
How many purchase orders are shipped by each method in the entire dataset? Note that because this data is artificial, everything is very clean and matches nicely. In the real world, for countless reasons tables frequently end up with misaligned timeframes, duplicate rows, or missing data.
What is the average number of orders purchased across all vendors?
What is the average cost across all vendors?
Here is an excellent way to understand sql joins:
Let’s investigate purchasing.productvendor and purchasing.vendor.
- How many records are there in each table?
- How many different businessentityid?
- How many businessentityid are there matching between purchasing.productvendor and purchasing.vendor?
- How many records are there when matching on businessentityid?
- How many records are there in purchasing.productvendor which have no match in purchasing.vendor when joining on businessentityid?
- How many records are there in product purchasing.vendor which have no match in purchasing.productvendor when joining on businessentityid?
- Make a table which has EVERY combination of businessentityid (from purchasing.vendor) and productid (from purchasing.productvendor).
- How many records are the resulting table?
- Remove the table from the database.
Understanding filtering in SQL queries with examples:
- is not
- not equal
- not in
- greater than
- less than
Having run through the above code you are now ready for the Basic Test!