Database Management system

By Prof. Archana Kotangale

Department of Computer Engineering
A. P. Shah Institute of Technology, Thane

AJK
Module 3 : Structured Query Language(SQL)

AJK
 Module 3 : Structured Query Language(SQL)
Syllabus : (10 Hours)
▪ Overview of SQL, Data Definition Commands,
▪ Integrity constraints: key constraints, Domain Constraints,
Referential integrity, check contraints
▪ Data Manipulation commands,
▪ Data Control commands
▪ Transaction Control Commands.
▪ aggregate function-group by, having, order by,
▪ joins, Nested and complex queries,
▪ Views in SQL, Set and string operations,
▪ Triggers, Introduction to Pl/SQL Block Structure

AJK
 SQL History
▪ SQL stands for Structured Query Language.
▪ It is used to manage data in a Relational Database Management System (RDBMS).
▪ SQL is the most widely used commercial database language.

▪ Origins:
• 1969: Edgar F. Codd introduced the Relational Model at IBM.
• 1970s: SEQUEL (later renamed SQL) was developed by Donald Chamberlin & Raymond
Boyce at IBM.
• 1979: Oracle released the first commercial SQL-based database.

▪ Why SQL is Important

• Foundation of modern DBMS.
• Used in MySQL, PostgreSQL, SQL Server, Oracle.
• Backbone of data-driven applications.
AJK
 SQL History
• SQL Evolution & Key Milestones
• Standardization of SQL
• 1986 – SQL-86 First ANSI standard version.
• 1992 – SQL-92 Added joins, subqueries, improved constraints.
• 1999 – SQL:1999 Introduced recursive queries and triggers.
• Modern Enhancements
• 2003–2016
Support for XML, JSON, window functions, advanced analytics.
• 2019–Present
Big Data integration, Cloud databases, AI-driven analytics.
• Modern SQL Today
• Works with relational + semi-structured data (JSON).
• Integrates with cloud platforms.
• Remains the backbone of enterprise systems.

AJK
 What is SQL?

• What is SQL?
• SQL is based on Relational Algebra and Tuple Relational Calculus.
• SQL is not case-sensitive.
• It uses English-like commands to interact with databases.

• SQL is used to:

• Create database objects (tables, views, indexes).
• Insert, update, delete, and retrieve data.
• Create stored procedures and functions.
• Set user permissions on tables, views, and procedures.

AJK
 Types of SQL Commands

Data Definition
Language ▪ Create, Alter, Drop, Truncate, Rename

Data Manipulation
Language ▪ Select, Delete ,Insert, Update
SQL

Data Control
▪ Grant ,Revoke
Language

Transaction Control
▪ Commit Rollback, Savepoint
Language

Data Query Language ▪ Select

AJK
 SQL Commands

Category Purpose Key Commands

DDL (Data Definition Language) Defines database structure CREATE, ALTER, DROP, TRUNCATE

DML (Data Manipulation Language) Manipulates data in tables INSERT, UPDATE, DELETE, SELECT

DCL (Data Control Language) Manages user permissions GRANT, REVOKE

TCL (Transaction Control Language) Controls transactions COMMIT, ROLLBACK, SAVEPOINT

DQL (Data Query Language) Retrieves data SELECT

AJK
 Data Definition Language (DDL)
• Used to define and modify the structure (schema) of a database
• Creates, alters, and deletes database objects such as:
• Tables
• Schemas
• Indexes
• Views
• Affects the database schema, not the actual data.
• Key Characteristics:
• Changes database structure.
• Most DDL commands are auto-committed.
• Defines attribute domains (data types).
• Enforces integrity constraints (PRIMARY KEY, FOREIGN KEY, etc.).
DDL commands are as follows:
1. Create 2. Drop 3. Alter 4. Rename
AJK 5. Truncate
 Domain Types in SQL
Data type Description
Numeric values int It is used to specify an integer value.
datatype smallint It is used to specify small integer value.
bigint It is used to specify big integer value.
decimal It specifies a numeric value that can have a decimal
number.
numeric It is used to specify a numeric value.

Data type Description

Character string char It has a maximum length of 8000 characters. It
values datatype contains Fixed-length non-unicode characters.
Varchar() It has a maximum length of 8000 characters. It
contains variable-length non-unicode characters. You
must specify
AJK size for VARCHAR.
 Domain
