Database Systems

DSE 2221

3 Credits

Reference:
Database System Concepts , 6th Edition
Authors:
Abraham Silberschatz
Henry F. Korth
S. Sudarshan

1
 Main Concepts Discussed

• Database concepts, data models and database architecture.

• Manipulate, retrieve the data from database using SQL, PL/SQL.
• Database Design – ER Model & Normalization.
• Database Query execution, Transaction Management , Concurrency & Recovery.
• Unstructured Database.

2
 Database System Applications

1/14/2025 ADBMS 3
 Database Management System (DBMS)
➢Database A collection of related data (and a description of this
data), designed to meet the information needs of an organization.
➢DBMS is a collection of interrelated data and set of programs to
access those data.
➢DBMS is a general purpose software system that facilitates the
process of defining, constructing, manipulating and sharing
databases among users and applications.

1/14/2025 ADBMS 4
1/14/2025 ADBMS 5
 Unit 4
Structured Query Language
Overview of SQL Query Language
• IBM developed the original version of SQL, originally SEQUEL in 1970s
• The sequel language has evolved since then and the name changed as SQL and
has established itself as the standard relational database language
• In 1986, the ANSI and ISO published an SQL standard called SQL-86
• Recently SQL:2008
• SQL (Structured Query Language) is a special – purpose programming language
designed for managing data held in a relational database management
system(RDBMS)
• Each SQL command should be terminated by ;

1/14/2025 7
Different Types of SQL Commands

✓DDL (Data Definition Language) commands: -

To create and modify database objects - CREATE, ALTER, DROP
✓DML(Data Manipulation Language) commands: -
To manipulate data of a database objects- INSERT, DELETE, UPDATE
✓DQL(Data Query Language) command: -
To retrieve the data from a database - SELECT
✓DCL(Data Control Language) commands: -
To control the data of a database – GRANT, REVOKE
✓TCL(Transaction Control Langage) commands:-
To control and manage transactions – COMMIT, ROLLBACK

SQL 11
Oracle- SQL Data Types…
1. Character
• Char – fixed length character string that can varies between 1-2000 bytes
• Varchar / Varchar2 – variable length character string, size ranges from 1-4000
bytes.
• Long - variable length character string, maximum size is 2 GB
Example: Name Char(10)

2. Number : Can store +ve,-ve,zero,fixed point,floating point with 38

precision.
• Number – {p=38,s=0}
• Number(p) - fixed point
• Number(p,s) –floating point (p=1 to 38,s= -84 to 127)
Example: Marks Number(3) fixed point
Salary Number(9,2) Floating point

SQL 12
SQL 13
 SQL Data Types
3. Date : used to store date and time in the table. DB uses its own format of storing in fixed
length of 7 bytes for century, date, month, year, hour, minutes, seconds. The default data type is
“dd-mon-yy” Example: Birth_date Date

4. Interval Year To Month : Stores a period of time using the YEAR and MONTH date
time fields Example: year_of_experience INTERVAL YEAR TO MONTH
CREATE TABLE Emp ( empno NUMBER, ename VARCHAR2(50), job VARCHAR2(255) , year_of_experience INTERVAL
YEAR TO MONTH );

INSERT INTO EMP VALUES (1,'Rajesh','[Link]', INTERVAL '10-5' YEAR TO MONTH);

SQL 15
 Create Table Construct
An SQL relation is defined using the create table command:
create table r (A1 D1, A2 D2, ..., An Dn); both are equivalent syntax

CREATE TABLE table-name ( column_name Datatype(size),

column_name Datatype (size), . . . );

r is the name of the relation/table
each Ai is an attribute (column) name in the schema of relation r
Di is the data type of values in the domain of attribute Ai
Example:
create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2));
insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000);
insert into instructor values (‘10211’, null, ’Biology’, 66000);
SQL 17
 INTEGRITY CONSTRAINTS
Valid data means –the data which follows certain rules/ regulations of real world system.

Therefore designer has to ensure that data entered by user has to be checked against
these rules and allowed to store if valid otherwise need to be rejected.

Integrity constraints guard against accidental damage to the database, by ensuring

that authorized changes to the database do not result in a loss of data consistency.
Example:
Data in some Column such as Phone_Number is mandatory for user to enter.
Data in some Column such as Registration_Number has to be Unique ( No duplicated allowed).
Data in some Column such as Registration_Number is used to identify every student distinctly.
Valid range of Data for some Column such as Under_Gradate is BSc, [Link]. BE.
A SB account must have a balance greater than 1000/-

SQL 18
 TYPE of CONSTRAINTS
Rule/Constraints can be imposed on single column or combination of columns.

Column-level Constraints- Imposed on Single Column. Defined along with

Column

Table Level Constraint.- Defined at the end after defining all the columns.

 Multi-level Column.

– Primary key imposed on combination of columns- (Name,[Link],Surname)

 Constraint imposed on a column that reference another column in the constraint.

– Assume that are two columns in the table say- Date_of_Birth and Date_of_Join.

– We want to impose condition(constraint) on Date_of_Join that

Date_of_Join > Date_of_Birth.

SQL 19
 Integrity Constraints in Create Table
SQL supports a number of different integrity constraints.

not null -
primary key (A1, ..., An )
foreign key (Am, ..., An ) references r
Unique
Check
Default

SQL 20
 NOT NULL
