1.

SQL DDL and DML commands

Aim:
To Create a database table, add constraints (primary key, unique, check, Not null), insert
rows, update and delete rows using SQL DDL and DML commands.
1.To Create table bank

Create table bank101(

accountno number(10) primary key,

name varchar2(10) not null,

mobileno number(10) unique,

balance number(6,2) check (balance >0));

2.To describe the table

desc bank101;

7
3.To insert values into the table

insert into bank101 values(&accountno,’&name’,&mobileno,&balance);

4. To display the records

select * from bank101;

8
5. To show the error message on abandon the constraints

9
10
 2. REFERENTIAL INTEGRITY CONSTRAINTS

Aim:
To Create set of tables, add foreign key constraints and incorporate referential integrity.

A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY
KEY in another table.

The table containing the foreign key is called the child table, and the table containing the
candidate key is called the referenced or parent table.

"Persons" table:

PersonID LastName FirstName Age

1 Hansen Ola 30

2 Svendson Tove 23

3 Pettersen Kari 20

"Orders" table:

OrderID OrderNumber PersonID

1 77895 3

11
2 44678 3

3 22456 2

4 24562 1

Notice that the "PersonID" column in the "Orders" table points to the
"PersonID" column in the "Persons" table.

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 is used to prevent actions that would destroy
links between tables.

The FOREIGN KEY constraint also prevents invalid data from being inserted
into the foreign key column, because it has to be one of the values contained
in the table it points to.

SQL Foreign Key on Create Table

The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders"
table is created:

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (

OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY
constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (

OrderID int NOT NULL,

12
 OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);

SQL FOREIGN KEY on ALTER TABLE

To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is
already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders

ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY
constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders

ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Orders

DROP FOREIGN KEY FK_PersonOrder;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders

DROP CONSTRAINT FK_PersonOrder;

13
 3. GROUP BY AND ORDER BY CLAUSE AND
AGGREGATE FUNCTION
Aim:
To Query the database tables using different ‘where’ clause conditions and also implement
aggregate functions.

1. To create table customer

create table customers(customerid number(10),

custname varchar2(15),

country varchar2(10));

2. To insert records into customer table

insert into customers values(&custid,'&custname','&country');

3. To display records of payroll table

select * from customers;

14
4. To display the count of customers group by country and order by count of customers

select count(customerid),country from customers group by country order by

count(customerid);

select count(customerid),country from customers group by country order by

count(customerid) desc;

15
7. Aggregate functions

Select max (student12thmark) from studentinfo101;

Select min (student12thmark) from studentinfo101;

select avg(student12thmark) from studentinfo101;

select count(student12thmark) from studentinfo101;

16
17
 .

4. SUBQUERIES AND SIMPLE JOIN OPERATIONS

Aim:

To Query the database tables and explore sub queries and simple join operations

1. To create table product

create table product101(productid number(10) primary key,

productname varchar2(15));

2. To insert records into customer table

insert into product101 values(&productid,'&productname');

3. To display the records

Select * from product101;

18
4. To create table order

create table order101(orderid number(10) primary key,

productid number(10),

quantity number(3),

unitprice number(6,2));

5. To insert records into customer table

insert into order101 values (&orderid,&productid,&quantity,&unitprice);

6. To display the records

Select * from order101;

7. To display the records from product table whose product id matches with product id
in order table with quantity greater than 200

19
Select productname from product101 where productid in (select productid from order101
where quantity>200);

JOIN QUERY

1.To Create table student

Create table student101 (

Sname varchar2 (20),

Rollno number (10) primary key,

Class varchar2 (10));

2.To describe the table

Desc student101;

3. To insert the recordsinto student table

Insert into student101 values(‘&sname’,&rollno,’&class’);

20
4. To display the records from library table

Select * from student101;

5. To Create table library

Create table library101 (

Rollno number(10),

Bookid number(10) primary key,

Bookname varchar2(20));

21
6. To insert the records into library table

Insert into library101 values(&rollno,&bookid,’&bookname’);

7. To display the records from library table

Select * from library101;

8. To display the records from student and library table using equi-join

Select s.rollno,s.sname,lib.bookname from student101 s,library101 lib where

s.rollno=l.rollno;

22
23
 5. Joins – Natural, Equi and Outer Joins
Aim:
To Query the database tables and explore natural, equi and outer joins.

INNER Join or EQUI Join

This is a simple JOIN in which the result is based on matched data as per the equality
condition specified in the SQL query.
Inner Join Syntax is,

SELECT column-name-list FROM

table-name1 INNER JOIN table-name2

WHERE table-name1.column-name = table-name2.column-name;

Example of INNER JOIN

Consider a class table,

ID NAME

1 abhi

2 adam

3 alex

4 anu

and the class_info table,

ID Address

24
1 DELHI

2 MUMBAI

3 CHENNAI

Inner JOIN query will be,

SELECT * from class INNER JOIN class_info where class.id = class_info.id;

The resultset table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 2 MUMBAI

3 alex 3 CHENNAI

Natural JOIN
Natural Join is a type of Inner join which is based on column having same name and same
datatype present in both the tables to be joined.
The syntax for Natural Join is,

SELECT * FROM

table-name1 NATURAL JOIN table-name2;

Example of Natural JOIN

Here is the class table,

25
ID NAME

1 abhi

2 adam

3 alex

4 anu

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

Natural join query will be,

SELECT * from class NATURAL JOIN class_info;

The resultset table will look like,

ID NAME Address

1 abhi DELHI

26
2 adam MUMBAI

3 alex CHENNAI

In the above example, both the tables being joined have ID column(same name and same
datatype), hence the records for which value of ID matches in both the tables will be the
result of Natural Join of these two tables.

OUTER JOIN
Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,

1. Left Outer Join

2. Right Outer Join
3. Full Outer Join

LEFT Outer Join

The left outer join returns a resultset table with the matched data from the two tables and
then the remaining rows of the left table and null from the right table's columns.
Syntax for Left Outer Join is,

SELECT column-name-list FROM

table-name1 LEFT OUTER JOIN table-name2

ON table-name1.column-name = table-name2.column-name;

To specify a condition, we use the ON keyword with Outer Join.

Left outer Join Syntax for Oracle is,

SELECT column-name-list FROM

table-name1, table-name2 on table-name1.column-name = table-name2.column-name(+);

Example of Left Outer Join

Here is the class table,

27
ID NAME

1 abhi

2 adam

3 alex

4 anu

5 ashish

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

7 NOIDA

8 PANIPAT

Left Outer Join query will be,

28
SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id = class_info.id);