DomainTypes
TypesininSQL
SQL

Data type Description

Date values date It is used to store the year, month, and days value.
datatype time It is used to store the hour, minute, and second
values.
timestamp It stores the year, month, day, hour, minute, and the
second value.

AJK
Sample Schema of University Database

AJK
 SQL DDL Create command (Create Table Construct)

• Create command is used for creating the database object.

• An SQL relation is defined/created using the create table command:
create table R (A1 D1, A2 D2, ..., An Dn,
(integrity-constraint1),..., (integrity-constraint ))
• R is the name of the relation(Table)
• each Ai is an attribute name in the schema of relation R
• Di is the data type of values in the domain of attribute Ai
• Example:
Create database database_name ;

create table instructor (ID int primary key, name varchar(20), dept_name varchar(20), salary numeric(8,2));

AJK
 SQL DDL Create command (Create Table Construct)
Integrity Constraints in Create Table(Referential Integrity)
create table instructor (ID char(5), name varchar(20) not null,
dept_name varchar(20), salary numeric(8,2),
primary key (ID),
foreign key (dept_name) references department(dep_name)
On delete cascade on update cascade);

• primary key declaration on an attribute automatically ensures not null.

AJK
 And a Few More Relation Definitions

❑ create table student (ID varchar(5),name varchar(20) not null,

dept_name varchar(20), tot_cred numeric(3,0),
primary key (ID),
foreign key (dept_name)references department(dept_name) on
delete cascadeon update cascade);

❑ create table takes ( ID varchar(5), course_id varchar(8),

sec_id varchar(8),semester varchar(6),year numeric(4,0),
grade varchar(2),
primary key (ID, course_id, sec_id, semester, year) ,
foreign key (ID) references student(ID) on delete cascade on update
cascade,
foreign key (course_id, sec_id, semester, year) references section on
delete cascade on update cascade);

AJK
 Alter Command
➢The ALTER statement is used to add, modify or delete columns in existing table.
a) Adding column to table :
Syntax- alter table table_name add column_name datatype;
e.g. alter table student add mobile_no int;
b) Dropping the column :
Syntax- alter table table_name drop column _name ;

e.g. alter table student drop last_name

c) Rename column:
Syntax- alter table table_name change column old_name new_name datatype

e.g. alter table student change last_name student_lastName varchar(90);

d) Modify column
Syntax- alter table table_name modify column old_name datatype
alter table student modify AADHAR bigint
AJK ;
 Truncate Command
➢It allows to delete all data in a table. It empties a table completely.
Syntax- Truncate table table_name
truncate table student ;

Rename Command
➢It is used to rename a database object.
Syntax- RENAME TABLE old_table TO new_table;
Rename student to updated_student;

AJK
 Constraints
• Constraints are rules used to maintain the integrity (data correctness) of data.
• They ensure that only valid data is stored in a table.
• If a data operation violates a constraint, the database rejects the operation.
• Constraints are defined at the time of table creation (or can be added later).

• Syntax:
create table table_name (Column_name1 data_type(size) constraint_name, Column_name2
data_type(size) constraint_name,..);
➢ NOT NULL
Most common constraints used in SQL are ➢ UNIQUE
➢ PRIMARY KEY
➢ AUTO_INCREMENT
➢ FOREIGN KEY
➢ CHECK
AJK ➢ DEFAULT
Constraints

AJK
 Constraints
In this example,
Null/not null, unique, check and default constraints are explained with SQL Query

AJK
 Referential Integrity Constraint (Foreign key)
Faculty Table Subject Table
FacultyID Faculty_Name CourseID Course_Name FacultyID
101 Prof A CSC401 DBMS 101
102 Prof B CSC402 OS 102
103 Prof C CSC403 MP 103
104 Prof D CSC504 ML
105 Prof E CSC501 DWM 101

➢A foreign key in one table points to a PRIMARY KEY in another table

➢Foreign key can have the different name than the primary key it comes from
➢The table where the primary key is from is known as Parent table or Referenced table
➢The table which takes the reference i.e. having foreign key is child table or Referencing table
➢Foreign key can be used to make sure that the row in one table have corresponding row in
another table.
➢Primary key cannot be null. Foreign key can be null
Prof Archana Kotangale
➢Primary key always have unique value while AJK foreign
APSIT key may not
 Referential Integrity Constraint (Foreign key)