NULL is special kind of value applicable to any domain(datatype).
Note: NULL is not equivalent to '' or ' '
In some cases, value to some column is mandatory to enter.
In other words we want to force the user to enter some value to the column.
Example: Assume that for the table Instructor we want to make user to enter some values for
name

create table instructor (

ID char(5),
name varchar(20) NOT NULL , dept_name varchar(20),
salary numeric(8,2) );
SQL 21
 PRIMARY KEY…
Identifies every tuple(record/row) in the table uniquely.
primary key (Aj1, Aj2, . . . , Ajm )
Where Aj1, Aj2, . . . , Ajm are the set of attributes in the table used to form a primary key.
Aj1, Aj2, . . . , Ajm are said to be components of primary key.
Primary key may be imposed on a single attribute or multiple attributes of the table.
There can be ONLY ONE PRIMARY key for a table.
Properties:
NO component of primary key can be NULL.
Values to the columns must be Unique( Duplicate values can’t be entered to a column)

Example: Declare ID as the primary key for instructor

COLUMN LEVEL DECLARATION TABLE LEVEL DESCRIPTION

create table instructor ( create table instructor (
ID char(5) PRIMARY KEY, ID char(5),
name varchar(20) not null,
name varchar(20) not null,
dept_name varchar(20),
dept_name varchar(20), salary numeric(8,2),
salary numeric(8,2)); SQL PRIMARY KEY(ID)); 22
 …PRIMARY KEY-Table Level
Example: Create a table Enrollment containing fields –SID –student ID , CNo-Course Number and
Year – Joining Year to the Course.
Condition to be imposed that – We want to identify a student Uniquely who enrolled to a Course on a
Particular year. Therefore combination of SID,CNO and YEAR has to be Unique and can’t be Null.
Therefore we need to impose Primary Key on SID,CNO and YEAR .
Since Constraint is on multiple column, it has to be defined as Table level Constraints.
CREATE TABLE Enrollment
(SID char(9) NOT NULL,
CNO varchar2(7) NOT NULL,
Year number(2) NOT NULL,
Grade char(2),
PRIMARY KEY (SID, CNO, Year)); Note: primary key defined after defining all the columns

SQL 23
Note: NO component of primary key can be NULL
 FOREIGN KEY…
foreign key (Ak1 , Ak2, . . . , Akn ) references s:
The foreign key in a relation r specification says that the values of attributes (Ak1 , Ak2, . . . , Akn )
for any tuple in the relation r must correspond to values of the primary key attributes of some tuple
in relation s. [Link]
[Link]

Enrollment can be done to only to those who are student, therefore SID column in Enrollment can have only
values which are present in SID in Student table.
This condition is imposed by defining SID in Enrollment as Foreign key referencing Students

This is known as Referential

SQL Integrity Constraint 24
 …Referential Integrity Constraint
Ensures that a value that appears in one relation for a given set of attributes also
appears for a certain set of attributes in another relation.
Example: If “S101” is a Student Id appearing in one of the tuples in the Enrollment relation,
then there exists a tuple in the Students relation for “S101”.
Let A be a set of attributes. Let R and S be two relations that contain attributes A and
where A is the primary key of S. A is said to be a foreign key of R if for any values
of A appearing in R these values also appear in S.

Child
Parent

Note: In relation R, attribute A can’t contain a value which is not existing in attribute A of relation S.
In the example above , at this instance A in R can’t have a value a6 or a7 etc.
SQL 25
 ..FOREIGN KEY
Properties:
A Foreign key can contain-
 Only values present in the corresponding Parent Column/s.
 NULL values accepted, if Foreign key is not defined with additional NOT NULL
constraints.
Foreign key column can reference to any column (parent column) whose data
type, width is same and Parent column has to be defined with Primary key or
Unique constraint.
A Parent Column has to exist before creation of Child Column with Foreign
key Constraint.

Restrictions: Any UPDATE/INSERT/DELETE of Records , ALTER or DROP

Operation that Violates any of the above properties is restricted and hence
Rejected by the Database System.
SQL 26
 ..FOREIGN KEY column-level
Example:
We have to create both Parent Tables First.
CREATE TABLE Students (SID char (9) PRIMARY KEY , Name varchar2(25)
not null, Age integer);

CREATE TABLE Courses (CID varchar2 (9) UNIQUE , C_Name varchar2(25)

not null, Credits number(2), Duration Number(2));
After Creating Parent Table/s, create Child tables
CREATE TABLE Enrollment

( SID char (9) NOT NULL References Students,

CNo varchar2 (9) References Courses(CID), Why- Courses(CID)?
Year number (2) not null,
Grade char (2), Primary key (SID, CNO, Year) );

SQL 27
 ..FOREIGN KEY table-level
Example:

Parent(Master) Table:
CREATE TABLE Items( Item_name varchar2(10), Comp_name varchar2(10),
Price Number(3),
PRIMARY KEY ( Item_name,Comp_name ) );

Child(Detail) Table
CREATE TABLE Transactions( It_name varchar2(10), Comp_name varchar2(10),
Tr_Date date, Qty Number(3),
FOREIGN KEY(It_name, Comp_name) REFERENCES Items);

SQL 28
Does the following table get created with Foreign key constraint?

• Create table DEPT ( Dno Varchar2(3) ,Dname varchar2(10));

• Create table EMP( Empno Number(3) Primary key, Name

varchar2(10), Deptno varchar2(3) References Dept);

ERROR: referenced table does not have a primary key

SQL 29
Does the following table get created with Foreign key constraint?

• Create table DEPT ( Dno Varchar2(3) UNIQUE ,Dname varchar2(10));

