SRINIVAS UNIVERSITY COLLEGE OF ENGINEERING

AND TECHNOLOGY

IV SEMESTER
DATABASE MANAGEMENT SYSTEMS
LABORATORY(19SCFL46)

LAB MANUAL

Prepared by:
Dyanila Ferrao (B.E, M.Tech)
Assistant Professor
 INSTITUTE VISION AND
MISSION
To be a trendsetter among universities and build students who emerge as
leaders with competence, conscience and compassion by empowering them with
sound education and high standards of ethical and professional behavior enabling
them to build and promote a more humane, just and sustainable world for future
generations.

Our mission is to provide an exceptional learning environment where students can

develop and enhance their leadership and teamwork skills, creative and intellectual
powers and passion for learning by providing an uncompromising standard of
excellence in teaching: embodying the spirit of excellence to educate the citizen-
leaders of society with distinction.

DEPARTMENT VISION AND MISSION

Vision
To emerge as a center of excellence with global reputation with adaption of
rapid advancements in the field of computer specialization.
Mission
1. To provide a strong theoretical and practical background in area of computer
science with an emphasize on software development.

2. To inculcate Professional behavior, strong ethical values, leadership qualities,

research capabilities and lifelong learning.

3. To educate students to become effective problem solvers, apply knowledge with

social sensitivity for the betterment of the society and humanity as a whole.

PROGRAM EDUCATIONAL OBJECTIVES (PE0s)

Programme educational objectives are broad statements that describe the career and
professional accomplishments that the programme is preparing graduates to achieve
within 3 to 3 years after graduation. The Programme Educational Objectives of the B.
Tech CSE programme are:
  PEO1: To apply the knowledge of mathematics, basic science and engineering solving the real
world computing problems to succeed higher education and professional careers.
 PEO2: To develop the skills required to comprehend, analyze, design and create innovative
computing products and solutions for real life problems.
 PEO3: To inculcate professional and ethical attitude, communication and teamwork skills, multi-
disciplinary approach and an ability to relate computer engineering issues with social awareness.

COURSE OBJECTIVES:
This course will enable students to
• Foundation knowledge into database concepts, technology and practice to groom stud into well-informed
database application developers.
• Strong practice in SQL programming through a variety of database problems.
• Develop database applications using front-end tools and back-end DBMS.

COURSE OUTCOMES:
The students should be able to:
• Create, Update and query on the database.
• Demonstrate the working of different concepts of DBMS
• Implement, analyze and evaluate the project developed for an application.

GUIDELINES TO STUDENTS
1. Equipment in the lab for the use of student community. Students need to maintain proper decorum in the
computer lab. Students must use the equipment with care Any damage is caused is punishable.
2. Students are instructed to come to lab in formal dresses only.
3. Students are supposed to occupy the systems allotted to them and are not supposed to talk or make noise in
the lab.
4. Students are required to carry their observation book and lab records with completed exercises while
entering the lab.
5. Lab records need to be submitted every week. 6. Students are not supposed to use pen drives in the lab.

Do's

1. Come with completed observation and record.

2. Wear ID card before entering into the lab.
3. Read and understand how to carry out an activity thoroughly before coming to laboratory.
4. Report any broken plugs or exposed electrical wires to your lecturer/laboratory technician immediately.

5. Write in time, out time and system details in the login register.
 Don'ts

1. Do not eat or drink in the laboratory.

2. Do not operate mobile phones in the lab.

3. Do not change system settings.

4. Do not disturb your neighboring students. They may be busy in completing tasks.
5. Do not remove anything from the computer laboratory without permission.

6. Do not use pen drives.

 DATABASE MANAGEMENT SYSTEM LABORATORY

Subject Code: 19SCSL46 IA Marks:50

Number of Lecture Hours/Week:01I+02P Exam Marks:50
Total Number of Lecture Hours :40 Exam Hours:03

COURSE LEARNING OBJECTIVES

 Foundation knowledge in database concepts, technology and practice to groom students into well-informed
database application developers.
 Strong practice in SQL programming through a variety of database problems.
 Develop database applications using front-end tools and back-end DBMS.

Lab Experiments:

A. Consider the following schema for a LibraryDatabase:

BOOK (Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS (Book_id, Author_Name)
PUBLISHER (Name, Address, Phone)
BOOK_COPIES (Book_id, Branch_id, No-of_Copies)
BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date) LIBRARY_BRANCH
(Branch_id, Branch_Name, Address)

Write SQL queries to

1. Retrieve details of all books in the library – id, title, name of publisher, authors, number of
copies in each branch,etc.
2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan
2017 to Jun2017
3. Delete a book in BOOK table. Update the contents of other tables to reflect this data
manipulationoperation.
4. Partition the BOOK table based on year of publication. Demonstrate its working with a
simplequery.
5. Create a view of all books and its number of copies that are currently available in the
Library.

B. Consider the following schema for OrderDatabase:

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) Write SQL queries to
1. Count the customers with grades above Bangalore’saverage.
2. Find the name and numbers of all salesmen who had more than onecustomer.
3. List all salesmen and indicate those who have and don’t have customers in
their cities (Use UNIONoperation.)
4. Create a view that finds the salesman who has the customer with the highest
order of a day.
5. Demonstrate the DELETE operation by removing salesman with id 1000. All his
orders must also bedeleted.

C. Consider the schema for MovieDatabase:

ACTOR (Act_id, Act_Name, Act_Gender)
DIRECTOR (Dir_id, Dir_Name, Dir_Phone)
MOVIES (Mov_id, Mov_Title, Mov_Year, Mov_Lang, Dir_id) MOVIE_CAST (Act_id, Mov_id,
Role)
RATING (Mov_id, Rev_Stars)
Write SQL queries to
1. List the titles of all movies directed by‘Hitchcock’.
2. Find the movie names where one or more actors acted in two or moremovies.
3. List all actors who acted in a movie before 2000 and also in a
movieafter 2015 (use JOINoperation).
4. Find the title of movies and number of stars for each movie that has at least
one rating and find the highest number of stars that movie received. Sort the
result by movie title.
5. Update rating of all movies directed by ‘Steven Spielberg’ to5.

