Day 1 @ AdventureWorks, some basics about SELECT, JOINS and more

Day 1 of exercises using the AdventureWorks sample database, the SELECT statement The first day of this journey we’ll work on the most basic statement I can think of, the SELECT statement.

Exercises are growing in difficulty, from the most basic to others a bit more complicated, where you’d get data from more than on table in the database or different clauses might need to be applied to obtain the desired results.

The exercises are as follows, in the post with the answers I’ll try to provide a reasonable explanation for each query along with some useful resources for a better understanding.

You can copy this into your SSMS (SQL Server Management Studio) using the sample database AdventureWorks, I’ve used the current version (2014), but most likely you can try in earlier versions without problem.

--==================================================
-- SELECT Statements
--==================================================

--01============================================================================================================
-- Retrieve a list of the different types of contacts that can exist in the database
-- Output:
-- - ContactTypeID
-- - Name

--02============================================================================================================
-- Retrieve (ContactTypeID, Name) for the contact type 'Marketing Manager'
-- Output:
-- - ContactTypeID
-- - Name


--03============================================================================================================
-- Retrieve a list of the different types of contacts which are managers
-- Output:
-- - ContactTypeID
-- - Name


--04============================================================================================================
-- Retrieve a list of the different types of contacts which are managers
-- Output:
-- - ContactTypeID
-- - Name
-- Sorted by
-- - Alphabetically Descending


--05============================================================================================================
-- Retrieve a list of all contacts which are 'Purchasing Manager'
-- Output:
-- - BusinessEntityID
-- - LastName
-- - FirstName
-- - MiddleName
--
-- Sorted by:
-- - LastName ascending
-- - FirstName ascending
-- - MiddleName ascending


--06============================================================================================================
-- Retrieve a list of all contacts which are 'Purchasing Manager'
-- Output:
-- - BusinessEntityID
-- - LastName
-- - FirstName
-- - MiddleName, if there is no MiddleName, to display '' (empty string) instead of NULL
--
-- Sorted by:
-- - LastName ascending
-- - FirstName ascending
-- - MiddleName ascending


--07============================================================================================================
-- Retrieve a list of the different types of contacts and how many of them exist in the database
-- Output:
-- - ContactTypeID
-- - ContactTypeName
-- - N_contacts
--


--08============================================================================================================
-- Retrieve a list of the different types of contacts and how many of them exist in the database
-- Output:
-- - ContactTypeID
-- - ContactTypeName
-- - N_contacts
--
-- Sorted by:
-- - N_contacts descending


--09============================================================================================================
-- Retrieve a list of the different types of contacts and how many of them exist in the database
-- Output:
-- - ContactTypeID
-- - ContactTypeName
-- - N_contacts
--
-- Sorted by:
-- - N_contacts descending
-- Filter:
-- - Only interested in ContactTypes that have 100 contacts or more.


--10============================================================================================================
--
-- The table [HumanResources].[EmployeePayHistory] holds employees' current salary along with historical data
--
--
-- List of employees and their Historical weekly salary (based on 40h a week)
-- Output:
-- - [RateChangeDate] from historical table aliased to DateFrom, in the format 'dd/mm/yyyy'
-- - 1 column which includes 'LastName, FirstName MiddleName' called FullNamne
-- - WeeklySalary which must be calculated too
--
-- Sorted by
-- - FullName
-- - DateFrom


--============================================================================================================
-- BONUS
--============================================================================================================


--============================================================================================================
-- List of employees and their current weekly salary (based on 40h a week)
-- Output:
-- - [RateChangeDate] from historical table aliased to DateFrom, in the format 'dd/mm/yyyy'
-- - 1 column which includes 'LastName, FirstName MiddleName' called FullNamne
-- - WeeklySalary which must be calculated too
--
-- Sorted by
-- - FullName
-- - DateFrom
--
-- ONLY 1 line per Employee, the current one to be precise.