INTERMEDIATE T-SQL

WHERE CLAUSE, LIKE OPERATORS,SCALER FUNCTIONS; “INTERMEDIATE T-SQL” GOT YOU COVERED.

Posted By myself on 1/9/2016 at http://rchannel.azurewebsites.net/ChannelR/Details/4

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.

F. SQL BETWEEN OPERATOR

  • 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. SQL ALIASES

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