D. Consider the schema for CollegeDatabase:

STUDENT (USN, SName, Address, Phone, Gender)
SEMSEC (SSID, Sem, Sec)
CLASS (USN, SSID)
SUBJECT (Subcode, Title, Sem, Credits)
IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)
Write SQL queries to
1. List all the student details studying in fourth semester ‘C’section.
2. Compute the total number of male and female students in each semester and
in each section.
3. Create a view of Test1 marks of student USN ‘1BI15CS101’ in allsubjects.
 4. Calculate the FinalIA (average of best two test marks) and
update the corresponding table for allstudents.
5. Categorize students based on the following criterion: If FinalIA = 17 to 20 then CAT
=‘Outstanding’If FinalIA = 12 to 16 then CAT = ‘Average’ If FinalIA< 12 then CAT =
‘Weak’

Give these details only for 8th semester A, B, and C section students.

E. Consider the schema for CompanyDatabase:

EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo) DEPARTMENT (DNo,
DName, MgrSSN, MgrStartDate)
DLOCATION (DNo,DLoc)
PROJECT (PNo, PName, PLocation, DNo)
WORKS_ON (SSN, PNo, Hours)

Write SQL queries to

1. Make a list of all project numbers for projects that involve an employee
whose last name is ‘Scott’, either as a worker or as a manager of the
department that controls theproject.
2. Show the resulting salaries if every employee working on the ‘IoT’ project is
given a 10 percentraise.
3. Find the sum of the salaries of all employees of the ‘Accounts’ department, as
well as the maximum salary, the minimum salary, and the average salary in this
department
4. Retrieve the name of each employee who works on all the projects controlled
by department number 5 (use NOT EXISTS operator). For each department
that has more than five employees, retrieve the department number and the
number of its employees who are making more than Rs.6,00,000.
 EXPERIMENTS NO.1:LIBRARY Database

1. Creating tables using CREATE TABLE command.

CREATE TABLE PUBLISHER(NAME VARCHAR(20) PRIMARY KEY, ADDRESS

VARCHAR(20),PHONE VARCHAR(10));

CREATE TABLE LIBRARY_BRANCH(BRANCH_ID INT PRIMARY KEY,BRANCH_NAME

VARCHAR(20),ADDRESS VARCHAR(20));

CREATE TABLE BOOK(BOOK_ID INT PRIMARY KEY,TITLE

VARCHAR(20),PUBLISHER_NAME VARCHAR(20) REFERENCES PUBLISHER(NAME) ON
DELETE CASCADE, PUB_YEAR VARCHAR(20));

CREATE TABLE BOOK_AUTHORS(BOOK_ID INT REFERENCES BOOK(BOOK_ID) ON

DELETE CASCADE,AUTHOR_NAME VARCHAR(20),PRIMARY
KEY(BOOK_ID,AUTHOR_NAME));

CREATE TABLE BOOK_COPIES(BOOK_ID INT REFERENCES BOOK(BOOK_ID) ON

DELETE CASCADE,BRANCH_ID INT REFERENCES LIBRARY_BRANCH(BRANCH_ID)ON
DELETE CASCADE, NO_OF_COPIES INT,PRIMARY KEY(BOOK_ID,BRANCH_ID));

CREATE TABLE BOOK_LENDING(BOOK_ID INT REFERENCES BOOK(BOOK_ID) ON

DELETE CASCADE,BRANCH_ID INT REFERENCES LIBRARY_BRANCH(BRANCH_ID)ON
DELETE CASCADE,CARD_NO INT, DATE_OUT DATE, DUE_DATE DATE,PRIMARY
KEY(BOOK_ID,BRANCH_ID,CARD_NO));

2. Inserting Values to the tables using INSERT INTO command.

INSERT INTO PUBLISHER VALUES ('Pearson','BANGALORE', 9989076587);

INSERT INTO PUBLISHER VALUES ('SBC','MANGALORE', 9971264715);
INSERT INTO PUBLISHER VALUES ('CELLO','BANGALORE', 9949076558);
INSERT INTO PUBLISHER VALUES ('AB+','MANGALORE', 8989076555);
INSERT INTO PUBLISHER VALUES ('DC','BANGALORE', 8855779621);

INSERT INTO BOOK VALUES (101,'DBMS', 'Pearson',2016);

INSERT INTO BOOK VALUES (102,'VISUAL STUDIO', 'Pearson',2016);
INSERT INTO BOOK VALUES (103,'NET', 'SBC',2016);
INSERT INTO BOOK VALUES (104,'ADE', 'SBC',2016);
INSERT INTO BOOK VALUES (105,'JAVA', 'SBC',2013);

INSERT INTO BOOK_AUTHORS VALUES(101 ,'SHAMAKANTH');

INSERT INTO BOOK_AUTHORS VALUES(102 ,'RAMEZ');
INSERT INTO BOOK_AUTHORS VALUES(103 ,'JOHN');
INSERT INTO BOOK_AUTHORS VALUES(104 ,'GENRKE');
INSERT INTO BOOK_AUTHORS VALUES(105 ,'JOHN');

INSERT INTO LIBRARY_BRANCH VALUES (101,'PEARSON','BANGALORE');

