Ranking Functions in SQL...

Que: What are the available Ranking Functions in SQL?

Ans: Rank(), Dense_Rank(), Row_Number(), ntile(), these are the available ranking functions in
sql...

Que: What is Rank()?

Ans: Rank() function assings rank to every value in the result set, if the result set contains / has
duplicated/repeated values, then those duplicated or repeated values receive the same rank
number, and rank() function skips the rank sequence number in the result set based on How
many times the same rank number is repeated, based on that it skips the those many rank
numbers in the rank sequence and then assigns the next rank number to the next value in the
result set...
e.g.:

Prod Price Rank()

A 90 1
B 85 2
C 85 2
D 85 2
E 80 5

NOTE: Prod E and its price 80 is received rank 5 becuase in the result set before 80 the value is
85 which is 2nd highest so received rank 2 and its repeated 3 times, As result set has repeated
values, which repeated 3 times, Rank() functions those many rank numbers in the rank result
set sequence, and assigning that next rank number 5 to Prod E...
Req: Print total profit by country, and assign a rank to every country based on total profit?

NOTE: by making use of Rank functions, we can assign a rank to categorical values...

NOTE: Rank acts as a column in the result set, so whatever the statement we need to write to
asign rank, it must be in the select statement...

NOTE: As per the requirement, we need to print country, total profit and rank columns, where
we enter or define column names in the select statement, in the sequence we need to write
statement to extract / derive ranks....
Que: What is OVER in the above statement?
Ans: Over is Clause, which applies rank functionality on the result set it receives from it's
statement, and to make use of ANY RANKING functions, you must use OVER Clause, every RANK
function must be applied over the result set it receives from Order BY or Partition By and Order
By cluases....
Req: Print total quantity by prod name, and assign a rank to every product based on total
quantity?

Situation: Your working with categorical managers, every category has different products, and
your manager wants to analyse how their category and products with in the category
performing based on their total quantity, your categorical managers wants data in such a way
by assigning a rank to every product with in their category....

Req: Print total quantity by product name, category, assign a rank to every product within
every category based on total Quantity?

NOTE: as per the requirement, we need to perform partitions on the category and with in the
category, need to assign rank to every product based on total quantity...

Que: How to perform partitions on higher categorical data and assign a rank?
Ans: By making use of PARTITION BY Clause, we can perform partitions on the data and assign
ranks to every value within each partition...

Partition By: It's a clause, which performs partitoins on the data based on the given columns.

QUE: how to use Partition By in the rank() statement?

Ans: you need to use Rank function and then Over and then Partition By statement, and use the
required categorical fields in the partition by clause...

Req: Print total quantity by city, state, country, assign rank to every city within every state
and country based on total quantity?

Que: As per the requirement, how many partition we need to perform?

Ans: As per the requirement you need to perform partitions on 2 categorical fields, those are
Country and State, so we need to perform partitions on these 2 fields...

Dense_Rank():

Que: What is Dense_Rank()?

Ans: It assigns rank to every value in the result set, if the result set has repeated values, those
repeated values receives the same rank number, and next value in the result set will receive
next rank number in the rank sequence without skipping the rank sequence...

Que: What is the difference between Rank and Dense_Rank?

Ans: If the result set has repeated values the rank function skips the rank sequence in the result
set, but Dense_rank does not skip the rank sequence eventhough result set has repeated
values...

Que: When to use Dense_rank()?

Ans: If the requirement is to assign a rank without skipping the rank sequence in the result set,
then you need to use Dense rank...

Req: Print total quantity by city, state, country, assign rank without skipping the rank
sequence to every city within every state and country based on total quantity?

Row_Number()

Que: What is Row_Number()?

Ans: It's ranking function, it assigns a UNIQUE rank to every value in the result set, eventhough
result set has repeated values, those repeated values will receive UNIQUE rank....

Req: Print total quantity by product, and assign a unique rank to every product based on total
quantity?

Req: Print total profit by product, sub category and category, assign UNIQUE rank to every
product based on total profit with in each category and sub category?

Ntile()

Que: What is Ntile() ranking function?

Ans: Ntile() ranking function, splits the result set/output rows into given number of
windows/partitions

E.g.: your statement is printing 100 rows, you want split into 5 windows, then in the Ntile
ranking function, you need to specify the window size...

NOTE: Ntile is the only ranking function, that takes an expression

Req: Print total profit by country and split the result set into 4 windows?

String Functions:

String Functions takes input as String (textual values) and returns output as String, or an
interger or boolean values

Lower: It converts a given string / textual values into lower case

