Data Definition Commands

Introduction to DDL
• Data Definition Language (DDL) is used to
define the structure of a database. It includes
commands like CREATE, ALTER, DROP,
TRUNCATE, and RENAME.
CREATE Command
• Syntax:
• CREATE TABLE table_name (
• column1 datatype constraints,
• column2 datatype constraints,
• ...
• );
• Conditions:
• - Table name must be unique.
• - Column names should be valid identifiers.
ALTER Command
• Syntax:
• ALTER TABLE table_name ADD column_name
datatype;
• ALTER TABLE table_name DROP COLUMN
column_name;
• Conditions:
• - Column name should not conflict with existing
ones.
• - Only allowed modifications can be performed.
DROP Command
• Syntax:
• DROP TABLE table_name;

• Conditions:
• - The table must exist.
• - This action permanently deletes the table
and cannot be undone.
TRUNCATE Command
• Syntax:
• TRUNCATE TABLE table_name;

• Conditions:
• - The table remains but all data is removed.
• - Cannot be rolled back in some database
systems.
RENAME Command
• Syntax:
• RENAME TABLE old_name TO new_name;

• Conditions:
• - The old table name must exist.
• - The new name should not conflict with
existing tables.
Conclusion
• DDL commands define database structure and
play a crucial role in database management.
Understanding their syntax and conditions
ensures proper database design.
Data Manipulation Commands
Introduction to Data Manipulation Language (DML)
Data Manipulation Language (DML) is used to manage data within
tables.Common DML commands:
○ SELECT
○ INSERT
○ UPDATE
○ DELETE
DML commands affect the data but do not modify the structure of the
table.
SELECT Command: Retrieves data from a database. Syntax:
SELECT column1, column2 FROM table_name WHERE condition;
Example: Retrieve all employees from the "employees" table:
SELECT * FROM employees;
INSERT Command: Adds new records to a table

Syntax:
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
Example: Add a new employee:
INSERT INTO employees (id, name, salary) VALUES (101, 'John Doe',
50000);

UPDATE Command :Modifies existing records in a table.

UPDATE table_name SET column1 = value1 WHERE condition;
Example: Update salary of an employee:
UPDATE employees SET salary = 60000 WHERE id = 101;

DELETE Command: Removes records from a table.

Syntax:
DELETE FROM table_name WHERE condition;
Example: Remove an employee:
DELETE FROM employees WHERE id = 101;
Data Control Language (DCL) Commands

DCL:DCL is used for database security and permission

management.It consists of two main commands: GRANT and
REVOKE.Ensures controlled access to database objects.

Importance of DCL

Helps in defining access permissions.

Prevents unauthorized data modifications.

Critical for multi-user database environments.

GRANT Command:Used to provide specific privileges to users.

Syntax:
GRANT privileges ON object TO user;
REVOKE Command

● Used to remove previously granted privileges.

● Syntax:
REVOKE privileges ON object FROM user;
● Example:
REVOKE INSERT ON Employees FROM Ali
Common Privileges in SQL
● SELECT: Read data from a table.
● INSERT: Add new records to a table.
● UPDATE: Modify existing records.
● DELETE: Remove records from a table.
● ALL PRIVILEGES: Grants all available privileges.
Granting RolesRoles simplify permission management by
grouping privileges.

Example:
CREATE ROLE manager;

GRANT SELECT, INSERT ON Employees TO manager;

● GRANT manager TO Alice;

Revoking Roles

● Roles can be revoked to remove multiple privileges at once.

● Example: REVOKE manager FROM Alice;
Set Operations in SQL: combine results from multiple queries.

● Common operations include UNION, INTERSECT, and

EXCEPT.These operations follow set theory principles.

UNION Operation:Combines results from two queries and removes

duplicates.

Syntax:SELECT column FROM table1

UNION
SELECT column FROM table2;

Example:

SELECT name FROM Employees

UNION

● SELECT name FROM Customers;

UNION ALL Operation:Similar to UNION but keeps duplicates.

Syntax:
SELECT column FROM table1
UNION ALL

SELECT column FROM table2;

Example:

SELECT name FROM Employees

UNION ALL