The resultset table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 2 MUMBAI

3 alex 3 CHENNAI

4 anu null null

5 ashish null null

RIGHT Outer Join

The right outer join returns a resultset table with the matched data from the two tables being
joined, then the remaining rows of the right table and null for the remaining left table's
columns.
Syntax for Right Outer Join is,

SELECT column-name-list FROM

table-name1 RIGHT OUTER JOIN table-name2

ON table-name1.column-name = table-name2.column-name;

Right outer Join Syntax for Oracle is,

SELECT column-name-list FROM

table-name1, table-name2

ON table-name1.column-name(+) = table-name2.column-name;

29
Example of Right Outer Join
Once again the class table,

ID NAME

1 abhi

2 adam

3 alex

4 anu

5 ashish

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

7 NOIDA

30
 8 PANIPAT

Right Outer Join query will be,

SELECT * FROM class RIGHT OUTER JOIN class_info ON (class.id = class_info.id);

The resultant table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 2 MUMBAI

3 alex 3 CHENNAI

null null 7 NOIDA

null null 8 PANIPAT

Full Outer Join

The full outer join returns a resultset table with the matched data of two table then
remaining rows of both left table and then the right table.
Syntax of Full Outer Join is,

SELECT column-name-list FROM

table-name1 FULL OUTER JOIN table-name2

ON table-name1.column-name = table-name2.column-name;

Example of Full outer join is,

The class table,

31
ID NAME

1 abhi

2 adam

3 alex

4 anu

5 ashish

and the class_info table,

ID Address

1 DELHI

2 MUMBAI

3 CHENNAI

7 NOIDA

