SELECT c.ContactTypeID, c.Name AS ContactTypeName, COUNT(*) AS N_contacts Retrieve a list of the different types of contacts and how many of them exist in the database To do so, we need to use COUNT(), which is an aggregate function and in this case requires a GROUP BY clause to return one single row for each group.Р В РІР‚СњР В РІР‚С›gain we need to use an alias to name the column.

We need now to count the number of contacts for each type and display it along the ContactTypeID and ContactTypeName SELECT p.BusinessEntityID, LastName, FirstName, ISNULL(MiddleName,'') AS MiddleName MiddleName, if there is no MiddleName, to display '' (empty string) instead of NULL To avoid this, we can use the TSQL function ISNULL(), to replace NULL values for the value we want, in this case an empty string Р В Р вЂ Р В РІР‚С™Р РЋРЎС™.Р В РІР‚СњР В РІР‚С›lso we need to display the name of the column as MiddleName, hence we need to use an alias for it. You have seen that thereР В Р вЂ Р В РІР‚С™Р Р†РІР‚С›РЎС›s a number of people which do not have MiddleName and displays as NULL. Next one is built on top of the previous again. SELECT p.BusinessEntityID, LastName, FirstName, MiddleName Retrieve a list of all contacts which are 'Purchasing Manager' which contains Foreign Keys referencing both tables. , but since we need to filter only those which are Р В Р вЂ Р В РІР‚С™Р вЂ™Р’ВPurchasing ManagerР В Р вЂ Р В РІР‚С™Р Р†РІР‚С›РЎС› we need somehow to involve. In this SELECT, we need to retrieve only columns from one of the tables. 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. Sounds pretty obvious but itР В Р вЂ Р В РІР‚С™Р Р†РІР‚С›РЎС›s awesome how many people will fail to know it, even experienced professionals. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified. Next question adds another clause, this time is ORDER BY, which sorts the returned results. Retrieve a list of the different types of contacts which are managers Third weР В Р вЂ Р В РІР‚С™Р Р†РІР‚С›РЎС›ll need to use the WHERE clause combined with the logical operator LIKE to filter the different names that contain the word Retrieve (ContactTypeID, Name) for the contact type 'Marketing Manager' The Second is based on the first and will use the WHERE clause to filter the results returned by the query. Retrieve a list of the different types of contacts that can exist in the database 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. That doesnР В Р вЂ Р В РІР‚С™Р Р†РІР‚С›РЎС›t mean my solutions are the only possibles, and in some cases IР В Р вЂ Р В РІР‚С™Р Р†РІР‚С›РЎС›ll provide more than one answer taking different approaches. ItР В Р вЂ Р В РІР‚С™Р Р†РІР‚С›РЎС›s been a while since I published Day 1 questions and I guess itР В Р вЂ Р В РІР‚С™Р Р†РІР‚С›РЎС›s time to post the answers I came up with.

Adventureworks database install