0% found this document useful (0 votes)

7 views203 pages

Overview of Query Languages and Algebra

Uploaded by

madhurbiradar.dev

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

7 views203 pages

Overview of Query Languages and Algebra

Uploaded by

madhurbiradar.dev

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

Unit 3 Query Languages

“A person who never made a

mistake never tried anything new."
— Albert Einstein
Query Languages

• Answer: Let BoB need $y more to buy the ticket.

• So, we will solve this problem using algebra, 3 + y = 4,

• We add y to 3 to get [Link] we need to find the value of y.

• y=4−3 y=1
Algebra
• Abstract algebra, Linear algebra, and Boolean algebra
• Abstract algebra is the set of advanced topics of algebra that deal with
abstract algebraic structures rather than the usual number systems. The most
important of these structures are groups(Closure:Identity:Inverse:
Associativity: The defined multiplication is associative, i.e., for all A,B,C in G,
(AB)C=A(BC).),rings, and fields. It's used in computer science for secure
communication, in physics to understand symmetries and conservation laws,
and in engineering to design error-correcting codes.
• Linear algebra is the study of linear combinations. It is the study of vector
spaces, lines and planes, and some mappings that are required to perform the
linear transformations. It includes vectors, matrices and linear functions.
• Boolean algebra is a branch of mathematics that deals with operations on
logical values with binary variables. Applications are digital circuit
design,pocket calculator,CD Palyer,Cell Phone and other electronic products.
Relational Algebra
• Relational algebra is a procedural query language which follows a particular
syntax with the help of which, data can be accessed and retrieved very easily
from single as well as multiple table/data sources.
• The relational algebra provides basic operations which can be performed over
single or multiple relations in order to generate new relations(single or
multiple).
• Certain operators are used to perform queries and retrieve desired results.
• These operators can perform certain operations on single attribute(called
unary operator) or multiple attribute(called binary operator).
• Types of operations in relational algebra
• 1. Basic Operations
• 2. Derived Operations
Relational Algebra
• Basic/Fundamental Operations:
• 1. Select (σ)
• 2. Project (∏)
• 3. Union ( )
• 4. Set Difference (-)
• 5. Cartesian product (X)
• 6. Rename (ρ)
• Derived Operations:
• 1. Natural Join ( )
• 2. Left, Right, Full outer join ( , , )
Relational Algebra : Operations
• Project(∏): This operation is also used to fetch all the rows/tuples/data according
to the requested attribute/Column. It means, using project operation one can
simply fetch all the tuples corresponding to a single attribute or multiple
attributes. It does not supports any conditions as select operation and is denoted
using “Pie(π)”.
• Syntax :∏column_name1,column_name2,..,column_nameN(table_name)
• Project operator in relational algebra is similar to the Select statement in SQL.
• For example : Consider the table of relation R(Roll No, Name, Age, Marks). If we
want to project the marks column, then it can be done by :
• Query Used : πMarks(Student_Details)
Relational Algebra : Operations
• Select (σ): This operation is used to fetch rows from given table or relation
on the basis of given conditions, it is denoted by “Sigma(σ)”.
• Syntax : σ Condition/Predicate(Relation/Table name)
• where clause in SQL, is used for the same purpose.
• For example : Consider the table of relation R(Roll No, Name, Age, Marks). If
we want to select the name and age of student, then it can be done by:
• Query Used : σ Age>21 (Student_Details)
AND and OR Conjunctive Operators
• The SQL AND & OR operators are used to combine multiple conditions to narrow data in
an SQL statement. These two operators are called as the conjunctive operators.
• These operators provide a means to make multiple comparisons with different operators
in the same SQL statement.
• Q: Find Names of all CUSTOMERs , where the salary is
greater than 2000 and the age is less than 25 years .
• ᴨ Name [σSalary>2000 and Age<25 (Customer)]

• Q: SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE

SALARY > 2000 OR age < 25;
• ᴨ ID,Name , Salary [σSalary>2000 Or Age<25 (Customer)]
Relational Algebra : Operations
• Union( ):In order to fetch data from two relations to generate new relation
with combined capabilities, union operations can be used. The union
operation fetches the data from both tables and projects it accordingly. It is
denoted through “Union Symbol(U)”.
• In Union Operation Both the relations compulsory to have same domain for
attributes.
• Syntax : X1 U X2 , where X1 & X2 are two different relations satisfying the
above two conditions.
• Note: The rows (tuples) that are present in both the tables will only appear
once in the union set. In short you can say that there are no duplicates
present after the union operation.
Relational Algebra : Operations
• Intersec on(∩) : Intersection operator is denoted by ∩ symbol and it is used
to select common rows (tuples) from two tables (relations).
• Lets say we have two relations R1 and R2 both have same columns and we
want to select all those tuples(rows) that are present in both the relations,
then in that case we can apply intersection operation on these two
relations R1 ∩ R2.
• Note: Only those rows that are present in both the tables will appear in the
result set. COURSE ∩ STUDENT
Relational Algebra : Operations
• Set Difference (-) : In order to fetch the data which is not present in any one
of the relation, set difference operation is used. The set difference operation
is denoted by “Minus(-)”.
• Lets say we have two relations R1 and R2 and we want to select all those
tuples(rows) that are present in Relation R1 but not present in Relation R2,
this can be done using Set difference R1 – R2.
• For example : Consider the two tables with relations X1(Name, Age) and
X2(Name, Age). If we wish to apply the set difference operation, then it can
be done by :
Examples
• 1] Retrieve details of students
who received A grade in CS101.
• 2] List names of all enrolled
students .
• 3] List the courses that are
common to both "Students" and
“Instructors“
Examples

• 1] List students whose "AGE" is 20

• 2] List all the names from STUDENT and EMPLOYEE relation.
• 3] List students those who are not EMPLOYEE and those who are
employee.
Relational Algebra : Operations
• Cartesian product (X): The Cartesian product operation will generate the
possible combinations among the tuples from the relations resulting in
table containing all the data.
• It combines the information of two or more relations in one single relation.
Cartesian product is different from union operation and is denoted by
“Cross(X)”.
Relational Algebra : Operations
• Division Operation(÷) : The division operator is used when you want to
find tuples in one relation that are associated with all tuples in another
relation. R1 ÷ R2 = tuples of R1 associated with all tuples of R2.
• e.g we have two relations: 1. Enrolled(student, course)
student course • Find all students who are enrolled in all required courses.
Alice DBMS
• Result(student) := Enrolled ÷ RequiredCourses
Alice OS • Bob is enrolled in DBMS, OS, and Networks → has
Bob DBMS both required courses.
Bob OS • Alice is enrolled in DBMS and OS → has both
Bob Networks required courses.
Charlie DBMS • Charlie is enrolled only in DBMS → missing OS, so
• 2. RequiredCourses(course) not included.
course student
DBMS • Final Result: Alice
OS Bob
Relational Algebra : Operations
• Rename (ρ) : Rename (ρ) operation can be used to rename a relation or an
attribute of a relation.
• Rename (ρ) Syntax: ρ(new_relation_name, old_relation_name)
• Query: ρ(CUST_NAMES, ∏(Customer_Name)(CUSTOMER))
Relational Algebra examples
• 1. Find Indian player id .
• 2. List out players those
who are having more
than 10000 runs.
• 3. List out player along
with their country
details having age below
25 and more than 100
wickets.
• 4. List all the countries
in Player relation.
Relational Algebra examples
• 1. Find all the Customers having an
account but not the loan.
• [Link] the Name of the deposit
relation as “Depositor –details”.
• [Link] cust-name attribute of Π
borrower relation as “Customer-
Name”.
• 4. Find customers those who have
account and also borrowed loan
from a bank.
Relational Algebra example

• 1. Find the names of all red parts.

• [Link] all prices for parts that are red or
green.
• 3. Find the sIDs of all suppliers who
supply a part that is red or green
• Employee (EmpID, Name, Dept, Salary, Age) 1. Retrieve the EmpIDs of employees who work
• Department (DeptID, DeptName, Location) on at least one project and are also listed in
• Project (ProjID, ProjName, DeptID) the employee table.
• WorksOn (EmpID, ProjID, Hours) 2. Retrieve the EmpIDs of employees who are
• Supplier (SuppID, SuppName, City) in the Employee table but are not assigned
to any project.
• Supplies (SuppID, ItemID, Cost)
3. Retrieve all employees who work in the "HR"
• Item (ItemID, ItemName, Category) department.
4. Rename the attribute DeptID of the
Department table to Department_Code.
5. Retrieve the EmpIDs of employees who have
worked on all projects.
6. Retrieve the SuppIDs of suppliers who
supply all items in the "Electronics" category.
Relational Algebra : Joins
• The database joins has the ability of
combining two or more data tables
into a single table only if the
following conditions are satisfied.
• There must be a common attribute in
both(tables which are participating)
tables.
• Join condition must be satisfied .
• Database joins can be broadly
classified into two categories which
are further categorized into sub
categories.
Relational Algebra : Joins (The Inner Joins)
• When inner join is applied to tuples or tables, only those tuples of the table are kept
which have common attribute in all the tables. Other tuples which are not common are
dropped from the resulting tuple/table.
• Two possible inner joins are available i.e. Theta Join & Natural Join.
• Theta Join :If a condition is satisfied by the participating tables from different relations,
then the tuples are combined together using Theta Join. Theta join is denoted through
“Theta(Θ)”.
• Syntax : R1(X1, X2,X3…Xn) (Condition “θ”) R2(Y1, Y2,Y3…Yn) where, R1 and R2 are
relations having (X1, X2,X3…Xn) and (Y1, Y2,Y3…Yn) as attributes respectively.
• For example : Consider the
tables Student_Details and
Student_Result. Now, if we
want to implement theta
join on these relations, the
result will look like:
Relational Algebra : Joins(Equi Joins)
• EQUI Join is done when a Theta join uses only the equivalence condition.
• EQUI JOIN creates a JOIN for equality or matching column(s) values of the
relative tables.
• For example:
• A [Link] 2 = [Link] 2 (B)
• SELECT [Link], [Link], [Link], [Link] FROM student JOIN
record ON [Link] = [Link];
Relational Algebra : Joins (The Inner Joins)
• Natural Join ( ):
• Natural join does not supports any condition such as theta join and works
only if, one attribute or more than one attributes are common between the
joining/participating relations.
• Syntax : R1(X1, X2,X3…Xn) R2(Y1, Y2,Y3…Yn) where, R1 and R2 are
relations having (X1, X2,X3…Xn) and (Y1, Y2,Y3…Yn) as attributes
respectively.
• For example : Consider the tables Student_Details and Student_Result. Now,
if we want to implement natural join on these relations, the result will be
Relational Algebra : Joins (The Outer Joins)
• Outer Join ( ):Outer join overcomes the inability of inner joins of dropping the
tuples which are uncommon among participating relations. If we want to display
those tuples which are not common, the concept of outer join is used.
• Also, if all the tuples needs to be displayed from all the participating relations, outer
joins can be used. They are of three types : Left Outer Join, Right Outer Join & Full
Outer Join.
• Left Outer Join
• There exists a concept of position(left or right) of relations in case of both left and
right outer join.
• In the left outer join, operation allows keeping all tuple in the left relation+
common tuples from both tables.
• If in case any tuple in left relation does not matches with the tuple in right
relation, NULL value will be displayed against that tuple in the resulting relation.
Relational Algebra : Joins (The Outer Joins)
• Left Outer Join:

• For example : Consider the tables Student_Details and Student_Result. Now,

if we want to implement left outer join on these relations, the result will
look like:
Relational Algebra : Joins (The Outer Joins)
• Right Outer Join : In the right outer join, operation allows keeping all tuple
in the right relation.
• However, if there is no matching tuple is found in the left relation, then the
attributes of the left relation in the join result are filled with null values.

• For example : Consider the tables Student_Details and Student_Result. Now,

if we want to implement right outer join on these relations, the result will
look like:
Relational Algebra : Joins (The Outer Joins)
• Full Outer Join : In a full outer join, all tuples from both relations are
included in the result, irrespective of the matching condition.
• If in case, tuples doesn’t matches, NULL value is passes against that.

• For example : Consider the tables Student_Details and Student_Result. Now,

if we want to implement full outer join on these relations, the result will
look like:
• 1] List the names and age of students enrolled in different courses.
• 2]Identify names of all students those who are admitted and not enrolled for
courses.
• 3] Identify names of enrolled students along with their course details , those who
belong to Bihar.
• 4] Retrieve Student Details Along with Their Courses
• 5] Retrieve Course IDs Along with the Names of Enrolled Students
• 1] write a SQL query to find
the salesperson and customer
who reside in the same city.
Return Salesman, cust_name
and city.
• 2]Identify salesman who
delivered orders to multiple
customer.
Relational Algebra:Summary
Operation (Symbols) Purpose
Select(σ) The SELECT operation is used for selecting a subset of the tuples according to a given selection
condition
Projection(π) The projection eliminates all attributes of the input relation but those mentioned in the
projection list.
Union Operation(∪) UNION is symbolized by symbol. It includes all tuples that are in tables A or in B.
Set Difference(-) The result of A – B, is a relation which includes all tuples that are in A but not in B.
Intersec on(∩) Intersection defines a relation consisting of a set of all tuple that are in both A and B.
Cartesian Product(X) Cartesian operation is helpful to merge columns from two relations.
Inner Join Inner join, includes only those tuples that satisfy the matching criteria.
Theta Join(θ) The general case of JOIN operation is called a Theta join. It is denoted by symbol θ.
EQUI Join When a theta join uses only equivalence condition, it becomes a equi join.
Natural Join(⋈) Natural join can only be performed if there is a common attribute between the relations.
Outer Join In an outer join, along with tuples that satisfy the matching criteria.
Left Outer Join( ) In the left outer join, operation allows keeping all tuple in the left relation.
Right Outer join( ) In the right outer join, operation allows keeping all tuple in the right relation.
Full Outer Join( ) In a full outer join, all tuples from both relations are included in the result irrespective of the
matching condition.
Query Languages

int It is used to specify an integer value.

smallint It is used to specify small integer value.

bit It has the number of bits to store.