8 PANIPAT

Full Outer Join query will be like,

SELECT * FROM class FULL OUTER JOIN class_info ON (class.id = class_info.id);

32
The resultset table will look like,

ID NAME ID Address

1 abhi 1 DELHI

2 adam 2 MUMBAI

3 alex 3 CHENNAI

4 anu null null

5 ashish null null

null null 7 NOIDA

null null 8 PANIPAT

33
 6. FUNCTIONS AND STORED PROCEDURES

Aim:
To Write user defined functions and stored procedures in SQL.

CREATE OR REPLACE FUNCTION get_annsal (p_id number) return number

as
v_salary number(10);
BEGIN
select sal*12 into v_salary from emp
where empno = p_id;
return v_salary;
END;
18
CREATE OR REPLACE FUNCTION tax (p_sal number) return number
as
v_tax number(8,2) :=0;
BEGIN
if p_sal between 0 and 2000 then
v_tax := p_sal * 0.10;
elsif p_sal between 2001 and 4000 then
v_tax := p_sal *0.15;
else
v_tax := p_sal * 0.25;
end if;
return v_tax;
END;
CREATE OR REPLACE FUNCTION emp_exp (p_eno number) return number
as
hdate date;

34
e number;
BEGIN
select hiredate into hdate
from emp
where empno = p_eno;
e := months_between(sysdate, hdate) / 12;
return round(e);
end;
Procedures

SQL> grant create procedure to user_name; (DBA)

SQL> Grant Execute on <procedure_name> to user_name (owner)
SQL> show error;
Show err;
/* PROCEDURE TO ADD A Record in Dept Table */
create or replace procedure add_dept
(p_dno in number default 10,
p_name in varchar2 default 'IT',
p_loc in varchar2 default 'HYD')
as
begin
insert into dept values(p_dno, p_name, p_loc);
end;
16
/* PROC To Fetch Data From Emp Table */
create or replace procedure get_emp
(p_eno in number,
p_name out varchar2,
p_job out varchar2,
p_sal out number

35
)
is
begin
select ename, job, sal into
p_name,p_job, p_sal from emp
where empno = p_eno;
end;

36
 7. DCL (Data Control Language) and TCL (Transaction
control language) Commands
Aim:
To Execute complex transactions and realize DCL and TCL commands.

Grant:

This command is use to give user access privileges to a database.

Syntax:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

SQL> GRANT SELECT ON Users TO'Tom'@'localhost;

Revoke:

It is useful to back permissions from the user.

Syntax:

REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}

SQL>REVOKE SELECT, UPDATE ON student FROM BCA, MCA;

TCL

Transaction control language or TCL commands deal with the transaction within the
database.

Commit

This command is used to save all the transactions to the database.

Syntax:

Commit;

SQL> DELETE FROM Students

WHERE RollNo =25;
COMMIT;

Rollback

37
Rollback command allows you to undo transactions that have not already been saved to the
database.

Syntax:

ROLLBACK;

SQL> DELETE FROM Students

WHERE RollNo =25;

SAVEPOINT

This command helps you to sets a savepoint within a transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

SQL> SAVEPOINT RollNo;

38
 8. DATABASE TRIGGERS
AIM:
To Write SQL Triggers for insert, delete, and update operations in database table.

TRIGGERS USING PL/SQL

1. To create employee table

create table emp_101(empid number(10),

ename varchar2(20),

sal number(7,2));

2. To insert records into employee table

insert into emp_101 values(&empid,'&ename',&sal);

PL/SQL CODING

CREATE OR REPLACE TRIGGER display_salary_changes

BEFORE DELETE OR INSERT OR UPDATE ON emp_101

FOR EACH ROW

WHEN (NEW.EMPID > 0)

DECLARE

sal_diff number;

BEGIN

sal_diff := :NEW.sal - :OLD.sal;

dbms_output.put_line('Old salary: ' || :OLD.sal);

dbms_output.put_line('New salary: ' || :NEW.sal);

dbms_output.put_line('Salary difference: ' || sal_diff);

END;

39
OUTPUT

40
41
 9. VIEW AND INDEX
AIM:
To Create View and index for database tables with large number of records.