Upper: It converts a given string / textual values into UPPER case
Concat: It merge / combine 2 or textual values or columns into one column
Len (Length): It counts number of characters in every row and returns the length of textual
values / string in the given column
Replace: It replaces all occurrences of a substring within a string with the given new string in
the expression

Que: How to replace a part of the string?

Ans: using replace we can replace part of the substring

Left: it extracts given number of character from starting of the given string column
Req: extract first 3 characters from every month name, and print totol profit by month?
Right: it extracts given number of character from ending of the given string column
Reverse: It reverse the given string

Like Operators:

NOTE: We often use LIKE OPERATORS with WHERE clauses

NOTE: there are 2 different signs, we use in the like operators, those % (Percent), _
(underscore)
Req: Print customer names whose name starts with 'z'?
Req: Print customer names whose name ends with 'z'?
Req: print all the cusotmers, who name contains a character 'a' in the 2nd position?
Req: Print all the cusotmers whose name starts with character 'a' and ends with 'r'

Common Table Expression:

Que: how can write a statement as per the best practices, and need print the same output
what the above statement is printing?
Ans: We can do it by using CTE's?

Que: What is CTE?

Ans: Common Table Expression

Que: What are the uses of CTE's?

Ans: With the help CTE's, we can write a statement in readable format, which is easy to read,
and Debug, and understand, and analyse the statement, what your satement is doing, and with
the help of CTE's we can split / devide a COMPLEX SQL statement into bit's and pieces, so that
it's easy to write a complex statement in easy way, which reduces the complexcity of the
statement, also, which ensures that your not missing anything in the statement, and can ensure
every part of your CTE statement is perfoming a specific task....
Req: Write CTE statement as an alternate to the above statement?

Que: how many select statement the above statement has?

Ans: the above statement has 3 select statements.

Que: How many results sets (how many result set windows) the above statement prints /
opens?
Ans: It print ONE result set...

Que: but you said the above statement has 3 different select statements, but its printing only
one result set, what about the reamining 2 select statements result set???
where is the output of those 2 select statements?
Ans: The above statement is CTE, with the help of CTE's we can combine 2 or more SELECT
statements result set into a SINGLE RESULT SET