SELECT name FROM Customers;

INTERSECT Operation:Returns common records from both queries.

Syntax:
SELECT column FROM table1
INTERSECT
SELECT column FROM table2;

Example:
SELECT name FROM Employees
INTERSECT
SELECT name FROM Customers;

EXCEPT Operation:Returns records from the first query that are not
in the second query.
Syntax:
SELECT column FROM table1
EXCEPT
SELECT column FROM table2;
Example:
Rules and Considerations

● The number of columns must be the same in both queries.

● The data types of corresponding columns must match.
● Sorting can be applied using ORDER BY.
SET OPERATION
Types of Set Operations in DBMS
1. UNION

2. UNION ALL

3. INTERSECT

4. MINUS (EXCEPT in SQL Server)

UNION [A ∪ S]
Combines the results of two
queries.Eliminates duplicate values (by
default).Both queries must have the same
number of columns with the same data types.
Syntax:
SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

Removes duplicates in the result.

Important points on UNION Operation:
1. The UNION operation is commutative, that is :
A∪B=B∪A
2. The UNION is associative, that means it is applicable to
any number of relation.
A∪(B∪C)=(A∪B)∪C

The result of Union operation, which is denoted by A ∪ S, is a

relation that basically includes all the tuples that are present in A
or in S, or in both, eliminating the duplicate tuples.
UNION ALL

Similar to UNION, but does not remove duplicates.Faster

than UNION because no duplicate check is performed.

Syntax
SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;
Keeps duplicates in the result.
INTERSECT
Returns only common records between two
queries.Both queries must have the same number of
columns with the same data types.
Syntax
SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;

Returns names present in both students and teachers.

MINUS (EXCEPT in SQL Server)
Returns records from the first query that do not exist in the
second query.Available in Oracle, PostgreSQL (EXCEPT in
SQL Server, PostgreSQL).Not supported in MySQL
(alternative: use LEFT JOIN with WHERE condition).
Syntax
SELECT column_name FROM table1
MINUS
SELECT column_name FROM table2;
Returns names of students that are not teachers.
NULL VALUES
The result of an airthmentic expression is null
ifany of the input value is null
eg (A+5 ).r A is null for particular tupple then
the expression result must also be null for that
Aggregate Functions in DBMS

Aggregate functions operate on a collection of values and return

a single computed result.

Function Description
count () count no of row
sum () sum the values of row
avg() computes avg
max() returns max value
min() returns min value
Example:

SELECT COUNT(*) FROM students;

SELECT SUM(marks) FROM students;
SELECT AVG(marks) FROM students;
SELECT MAX(marks) FROM students;
SELECT MIN(marks) FROM students;
Aggregation with grouping
● Aggregation allows summarizing or transforming data into a more useful format.
● Grouping is essential when you need to organize data into meaningful sets.
● Tupple with same value on all attributes in the groupby clause are placed in one group

select dept_name,avg (salary)as avg_salary

from instructor
group by dept name ;
dept_name avg_salary
biology 72000
CSE 77000
History 80000
music 56000
Arts 85000
Multiple Grouping Criteria
Group data by multiple columns.
SELECT category, store, SUM(s ales_amount)
FROM sales
GROUP BY category, store;
Explanation:The data is first grouped by category, then by store.
The result gives the sum of sales_amount for each combination
of category and store.
The Having clause
HAVING Clause
○ The HAVING clause filters the results after GROUP BY has
been applied.
○ It is used to filter groups based on an aggregate function.
● Difference Between WHERE and HAVING:
○ WHERE: Filters rows before grouping.
○ HAVING: Filters groups after grouping.
Example: SELECT category, SUM(sales_amount)
FROM sales
GROUP BY category
HAVING SUM(sales_amount) > 1000;
● Order of Execution in a SQL Query:
1. FROM: Data is retrieved from the table(s).
2. WHERE: Filters rows based on conditions.
3. GROUP BY: Groups the rows into subsets.
4. HAVING: Filters groups based on aggregate conditions.
5. SELECT: Selects columns and performs aggregation.
6. ORDER BY: Orders the results (optional).
Handling NULL Values in Aggregation
A NULL represents missing or undefined data.In
databases, it is not equivalent to zero or an empty string.
● Behavior of Aggregation Functions with NULL:
COUNT(): Counts non-NULL values.
Eg :SELECT COUNT(sales_amount) FROM sales;
This counts the number of rows where sales_amount is
not NULL.
If some sales_amount values are NULL, they will be
excluded from the average calculation.
SUM() and AVG(): Ignores NULL values in calculations.
● SUM(): Adds only non-NULL numeric values.
● AVG(): Calculates average only from non-NULL
values.
SELECT AVG(sales_amount) FROM sales;
Boolean Values in Aggregation
Boolean values:Boolean values are either TRUE
or FALSE.
○ SQL does not have a native Boolean type, so
TRUE is usually represented as 1 and FALSE
as 0.
● How Boolean values interact with
aggregation:
○ COUNT(): Counts rows where the Boolean
condition is TRUE (or 1).
○ SUM(): Adds up the TRUE values (1s) and
ignores FALSE values (0s)
SELECT COUNT(is_active), SUM(is_active)
FROM users;