What is Referential Integrity?
➢ Referential Integrity is a rule that ensures relationships between tables remain
consistent and valid.
• It ensures that:
• A foreign key value in the child table must match a primary key value in the parent table
OR
• It must be NULL (if allowed).

• Why Do We Need Referential Integrity?

• Without referential integrity:
➢We could assign a course to a faculty that does not exist
➢Database would contain invalid or orphan records
➢Data consistency would be broken
Prof Archana Kotangale
AJK APSIT
 Referential Integrity Constraint (Foreign key)
Referential Integrity Actions (ON DELETE / ON UPDATE)
When we maintain referential integrity, the database also controls what happens
when parent data changes.
➢ON DELETE Rules
• When a parent record is deleted:
▪ RESTRICT (Default)
▪ Cannot delete parent if child exists.
▪ Example: Cannot delete Faculty 101 if courses are assigned.
▪ CASCADE
▪ If parent is deleted → child records are automatically deleted.
▪ Delete Faculty 101
→ All courses taught by 101 are also deleted.
▪ SET NULL
▪ If parent is deleted → foreign key becomes NULL.
▪ Faculty 101 deleted
→ Courses remain but FacultyID Prof becomes NULL.
Archana Kotangale
AJK APSIT
 Referential Integrity Constraint (Foreign key)
Referential Integrity Actions (ON DELETE / ON UPDATE)
When we maintain referential integrity, the database also controls what happens
when parent data changes.
➢ON UPDATE Rules
• When a parent record is Updated:
▪ If Primary Key changes:
• CASCADE → Foreign key also updates
• RESTRICT → Prevent update

Prof Archana Kotangale

AJK APSIT
 DML

• DML (Data Manipulation Language) is used to modify and retrieve data in a database.
• It works with table data without altering the database structure.
• Allows adding, modifying, and deleting records.
• Used frequently in applications for CRUD operations (Create, Read, Update, Delete).

• DML commands
• Insert command
• Select command
• Update command
• Delete command

AJK
 DML Commands

