R.M.D.

ENGINEERING COLLEGE

24CS303 - DATABASE MANAGEMENT SYSTEM

UNIT 2
STRUCTURED QUERY LANGUAGE
COURSE OBJECTIVES
• To understand the basic concepts of Data modeling and Database
Systems.
• To understand SQL and effective relational database design concepts.
• To learn relational algebra, calculus and normalization
• To know the fundamental concepts of transaction processing,
concurrency control techniques, recovery procedure and data storage
techniques.
• To understand query processing, efficient data querying and advanced
databases.
 SYLLABUS – UNIT 2

UNIT II STRUCTURED QUERY LANGUAGE

SQL Data Definition and Data Types – Constraints – Queries –
INSERT, UPDATE, and DELETE in SQL - Views - Integrity
Procedures, Functions, Cursor and Triggers - Embedded SQL -
Dynamic SQL.
List of Exercise/Experiments
Case Study using real life database applications anyone from the following
list and do the following exercises.
a) Inventory Management for a EMart Grocery Shop
b) Society Financial Management
c) Cop Friendly App – Eseva
d) Property Management – eMall
e) Star Small and Medium Banking and Finance
1. Data Definition Commands, Data Manipulation Commands for inserting, deleting,
updating and retrieving Tables and Transaction Control statements
2. Database Querying – Simple queries, Nested queries, Sub queries and Joins
3. Views, Sequences, Synonyms
4. Database Programming: Implicit and Explicit Cursors
5. Procedures and Functions
6. Triggers
7. Exception Handling
COURSE OUTCOMES

CO1: Map ER model to Relational model to perform database design

effectively.
CO2: Implement SQL and effective relational database design
concepts.
CO3: Apply relational algebra, calculus and normalization techniques
in database
design.
CO4: Understand the concepts of transaction processing, concurrency
control,
recovery procedure and data storage techniques.
CO5: Apply query optimization techniques and understand advanced
databases.
Structured query language
(SQL)
• Structured query language (SQL) is a programming language for
storing and processing information in a relational database. A
relational database stores information in tabular form, with rows and
columns representing different data attributes and the various
relationships between the data values. SQL statements can be used to
store, update, remove, search, and retrieve information from the
database. SQL can also be used to maintain and optimize database
performance.
SQL commands
• Structured query language (SQL) commands are specific keywords or
SQL statements that developers use to manipulate the data stored in a
relational database. You can categorize SQL commands as follows.
Data definition language
• Data definition language (DDL) refers to SQL commands that design the
database structure. Database engineers use DDL to create and modify
database objects based on the business requirements. For example, the
database engineer uses the CREATE command to create database
objects such as tables, views, and indexes.
Data query language
• Data query language (DQL) consists of instructions for retrieving data
stored in relational databases. Software applications use the SELECT
command to filter and return specific results from a SQL table.
Data manipulation language
• Data manipulation language (DML) statements write new information or
modify existing records in a relational database. For example, an
application uses the INSERT command to store a new record in the
database.
Data control language
• Database administrators use data control language (DCL) to manage or
authorize database access for other users. For example, they can use
the GRANT command to permit certain applications to manipulate one
or more tables.
Transaction control language
• The relational engine uses transaction control language (TCL) to
automatically make database changes. For example, the database uses
the ROLLBACK command to undo an erroneous transaction.
Data types in SQL:
• char(n): Fixed length character string, with user-specified length ‘n’.
• varchar(n): Variable length character strings, with user-specified
maximum length n.
• Int: Integer (a finite subset of the integers that is machine-
dependent).
32 bits
• Smallint: Small integer (a machine-dependent subset of the integer
domain type). 16 bits.
• numeric(p,d): Fixed point number, with user-specified precision of
p digits, with d digits to the right of decimal point.
 Data types in SQL:
• real, double precision: Floating point and double-precision
floating point (no parameters req. Maxi 64 bits)numbers, with
machine-dependent precision
• float(n): Floating point number, with user-specified precision
of at least n digits.
• date: A calendar date containing a (four digit) year, month
and day of the month. Date values should be specified in the
form: YYYY-MM-DD
• time: the time of day in hours, minutes and seconds.
for example, TIME '07:30:00'
• timestamp: a combination of date and time. Format :YYYY-
MM-DD HH:MM:SS. Eg: TIMESTAMP '1999-04-04 07:30:00'
 CONSTRAINTS

• Constraints are part of the table definition that limits and restriction
on the value entered into its columns.

TYPES OF CONSTRAINTS:
1) Primary key Constraints
2) Foreign key/references(Integrity constraint)
3) Check
4) Unique
5) Not null
6) Default
CONSTRAINTS
PRIMARY KEY CONSTRAINTS:

• A primary key is a unique field of the table

• It does not allow duplicate and null values.

“A primary key, is a column in a relational database table that's distinctive

for each record”
Primary Key Constriant
mysql>create table employee(eno int primary key, ename
varchar(10), salary int, address varchar(35), deptno
int);
• Query OK, 0 rows affected

mysql>insert into employee

values(1001,’lalith’,50000,’chennai’,1),(1002,’lalitha’,55000,
’chennai’,2);
• Query OK, 2 rows affected

mysql> select *from customer;

PRIMARY KEY CONSTRAINTS
ENO ENAME SALARY CITY Existing table
1001 lalith 50000 chennai
1002 lalitha 55000 chennai
• 2 rows in set

Errors :
insert into employee values(1001,'Kavi',65000,'chennai',2);
• ERROR 1062 (23000) at line 11: Duplicate entry '1001' for key
'[Link]'
insert into employee values(null,'jahnavi',60000,'AP', 2);
• ERROR 1048 (23000) at line 8: Column 'eno' cannot be null
CONSTRAINTS
UNIQUE KEY CONSTRAINTS:

• A unique key does not allow duplicate values, but it accepts null values.
• A table can have more than one unique key

“A unique key is a set of one or more than one fields/columns of a table

that uniquely identify a record in a database table”
UNIQUE KEY CONSTRAINT
mysql> create table emp(eno int unique, ename varchar(6));
• Query OK, 0 rows affecte

mysql> insert into emp values(1001, ‘abitha’),

(1002,’banu’);
• Query OK, 2 rows affected

mysql> SELECT *FROM emp;

ENO ENAME
-------- ----------
1001 abitha
1002 banu
• 2 rows in set
CONSTRAINTS
insert into emp values(1001, 'janani');
• ERROR 1062 (23000) at line 9: Duplicate entry '1001' for key
'[Link]'

insert into emp values(null, 'chitra');

Query OK, 1 row affected

select * from emp;

eno ename
------ ----------
1001 abitha
1002 banu
NULL chitra
CONSTRAINTS
DEFAULT CONSTRAINTS:
The DEFAULT constraint is used to set a default value for a column. The default value will
be added to all new records, if no other value is specified.

mysql> create table emp(num int, name varchar(20), bonus int default
5000);
mysql> insert into emp values(101,'dev',10000);
#specified
mysql> insert into emp(num,name) values(102,'abi'); #not
specified

mysql> select * from emp;

| num | name | bonus |
-----------------------------
CONSTRAINTS

CHECK CONSTRAINTS:
Check constraint is used to check the values for specific attribute at the time of
insertion. If check constraint is violated, the record is not allowed to be inserted.
• MYSQL does not support CHECK constraints. They are just ignored.
mysql> create table customercheck (cusno int,cusname varchar(30), sal int ,
check(sal>5000));
• Query OK, 0 rows affected

mysql> insert into customercheck values(101,’karthick’,45000),(102,’aruna’,60000);