○ COUNT(is_active) will return 4, counting all rows.

○ SUM(is_active) will return 2, since TRUE (1) is counted for 2 active
users.
Views in SQL : SQL allows a virtual relation to be defined by
a query and the relation conceptually contains the result of the
query

The virtual relation is not precomputed and stored but instead is

computed by executing the query whenever the virtual relation
is used

View Definition : We define a view in SQLby create view

command

Syntax
create view SPIT as <query expression>
Create StudentDetails table
CREATE TABLE StudentDetails (
S_ID INT PRIMARY KEY,
NAME VARCHAR(255),
ADDRESS VARCHAR(255)
);
INSERT INTO StudentDetails (S_ID, NAME, ADDRESS)
VALUES
(1, 'Harsh', 'Kolkata'),
(2, 'Ashish', 'Durgapur'),
(3, 'Pratik', 'Delhi'),
(4, 'Dhanraj', 'Bihar'),

-- Create StudentMarks table

CREATE TABLE StudentMarks (
ID INT PRIMARY KEY,
NAME VARCHAR(255),
Marks INT,
Age INT
);
INSERT INTO StudentMarks (ID, NAME, Marks, Age)
VALUES
(1, 'Harsh', 90, 19),
(2, 'Suresh', 50, 20),
(3, 'Pratik', 80, 19),
(4, 'Dhanraj', 95, 21),
Creating View From a Single Table
CREATE VIEW DetailsView AS
SELECT NAME, ADDRESS
FROM StudentDetails
WHERE S_ID < 5;
SELECT * FROM DetailsView;

OUTPUT
Creating View From Multiple Tables
CREATE VIEW MarksView AS
SELECT StudentDetails.NAME, StudentDetails.ADDRESS,
StudentMarks.MARKS
FROM StudentDetails, StudentMarks
WHERE StudentDetails.NAME = StudentMarks.NAME;

SELECT * FROM MarksView;

Listing all Views in a Database

USE "database_name";
SHOW FULL TABLES
WHERE table_type LIKE "%VIEW";

Using information_schema
SELECT table_name
FROM information_schema.views
WHERE table_schema = 'database_name';
OR
SELECT table_schema, table_name, view_definition
FROM information_schema.views
WHERE table_schema = 'database_name';
Operation on Views
Delete View in SQL
DROP VIEW view_name;

Update View in SQL

UPDATE view_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];
Rules to Update Views in SQL:
Certain conditions need to be satisfied to update a view. If
any of these conditions are not met, the view can not be
updated.

1.The SELECT statement which is used to create the view

should not include GROUP BY clause or ORDER BY clause.
2.The SELECT statement should not have the DISTINCT
keyword.
3.The View should have all NOT NULL values.
4.The view should not be created using nested queries or
complex queries.
5.The view should be created from a single table. If the
view is created using multiple tables then we will not be
allowed to update the view.
WITH CHECK OPTION Clause
CREATE VIEW SampleView AS
SELECT S_ID, NAME
FROM StudentDetails
WHERE NAME IS NOT NULL
WITH CHECK OPTION;