Syntax:
Create View <View_Name> As Select statement;
SQL>Create View EmpView As Select * from Employee;
Output :View created
Syntax: Select columnname,columnname from <View_Name>;

SQL>Select Empno,Ename,Salary from EmpView where Deptno in(10,30);

UPDATABLE VIEWS:
Syntax for creating an Updatable View:
Create View Emp_vw As
Select Empno,Ename,Deptno from Employee; View
created.
SQL>Insert into Emp_vw values(1126,’Brijesh’,20);
1 row created
SQL>Update Emp_vw set Deptno=30 where Empno=1125;
1 row updated.
SQL>Delete from Emp_vw where Empno=1122;
1 row deleted

SQL>Update EmpDept_Vw set salary=4300 where Empno=1125;

1 row updated.

42
SQL>Delete From EmpDept_Vw where Empno=1123;
1 row deleted

DESTROYING A VIEW:
Syntax: Drop View <View_Name>;

SQL>Drop View Emp_Vw;

View Dropped

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name

ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, ...);
Note: The syntax for creating indexes varies among different databases. Therefore: Check the
syntax for creating indexes in your database.

CREATE INDEX Example

The SQL statement below creates an index named "idx_lastname" on the "LastName"
column in the "Persons" table:

CREATE INDEX idx_lastname

ON Persons (LastName);

43
If you want to create an index on a combination of columns, you can list the column names
within the parentheses, separated by commas:

CREATE INDEX idx_pname

ON Persons (LastName, FirstName);
DROP INDEX Statement
The DROP INDEX statement is used to delete an index in a table.

DROP INDEX index_name;

Index Dropped

44
 10. USER DEFINED EXCEPTIONS USING PL/SQL

AIM:
To execute user defined exceptions using PL/SQL

PL/SQL CODING

DECLARE

Sample_exception EXCEPTION;

PROCEDURE nested_block

IS

BEGIN

Dbms_output.put_line('Inside nested block');

Dbms_output.put_line('Raising sample_exception from nested block');

RAISE sample_exception;

EXCEPTION

WHEN sample_exception THEN

Dbms_output.put_line ('Exception captured in nested block. Raising to main block');

END;

BEGIN

Dbms_output.put_line('Inside main block');

Dbms_output.put_line('Calling nested block');

Nested_block;

EXCEPTION

WHEN sample_exception THEN

45
Dbms_output.put_line ('Exception captured in main block');

END;

OUTPUT

46
 REFERENCES

BOOKS

1. Bipin C. Desai. (2001). An Introduction to Database Systems. Galgotia

PublicationsPvt. Limited.
2. C.J. Date, A.Kannan, S.Swamynathan.(2006). An Introduction to Database
Systems. 8th Edition. Pearson Education.
3. C. J. Date. (2003). An Introduction to Database Systems.7th Edition, Pearson
Education.
4. G.K.Gupta.(2011). Database Management Systems. Tata McGraw Hill
Education.
5. Jeffrey D. Ullman, Jenifer Wisdom (2001). A First Course in Database
Systems. 3rdEdition, Pearson Education Asia.

6. Koch and Liney. (2002). Oracle9iThe Complete reference.5th Edition. Tata

McGraw–Hill.
7. Raghu Ramakrishnan. (2010). Database Management Systems. 4 th Edition. Tata
McGraw Hill
8. Ramez Elmasri, Sham Navathe (2016). Fundamentals of Database Systems.6th
Edition. Addison-Wesley
9. Ramez Elmasri, Sham Navathe (2016). Fundamentals of Database Systems.6th
Edition. Addison-Wesley
10. A.Silberchatz, H. Korth, Subarshan. (2012). Database System Concepts. 5th
Edition. McGraw – Hill Higher Education.

Web Links

1. https://www.appdynamics.com/topics/database-management-systems
2. https://www.javatpoint.com/dbms-tutorial
3. https://www.tutorialspoint.com/Relational-Database-Management-System-
RDMS
4. https://www.codecademy.com/article
5. https://www.ibm.com/analytics/relational-database
6. https://www.oracle.com/database/what-is-a-relational-database

47