Monday, February 28, 2011

SQL 2005 - Examples of Various Select Queries

1. Introduction

In this Article, we will see how we can query the data from the database using the "Select statement" in SQL. In this article, I will show some basics of it. Before you start working on this, download the Script file attached and run it on the SQL Server to create the database Northwnd. If you already have it, skip the download.

2. Row and column filtering

2.1 Selecting all the columns

Consider the below example:

Example 1:
Select * from Employees

This will select all the columns from the table Employees. Before you execute it, make sure the NorthWnd database is selected as shown in the below Picture.

In the above Query, * represents all the columns and Employees specifies the table name.

2.2 Filtering the Specific Column

Example 2:
Select EmployeeID, FirstName, LastName, City from Employees

The above query does not select all the columns from the table employees because we replaced the * with specific column names. So, the * in the previous query states all the columns. Here, in the above query we ask for four specific columns. Usually, people will not go for all the columns by using the * as it generates network traffic. Keep in mind that it is always good to specify the column you want in place going for all the columns. Execute above query and see the results.

2.3 Concatenating the Columns

Example 3:
Select EmployeeID,
TitleOfCourtesy + ' ' + FirstName + ', ' + LastName As [Full Name] 
from Employees;

In the above example, first, note that the query is wrapped into three lines. SQL Server understands where the query starts and where it ends. The second columns are the concatenated column formed by joining the columns TitleOfCourtesy, FirstName and LastName. To have a valid column name we used "As [Full Name]". The Full Name is given in the Square brackets because it has the Space between FullName. You no need to use Square brackets when the column name does not have space. Also, note that the concatenation is done by the operator plus by joining the table columns with a constant string like space and comma wrapped in single quotes. Execute the above query to see the results. Here, the column Full Name is called the "Column Alias".

Alternate Query for the Column Alias:

Example 4:

Select EmployeeID,
[Full Name] = TitleOfCourtesy + ' ' + FirstName + ', ' + LastName 
from Employees;

2.4 Usage of distinct in SQL2005

Select city from Employees

The above example will select City from the table employees. When you look at the output, the city names Seattle and London is repeated more than once. How do we see all the different city names in the column City? Below is the query for that:

Example 5:

Select distinct city from Employees

The "distinct" keyword in front of the column name tells the SQL server that does not repeat the column value once it is already displayed. The output of the above query will show only the different city names.

2.5 Where clause in SQL2005

So far we had seen filtering the column names from the table. How about filtering only the required row that we need? We should use the "where clause" for this purpose. Where clause will operate based on the Boolean expression formed on the column values. Consider below specified example:

Example 6: 

Select * from Employees where employeeid = 5;

In the above query, we are selecting all the columns from the table employees. But, we are selecting the row which has employeeid = 5. How does SQL look the above SQL statement:

  1. I need to Select all the columns because * is specified in place of column names. 
  2. For each row, I need to compare the value in the employeeid with constant 5. When the expression returns true, select that row.

So, the expression you specify in the where clause or where condition will be evaluated for each row. And, the row will be displayed only when the condition returns true.

Example 7:

Select * from Employees where employeeid > 5;

3. Operators in SQL Server

In the previous section we saw the basics of select statements, how do we filter the columns and how do we filter rows. We finished at the usage of the where clause usage. In this section, we will continue exploring the where clause with some useful operators.

3.1 Usage of Like operator

You can use the Boolean operator = to compare the string also. But, it will try to match the exact strings. How about matching the string with some patterns? The answer is; use the "Like operator". Consider the below simple example:

Example 8

Select * from employees where Title like 'Sales%';

In the above query, we are matching the title column with the string that follows a specific pattern Sales%. The Sales% denotes any string that starts with Sales. Execute the query to see the results. There symbols other than "%" that can be used with Like operator. Those operators are shown below:

Play with the above patterns with the like operator with your own queries. 

3.2 Between Operator

This operator is used to check whether the value falls in between an upper and lower range. Please consider the below example:

Example 9

Select * from employees where employeeid between 4 and 7

Here, we specified that we need all columns from the employee table and their Ids should be in the range of 4 to 7. Also not that you can use this "between operator" for String and Date ranges also. Execute the above query in the NortWnd database to see the results.

3.3 IS NULL operator

The "IS NULL" operator is useful to check the empty columns. Note, here empty stands for the column that never received any values through the SQL statement. If a column does not have any value at all then what do we compare to bring that column? That is where the IS NULL operator comes into the picture.

Example 10

Select * from employees where Region IS NULL

The above query will bring all the records which have no region specified.

3.4 And, OR, NOT in SQL2005

These are all "Boolean Operators" and used in the same way we programmers uses them in the conditional statements. You can combine two or three conditions together in the where clause using these operators.

Example 11: 

Select * from employees where title = 'Sales Representative' AND City = 'London'

In the above query "AND Operator" is used to get the combination of Two conditions. Above SQL will retrieve all the employees who has the job title as Sales Representative as well as he belongs to city London.

Example 12

Select * from employees where title = 'Sales Representative' OR City = 'London'

The Condition is joint through the "OR Operator" which states the employee should be a Sales Rep Or else he should be from the London city.

Example 13

Select * from employees where title = 'Sales Representative' AND NOT City = 'London'

The above query uses both And operator and "NOT operator". As per the Operator precedence, the Not operator is applied first to skip all the records which have City as London. So the above query will return all the Sales Rep employees who do not belong to the City London.

3.5 In Operator in SQL2005

The In operator allows you filter a particular column by comparing it with a given list of values. You can use "IN operator" with sub-Queries also. First, we will look at the Simple query that uses this operator. Then we look how it is used in the sub-queries. Look at the simple example below:

Example 14: Select * from employees where FirstName IN ('Janet', 'Margaret', 'Steven', 'Michael', 'Robert')

The above query will select all employees who have the first name provided in the comma separated list. Now look at the example below:

Example 15: Select * from employees where FirstName IN (Select FirstName from employees where TitleOfCourtesy = 'Ms.')

The above query will first execute the sub-query to get all the first name with title Ms. Then the main query performs the filter based on the Subquery returned, "First Name". Also, note that subquery need not be on the same table. It can be a query for a different table also. The list for the IN operator is formed using the Sub-Query in the above example.

Note: The above query examples are created using the NorthWnd database. If you don’t have the DB download the attached script and execute in your SQL server. 

NorthWnd Database : Download 

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Firend :)