• Insert command :-
▪ The INSERT command is used to add new records into a table.
▪ It adds data into specified columns of a table.
▪ The number of values must match the number of columns.
• Employee (Emp_id, name, salary)
➢Inserting into all columns:
Insert into Employee values (101, 'John’, 50000);
➢Inserting into specific columns:
Insert into Employee (Emp_id, name) values (102, ‘Priya’);
➢Inserting multiple rows:
Insert into Employee (Emp_id, name, salary) values ((103, ‘Ved’, 60000), (104, ‘Arya’, 70000))
AJK
 DML Commands

• Insert command :-

AJK
 Basic Query Structure

• A typical SQL query has the form:

select A1, A2, ..., An
from r1, r2, ..., rm
where P

• Ai represents an attribute
• Ri represents a relation
• P is a predicate
• The result of an SQL query is a relation/table

AJK
 unary relation conversion to relational table
Part of Company ER
SSN Name salary Mgr_ssn

Wrong 101 Amit 30000 107

102 Sumit 40000 107

107 Vijay 50000 100

100 Rahul 50000 --

SSN EmpName Salary Manager_SSN

103 Amit 30000 102

102 Sumit 40000 101
101 Ajay 50000 Null
104 Sneha 20000 103

AJK
 The select clause
• The select clause retrieve the information from table.
• corresponds to the projection operation of the relational algebra
• Example: find the names of all students:

select stud_fname
from Student;

• NOTE: SQL names are case insensitive (i.e., you may use upper- or lower-case letters.)
• E.g., Name ≡ NAME ≡ name

AJK
 The select clause
➢ Distinct and all
• SQL allows duplicates in relations as well as in query results
• To force the elimination of duplicates, insert the keyword distinct after select.
• Find the designations are available in faculty table.
select distinct designation
from Faculty ;

• The keyword all specifies that duplicates should not be removed

select all designation
from Faculty;
• An asterisk in the select clause denotes “all attributes”
select *
from Faculty;
AJK
 The select clause

• The select clause can contain arithmetic

expressions involving the operation, +, –, *, and /.
• The query:
select ID, name, salary*12
from Faculty;

Now, if we want output as annual_salary then the

query can be written as,
select FID, Fname, salary*12 as annual_salary
From Faculty;
AJK
 The
The where clause
Where Clause

• The where clause specifies conditions that the result must satisfy.
• Ex. Find all faculties in Comp. Sci. dept
select name
from Faculty
where dept_name = ‘Comp. Sci.'

Comparison results can be combined using the logical connectives and, or, and not.
• To find all faculties in Comp. Sci. dept with salary > 70000

select fname
from Faculty
where dept_name = ‘Comp. Sci.' and salary > 70000
AJK
 The From clause
• The from clause lists the relations(tables) involved in the query.
• From clause is compulsory.
• The from clause is very important because it can result into the cartesian product.
• The FROM clause in SQL is used to specify the table or tables from which to retrieve data
• SELECT column1, column2 FROM table_name;

select name
from Faculty ;

AJK
 DML Commands
➢Update command
• The UPDATE statement in SQL is used to modify existing records in a table.
• Update address of student having roll no 103 as Vasai.

update Student
set address= ‘Vasai’
where rollno=103

• Always use WHERE to avoid updating all records.

• You can update multiple columns at once.
• Omitting WHERE updates all rows in the table.

AJK
 Rename Operation
• The SQL allows renaming relations and attributes using the as clause:
old-name as new-name
• Find the names of all instructors who have a higher salary than some instructor in ‘Comp.
Sci’.
select distinct [Link]
from instructor as T, instructor as S
where [Link] > [Link] and S.dept_name = ‘Comp. Sci.’

Keyword as is optional and may be omitted

instructor as T ≡ instructor T

AJK
 String Operations
• SQL includes a string-matching operator for comparisons on character strings. The
operator like uses patterns that are described using two special characters:
• percent ( % ). The % character matches any substring
• underscore ( _ ). The _ character matches any character
• Find the names of all faculties whose name includes the substring “ar”
select fname
from Faculty
where fname like '%ar%’;

• Find the names of all faculties whose name starts with ‘S’.
select fname
from Faculty
where fname like ‘S%’;
AJK
 String Operations
• Patterns are case sensitive
• Pattern matching examples:
• ‘Intro%’ matches any string beginning with “Intro”
• ‘%Comp%’ matches any string containing “Comp” as a substring
• ‘_ _ _’ matches any string of exactly three characters
• ‘_ _ _ %’ matches any string of at least three characters
select fname
from Faculty
where fname like ‘_a%’;
• SQL supports a variety of string operations such as concatenation (using “||”)
• converting from upper to lower case (and vice versa)
• finding string length, extracting substrings, etc.
AJK
 Ordering the Display of Tuples

• List in alphabetic order the names of all Faculties

select distinct fname
from Faculty
order by fname
Can specify desc for descending order or asc for ascending order, for each
attribute;
• ascending order is the default.
• Example: order by name desc
• Can sort on multiple attributes
• Example: order by name, dept_name,

AJK
 Where Clause Predicates
• SQL includes a between comparison operator
• Example: Find the names of all faculty with salary between Rs.60,000 and Rs.70,000

select name
from faculty
where salary between 60000 and 70000

AJK
 The Null values
• null signifies an unknown value or that a value does not exist
• The result of any arithmetic expression involving null is null
• Example: 5 + null returns null
• The predicate is null can be used to check for null values
• Example: Find all faculty whose salary is null

select fname
from Faculty
where salary is null;

AJK
 In or not in

• IN operator is used to avoid multiple OR conditions.

• It allows test if an expression matches any value in a list of values.
• Ex. IN(value1,value2,value3);
select * from customers where city in (‘Mumbai’, ’Thane’);

Same as
Select * from customer where city=‘Mumbai’ or city=‘Thane’ ;

AJK
 Aggregate Functions

• Aggregate functions perform calculations on multiple rows of a table and return a

single value.
• SUM() – Returns the total sum of a numeric column.
• AVG() – Returns the average value of a numeric column.
• COUNT() – Returns the number of rows.
• MAX() – Returns the highest value in a column.
• MIN() – Returns the lowest value in a column.

• Used with GROUP BY for grouped calculations.

• NULL values are ignored in most aggregate functions.
• Useful for generating reports and summaries.

AJK
 Aggregate Functions

• Find the average salary of instructors in Computer Sci. department

select avg (salary)

from Faculty
where dept_name= ’Comp. Sci.’;

• Find total number of faculty who teach in Computer Sci department

select count (distinct ID)

from Faculty
where dept_name= ‘Comp. Sci’;

AJK