I introduced you to the SELECT statement in SQL in my last SQL posting: SQL: SELECT Statement
Now I am going to show you some tricks that will provide you with more functionality in your Select statements.
**note, in the examples below, I am working with the HumanResources.Employee table from the AdventureWorks2012 data set. If you want to follow along with the exercises, go to my previous SQL posting: SQL: SELECT Statement where I show you how to download and attach the AdventureWorks2012 database.
Top allows you to limit the number of rows that are returned. (note Top is a T-SQL (Microsoft’s version of SQL command. MySQL and Oracle use the term Limit).
Select Top 10 * from HumanResources.Employee go
Using Top 10 we are querying only the first 100 records from the Employee table. This is often useful when you are exploring a database and just want to see what is in a table. Using Select * in some larger tables without using the Top command to limit the amount of output can result in a query that could take minutes to hours to complete.
Note that the query above returns the first 10 records based usually on the order records were entered. If you want to top 10 newest employees you would need to add an Order By command.
Select Top 10 * from HumandResources.Employee order by HireDate desc go
The order by command sorts the results by the dates in the column HireDate in descending order (desc) most recent to earliest. If I wanted to go the other way, I would use the command: order by HireDate asc – ascending(asc)
If yon want a count of the number of records in a table, SQL has a Count() function. The syntax is below.
Select Count(*) from HumandResources.Employee go
This returns the number of records in the table. If you wanted to know how many male employees are in the table, you can add a Where clause (I will cover Where more in-depth in later lessons)
Select Count(*) from HumandResources.Employee where Gender like 'M' go
Notice how there are repeated JobTitles in the table below. What if you wanted a list of job titles without any repeating elements.
In this case, you will use the Distinct() function. Placing the column you want in the parenthesis and the query will return a non repeating list.
Select Distinct(JobTitle) from HumandResources.Employee go
Now, what if you want to know how many distinct job titles exist in the company. Well, we can use the Count() function in conjunction with Distinct()
Select Count(Distinct(JobTitle)) from HumandResources.Employee go
The query above will return a count (number) of the distinct job titles found in the table
Max() and Min()
Max() and Min() do pretty much what you would expect them to do. They return the maximum or minimum value in column.
Select Max(HireDate) as Max, Min(HireDate) as Min from HumandResources.Employee go
** “as” command in a Select statement gives a name to result columns in query
If you enjoyed this lesson, click LIKE below, or even better, leave me a COMMENT.
Follow this link for more SQL content: SQL