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.
0 kommentar(er)