It specifies a numeric value that can have a decimal
decimal
number.
numeric It is used to specify a numeric value.
SQL Commands
• SQL commands are
instructions. It is used to
communicate with the
database. It is also used to
perform specific tasks,
functions, and queries of
data.
• SQL can perform various tasks
like create a table, add data
to tables, drop the table,
modify the table, set
permission for users.
• Comments in SQL
SQL Commands • Single-Line Comments
• The single line comment starts with two
• Data Definition Language(DDL) – hyphens (–). So, any text mentioned after
Consists of commands which are (–), till the end of a single line will be
used to define the database. ignored by the compiler.
• Example:
• Data Manipulation Language(DML) • --Select all:
– Consists of commands which are
used to manipulate the data • SELECT * FROM Employee_Info;
present in the database. • Multi-Line Comments
• The Multi-line comments start with /* and
• Data Control Language(DCL) – end with */. So, any text mentioned
Consists of commands which deal between /* and */ will be ignored by the
with the user permissions and compiler.
controls of the database system. • Example:
• Transaction Control Language(TCL) • /*Select all the columns
– Consist of commands which deal • of all the records
with the transaction of the • from the Employee_Info table:*/
database. • SELECT * FROM Students;
Data Definition Language (DDL)
• DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
• All the command of DDL are auto-committed that means it permanently save all the changes in the
database.
• Here are some commands that come under DDL:
• CREATE
• ALTER
• DROP
• TRUNCATE
• TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.
• TRUNCATE TABLE table_name;
• TRUNCATE TABLE EMPLOYEE;
• DROP: It is used to delete both the structure and record stored in the table.
• DROP TABLE table_name;
• DROP TABLE EMPLOYEE;
Data Definition Language (DDL)
• CREATE It is used to create a new table in the database.
• CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
• CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DA
TE);
• You can also create a table using another table.
• Syntax:
• Example:
• CREATE TABLE NewTableName AS
• CREATE TABLE ExampleTable AS
• SELECT Column1, column2,..., ColumnN
• SELECT Emp_Name, Phone_Num
• FROM ExistingTableName
• FROM Employee_Info;
• WHERE ....;
Data Definition Language (DDL)
• ALTER: It is used to alter the structure of the database. This change could be either to
modify the characteristics of an existing attribute or probably to add a new attribute.
• ALTER TABLE table_name ADD column_name COLUMN-definition;
• ALTER TABLE table_name MODIFY(column_definitions....);
[Link] TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));
[Link] TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));
• SQL Commands: Constraints Used In Database
• Constraints are used in a database to specify the rules for data in a table. The
following are the different types of constraints:
• NOT NULL :This constraint ensures that a column cannot have a NULL value.
• UNIQUE: This constraint ensures that all the values in a column are unique.
• CHECK: This constraint ensures that all the values in a column satisfy a specific
condition.
• DEFAULT: This constraint consists of a set of default values for a column when no
value is specified.
Data Definition Language (DDL) • --UNIQUE on Create Table
• CREATE TABLE Employee
• --NOT NULL on Create Table
• CREATE TABLE Employee • (
• ( • EmployeeID int NOT NULL UNIQUE,
• EmployeeID int NOT NULL, • EmployeeName varchar(255) NOT
• EmployeeName varchar(255) NOT NULL, NULL,
• Emergency ContactName varchar(255), • Emergency ContactName varchar(255),
• PhoneNumber int NOT NULL, • PhoneNumber int NOT NULL,
• Address varchar(255), • Address varchar(255),
• City varchar(255), • City varchar(255),
• Country varchar(255)
• Country varchar(255)
• );
• --NOT NULL on ALTER TABLE • );
• ALTER TABLE Employee • --UNIQUE on ALTER TABLE
• MODIFY PhoneNumber int NOT NULL; • ALTER TABLE Employee
• ADD UNIQUE (Employee_ID);
Data Definition Language (DDL) • --DEFAULT Constraint on CREATE TABLE
• CREATE TABLE Employee
• --CHECK Constraint on CREATE TABLE
• CREATE TABLE Employee
• (
• ( • EmployeeID int NOT NULL,
• EmployeeID int NOT NULL, • EmployeeName varchar(255),
• EmployeeName varchar(255), • Emergency ContactName varchar(255),
• Emergency ContactName varchar(255), • PhoneNumber int,
• PhoneNumber int, • Address varchar(255),
• Address varchar(255), • City varchar(255),
• City varchar(255), • Country varchar(255) DEFAULT 'India'
• Country varchar(255) CHECK • );
(Country=='India')
• --DEFAULT Constraint on ALTER TABLE
• );
• ALTER TABLE Employee
• --CHECK Constraint on ALTER TABLE
• ADD CONSTRAINT defau_Country
• ALTER TABLE Employee
• ADD CHECK (Country=='India');
• DEFAULT 'India' FOR Country;
Data Definition Language (DDL)
• -- Add TEXT and TIME Columns
• ALTER TABLE Employee ADD COLUMN notes TEXT,ADD COLUMN check_in
TIME;
• -- Add DECIMAL Column
• ALTER TABLE Employee MODIFY COLUMN salary DECIMAL(10, 2);
• ALTER TABLE Employee ADD COLUMN bonus_amount DECIMAL(10, 2);
• DECIMAL(10, 2) means:
• Up to 10 digits total, with 2 digits after the decimal point.
• Examples: 12345678.90, 5000.00, 75.25
Data Definition Language (DDL)
• ALTER TABLE Employee ADD COLUMN project_count INT,ADD COLUMN
experience_years SMALLINT,ADD COLUMN is_active BIT,ADD COLUMN
tax_rate NUMERIC(5, 2);
Column Name Data Type Description
For storing total projects handled (e.g., 12, 100,
project_count INT
etc.)
experience_years SMALLINT For storing small integers like years of experience
is_active BIT 0 or 1 to represent active status (Boolean-like)
tax_rate NUMERIC(5,2) For storing tax rate like 18.00 (supports precision)
• DECIMAL(M, D)
• Are NUMERIC and DECIMAL Different in MySQL?
• No – in MySQL, NUMERIC and DECIMAL are • NUMERIC(M, D)
functionally the same. • M = total number of digits (precision)
• MySQL treats NUMERIC as a synonym for DECIMAL. • D = number of digits after the decimal point
• Both store exact fixed-point numbers, making them (scale)
ideal for financial and monetary values.
Data Definition Language (DDL)
Column Name Data Type Description
is_active BIT 0 or 1 to represent active status (Boolean-like)
• What is b'0' and b'1'?
• These are bit literals, and the b'' syntax tells MySQL to interpret the string
inside as binary.
Expression Meaning Stored As Equivalent To
b'0' Binary 0 00000000 FALSE or OFF
b'1' Binary 1 00000001 TRUE or ON
• is_active BIT CHECK
(is_active IN (b'0', b'1’)) INSERT INTO Employee (
• This means is_active must be emp_name, phone_num, is_active
either b'0' or b'1' ) VALUES (
• You cannot insert '0' or 0 ‘Vikas Sawant', 9876543210, b'1' -- Active
directly use b'0’ );
Data Definition Language (DDL) : Let us Create Table
CREATE TABLE Employee ( Emp_ID INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(300) NOT NULL, phone_num BIGINT UNIQUE, city
VARCHAR(100) DEFAULT 'Pune', Country VARCHAR(100), blood_group
VARCHAR(10), salary DECIMAL(10,2) CHECK (salary >= 0), designation
VARCHAR(100), DOB DATE, notes TEXT, check_in TIME, bonus_amount
DECIMAL(10,2) DEFAULT 0.00, project_count INT CHECK (project_count >= 0),
experience_years SMALLINT CHECK (experience_years >= 0), is_active BIT
CHECK (is_active IN (b'0', b'1')), tax_rate NUMERIC(5,2) CHECK (tax_rate
BETWEEN 0 AND 100) );
Query Languages

mysql> desc employee;

Data Manipulation Language
• DML commands are used to modify the database. It is responsible for all form of
changes in the database.
• The command of DML is not auto-committed that means it can't permanently save
all the changes in the database. They can be rollback.
• INSERT
• UPDATE
• DELETE
• INSERT: The INSERT statement is a SQL query. It is used to insert data into the row
of a table.
• INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, val
ue3, .... valueN);
• INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);
• INSERT INTO book (Author, Subject) VALUES (“ABC", "DBMS");
Data Manipulation Language
• UPDATE: This command is used to update or modify the value of a
column in the table.
• UPDATE table_name SET [column_name1= value1,...column_nameN =
valueN] [WHERE CONDITION]
• UPDATE students SET User_Name = ’XYZ' WHERE Student_Id = '3’
• DELETE: It is used to remove one or more row from a table.
• DELETE FROM table_name [WHERE condition];
• DELETE FROM book WHERE Author=“ABC";
Data Manipulation Language - Insert
• INSERT INTO Employee ( emp_name, phone_num, city, Country,
blood_group, salary, designation, DOB, notes, check_in, bonus_amount,
project_count, experience_years, is_active, tax_rate) VALUES ( 'Anita
Deshmukh', 9876543210, 'Mumbai', 'India', 'A+', 65000.00, 'Software
Engineer', '1990-04-15', 'Highly skilled in Java and Python.', '09:00:00',
5000.00, 6, 8, b'1', 18.00);
• INSERT INTO Employee ( emp_name, phone_num, city, Country,
blood_group, salary, designation, DOB, notes, check_in, bonus_amount,
project_count, experience_years, is_active, tax_rate) VALUES ( 'Ravi
Patil', 9988776655, 'Pune', 'India', 'O-', 48000.50, 'QA Analyst', '1988-12-
22', 'Manual and automation testing expert.', '09:30:00', 3000.00, 4, 5,
b'1', 15.75);
Data Manipulation Language
• Experiment with Insert Data:
• Verify CHECK constraints on salary,
project_count, experience_years, tax_rate,
is_active
• Provide valid BIT values using b’1’ also check
invalid bit
• Check NULL in NOT NULL fields (emp_name)
• Check DECIMAL(10,2) format for money
fields
• Check valid and invalid DOB (can be possible
with trigger because these values can change
at runtime and are not deterministic)
• Check for Valid Data Type
• Check for UNIQUE Constraint
• So On………
Data Manipulation Language -Insert
• Insert More Data:
INSERT INTO Employee ( emp_name, phone_num, city, Country, blood_group, salary, designation,
DOB,
notes, check_in, bonus_amount, project_count, experience_years, is_active, tax_rate
) VALUES
('Jesse Townsend', 9156411172, 'Dennischester', 'India', 'O-', 57295.62, 'HR Manager', '1986-11-01',
'Mouth bag lay answer.', '10:45:00', 7587.63, 13, 17, b'0', 18.32),
('Theresa Rogers', 9086064385, 'Maryborough', 'India', 'O-', 45804.98, 'Data Analyst', '2002-04-13',
'Cell throughout have important.', '09:15:00', 3784.65, 6, 6, b'0', 9.71),
('Jennifer Murray', 9372593194, 'Rachelfurt', 'India', 'AB-', 67579.30, 'Data Analyst', '1975-01-17',
'Congress owner challenge surface.', '09:45:00', 5998.60, 12, 6, b'0', 9.52),
('Christopher Johnson', 9647210940, 'South Lauren', 'India', 'O+', 38856.27, 'Tech Lead', '2000-12-
20',
'Call network why participant response simple.', '08:15:00', 2503.13, 0, 30, b'0', 29.25),
('Donna Garza', 9858352545, 'Amystad', 'India', 'O+', 37596.91, 'UI/UX Designer', '1986-01-15',
'Seven sport three prepare education suggest.', '08:15:00', 7455.74, 9, 23, b'1', 18.20);
Data Manipulation Language-Insert
• Insert More Data:
INSERT INTO Employee ( emp_name, phone_num, city, Country, blood_group, salary, designation,
DOB, notes, check_in, bonus_amount, project_count, experience_years, is_active, tax_rate)
VALUES('Jessica Lane', 9613538868, 'South Lisa', 'India', 'A+', 55733.34, 'UI/UX Designer', '1997-08-
11', 'President civil minute foreign economic.', '08:15:00', 5163.22, 15, 27, b'1', 21.34),('Whitney
Williams', 9172045179, 'New Michael', 'India', 'A+', 61240.16, 'Frontend Engineer', '1983-12-03',
'Step energy company field guy.', '08:15:00', 9571.59, 11, 2, b'1', 26.5),('Scott Allen', 9647670986,
'Austinton', 'India', 'AB-', 52896.36, 'Backend Engineer', '1989-06-03', 'Dog crime travel.',
'10:30:00', 5111.48, 9, 22, b'1', 8.32),('Patricia Allen', 9824459572, 'Grahamland', 'India', 'B-',
88163.29, 'UI/UX Designer', '1991-10-21', 'Foot maybe success wide draw all feeling parent.',
'09:00:00', 6246.38, 7, 5, b'0', 28.53),('Thomas Miller', 9325194051, 'Bradleystad', 'India', 'AB-',
76323.33, 'Data Scientist', '2000-08-25', 'Change white significant kid word course give wife.',
'09:45:00', 4924.28, 2, 21, b'1', 26.1),('Michael Thomas', 9167953535, 'Port David', 'India', 'B-',
88187.02, 'Data Scientist', '1985-02-03', 'Close possible start since lose.', '08:00:00', 5469.43, 2, 18,
b'0', 8.74),('Jeffrey Smith', 9609064450, 'West Pennybury', 'India', 'AB-', 84777.75, 'Project
Manager', '1972-04-22', 'Large old hospital thank pull.', '08:00:00', 4973.96, 6, 12, b'1',
29.26),('Joseph Lozano', 9478467163, 'Guzmanland', 'India', 'B-', 70847.37, 'Project Manager',
'1975-03-31', 'Purpose today career must glass hold reduce.', '10:15:00', 8783.94, 8, 15, b'1',
9.12),('Annette Dickson', 9696562983, 'Port Aaronside', 'India', 'B+', 76650.68, 'DevOps Engineer',
'1971-10-19', 'Glass charge part.', '09:30:00', 4912.39, 0, 18, b'0', 13.29),('Rebecca Taylor',
9342978092, 'Hamptonmouth', 'India', 'B-', 34795.50, 'DevOps Engineer', '1981-04-07', 'Today
recently interest power travel.', '09:15:00', 4515.28, 15, 22, b'0', 23.41);
Data Manipulation Language-Update
• Update salary, bonus_amount, • Update city and tax rate for an
and is_active for a specific employee with specific phone
employee number

UPDATE Employee UPDATE Employee

SET SET
salary = 82000.75, city = 'Bengaluru',
bonus_amount = 5500.00, tax_rate = 22.50
is_active = b'1' WHERE phone_num =
WHERE Emp_ID = 1; 9876543210;
Data Manipulation Language-Update With CASE Statement
• Syntax of CASE inside • Update bonus_amount based on
UPDATE salary

UPDATE table_name UPDATE Employee

SET column_name = CASE SET bonus_amount = CASE
WHEN condition1 THEN value1 WHEN salary >= 80000 THEN 10000.00
WHEN condition2 THEN value2 WHEN salary >= 60000 THEN 7000.00
... WHEN salary >= 40000 THEN 5000.00
ELSE default_value ELSE 3000.00
END END;
WHERE optional_filter;
Data Manipulation Language-Update With CASE Statement
• Update is_active based on • Update tax_rate based on
experience_years designation

UPDATE Employee UPDATE Employee

SET is_active = CASE SET tax_rate = CASE designation
WHEN experience_years >= 10 WHEN 'Project Manager' THEN 25.00
THEN b'1' WHEN 'DevOps Engineer' THEN 20.00
ELSE b'0' WHEN 'HR Specialist' THEN 15.00
END; ELSE 10.00
END;
Data Manipulation Language-Update With CASE Statement
• Problem : Update multiple column using CASE Statement. Example DOB, project_count, and
experience_years
UPDATE Employee
SET
bonus_amount = CASE
WHEN project_count > 10 THEN 10000.00
WHEN project_count BETWEEN 5 AND 10 THEN 7000.00
ELSE 4000.00
END,
tax_rate = CASE
WHEN experience_years >= 20 THEN 25.00
WHEN experience_years >= 10 THEN 20.00
ELSE 15.00
END,
is_active = CASE
WHEN DOB < '1985-01-01' THEN b'0' -- Assuming older employees retired
ELSE b'1'
END
WHERE salary > 30000;
Data Manipulation Language-Update
• Add Age Column and Update with DOB

• Add age Column

ALTER TABLE Employee
ADD COLUMN age INT;

• Update age from DOB

UPDATE Employee
SET age = TIMESTAMPDIFF(YEAR, DOB, CURDATE());
Data Manipulation Language-Update
• Automatically Update age Periodically
• MySQL doesn’t support virtual/auto-updating columns directly with
functions like CURDATE() in stored fields. But you can:
• Use a triggered job (event scheduler) to update age daily or monthly
• Recompute age via VIEW or SELECT when needed
• OR, use a generated column if you want real-time age
• Use a Generated Column (MySQL 5.7+)
• ALTER TABLE Employee ADD COLUMN age_generated INT
• AS (TIMESTAMPDIFF(YEAR, DOB, CURDATE())) STORED;
• ERROR 3763 (HY000): Expression of generated column 'age_generated'
contains a disallowed function: CURDATE
• because MySQL does not allow non-deterministic functions like CURDATE(),
NOW(), RAND(), etc., inside generated columns — even STORED ones.
• 3. Delete Multiple Rows Based on Age
Data Manipulation Language- Delete DELETE FROM Employee
• 1. Delete by Primary Key (Emp_ID) WHERE age >= 60;
DELETE FROM Employee • Deletes employees who are 60
WHERE Emp_ID = 5; years or older (if age is stored or
updated).
• Deletes the employee with • 4. Delete Inactive Employees
Emp_ID = 5. (is_active = b'0')
• 2. Delete by Name DELETE FROM Employee
DELETE FROM Employee WHERE is_active = b'0';
• Deletes all inactive employees.
WHERE emp_name = 'Meera Joshi';
• 5. Delete Employees with Low
• Deletes the employee named Experience and Salary
Meera Joshi. DELETE FROM Employee
• Delete All Records ( Use with WHERE experience_years < 2 AND
Caution) salary < 35000;
• DELETE FROM Employee; • Deletes junior employees with low
salary.
Create Duplicate Table • Duplicate Without Constraints?
• Creating Duplicate Table (Structure CREATE TABLE Employee_Simple AS
Only) SELECT * FROM Employee;
CREATE TABLE Employee_Copy LIKE
Employee;
• Inserting Data from Original Table
INSERT INTO Employee_Copy
SELECT * FROM Employee;
Query Languages

• Relational Algebra
• SQL: DDL, DML, Select Queries, Set, String, Date
and Numerical Functions, Aggregate Functions
,Group by and Having Clause, Join Queries, Nested
queries, DCL, TCL
• PL/SQL: Procedure, Function, Trigger
• Mapping of Relational Algebra to SQL, Assertions,
roles and privileges
• Embedded SQL, Dynamic SQL.
Data Query Language
• SELECT
• This statement is used to select data from a database and the data returned
is stored in a result table, called the result-set.
• Syntax : SELECT Column1, Column2, ...ColumN FROM TableName;
• SELECT * FROM table_name;
• Example: SELECT EmployeeID, EmployeeName FROM Employee;
• --(*) is used to select all from the table
• SELECT * FROM Employee;
• -- To select the number of records to return use:
• SELECT TOP 3 * FROM Employee;
Select Queries
• Filtering with WHERE
• Basic Queries • Active Employees Only
• Select All Employees SELECT emp_name, is_active
SELECT * FROM FROM Employee
Employee; WHERE is_active = b'1';
• Select Specific • Employees from a Specific City
Columns SELECT emp_name, city
FROM Employee
SELECT emp_name, WHERE city = 'Pune';
salary, designation • Employees with Salary > 60000
FROM Employee;
SELECT emp_name, salary
FROM Employee
WHERE salary > 60000;
Select Queries • NULL and DEFAULTs
• Employees Without Phone
• Working with Dates Numbers
• Employees Born After
1990 SELECT emp_name
SELECT emp_name, DOB FROM Employee
FROM Employee WHERE phone_num IS NULL;
WHERE DOB > '1990-01- • Show Employees with is_active as
01'; 'Yes'/'No'
• Compute Age on the Fly SELECT emp_name,
(if no age column exists) CASE is_active
SELECT emp_name, DOB, WHEN b'1' THEN 'Yes'
TIMESTAMPDIFF(YEAR,
DOB, CURDATE()) AS ELSE 'No'
computed_age END AS Active_Status
FROM Employee;
FROM Employee;
Select Queries • Conditional Logic
• Average Salary by • Label Experience Level
Designation SELECT emp_name,
SELECT designation, experience_years,
AVG(salary) AS CASE
avg_salary WHEN experience_years >= 15
FROM Employee THEN 'Senior'
GROUP BY designation; WHEN experience_years >= 5
THEN 'Mid-Level'
ELSE 'Junior'
END AS experience_level
FROM Employee;
Data Manipulation Language
• You can use the following keywords with the SELECT statement:
• DISTINCT
• ORDER BY
• GROUP BY
• HAVING Clause
• The ‘SELECT DISTINCT’ Statement
• This statement is used to return only different values.
• Syntax: SELECT DISTINCT Column1, Column2, ...ColumnN
FROM TableName;
• Example SELECT DISTINCT is_active FROM Employee;
Data Manipulation Language
• The ‘ORDER BY’ Statement
• The ‘ORDER BY’ statement is used to sort the required results in
ascending or descending order. The results are sorted in
ascending order by default.
• Syntax: SELECT Column1, Column2, ...ColumnN FROM
TableName ORDER BY Column1, Column2, ... ASC|DESC;
• SELECT emp_name, salary
• FROM Employee
• ORDER BY salary DESC
• LIMIT 5; --Top 5 Highest Paid Employees
Data Manipulation Language
• The ‘GROUP BY’ Statement
• This ‘GROUP BY’ statement is used with the aggregate functions to
group the result-set by one or more columns.
• Syntax :SELECT Column1, Column2,..., ColumnN
• FROM TableName
• WHERE Condition
• GROUP BY ColumnName(s)
• ORDER BY ColumnName(s);
• Example Count Employees by Blood Group
• SELECT blood_group, COUNT(*) AS total
• FROM Employee
• GROUP BY blood_group;
Data Manipulation Language
• The ‘HAVING’ Clause
• Syntax : SELECT ColumnName(s)
• FROM TableName
• WHERE Condition
• GROUP BY ColumnName(s)
• HAVING Condition
• ORDER BY ColumnName(s);
• Example /* Count Employees by Blood Group > 2:*/
• SELECT COUNT(*), blood_group
• FROM Employee
• GROUP BY blood_group
• HAVING COUNT(blood_group) > 2
• ORDER BY COUNT(blood_group) DESC;
Group by and Having Clause Examples
• 1. Group Employees by Designation • 3. Count of Employees in Each
and Show Average Salary City
SELECT designation, AVG(salary) AS SELECT city, COUNT(*) AS
avg_salary FROM Employee employee_count FROM Employee
GROUP BY designation; GROUP BY city;
• 2. Show Designations With Average • 4. Cities with More Than 5
Salary Above ₹60,000 (Using Employees
HAVING) SELECT city, COUNT(*) AS
SELECT designation, AVG(salary) AS employee_count FROM Employee
avg_salary FROM Employee GROUP BY city
GROUP BY designation HAVING COUNT(*) > 5;
HAVING AVG(salary) > 60000;
Group by and Having Clause Examples
• 5. Group by Blood Group with • 7. Show Total Salary per Country Where
Total > ₹200,000
Minimum Bonus > ₹4000
SELECT Country, SUM(salary) AS total_salary
SELECT blood_group,
MIN(bonus_amount) AS min_bonus FROM Employee
FROM Employee GROUP BY Country
GROUP BY blood_group HAVING SUM(salary) > 200000;
HAVING MIN(bonus_amount) > 4000; • 8. Employees Grouped by Active/Inactive
Status
SELECT
• 6. Group by Project Count and Show CASE is_active
Average Experience
WHEN b'1' THEN 'Active'
SELECT project_count, ELSE 'Inactive'
AVG(experience_years) AS
avg_experience END AS status,
FROM Employee COUNT(*) AS total
GROUP BY project_count; FROM Employee
GROUP BY is_active;
WHERE HAVING
It is used to filter data directly It is used to filter the data of
on the table groups created on a table.
It is applied as a row It is applied as a column
operation. operation.
The WHERE clause pulls only The HAVING clause fetches all
the filter data based on data before applying the filter
condition. Filter rows before condition. Filter groups after
grouping aggregation
You cannot use aggregate It is used on aggregate
functions on the WHERE function on which group has
condition. performed.
Having and Where Clause Example
• List the book
details whose
price is less than
350 [Table 1]

• Identify language
and count number
of books for the
language having
multiple books.
Aggregate Functions
• An aggregate function in SQL performs a • MAX() Function
calculation on multiple values and returns
a single value. • The MAX function returns
• SQL provides many aggregate functions the largest value of the
that include avg, count, sum, min, max, selected column in a table.
etc.
• Syntax
• An aggregate function ignores NULL
values when it performs the calculation, • SELECT MAX(ColumnName)
except for the count function.
• MIN() Function • FROM TableName
• The MIN function returns the smallest • WHERE Condition;
value of the selected column in a table.
• Syntax
• SELECT MIN(ColumnName)
• FROM TableName
• WHERE Condition;
Aggregate Functions • AVG() Function
• The AVG function returns the
• COUNT() Function average value of a numeric
• The COUNT function returns the column that you choose.
number of rows which match the
specified criteria. • Syntax SELECT
AVG(ColumnName)
• Syntax: SELECT COUNT(ColumnName)
FROM TableName • FROM TableName WHERE
• WHERE Condition; Condition;
• SUM() Function
• The SUM function returns the total
sum of a numeric column that you
choose.
• Syntax: SELECT SUM(ColumnName)
FROM TableName WHERE Condition;
Select Queries With Aggregate Functions
• 1. Total Number of Employees
SELECT COUNT(*) AS total_employees FROM Employee;

• 2. Average Salary of All Employees

SELECT AVG(salary) AS average_salary FROM Employee;

• 3. Minimum and Maximum Bonus

SELECT
MIN(bonus_amount) AS minimum_bonus,
MAX(bonus_amount) AS maximum_bonus
FROM Employee;
Select Queries With Aggregate Functions
• 4. Total Bonus Payout
SELECT SUM(bonus_amount) AS total_bonus_paid
FROM Employee;

• 5. Average Salary by Designation

SELECT designation, AVG(salary) AS avg_salary
FROM Employee
GROUP BY designation;

• 6. Employee Count by City

SELECT city, COUNT(*) AS num_employees
FROM Employee
GROUP BY city
ORDER BY num_employees DESC;
Select Queries With Aggregate Functions
• 7. Blood Group Distribution
SELECT blood_group, COUNT(*) AS total FROM Employee GROUP BY
blood_group;

• 8. Average Experience by Role

SELECT designation, AVG(experience_years) AS avg_experience FROM
Employee GROUP BY designation;

• 9. Total and Average Salary by Country

SELECT Country, COUNT(*) AS total_employees, SUM(salary) AS total_salary,
AVG(salary) AS avg_salary FROM Employee GROUP BY Country;

• 10. Number of Employees Per Project Count

SELECT project_count, COUNT(*) AS num_employees FROM Employee
GROUP BY project_count
ORDER BY project_count;
Numerical Functions • 3. ROUND() – Round to N Decimal
Places
• 1. ABS() – Absolute Value
SELECT emp_name, tax_rate,
SELECT emp_name, ABS(bonus_amount -
5000) AS bonus_difference ROUND(tax_rate, 1) AS
FROM Employee; rounded_tax
• Returns the absolute difference from a FROM Employee;
fixed bonus amount.
• 4. MOD() – Modulus (Remainder)
• 2. CEIL() and FLOOR() – Round Up or Down SELECT emp_name, project_count,
SELECT emp_name, salary, MOD(project_count, 2) AS
CEIL(salary) AS rounded_up, even_or_odd
FLOOR(salary) AS rounded_down FROM Employee;
FROM Employee;
• Useful when handling payouts, • 0 = even, 1 = odd
approximations.
Numerical Functions • 7. LEAST() / GREATEST() – Min/Max of
Multiple Values
• 5. POWER() – Exponentiation SELECT emp_name, salary,
SELECT emp_name, bonus_amount,
POWER(experience_years, 2) AS LEAST(salary, bonus_amount) AS
experience_squared lesser_amount,
FROM Employee; GREATEST(salary, bonus_amount)
AS greater_amount
FROM Employee;
• 6. SQRT() – Square Root
SELECT emp_name, SQRT(project_count) • 8. TRUNCATE() – Truncate Decimal
AS sqrt_projects without Rounding
FROM Employee; SELECT emp_name, TRUNCATE(tax_rate,
0) AS tax_whole_number
FROM Employee;
Numerical Functions
• 9. RAND() – Random Value (Useful for Simulations or Testing)
SELECT emp_name, RAND() AS random_score
FROM Employee
LIMIT 5;

• 10. Combine Functions

SELECT emp_name,
salary,
ROUND(salary * tax_rate / 100, 2) AS estimated_tax
FROM Employee;
Date functions
• Date functions in MySQL are a set of built-in functions designed to
manage, manipulate, extract, and format date and time values
stored within a MySQL database.
• NOW(): Returns the current date and time.
• CURDATE(): Returns the current date.
• YEAR(): Extracts the year from a date.
• MONTH(): Extracts the month from a date.
• DAY(): Extracts the day of the month from a date.
• DATE_ADD() / ADDDATE(): Adds a specified time interval to a date.
• DATE_SUB() / SUBDATE(): Subtracts a specified time interval from a
date.
• DATEDIFF(): Calculates the difference in days between two dates.
• DATE_FORMAT(): Formats a date value into a specified string
format.
Date functions
• Data Manipulation and Calculation:
• Determining age, calculating durations between events, or scheduling
future tasks.
• Data Extraction and Analysis:
• They allow the extraction of specific components of a date (e.g., year,
month, day of week) for analysis, reporting, and grouping data based
on time periods.
• Data Formatting and Presentation:
• Date functions provide flexibility in formatting dates and times into
human-readable or application-specific formats, ensuring consistent
and clear data presentation.
• Filtering and Querying:
• They are crucial for filtering data based on date ranges, specific days,
months, or years, enabling more precise and efficient data retrieval.
Date Functions • 4. DATEDIFF() – Difference Between
Two Dates
• 1. CURDATE() – Get Current Date
SELECT emp_name,
SELECT emp_name, CURDATE() AS today DATEDIFF(CURDATE(), DOB) AS
FROM Employee; days_since_birth
• 2. NOW() – Get Current Date and Time FROM Employee;
SELECT emp_name,current_timestamp Returns the number of days since the
FROM Employee; employee's date of birth.
• 3. YEAR(), MONTH(), DAY() – Extract • 5. TIMESTAMPDIFF() – Get Difference
Parts of Date in Years, Months, Days
SELECT emp_name, SELECT emp_name,
YEAR(DOB) AS birth_year, TIMESTAMPDIFF(YEAR, DOB,
MONTH(DOB) AS birth_month, CURDATE()) AS age_years,
DAY(DOB) AS birth_day TIMESTAMPDIFF(MONTH, DOB,
FROM Employee; CURDATE()) AS age_months
FROM Employee;
Date Functions • 9. DATE_FORMAT() – Format Date
• 6. ADDDATE() or DATE_ADD() – Add Days Output
to a Date SELECT emp_name,
SELECT emp_name, DOB, DATE_FORMAT(DOB, '%d-%M-%Y') AS
DATE_ADD(DOB, INTERVAL 60 YEAR) formatted_DOB
AS retirement_date FROM Employee;
FROM Employee; Output: 15-August-1990
• 7. SUBDATE() or DATE_SUB() – Subtract
Days from a Date
SELECT emp_name, • 10. TIME_FORMAT() – Format Time
DATE_SUB(CURDATE(), INTERVAL 7 Output
DAY) AS one_week_ago SELECT emp_name,
FROM Employee; TIME_FORMAT(check_in, '%h:%i %p')
• 8. STR_TO_DATE() – Convert String to AS formatted_checkin
Date
FROM Employee;
SELECT STR_TO_DATE('10-08-2025', '%d-
%m-%Y') AS formatted_date; • Output: 09:15 AM
Date Functions • Automatically Mark as Inactive
• Show Employees with Retirement
Eligibility • If you want to mark is_active = b'0'
SELECT emp_name, DOB, for retired employees:
TIMESTAMPDIFF(YEAR, DOB, UPDATE Employee
CURDATE()) AS age, SET is_active = b'0'
CASE WHERE CURDATE() >=
WHEN TIMESTAMPDIFF(YEAR, DATE_ADD(DOB, INTERVAL 60
DOB, CURDATE()) >= 60 THEN 'Eligible' YEAR);
ELSE 'Not Eligible'
END AS retirement_status
FROM Employee;
Date Functions • 12. Employees Born This Month
• 11. HOUR(), MINUTE(), SECOND() – SELECT emp_name, DOB
Extract from Time FROM Employee
SELECT emp_name, WHERE MONTH(DOB) =
HOUR(check_in) AS checkin_hour, MONTH(CURDATE());
MINUTE(check_in) AS
checkin_minute • 13. Employees Checked In Before 9
FROM Employee; AM
SELECT emp_name, check_in
FROM Employee
WHERE check_in < '09:00:00';
String Functions
• 3. LENGTH() – String Length in
• 1. CONCAT() – Combine Strings Bytes
SELECT CONCAT(emp_name, ' - ', SELECT emp_name,
designation) AS full_title LENGTH(emp_name) AS
FROM Employee; name_length
FROM Employee;

• 2. UPPER() / LOWER() – Change

Case • 4. CHAR_LENGTH() – Length in
Characters
SELECT emp_name, UPPER(city) AS SELECT emp_name,
city_uppercase, LOWER(Country) CHAR_LENGTH(emp_name) AS
AS country_lowercase name_characters
FROM Employee; FROM Employee;
String Functions
• 5. LEFT() / RIGHT() – Extract • 7. INSTR() – Find Position of
Characters from Start or End Substring
SELECT emp_name, SELECT emp_name,
LEFT(emp_name, 5) AS INSTR(emp_name, 'a') AS
short_name, position_of_a
RIGHT(emp_name, 3) AS FROM Employee;
ending_chars
FROM Employee;
• 8. REPLACE() – Replace Part of
String
• 6. SUBSTRING() – Extract Substring
from Any Position SELECT emp_name,
SELECT emp_name, REPLACE(designation, 'Engineer',
SUBSTRING(emp_name, 2, 4) AS 'Dev')
middle_chars FROM Employee;
FROM Employee;
String Functions • 11. LPAD() / RPAD() – Pad String
with Characters
• 9. TRIM() – Remove Spaces from SELECT emp_name, LPAD(emp_name,
Both Ends 20, '*') AS left_padded
SELECT emp_name, TRIM(emp_name) FROM Employee;
AS trimmed_name
FROM Employee;
• 12. FORMAT() – Format Numbers as
• You can also use LTRIM() or RTRIM() Strings
for left/right only. SELECT emp_name, FORMAT(salary,
• 10. REVERSE() – Reverse String 2) AS formatted_salary
SELECT emp_name, FROM Employee;
REVERSE(emp_name) AS
reversed_name
FROM Employee;
String Functions Function Use Case

CONCAT() Join strings

• 13. LOCATE() – Same as INSTR() but
argument order reversed UPPER()/LOWER() Change case

SELECT emp_name, LOCATE('an', LENGTH() Get byte length

emp_name) AS position_of_an
FROM Employee; CHAR_LENGTH() Get character count

SUBSTRING() Extract substring

• 14. ELT() + FIELD() – Map Code to
Meaning REPLACE() Replace part of string

SELECT emp_name, blood_group, INSTR()/LOCATE() Find position of substring

ELT(FIELD(blood_group, 'A+', 'B+',
TRIM() Remove spaces
'O+', 'AB+'), 'Group A', 'Group B',
'Group O', 'Group AB') AS LPAD() / RPAD() Pad string left/right
blood_group_label
FROM Employee; REVERSE() Reverse the string
IN Operator
• The SQL IN Operator is used to specify multiple values in the
WHERE clause.
• It returns all rows in which the specified column matches one
of the values in the list.
• The list of values or sub query must be specified in the
parenthesis e.g. IN (select query) or IN (Value1, Value2,
Value3, ...).
• In some scenarios we may use multiple OR statements to
include multiple conditions in SELECT, DELETE, UPDATE, or
INSERT statements. Alternatively, we can use the IN
operator instead of multiples OR statements.
• The IN operator can be used with any data type in SQL. It is
used to filter data from a database table based on specified
values.
SELECT emp_name, Emp_ID
IN Operator FROM Employee
WHERE Emp_ID IN (1, 3, 5);
• The SQL IN Operator is used to specify multiple values in the
WHERE clause.
• It returns all rows in which the specified column matches one
of the values in the list.
• The list of values or sub query must be specified in the
parenthesis e.g. IN (select query) or IN (Value1, Value2,
Value3, ...).
• In some scenarios we may use multiple OR statements to
include multiple conditions in SELECT, DELETE, UPDATE, or
INSERT statements. Alternatively, we can use the IN
operator instead of multiples OR statements.
• The IN operator can be used with any data type in SQL. It is
used to filter data from a database table based on specified
values.
EXISTS Operator • SELECT e.emp_name, e.Emp_ID
• FROM Employee e
• The SQL EXISTS operator is used • WHERE EXISTS (
to verify whether a particular
record exists in a MySQL table. • SELECT 1
• It is a logical operator. • FROM Department d
• It returns a Boolean value TRUE • WHERE d.manager_id = e.Emp_ID
or FALSE. • );
• It returns TRUE if the subquery
returns at least one record. • Meaning: For each employee, check if
• If the EXISTS operator returns there’s at least one row in
TRUE, the outer query will get Department with their Emp_ID as
executed; otherwise not. manager_id.
• It can be used in SELECT, UPDATE,
DELETE or INSERT statements.
What is indexing in SQL?
• Indexing in SQL is a powerful technique used to
optimize the performance of database queries by
enabling faster retrieval of data.
• An index is a database object that provides a quick
lookup mechanism for efficiently locating and
accessing the data within tables.
• By creating indexes on one or more columns of a
table, you can significantly reduce the amount of data
the database engine needs to scan, thereby speeding
up query execution times.
What is an Index?
• “An index in SQL is analogous to an index in a book. Just as a book index
allows you to quickly find the page where a particular topic is discussed, a
database index allows the SQL engine to quickly locate the rows in a
table that match specific query criteria without scanning the entire
table.”
• Key Characteristics:
• Speed Up Queries: Indexes enhance the speed of data retrieval operations.
• Storage Overhead: While they improve read performance, indexes consume
additional disk space.
• Maintenance Costs: Indexes need to be maintained during data
modification operations (INSERT, UPDATE, DELETE), which can introduce
some overhead.
• How Indexes Work
• When you create an index on a column, the database creates a separate
data structure (often a B-tree) that holds the indexed column's values
along with pointers to the corresponding rows in the table. This
structure allows the database engine to perform binary searches or other
efficient search algorithms to quickly locate data.
Indexing
• Example Without Index:
• SELECT * FROM Employees WHERE LastName = 'Smith';
• The database performs a full table scan, checking each
row's LastName to find matches.

• Example With Index:

• CREATE INDEX idx_lastname ON Employees (LastName);
• SELECT * FROM Employees WHERE LastName = 'Smith';
• The database uses the idx_lastname index to directly
locate rows where LastName is 'Smith', avoiding a full table
scan.
Types of Indexes
• a. Single-Column Index
• An index created on a single column.
• Example:
• CREATE INDEX idx_firstname ON Employees (FirstName);
• b. Composite (Multi-Column) Index
• An index that includes multiple columns. Useful for queries that
filter based on multiple columns.
• Example:
• CREATE INDEX idx_department_lastname ON Employees
(Department, LastName);
Types of Indexes
• c. Unique Index
• Ensures that all values in the indexed column(s) are unique.
Automatically created when you define a PRIMARY KEY or
UNIQUE constraint.
• Example:
• CREATE UNIQUE INDEX idx_unique_email ON Employees
(Email);
• d. Full-Text Index
• Optimized for searching large text fields. Ideal for
implementing search functionality within textual data.
• Example (MySQL):
• CREATE FULLTEXT INDEX ft_idx_description ON Products
(Description);
Column Name Explanation

Table Name of the table (Employee)

View Existing Indexes Non_unique

Whether the index allows duplicates:0 = unique (e.g., PRIMARY KEY,
UNIQUE) 1 = not unique

Name of the index: PRIMARY → primary keyphone_num → unique

Key_name
index

Order of the column in a composite index (1 = first column, 2 = second,

Seq_in_index
etc.)

Column_name Name of the column indexed (Emp_ID, phone_num)

Collation How the index is sorted: A = Ascending

Estimate of the number of unique values in the indexed columnHigher

Cardinality
= better selectivity

If only part of the column is indexed (e.g., first 10 chars of a string).

Sub_part
NULL means entire column is indexed

Packed How the key is packed (compression). Mostly NULL

Null Whether the column can be NULL (YES or empty for NOT NULL)

Index_type Index algorithm used: typically BTREE in MySQL

Comment Any comment for the index (rarely used)

Index_comment Optional comment when index was created

Visible Whether the index is visible to the optimizer (YES)

Used if the index is defined on an expression (e.g., generated column);

Expression
otherwise NULL
Create Index
• 1. Single-Column Indexes
• -- Index on emp_name for faster search/filtering
CREATE INDEX idx_emp_name ON Employee(emp_name);
• -- Index on city for faster filtering or grouping
CREATE INDEX idx_city ON Employee(city);
• -- Index on salary for range queries and ordering
CREATE INDEX idx_salary ON Employee(salary);

• 2. Multi-Column (Composite) Index

• Useful when you often query using multiple columns together.
• -- Composite index for filtering or sorting by city and designation
CREATE INDEX idx_city_designation ON Employee(city,
designation);
Create Index
• 3. Unique Index (already used)
• We're already using one:
• -- phone_num is UNIQUE (defined during table creation)
• -- So it has an implicit unique index

• 4. Fulltext Index (for TEXT/VARCHAR fields)

• If you need advanced searching (e.g., keyword matching on
notes):
• -- Fulltext index for searching inside large text
• CREATE FULLTEXT INDEX idx_notes ON Employee(notes);
• Use with MATCH(notes) AGAINST('text') syntax
Create Index
• 5. Indexing Date/Time Columns
• -- For filtering by DOB
• CREATE INDEX idx_dob ON Employee(DOB);
• -- For queries involving check-in time
• CREATE INDEX idx_checkin ON Employee(check_in);
Nested queries in SQL
• Nested queries are those queries which have an outer query
and inner subquery.
• The inner query executes first, and its result set is then used
by the outer query to achieve a more complex or specific data
retrieval.
SELECT column_name
FROM table_name1
WHERE VALUE IN
(SELECT column_name
FROM table_name2
WHERE condition)
Nested queries in SQL
• Rules to Use Subqueries in SQL
• Subqueries need to be enclosed in the Where clause and can be used with
Insert, Update, Delete, and Select statements.
• We can use comparison operators for example: <, >, > =, < =, !=, IN , Between
for the subqueries.
• The subquery is always executed first and then the main query.
• Subquery should be enclosed within parentheses.
• Subqueries are always to the right of the comparison operators.
• We can’t use Order By clause in the subquery; instead, we can use the Group
By clause.
• We can’t use Between clause with a subquery, but we can use Between in a
subquery.
Nested queries in SQL • Nested Query with IN
• Get employees working in any
• Simple Nested Query in WHERE department located in Amystad.
• Get employees who work in the same • SELECT emp_name
city as 'Ravi Patil'
• FROM Employee
• SELECT emp_name, city • WHERE Emp_ID IN (
• FROM Employee • SELECT manager_id
• WHERE city = ( • FROM Department
• SELECT city • WHERE City = 'Amystad’
• FROM Employee
• );
• WHERE emp_name = 'Ravi Patil’
• ); • Inner query → Managers of Amystad
departments.
• Inner query finds Ravi Patil city.
• Outer query returns all employees in that
city. • Outer query → Employee names of
those managers.
Nested queries in SQL • Get the average salary per
• Finding second highest city, then filter for cities
salary through nested where the average salary is
above 50,000.
query

• SELECT city, avg_salary

• SELECT max(salary) • FROM (
• FROM Employee • SELECT city, AVG(salary)
• WHERE salary < ( AS avg_salary
• FROM Employee
• SELECT MAX(salary)
• GROUP BY city
• FROM Employee
• ) AS city_avg
• ); • WHERE avg_salary >
50000;
Nested Query Examples
• Identify list of employee
having salary >4500.

• Update salary of an
employee with 0.25%
whose age is greater than
or equal to 29.
Data Control Language
• Data Control Language (DCL) in SQL is used to control access
and permissions for users in a database. DCL is mainly about
security & permissions.
• Commands that come under DCL: Grant, Revoke
• Grant: Gives specific privileges to a user or role.
• GRANT privilege_list ON object_name TO user_name;
• -- Give SELECT and UPDATE permission on Employee table to user
‘root’
• GRANT SELECT, UPDATE ON Employee TO ‘root'@'localhost’;

• privilege_list → SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES

• object_name → Table, view, database, etc.
• ‘root'@'localhost' → User account in MySQL.
Data Control Language
• Revoke: Removes previously granted privileges.
• REVOKE privilege_list ON object_name FROM user_name;
• Example
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
• -- Remove UPDATE permission from ‘root’
• REVOKE UPDATE ON Employee FROM ‘root'@'localhost’;
• Important Privileges in MySQL
• SELECT → Read data.
• INSERT → Add data.
• UPDATE → Modify data.
• DELETE → Remove data.
• ALL PRIVILEGES → All above + more.
• CREATE, DROP → Create or delete tables/databases.
• GRANT OPTION → Allow a user to grant privileges to others.
Data Control Language
• View User Privileges
• SHOW GRANTS FOR 'root'@'localhost’;

• To grant all permissions to a user in MySQL, you use the GRANT

ALL PRIVILEGES command.
• GRANT ALL PRIVILEGES ON database_name.* TO
'username'@'host’;
• database_name.* → All tables in a specific database.
• *.* → All databases and all tables.
• 'username'@'host' → The MySQL user account (host is usually
localhost or % for any host).
• -- Create a new user
• CREATE USER ‘root'@'localhost' IDENTIFIED BY 'password123';
Example GRANT ALL PRIVILEGES
• Example: Give full access to one database
• GRANT ALL PRIVILEGES ON mydb.* TO ‘root'@'localhost’;

• Example: Give full access to ALL databases

• GRANT ALL PRIVILEGES ON *.* TO ‘root'@'localhost’;

• Also Allow User to Grant Permissions to Others

• If you want the user to be able to grant privileges to others:
• GRANT ALL PRIVILEGES ON *.* TO ' root '@'localhost' WITH GRANT
OPTION;

• Apply Changes
• FLUSH PRIVILEGES;
• This reloads the privileges from the MySQL grant tables.
Difference between Grant and Revoke
[Link] GRANT REVOKE

1 GRANT command is used to give access The REVOKE command does just opposite to the
privileges to the users or other rights GRANT command. It withdraws user privileges on
or opportunities for the database. database objects.

2 It authorizes access preferences to It withdraws access preferences to users.

users.
3 In the GRANT command, you need to In the REVOKE command, if the access for one
define the permissions for each user. user is withdrawn, then all the permissions
provided by that particular person to others will
also be removed.
Transaction Control Language
• Transaction Control Language (TCL) commands are used to
manage transactions groups of SQL statements that are
executed as a single logical unit.
• Transactions are supported only in transactional storage
engines like InnoDB.
• By default, MySQL runs in autocommit mode, meaning each
statement is committed automatically unless you explicitly
start a transaction.
• commands that come under TCL:
• COMMIT
• ROLLBACK
• SAVEPOINT
Transaction Control Language
• TCL Commands in MySQL
• 1. START TRANSACTION / BEGIN
• Starts a new transaction so that changes are not saved
automatically.
• START TRANSACTION;
• -- or
• BEGIN;
• 2. COMMIT
• Saves all changes made in the current transaction permanently.
• COMMIT;

• 3. ROLLBACK
• Undoes all changes made in the current transaction.
• ROLLBACK;
Transaction Control Language
• 4. SAVEPOINT
• Creates a point inside a transaction that you can roll back
to without affecting earlier work.
• SAVEPOINT sp1;

• 5. ROLLBACK TO SAVEPOINT
• Rolls back to a specific savepoint.
• ROLLBACK TO SAVEPOINT sp1;

• 6. RELEASE SAVEPOINT
• Deletes a savepoint (so it can’t be rolled back to later).
• RELEASE SAVEPOINT sp1;
Transaction Control Language
• COMMIT
DELETE FROM CUSTOMERS
WHERE AGE = 25;
COMMIT;
• ROLLBACK
DELETE FROM CUSTOMERS
WHERE AGE = 25;
ROLLBACK;
• Rollback: Rollback command is used to undo transactions that
have not already been saved to the database.
• SAVEPOINT: It is used to roll the transaction back to a certain
point without rolling back the entire transaction.
• SAVEPOINT SAVEPOINT_NAME;
Transaction Control Language
• ROLLBACK TO SavePoint2;
• Once you execute the above
rollback statement, then
commit the transaction by
executing the below commit
statement which will
commit two records
(ProductId 1005 and 1006)
into the database.
• COMMIT;
Transaction Control Language • -- Step 3: Set a
SAVEPOINT after salary
• -- Step 1: Start the transaction update
• START TRANSACTION; • SAVEPOINT
after_salary_update;
• --Turns off autocommit and • --Marks this stage so we
begins a transaction. can come back to it if
needed.

• -- Step 2: Give a salary • -- Step 4: Reassign an

increment to an employee employee to a new
department
• UPDATE Employee • UPDATE Department
• SET salary = salary + 2000 • SET manager_id = 2
• WHERE dept_id = 1;
• WHERE Emp_ID = 1;
• --A manager change is
• --Employee with Emp_ID = 1 made in the Department
gets a salary increment. table.
Transaction Control Language • -- Step 6: Correct the
department assignment
• -- Step 5: Oops! Accidentally
assigned the wrong manager • UPDATE Department
• -- Rollback to the point after • SET manager_id = 3
salary update (undo • WHERE dept_id = 1;
department change only) • --Now assigns the
• ROLLBACK TO SAVEPOINT correct manager.
after_salary_update;
• -- Step 7: Commit the
final changes
• --Cancels only the department
• COMMIT;
change, but keeps the salary
update. • --Saves both the salary
update and correct
department assignment
permanently.
7th Highest Salary
• SELECT DISTINCT salary
• FROM Employee ORDER BY salary DESC
• LIMIT 1 OFFSET 6;

• ORDER BY salary DESC → sorts salaries from highest to lowest.

• LIMIT 1 OFFSET 6 → skips the top 6 salaries and picks the 7th.
• DISTINCT → ensures duplicate salaries are not counted multiple
times.
View Constraint on table
• Display the full table definition
mysql> SHOW CREATE TABLE Employee\G;
• Display all constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE,
CHECK).
SELECT
CONSTRAINT_NAME,
TABLE_NAME,
CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'semi25_26'
AND TABLE_NAME = 'Employee';
Query Languages

• INSERT INTO Employee_Copy (Emp_ID, emp_name, phone_num, city, Country,

salary, designation) VALUES
• (201, 'Amit Sharma', 9876543210, 'Pune', 'India', 55000.00, 'Manager'),
• (202, 'Priya Verma', 9123456780, 'Mumbai', 'India', 48000.00, 'Developer'),
• (203, 'John Doe', 9988776655, 'New York', 'USA', 72000.00, 'Analyst'),
• (204, 'Sara Khan', 8877665544, 'Delhi', 'India', 46000.00, 'HR'),
• (205, 'Michael Lee', 7766554433, 'London', 'UK', 85000.00, 'Consultant'),
• (206, 'Rajesh Kumar', 8899001122, 'Chennai', 'India', 50000.00, 'Team Lead'),
• (207, 'Anita Desai', 9001122334, 'Bangalore', 'India', 47000.00, 'Developer'),
• (208, 'David Miller', 7788990011, 'Sydney', 'Australia', 91000.00, 'Architect');
Add Same first 2 record into Employee_copy as
present in Employee Table

• INSERT INTO Employee_Copy

• ( Emp_ID, emp_name, phone_num, city, Country, salary, designation)
VALUES
• ( 1,'Anita Deshmukh', 9876543210, 'Bengaluru', 'India', 65000.00,
'Software Engineer'),
• (2,'Ravi Patil', 9988776655, 'Pune', 'India', 48000.50, 'QA Analyst');
SQL Commands: Set Operations • UNION ALL Operator in MySQL
• UNION Operator in MySQL
• The UNION operator is used to combine
• The UNION ALL operator is used to
the result set of two or more SELECT combine the result set of two or more
statements into a single result set by SELECT statements into a single result
removing the duplicate records. That
means the UNION Operator selects only including the duplicate values.
the distinct values.

• SELECT emp_name, city FROM • SELECT emp_name, city FROM

Employee Employee
• UNION
• SELECT emp_name, city FROM
• UNION All
Employee_Copy; • SELECT emp_name, city FROM
• Here we don’t have any duplicate data. Employee_Copy;
Here, in the result set, we got a total of
25 rows out of 27 rows. This is because • Here we got all the 27 rows in the
2 rows are present in both the result
set. result set.
SQL Commands: Set Operations • MySQL EXCEPT Operator:
• INTERSECT Operator in MySQL • The EXCEPT operator is used to combine two
• The INTERSECT operator is used to combine two tables or two result sets and will return rows
result sets and returns the data which are from the first select statement that are not
common in both the result set. present in the second select statement.
• But the INTERSECT Operator is not supported by • But, the EXCEPT Operator is not supported by
MYSQL. We can achieve the INTERSECT Operator MYSQL. We can achieve the EXCEPT Operator
functionality in MySQL using the following ways. functionality in MySQL using the following ways.
• Using IN Operator to achieve INTERSECT • Using NOT IN Operator to achieve EXCEPT
functionality: functionality:
• Here, we are checking the emp_name column • SELECT emp_name, city FROM Employee
value only. Following is the SQL Query using the IN
Operator which returns the common employees • WHERE emp_name NOT IN (SELECT
• SELECT emp_name, city FROM Employee emp_name FROM Employee_copy);
• WHERE emp_name IN (SELECT • EXCEPT (MySQL doesn't support EXCEPT —
emp_name FROM Employee_copy); simulate using LEFT JOIN + IS NULL):
• INTERSECT (MySQL doesn't support INTERSECT • SELECT e.emp_name, [Link]
directly — use INNER JOIN instead): • FROM Employee e
• SELECT emp_name, city FROM Employee • LEFT JOIN Employee_Copy c
• INNER JOIN Employee_Copy USING • ON e.emp_name = c.emp_name
(emp_name, city); • AND [Link] = [Link]
• WHERE c.emp_name IS NULL;
Query Languages

• manager_id must match an existing Emp_ID in Employee. If you

insert a value not in Employee, MySQL will throw a foreign key
constraint error.

• INSERT INTO Department (dept_name, location, manager_id) VALUES ('Legal',

'Hyderabad', 35);
• ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'manager_id
must match an existing Emp_ID in Employee. If you insert a value not ' at line 1
Add Data to Department Table
• INSERT INTO Department (dept_name, location, manager_id)
• VALUES('Customer Support', 'Kolkata', 6),
• ('Research & Development', 'Hyderabad', 7),
• ('Quality Assurance', 'Pune', NULL),
• ('Administration', 'Mumbai', NULL),
• ('Logistics', 'Ahmedabad', 11),
• ('Procurement', 'Jaipur', NULL),
• ('Legal', 'Hyderabad', NULL);
INNER JOIN
• List all departments along with their manager's name (only if the manager
exists in Employee table).
• SELECT
• d.dept_id,
• d.dept_name,
• [Link],
• e.emp_name AS manager_name
• FROM Department d
• INNER JOIN Employee e
• ON d.manager_id = e.Emp_ID;
• Only returns rows where manager_id is not NULL and matches an
employee.
LEFT JOIN
• List all departments, showing manager names if available (NULL if not
assigned).
• SELECT
• d.dept_id,
• d.dept_name,
• [Link],
• e.emp_name AS manager_name
• FROM Department d
• LEFT JOIN Employee e
• ON d.manager_id = e.Emp_ID;
• Shows all departments even if they have no valid manager assigned.
RIGHT JOIN
• List all employees who are managers, and their departments (if any).
• SELECT
• e.Emp_ID,
• e.emp_name,
• d.dept_name,
• [Link]
• FROM Employee e
• RIGHT JOIN Department d
• ON d.manager_id = e.Emp_ID;
• Shows all departments and the manager info, even if some managers
are missing.
• SELECT
FULL OUTER JOIN • e.Emp_ID,
• MySQL doesn’t directly support • e.emp_name,
FULL OUTER JOIN, We can simulate • d.dept_name,
• [Link]
it by combining a LEFT JOIN and a
• FROM Employee e
RIGHT JOIN with a UNION.
• LEFT JOIN Department d
• ON d.manager_id = e.Emp_ID
• UNION
• SELECT
• e.Emp_ID,
• e.emp_name,
• d.dept_name,
• [Link]
• FROM Employee e
• RIGHT JOIN Department d
• ON d.manager_id = e.Emp_ID;
SELF JOIN (Employee managing other Employees)

SELECT
e.emp_name AS employee,
m.emp_name AS manager
FROM Employee e
JOIN Employee m
ON e.Emp_ID = m.Emp_ID;
Query Languages

mysql> CALL viewEmployees();

Example : Show all Employees
DELIMITER $$
CREATE PROCEDURE ShowEmployees()
BEGIN
SELECT Emp_ID, emp_name, salary, designation
FROM Employee;
END $$
DELIMITER ;

• CALL ShowEmployees();
Example : Show all Departments
DELIMITER $$
CREATE PROCEDURE ShowDepartments()
BEGIN
SELECT dept_id, dept_name, location
FROM Department;
END $$
DELIMITER ;

CALL ShowDepartments();
Example With IN Parameter: Show employees from a given city
DELIMITER $$
CREATE PROCEDURE GetEmployeesByCity(IN cityName VARCHAR(100))
BEGIN
SELECT Emp_ID, emp_name, city, designation, salary
FROM Employee
WHERE city = cityName;
END $$
DELIMITER ;

CALL GetEmployeesByCity('Pune');
Example With IN Parameter : Show departments managed
by a specific manager
DELIMITER $$
CREATE PROCEDURE GetDepartmentsByManager(IN managerId INT)
BEGIN
SELECT dept_id, dept_name, location
FROM Department
WHERE manager_id = managerId;
END $$
DELIMITER ;

CALL GetDepartmentsByManager(3);
Example With IN Parameter : Increase Employee Salary
DELIMITER $$
CREATE PROCEDURE IncreaseSalary (
IN p_emp_id INT,
IN p_increment DECIMAL(10,2)
)
BEGIN CALL IncreaseSalary(1, 2000);
UPDATE Employee
SET salary = salary + p_increment
WHERE Emp_ID = p_emp_id;
END $$
DELIMITER ;
Example With IN OUT Parameter: Get salary of an employee by ID
DELIMITER $$
CREATE PROCEDURE GetEmployeeSalary(
IN empId INT,
OUT empSalary DECIMAL(10,2)
)
BEGIN CALL GetEmployeeSalary(1, @sal);
SELECT salary SELECT @sal AS Employee_Salary;
INTO empSalary
FROM Employee
WHERE Emp_ID = empId;
END $$
DELIMITER ;
SQL Function
• A function in MySQL is a subprogram that is used to perform an action such as complex
calculations and returns the result of the action as a value. There are two types of
functions available in MySQL. They as follows:
[Link] Defined Function
[Link]-Defined Function
• The functions which are already defined or predefined by MySQL and ready to be used
by the developer are called as system-defined function whereas if the function is
defined by the developer, then such functions are called as a user-defined function.
[Link] functions take parameters; do some processing and returning some results back.
For example, SELECT SQRT(16); will return the square root of 16 i.e. 4.
[Link] functions do not take any parameters but return some results back. For
example, SELECT NOW(); will return the current date-time like 2021-07-09 07:11:07.
SQL Function
• How to Create User-Defined Functions
• First, we need to specify the name of the user-defined
function
• Second, list all the input parameters of the user-defined
function
• Third, specify the data type of the return value in the
RETURNS statement.
• Fourth, specify if the function is deterministic or not
• If we don’t specify MySQL uses the NOT DETERMINISTIC
option. A deterministic function in MySQL always returns
the same result for the same input parameters whereas a
non-deterministic function returns different results for the
same input parameters.
• Fifth, write the code in the body of the user-defined
function within the BEGIN & END block.
SQL Function
• What is DETERMINISTIC or NOT
DETERMINISTIC
• DETERMINISTIC
• A function always returns the same
output for the same input.
• Example: mathematical functions
(square(x), add(a,b))
• NON-DETERMINISTIC
• A function may return different results
even with the same input.
• Example: functions using NOW(),
RAND(), CURDATE(), etc.
Example : Get Age from DOB
-- Change the delimiter so we can use ; inside the function body
DELIMITER $$
DROP FUNCTION IF EXISTS GetAge $$
CREATE FUNCTION GetAge(dob DATE)
RETURNS INT SELECT emp_name, GetAge(DOB) AS
NOT DETERMINISTIC Age FROM Employee;
NO SQL
BEGIN
DECLARE age INT;
SET age = TIMESTAMPDIFF(YEAR, dob, CURDATE());
RETURN age;
END $$
-- Reset delimiter back to normal
DELIMITER ;
Example : Calculate Bonus
DELIMITER $$
CREATE FUNCTION CalculateBonus(salary DECIMAL(10,2), experience
INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE bonus DECIMAL(10,2);
SET bonus = salary * (experience * 0.05);
RETURN bonus;
END $$ SELECT emp_name, salary, CalculateBonus(salary,
DELIMITER ; experience_years) AS Bonus FROM Employee;
Functions Vs Stored Procedures
Function Stored Procedure
Always returns a single value; either scalar or a table. Can return zero, single or multiple values.
Functions are compiled and executed at run time. Stored procedures are stored in parsed and
compiled state in the database.
Only Select statements. DML statements like update & Can perform any operation on database objects
insert are not allowed. including select and DML statements.
Allows only input parameters. Does not allow output Allows both input and output parameters
parameters.
Does not allow the use of Try…Catch blocks for exception Allows use of Try…Catch blocks for exception
handling. handling.
Cannot have transactions within a function. Can have transactions within a stored procedure.
Cannot call a stored procedure from a function. Can call a function from a stored procedure.
Functions can be called from a Select statement. Stored procedures cannot be called from a
Select/Where or Having statements. Execute
statement has to be used to execute a stored
procedure.
Functions can be used in JOIN clauses. Stored procedures cannot be used in JOIN clauses
Triggers in SQL
• Triggers are a set of SQL statements which are stored in the database catalog.
These statements are executed whenever an event associated with a table
occurs.
• So, a trigger can be invoked either BEFORE or AFTER the data is changed
by INSERT, UPDATE or DELETE statement.
• Before Insert: It is activated before the insertion of data into the table.
• After Insert: It is activated after the insertion of data into the table.
• Before Update: It is activated before the update of data in the table.
• After Update: It is activated after the update of the data in the table.
• Before Delete: It is activated before the data is removed from the table.
• After Delete: It is activated after the deletion of data from the table.
Triggers
• The following are the main characteristics that
distinguish triggers from stored procedures:
• We cannot manually execute/invoked triggers.
• Triggers have no chance of receiving
parameters.
• A transaction cannot be committed or rolled
back inside a trigger.
SN Parameters Triggers Procedures
A Procedure is explicitly called by the
A Trigger is implicitly invoked whenever any event such
1. Basics user/application using statements or commands such
as INSERT, DELETE, or UPDATE occurs in a TABLE.
as exec, EXECUTE, or simply procedure name

When an event occurs, a trigger helps to execute an A procedure helps to perform a specified task when
2. Action
action automatically. it is invoked.

We can define/call procedures inside another

3. Define/ call We cannot define/call a trigger inside another trigger.
procedure.

In a database, the syntax to define a trigger: CREATE In a database, the syntax to define a procedure:
4. Syntax
TRIGGER TRIGGER_NAME CREATE PROCEDURE PROCEDURE_NAME

Transaction Transaction statements such as COMMIT, ROLLBACK, and All transaction statements such as COMMIT and
5.
statements SAVEPOINT are not allowed in triggers. ROLLBACK are allowed in procedures.

Triggers are used to maintain referential integrity by Procedures are used to perform tasks defined or
6. Usage
keeping a record of activities performed on the table. specified by the users.

We cannot return values in a trigger. Also, as an input, We can return 0 to n values. However, we can pass
7. Return value
we cannot pass values as a parameter. values as parameters.
Triggers in SQL
• Syntax:
CREATE TRIGGER trigger_name
(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON table_name FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END;
Triggers in SQL :Auto-Calculate Age on INSERT
DELIMITER $$
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON Employee
SELECT emp_name, DOB, age FROM
FOR EACH ROW Employee WHERE emp_name = 'Ravi
BEGIN Kumar';
IF [Link] IS NOT NULL THEN
SET [Link] = TIMESTAMPDIFF(YEAR,
[Link], CURDATE());
END IF;
INSERT INTO Employee (emp_name, phone_num, city, Country,
END $$ blood_group, salary, designation, DOB)
VALUES ('Ravi Kumar', 9376943210, 'Delhi', 'India', 'O+', 55000.00,
DELIMITER ; 'Engineer', '1995-05-10');
Triggers in SQL :Auto-Update Age on DOB Change
DELIMITER $$
CREATE TRIGGER before_dob_update
BEFORE UPDATE ON Employee
FOR EACH ROW
BEGIN
IF [Link] <> [Link] THEN
SET [Link] = TIMESTAMPDIFF(YEAR,
[Link], CURDATE());
END IF;
END $$
DELIMITER ;
Triggers in SQL :Prevent Deletion of a Department with Employees
DELIMITER $$
CREATE TRIGGER prevent_dept_delete
BEFORE DELETE ON Department
FOR EACH ROW
BEGIN
IF EXISTS (SELECT 1 FROM Employee WHERE Emp_ID = OLD.manager_id)
THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Cannot delete department: Manager still exists in
Employee table';
END IF;
END $$
DELIMITER ;
View Procedures, Functions, And Triggers
• SHOW PROCEDURE STATUS WHERE Db = 'semi25_26';
• SHOW FUNCTION STATUS WHERE Db = 'semi25_26';
• SHOW TRIGGERS FROM semi25_26;

• mysql> use semi25_26

• Database changed
• mysql> SHOW CREATE PROCEDURE ShowEmployees;
• mysql> SHOW CREATE FUNCTION GetAge;
• mysql> SHOW CREATE TRIGGER before_employee_insert;
Views in SQL
• VIEW is a database object that can be created like a table. In SQL, a VIEW is
similar to a virtual table. But unlike tables VIEWS don’t actually store data.
• For security purposes, we can restrict users from accessing underlying
tables and instead give access to views or virtual tables with limited
columns.
• Since, every time user request view, the database engine recreates the
result set, which always returns up-to-date data rows from views.
Views in SQL
• Table Vs View
[Link] table is physical i.e. it is an actual table whereas the view is logical i.e.
it is a virtual table.
2.A Table is an independent object whereas a view is a dependent object.
[Link] Table stores the actual data of the database whereas View creates a
logical subset of data from one or more tables.
[Link] a new table is created from an existing table, the new and old tables
are independent themselves, that is the changes of one table will not be
reflected into the other table whereas if a view is created based on a table,
any changes that are performed on the table will reflect into the view and
any changes performed on the view reflected in the table also.
Creating a Simple View
CREATE VIEW Employee_Salary_View AS
SELECT emp_name, salary
FROM Employee;
• SELECT * FROM Employee_Salary_View;

• View with Join

• CREATE VIEW Emp_Dept_View AS
• SELECT e.Emp_ID, e.emp_name, [Link], d.dept_name,
[Link] FROM Employee e
• JOIN Department d ON e.Emp_ID = d.manager_id;
• SELECT * FROM Emp_Dept_View;
Updateable View
CREATE VIEW Active_Employees AS
SELECT Emp_ID, emp_name, city, salary FROM Employee
WHERE is_active = b'1';
UPDATE Active_Employees
SET salary = salary + 5000
WHERE Emp_ID = 2;

• DROP VIEW Employee_Salary_View;

Query Languages

• Relational Algebra
• SQL: DDL, DML, Select Queries, Set, String, Date
and Numerical Functions, Aggregate Functions
,Group by and Having Clause, Join Queries, Nested
queries, DCL, TCL
• PL/SQL: Procedure, Function, Trigger
• Mapping of Relational Algebra to SQL,
Assertions, roles and privileges
• Embedded SQL, Dynamic SQL.
Mapping of Relational Algebra to SQL
• σ → WHERE
• π → SELECT cols
• ρ → AS
• , −, ∩ → UNION, EXCEPT, INTERSECT (or
JOIN tricks in MySQL)
• × → CROSS JOIN
• → JOIN
• ÷ → Nested queries
Mapping of Relational Algebra to SQL
Relational
SQL
Algebra Example (Employee Table)
Equivalent
Operation
Selection (σ) Select
RA: σ salary > 50000 (Employee) SQL:
rows satisfying a WHERE clause
SELECT * FROM Employee WHERE salary > 50000;
condition
Projection (π) Select SELECT
RA: π emp_name, salary (Employee) SQL:
certain attributes specific
SELECT emp_name, salary FROM Employee;
(columns) columns
Renaming (ρ)
RA: ρ(Emp)(Employee) SQL:
Rename AS alias
SELECT emp_name AS Name FROM Employee;
relation/attributes
Union ( ∪ ) Combine
RA: Employee ∪ Employee_Copy SQL: SELECT
rows from two
UNION emp_name FROM Employee UNION SELECT
relations (removes
emp_name FROM Employee_Copy;
duplicates)
Mapping of Relational Algebra to SQL
Relational
Algebra SQL Equivalent Example (Employee Table)
Operation
RA: Employee − Employee_Copy SQL (MySQL way): SELECT
EXCEPT (MySQL →
e.emp_name FROM Employee e LEFT JOIN Employee_Copy
Set Difference ( − ) simulate using LEFT JOIN
c ON e.emp_name=c.emp_name WHERE c.emp_name IS
+ WHERE NULL)
NULL;
RA: Employee ∩ Employee_Copy SQL (MySQL way): SELECT
INTERSECT (MySQL →
Intersection ( ∩ ) e.emp_name FROM Employee e INNER JOIN
simulate with INNER JOIN)
Employee_Copy c ON e.emp_name=c.emp_name;
RA: Employee × Department SQL: SELECT * FROM
Cartesian Product (×) CROSS JOIN
Employee CROSS JOIN Department;
RA: Employee ⨝ Department (Emp_ID = manager_id) SQL:
INNER JOIN, LEFT JOIN,
Join (⨝) SELECT e.emp_name, d.dept_name FROM Employee e JOIN
etc.
Department d ON e.Emp_ID=d.manager_id;
RA: A ÷ B SQL: SELECT A.emp_id FROM WorksOn A
Division (÷) (find tuples Nested queries with NOT
WHERE NOT EXISTS (SELECT * FROM Project B WHERE
related to all tuples in EXISTS or GROUP BY
NOT EXISTS (SELECT * FROM WorksOn C WHERE
another relation) HAVING COUNT
C.emp_id=A.emp_id AND C.proj_id=B.proj_id));
Example with Employee Tables
• 1. Selection (σ)
• SELECT * FROM Employee WHERE city = 'Pune';
• 2. Projection (π)
• SELECT emp_name, salary FROM Employee;
• 3. Union
• SELECT emp_name FROM Employee
• UNION
• SELECT emp_name FROM Employee_Copy;
• 4. Join
• SELECT e.emp_name, d.dept_name
• FROM Employee e
• JOIN Department d ON e.Emp_ID = d.manager_id;
What is an Assertion in SQL?
• An assertion is a condition (rule) that must always hold true for
the database or It’s integrity constraints in SQL.
• If any operation (INSERT/UPDATE) violates it, the DBMS rejects
the change.
• MySQL does not support CREATE ASSERTION (only Oracle,
PostgreSQL, etc. support it partially).
• Assertions are defined using the CREATE ASSERTION statement
and include a CHECK clause that specifies the condition to be
enforced.
• If any database operation (such as INSERT, UPDATE, or DELETE)
would cause the assertion's condition to become false, the
operation is prevented, and an error is typically returned.
• Assertions are used to maintain data consistency and validity by
preventing operations that would lead to an invalid database state
according to defined business rules.
Assertion in SQL?
• Syntax (Standard SQL):
• CREATE ASSERTION assertion_name CHECK ( condition );
• Example Assertions
• 1. Salary must be greater than 0(Standard SQL)
• CREATE ASSERTION salary_positive
• CHECK (NOT EXISTS (
• SELECT * FROM Employee WHERE salary <= 0
• ));
• MySQL Equivalent → Use CHECK constraint
• ALTER TABLE Employee ADD CONSTRAINT chk_salary CHECK (salary > 0);
Assertion for An Employee’s Age must match DOB (Auto-calculated)
• MySQL Equivalent using Trigger:
• Standard SQL Assertion:
• DELIMITER $$
• CREATE ASSERTION • CREATE TRIGGER age_check
• BEFORE INSERT ON Employee
emp_age_check
• FOR EACH ROW
• CHECK (NOT EXISTS ( • BEGIN
• SELECT * FROM Employee • IF TIMESTAMPDIFF(YEAR, [Link],
CURDATE()) <> [Link] THEN
• WHERE TIMESTAMPDIFF(YEAR, • SIGNAL SQLSTATE '45000'
DOB, CURDATE()) <> age • SET MESSAGE_TEXT = 'Age does not
match DOB!';
• ));
• END IF;
• END$$
• DELIMITER ;
Roles and Privileges
• 1. What are Privileges?
• Privileges are the rights or permissions given to a user to
perform certain operations on the database.
• Examples:
• SELECT, INSERT, UPDATE, DELETE → Data operations
• CREATE, DROP, ALTER → Schema operations
• GRANT, REVOKE → Control access
• 2. What are Roles?
• A Role is a named collection of privileges that can be
assigned to one or more users.
Working with Roles & Privileges
• Create a User
CREATE USER ‘root'@'localhost' IDENTIFIED BY ‘root';
• Create a Role
CREATE ROLE 'hr_role’;
CREATE ROLE 'manager_role';
• Grant Privileges to a Role
• -- HR can only view employee data
GRANT SELECT ON Employee TO 'hr_role';
• -- Manager can update salaries
GRANT UPDATE(salary) ON Employee TO 'manager_role';
Working with Roles & Privileges
• Assign Role to a User
GRANT 'hr_role' TO ‘root'@'localhost';
• Activate Role for a Session
SET ROLE 'hr_role';
• Grant All Privileges (Super User)
• --WITH GRANT OPTION → allows this user to give their
privileges to others
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost'
WITH GRANT OPTION;
Working with Roles & Privileges
• Revoke Privileges / Roles
REVOKE UPDATE ON Employee FROM 'manager_role';
REVOKE 'hr_role' FROM ‘root'@'localhost';
• View Privileges
• --Show user’s privileges:
SHOW GRANTS FOR ‘root'@'localhost’;
• --Show role privileges:
SHOW GRANTS FOR 'hr_role';
Working with Roles & Privileges-Employee–Department
Database
• Create roles
• CREATE ROLE 'employee_reader', 'department_manager’;
• Assign privileges
• -- Employee reader can only view employees
• GRANT SELECT ON Employee TO 'employee_reader';
• -- Department manager can insert and update departments
• GRANT INSERT, UPDATE ON Department TO 'department_manager’;
• Assign to users
• CREATE USER 'john'@'localhost' IDENTIFIED BY 'john123';
• CREATE USER 'alice'@'localhost' IDENTIFIED BY 'alice123';
• GRANT 'employee_reader' TO 'john'@'localhost';
• GRANT 'department_manager' TO 'alice'@'localhost';
Query Languages

• Relational Algebra
• SQL: DDL, DML, Select Queries, Set, String, Date
and Numerical Functions, Aggregate Functions
,Group by and Having Clause, Join Queries, Nested
queries, DCL, TCL
• PL/SQL: Procedure, Function, Trigger
• Mapping of Relational Algebra to SQL,
Assertions, roles and privileges
• Embedded SQL, Dynamic SQL.
Embedded SQL
• How is SQL Statement Processed?
Embedded SQL
• Embedded SQL is a method that allows the integration of
SQL statements directly within the source code of a high-
level programming language, often referred to as a "host
language" (e.g., C, C++, COBOL, Fortran).
• This approach enables developers to combine the
computational power of a programming language with the
data manipulation capabilities of SQL.
• Embedded SQL, often called static SQL, refers to SQL
statements that are fixed in an application and can’t be
modified at runtime. Only at compile time these statements
get compiled.
Structure of Embedded SQL
• Connection to the database:
• Connection to the database to be
established first, it can be done either by
using any specific function provided by
the programming language or any
specific Database Management library.
• The CONNECT keyword may be accustomed
do that. It should, however, be preceded
with ‘EXEC SQL’ to specify the SQL
statement.
• EXEC SQL CONNECT db_name;
• EXEC SQL CONNECT HR_USER;
//connects to DB HR_USER
Structure of Embedded SQL
• Declaration Section: The declaration section is used to declare
variables that will be used in SQL queries and to capture the
results of those queries. Types of variables used in the host
language are:
• Host Variable: Host variables are variables of the host language
that are used to supply data and store the results of SQL queries.
The BEGIN DECLARE and END DECLARE sections are defined
within EXEC SQL and should be used to declare the variables
within the SQL code.
• EXEC SQL BEGIN DECLARE SECTION;
• int id;
• int roll_no;
• char name[10];
• char address[30];
• EXEC SQL END DECLARE SECTION;
• To indicate that the host variable could be a dynamic host, it
should be preceded by a colon — ‘:’ within the SQL query.
• EXEC SQL SELECT * FROM STUDENT WHERE STUDENT_ID
=:STD_ID;
Structure of Embedded SQL
• Indicator variables: Indicator variables are also
host variables but they are used to capture NULL
values which are returned by queries or to set
NULL values in INSERT or UPDATE statements.
• EXEC SQL SELECT NAME INTO :NAME
:IND_NAME FROM STUDENT WHERE ID = :ID;
• INSERT INTO STUDENT (ID, SNAME) VALUES
(:ID, :NAME :IND_NAME);
• UPDATE STUDENT SET ADDRESS = :ADDRESS
:IND_ADDRESS;
Structure of Embedded SQL
• Execution Section
• The execution section contains all the SQL queries and
statements that are prefixed by EXEC SQL.
• EXEC SQL SELECT * FROM STUDENT WHERE
STUDENT_ID = :STD_ID;
• Error Handling
• In embedded SQL, error handling is based on the host
language. In C programs, error handling is typically done
using labels and the WHENEVER statement. The
WHENEVER statement is used to define the action to be
taken when a certain condition occurs.
• The condition can be SQLWARNING, SQLERROR, or NOT
FOUND. The action can be CONTINUE, DO <function>,
GOTO , or STOP.
• Example
• EXEC SQL WHENEVER SQLWARNING DO
display_warning();
• EXEC SQL WHENEVER SQLERROR STOP;
• EXEC SQL WHENEVER NOT FOUND GOTO lbl_no_records;
Select Employees in C with Embedded SQL
#include <stdio.h> int main() {
#include <stdlib.h> /* Connect to the database */
#include <string.h> EXEC SQL CONNECT TO ‘employee'
/* Include SQL communications area USER 'root' IDENTIFIED BY ‘root';
*/
EXEC SQL INCLUDE sqlca; if ([Link] != 0) {
/* Declare host variables */ printf("Connection failed! Error
EXEC SQL BEGIN DECLARE SECTION; code: %d\n", [Link]);
int emp_id; exit(1);
char emp_name[100]; }
char city[50]; printf("Connected to database
successfully!\n");
EXEC SQL END DECLARE SECTION;
Select Employees in C with Embedded SQL
/* Fetch rows one by one */
/* Declare a cursor for fetching while (1) {
Employee data */ EXEC SQL FETCH emp_cursor INTO :emp_id,
:emp_name, :city;
EXEC SQL DECLARE emp_cursor
if ([Link] == 100) { // 100 = no more
CURSOR FOR rows
SELECT Emp_ID, emp_name, city break;
FROM Employee; }
if ([Link] < 0) {
printf("Error while fetching data. Code:
/* Open the cursor */ %d\n", [Link]);
break;
EXEC SQL OPEN emp_cursor;
}
printf("ID: %d, Name: %s, City: %s\n",
emp_id, emp_name, city);
printf("\nEmployee Records:\n");
}
Select Employees in C with Embedded SQL
• EXEC SQL ... → Embedded SQL
/* Close the cursor */
commands inside C.
EXEC SQL CLOSE emp_cursor;
• Host variables → C variables
/* Disconnect */ (emp_id, emp_name, city) are
EXEC SQL COMMIT WORK bound to SQL query results.
RELEASE;
• Cursor → Used to fetch
return 0; multiple rows one by one.
}
• [Link] → Stores the
status of SQL execution (0 =
success, 100 = no data).
Dynamic SQL
• Dynamic SQL is SQL code that is built and executed at runtime instead
of being hard-coded in advance.
• Unlike static SQL, where queries are fixed during the development phase, dynamic SQL
enables developers to build flexible and general-purpose SQL queries that adapt to varying
conditions.
• Dynamic SQL can be executed using commands like EXEC or
sp_executesql.
• Static SQL
• SELECT emp_name, salary FROM Employee WHERE Emp_ID = 101;
• Dynamic SQL
• SET @sql = 'SELECT emp_name, salary FROM Employee WHERE
Emp_ID = ?';
• PREPARE stmt FROM @sql;
• SET @id = 101;
• EXECUTE stmt USING @id;
• DEALLOCATE PREPARE stmt;
Dynamic SQL
• When to Use Dynamic SQL
• Dynamic SQL is particularly useful in the following scenarios:
• When query structure varies significantly based on user
input or business logic, such as filtering reports by different
criteria.
• Example: Building a WHERE clause dynamically based on
user-selected filters.
• Generating reports where column selection, sorting, or
grouping criteria are dynamically specified.
• When querying multiple databases or dynamically choosing
databases during runtime based on certain conditions.
• Automating the creation or modification of database objects
(e.g., tables, indexes, stored procedures) programmatically.
Simple Dynamic SQL
• use sycsa;
• SET @sql = 'SELECT emp_name, salary FROM Employee WHERE
salary > 50000';
• PREPARE stmt FROM @sql;
• EXECUTE stmt;
• DEALLOCATE PREPARE stmt;
• SET @sql = ... putting query in a variable (@sql).
• PREPARE stmt FROM @sql: The query string inside @sql is
compiled into an executable statement and stored as stmt.
• EXECUTE stmt : Runs the prepared statement (stmt). At this
point, the SQL actually hits the database and returns results.
• DEALLOCATE PREPARE stmt : Cleans up memory by removing
the prepared statement.
Dynamic SQL with Parameter
• SET @sql = 'SELECT emp_name, salary FROM Employee WHERE salary > ?';
• PREPARE stmt FROM @sql;
• SET @min_salary = 40000;
• EXECUTE stmt USING @min_salary;
• DEALLOCATE PREPARE stmt;
Dynamic SQL with Parameter
• -- Step 1: Define a variable (threshold
salary)
• SET @min_salary = 60000; • -- Step 4: Execute the dynamic SQL
• -- Step 2: Build the SQL string • EXECUTE stmt;
dynamically
• SET @sql = CONCAT('SELECT Emp_ID,
emp_name, salary • -- Step 5: Free resources
• FROM Employee • DEALLOCATE PREPARE stmt;
• WHERE salary > ',
@min_salary);

• -- Step 3: Prepare the statement

• PREPARE stmt FROM @sql;
Dynamic Column Selection
• -- Choose which column you want to see
• SET @column = 'city';

• -- Build query dynamically

• SET @sql = CONCAT('SELECT Emp_ID, emp_name, ', @column, ' FROM
Employee');

• -- Prepare & execute

• PREPARE stmt FROM @sql;
• EXECUTE stmt;
• DEALLOCATE PREPARE stmt;
Dynamic ORDER BY
• SET @order_by = 'salary';

• SET @sql = CONCAT('SELECT Emp_ID, emp_name, salary

• FROM Employee
• ORDER BY ', @order_by, ' DESC');

• PREPARE stmt FROM @sql;

• EXECUTE stmt;
• DEALLOCATE PREPARE stmt;

Unit 3 Relational Algebra
No ratings yet
Unit 3 Relational Algebra
77 pages
Introduction to Relational Algebra
No ratings yet
Introduction to Relational Algebra
31 pages
Relational Algebra and Normalization Guide
No ratings yet
Relational Algebra and Normalization Guide
54 pages
Understanding Relational Algebra Basics
No ratings yet
Understanding Relational Algebra Basics
57 pages
Formal Relational Query Languages Overview
No ratings yet
Formal Relational Query Languages Overview
51 pages
Understanding Relational Algebra Basics
No ratings yet
Understanding Relational Algebra Basics
20 pages
Relational Algebra Basics and Operators
No ratings yet
Relational Algebra Basics and Operators
44 pages
Understanding Relational Algebra Basics
No ratings yet
Understanding Relational Algebra Basics
30 pages
DBMS Unit 2
No ratings yet
DBMS Unit 2
134 pages
Module 3
No ratings yet
Module 3
9 pages
Relational Algebra Operations in DBMS
No ratings yet
Relational Algebra Operations in DBMS
3 pages
Relational Algebra Basics and Operations
No ratings yet
Relational Algebra Basics and Operations
70 pages
Understanding Relational Algebra Basics
No ratings yet
Understanding Relational Algebra Basics
42 pages
Relational Algebra Basics Explained
No ratings yet
Relational Algebra Basics Explained
21 pages
Relational Algebra Overview
No ratings yet
Relational Algebra Overview
32 pages
Relational Algebra Operations Explained
No ratings yet
Relational Algebra Operations Explained
9 pages
Introduction to Relational Algebra in DBMS
No ratings yet
Introduction to Relational Algebra in DBMS
14 pages
RelationalAlgebra RelationalCalculus SQL
No ratings yet
RelationalAlgebra RelationalCalculus SQL
119 pages
Understanding Query Languages in SQL
No ratings yet
Understanding Query Languages in SQL
78 pages
Relational Algebra and Calculus Overview
No ratings yet
Relational Algebra and Calculus Overview
58 pages
Relational Algebra Basics and Operations
No ratings yet
Relational Algebra Basics and Operations
31 pages
Relational Algebra Operations in DBMS
No ratings yet
Relational Algebra Operations in DBMS
67 pages
Relational Algebra
No ratings yet
Relational Algebra
67 pages
MDM Dbms - 3 - 1770487219570
No ratings yet
MDM Dbms - 3 - 1770487219570
12 pages
Unary Operations in Relational Algebra
No ratings yet
Unary Operations in Relational Algebra
11 pages
Relational Query Language in DBMS
No ratings yet
Relational Query Language in DBMS
28 pages
Chapter 3 Relational Query Language - Ee7bb776 3799 4e9e Bc39 02e205927a71
No ratings yet
Chapter 3 Relational Query Language - Ee7bb776 3799 4e9e Bc39 02e205927a71
104 pages
Understanding Query Languages
No ratings yet
Understanding Query Languages
84 pages
Understanding Relational Algebra Basics
No ratings yet
Understanding Relational Algebra Basics
113 pages
Relational Algebra Operations Explained
No ratings yet
Relational Algebra Operations Explained
19 pages
Relational Algebra and Calculus Overview
No ratings yet
Relational Algebra and Calculus Overview
29 pages
Relational Algebra and Calculus Overview
No ratings yet
Relational Algebra and Calculus Overview
30 pages
Relational Algebra Operations Overview
No ratings yet
Relational Algebra Operations Overview
59 pages
Relational Algebra Basics for Query Languages
No ratings yet
Relational Algebra Basics for Query Languages
10 pages
Relational Algebra Basics in DBMS
No ratings yet
Relational Algebra Basics in DBMS
24 pages
Unit - 2
No ratings yet
Unit - 2
25 pages
Relational Algebra and SQL Overview
No ratings yet
Relational Algebra and SQL Overview
61 pages
Understanding Relational Algebra Basics
No ratings yet
Understanding Relational Algebra Basics
87 pages
Query Processing and Optimization Guide
No ratings yet
Query Processing and Optimization Guide
184 pages
Relational Query Languages and Relational Opertations
No ratings yet
Relational Query Languages and Relational Opertations
58 pages
Relational Algebra Basics in DBMS
No ratings yet
Relational Algebra Basics in DBMS
27 pages
Relational Algebra Summary
No ratings yet
Relational Algebra Summary
3 pages
Relational Query Languages Explained
No ratings yet
Relational Query Languages Explained
15 pages
Deadlock Handling and Relational Algebra in DBMS
No ratings yet
Deadlock Handling and Relational Algebra in DBMS
188 pages
Relational Algebra and Calculus Overview
No ratings yet
Relational Algebra and Calculus Overview
39 pages
Understanding Relational Query Languages
No ratings yet
Understanding Relational Query Languages
15 pages
Understanding Relational Algebra Basics
No ratings yet
Understanding Relational Algebra Basics
13 pages
Relational Query Language Overview
No ratings yet
Relational Query Language Overview
26 pages
Relational Algebra in DBMS Explained
No ratings yet
Relational Algebra in DBMS Explained
13 pages
Relational Algebra & Calculus in SQL
No ratings yet
Relational Algebra & Calculus in SQL
106 pages
Mastering SQL and Relational Algebra
No ratings yet
Mastering SQL and Relational Algebra
83 pages
Understanding Relational Algebra in DBMS
No ratings yet
Understanding Relational Algebra in DBMS
10 pages
Relational Query Languages Overview
No ratings yet
Relational Query Languages Overview
27 pages
Fundamental Operators in Relational Algebra
No ratings yet
Fundamental Operators in Relational Algebra
15 pages
Relational Algebra in Database Systems
No ratings yet
Relational Algebra in Database Systems
30 pages
Relational Algebra: Query Processing Guide
No ratings yet
Relational Algebra: Query Processing Guide
143 pages
DBMS U - 2
No ratings yet
DBMS U - 2
14 pages
Hierarchical Report for VENUM Data
No ratings yet
Hierarchical Report for VENUM Data
3 pages
GE 3171 Python Programming Lab Guide
No ratings yet
GE 3171 Python Programming Lab Guide
3 pages
Android UI Components Implementation
No ratings yet
Android UI Components Implementation
8 pages
My Revision Notes Ocr Gcse Computer Science Third Edition Sample Pages 9781398321144
No ratings yet
My Revision Notes Ocr Gcse Computer Science Third Edition Sample Pages 9781398321144
16 pages
HCI Exam MCQs and Concepts Guide
No ratings yet
HCI Exam MCQs and Concepts Guide
14 pages
CSS 2019 Computer Science Past Papers
No ratings yet
CSS 2019 Computer Science Past Papers
5 pages
Cryptography Homework: Perfect Secrecy
No ratings yet
Cryptography Homework: Perfect Secrecy
11 pages
Presentation 1
No ratings yet
Presentation 1
12 pages
Employee Laptop Buyback Policy
No ratings yet
Employee Laptop Buyback Policy
2 pages
Oracle PLSQL Database Administration Guide
No ratings yet
Oracle PLSQL Database Administration Guide
40 pages
Neural Networks in Deep Learning Applications
No ratings yet
Neural Networks in Deep Learning Applications
15 pages
Penilaian Simulasi Digital SMK 2023
No ratings yet
Penilaian Simulasi Digital SMK 2023
6 pages
Understanding Midrise Uniform Quantisation
No ratings yet
Understanding Midrise Uniform Quantisation
8 pages
Aadhaar Details for Madan Mohan Siromani
No ratings yet
Aadhaar Details for Madan Mohan Siromani
1 page
Computer Graphics in Architecture and BIM
No ratings yet
Computer Graphics in Architecture and BIM
16 pages
Image Pruning via Belief Propagation
No ratings yet
Image Pruning via Belief Propagation
69 pages
Python List Operations and Sorting
No ratings yet
Python List Operations and Sorting
7 pages
Input and Output Devices in Graphics
100% (1)
Input and Output Devices in Graphics
15 pages
Advance Degree Application Form
100% (1)
Advance Degree Application Form
1 page
PLCs vs Microcontrollers: Pros and Cons
100% (7)
PLCs vs Microcontrollers: Pros and Cons
3 pages
Master Pages and CSS in Web Design
No ratings yet
Master Pages and CSS in Web Design
30 pages
Ablerex Ares Plus RT 3000 Manual
No ratings yet
Ablerex Ares Plus RT 3000 Manual
48 pages
IT Law Framework in India: IT Act & IPC
No ratings yet
IT Law Framework in India: IT Act & IPC
36 pages
AC2Meter User Manual v3.1+
No ratings yet
AC2Meter User Manual v3.1+
35 pages
Ansible DevSecOps Petshop CI/CD Guide
No ratings yet
Ansible DevSecOps Petshop CI/CD Guide
35 pages
Features of Operating Systems Explained
No ratings yet
Features of Operating Systems Explained
38 pages
Benzara MBA Placement Opportunity 2023
No ratings yet
Benzara MBA Placement Opportunity 2023
3 pages
LMT Circuit Modeling with SPICE
No ratings yet
LMT Circuit Modeling with SPICE
5 pages
AS DEALER: Cashback & Discounts Guide
No ratings yet
AS DEALER: Cashback & Discounts Guide
2 pages

Overview of Query Languages and Algebra

Uploaded by

Overview of Query Languages and Algebra

Uploaded by

Unit 3 Query Languages

“A person who never made a

• Answer: Let BoB need $y more to buy the ticket.

• So, we will solve this problem using algebra, 3 + y = 4,

• We add y to 3 to get [Link] we need to find the value of y.

• Q: SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE

• 1] List students whose "AGE" is 20

• 1. Find the names of all red parts.

• For example : Consider the tables Student_Details and Student_Result. Now,

• For example : Consider the tables Student_Details and Student_Result. Now,

• For example : Consider the tables Student_Details and Student_Result. Now,

int It is used to specify an integer value.

smallint It is used to specify small integer value.

bit It has the number of bits to store.

mysql> desc employee;

UPDATE Employee UPDATE Employee

UPDATE table_name UPDATE Employee

UPDATE Employee UPDATE Employee

• Add age Column

• Update age from DOB

• 2. Average Salary of All Employees

• 3. Minimum and Maximum Bonus

• 5. Average Salary by Designation

• 6. Employee Count by City

• 8. Average Experience by Role

• 9. Total and Average Salary by Country

• 10. Number of Employees Per Project Count

• 10. Combine Functions

• 2. UPPER() / LOWER() – Change

CONCAT() Join strings

SELECT emp_name, LOCATE('an', LENGTH() Get byte length

SUBSTRING() Extract substring

SELECT emp_name, blood_group, INSTR()/LOCATE() Find position of substring

• Example With Index:

Table Name of the table (Employee)

View Existing Indexes Non_unique

Name of the index: PRIMARY → primary keyphone_num → unique

Order of the column in a composite index (1 = first column, 2 = second,

Column_name Name of the column indexed (Emp_ID, phone_num)

Collation How the index is sorted: A = Ascending

Estimate of the number of unique values in the indexed columnHigher

If only part of the column is indexed (e.g., first 10 chars of a string).

Packed How the key is packed (compression). Mostly NULL

Index_type Index algorithm used: typically BTREE in MySQL

Comment Any comment for the index (rarely used)

Index_comment Optional comment when index was created

Visible Whether the index is visible to the optimizer (YES)

Used if the index is defined on an expression (e.g., generated column);

• 2. Multi-Column (Composite) Index

• 4. Fulltext Index (for TEXT/VARCHAR fields)

• SELECT city, avg_salary

• privilege_list → SELECT, INSERT, UPDATE, DELETE, ALL PRIVILEGES

• To grant all permissions to a user in MySQL, you use the GRANT

• Example: Give full access to ALL databases

• Also Allow User to Grant Permissions to Others

2 It authorizes access preferences to It withdraws access preferences to users.

• -- Step 2: Give a salary • -- Step 4: Reassign an

• ORDER BY salary DESC → sorts salaries from highest to lowest.

• INSERT INTO Employee_Copy (Emp_ID, emp_name, phone_num, city, Country,

• INSERT INTO Employee_Copy

• SELECT emp_name, city FROM • SELECT emp_name, city FROM

• manager_id must match an existing Emp_ID in Employee. If you

• INSERT INTO Department (dept_name, location, manager_id) VALUES ('Legal',

mysql> CALL viewEmployees();

We can define/call procedures inside another

• mysql> use semi25_26

• View with Join

• DROP VIEW Employee_Salary_View;

• -- Step 3: Prepare the statement

• -- Build query dynamically

• -- Prepare & execute

• SET @sql = CONCAT('SELECT Emp_ID, emp_name, salary

• PREPARE stmt FROM @sql;

You might also like