CTE & Ranking Function

SQL Code Practice

LEARNING GOALS

By the end of this lecture Understand common table expressions and

ranking functions
students should be able to:

Use smoothly them and apply to real

projects

Know some best practices in SQL

2
TABLE OF CONTENTS

Common Table Expressions

Ranking Functions

SQL Code Practice

3
COMMON TABLE EXPRESSIONS

4
1.COMMON TABLE EXPRESSIONS (1/3)
A CTE can be thought of as a temporary result set that is defined
within the execution scope of a single SELECT, INSERT, UPDATE,
DELETE. It can be used:
 This is used to store result of a complex sub query for further
use.(As a temporary table)
 Create a recursive query.
Syntax:
;WITH CTE_Name [ col_names]
AS
(
CTE_query_definition
)
1.COMMON TABLE EXPRESSIONS (2/3)
Example:

Category
Id
Name
Description
ParentID

Proplem: Select the Level of each element ??

1.COMMON TABLE EXPRESSIONS (3/3)
Solution for this example:
WITH temp(id, name, alevel)
as
(
Select id, name, 0 as aLevel
From Category Where parent_id is null
Union All
Select b.id, b.name, a.alevel + 1 From temp as a, Category as b
Where a.id = b.parent_id
)
Select * From temp

7
1.COMMON TABLE EXPRESSIONS RECURSIVE

Recursive Queries Using Common Table Expressions

 Syntax:
WITH cte_name ( col_names)
AS
(
-- Anchor member is defined.
CTE_query_definition
UNION ALL
-- Recursive member is defined referencing cte_name.
CTE_query_definition
)
-- Statement using the CTE
SELECT *
FROM cte_name
COMMON TABLE EXPRESSIONS DEMO
Demo

9
RANKING FUNCTIONS

10
2.RANKING FUNCTIONS (1/6)
Ranking functions: Ranking functions provides the ability to
rank each row of data.

RANK NTILE

DENSE_RANK ROW_NUMBER

Four kinds of Ranking functions:

ROW_NUMBER
RANK_DENSE
NTILE
RANK
2.RANKING FUNCTIONS (2/6)
Let's take following sample table and data to know about
RANK, RANK_DENSE, NTILE and ROW_NUMBER with
examples:
CREATE TABLE ExamResult(FullName varchar(50), Subject varchar(20), Marks int)

INSERT INTO ExamResult VALUES('Adam','Maths',70)

INSERT INTO ExamResult VALUES ('Adam','Science',80)
INSERT INTO ExamResult VALUES ('Adam','Social',60)

INSERT INTO ExamResult VALUES('Rak','Maths',60)

INSERT INTO ExamResult VALUES ('Rak','Science',50)
INSERT INTO ExamResult VALUES ('Rak','Social',70)

INSERT INTO ExamResult VALUES('Sam','Maths',90)

INSERT INTO ExamResult VALUES ('Sam','Science',90)
INSERT INTO ExamResult VALUES ('Sam','Social',80)
2.RANKING FUNCTIONS (3/6)
Row_Number: Returns the sequential number of a row within
a partition of a result set
Example:
SELECT FullName, Subject, Marks,
ROW_NUMBER() OVER(ORDER BY FullName) RowNumber
FROM ExamResult
ORDER BY FullName, Subject
2.RANKING FUNCTIONS (4/6)
Rank: Returns the rank of each row within the partition of a
result set
Example:
SELECT FullName, Subject, Marks, RANK() OVER(PARTITION BY
FullName ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY FullName, Subject
2.RANKING FUNCTIONS (5/6)
Dense_Rank: Returns the rank of rows within the partition of a
result set, without any gaps in the ranking
Example:
SELECT FullName, Subject, Marks, DENSE_RANK() OVER
(PARTITION BY FullName ORDER BY Marks DESC) Rank
FROM ExamResult
ORDER BY FullName
Dense_Rank Rank
2.RANKING FUNCTIONS (6/6)
Ntitle: Distributes the rows in an ordered partition into a
specified number of groups
Example:
SELECT FullName, Subject, Marks, NTILE(2) OVER
(ORDER BY Marks DESC)Quartile
FROM ExamResult
RANKING FUNCTIONS DEMO
Demo
SQL CODE PRACTICE

18
3.SQL CODE PRACTICE
Explicitly Name Columns in SELECT Statements
 Improve performance.
 Prevent potential failures related to some database schema
change in the future.

For example, using:

SELECT ContactID, FirstName, LastName
FROM Person.Contact
Instead of:
SELECT * FROM Person.Contact
3.SQL CODE PRACTICE
Explicitly Name Columns in INSERT Statements
 Prevent potential failures related to some database schema change in
the future.
 Prevent error with identity column
For example, using:
INSERT dbo.Employee (FirstName, LastName, NationalIDNumber,
ManagerID, Title, BirthDate, MaritalStatus, Gender)
VALUES ('Bill', 'Gates', '123456', NULL, 'CEO', '1959-01-01', 'M' , 'M')
Instead of :
INSERT dbo.Employee
VALUES ('Bill', 'Gates', '123456', NULL, 'CEO', '1959-01-01', 'M' , 'M')
3.SQL CODE PRACTICE

Always specific schema for tables in query.

 Prevent potential failures related to some database schema change or
permission change on schema in the future.

SELECT A.Key1 , B.Col1 , C.Col2 SELECT A.Key1 , B.Col1 , C.Col2

FROM dbo.TableA A FROM TableA A
INNER JOIN dbo.TableB B INNER JOIN TableB B
ON A.Key1 = B.Key1 ON A.Key1 = B.Key1
INNER JOIN dbo.TableC C INNER JOIN TableC C
ON A.Key1 = C.Key1 ON A.Key1 = C.Key1
WHERE A.Col1 = '123' WHERE A.Col1 = '123'
AND B.Col2 like 'A%' AND B.Col2 like 'A%‘
3.SQL CODE PRACTICE
Always provides alias for tables in query.
 Make query more clearer and easier to read.

SELECT A.Key1 , B.Col1 , C.Col2 SELECT TableA.Key1 , TableB.Col1

FROM dbo.TableA A , TableC.Col2
INNER JOIN dbo.TableB B FROM dbo.TableA
ON A.Key1 = B.Key1 INNER JOIN dbo.TableB
INNER JOIN dbo.TableC C ON TableA.Key1 = TableB.Key1
ON A.Key1 = C.Key1 INNER JOIN dbo.TableC
WHERE A.Col1 = '123' ON TableA.Key1 = TableC.Key1
AND B.Col2 like 'A%‘ WHERE TableA.Col1 = '123'
AND TableB.Col2 like 'A%‘
3.SQL CODE PRACTICE
Avoid SQL Server functions in the WHERE clause
 Improve performance.

SELECT EmailAddress SELECT EmailAddress

FROM person.contact FROM person.contact
WHERE EmailAddress like 'As%' WHERE left(EmailAddress,2) = 'As'
3.SQL CODE PRACTICE
Only use DISTINCT if necessary
Only use UNION if necessary, in other case use UNION ALL
Quiz!

Now let's check how you understand

the lecture!

There are 7 questions below.

Click NEXT button to start!

25
 Quiz!
There are 7 questions below.
Click NEXT button to start!

26
SUMMARY

Common Table Expressions

Ranking Functions

SQL Code Practice

27
EXIT COURSE

THANK YOU
You have completed "Lecture _03" course.

Click EXIT button to exit course and discover

the next Lecture "Lecture_04".

EXIT

28