• Query OK, 2 rows affected
Giving the constraint separately :
mysql> select *from customercheck;
CUSNO CUSNAME SAL CONSTRAINT emp_salary_check CHECK (sal >= 5000)
--------- --------------- ----------
101 karthick 45000 Have to give a name of
102 aruna 60000 check constraint
CHECK CONSTRAINTS:

• Example:

CREATE TABLE Persons (

ID int NOT NULL,
FirstName varchar(255) NOT NULL,
Age int NOT NULL,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City=‘Chennai')
);
CONSTRAINTS
NOT NULL CONSTRAINTS:
• Does not allow the attribute value to be null
mysql>create table customernull (cusno int, cusname varchar(5) not null, salary int not
null, c_ph
int)
• Query OK, 0 rows affected
mysql>insert into customernull values(1,'a' ,40000,123456),(2,’b’,60000,null);
• Query OK, 2 rows affected
mysql>insert into customernull values(3,’c’,null,32456);
• Error: cannot insert NULL into (3.”c”."CUSTOMERNULL"."SALARY")
mysql> select *from customernull;
CUSNO CUSNAME CUSAL C_PH
---------- ----------- -------- ------------
1 a 40000 123456
2 b 60000 32456
CONSTRAINTS
FOREIGN KEY Constraint

• A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the PRIMARY KEY in another table.
• The table with the foreign key is called the child table, and the table with the
primary key is called the referenced or parent table.
• The FOREIGN KEY constraint is used to prevent actions that would destroy
links between tables.
FOREIGN KEY Constraint

Attribute
name(primary
Table key)
name

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column,
because it has to be one of the values contained in the parent table.
CONSTRAINTS
FOREIGN KEY CONSTRAINTS:
• Creation of Parent table with primary key
Mysql > create table first(rno int primary key,name varchar(20), marks
int);
Query OK, 0 rows affected (0.03 sec)

• Creation of child table with foreign key

Mysql > create table second(regno int, contact int, foreign key(regno)
references
first(rno));
Query OK, 0 rows affected (0.07 sec)
CONSTRAINTS
• Inserting records in parent table
Mysql > insert into first values(101,'vini',99),(102,'arun', '89'),(103,'kabi',100);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
Mysql > select * from first;
+------+------
| rno | name | marks |
+-----+------+------
| 101 | vini | 99 |
| 102 | arun | 89 |
| 103 | kabi | 100 |
+-----+------+-------
3 rows in set (0.02 sec)
CONSTRAINTS
• Inserting records in child table
Mysql > insert into second values(105,895478);
• ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
(``.`second`, CONSTRAINT `second_ibfk_1` FOREIGN KEY (`regno`) REFERENCES `first`
(`rno`))
Mysql > insert into second values(101,895478),(102,4743135);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
Mysql > select * from second;
+-------+---------
| regno | contact |
+-------+---------
| 101 | 895478 |
| 102 | 4743135 |
+-------+---------
CONSTRAINTS
• Deleting a record form parent table
mysql > delete from first where rno=101;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(``.`second`, CONSTRAINT `second_ibfk_1` FOREIGN KEY (`regno`) REFERENCES `first`
(`rno`))
mysql >select * from first;
+-----+------+-------
| rno | name | marks |
+-----+------+-------
| 101 | vini | 99 |
| 102 | arun | 89 |
| 103 | kabi | 100 |
3 rows in set (0.00 sec)
mysql> select * from second;
| regno | contact |
DATA-DEFINITION LANGUAGE (DDL)
• Data Definition Language (DDL) statements are used to define and
modify the database structure or schema.
• The SQL data definition language allows specification of not only a set
of relations but also information about each relation, including
The schema for each relation.
The domain of values associated with each attribute.
Some examples:
 CREATE - to create database objects
 ALTER - alters the structure of the database
 DROP - delete objects from the database
 TRUNCATE - remove all records from a table ,but, the table still
exists
 RENAME - rename an object