INSERT INTO SampleView(S_ID) VALUES(6);

if we now try to insert a new row with a null value in the NAME column
then it will give an error because the view is created with the condition
for the NAME column as NOT NULL.
SQL Trigger

SQL triggers are a critical feature in database management systems

(DBMS) that provide automatic execution of a set of SQL statements
when specific database events, such as INSERT, UPDATE, or DELETE
operations, occur.

Triggers are commonly used to maintain data integrity, track changes,

and enforce business rules automatically, without needing manual
input.

Definition :A trigger is a stored procedure in a database that

automatically invokes whenever a special event in the database occurs

It belongs to a specific class of stored procedures that are automatically

invoked in response to database server events. Every trigger has a
table attached to it.
Key Features of SQL Triggers:
● Automatic Execution: Triggers fire automatically when the defined event occurs (e.g.,
INSERT, UPDATE, DELETE).
● Event-Driven: Triggers are tied to specific events that take place within the database.
● Table Association: A trigger is linked to a specific table or view, and operates
whenever changes are made to the table’s data.

Key Terms

● trigger_name: The name of the trigger to be created.

● BEFORE | AFTER: Specifies whether the trigger is fired before or after the triggering event
(INSERT, UPDATE, DELETE).
● {INSERT | UPDATE | DELETE}: Specifies the operation that will activate the trigger.
● table_name: The name of the table the trigger is associated with.
● FOR EACH ROW: Indicates that the trigger is row-level, meaning it executes once for each
affected row.
● trigger_body: The SQL statements to be executed when the trigger is fired.
Syntax

create trigger [trigger_name]

[before | after]
{insert | update | delete}
on [table_name]
FOR EACH ROW
BEGIN
END;
Types of SQL Triggers
1. 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. They allow us
to track changes in the structure of the database. The trigger will
prevent any table creation, alteration, or deletion in the database.

Example:
CREATE TRIGGER prevent_table_creation
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE
AS
BEGIN
PRINT 'you can not create, drop and alter table in this database';
ROLLBACK;
END;
2. DML Triggers:The Data manipulation Language (DML) command
events that begin with Insert, Update, and Delete set off the DML
triggers. DML triggers are used for data validation, ensuring that
modifications to a table are done under controlled conditions.
CREATE TRIGGER prevent_update
ON students
FOR UPDATE
AS
BEGIN
PRINT 'You can not insert, update and delete this table i';
ROLLBACK;
END;
3. Log on Triggers:response to logon events. Logon triggers are
useful for monitoring user sessions or restricting user access to the
database.As a result, the PRINT statement messages and any errors
generated by the trigger will all be visible in the SQL Server error log.
Authentication errors prevent logon triggers from being used. These
triggers can be used to track login activity or set a limit on the
number of sessions that a given login can have in order to audit and
manage server sessions.

Example CREATE TRIGGER track_logon

ON LOGON
AS
BEGIN
PRINT 'A new user has logged in.';
END;
1.Automatically Updating Related Tables (DML TriggerExample)

CREATE TRIGGER update_student_score

AFTER UPDATE ON student_grades
FOR EACH ROW
BEGIN
UPDATE total_scores
SET score = score + :new.grade
WHERE student_id = :new.student_id;
END;
2. Data Validation (Before Insert Trigger Example)

CREATE TRIGGER validate_grade

BEFORE INSERT ON student_grades
FOR EACH ROW
BEGIN
IF :new.grade < 0 OR :new.grade > 100 THEN
RAISE_APPLICATION_ERROR(-20001, 'Invalid grade value.');
END IF;
END;
Output
This trigger ensures that no grade less than 0 or greater than 100 is
inserted into the student_grades table.
Viewing Triggers in SQL

Syntax
SELECT name, is_instead_of_trigger
FROM sys.triggers
WHERE type = ‘TR’;

Key Terms

● name: The name of the trigger.

● is_instead_of_trigger: Whether the trigger is an INSTEAD OF trigger.
● type = ‘TR’: This filters the results to show only triggers.
BEFORE and AFTER Triggers
Example: Using BEFORE Trigger for Calculations

mysql>>desc Student;
THANK YOU