Adventureworks2012 lt script full#

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. Only interested in ContactTypes that have 100 contacts or more. 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 clause. Just add the ORDER BY clause to complete the next question 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.

Adventureworks2012 lt script