DATA-DEFINITION LANGUAGE (DDL)
Creating Tables:
• The SQL command for creating an empty table has the following form:
Syntax:
create table <table-name> (<column 1> <data type> ,<column2t>
<data type>, . . . , .<column n> <data type>, <integrity constraint1>,….,
<integrity constraint n>);
Example:
• The create table statement for EMP table has the form

create table EMP (empno int primary key, ename varchar(30) NOT NULL,
salary decimal(7,2));
Empno Empname Salary
Table name
EMP
DATA-DEFINITION LANGUAGE (DDL)

Deleting tables:
• The following command will drop the table from the database.
Syntax:
• drop table <table-name>;
Example:
drop table emp;
drop table emp,project; #dropping two tables together.
DATA-DEFINITION LANGUAGE (DDL)
Alter table:
• Alter table command is used to add, modify or drop attributes or columns
from the table.
Syntax:
• alter table <table-name> add/modify <col-name1> <data type>,….,<col-name
n>
<data type>;
• alter table <table-name> drop <col-name>;
Example:
• alter table emp add year varchar(2); //adds a column to the table
• alter table emp modify year varchar(4); // modify the column name
• alter table emp drop year //delete/drop a column from
the table
Empno Empname Salary Empno Empname Salary Year
 Alter commands
• To add a column in a table
• ALTER TABLE table_name ADD column_name datatype;
• ALTER TABLE Customers ADD Email varchar(255);
• To delete a column in a table
• ALTER TABLE table_name DROP COLUMN column_name;
• ALTER TABLE Customers DROP COLUMN Email;
• To rename a column in a table
• ALTER TABLE table_name RENAME COLUMN old_name to new_name;
• ALTER TABLE table_name CHANGE COLUMN oldcolumn_name to
newcolumn_name;
• To delete the column
• ALTER TABLE table_name DROP COLUMN column_name;
• ALTER TABLE Persons DROP COLUMN DateOfBirth;
DATA-DEFINITION LANGUAGE (DDL)
Truncate table:
• The truncate table command deletes the rows in the table but retains
the table structure.
Syntax:
• truncate table <table-name>;
Example:
• truncate table emp;

Empno Empname Salary After Empno Empname Salary

