Intermediate T-SQL

Where clause, Like operators,scaler functions;  "Intermediate T-SQL" got you covered.

Posted by Rishi Raj Gujadhur 1/9/2016

Table of content

 

Prerequisites
 

  • A solid understanding of basic transact SQL programming.  You can learn about SQL basic programming using the following link: T-SQL_Basics
 
  • SQL Server Management Studio 2012 Express or SQL Server Management Studio 2014 Express. You can download one of the above software at Microsoft's website.
 

A. View all records before filtering


1. Create a new query
 
2. Select your database
 
3. Type:
 
select * from tblEmployee
 
Result: 
 

B. Filtering using Where clause


Short answer:
 
select * from tblEmployee
where age = 30
 
Long answer:
 
1. Create a new query

2. Select your database

3. Type:

select * from tblEmployee
where age = 30
 
where age ----> select records and filter based on the age column  

= 30 ----> is equal to 30
 
4. Execute the query

Result: 
 

C. Conditional Operators:


Diagram 1


 
The where clause uses conditional operators to filter records.

The where clause is always followed by a subject which is followed by an operator.  
   
For instance:
 
To filter the records based on adult employees
 
1. In the query that you previously created, Type:

select * from tblEmployee
where age > 18
 
> ----> is greater (conditional operator)
 

2. Execute the query
 
Result: 
 

D. SQL Like operator

 
  • The LIKE operator is used along with a WHERE clause to find one or many values corresponding a pattern specified by a user.
 
  • The Like operator is commonly used with SQL wildcards.


SQL Wildcards


Diagram 2

 


To view all employees with a full name ending with the character 'r':

 
1.In the previously created query, type:
 
SELECT * FROM tblEmployee
WHERE FullName LIKE '%r'
 
2. Execute the query
 
Result:
 
  • Feel free to try all the wildcards as shown in the Diagram 2.
 

E. SQL AND Operators


Short answer:
 
SELECT * FROM tblEmployee
WHERE age = 10 and salary =10000
 
Long answer:

1. Create a query (by now you should be able to select the right database) if you followed the tutorial properly.

2. Type:

SELECT * FROM tblEmployee
WHERE age = 10 and salary =10000
 
and --> If both the first condition (age = 10) AND the second condition (salary =10000) that you provided are true then one or many records gets displayed.

3. Execute the query.
  • In this case no records were displayed since there are no employees with age:10 and salary: 10000 in the table.  
   
  • The BETWEEN operator selects records from a specified table based on a range of values that you provide it with.

Short answer:

SELECT * FROM tblEmployee
WHERE age between 10 and 15
 
Long answer:
 
1. Create a new query
 
2. Select the somethingDB database
 
3. Type:
 
SELECT * FROM tblEmployee
WHERE age between 10 and 15
 
between 10 and 15 ----> range specified is 10 to 15  
 
4. Execute the query
 
Result:
 

G. SQL OR operator


Short answer:

SELECT * FROM tblEmployee
WHERE age = 10 OR salary =10000
 
Long answer:
 
1. Create a query

2. Type:

SELECT * FROM tblEmployee
WHERE age = 10 OR salary =10000
 
OR  ----> If either the first OR the Second condition that you provided is true then one or many records gets displayed. 3. Execute the query.

Result: 
 

H. Select individual columns


Select FullName,age
from tblEmployee
 
Select FullName,age ----> After the select keyword Individual columns names are specified followed by commas , when selecting individual columns  

I could not do an SQL intermediate tutorial without mentioning SQL aliases. 
 
  • SQL aliases are used to give a temporary name to a column in a table, to make it more readable.
 
Short answer:
 
SELECT FullName as Employee_Full_Name
FROM tblEmployee
 
Long answer:
 
1. Create a new query

2. Type:

SELECT FullName as Employee_Full_Name
FROM tblEmployee
​
as Employee_Full_Name ----> A more readable temporary column name

The as keyword as is always followed by a temporary name without spaces.  

3.  Execute the query.                                                                                                                     

Result: 
 

J. Order by keyword
 

The ORDER BY keyword is used to sort records from a table.

Short answer:

select * from tblEmployee
order by age asc
 
Long answer:

1. Create a new query
 
2. Type:
 
select * from tblEmployee
order by age asc
 
order by age asc ----> sort the records by age starting the youngest employee.  

By default if records are sorted in the ascending order.  

You can use the order by age desc ----> to sort the records by age starting the eldest employee.  

3. Execute the query.

Result: 
 

K. Top Clause

 

To select top 3 most paid employees


Short answer :

select top 3 * from tblEmployee
order by salary desc
 
Long answer :

1. Create a query
 
2.Type:
 
select top 3 * from tblEmployee
order by salary desc
 
select top 3 * ----> Select the top 3 records that the query returned from the table.

The SELECT TOP clause can be used to specify the number of records to return from a table.  

3. Execute the query.

Result: 
 

L. Aggregate functions


AVG() - Returns the average value


1.Type: 

Select avg(Salary) AS Average_Salary
from tblEmployee
 
avg(column_name) ----> returns the average value of a the column name you specify.  

The column those name you specify should contain digits.  

avg(Salary) AS Average_Salary  ----> You need to alias the column name to ensure that a column name is displayed along with its values.


COUNT() - Returns the number of rows

 
1. Type:

Select Count(fullName) as Number_Of_Employee
from tblEmployee
 
Count(fullName) ----> Returns the number of number of values the fullName column has.

If there are 4 Employees with their full names; there are 4 values of fullName in the table. Thus the result of this query will be 4.
 

SUM() - Returns the sum

 
1.Type:

Select sum(salary) as Total
tblEmployee
 
sum(Column_Name) ----> returns the total of all the values of that column combined.

Unlike the count() function the sum() function requires the column those name has been provided to contain digits.  
 

Challenge:
 

Try using the following aggregate functions on your own.  Also feel free to post your result in the comment section of this post.
 

  • MAX() - Returns the largest value of a specified column.
 
  • MIN() - Returns the smallest value of a specified column.
 

M. SQL Scalar functions


Lower () - Converts a field to upper case

 
1. Type:

Lower(fullName) as Lower_Case_Names
tblEmployee
 
Lower(fullName) ----> Change all text values (fields) from the "fullName" column to lowercase  
 
2.Execute query


LEN() - Provides the length of a text field that you specify 


1. Type:
 
select LEN('Joe') as Name
 
'Joe' ----> a set of characters (string) , also a parameter for the Len() function.  

2. Execute the query.
 
Result: 3
  
1.Type:
 
select LEN(fullName) AS Name_Length
from tblEmployee
 
LEN(fullName) ----> Returns the length of all the fields of the fullName column

2.Execute the query.
 
Result: 
 

Challenge:

Try using the following scalar  functions on your own.  Also feel free to post your result in the comment section of this post.
 

  • Upper() - Converts a field to upper case  
 
  • at you learnt in this tutorial using SQL Server Management Server (SSMS ).
   
Download link: T-SQL_Intermediate_Tutorial.docx