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.