SQL Fundamentals
1. Introduction:
a. Database:
Database is a structured collection of data that is organized in a way that makes it easy to manage, access and update.
It is designed to efficiently store and retrieve information.
Types:
Relational Database
No SQL Database
Network Database
Hierarchical Database, etc..
b. DBMS (DATABASE MANAGEMENT SYSTEM)
It is a software that provides an interface for interacting with databases and managing the data stored in them.
Designed to manage large amount of data.
Why DBMS?
Consider a university that needs to manage information about students, courses, and grades.
The DBMS would allow the university to create a database with separate tables for students, courses, and grades.
c. RDBMS :
RDBMS stands for Relational Database Management System
It is a type of database management system that organizes data into tables with rows and columns, and establishes relationships between the tables.
In an RDBMS, data is stored in tables where each table consists of rows and columns. Each row in a table is a record, and each column represents a specific attribute of that record.
Components of RDBMS :
2. SQL :
SQL stands for Query Language.
SQL is used for various tasks related to database management, such as creating and modifying database schemas, inserting, updating, and deleting records, and querying and retrieving data from databases.
Types of commands in SQL :
There are 5 types of commands in SQL, they are
1.DDL :
Data Types:
DDL used for
NOTE:
Before creating tables in the database, you must initially create and choose the specific database. Utilize the following syntax to select the database:
Create Database DatabaseName; Use DatabaseName ;
|
TASK:
|
2.DML :
INSERT :
Syntax: INSERT INTO tableName VALUES(value1,value2,value3,…,);
Example:
ID |
Student_Name |
DOB |
Age |
Department |
Mobile_NO |
Location |
101 |
Joe |
2002-03-26 |
21 |
CSE |
1234567891 |
Tenkasi |
102 |
Michael |
2003-04-06 |
20 |
BCA |
9283874190 |
Tirunelveli |
103 |
Mohammed |
2001-03-21 |
22 |
ECE |
9283927419 |
Chennai |
The another way specifies both the column names and the values to be inserted
Syntax: INSERT INTO tablename (column1, column2, column3,...) VALUES (value1, value2, value3, ...);
Example:
ID |
Student_Name |
DOB |
Age |
Department |
Mobile_NO |
Location |
101 |
Joe |
2002-03-26 |
21 |
CSE |
1234567891 |
Tenkasi |
102 |
Michael |
2003-04-06 |
20 |
BCA |
9283874190 |
Tirunelveli |
103 |
Mohammed |
2001-03-21 |
22 |
ECE |
9283927419 |
Chennai |
UPDATE:
The UPDATE statement in SQL is used to modify existing records in a table. It allows you to change the values of one or more columns in a specific row or set of rows based on a specified condition.
ID |
Student_Name |
DOB |
Age |
Department |
Mobile_NO |
Location |
101 |
Joe |
2002-03-26 |
21 |
CSE |
1234567891 |
Surandai |
102 |
Michael |
2003-04-06 |
20 |
BCA |
9283874190 |
Surandai |
103 |
Mohammed |
2001-03-21 |
22 |
ECE |
9283927419 |
Surandai |
WHERE Condition: Specifies the condition that determines which rows to update. If you omit the WHERE clause, all rows in the table will be updated.
ID |
Student_Name |
DOB |
Age |
Department |
Mobile_NO |
Location |
101 |
Joe |
2002-03-26 |
21 |
CSE |
1234567891 |
Tenkasi |
102 |
Michael |
2003-04-06 |
20 |
BCA |
9283874190 |
Tirunelveli |
103 |
Mohammed |
2001-03-21 |
22 |
ECE |
9283927419 |
Surandai |
DELETE:
The DELETE statement in SQL is used to remove one or more rows from a table based on a specified condition.
ID |
Student_Name |
DOB |
Age |
Department |
Mobile_NO |
Location |
101 |
Joe |
2002-03-26 |
21 |
CSE |
1234567891 |
Tenkasi |
103 |
Mohammed |
2001-03-21 |
22 |
ECE |
9283927419 |
Surandai |
When using the DELETE statement in SQL without a WHERE clause, it deletes all rows from the specified table. This operation removes all data in the table, effectively emptying the entire table.
Syntax: DELETE FROM Tablename
Example: DELETE FROM Student;
3.DQL:
Syntax:
If you want to select all the fields available in the table, use the following syntax
ID |
Student_Name |
DOB |
Age |
Department |
Mobile_NO |
Location |
101 |
Joe |
2002-03-26 |
21 |
CSE |
1234567891 |
Tenkasi |
102 |
Michael |
2003-04-06 |
20 |
BCA |
9283874190 |
Tirunelveli |
103 |
Mohammed |
2001-03-21 |
22 |
ECE |
9283927419 |
Chennai |
To select the particular column, have to use the below syntax,
Student_Name |
Age |
Joe |
21 |
Michael |
20 |
Mohammed |
22 |
TASK:
|
WHERE CLAUSE:
Types Of WHERE Clause Predicates:
1.Comparison Operators:
A. Equality Operator (=):
SELECT * FROM EMPLOYEE WHERE ID = 1001;
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
15,000 |
B. Not Equality Operator (!=,<>):
SELECT * FROM EMPLOYEE WHERE ID != 1005
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
15,000 |
1001 |
Bavani |
34 |
20,000 |
1002 |
Sivasabarish |
29 |
35,000 |
1003 |
Nisha |
34 |
10,000 |
1004 |
Meeto |
48 |
12,000 |
1006 |
Shivanya |
26 |
22,000 |
C. GREATER THAN (<):
SELECT * FROM EMPLOYEE WHERE Salary>20,000
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
15,000 |
1001 |
Bavani |
34 |
20,000 |
1002 |
Sivasabarish |
29 |
35,000 |
1003 |
Nisha |
34 |
10,000 |
1004 |
Meeto |
48 |
12,000 |
1005 |
Dhanu |
22 |
23,000 |
1006 |
Shivanya |
26 |
22,000 |
D. LESS THAN (>):
SELECT * FROM EMPLOYEE WHERE Salary<15,000
Employee_ID |
Employee_Name |
Age |
Salary |
1003 |
Nisha |
34 |
10,000 |
1004 |
Meeto |
48 |
12,000 |
E. GREATER THAN OR EQUAL TO (>=)
SELECT * FROM EMPLOYEE WHERE Salary>=15,000
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
15,000 |
1001 |
Bavani |
34 |
20,000 |
1002 |
Sivasabarish |
29 |
35,000 |
1005 |
Dhanu |
22 |
23,000 |
1006 |
Shivanya |
26 |
22,000 |
F. GREATER THAN OR EQUAL TO (>=)
SELECT * FROM EMPLOYEE WHERE Salary<=15,000
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
15,000 |
1003 |
Nisha |
34 |
10,000 |
1004 |
Meeto |
48 |
12,000 |
2.Logical Operator :
Team |
Employee_Name |
Age |
Salary |
Team C |
Maha |
42 |
15,000 |
Team A |
Bavani |
34 |
20,000 |
Team C |
Sivasabarish |
29 |
35,000 |
Team A |
Nisha |
34 |
10,000 |
Team B |
Meeto |
48 |
15,000 |
Team C |
Dhanu |
22 |
23,000 |
Team B |
Shivanya |
26 |
15,000 |
The AND operator is a logical operator used to combine multiple conditions in a WHERE clause.
Team |
Employee_Name |
Age |
Salary |
Team A |
Bavani |
34 |
20,000 |
Team A |
Nisha |
34 |
10,000 |
The OR operator is a logical operator used to combine multiple conditions in a WHERE clause.
Team |
Employee_Name |
Age |
Salary |
Team C |
Maha |
42 |
15,000 |
Team B |
Meeto |
48 |
15,000 |
Team B |
Shivanya |
26 |
15,000 |
The NOT operator is a logical operator used to negate a condition in a WHERE clause.
Team |
Employee_Name |
Age |
Salary |
Team A |
Bavani |
34 |
20,000 |
Team C |
Sivasabarish |
29 |
35,000 |
Team A |
Nisha |
34 |
10,000 |
Team C |
Dhanu |
22 |
23,000 |
3.Pattern Matching:
LIKE:
i) Percentage Symbol (%)
ii) Under Score Symbol (_)
The Percentage Symbol represent zero, one or more than one characters.
Retrieve Employee_Name starting with the letter S from the EMPLOYEE table:
Employee_ID |
Employee_Name |
Age |
Salary |
1002 |
Sivasabarish |
29 |
35,000 |
1006 |
Shivanya |
26 |
22,000 |
Retrieve Employee_Name END with the letter ha from the Employees table
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
15,000 |
1003 |
Nisha |
34 |
10,000 |
_
represents only one character.
Student_Detail:
Name |
College |
Year |
Maha |
JPCOE |
2023 |
Malar |
CKEC |
2022 |
Anitha |
FXEC |
2023 |
Priya |
PASC |
2021 |
Devi |
JPCOE |
2022 |
Arun |
TEC |
2020 |
Birundha |
CKEC |
2021 |
‘_A’:
If you want to retrieve all records from a table where the values in a specific column start with the letter 'A'.
Consider the Student_Detail table, here we retrieve all records from a table where the values in a Name column that not start with letter ‘M’.The Query is,
Name |
College |
Year |
Anitha |
FXEC |
2023 |
Priya |
PASC |
2021 |
Devi |
JPCOE |
2022 |
Arun |
TEC |
2020 |
Birundha |
CKEC |
2021 |
‘A_’:
If you want to retrieve all records from a table where the values in a specific column ends with the letter ‘A’.
Consider the Student_Detail table,here we retrieve all records from a table where the values in a year column that end with number 23.The Syntax and Query is,
Name |
College |
Year |
Maha |
JPCOE |
2023 |
Anitha |
FXEC |
2023 |
‘_A_’:
If you want to find records where 'A' is in the middle of a character string.
Consider the Student_Detail table,here we retrieve all records from a table where the values in a College column that not find the middle of the letter ‘C’. The Syntax and Query is,
Name |
College |
Year |
Maha |
JPCOE |
2023 |
Devi |
JPCOE |
2022 |
4.RANGE:
The BETWEEN operator is utilized to filter values that fall within a specified range. This range can encompass numerical values, strings, or dates.
Name |
College |
Year |
Malar |
CKEC |
2022 |
Anitha |
FXEC |
2023 |
Priya |
PASC |
2021 |
Devi |
JPCOE |
2022 |
Arun |
TEC |
2020 |
ESCAPE CHARACTER:
The ESCAPE keyword is used to escape pattern matching characters such as the percentage (%) and underscore (_) if they form part of the data.
For example, movies like 100% Love has the % symbol in its title, if we try to search the name of such movies using wildcard filtering the % in the title would be treated as wildcard. To make such characters treated as string, we use the ESCAPE keyword.
Text_Table:
ID |
Text |
1001 |
John scored 80% on his test, showing a solid understanding of the material. |
1002 |
The weather forecast indicates a possibility of rain tomorrow. |
1003 |
All the cookies baked at the bakery were sold within an hour. |
1004 |
Sally saved 20% of her allowance to buy a new toy. |
1005 |
Tom saved a fifth of his allowance to buy a new video game. |
1006 |
The bakery sold 90% of its freshly baked cookies within an hour. |
1007 |
Tom saved a fifth of his allowance to buy a new video game. |
consider the text table, here we retrieve the % sentence in the text column from the table
SELECT * FROM Text_Table WHERE Text LIKE '%\%%' ESCAPE '\';
ID |
Text |
1001 |
John scored 80% on his test, showing a solid understanding of the material. |
1004 |
Sally saved 20% of her allowance to buy a new toy. |
1006 |
The bakery sold 90% of its freshly baked cookies within an hour. |
TASK:
|
AGGREGATE FUNCTION :
An aggregate function is a function that performs a calculation on a set of values and returns a single, summarized result. These functions are often used with the SELECT statement to perform operations on data in a database. The Aggregate Function listed below,
Here is the example employee table for aggregated function
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
15,000 |
1001 |
Bavani |
34 |
20,000 |
1002 |
Sivasabarish |
29 |
35,000 |
1003 |
Nisha |
34 |
10,000 |
1004 |
Meeto |
48 |
12,000 |
1005 |
Dhanu |
22 |
23,000 |
1006 |
Shivanya |
26 |
22,000 |
SUM (): Calculates the sum of values in a specified column.
OUTPUT:
TotalSalary |
1,37,000 |
MAX ():Calculates the Maximum of values in a specified column.
OUTPUT:
MaximumAge |
48 |
MIN (): Calculates the Minimum of values in a specified column.
OUTPUT:
MinimumAge |
22 |
AVG (): Calculates the Average of values in a specified column.
OUTPUT:
AverageSalary |
19,571 |
COUNT (): Count the values in a specified column.
OUTPUT:
TotalEmployee |
7 |
Group By:
The GROUP BY clause is used to group rows in a table based on the values in one or more columns. It is often used in conjunction with aggregate functions, such as SUM, COUNT, AVG, MIN, or MAX, to perform calculations on each group of rows.
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
15,000 |
1001 |
Bavani |
34 |
20,000 |
1002 |
Sivasabarish |
28 |
15,000 |
1003 |
Nisha |
34 |
10,000 |
1004 |
Meeto |
28 |
12,000 |
1005 |
Dhanu |
22 |
12,000 |
1006 |
Shivanya |
43 |
12,000 |
Output:
Age |
Age_count |
42 |
1 |
34 |
2 |
28 |
2 |
22 |
1 |
43 |
1 |
ORDER BY:
The ORDER BY clause is used to sort the result set of a query in a specified order.
Example: Order the number of Age for each customer in ascending order:
Output:
Employee_Name |
Age |
Maha |
42 |
Shivanya |
43 |
Bavani |
34 |
Nisha |
34 |
Sivasabarish |
28 |
Meeto |
28 |
Dhanu |
22 |
TASK 1:
Create a table name as PRODUCTS with columns for ID, Product Name, Price, Location. Insert 10 Product Detail into the PRODUCTS Table. Calculate the total Price and average price per product category, and display the results in descending order of total sales.
|
TASK 2:
Create a table name as CUSTOMER with columns for Name, Order_ID, Price, Location. Insert 10 customer Detail into the Customer Table. Find the number of orders and the maximum order amount for each customer, and display the results in ascending order of customer names. |
Having Clause:
The HAVING clause was introduced in SQL to enable filtering based on conditions involving aggregate functions, which cannot be directly applied using the WHERE keyword.
Example:
Find the products that have generated a total revenue greater than 1000.
Product_ID |
Quantity |
Revenue |
1 |
10 |
500 |
2 |
50 |
2000 |
3 |
20 |
900 |
4 |
82 |
3000 |
5 |
25 |
1000 |
6 |
97 |
800 |
7 |
12 |
1500 |
Query:
SELECT Product_id, SUM(Revenue) AS total_revenue FROM sales GROUP BY Product_id HAVING SUM(Revenue) > 1000;
Output:
Product_ID |
Quantity |
Revenue |
2 |
50 |
2000 |
4 |
82 |
3000 |
7 |
12 |
1500 |
Joins: A JOIN operation is used to combine rows from two or more tables based on a related column between them. Joins allow you to retrieve data from multiple tables in a single query by specifying how the tables are related. Common types of joins include
Employee
EmpID |
EmpName |
DepID |
1001 |
Dhanu |
104 |
1002 |
Shivanya |
105 |
1003 |
Nisha |
104 |
1004 |
Meeto |
107 |
Department
DepID |
DepName |
108 |
EEE |
107 |
ECE |
104 |
IT |
An inner join in a relational database retrieves only the rows from two tables that have matching values in a specified column, excluding unmatched rows, resulting in a set of intersecting data.
Example:
Consider the above employee and department table. Using an inner join, you can retrieve employees along with their corresponding department names based on the common DepID.
Query:
Select Employee.EmpID, Employee.EmpName, Department.DepName from Employee INNER JOIN Department ON Employee.DepID=Department.DepID;
Output:
EmpID |
EmpName |
DepName |
1001 |
Dhanu |
IT |
1003 |
Nisha |
IT |
1004 |
Meeto |
ECE |
Left Outer Join:
A left outer join is a type of relational database operation that combines rows from two tables based on a related column, including all rows from the "left" table (the table specified first in the query) and only matching rows from the "right" table. If there's no match in the right table, NULL values are used for the columns from that table in the result set.
Query:
Select Employee.EmpID, Employee.EmpName, Department.DepName from Employee LEFT OUTER JOIN Department ON Employee.EmpID=Department.DepID.
EmpID |
EmpName |
DepName |
1001 |
Dhanu |
IT |
1002 |
Shivanya |
Null |
1003 |
Nisha |
IT |
1004 |
Meeto |
ECE |
Right Outer Join:
A right outer join is a type of relational database operation that combines rows from two tables based on a related column, including all rows from the "right" table (the table specified second in the query) and only matching rows from the "left" table. If there's no match in the left table, NULL values are used for the columns from that table in the result set.
Query:
Select Employee.EmpId, Employee.EmpName, Department.DepName from Employee RIGHT OUTER JOIN Department ON Employee.DepID=Department.DepID;
EmpID |
EmpName |
DepName |
1001 |
Dhanu |
IT |
1003 |
Nisha |
IT |
1004 |
Meeto |
ECE |
NULL |
NULL |
EEE |
FULL OUTER JOIN:
A full outer join is a type of relational database operation that combines rows from two tables based on a related column, including all rows from both tables. It returns a result set that includes all rows from both tables, matching rows from both tables where available, and NULL values where there is no match. Essentially, it combines the results of both left and right outer joins.
Query:
Select Employee.EmpID, Employee.EmpName, Department.DepName from Employee FULL OUTER JOIN Department ON Employee.DepID=Department.DepID.
EmpID |
EmpName |
DepName |
1001 |
Dhanu |
IT |
1003 |
Nisha |
IT |
1004 |
Meeto |
ECE |
1002 |
Shivanya |
Null |
Null |
Null |
EEE |
Task:
Retrieve a list of customers along with their orders.
Tables involved:
Customers: Contains information about customers such as customer_id, name, email, etc.
Orders: Contains information about orders such as order_id, customer_id , Product Name, order_date, etc.
COMPOUND SEARCH CONDITIONS:
A compound search condition in SQL involves combining multiple conditions in a WHERE clause using logical operators such as AND, OR, and NOT.
Example: EMPLOYEE TABLE
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
15,000 |
1001 |
Bavani |
34 |
20,000 |
1002 |
Sivasabarish |
29 |
35,000 |
1003 |
Nisha |
34 |
10,000 |
1004 |
Meeto |
48 |
12,000 |
1005 |
Dhanu |
22 |
23,000 |
1006 |
Shivanya |
26 |
22,000 |
Employee_ID |
Employee_Name |
Age |
Salary |
1001 |
Bavani |
34 |
20,000 |
Employee_ID |
Employee_Name |
Age |
Salary |
1001 |
Bavani |
34 |
20,000 |
1003 |
Nisha |
34 |
10,000 |
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
15,000 |
1002 |
Sivasabarish |
29 |
35,000 |
1004 |
Meeto |
48 |
12,000 |
1005 |
Dhanu |
22 |
23,000 |
1006 |
Shivanya |
26 |
22,000 |
TASK:
|
MISSING DATA:
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
|
1001 |
Bavani |
34 |
20,000 |
1002 |
Sivasabarish |
29 |
35,000 |
1003 |
Nisha |
34 |
|
1004 |
Meeto |
48 |
12,000 |
1005 |
Dhanu |
22 |
23,000 |
1006 |
Shivanya |
26 |
22,000 |
IS NULL:
Employee_ID |
Employee_Name |
Age |
Salary |
1000 |
Maha |
42 |
|
1003 |
Nisha |
34 |
|
IS NOT NULL:
Employee_ID |
Employee_Name |
Age |
Salary |
1001 |
Bavani |
34 |
20,000 |
1002 |
Sivasabarish |
29 |
35,000 |
1004 |
Meeto |
48 |
12,000 |
1005 |
Dhanu |
22 |
23,000 |
1006 |
Shivanya |
26 |
22,000 |
Numeric Function:
Numeric functions in SQL perform operations on Integers.
Numeric Function Includes,
Numeric Function |
Definition |
Example |
ABS () |
Abs() Function Returns the absolute value of the number.
|
Select abs(-314.7)
Output: 314.7 |
Power () |
The Power() function is used to raised a number to the power of another number.
|
Select power (5,5)
Output: 3125. |
Ceil () |
The CEILING() function is used to rounds a numeric value up to the nearest integer. |
Select Ceil( 110.59)
Output: 111. |
Floor () |
The FLOOR() function is used to rounds a numeric value down to the nearest integer |
Select Floor(110.59)
Output:110. |
Round () |
The ROUND() function is used to round a numeric value to a specified number of decimal places or to the nearest integer. |
Select Round(28.568,2)
Output: 28.58 |
Truncate () |
The TRUNCATE() function is used to truncate a numeric value to a specified number of decimal places, removing the digits beyond the specified precision. |
Select Truncate(28.568,2)
Output: 28.56 |
Sqrt () |
The SQRT() function is used to calculate the square root of a given numeric value. |
Select Sqrt (81)
Output: 9. |
Mod () |
The MOD() function is used to return the remainder of a division operation between two numbers. |
Select Mod (81,8)
Output: 1 |
Exp() |
The EXP() function is used to calculate the exponential value of a given numeric expression. It returns the value of e raised to the power of the specified numeric expression. |
Select Exp (5)
Output: 148.4131591 |
Sign () |
The SIGN() function in SQL is used to determine the sign of a numeric expression. It returns:
|
Select Sign (2) Output: 1 Output: 0 |
Rand() |
The RAND() function is used to generate a random floating-point value between 0 and 1. It returns a random number each time it is called within a query. |
Select Rand()
Output: 0.234 |
STRING FUNCTION:
SQL string functions are built-in operations for manipulating character data (strings) in databases or input. They handle tasks like extracting substrings, changing case, concatenating strings, pattern searching, character replacement, and whitespace trimming.
Each character is assigned a unique numeric value.
Ex:
Upper Case Letter (A to Z) start from 65 to 90.
Lower Case Letter (a to z) start from 97 to 12
Here are some common string function used in sql,
ASCII(), LEN(), CONCAT(), CONCAT_WS(), LEFT(), LOWER(), LTRIM(),
RTRIM(), TRIM(), RIGHT,REPLACE(), SUB_STRING(),
Example: Select ASCII (‘y’);
Output: 89
Example: Select LEN(‘Query’);
Output: 5
Concatenation Function is used to add two or more STRING together.
ID |
First_Name |
Last_Name |
1 |
Maha |
Lakshmi |
2 |
Inigo |
Cathrin |
3 |
Siva |
Sabarish |
OUTPUT:
ID |
FullName |
1 |
Maha _Lakshmi |
2 |
Inigo _Cathrin |
3 |
Siva _Sabarish |
OUTPUT:
ID |
FullName |
1 |
MahaLakshmi |
2 |
InigoCathrin |
3 |
SivaSabarish |
This Function is used to add two words or strings with a symbol as concatenating symbol.
Example: Select CONCAT_WS(‘_’,’Structured’,’Query’,’Language’);
Output: Structured_Query_Language.
This function is used to SELECT a sub string from the left of given size or characters.
Example: Select LEFT (‘Structured Query Language’,12);
Output: Structured Q.
This function is used to convert a uppercase word into lowercase.
Example: Select LOWER (‘STRUCTURED QUERY LANGUAGE’)
Output: structured query language.
It is used to removes the starting spaces from a string.
Example: Select LTRIM(‘ Hello ’);
Output: Hello .
It is used to removes the ending spaces from a string.
Example: Select RTRIM(‘ Hello ‘)
Output: Hello
It is used to removes the starting and ending space from a string.
Example: Select TRIM(‘ Hello ‘)
Output: Hello
The REPLACE function is used to remove a specified substring from a given string by replacing it with an empty string.
Example: Select REPLACE (‘Data Definition Language’, ‘Definition’,’Manipulation’);
Output: Data Manipulation Language.
The RIGHT Function is used to select a sub string from the right end of the given size.
Example: Select RIGHT (‘Structured Query’,7)
Output: d Query
substring used to extract certain part of a given string from given position
Index of a String:
L |
A |
N |
G |
U |
A |
G |
E |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
Syntax:Select SUBSTRING (String, String Index, Length)
Example:
CAST:Cast Function is used to convert the expression of one data type into another data type.
Convert:
Convert Function is also same as Cast Function. But the syntax of Convert Function is different.
Syntax: Select CONVERT (Data type, Expression, Style code)
The STYLE argument in the CONVERT function specifies the format style used for formatting date and time values in SQL Server. This argument is optional and can be used when converting datetime or smalldatetime values to character data types. Here are some common style codes,
Example:
Window Function:
A window function in SQL is a type of function that operates on a set of rows, known as a "window," within a result set.
Unlike traditional aggregate functions like SUM or AVG, which collapse multiple rows into a single result, window functions perform calculations on a subset of rows while maintaining the original row count and structure of the result set.
Syntax:
SELECT column1, column2, window_function(column3) OVER ( [PARTITION BY partition_column] [ORDER BY order_column1 [ASC | DESC] As Result Column FROM table_name;
OVER:
In SQL, the OVER clause is used in conjunction with window functions to define the window or set of rows over which the function operates.
The OVER clause allows you to specify partitioning, ordering, and framing options for the window function.
PARTITION BY:
This clause divides the result set into partitions or groups based on the specified column(s). The window function is applied independently within each partition. If omitted, the entire result set is treated as a single partition.
Types Of Window Function:
Aggregate Window Function:
These functions perform aggregate calculations over a window of rows.
Examples include SUM(), AVG(), COUNT(), MIN(), and MAX().
They calculate values such as the sum, average, count, minimum, or maximum within the window.
Date |
Region |
Sales_Amount |
2024-01-01 |
East |
1000 |
2024-01-02 |
East |
1500 |
2024-01-03 |
West |
1200 |
2024-01-04 |
East |
800 |
2024-01-05 |
West |
2000 |
2024-01-06 |
West |
700 |
1.SUM ():
As a window function, SUM() calculates a "running total" or "cumulative sum" of a column's values within a specified window of rows, without collapsing rows into a single result.
Example:
calculate the running total of sales for each region, ordered by date.
Date |
Region |
Sales_Amount |
Running_Sales_Amount |
2024-01-01 |
East |
1000 |
1000 |
2024-01-02 |
East |
1500 |
2500 |
2024-01-04 |
East |
800 |
3300 |
2024-01-03 |
West |
1200 |
1200 |
2024-01-05 |
West |
2000 |
3200 |
2024-01-06 |
West |
700 |
3900 |
MAX():
Example:
Find the maximum sales amount for each region.
Date |
Region |
Sales_Amount |
MaxSalesAmount |
2024-01-01 |
East |
1000 |
1500 |
2024-01-02 |
East |
1500 |
1500 |
2024-01-04 |
East |
800 |
1500 |
2024-01-03 |
West |
1200 |
2000 |
2024-01-05 |
West |
2000 |
2000 |
2024-01-06 |
West |
700 |
2000 |
Count():
Example:
Count the region using Aggregate window count function
Date |
Region |
Sales_Amount |
Reg |
2024-01-01 |
East |
1000 |
3 |
2024-01-02 |
East |
1500 |
3 |
2024-01-04 |
East |
800 |
3 |
2024-01-03 |
West |
1200 |
3 |
2024-01-05 |
West |
2000 |
3 |
2024-01-06 |
West |
700 |
3 |
Ranking Window Function :
A ranking function is used to assign a rank to each row in the result set based on specified criteria.
Ranking Functions are, ROW_NUMBER(), RANK(), DENSE_RANK().
ROW_NUMBER()
This function assigns a unique sequential integer to each row within the partition of a result set, without any gaps in the numbering.
Date |
Region |
Sales_Amount |
RowNum |
2024-01-01 |
East |
1000 |
1 |
2024-01-02 |
East |
1500 |
2 |
2024-01-04 |
East |
800 |
3 |
2024-01-03 |
West |
1200 |
4 |
2024-01-05 |
West |
2000 |
5 |
2024-01-06 |
West |
700 |
6 |
RANK():
This function assigns a unique rank to each distinct row within the partition of a result set, with the same rank given to rows with equal values. If two rows have the same value, they receive the same rank, and the next rank is skipped.
Example:
Date |
Region |
Sales_Amount |
2024-01-01 |
East |
1500 |
2024-01-02 |
East |
1500 |
2024-01-03 |
West |
1200 |
2024-01-04 |
East |
800 |
2024-01-05 |
West |
2000 |
2024-01-06 |
West |
700 |
Date |
Region |
Sales_Amount |
Ranked |
2024-01-01 |
East |
1500 |
1 |
2024-01-02 |
East |
1500 |
1 |
2024-01-04 |
East |
800 |
3 |
2024-01-03 |
West |
1200 |
1 |
2024-01-05 |
West |
2000 |
2 |
2024-01-06 |
West |
700 |
3 |
Dense_Rank():
Similar to the RANK() function, but it assigns consecutive ranks to distinct rows within the partition of a result set, without any gaps. If two rows have the same value, they receive the same rank, and the next rank is not skipped.
Date |
Region |
Sales_Amount |
Den_Ranked |
2024-01-01 |
East |
1500 |
1 |
2024-01-02 |
East |
1500 |
1 |
2024-01-04 |
East |
800 |
2 |
2024-01-03 |
West |
1200 |
1 |
2024-01-05 |
West |
2000 |
2 |
2024-01-06 |
West |
700 |
3 |