1.DDL :

• DDL stands for Data Definition Language.
• To establish and manage a table, having a database is essential.    
• When specifying columns for a table, it's crucial to provide the following details:

• The names of the columns,
• The data types (such as integer, floating point, string, etc.),

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 and Select a Database : Create Database DatabaseName; Use DatabaseName ;

 

TASK:

Create a table name as EMPLOYEE with columns for Employee_ID, Employee_Name, Date Of Birth, Department. ADD a new column Salary to the EMPLOYEE Table. Change the Data type of Employee_ID column from int to Varchar(). RENAME the column name Date Of Birth to DOB. Drop the employee table from the database.

 

2.DML :

• DML stands for Data Manipulation Language.
• DML operations allow you to INSERT, UPDATE, and DELETE data in database tables.

INSERT :

• The INSERT Operation is used to insert new row in a table.
• It is possible to write INSERT operation in Two Ways,
• The First Way is, to insert values can be in the following manner where we do not use the column names.

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:

• DQL Stands for Data Query Language and it is a subset of SQL (Structured Query Language) that is focused on retrieving and querying data from a database.
• DQL is primarily concerned with the SELECT statement, which is used to extract data from one or more tables in a database.

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:

Create a new database name as ‘EmployeeDetails’ and choose the Database. Create a table name as EMPLOYEE with columns for Employee_ID, Employee_Name, Date Of Birth, Department, Salary, Mobile_No. Insert a 10 Employee detail into the EmployeeDetails Table. Update the age of the employee with ID 1 to 32. Delete the employee with ID 1 from the " EMPLOYEE " table. Retrieve all records from the " EMPLOYEE " table. Retrieve the Employee_ID, Employee_Name of all employees from the " EMPLOYEE " table.

 

 

WHERE CLAUSE:

• The WHERE clause in SQL is used to filter the results of a SELECT, UPDATE, or DELETE statement based on a specified condition.
• It allows you to retrieve, modify, or delete only the rows that meet certain criteria.

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

 

1. AND:

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

                    

1. OR:

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

 

1. NOT:

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:

• LIKE Clause is used to Perform Pattern Matching in SQL.
• The LIKE clause in SQL is used to search for patterns within the values of a table. If a match is found between the specified pattern in the query and the values in the table, the corresponding rows are retrieved.
• The LIKE clause in SQL  can be applied to both string and numeric data types.
• In an SQL query, a WHERE clause is commonly used in conjunction with a LIKE clause. The LIKE clause allows you to search for a specified pattern within the values of a column, and when combined with WHERE, it helps filter and retrieve specific rows based on the matching pattern.

 

• The two primary wildcard characters used in LIKE Clause are

i) Percentage Symbol (%)

          ii) Under Score Symbol (_)

 

1. Percentage 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

 

1. Under Score Symbol :

• The UnderScore symbol represents  a SINGLE CHARACTER
• It can be any character or number, but each _ represents only one character.
• _ can be used either in the first place, in the last or at both side of the string. (‘_A’,’A_’,’_A_’)

 

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:

Write a SQL query to retrieve all employees whose salary is greater than 50,000.   Retrieve the orders placed between a specific date range.   Find all customers who do not have a specified email address in the database.   Write a query to select products where the price is less than $100 and the quantity in stock is greater than 50.   Retrieve all employees whose last name starts with 'S'.   Select all orders from customers located in either 'New York' or 'California'.   Find all products that are not in the 'Electronics' category.   Retrieve all customers whose names contain the word 'John' regardless of the position.   Write a query to select customers who have made a purchase in the last 30 days and spent more than $100.   Retrieve all products with a price between $20 and $50.

 

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

 

1. Inner Join:

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.

1. Write a SQL query to retrieve all customers along with their orders, ensuring that all customers are included in the result, even if they have no associated orders.

 

1. Write a SQL query to retrieve all orders along with their customers, ensuring that all customers are included in the result, even if they have no associated orders.

 

1. Write a SQL query to retrieve all customers along with their orders.

 

1. Write a SQL query to retrieve all customers along with their orders, including customers with no orders and orders with no associated customers.

 

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:

Create a table name as Student_Details with columns for ID, Name, Age, Department, Location, Mobile_No. Insert a 10 Students detail into the Student_Details Table. Retrieve students from the 'Computer Science' department who are 21 years old. Retrieve students from either 'Electrical Engineering' department OR 'Physics' department. Retrieve students who are NOT from the 'Chemistry' department. Retrieve students who are NOT from the 'English' department AND are younger than 22.

 

MISSING DATA:

• The SQL NULL is the term is used to represent a MISSING Values.
• A NULL value in a table is a value in a field that appears to be blank.
• You must use the IS NULL or IS NOT NULL operators to check for a NULL value

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:   -1 if the numeric expression is negative, 0 if the numeric expression is zero, and 1 if the numeric expression is positive.	Select Sign (2) Output: 1 Select sign (-2) output:-1 Select sign (0) 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(),

 

1. ASCII: This Function is used to find the ASCII value of a character.

Example: Select ASCII (‘y’);

Output: 89

1. LEN(): This Function is used to find the Length of the String.

Example: Select LEN(‘Query’);

Output: 5

1. CONCATENATION :

     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

 

1. CONCAT_WS:

          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.

 

1. LEFT:

           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.

 

1. LOWER:

          This function is used to convert a uppercase word into lowercase.

 

Example: Select LOWER (‘STRUCTURED QUERY LANGUAGE’)

Output: structured query language.

 

 

 

 

1. LRTIM:

          It is used to removes the starting spaces from a string.

Example: Select LTRIM(‘   Hello   ’);

Output: Hello   .

 

1. RTRIM:

          It is used to removes the ending spaces from a string.

Example: Select RTRIM(‘    Hello    ‘)

Output:    Hello

 

1. TRIM:

          It is used to removes the starting and ending space from a string.

Example: Select TRIM(‘    Hello     ‘)

Output: Hello

 

1. REPLACE:

          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.

 

1.  RIGHT:

     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

 

1. SUB STRING:

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:

1. Select SUBSTRING (‘Language’, 1, 5) à Langu
2. Select SUBSTRING (‘Language’, 8, 3) à e
3. Select SUBSTRING (‘Language’, 4, 3) à gua

 

 

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,

• 101: U.S. - mm/dd/yyyy
• 103: British/French - dd/mm/yyyy
• 120: ODBC Canonical - yyyy-mm-dd hh:mi:ss(24h)
• 126: ISO8601 - yyyy-mm-ddThh:mi:ss.mmm (no spaces)

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