• Create table EMP( Empno Number(3) Primary key, Name

varchar2(10), Deptno varchar2(3) References Dept);

No: referenced table has a unique key so it has to be refered during foreign key definition

SQL 30
 Write the SQL commands to create following tables with
mentioned constraints
*Assume that one student stays in one particular room of one particular Hostel only.

Student Hostel
Column DataType Constraint Column DataType Constraint
RegNo Number Primary key Hostel_NoVarchar Primary Key
Name Varchar Room_No Number Primary Key
Phone Number Unique RegNum Foreign Key

Create table Student( RegNo Number(3) PRIMARY KEY, Name Varchar2(10),Phone

Number(10) UNIQUE);
Create table Hostel (Hostel_no Varchar2(5), Room_no Number(3), Reg_no
Number(3) REFERENCES Student, PRIMARY KEY( Hostel_no, Room_no));
SQL 31
Restrictions on INSERT / UPDATE / DELETE Operations
Over Foreign Key

Any INSERT / UPDATE / DELETE of Records , ALTER or

DROP Operation that Violates any of the Foreign key
properties is restricted and hence the operation is
Rejected by the Database System.

SQL 32
MAINTAINING REFERENTIAL INTEGRITY
Any delete made to the department table that would
On Delete Restrict delete or change a primary key value will be rejected
unless no foreign key references that value in the
employee table. This is the default constraint in
Oracle.

On Delete Cascade Any delete made to the department table should be

cascaded through to the employee table.

On Delete Set Null Any values that are updated/deleted in the

department table cause affected columns in the
employee table to be set to null.

33
 ..FOREIGN KEY- ON DELETE CASCADE/ON DELETE SET NULL
A foreign key with cascade delete means that if a record in the parent table is deleted, then the
corresponding records in the child table will automatically be deleted. This is called a cascade delete
in Oracle.
Example: Create tables give in slide 29 with ON DELETE CASCADE clause along with FOREIGN
KEY.
Parent(Master) Table:

CREATE TABLE Department ( Dno varchar(2) PRIMARY KEY, Name varchar(10),Budget

Number(9) );

Child(Detail) Table

CREATE TABLE Emp ( Empno number(3) PRIMARY KEY, Name varchar(10), Deptno
varchar(2) REFERENCES Department ON DELETE CASCADE ) ;

Any Delete operation on the table Department(Parent) first deletes dependent records
in the EMP(child) table automatically. Thus Delete operation restriction on Foreign
SQL 34
key constraint is get resolved automatically.
 ..FOREIGN KEY- ON DELETE CASCADE/ON DELETE SET NULL
A foreign key with “ON DELETE SET NULL " means that if a record in the parent table is deleted, then
the corresponding records in the child table will have the foreign key fields set to null. The records in
the child table will not be deleted.
Example: Create tables give in slide 18 with ON DELETE SET NULL clause along with FOREIGN KEY.
Parent(Master) Table:

CREATE TABLE Department(Dno varchar(2) PRIMARY KEY, Name varchar(10), Budget

Number(9));

Child(Detail) Table

CREATE TABLE Emp( Empno number(3) PRIMARY KEY, Name varchar(10), Deptno
varchar(2) REFERENCES Department ON DELETE SET NULL );

SQL 35
..FOREIGN KEY- ON DELETE CASCADE/ON DELETE SET NULL

• When a record is deleted from Department(Parent) table it will not delete

dependent records in the EMP(child) table instead puts NULL values to
corresponding foreign key column/s.

• Thus removes dependency of corresponding records in the child table on table

records being deleted in the Parent table.

• Thus Delete operation restriction on Foreign key is get resolved automatically.

SQL 36
 ..INSERT

Syntax-
INSERT INTO table_name(column1,column2,..) VALUES (value1,value2,….)

Example: Insert a record into Course table having values to Course_id, Dept_Name columns
only. Course(Course_id,title,Dept_Name,Credits)

insert into course values (’CS-438’, NULL, ’Comp. Sci.’, NULL);

Note: NULL is not same as ‘NULL’

SQL 37
 UPDATE

To modify any column/s value in a already existing record.

Syntax:
UPDATE table_name SET column1=value1,column2=value2,…
WHERE condition involving any of column/s in the table ;

Example: Consider the table Instructor(Id, Name, Dept_name, Salary).

Increase the salary of instructor with ID I201 by 10%.

UPDATE Instructor SET Salary=Salary+Salary*0.1 WHERE Id=‘I201’;

SQL 38
 DELETE

Syntax:
DELETE FROM table_name WHERE condition;
Example:
• Delete all instructors
delete from instructor

• Delete all instructors from the Finance department

delete from instructor
where dept_name= ’Finance’;

SQL 39
 ..FOREIGN KEY – INSERT Restrictions

INSERT INTO EMP VALUES(105,’Rajesh’,’D4’);

Is rejected, to execute above INSERT command, execute in following Order

INSERT INTO DEPT VALUES(‘D4’,’Physics’,125678);

Note-Parent record is added to DEPARTMENT and now we can add Employee with D4 department

INSERT INTO EMP VALUES(105,’Rajesh’,’D4’); Now it is Accepted.

SQL 40
 ..FOREIGN KEY- UPDATE/DELETE Restrictions

Similarly,
UPDATE EMP SET DEPTNO=‘D5’ WHERE EMPNO=100;
is Rejected.
UPDATE EMP SET DEPTNO=‘D3’ WHERE EMPNO=100;
is Accepted.