101 Abc 10000 Truncate
102 xyz 20000
DATA MANIPULATION LANGUAGE
Select clause:
• The select clause is used to select a particular or all attributes from
relations.
Syntax:
• select <attribute list> or (*) from <table list> where <condition>;
Example:
• select * from emp;
• select * from emp where empno =1000;
• select empno, ename from emp;
• select ename from emp where empno=2000;
DATA MANIPULATION LANGUAGE
Insert command:
• Insert command is used to insert single or multiple rows in the
table.
Syntax:
Single row:
• insert into <table-name> values (list of values);
Example:
Single row:
• insert into emp values(1000,’anitha’,10000);
Multiple rows:
• insert into emp values(1001,'banu',20000),(1002,'anu', 25000),
(1003, 'hari’, 10000);
Insert values for specific columns:
DATA MANIPULATION LANGUAGE
Update command:
• The update command is used to modify attribute values of one or more
selected tuples. It includes a where clause to select the tuples to be
modified from a single relation.
Syntax:
update <table-name> set col1=val1,…,coln = valn where
condition;
Example:
Empnoupdate emp set
Empname salary = 30000
Salary where
Empno empno =101;
Empname Salary
101 Abc 10000 101 Abc 30000
102 xyz 20000 102 xyz 20000

Before update After update

DATA MANIPULATION LANGUAGE

Delete command:
• The delete command removes tuples from a relation. It
includes the where clause to select the tuples to be deleted.
Syntax:
• delete from <table-name> where attribute-name = ‘value’ or
value;
Example:
• delete from emp where ename = ‘anitha’;
• delete from emp where Empno = Empno
101; Empname Salary
102 xyz 2000
TRANSACTION CONTROL LANGUAGE
• There are following commands used to control transactions:
• COMMIT: to save the changes.
• ROLLBACK: to rollback the changes.
• SAVEPOINT: creates points within groups of transactions in which
to ROLLBACK
• They cannot be used while creating tables or dropping them
because these operations are automatically committed in the
database.
TRANSACTION CONTROL LANGUAGE
THE COMMIT COMMAND
• The COMMIT command is the transactional command used to save
changes invoked by a transaction to the database.
• The COMMIT command saves all transactions to the database since the
last COMMIT or ROLLBACK command.
• SYNTAX: COMMIT;
THE ROLLBACK COMMAND
• The ROLLBACK command is the transactional command used to undo
transactions that have not already been saved to the database.
• The ROLLBACK command can only be used to undo transactions since
the last COMMIT or ROLLBACK command was issued.
• SYNTAX: ROLLBACK
TRANSACTION CONTROL LANGUAGE
THE SAVEPOINT COMMAND
• A SAVEPOINT is a point in a transaction when you can roll the transaction back to
a certain point without rolling back the entire transaction.
• SYNTAX: SAVEPOINT SAVEPOINT_NAME;
• This command serves only in the creation of a SAVEPOINT among transactional
statements. The ROLLBACK command is used to undo a group of transactions.
• SYNTAX: ROLLBACK TO SAVEPOINT_NAME;
The RELEASE SAVEPOINT Command
• The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you
have created.
• Syntax: RELEASE SAVEPOINT SAVEPOINT_NAME;
• Once a SAVEPOINT has been released, you can no longer use the ROLLBACK
command to undo transactions performed since the SAVEPOINT.
TRANSACTION CONTROL LANGUAGE
• create table student(id int, name varchar(30), city
varchar(20));
• start transaction;
• insert into student values(101,'arun','chennai'), (102,
'adhi','nellore');
• select
id *name
from city
student;
101 arun chennai
102 adhi nellore
TRANSACTION CONTROL LANGUAGE
• savepoint s;
• insert into student values(103,'aron','chennai'), (104,
'john','trichy');
• select * from student;
id name city
101 arun chennai
102 adhi nellore
103 aron chennai
104 John trichy
TRANSACTION CONTROL LANGUAGE

• rollback to savepoint s;
• select * from student;

id name city
101 arun chennai
102 adhi nellore
DATA CONTROL LANGUAGE
[Link]: GRANT
• Syntax: GRANT permissions ON objects TO account
Example Query 1:
GRANT INSERT ON employee TO EMP1;
Grants the INSERT privilege on the employee table to the user or role
EMP1, allowing them to add rows to this table.
Example Query 2:
GRANT SELECT,UPDATE ON employee TO username;
[Link]: REVOKE
• Syntax: REVOKE permissions ON object FROM account
Example Query:
REVOKE SELECT ON student FROM username;
revoke the SELECT privilege on the student table from a user (or role)
identified as username.
SQL: Combining the AND and OR Conditions
• The SQL AND condition and OR condition can be combined to test for
multiple conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX : WHERE (condition1 AND condition2) OR condition_n;

Example :
SELECT * FROM emp
WHERE (ename = ‘ABC' AND emp_id <> 9) # <> is symbol for not equal
to
OR (emp_id = 10);
SQL - IN Operator
• The IN operator allows you to specify multiple values in a WHERE clause.

• Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example:
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
SQL: BETWEEN Condition
• The SQL BETWEEN condition allows you to easily test if an expression is
within a range of values (inclusive).
• It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX : expression BETWEEN value1 AND value2;

EXAMPLE : SELECT * FROM emp WHERE emp_id BETWEEN 3 AND 6;

NOT BETWEEN
SELECT * FROM emp WHERE emp_id NOT BETWEEN 3 AND 6;
Additional Operations in SQL:
String Operations / LIKE Operation:
• The most commonly used operation on strings is pattern matching using
the operator like.
• Patterns are case-sensitive; that is uppercase characters do not match
lower-case characters, or vice-versa.
• In sql the patterns are usually described using two special characters.
• The percent sign (%) represents zero, one, or multiple characters
• The underscore sign (_) represents single character

• ‘%idge%’ matches any string containing “idge” as a substring,

for example, ‘Perryridge’, ‘Rock Ridge’, ‘Mianus Bridge’, and ‘Ridgeway’.
• ‘_ _ _’ matches any string of exactly three characters.
LIKE OPERATION / STRING
OPERATION
INTEGRITY
PROCEDURES/STORED
PROCEDURES
• A stored procedure is a set of SQL code that can be
saved, so that the code can be reused again and again
whenever required.
• Stored procedure are called and executed whenever it is
required.
• You can also pass parameters to a stored procedure, so
that the stored procedure can act based on the
parameter value(s) that is passed.
Benefits of stored procedure
• Reusable: As mentioned, multiple users and applications can
easily use and reuse stored procedures by merely calling it.
• Easy to modify: You can quickly change the statements in a
stored procedure as and when you want to, with the help of
the ALTER TABLE command.
• Security: Stored procedures allow you to enhance the
security of an application or a database by restricting the
users from direct access to the table.
• Low network traffic: The server only passes the procedure
name instead of the whole query, reducing network traffic.
• Increases performance: Upon the first use, a plan for the
stored procedure is created and stored in the buffer pool for
quick execution for the next time.
The syntax of SQL stored procedure

CREATE PROCEDURE procedure_name(parameters)

AS
BEGIN;
//statements;
END;
Stored Procedure Example: Get
All Employees
output
EXAMPLE 2
Stored Procedure: Get Employees by
Department
ALTER STORED PROCEDURE
ALTER PROCEDURE EMPPROC
AS
BEGIN
SET NOCOUNT ON;

SELECT EmpID, EmpFN, EmpLN, EmpGender, EmpSalary

FROM Employee
ORDER BY EmpFN;
END;

EXEC EMPPROC;
OUTPUT
DELETE THE STORED
PROCEDURE
• SYNTAX
DROP PROCEDURE PROCEDURE_NAME;

EXAMPLE
DROP PROCEDURE EMPPROC;
FUNCTIONS IN SQL
• A function is a database object in SQL Server. It is a
set of SQL statements that accept only input
parameters, perform actions, and return the
result. Rules for creating SQL Server Functions
• The rules for writing SQL Server functions are as
follows:
• A function must be given a name, which cannot begin
with a special character such as @, $, #, or other
similar characters.
• The only statements that work with functions is SELECT
statements.
• AVG, COUNT, SUM, MIN, DATE, and other functions can
be used anywhere with the SELECT query in SQL.
• When a function is invoked, it gets compiled.
Types of Function
SQL Server divides functions into two types:
[Link] Functions(Built-in functions)
[Link]-Defined Functions
System Defined Function
Scalar function
Scalar functions in SQL are functions that operate on a
single value and return a single value. These functions can be
either built-in (provided by the database system) or user-defined
(created by the user).
Aggregate function
Aggregate functions in SQL perform a calculation on a set of
values and return a single summary value.
Scalar Function

abs(-10.67)- This returns an absolute number of the given

number, which means 10.67.
rand(10)- This will generate a random number of 10 characters.
round(17.56719,3)- This will round off the given number to 3
places of decimal meaning 17.567
upper('dotnet’)- This will return the upper case of the given string
meaning 'DOTNET'
lower('DOTNET’)- This will return the lowercase of the given
string means 'dotnet'
ltrim(' dotnet’)- This will remove the spaces from the left-hand
side of the 'dotnet' string.
convert(int, 15.56)- This will convert the given float value to
integer
Aggregate Functions
CREATE FUNCTION function-name (@parameter1 datatype, @parameter2
datatype, ...)
RETURNS @TableName TABLE
(Column_1 datatype,
.
.
Column_n datatype
)
AS
BEGIN
Statement 1
Statement 2
.
.
Statement n
RETURN
END
EXAMPLE
Let's assume you have a table like:

CREATE TABLE Courses (

CourseID INT,
Name NVARCHAR(100),
CreatedOn DATE,
InstructorID INT
);
CREATE FUNCTION GetCoursesByInstructor (@InstructorID INT)
RETURNS @CourseList TABLE (
CourseID INT,
CourseName NVARCHAR(100),
CreatedOn DATE
)
AS
BEGIN
INSERT INTO @CourseList
SELECT CourseID, Name, CreatedOn
FROM Courses
WHERE InstructorID = @InstructorID;

RETURN;
END;
To Execute
SELECT * FROM GetCoursesByInstructor(101);
Output
TRIGGERS IN SQL
• Trigger is a statement that a system executes automatically
when there is any modification to the database.
• In a trigger, we first specify when the trigger is to be
executed and then the action to be performed when the
trigger executes.
• Triggers are used to specify certain integrity constraints and
referential constraints that cannot be specified using the
constraint mechanism of SQL.
Syntax:
DDL Triggers
• The Data Definition Language (DDL) command events such as
Create_table, Create_view, drop_table, Drop_view, and Alter_table
cause the DDL triggers to be activated.
SQL Server
DML Triggers
DML (Data Manipulation Language) triggers fire automatically when data
changes occur on a table or view. They respond to INSERT, UPDATE, or
DELETE operations.
syntax
EXAMPLE
create trigger tg
on emp
for
insert,update ,delete
as
Begin
print 'you can not insert,update and delete this table ‘
End;
rollback;
• A trigger is called a special procedure because it cannot be called
directly like a stored procedure.
• The key distinction between the trigger and procedure is that a trigger
is called automatically when a data modification event occurs against
a table.
• A stored procedure, on the other hand, must be invoked.
Deleting a trigger:
Syntax:
• Drop trigger <trigger_name>;
deletes the created trigger.
Cursors in SQL
• Cursor is a Temporary Memory.
• It is Allocated by Database Server at the Time of Performing
DML(Data Manipulation Language) operations on the Table by the
User.
• The cursor is a temporary working area created in
the system memory when your SQL statement is
executed.
• Cursors are used to store Database Tables.
There are 2 types of Cursors:
• Implicit Cursors, and
• Explicit Cursors.
Implicit Cursors:
• Implicit Cursors are also known as Default Cursors of SQL SERVER.
• These Cursors are allocated by SQL SERVER when the user
performs DML operations.
• When a DML(Data Manipulation Language) statement (INSERT,
UPDATE, or DELETE) is issued, it is accompanied by an implicit
cursor.
• The cursor holds the data that needs to be inserted for INSERT
operations.
• In addition, the cursor identifies the rows that will be affected by
UPDATE and DELETE actions.
Explicit Cursors:

 Explicit Cursors are Created by Users whenever the user requires

them.
 Explicit Cursors are used for Fetching data from Table in Row-By-
Row Manner.
[Link] Cursor Object
Syntax:
DECLARE cursor_name CURSOR FOR SELECT * FROM table_name
Query:
DECLARE s1 CURSOR FOR SELECT * FROM studDetails
Open Cursor Connection

Syntax:

OPEN cursor_connection

Query:

OPEN s1
Fetch Data from the Cursor
• There is a total of 6 methods to access data from the cursor. They are
as follows:

• FIRST is used to fetch only the first row from the cursor table.
• LAST is used to fetch only the last row from the cursor table.
• NEXT is used to fetch data in a forward direction from the cursor
table.
• PRIOR is used to fetch data in a backward direction from the cursor
table.
• ABSOLUTE n is used to fetch the exact nth row from the cursor table.
• RELATIVE n is used to fetch the data in an incremental way as well as a
decremental way.
Syntax : FETCH FIRST FROM s1;

4. Close cursor connection.

Syntax : CLOSE cursor_name
 CLOSE s1

5. Deallocate cursor memory.

Syntax : DEALLOCATE cursor_name
 DEALLOCATE s1
Embedded SQL

• Embedded SQL is the one which combines the high level

language with the DB language like SQL.
• It allows the application languages to communicate with
DB and get requested result.
• The high level languages which supports embedding
SQLs within it are also known as host language.
• There are different host languages which support
embedding SQL within it like C, C++, ADA, Pascal,
FORTRAN, Java etc.
Structure of Embedded SQL
Connection to DB
This is the first step while writing a query in high
level languages. First connection to the DB that
we are accessing needs to be established.
This can be done using the keyword CONNECT.
But it has to precede with ‘EXEC SQL’- to indicate
that it is a SQL statement.

• EXEC SQL CONNECT db_name;

• EXEC SQL CONNECT HR_USER; //connects to DB
HR_USER
Declaration Section
• Once connection is established with DB, we can perform
DB transactions.
• Since these DB transactions are dependent on the
values and variables of the host language.
• Depending on their values, query will be written and
executed.
• Similarly, results of DB query will be returned to the
host language which will be captured by the variables of
host language.
Execution Section
This is the execution section, and it contains all the SQL
queries and
#include <stdio.h> // Run the SELECT query
int main() { EXEC SQL SELECT CustID, SalesPerson, Status
EXEC SQL BEGIN DECLARE SECTION; FROM Orders
int OrderID, CustID; WHERE OrderID = :OrderID

char SalesPerson[10], Status[6]; INTO :CustID, :SalesPerson, :Status;

// Display the results
EXEC SQL END DECLARE SECTION;
printf("Customer number: %d \n", CustID);
printf("Salesperson: %s \n", SalesPerson);
// Connect to the database (replace
with your DB credentials) printf("Status: %s \n", Status);
// Commit any changes (optional here)
EXEC SQL CONNECT TO mydb USER
'username' IDENTIFIED BY 'password'; EXEC SQL COMMIT WORK;
// Disconnect from the database
printf("Enter order number: "); EXEC SQL DISCONNECT;

scanf("%d", &OrderID); return 0;

}
DYNAMIC SQL
• Dynamic SQL allows you to construct and execute SQL statements at
runtime as strings.
• This is useful when the exact SQL query is not known at compile time,
for example, if you want to build queries based on user input or
variable table/column names.
• Unlike Static SQL, where the SQL statements are fixed and embedded
in the code, Dynamic SQL lets you build SQL statements dynamically.
#include <stdio.h>
int main() {
EXEC SQL BEGIN DECLARE SECTION;
char query[200];//string to hold dynamic query
int OrderID, CustID;
char SalesPerson[10], Status[6];
EXEC SQL END DECLARE SECTION;
// Connect to database
EXEC SQL CONNECT TO mydb USER 'username' IDENTIFIED BY 'password';
printf("Enter Order ID: ");
scanf("%d", &OrderID);
// Create dynamic SQL query string using OrderID
sprintf(query, "SELECT CustID, SalesPerson, Status FROM Orders WHERE OrderID =
%d", OrderID);//sprintf stores values in string buffer(query)
// Prepare the dynamic SQL statement
EXEC SQL PREPARE stmt FROM :query;
//Prepares the dynamically created SQL statement (stored in query) to be executed.
// Execute the prepared statement and fetch results into host variables
EXEC SQL EXECUTE stmt INTO :CustID, :SalesPerson, :Status;
// Print the results
printf("Customer number: %d\n", CustID);
printf("Salesperson: %s\n", SalesPerson);
printf("Status: %s\n", Status);

// Disconnect from database

EXEC SQL DISCONNECT;
return 0;
}