FROM [dbo].[DimProduct] WHERE [EnglishProductName] LIKE '%Road%' ORDER BY [List Price] DESC A first shortcoming of T-SQL is introduced. When the database program uses the FORMAT() function, the result is that the datatype for the numeric column is changed to character field, and the sorting stops working correctly. Just remove the FORMAT() syntax to fix the problem. It is widely recommended to perform the formatting in the reporting or data visualization software, rather than in SQL. SELECT [EnglishProductName], [Class] , Format([ListPrice], 'N0') as [List Price] FROM [dbo].[DimProduct] WHERE Class = 'M' AND [EnglishProductName] LIKE '%Red%' ORDER BY [List Price] DESC -----a different resultset if retrieved if you change the criteria-- SELECT [EnglishProductName], [Class] , Format([ListPrice], 'N0') as [List Price] FROM [dbo].[DimProduct] WHERE Class = 'L' AND [EnglishProductName] LIKE '%Silver%' ORDER BY [List Price] DES Just changing the Class and Color of course produces a completely different list USE [AdventureWorksDW2012]; SELECT [EnglishProductName], [Class] , ListPrice as [List Price] FROM [dbo].[DimProduct] What if you want to see the product line for the Road bikes? What if you want to just see the red road bikes? What if the customer has been measured and sized for the 52 cm model? Can you show a list of products that they can further examine? Can you retrieve a list of seats? Of bicycle helmets?
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’