DELETE FROM DEPARTMENT WHERE DNO= 'D1‘;

is Rejected

To execute above DELETE command, execute in following Order

1st Delete from Child Table(EMP) and then 2nd Delete from Parent(DEPARTMENT)
This Deletion process can be automated by using Clause ON DELETE CASCADE / ON DELETE
SET NULL while creating Child Table
SQL 41
Similarly Altering Structure of DNO or Dropping DNO is Rejected.
 Note: [Link] ;
Exercise (Hostel_No,Room_No)- [Link]
RegNum- [Link]
Hostel
Student
RegNo Name Phone RegNum Hostel_no Room_No What is the result of
111Ravi 122334 123H-16 376 execution of following SQL
123Raj 324555 111H-18 799
112Rakesh 563255
statements?

INSERT INTO Student VALUES(115,’Ajay’,567899); INSERTED

INSERT INTO Student VALUES(112,’Sridhar’,89979); NOT-INSERTED

INSERT INTO Hostel VALUES(112,’H-16’,376); NOT INSERTED

INSERT INTO Hostel VALUES(113,’H-17’,234); NOT-INSERTED

INSERT INTO Hostel VALUES(115,’H-18’,376); INSERTED

SQL 42
 Exercise Note: [Link] ;
(Hostel_No,Room_No)- [Link]
RegNum- [Link]
Hostel
Student
RegNo Name Phone RegNum Hostel_no Room_No What is the result of
111 Ravi 122334 123 H-16 376
execution of following SQL
123 Raj 324555 111 H-18 799
112 Rakesh 563255 115 H-18 376 statements?
115 Ajay 567899

UPDATE Student SET Regno=113 WHERE Regno=112; UPDATED

UPDATE Student SET Regno=222 WHERE Regno=123; NOT-UPDATED

UPDATE Hostel SET RegNum=113 WHERE RegNum=123; UPDATED

UPDATE Hostel SET RegNum=null WHERE RegNum=111; UPDATED

UPDATE Hostel SET RegNum=118 WHERE RegNum=111; NOT-UPDATED

SQL 43
 Note: [Link] ;
Exercise (Hostel_No,Room_No)- [Link]
RegNum- [Link]
Student Hostel
RegNo Name Phone RegNum Hostel_no Room_No What is the result of
111Ravi 122334 123H-16 376 execution of following SQL
123Raj 324555 111H-18 799 statements?
112Rakesh 563255 115H-18 376
115Ajay 567899

DELETE FROM Student WHERE Regno=112; DELETED

DELETE FROM Student WHERE Regno=123; NOT-DELETED

DELETE FROM Hostel WHERE Regnum=123; DELETED

DELETE FROM Student WHERE Regno=123; DELETED

SQL 44
..FOREIGN KEY - Recursive Relationship

Example:
CREATE TABLE EMP ( Empno number(3) PRIMARY KEY, Ename
Varchar2(10), MGRNO number(3) );

Note: Referential Integrity constraint on MGRNO can be defined using

Alter Table command after creating EMP table
OR
CREATE TABLE EMP( Empno number(3) PRIMARY KEY, Ename
Varchar2(10), MGRNO number(3) REFERENCES EMP );

SQL 45
Exercise

Create a table Student (Regno, Name, Class_Representative)

Where RegNo is Primary key and Class_Representative is RegNo of
students who are Class Representatives. Assume proper data type and
size.

CREATE TABLE Student(Regno Number(3) PRIMARY KEY, Name Varchar2(10),

Class_Representative Number(3) References Student);

SQL 46
Inserting Data into Student table having Recursive relationship

CREATE TABLE Student(Regno Number(3) PRIMARY KEY, Name

Varchar2(10), Class_Representative Number(3) References Student);

