{"id":91,"date":"2015-11-02T10:08:21","date_gmt":"2015-11-02T10:08:21","guid":{"rendered":"https:\/\/sqldoubleg.live-website.com\/?p=91"},"modified":"2015-11-02T10:11:56","modified_gmt":"2015-11-02T10:11:56","slug":"day-1-adventureworks-the-solutions","status":"publish","type":"post","link":"https:\/\/www.sqldoubleg.com\/es\/2015\/11\/02\/day-1-adventureworks-the-solutions\/","title":{"rendered":"Day 1 @ AdventureWorks, the Solutions"},"content":{"rendered":"

Solutions for Day 1 of exercises using the AdventureWorks sample database It’s been a while since I published Day 1 questions<\/a> and I guess it’s time to post the answers I came up with.
\nThat doesn’t mean my solutions are the only possibles, and in some cases I’ll provide more than one answer taking different approaches.<\/p>\n

The Answers<\/strong><\/p>\n

The first question does not have much to talk about, it’s just a matter of locate the table we want to retrieve the data from and select the columns we’re asked to.<\/p>\n

\r\n--01=================================================================================\r\n-- Retrieve a list of the different types of contacts that can exist in the database \r\n-- Output: \r\n--\t\t\t- ContactTypeID\r\n--\t\t\t- Name\r\n\r\nSELECT ContactTypeID, Name \r\n\tFROM Person.ContactType\r\n<\/pre>\n
The Second is based on the first and will use the WHERE clause to filter the results returned by the query.<\/p>\n
\r\n--02=================================================================================\r\n-- Retrieve (ContactTypeID, Name) for the contact type 'Marketing Manager'\r\n-- Output: \r\n--\t\t\t- ContactTypeID\r\n--\t\t\t- Name\r\nSELECT ContactTypeID, Name \r\n\tFROM Person.ContactType\r\n\tWHERE Name = 'Marketing Manager'\r\n<\/pre>\n
Third we’ll need to use the WHERE clause combined with the logical operator LIKE<\/a> to filter the different names that contain the word <\/p>\n
\r\n--03=================================================================================\r\n-- Retrieve a list of the different types of contacts which are managers\r\n-- Output: \r\n--\t\t\t- ContactTypeID\r\n--\t\t\t- Name\r\nSELECT ContactTypeID, Name \r\n\tFROM Person.ContactType\r\n\tWHERE Name LIKE '%Manager%'\r\n<\/pre>\n
Next question adds another clause, this time is ORDER BY<\/a>,  which sorts the returned results.
\nIt’s important to remind <\/p>\n
The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.<\/p><\/blockquote>\n
Sounds pretty obvious but it’s awesome how many people will fail to know it, even experienced professionals.<\/p>\n
\r\n--04=================================================================================\r\n-- Retrieve a list of the different types of contacts which are managers\r\n-- Output: \r\n--\t\t\t- ContactTypeID\r\n--\t\t\t- Name\r\n-- Sorted by\r\n--\t\t\t- Alphabetically Descending\r\nSELECT ContactTypeID, Name \r\n\tFROM Person.ContactType\r\n\tWHERE Name LIKE '%Manager%'\r\n\tORDER BY Name DESC\r\n<\/pre>\n
Retrieving data from more than one table requires a more elaborated query, where we specify the tables involved and the equality columns for each of the JOIN operators.
\nIn this SELECT, we need to retrieve only columns from one of the tables, [Person].[Person], but since we need to filter only those which are ‘Purchasing Manager’ we need somehow to involve [Person].[ContactType] in the query. <\/p>\n
The table which we can use both is [Person].[BusinessEntityContact] which contains Foreign Keys referencing both tables.<\/p>\n
\r\n--05=================================================================================\r\n-- Retrieve a list of all contacts which are 'Purchasing Manager' \r\n-- Output: \r\n--\t\t\t- BusinessEntityID\r\n--\t\t\t- LastName\r\n--\t\t\t- FirstName\r\n--\t\t\t- MiddleName\r\n--\r\n-- Sorted by:\r\n--\t\t\t- LastName\t\tascending\r\n--\t\t\t- FirstName\t\tascending\r\n--\t\t\t- MiddleName\tascending\r\n\r\nSELECT p.BusinessEntityID, LastName, FirstName, MiddleName\r\n\tFROM Person.BusinessEntityContact AS bec\r\n\t\tINNER JOIN Person.ContactType AS c\r\n\t\t\tON c.ContactTypeID = bec.ContactTypeID\r\n\t\tINNER JOIN Person.Person AS p\r\n\t\t\tON p.BusinessEntityID = bec.PersonID\r\n\tWHERE c.Name = 'Purchasing Manager'\r\n\tORDER BY LastName, FirstName, MiddleName\r\n\r\n<\/pre>\n
Next one is built on top of the previous again. You have seen that there’s a number of people which do not have MiddleName and displays as NULL.
\nTo avoid this, we can use the TSQL function ISNULL(), to replace NULL values for the value we want, in this case an empty string \u00bb.
\nAlso we need to display the name of the column as MiddleName, hence we need to use an alias for it.<\/p>\n
\r\n--06=================================================================================\r\n-- Retrieve a list of all contacts which are 'Purchasing Manager' \r\n-- Output: \r\n--\t\t\t- BusinessEntityID\r\n--\t\t\t- LastName\r\n--\t\t\t- FirstName\r\n--\t\t\t- MiddleName, if there is no MiddleName, to display '' (empty string) instead of NULL\r\n--\r\n-- Sorted by:\r\n--\t\t\t- LastName\t\tascending\r\n--\t\t\t- FirstName\t\tascending\r\n--\t\t\t- MiddleName\tascending\r\n\r\nSELECT p.BusinessEntityID, LastName, FirstName, ISNULL(MiddleName,'') AS MiddleName\r\n\tFROM Person.BusinessEntityContact AS bec\r\n\t\tINNER JOIN Person.ContactType AS c\r\n\t\t\tON c.ContactTypeID = bec.ContactTypeID\r\n\t\tINNER JOIN Person.Person AS p\r\n\t\t\tON p.BusinessEntityID = bec.PersonID\r\n\tWHERE c.Name = 'Purchasing Manager'\r\n\tORDER BY LastName, FirstName, MiddleName\r\n\r\n<\/pre>\n
We need now to count the number of contacts for each type and display it along the ContactTypeID and ContactTypeName
\nTo do so, we need to use COUNT()<\/a>, which is an aggregate function and in this case requires a GROUP BY<\/a> clause to return one single row for each group.
\nAgain we need to use an alias to name the column.<\/p>\n
\r\n--07=================================================================================\r\n-- Retrieve a list of the different types of contacts and how many of them exist in the database \r\n-- Output: \r\n--\t\t\t- ContactTypeID\r\n--\t\t\t- ContactTypeName\r\n--\t\t\t- N_contacts\r\n--\r\n\r\nSELECT c.ContactTypeID, c.Name AS ContactTypeName, COUNT(*) AS N_contacts\r\n\tFROM Person.BusinessEntityContact AS bec\r\n\t\tINNER JOIN Person.ContactType AS c\r\n\t\t\tON c.ContactTypeID = bec.ContactTypeID\r\n\tGROUP BY c.ContactTypeID, c.Name\r\n\t\r\n<\/pre>\n
Just add the ORDER BY clause to complete the next question<\/p>\n
\r\n--08=================================================================================\r\n-- Retrieve a list of the different types of contacts and how many of them exist in the database \r\n-- Output: \r\n--\t\t\t- ContactTypeID\r\n--\t\t\t- ContactTypeName\r\n--\t\t\t- N_contacts\r\n--\r\n-- Sorted by:\r\n--\t\t\t- N_contacts descending\r\n\r\nSELECT c.ContactTypeID, c.Name AS ContactTypeName, COUNT(*) AS N_contacts\r\n\tFROM Person.BusinessEntityContact AS bec\r\n\t\tINNER JOIN Person.ContactType AS c\r\n\t\t\tON c.ContactTypeID = bec.ContactTypeID\r\n\tGROUP BY c.ContactTypeID, c.Name\r\n\tORDER BY COUNT(*) DESC\r\n\r\n<\/pre>\n
We have seen before that to filter the returned rows we can use the WHERE clause, but to filter rows based on the aggregate function we must use the HAVING<\/a> clause.<\/p>\n
\r\n--09=================================================================================\r\n-- Retrieve a list of the different types of contacts and how many of them exist in the database \r\n-- Output: \r\n--\t\t\t- ContactTypeID\r\n--\t\t\t- ContactTypeName\r\n--\t\t\t- N_contacts\r\n--\r\n-- Sorted by:\r\n--\t\t\t- N_contacts descending\r\n-- Filter:\r\n--\t\t\t- Only interested in ContactTypes that have 100 contacts or more.\r\n\r\nSELECT c.ContactTypeID, c.Name AS ContactTypeName, COUNT(*) AS N_contacts\r\n\tFROM Person.BusinessEntityContact AS bec\r\n\t\tINNER JOIN Person.ContactType AS c\r\n\t\t\tON c.ContactTypeID = bec.ContactTypeID\r\n\tGROUP BY c.ContactTypeID, c.Name\r\n\tHAVING COUNT(*) >= 100\r\n\tORDER BY COUNT(*) DESC\r\n<\/pre>\n
More functions into play, in order to comply with the requirements, we will need to use a function to generate the column full name and another to format the date to the required, also some calculation must be done to calculate the weekly salary as in the database we only store the hourly rate.<\/p>\n