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 first part, I will show some basics of it. Before you start working on this, download the Script file attached and run it in the SQL Server to create the database Northwnd. If you already have it, skip the download.
2. Selecting all the columns
Consider the following example:
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 following Picture.
In the above Query, * represents all the columns and Employees specifies the table name.
3. Filtering the Specific Column
Ex2:
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 this Query above there are four specific columns. Usually people will not go for all the columns by using the * as it generates network traffic. Keep that in mind that always specify the column you want instead of going for all the columns. Execute the preceding querry and see the results.
4. Contatenating the Columns
Ex3:
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 column is the concatenated column formed by joining the columns TitleOfCourtesy, FirstName and LastName. To have a valid column name we used [Full Name]. The Full Name is given in the Square brackets because it has the Space between FullName. You do not need to use Square brackets when the column name does not have a space. Also note that the Concatenation is done by the operator plus by joining the table columns with constant string link space and comma wrapped in single quotes. Execute the preceding query to see the results. Here, the Full Name is called the Column Alias.
Alternate Query for the Column Alias:
Select EmployeeID,
[Full Name] = TitleOfCourtesy + ' ' + FirstName + ', ' + LastName
from Employees;
5. Usage of distinct
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 are repeated more than once. How do we eliminate duplicate city names in the column City? The following query does that:
Ex 5: Select distinct city from Employees
The distinct keyword in front of the column name tells the sql server do not repeat the column value once it is already displayed. The output of the above query will show only the different city names.
6. Where clause
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. The Where clause will operate based on the Boolean expression formed based on the column values. Consider the following example:
Ex 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 process the above SQL statement:
1) I need to Select all the columns, because * is specfied 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 (the where condition) will be evaluated for each row. And the row will be displayed only when the consdition returns true.
One more Example:
Select * from Employees where employeeid > 5;
7. 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 to use the Like operator. Consider the following simple example:
Ex 7: 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. Also try the following specified patterns:
<!--[if !supportEmptyParas]-->
<!--[endif]-->
Play with the above patterns with the like operator with your own queries. See you in the Next Part.
Note: The Examples are making use of the Northwnd Db. To get the DB, download script and run it on the SQL Server.