• INSERT INTO Student VALUES(123,'AAAA’,122);
Error
ORA-02291: integrity constraint (MCA2020.SYS_C007551) violated - parent key not

• INSERT INTO Student VALUES (123,'AAAA',NULL);

• INSERT INTO Student VALUES(122,'AAAA',NULL);
• UPDATE Student SET Class_Representative=122 WHERE Regno= 123;
OR
INSERT INTO Student VALUES(122,'AAAA',NULL); followed by INSERT INTO Student VALUES (123,'AAAA’,122);
SQL 47
 UNIQUE…
unique ( A1, A2, …, Am)
The unique specification states that the attributes A1, A2, … Am
form a candidate key.
Candidate keys are permitted to be null (in contrast to primary keys).
Example:
CREATE TABLE Student(
ID varchar(5) PRIMARY KEY,
Name Varchar(10),
Phone number(10) UNIQUE,
tot_credit Number(2) );

Phone is implemented with Column level UNIQUE Constraints.

SQL 48
Exercise UNIQUE…

Answer the validity of following statements with respect UNIQUE constraint on ID column-

INSERT INTO Student VALUES(123,’Vinay’,7799889788,54) ; YES/NO

INSERT INTO Student VALUES(123,’Vinay’,7799889788,54); YES/NO

INSERT INTO Student VALUES (NULL,’Vinay’,7799999788,54); YES/NO

INSERT INTO Student VALUES (124,’Raj’,NULL,54); YES/NO

SQL 49
 ..UNIQUE
In the following table combination of Area_code & Phone_Num is Unique
for a landline phone.

Area_code & Phone_Num is to be implemented as Table-level Constraint,

Example:
CREATE TABLE BsnL_Customer (
Customer_ID number(7) PRIMARY KEY,
Name varchar(10) NOT NULL,
Address varchar(20),
Area_Code Number(4),
Phone_Num Number(6),
UNIQUE(Area_Code , Phone_Num ) );

SQL 50
Exercise

Create a table Customer(Cust_id, Name, Phone, Email, Policy_No) Cust_id,

Phone, Email & Policy_No contains unique values and assume Cust_id as
Primary key. Also make Phone number mandatory.
Assume proper data type and size

CREATE TABLE Customer(Cust_id Varchar2(5) PRIMARY KEY ,

Name Varchar2(10), Phone Varchar2(10) UNIQUE NOT NULL,
Email Varchar2(20) UNIQUE , Policy_No Varchar2(10) UNIQUE );

SQL 51
 The CHECK clause – Using IN
check (P)
where P is a predicate(condition)

Example: Ensure that Type of Courses offered by a department is any one of MCA, MTech,
BTech, MS.

CREATE TABLE Department (

Department_name varchar2 (8) PRIMARY KEY,
Course_Type varchar2 (8) CHECK( Course_Type IN( 'MCA',' MTech ',' BTech', 'MS')),
Numb_of_Semester Number(1),
In_take_stud_num Number(2),
Department_Phone Number(10) NOT NULL UNIQUE );
Note: IN works like a Belongs to a set Operator
User_enetred_value ϵ { 'MCA',' MTech',' BTech', 'MS' } , evaluates to TRUE or FALSE

SQL 52
 ..The CHECK clause –Using BETWEEN
Create table Instructor and ensure that Salary column accepts only values in the range 50000 to
200000 ( both upper and lower bound values are valid).

CREATE TABLE instructor (

ID char(5),
name varchar2(20),
dept_name varchar2(20),
salary number(8,2) CHECK( Salary>=50000 AND Salary<=200000)
);

CREATE TABLE instructor (

ID char(5),
name varchar2(20),
dept_name varchar2(20),
salary number(8,2) CHECK( Salary BETWEEN 50000 AND 200000) );
53
SQL
 ..The check clause - using LIKE (Pattern Matching)
Example:

Create a table CANDIDATES(CandtID, Name, Branch) appearing for entrance exam at MIT.
Candidate numbers must be Unique & every candidate number must start with MIT.

CREATE TABLE CANDIDATES( CandtId varchar2(7) PRIMARY KEY CHECK (CandtId LIKE
'MIT%'), Name varchar2(10),Branch varchar2(10));

INSERT INTO CANDIDATE VALUES('MIT1020', 'Raghu', 'CompSc’); Accepted

INSERT INTO CANDIDATE VALUES('MIIT1021', 'Ram', 'CompSc’); Rejected

Wild characters-
% any number of characters
_ (underscore) Single character
SQL 54
 ..The check clause - using function UPPER()
Example:

Create a table CANDIDATES(CandtID, Name, Branch) appearing for entrance exam at MIT.
Candidate numbers must be Unique & every candidate number must start with MIT. User must enter
Branch in Capital letters only.

CREATE TABLE CANDIDATE( CandtId varchar2(7) PRIMARY KEY CHECK (CandtId LIKE 'MIT%'),
Name varchar(10),Branch varchar(10) CHECK(Branch=UPPER(Branch)));

Is this Insert command executed successful?

INSERT INTO CANDIDATE VALUES('MIT1021', 'Raghu', '[Link]');

If user enters Branch as –’[Link]’ , it is rejected with constraint error message.

* We will see other inbuilt functions later

SQL 55
 Exercise

Create a table Student(Regno,Name,Mark1,Mark2), Regno is primary key and Mark1 must

accept values in the range 0 to 100 , Mark2 must accept values in the range 0 to 150.

CREATE TABLE Student(Regno Number(9) PRIMARY KEY , Name Varchar2(10), Mark1

Number(3) CHECK (Mark1>=0 AND Mark1<=100),Mark2 Number(3) CHECK( Mark2
BETWEEN 0 AND 150 ));

What is the result of following insert command ?

INSERT INTO Student VALUES (100, ‘Raghu’,99, 159)
Error - ORA-02290: check constraint (SYSTEM.SYS_C007566) violated
SQL 56
 Exercise

Create a table Student( Regno, Name, Grade), Regno is primary

key and Grade must accept only values- A+,A,B,C,D,E,F

CREATE TABLE Student(Regno Number(9) PRIMARY KEY , Name

Varchar2(10), Grade Char(2) CHECK ( Grade IN ('A+','A','B','C','D','E','F'))) ;

What is the result of following insert command ?

INSERT INTO Student VALUES(100, 'Raghu', 'B+')
Error - ORA-02290: check constraint (SYSTEM.SYS_C007563) violated
SQL 57
 Exercise

Create a table Course_Structure( Subject_id, Sub_Name, Credits,

Sem), Subject_id is primary key and Subject Id must start with
MCA. Credit values must be 1 ,2,3,4. E.g. MCA4151, MCA5151 etc.

CREATE TABLE Course_Structure (Subject_id Varchar2(10) PRIMARY KEY

CHECK(Subject_id LIKE 'MCA%'), Sub_name Varchar2(20), Credits Number(1)
CHECK ( Credits IN (1,2,3,4))) ;
What is the result of following insert command ?
INSERT INTO Course_structure VALUES('MCa100', 'DBMS', 4)
Error - ORA-02290: check constraint (SYSTEM.SYS_C007557) violated
SQL 58
 DEFAULT
The DEFAULT constraint is used to provide a default value for a column.
Example:
CREATE TABLE Persons (
ID Number(3) NOT NULL,
LastName varchar(10) NOT NULL, FirstName varchar(10),
Age Number(2), City varchar(15) DEFAULT 'Manipal' );

Example:

INSERT INTO Persons(ID,Lastname) VALUES(100,’Smith');

Inserts value to ID=100 , Lastname=Smith , FirstName= NULL , Age=NULL & City takes value
Manipal automatically assigned though City value is not specified in the INSERT command.

SQL 59
 Naming the Constraints
• If user do not specifies Constraint Name while defining Constraints, System itself gives a name.
System uses auto generate method to give unique constraints names such as – SYS_C0003461
etc. As constraint names have to be unique. In case of constraint violation, it is easy to user to track
the constraint if user defined constraint name is given.

• Use CONSTRAINT name_of_constraint along with constraint definition in

CREATE or ALTER table.
• Create following tables with constraint names.
Department
Attribute Constraint Constr_Name
PRIMARY KEY Dname_PK Organization
Dname
Refers Organization fk_Orga Attribute Constraint Constr_Name
Course_Type Check Chk_Type
Dept_name Primary Key dp_PK
Numb_of_Sem
In_take_stud Head
Not Null NoNul
Dep_Phone
Unique Unq_Ph
SQL 60
 Example
• CREATE TABLE Organization(Dept_name varchar2(8) CONSTRAINT dp_PK
PRIMARY KEY, Head varchar2(10));

• CREATE TABLE Department ( Dname varchar2(8) CONSTRAINT Dname_PK

PRIMARY KEY CONSTRAINT fk_Orga REFERENCES Organization, Course_Type
varchar2(8) CONSTRAINT Chk_Type CHECK( Course_Type IN( 'MCA','MTech ',
'BTech', 'MS')), Numb_of_Sem Number(1), In_take_stud Number(2),
Dep_Phone Number(10) CONSTRAINT noNul NOT NULL CONSTRAINT
Unq_Ph UNIQUE );

SQL 61
 Exercise

Create a table Course_Structure( Subject_id, Sub_Name, Credits, Sem),

Subject_id is primary key and Subject Id must start with MCA. Credit values
must be 1 ,2,3,4. E.g. MCA4151, MCA5151 etc. Assign proper constraint names.

CREATE TABLE Course_Structure (Subject_id Varchar2(10) CONSTRAINT SubID_PK PRIMARY

KEY CONSTRAINT Starts_MCA CHECK(Subject_id LIKE 'MCA%’), Sub_name Varchar2(20),
Credits Number(1) CONSTRAINT Credt_Range CHECK ( Credits IN (1,2,3,4))) ;

What is the result of following insert command ?

INSERT INTO Course_structure VALUES('MCa100', 'DBMS', 4);
Error - ORA-02290: check constraint (SYSTEM.STARTS_MCA) violated
SQL 62
 Exercise
Create following tables( DEPT & EMP) with given constraint names.

DEPT Attribute Data Type Size Constraints Constraint Name

DNO VARCHAR2 2 PRIMARY KEY
DNAME VARCHAR2 10
HEAD_OFFC_CITY VARCHAR2 10 UDP,BNG,HYD,MUB,LA Valid_offc_city

Attribute Data Type Size Constraints Constraint Name

EMP EMPNO NUMBER 3 PRIMARY KEY PK_Empno
ENAME VARCHAR2 10
MGRNO NUMBER 3 References EMP(EMPNO) FK_MgrNo_EMP
DEPTNO VARCHAR2 2 References DEPT(DNO) FK_Deptno_DEPT
DOB DATE
DOJ DATE DOJ>DOB DOJ_Grtr_DOB
SAL NUMBER 7,2 SAL>30000 SAL_Grtr_30K

SQL 63
 CREATE TABLE … AS SELECT…
The CREATE TABLE … AS SELECT… statement is used to create a new table having
same/partial structure of an existing table given with SELECT statement.

EMP_SPOUSE
Attribute DataType Size
EMPNO NUMBER 3 We can create EMP_SPOUSE table by copying
ENAME VARCHAR2 10 structure for EMPNO and ENAME from EMP
SPOUSE_NAME VARCHAR2 10 table.

EMP
Attribute Data Type Size Constraints Constraint Name
EMPNO NUMBER 3 PRIMARY KEY PK_Empno
ENAME VARCHAR2 10
MGRNO NUMBER 3 References EMP(EMPNO) FK_MgrNo_EMP
DEPTNO VARCHAR2 2 References DEPT(DNO) FK_Deptno_DEPT
DOB DATE
DOJ DATE DOJ>DOB DOJ_Grtr_DOB
SAL NUMBER 7,2 SAL>30000 SAL_Grtr_30K
SQL 64
 CREATE TABLE … AS SELECT…
CREATE TABLE EMP_SPOUSE(ENO,NAME) AS SELECT EMPNO,ENAME FROM EMP;

ALTER TABLE EMP_SPOUSE ADD(Spouse_name Varchar2(10));

EMP_SPOUSE
EXAMPLE: Create a table EMP_SPOUSE using
Attribute DataType Size already existing EMP table
ENO NUMBER 3
NAME VARCHAR2 10
SPOUSE_NAME VARCHAR2 10
*More about ALTER TABLE we will see in coming slides
EMP
Attribute Data Type Size Constraints Constraint Name
EMPNO NUMBER 3 PRIMARY KEY PK_Empno
ENAME VARCHAR2 10
MGRNO NUMBER 3 References EMP(EMPNO) FK_MgrNo_EMP
DEPTNO VARCHAR2 2 References DEPT(DNO) FK_Deptno_DEPT
DOB DATE
DOJ DATE DOJ>DOB DOJ_Grtr_DOB
SAL NUMBER 7,2 SAL>30000 SAL_Grtr_30K
SQL 65
 Drop Table Constructs

The DROP TABLE statement allows you to remove or delete a table from the database.

Syntax:

DROP TABLE tablename;

Example: DROP TABLE Emp;

SQL 66
 Alter Table Constructs…
The ALTER TABLE statement is used to add, modify, or drop/delete columns/constraints in
a table.

The SQL ALTER TABLE statement is also used to rename a table.

Adding Column
Syntax:

ALTER TABLE table_name ADD (column_name1 column-

definition, column_name1 column-definition,…..) ;

Example: Consider Emp(Eno,Ename)

Add column Salary and Phone to Emp table

ALTER TABLE Emp ADD (Salary Number(7), Phone Number(10));

SQL 67
 ..Alter Table Constructs

Modifying Column

Syntax:
ALTER TABLE table_name
MODIFY (column_1 column_type, column_2 column_type, ... column_n
column_type);

Example: Increase the size of Salary column & modify Name column definition by adding NOT NULL rule

ALTER TABLE Emp MODIFY ( EName VARCHAR(25) NOT NULL, Salary

Number(9,2) );

SQL 68
 ..Alter Table Constructs

DROP a Column
Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;

Example: Drop a column EName from Emp table.

ALTER TABLE Emp DROP COLUMN EName;

SQL 69
 ..Alter Table Constructs

Change name of column

Syntax:
ALTER TABLE table_name
rename COLUMN old_column_name to newcolumn_name;

Example: Drop a column EName from Emp table.

ALTER TABLE Emp rename COLUMN Ename to Employee_name;

SQL 70
 ..Alter Table Constructs

RENAME a Table
Syntax:
ALTER TABLE table_name RENAME TO New_table_name;
Example:

ALTER TABLE Emp RENAME TO Employee;

SQL 71
 ..Alter Table Constructs
Adding CHECK Constraint to a column
Syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK( p ) );
Where p - predicate

Example: Add constraint to Students table to check mark2 column takes values
only in the range 0 to 100.

ALTER TABLE Students

ADD CONSTRAINT check_mark_range
CHECK (mark2>=0 AND mark2<=100);

SQL 72
 ..Alter Table Constructs
Adding UNIQUE Constraint to a column
Syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE( column1,column2,..columnn ) );

Example: Add constraint to Students table make Phone column as Unique.

ALTER TABLE Student

ADD CONSTRAINT uniq_phone
UNIQUE(Phone);

SQL 73
 ..Alter Table Constructs
Adding PRIMARY KEY Constraint to a column
Syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
PRIMARY KEY (column1, column2, ... column_n) ;

Example: Assume that Person(Fname, Lname, Address) table is already created. Add
constraint to Person table to make (FName,LName) column as Primary Key.
ALTER TABLE Person ADD CONSTRAINT F_L_Name_FK
PRIMARY KEY (FName,LName);

SQL 74
 ..Alter Table Constructs
Adding FOREIGN KEY Constraint to a column
Syntax:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n);

Example: Assume that Person(Fname, Lname, Address) table is already created with (Fname,LName) as
Primary Key. Also a table Customer(Cust_Id, Cust_FName,Cust_Lname,Credits) is also created already.
Now we want to make (Cust_FName,Cust_Lname) as foreign key referencing Person

ALTER TABLE Customer ADD CONSTRAINT Cust_FLName_FK

FOREIGN KEY(Cust_Fname , Cust_Lname) REFERENCES Person;

SQL 75
 ..Alter Table Constructs
Removing Constraints
Syntax:
ALTER TABLE table_name
DROP CONSTRAINT constraint_name ;

Example: Assume that Person(Fname, Lname, Address) table is already created with
(Fname,LName) as Primary Key Also a table Customer(Cust_Id,
Cust_FName,Cust_Lname,Credits) is also created already. Now we want to remove foreign
key constraint from (Cust_FName,Cust_Lname).

ALTER TABLE Customer DROP CONSTRAINT Cust_FLName_FK;

SQL 76
 INSERT

Inserts a new record at the end of given table.

Syntax-
INSERT INTO table_name VALUES (value1,value2,….)

Example: Insert a record to a table Course(Course_id,title,Dept_Name, Credits)

insert into course values (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);
There will be 1 to 1 mapping between values given and order in which columns are
created in relation Course.
1st value ‘CS-437’ is mapped to column Course_id,
2nd value ‘Database Systems’ is mapped to column title and so on.

SQL 77
 ..INSERT
Syntax-
INSERT INTO table_name(column1,column2,..) VALUES (value1,value2,….)

Example: Course(Course_id,title,Dept_Name,Credits)

➢Insert a record into Course table by changing the order of the columns:

insert into course (title,course_id,credits,dept_name) values

(’DBMS’,’MCA101’, 4,’DSCA’);

➢Insert a record into Course table having values to Course_id, Dept_Name columns only.

insert into course (course_id,dept_name) values (’CS-438’, ’Comp. Sci.’);

It is equivalent to –
insert into course values (’CS-438’, NULL, ’Comp. Sci.’, NULL);
SQL Note: NULL is not same as78‘NULL’
 INSERT MULTIPLE RECORDS
• SQL> INSERT INTO Course values (‘&Course_id’,’&title’,’&Dept_Name’,&Credits);
User is prompted to enter the appropriate values to the corresponding column
• SQL>/

• SQL> INSERT ALL

INTO Course VALUES ( ‘MCA102’, ‘OS’, ’DSCA’, 3)
INTO Course VALUES ( ‘MCA103’, ‘SE’, ’DSCA’, 4)
SELECT * FROM dual;

DBMS LAB 79
 Insert into… Select .. From…
• Some time instead of giving data for every tuple in the INSERT INTO command,
we can insert tuples on the basis of the result of a query.
• Using SELECT statement as sub query in the INSERT INTO, we can select
(copy) some set of records from a relation(source) and insert into another
relation(Destination).
• Note that we need to take care of datatype and size compatibility.
STUD Rollno Name Course Dept MARKS Rno Course Marks Attendance
101 Ajit Algorithms CS

102 Ravi IoT IT

103 Anish Algorithms MCA

101 Ram ML MCA

Example: Insert Rollno and course information of students enrolled to MCA department into
MARKS relation.

INSERT INTO MARKS(RNo, Course) SELECT Rollno, Course FROM STUD WHERE Dept=‘MCA’;
SQL 80
 ..INSERT

Syntax-
INSERT INTO table1(column1,column2,..) SELECT column1,column2,.. FROM table2;

Example: Consider the tables Student(Id, Name, D_name, tot_cred) and Instructor(Id,
Name, Dept_name, Salary). Add all instructors to the student relation with tot_creds set to 0
insert into student
select Id, Name, Dept_name, 0
from instructor;
OR
insert into student(ID, name, D_name)
select ID, name, dept_name
from instructor;
The select from where statement is evaluated fully before any of its results are inserted into the
relation
SQL 81
 ..INSERT (date value)
Example: Assume a table Stud (Rno, Name, Birth_Date)
▪ Insert a record into STUD table.

INSERT INTO Stud VALUES(19011102, ‘Ajay’, TO_DATE(‘21-09-2001’,’DD-MM-

YYYY’));

▪ TO_DATE () is a oracle inbuilt function, which converts given date value (in the form
character value) into date type.

▪ Date has a default format set. Example: Default format is say : DD-MON-YY , then
you can enter data as below without TO_DATE()

INSERT INTO Stud VALUES(19011103, ‘Aman’, ‘21-OCT-2001’);

SQL 82
 UPDATE

To modify any column/s value in a already existing record.

Syntax:
UPDATE table_name SET column1=value1,column2=value2,…
WHERE condition involving any of column/s in the table ;

Example: Consider the table Instructor(Id, Name, Dept_name, Salary).

Increase the salary of instructor with ID I201 by 10%.

UPDATE Instructor SET Salary=Salary+Salary*0.1 WHERE Id=‘I201’;

SQL 83
 ..UPDATE
• Example: Consider the table Instructor(Id, Name, Dept_name, Salary).
Increase salaries of instructors whose salary is over $100,000 by 3%, and all
others receive a 5% raise
• Write two update statements:
UPDATE instructor
set salary = salary * 1.03
where salary > 100000;

UPDATE instructor
set salary = salary * 1.05
where salary <= 100000;
• The order is important
SQL 84
 ..UPDATE –using CASE
• Same query(previous slide) as before but with case statement
update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end;

Assume the table Emp(Empno,ename,deptnosal)

update emp set sal=case
when sal<=3000 then sal*1.1
when sal<=5000 then sal*1.05
else sal*1
end; SQL 85
 DELETE

Syntax:
DELETE FROM table_name WHERE condition;
Example:
• Delete all instructors
delete from instructor

• Delete all instructors from the Finance department

delete from instructor
where dept_name= ’Finance’;

SQL 86
 ..DELETE
Syntax:
DELETE FROM table_name WHERE condition;
Note- Condition is involving some sub-query
Example:
• Delete all tuples in the instructor relation for those instructors associated
with a department located in the ‘Watson’ building.
delete from instructor
where dept_name in (select dept_name
from department
where building = ’Watson’);

SQL 87
 ..DELETE
Example:
Delete all instructors whose salary is less than the average salary of instructors

delete from instructor

where salary< (select avg (salary) from instructor);

Problem: as we delete tuples from deposit, the average salary changes

Solution used in SQL:
1. First, compute avg salary and find all tuples to delete
2. Next, delete all tuples found above (without recomputing avg or
retesting the tuples)

SQL 88
 Tutorial 1 25-01-2025
Write the SQL-DDL commands to do the following:
1) Create the following tables
SALESMAN (Salesman_id, Name, City, Commission)
CUSTOMER (Customer_id, Cust_Name, City, Grade, Salesman_id)
ORDERS (Ord_No, Purchase_Amt, Ord_Date, Customer_id, Salesman_id)
▪ Underlined attributes are primary keys and other column with same names are foreign
keys.
▪ Assume appropriate datatype and size. Give your own proper constraint names to the
constraints
▪ Also impose following constraints-
▪ Commission – minimum 1000 and maximum 20000
▪ Grade – Silver or Gold or Diamond or Platinum
▪ Minimum Purchase_amount 500/-
2) Create a new column- Points number type into table Customer and put constraint
minimum points 10;
END

SQL 90