Que: Okay, we are combining, but where the result set stores?
Ans: the reamining SELECT statement result sets stores templorarly in TEMP DATABASE in the
EXECUTION TIME (when the statement executes, at that time, whatever the output your
statement prints, that output get stores in the temp db temporarly, its not going save any
where just for time being it get store in the temb db, when your statement executes, ifyour
statement is not executing, then it's not going to store anywhere)

Que: Okay, then how are we able to combine these different SELECT statemetn result sets
into a single result set?
Ans: By using JOINS, we are able to combine those different or multiple SELECT statements
result sets into a single result set in CTE....

Req: Print every dept average salary and compare every employee salary with their
Department average salary and find the different difference between Department Avg Salary
and Emp salary?

E.g.: one of the deparment is HR department, as per the requirement, we need to calculate
(extract) HR department Average Salary, and then who are the employess in HR department, we
need to compare every HR department employee salary with HR department average salary,
and find the difference between them....

Que: How to write CTE statement?

Or
Que: Which key word you use to create CTE?
Or
Que: What do you need to use to Create CTE?
Or
Que: how to do you define CTE?
Ans: TO Create CTE / define CTE we need to use a key word called WITH....

Que: I have a SINGLE statement which prints 2 different CTE's, how many WITH keyword's i
might have used to write a SINGEL statement which prints / which has 2 different CTE's?
ANS: Only ONE, It does not matter how many CTE's your SINGLE statement contains, but we
need to use SINGEL WITH key word, we need to use WITH keyword before the FIRST CTE in the
statement, and We need to use CTE delimiter COMMA, after each CTE statement...
Req: Print every dept average salary and compare every employee salary with their
Department average salary and find the different difference between Department Avg Salary
and Emp salary, and print only the employees who is getting paid more than Department
Average Salary?
Req: Print / find Number of Employees in each department who are getting paid more than
department average salary and less than department average salary?

NOTE: as per the requirement, if you want create CTE to achieve the above requirement, you
need to create 3 CTE's,

CTE 1 to print Average Salary by Department

CTE 2 to compare every emp salary with their Department avergae salary find the employess
count who is getting paid MORE then their department average salary

CTE 3 to compare every emp salary with their Department avergae salary find the employess
count who is getting paid LESS then their department average salary ....
Emp Data:

EID Ename DeptID Sal

100 AB 200 10000
102 C 200 15000
104 D 204 12500
105 E 205 13000
106 F 206 9500
107 G 209 9000
108 H 209 20000
109 I 200 50000
110 J 200 55000
111 K 204 60000
112 L 204 65000
113 M 205 90000
114 N 205 25000
115 O 206 35000
116 P 209 20000
REQUIREMENT: Find number of employess (Count of employess) in each department, who is
getting paid > their Department average salary, and number of employess (count of
employees), who id gettig paid < their department average salary????

TCL (Transaction Control Language)

Transaction: A transaction is a unit of work that is performed against a database or set of
statement (Insert, Update and Delete) which should be executed as one unit.
 A transaction is the propagation of one or more changes to the database. For example, if
you are inserting a record or updating a record or deleting a record from the table, then
you are performing transaction on the table. It is important to control transactions to
ensure data integrity and to handle database errors.
 The rule of transaction tells that either all the statements in the transaction should be
execute successfully or none of those statement to be executed.

To manage transaction we have provide with transaction control language that provides a commands
like
 BEGIN TRANSACTION
 COMMIT
 ROLLBACK
 SAVE POINT

BEGIN TRANSACTION:
Begin Transaction command is used to start the transaction. Begin Transaction with name is
used to add nested transactions.
Syntax:
Begin transaction
<Write Statements>
COMMIT:
Commit command is used to end the transaction and save the data permanent part of the
database (or) it is used to make the transaction is permanent so we cannot undo or recall the
records.
 Commit is used for saving the data that has been changed permanently because
whenever you perform any DML (Data Manipulation Language) like UPDATE, INSERT OR
DELETE then you are required to write Commit at the end of all or every DML operation
in order to save it permanently.
 If you do not write Commit then your data will be restored into its previous condition.

Syntax:
Begin Transaction
Ex:
<Write Statements>
Commit
BEGIN TRANSACTION
INSERT INTO EMPLOYEE VALUES(105,'KAMAL',62000,'MUMBAI')
INSERT INTO EMPLOYEE VALUES(106,'SUJATHA',82000,'DELHI')
COMMIT
 The above records are stored permanently into a table because we commited that
[Link] we cannot roll back in to its previous position.

ROLLBACK:
Rollback command is used to undo the transactions and gets back to the initial state where
transaction started.
 Whereas if you want to restore your data into its previous condition then you can write
Rollback at any time after the DML queries has been written but remember once
Commit has been written then you cannot rollback the data.
 Moreover you can only rollback the DML queries that have been written after the last
commit statement. The concept of commit and rollback is designed for data consistency
because many users manipulate data of the same table, using the same database so the
user must get updated data.

That is why commit and rollback are used.

Syntax:
Ex:
Begin Transaction
Rollback
BEGIN TRANSACTION
DELETE FROM EMPLOYEE WHERE EID=105
DELETE FROM EMPLOYEE WHERE EID=106
BEGIN TRANSACTION
ROLLBACK
 The above records we can rollback into a table because those records are not commited.

SAVEPOINT:
Save point is used for dividing (or) breaking a transaction into
multiple units. So that user will have a chance of roll backing a transaction up to a
location.
 When a user sets a save point with in a transaction the save point defines a location to
which a transaction can return if part of the transaction conditionally canceled.
 If a transaction is roll back to a save point, it must be proceed to completion of the
transaction with commit statement or it must be cancelled altogether by rolling the
transaction back to its beginning

Syntax:
Begin Transaction
Save transaction < transaction name>
<Write Statements>
Ex:
BEGIN TRANSACTION
UPDATE EMPLOYEE SET SALARY=99000 WHERE EID=101
UPDATE EMPLOYEE SET SALARY=88000 WHERE EID=102
SAVE TRANSACTION S1
UPDATE EMPLOYEE SET SALARY=77000 WHERE EID=103
UPDATE EMPLOYEE SET SALARY=66000 WHERE EID=104
SAVE TRANSACTION S2
UPDATE EMPLOYEE SET SALARY=55000 WHERE EID=105
UPDATE EMPLOYEE SET SALARY=44000 WHERE EID=106
 In the above case we are dividing or breaking the transaction into three units. So we
have a chance of rollbacking either completely i.e six statements get roll back (or) roll
back save point S1 i.e four statements(103 to 106) (or) rollback save point S2 i.e two
records (105,106 ) only

CASE 1:
BEGIN TRANSACTION
ROLLBACK
 All records will roll back i.e complete records(six records)

CASE 2:
BEGIN TRANSACTION
ROLLBACK TRANSACTION S1
 We can roll back four records only i.e 103 to 106.

CASE 3:
BEGIN TRANSACTION
ROLLBACK TRANSACTION S2
 We can roll back two records only i.e 105 and 106

SQL KT [Link]