INSERT INTO LIBRARY_BRANCH VALUES (102,'PEARSON','BANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES (103,'SBC','MANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES (104,'SBC','MANGALORE');
INSERT INTO LIBRARY_BRANCH VALUES (105,'SBC','MANGALORE');

INSERT INTO BOOK_COPIES VALUES(101, 101,200);

INSERT INTO BOOK_COPIES VALUES(102, 102,400);
INSERT INTO BOOK_COPIES VALUES(103, 103,500);
INSERT INTO BOOK_COPIES VALUES(104, 104,600);
INSERT INTO BOOK_COPIES VALUES(105, 105,700);
INSERT INTO BOOK_COPIES VALUES(101, 105,400);
INSERT INTO BOOK_COPIES VALUES(101, 104,500);

INSERT INTO BOOK_LENDING VALUES (101,101,02,'16-AUG-2017','25-AUG-2017' );

INSERT INTO BOOK_LENDING VALUES (102,102,09,'14-AUG-2017','22-AUG-2017' );
INSERT INTO BOOK_LENDING VALUES (103,103,05,'18-AUG-2017','27-AUG-2017' );
INSERT INTO BOOK_LENDING VALUES (104,104,06,'01-AUG-2017','09-AUG-2017' );
INSERT INTO BOOK_LENDING VALUES (105,105,01,'15-AUG-2017','24-AUG-2017' );
INSERT INTO BOOK_LENDING VALUES (101,105,02,'16-JAN-2017','25-FEB-2017' );
INSERT INTO BOOK_LENDING VALUES (101,103,02,'16-FEB-2017','25-FEB-2017' );
INSERT INTO BOOK_LENDING VALUES (103,105,02,'16-FEB-2017','25-FEB-2017' );
INSERT INTO BOOK_LENDING VALUES (104,105,01,'16-APR-2017','25-JUN-2017' );
INSERT INTO BOOK_LENDING VALUES (104,101,02,'16-JAN-2017','25-FEB-2017' );
INSERT INTO BOOK_LENDING VALUES (102,105,02,'01-JAN-2017','25-FEB-2017' );

Query 1:
SELECT B.Book_id,Title,P.Name,Author_Name,Branch_id,No_of_copies
FROM Book B,Book_Authors BA,Publisher P,Book_Copies BC
WHERE B.Book_id=BA.Book_id AND
B.Publisher_Name=P.Name AND
B.Book_id=BC.Book_id;
Output:
Book Title Name Author Name Branch Id No Of Copies

101 Fundamentals of DBMS Pearson SHAMAKANTH 101 200

102 Micrsoft Visual Studio Pearson RAMEZ 102 400
103 .NET SBC JOHN 103 500

104 ADE SBC GENRKE 104 600

105 JAVA SBC JOHN 107 700
106 Fundamentals of DBMS Pearson SHAMAKANTH 107 400

101 Fundamentals of DBMS Pearson SHAMAKANTH 104 500

Query 2:
SELECT Card_No,COUNT(*)
FROM BOOK_LENDING
WHERE Date_out BETWEEN '01-JAN-17' AND '30-JUN-17'
GROUP BY Card_No
HAVING COUNT(*)>3;
Output:
Card_no No_of_Copies
2 5

Query 3:
DELETE
FROM BOOK
WHERE Book_id='101';
Output:
1 row deleted

Query 4:
CREATE VIEW V_PUBLICATION AS SELECT PUB_YEAR
FROM BOOK;
select * from V_PUBLICATION;
Output:
pub_year
2016
2016
2016
2016
2013

Query 5:
CREATE VIEW TOTAL_BOOK_COPIES(Book_id,Total_Books)
AS
Select Book_id,SUM(No_of_copies)
FROM BOOK_COPIES
GROUP BY Book_id;
Select * FROM TOTAL_BOOK_COPIES;
Output:
Book_id Total_Books
102 400
101 1100
107 700
104 600
103 500
 EXPERIMENTS NO.2:ORDER Database

1. Creating the tables

CREATE TABLE SALESMAN (SALESMAN_ID INTEGER
PRIMARY KEY,
NAME VARCHAR (20),
CITY VARCHAR (30),
COMMISSION VARCHAR (20));

CREATE TABLE CUSTOMER (CUSTOMER_ID INTEGER

PRIMARY KEY,
CUST_NAME VARCHAR (20),
CITY VARCHAR (30),
GRADE INT,
SALESMAN_ID INTEGER,
FOREIGN KEY(SALESMAN_ID)REFERENCES
SALESMAN (SALESMAN_ID)
ON DELETE SET NULL);

CREATE TABLE ORDERS (ORD_NO INTEGER PRIMARY KEY,

PURCHASE_AMT INTEGER,
ORD_DATE DATE,
CUSTOMER_ID INTEGER,
SALESMAN_ID INTEGER,
FOREIGN KEY(SALESMAN_ID)REFERENCES
SALESMAN (SALESMAN_ID)
ON DELETE CASCADE,
FOREIGN KEY(CUSTOMER_ID) REFERENCES
CUSTOMER(CUSTOMER_ID));

2. Inserting the values

INSERT INTO SALESMAN VALUES (1000, 'JOHN','BANGALORE',2000);
INSERT INTO SALESMAN VALUES (1001, 'RAMEZ','BANGALORE',1000);
INSERT INTO SALESMAN VALUES (1002, 'GENRKE','MANGALORE',800);
INSERT INTO SALESMAN VALUES (1003, 'GORGE','MANGALORE',2800);
INSERT INTO SALESMAN VALUES (1004, 'RAMESH','BANGALORE',2890);
INSERT INTO SALESMAN VALUES (1005, 'SUNIL','MANGALORE',3890);

INSERT INTO CUSTOMER VALUES (2000, 'SUNIL','MANGALORE', 1, 1000);

INSERT INTO CUSTOMER VALUES (2001, 'ANIL','BANGALORE', 1, 1000);
INSERT INTO CUSTOMER VALUES (2002, 'JOHN','BANGALORE', 2, 1005);
INSERT INTO CUSTOMER VALUES (2003, 'JOHN','BANGALORE', 2, 1000);
INSERT INTO CUSTOMER VALUES (2004, 'SUNIL','MANGALORE', 3, 1005);
INSERT INTO CUSTOMER VALUES (2005, 'SUNIL','BANGALORE', 1, 1002);
INSERT INTO CUSTOMER VALUES (2006, 'SUNIL','BANGALORE', 1, 1003);
INSERT INTO CUSTOMER VALUES (2007, 'RAHUL','CHENAI', 2, 1003);

INSERT INTO ORDERS VALUES (101, 7000, '21-AUG-17', 2002, 1005);

INSERT INTO ORDERS VALUES (102, 3000, '21-AUG-17', 2001, 1000);
INSERT INTO ORDERS VALUES (105, 8000, '21-AUG-17', 2000, 1000);
INSERT INTO ORDERS VALUES (108, 9000, '21-AUG-17', 2000, 1000);
INSERT INTO ORDERS VALUES (111, 5000, '29-AUG-17', 2000, 1000);
INSERT INTO ORDERS VALUES (121, 5900, '29-AUG-17', 2003, 1000);
INSERT INTO ORDERS VALUES (221, 5970, '29-AUG-17', 2004, 1005);
INSERT INTO ORDERS VALUES (291, 5970, '29-AUG-17', 2005, 1002);
INSERT INTO ORDERS VALUES (991, 5970, '29-AUG-17', 2006, 1003);

Query 1:
SELECT COUNT(Customer_id)
FROM CUSTOMER
WHERE GRADE>(SELECT AVG(GRADE) FROM CUSTOMER
WHERE CITY='BANGALORE');
Output:
No_Of_Copies
2

Query 2:
SELECT S.Salesman_id,S.Name
FROM SALESMAN S,CUSTOMER C
WHERE S.Salesman_id=C.Salesman_id
GROUP BY S.Salesman_id,S.Name
HAVING COUNT(Customer_id)>1;
Output:
Salesman_Id Name
1003 GORGE
1000 JOHN
1005 SUNIL

Query 3:
SELECT S.Salesman_id FROM
SALESMAN S,CUSTOMER C
WHERE S.Salesman_id=C.Salesman_id
AND S.City!=C.City
UNION
SELECT S.Salesman_id
FROM SALESMAN S,CUSTOMER C
WHERE S.Salesman_id=C.Salesman_id
AND S.City=C.City;
Output:
Salesman_Id
1000
1002
1003
1005

Query 4:
create view SALESMAN_WITH_MAX_ORDER as
select B.ORD_DATE,A.SALESMAN_ID,A.NAME
from salesman a, orders b
where a.salesman_id=b.salesman_id and
b.purchase_amt=(select max(purchase_amt)
from orders c where c.ord_date=b.ord_date);
select * from SALESMAN_WITH_MAX_ORDER;
Output:
Salesman_Id Name City Commision
1000 JOHN BANGALORE 2000

Query 5:
DELETE FROM SALESMAN
WHERE Salesman_id=1000;
Output:
1 Row Deleted.
 EXPERIMENTS NO.3:MOVIE Database

1. Creating the tables

create table actor( act_id int primary key,
act_name varchar(20),
act_gender char);

create table director( dir_id int primary key,

dir_name varchar(20),
dir_phone varchar(10));

create table movies( mov_id int primary key,

mov_title varchar(30),
mov_year varchar(4),
mov_lang varchar(20),
dir_id int,
foreign key(dir_id) references director(dir_id));

create table movie_cast( act_id int,

mov_id int,
role varchar(20),
primary key(act_id,mov_id) ,
foreign key(act_id) references actor(act_id),
foreign key(mov_id) references movies(mov_id));

create table rating( mov_id int,

rev_stars int,
primary key(mov_id),
foreign key(mov_id) references movies(mov_id));

2. Inserting the values

insert into actor values(1,'Ganesh','M');
insert into actor values(2,'Sudeep','M');
insert into actor values(3,'Radhika','F');
insert into actor values(4, 'Rakshitha','F');
insert into actor values(5,'Ravichandran','M');
insert into actor values(6,'Lakshmi','F');

insert into director values( 101 ,'Dorai Bhagavan',9408757465);

insert into director values( 102,'Yogaraj Bhat',9408432566);
insert into director values( 103,'Sudeep',9987657465);
insert into director values(104,'Ravichandran',9888757465);
insert into director values(105,'Dwarakeesh',9484567465);
insert into director values(106,'Hitchcock',9574635243);
insert into director values( 107,'Steven Spielberg',9384756452);
insert into movies values(11,'Mungaru Male',2008,'Kannada',102);
insert into movies values(12,'Kempegouda ',2010,'Kannada', 103);
insert into movies values( 13,'Premaloka', 1990,'Kannada', 104);
insert into movies values(14,'Apta Mitra',2010,,'Kannada', 105);
insert into movies values(15,'Apta Rakshaka',2012,'Kannada', 105);
Insert Into movies values( 16,'Ranadheera', 1992 'Kannada', 104);
Insert Into movies values( 17,'Ganeshana Maduve', 1990,'Kannada',101);
Insert Into movies values( 18,'The Last Chance', 2015,'English',106);
Insert into movies values( 19,'The War' ,2016,'English', 106);
insert into movies values( 20,'Murder', 2016,'English', 106);
Insert Into movies values(21, 'The Beautiful Bird' ,'English', 107);

insert into movie_cast values( 1,11,'Hero');

insert into movie_cast values( 2 ,12,' Hero');
insert into movie_cast values( 3 ,14,' Hero');
insert into movie_cast values( 5 ,16,' Hero');
insert into movie_cast values( 6 ,17,' Hero');
insert into movie_cast values( 1 ,17,' Hero');
insert into movie_cast values( 1 ,18,' Hero');
insert into movie_cast values( 1 ,19,' Subordinate');
insert into movie_cast values( 2 ,20,' Hero');
insert into movie_cast values( 2 ,21,' Hero');

insert into rating values( 11,5);

insert into rating values( 12,4);
insert into rating values( 13,5);
insert into rating values( 14,4);
insert into rating values( 15,5);
insert into rating values( 16,5);
insert into rating values( 17,5);
insert into rating values( 18,3);
insert into rating values( 19,3);
insert into rating values( 20,1);
insert into rating values( 21,4);

Query 1:
select mov_title
from movies
where dir_id=(select dir_id from director where dir_name = 'Hitchcock');
Output:
Mov_Title
The Last Chance
The War
Murder

Query 2:
select m.mov_id,mov_title from movies m,movie_cast mc
where m.mov_id=mc.mov_id and act_id in
( select act_id from movie_cast
group by act_id having count(*)>=2) order by m.mov_id;
Output:
Mov_Id Mov_Title

11 Mungaru Male
12 Renipegouda
17 Ganeshana Macluvc
18 The Last Chance
19 The War
20 Murder
21 The Beautiful Bird

Query 3:
select a.act_id,act_name
from actor a, movies m, movie_cast mc
where a.act_id= mc.act_id and m.mov_id =mc.mov_id and mov_year> 2015
INTERSECT
select a.act_id,act_name
from actor a, movies m, movie_cast mc
where a.act_id =mc.act_id and m.mov_id =mc.mov_id and mov_year< 2000;
Output:
Act_Id Act_Name
1 Ananthnag

Query 4:
select mov_title,rev_stars
from movies m, rating r
where m.mov_id= r.mov_id and rev_stars in
(select max(rev_stars) from rating) order by mov_title;
Output:
Mov_Title Rev_Stars

Apta Rakshaka 5
Ganeshana Maduve 5
Mungaru Male 5
Premaloka 5
Ranadheera 5
Query 5:
update rating
set rev_stars= 5
where mov_id IN (select mov_id from movies m, director d
where m.dir_id =d.dir_id and dir_name='Steven Spielberg');
Output:
1 row updated.
 EXPERIMENTS NO.4:STUDENT Database

1. Creating the tables

create table student( usn char( 10) primary key,
sname varchar(20),
address varchar(20),
phone varchar( 10),
gender char);

create table semsec( ssid int primary key,

sem int,
sec char);

create table class( usn char(10) primary key,

ssid int references semsec(ssid),
foreign key(usn) references student(usn));

create table subject( subcode varchar(8) primary key,

title varchar(20),
sem int,
credits int);

create table iamarks( usn char( 10) references student(usn),

subcode varchar(8) references subject(subcode),
ssid int references semsec(ssid),
test1 int, test2 int, test3 int, finalia int,
primary key(usn,subcode,ssid));

2. Inserting the values

insert into student values ('4MT15CS001','ABHIRAM','KERALA',7658475647,'M');
insert into student values ('4MT15CS003','ADITHYA','KERALA',8658495647,'M');
insert into student values ('4MT15CS005','AKHIL','KERALA',8858475947,'M');
insert into student values ('4MT15CS006','AKSHATHA','MANGALORE',9958475697,'F');
insert into student values ('4MT15CS007','AKSHAY','MOODBIDRI',7758475647,'M');
insert into student values ('4MT15CS008','ALOMA','MANGALORE',9658475697,'MF
insert into student values ('4MT15CS010','ANANTHA','KARKALA',8656975647,'M');
insert into student values ('4MT15CS011','ANIL','KERALA',9658471647,'M');
insert into student values ('4MT15CS012','ANKITHA','MANGALORE',9988275647,'F');
insert into student values ('4MT15CS014','ANUSHA','MANGALORE',7658987447,'F');
insert into student values ('4MT15CS015','ANUSHREE','MOODBIDRI',9988445522,'F');
insert into student values ('4MT15CS017','ASHIKA','MANGALORE',8866335577,'F');

insert into semsec values( 1 ,5,'A');

insert into semsec values(2,5,'B');
insert into semsec values(3,7,'A');
insert into semsec values(4,7,'B');
insert into semsec values(5,3,'A');
insert into semsec values(6,3,'B');

insert into class values('4MT15CS001',l);

insert into class values('4MT15CS003',l);
insert into class values('4MT15CS005',l);
insert into class values('4MT15CS006',l);
insert into class values('4MT15CS007',l);
insert into class values('4MT15CS008',l);
insert into class values('4MT15CS010',l);
insert into class values('4MT15CS011',2);
insert into class values('4MT15CS012',2);
insert into class values('4MT15CS014',2);
insert into class values('4MT15CS015',2);
insert into class values('4MT15CS017',2);

insert into subject values('15CS51','ME',5,4);

insert into subject values('15CS52', 'CN',5,4);
insert into subject values('15CS53','DBMS',5,4);
insert into subject values('15CS54','ATC',5,4);
insert into subject values('15CS553','ADVANCED JAVA',5,4);
insert into subject values('15CS561','DOT NET',5,4);

insert into iamarks values('4MT15CS001','15CS51',1,17,18,15,null);

insert into iamarks values('4MT15CS001','15CS52',1,12,18,13,null);
insert into iamarks values('4MT15CS001','15CS53',1,10,14,17,null);
insert into iamarks values('4MT15CS001','15CS54',1,11,18,14,null);
insert into iamarks values('4MT15CS001','15CSS53',1,17,18,19,null);
insert into iamarks values('4MT15CS001','15CS561',1,8,8,18,null);

insert into iamarks values('4MT15CS003','15CS51',1,17,18,15,null);

insert into iamarks values('4MT15CS003','15CS52',1,12,18,13,null);
insert into iamarks values('4MT15CS003','15CS53',1,10,14,17,null);
insert into iamarks values('4MT15CS003','15CS54',1,11,18,14,null);
insert into iamarks values('4MT15CS003','15CSS53',1,17,18,19,null);
insert into iamarks values('4MT15CS003','15CS561',1,8,8,18,null);

insert into iamarks values('4MT15CS005','15CS51',1,17,18,15,null);

insert into iamarks values('4MT15CS005','15CS52',1,12,18,13,null);
insert into iamarks values('4MT15CS005','15CS53',1,10,14,17,null);
insert into iamarks values('4MT15CS005','15CS54',1,11,18,14,null);
insert into iamarks values('4MT15CS005','15CSS53',1,17,18,19,null);
insert into iamarks values('4MT15CS005','15CS561',1,8,8,18,null);

insert into iamarks values('4MT15CS006','15CS51',1,17,18,15,null);

insert into iamarks values('4MT15CS006','15CS52',1,12,18,13,null);
insert into iamarks values('4MT15CS006','15CS53',1,10,14,17,null);
insert into iamarks values('4MT15CS006','15CS54',1,11,18,14,null);
insert into iamarks values('4MT15CS006','15CSS53',1,17,18,19,null);
insert into iamarks values('4MT15CS006','15CS561',1,8,8,18,null);

insert into iamarks values('4MT15CS007','15CS51',1,17,18,15,null);

insert into iamarks values('4MT15CS007','15CS52',1,12,18,13,null);
insert into iamarks values('4MT15CS007','15CS53',1,10,14,17,null);
insert into iamarks values('4MT15CS007','15CS54',1,11,18,14,null);
insert into iamarks values('4MT15CS007','15CSS53',1,17,18,19,null);
insert into iamarks values('4MT15CS007','15CS561',1,8,8,18,null);

Query 1:
select s.usn,s.sname,address,phone,gender
from student s, semsec ss, class c
where s.usn = c.usn and c.ssid = ss.ssid
and sem =5
and sec ='B';
Output:
USN SNAME ADDRESS PHONE G

4MT15CS011 ANIL KUMAR KERALA 7657561234 M

4MT15CS012 ANKITHA MANGALORE 9876555647 F
4MT15CS014 ANUSHA MANGALORE 798123457 F
4MTl 5CS015 ANUSHREE M MOODBIDRI 7653456743 M
4MT15CS017 ASHIKA SHETTY MANGALORE 7877564547 F

Query 2:
select ss.sem, ss.sec,s.gender,count(s.gender)
as count from student s, semsec ss,class c
where s.usn=c.usn and ss.ssid=c.ssid
group by ss.sem,ss.sec,s.gender
order by sem;
Output:
SEM S No of Male Students No of Female Students
5 A 5 2
5 B 2 3

Query 3:
create view stu_test1_marks_view as
select test1, subcode
from iamarks
where usn='4MT15CS005';
select * from stu_test1_marks_view;
Output:
Usn Student Name SubjectCode Subject Name Test1 Marks

4MT15CS005 AKHIL 15CS51 ME 17

4MT15CS005 AKHIL 15CS52 CN 12

4MT15CS005 AKHIL 15CS53 DBMS 10
4MT15CS005 AKHIL 15CS54 ATC 11
4MT15CS005 AKHIL 15CS553 JAVA 17
4MT15CS005 AKHIL 15CS561 DOT NET 8

Query 4:
CREATE OR REPLACE PROCEDURE AVGMARKS IS
CURSOR C_IAMARKS IS
SELECT GREATEST(TEST1,TEST2) AS A, GREATEST(TEST1,TEST3) AS B,
GREATEST(TEST3,TEST2) AS C
FROM IAMARKS
WHERE FINALIA IS NULL
FOR UPDATE
C_A NUMBER;
C_B NUMBER;
C_C NUMBER;
C_SM NUMBER;
C_AV NUMBER;
BEGIN
OPEN C_IAMARKS;
LOOP
FETCH C_IAMARKS INTO C_A, C_B, C_C;
EXIT WHEN C_IAMARKS%NOTFOUND;
--DBMS_OUTPUT.PUT_LINE(C_A || ' ' || C_B || ' ' ||C_C);
IF (C_A != C_B)
THEN
C_SM:=C_A+C_B;
ELSE
C_SM:=C_A+C_C;
END IF;
C_AV:=C_SM/2;
--DBMS_OUTPUT.PUT_LINE('SUM = '||C_SM);
--DBMS_OUTPUT.PUT_LINE('AVERAGE = '||C_AV);
UPDATE IAMARKS SET FINALIA=C_AV WHERE CURRENT OF C_IAMARKS;
END LOOP;
CLOSE C_IAMARKS;
END;
/
Output:
1 ROW UPDATED

Query 5:
select s.usn,sname,finalia,
(CASE
WHEN finalia>=17 and finalia<=20 then 'Outstanding'
WHEN finalia>=12 and finalia<17 then 'Average'
WHEN finalia<12 then 'Weak'
END) CAT
from student s, iamarks ia where s.usn=ia.usn;
Output:
USN SNAME FINALIA CAT
4MT15CS001 ABHIRAM 17.5 OUTSTANDING
4MT15CS001 ABHIRAM 15.5 AVERAGE
4MT15CS001 ABHIRAM 16 AVERAGE
4MT15CS001 ABHIRAM 18.5 OUTSTANDING
4MT15CS001 ABHIRAM 13 AVERAGE
4MT15CS001 ADHITYA 15.5 AVERAGE
4MT15CS001 ADHITYA 15.5 AVERAGE
4MT15CS001 ADHITYA 16 AVERAGE
4MT15CS001 ADHITYA 18.5 OUTSTANDING
4MT15CS001 ADHITYA 13 AVERAGE
4MT15CS001 AKHIL 17.5 OUTSTANDING
4MT15CS001 AKHIL 15.5 AVERAGE
4MT15CS001 AKHIL 15.5 AVERAGE
4MT15CS001 AKHIL 16 OUTSTANDING
4MT15CS001 AKHIL 18.5 OUTSTANDING
4MT15CS001 AKHIL 13 AVERAGE
 EXPERIMENTS NO.5:EMPLOYEE Database

create table department5( dno int primary key,

dname varchar(20));

insert into department5 values( 1 ,'Accounts');

insert into department5 values(2,'Headquarters');
insert into department5 values(5,'Administration');

create table employee5( ssn int primary key,

Name varchar(20),
address varchar(30),
sex varchar(3),
salary int,
superssn int,foreign key(superssn) references employee5(ssn),
dno int,foreign key(dno) references department5(dno));

insert into employee5 values (123456789, 'John Smith', 'Houston','M', 25000, null,1);
insert into employee5 values (123123123, 'Scott', 'Houston', 'M', 25000, 123456789,1);
insert into employee5 values (134134134, 'Alicia', 'Houston', 'F' ,23000, 156156156,5);
insert into employee5 values (124124124, 'Ramesh', 'India', 'M', 612000, 156156156,5);
insert into employee5 values (145145145, 'Narayant', 'India', 'M', 25000, 156156156, 5);
insert into employee5 values (156156156, 'Franklin', 'Texas', 'M', 775000, null,5);
insert into employee5 values (167167167, 'Ganesh', 'Texas', 'M', 79000, 178178178, 2);
insert into employee5 values (178178178, 'Mahesh', 'Houston', 'M', 610000, null, 2);
insert into employee5 values (189189189, 'Prakash', 'Houston', 'M', 85000, 156156156, 5);
insert into employee5 values (190190190, 'Smith', 'Texas', 'M', 75000, 178178178, 2);

alter table department5 add mgrssn int,

mgrstartdate varchar(15),foreign key(mgrssn) references employee5(ssn);

update department5 set mgrssn=123456789, mgrstartdate=' 10-Aug-2015' where dno=1;

update department5 set mgrssn=178178178, mgrstartdate=' 25-DEC-2005' where dno=2;
update department5 set mgrssn=156156156, mgrstartdate=' 12-JAN-2010' where dno=5;

create table dlocation5( dno int,

dloc varchar(20),
primary key(dno,dloc),
foreign key (dno) references department5(dno));

insert into dlocation5 values( 1,'Houston');

insert into dlocation5 values( 1 ,'Texas');
insert into dlocation5 values(2,'India');
insert into dlocation5 values(5,'Houston');
insert into dlocation5 values(5,'Sugarland');
insert into dlocation5 values(5,'India');

create table project5( pno int primary key,

pname varchar(20),
plocation varchar(20),
dno int,
foreign key (dno)references department5(dno));

insert into project5 values(101,'IoT','Houston', 1);

insert into project5 values( 102,'ProductX','Houston',5);
insert into project5 values( 105,'ProductY','Sugarland',2);
insert into project5 values( 106,'ProductZ','Houston',5);

create table works_on5( ssn int , pno int,

foreign key (ssn) references employee5(ssn),
foreign key (pno) references project5(pno),
hours int,
primary key( ssn,pno));

insert into works_on5 values(123456789,101,10);

insert into works_on5 values(123456789,106,20);
insert into works_on5 values(123123123,102,30);
insert into works_on5 values(123456789,105,10);
insert into works_on5 values(124124124,101,5);
insert into works_on5 values(124124124,106,40);
insert into works_on5 values(145145145,102,20);
insert into works_on5 values(145145145,106,20);
insert into works_on5 values(156156156, 105,30);
insert into works_on5 values(167167167,106,10);
insert into works_on5 values( 178178178, 105,5);
insert into works_on5 values( 189189189,101,10);

Query 1:
select pno from employee5 e, works_on5 w
where e.ssn=w.ssn and name='Scott'
UNION
select pno from employee5 e, department5 d, project5 p
where e.ssn=d.mgrssn and d.dno=p.pno and name='Scott' ;
Output:
PNO

102

105

Query 2:
select Name,Salary "Old Salary", Salary* 1.1 "New Salary"
from employee5 e,works_on5 w, project5 p
where e.ssn =w.ssn and w.pno= p.pno and pname = 'IoT';
Output:
NAME OLD SALARY NEW SALARY

JOHN 25000 27500

ALICIA 23000 25300

PRAKASH 85000 93500

Query 3:
select sum(salary) "Total Salary",
Max(Salary) "Maximum Salary" ,
Min(Salary) "Minimum Salary",
Avg(Salary) "Average Salary"
from employee5 e, department5 d
where e.dno= d.dno and dname='Accounts';
Output:
TOTAL SALARY MAX SALARY MIN SALARY AVERAGE SALARY

50000 25000 25000 25000

Query 4:
select e.name from employee5 e
where not exists
((select pno
from project5
where dno=5) except
(select pno
from works_on5
where e.ssn= ssn));
Output:
SSN

145145145

Query 5:
select dno,
(select count(*)
from employee5
where salary>600000 and dno in
(select dno
from employee5
group by dno
having count(*)>=5 ))
as count_salary from employee5
group by dno
having count(*)>=5;
Output:
DNO NO_OF_EMP_MORE_THAN_600000
5 2
 Viva Questions

1. What is SQL?
Structured Query Language
2. What is database?
A database is a logically coherent collection of data with some inherent meaning, representing
some aspect of real world and which is designed, built and populated with data for a specific
purpose.
3. What is DBMS?
It is a collection of programs that enables user to create and maintain a database. In other
words it is general-purpose software that provides the users with the processes of defining,
constructing and manipulating the database for various applications.
4. What is a Database system?
The database and DBMS software together is called as Database system.
5. Advantages of DBMS?
➢ Redundancy is controlled.
➢ Unauthorized access is restricted.
➢ Providing multiple user interfaces.
➢ Enforcing integrity constraints.
➢ Providing backup and recovery.
6. Disadvantage in File Processing System?
➢ Data redundancy &inconsistency.
➢ Difficult in accessing data.
➢ Data isolation.
➢ Data integrity.
➢ Concurrent access is not possible.
➢ Security Problems.
7. Describe the three levels of data abstraction?
There are three levels of abstraction:
➢ Physical level: The lowest level of abstraction describes how data are stored.
➢ Logical level: The next higher level of abstraction, describes what data are stored in
database and what relationship among those data.
➢ View level: The highest level of abstraction describes only part of entire database.
8. Define the "integrity rules"
There are two Integrity rules.
➢ Entity Integrity: States that―Primary key cannot have NULL value□
➢ Referential Integrity:States that ―Foreign Key can be either a NULL value or
should be Primary Key value of other relation.
9. What is extension and intension?
Extension - It is the number of tuples present in a table at any instance. This is time dependent.
Intension -It is a constant value that gives the name, structure of table and the constraints laid on it.

10. What is Data Independence?

Dataindependencemeansthat―theapplicationisindependentofthestoragestructureand access
strategy of data□. In other words, The ability to modify the schema definition in one level
should not affect the schema definition in the next higher level.
Two types of Data Independence:
➢ Physical Data Independence: Modification in physical level should not affect
the logical level.
➢ Logical Data Independence: Modification in logical level should affect the view
level.

11. What is a view? How it is related to data independence?

A view may be thought of as a virtual table, that is, a table that does not really exist in its
own right but is instead derived from one or more underlying base table. In other words, there is
no stored file that direct represents the view instead a definition of view is stored in data
dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can
insulate users from the effects of restructuring and growth in the database. Hence accounts for
logical data independence.
12. What is Data Model?
A collection of conceptual tools for describing data, data relationships data semantics and
constraints.
13. What is E-R model?
This data model is based on real world that consists of basic objects called entities and
of relationship among these objects. Entities are described in a database by a set of attributes.
14. What is Object Oriented model?
 This model is based on collection of objects. An object contains values stored in instance
variables within the object. An object also contains bodies of code that operate on the object.
These bodies of code are called methods. Objects that contain same types of values and the same
methods are grouped together into classes.
15. What is an Entity?
It is an 'object' in the real world with an independent existence.
16. What is an Entity type?
It is a collection (set) of entities that have same attributes.
17. What is an Entity set?
It is a collection of all entities of particular entity type in the database.
18. What is an Extension of entity type?
The collections of entities of a particular entity type are grouped together into an entity set.
19. What is an attribute?
It is a particular property, which describes the entity.
20. What is a Relation Schema and a Relation?
A relation Schema denoted by R(A1, A2, …, An) is made up of the relation name R
and the list of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the
relation which contains set tuples (t1,t2, t3,...,tn). Each tuple is an ordered list of n- values
t=(v1,v2, ...,vn).
21. What is degree of a Relation?
It is the number of attribute of its relation schema.
22. What is Relationship?
It is an association among two or more entities.
23. What is Relationship set?
The collection (or set) of similar relationships.
24. What is Relationship type?
Relationship type defines a set of associations or a relationship set among a given set
of entity types.
25. What is degree of Relationship type?
It is the number of entity type participating.
26. What is DDL (Data Definition Language)?
A data base schema is specified by a set of definitions. expressed by a special
language called DDL.
27. What is VDL (View Definition Language)?
It specifies user views and their mappings to the conceptual schema.
28. What is SDL (Storage Definition Language)?
This language is to specify the internal schema. This language may specify the mapping between two
schemas.
29. What is Data Storage – Definition Language?
The storage structures and access methods used by database system are specified by a set of definition
in a special type of DDL called data storage-definition language.
30. What is DML (Data Manipulation Language)?
This language that enable user to access or manipulate data as organized by appropriate data model.
➢ Procedural DML or Low level: DML requires a user to specify what data are
needed and how to get those data.
➢ Non-Procedural DML or High level: DML requires a user to specify what data are needed without
specifying how to get those data.

31. What is DML Compiler?

It translates DML statements in a query language into low-level instruction that the
query evaluation engine can understand.
32. What is Relational Algebra?
It is a procedural query language. It consists of a set of operations that take one or
two relations as input and produce a new relation.
33. What is Relational Calculus?
 It is an applied predicate calculus specifically tailored for relational databases
proposed by E.F. Codd. E.g. of languages based on it are DSL, ALPHA,QUEL.
34. What is normalization?
It is a process of analyzing the given relation schemas based on their Functional
Dependencies (FDs) and primary key to achieve the properties
➢ Minimizing redundancy
➢ Minimizing insertion, deletion and update anomalies.
35. What is Functional Dependency?
A Functional dependency is denoted by X Y between two sets of attributes X and Y
thataresubsetsofRspecifiesaconstraintonthepossibletuplethatcanformarelationstaterof
R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] =
t2[Y]. This means the value of X component of a tuple uniquely determines the value of
component Y.
36. When is a functional dependency F said to be minimal?
➢ Every dependency in F has a single attribute for its right hand side.
➢ We cannot replace any dependency X A in F with a dependencyY A where Y is a
proper subset of X and still have a set of dependency that is equivalent to F.
➢ We cannot remove any dependency from F and still have set of dependency that is
equivalent to F.
37. What is Multivalued dependency?
Multivalued dependency denoted by X Y pecified on relation schema R, where X and Y
are both subsets of R, specifies the following constraint on any relation r of R: if two tuples t1
and t2 exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the following
properties
➢ t3[x] = t4[X] = t1[X] =t2[X]
➢ t3[Y] = t1[Y] and t4[Y] =t2[Y]
➢ t3[Z] = t2[Z] and t4[Z] = t1[Z]
where [Z = (R-(X U Y)) ]
38. What is Lossless join property?
It guarantees that the spurious tuple generation does not occur with respect to
relation schemas after decomposition.
39. What is 1 NF (Normal Form)?
The domain of attribute must include only atomic (simple, indivisible) values.
40. What is Fully Functional dependency?
It is based on concept of full functional dependency. A functional dependency X Y is
fully functional dependency if removal of any attribute A from X means that the dependency
does not hold anymore.
41. What is2NF?
A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully
functionally dependent on primary key.
42. What is3NF?
A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the
following is true
➢ X is a Super-key of R.
➢ A is a prime attribute of R.
In other words, if every non prime attribute is non-transitively dependent on primary key.
43. What is BCNF (Boyce-Codd Normal Form)?
A relation schema R is in BCNF if it is in 3NF and satisfies additional constraints that for
every FD X A, X must be a candidate key.
44. What is4NF?
A relation schema R is said to be in 4NF if for every Multivalued dependency X

Y that holds over R, one of following is true

➢ X is subset or equal to (or) XY =R.
➢ X is a super key.
45. What is5NF?
A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ...,Rn}
that holds R, one the following is true
➢ Ri = R for somei.
➢ The join dependency is implied by the set of FD, over R in which the left side is key ofR.
46. What is Domain-Key Normal Form?
A relation is said to be in DKNF if all constraints and dependencies
that should hold on the constraint can be enforced by simply enforcing
the domain constraint and key constraint on the relation.