WHERE [EnglishProductName] LIKE 'Road%Red%' Take some time to try out different wild card search terms. --you can also substitute in the following line to retrieve the results shown on the right WHERE [EnglishProductName] LIKE '%52%' USE [AdventureWorksDW2012]; This is how you can concatenate two columns if data, adding a space between the firstname and lastname columns. Using the Concat() function SELECT CONCAT([FirstName], ' ', [LastName]) AS Employee rather than + to concatenate columns is more secure to SQL injection , [Title], [EmailAddress] attacks. While we use concatenation to inprove the output, we still sort by , FORMAT([BaseRate], 'N2') as [Base rate] one column - the LastName (not a concatenated term). FROM [dbo].[DimEmployee] The outcome of the query is a list of employees. Presumably the list can be created within a stored procedure that is fed to SMTP server for email WHERE [LastName] BETWEEN 'A%' AND 'M%' distribution. Of course the list could be further sorted by job title or other ORDER BY LastName HR information. Notice also that you can order (sort) by a column that isn’t in the resultset. Can you retrieve a list of employees whose last name begins with the same How can HR send an email to approximately half of the letter as your last name? employees? Here we pull a list of employees whose last names start with the first letter A thru L. The BETWEEN statement can also be used to filter by a range such as Notice the use of M as the ending range, the ending range is not BETWEEN 500 AND 1000. This is the same as typing fieldname > 500 AND inclusive. fieldname < =1000 USE [AdventureWorksDW2012]; Sometimes your filters are numerous to filter down the data to retrieve only what you need. You just need to specify which column to exclude one SELECT [EnglishProductName], Color, [ListPrice] (or more) category or value. You can use this fieldname <> ‘value’ to accomplish this. The syntax is column name <> ‘criteria’ FROM [dbo].[DimProduct]
WHERE [EnglishProductName] LIKE 'Road%Red%' --you can also substitute in the following line to retrieve the results shown on the right WHERE [EnglishProductName] LIKE '%52%' Take some time to try out different wild card search terms. USE [AdventureWorksDW2012]; SELECT CONCAT([FirstName], ' ', [LastName]) AS Employee , [Title], [EmailAddress] , FORMAT([BaseRate], 'N2') as [Base rate] FROM [dbo].[DimEmployee] WHERE [LastName] BETWEEN 'A%' AND 'M%' ORDER BY LastName How can HR send an email to approximately half of the employees? Here we pull a list of employees whose last names start with the first letter A thru L. Notice the use of M as the ending range, the ending range is not inclusive. This is how you can concatenate two columns if data, adding a space between the firstname and lastname columns. Using the Concat() function rather than + to concatenate columns is more secure to SQL injection attacks. While we use concatenation to inprove the output, we still sort by one column - the LastName (not a concatenated term). The outcome of the query is a list of employees. Presumably the list can be created within a stored procedure that is fed to SMTP server for email distribution. Of course the list could be further sorted by job title or other HR information. Notice also that you can order (sort) by a column that isn’t in the resultset. Can you retrieve a list of employees whose last name begins with the same letter as your last name? The BETWEEN statement can also be used to filter by a range such as BETWEEN 500 AND 1000. This is the same as typing fieldname > 500 AND fieldname < =1000 USE [AdventureWorksDW2012]; SELECT [EnglishProductName], Color, [ListPrice] FROM [dbo].[DimProduct] Sometimes your filters are numerous to filter down the data to retrieve only what you need. You just need to specify which column to exclude one (or more) category or value. You can use this fieldname <> ‘value’ to accomplish this. The syntax is column name <> ‘criteria’
Featherman-TSQL-Primer-12-29-16.docx