2019-20 19MCAL38 1NH18MCA39

+Exercise 1: Course Database

Consider the following relations:

Student (snum: integer, sname: string, major: string, level: string, age: integer)

Class (cname: string, meets at: string, room: string, d: integer)

Enrolled (snum: integer, cname: string)

Faculty (fid: integer, fname: string, deptid: integer)

The meaning of these relations is straightforward; for example, enrolled has one record per student-
class pair such that the student is enrolled in the class. Level is a two-character code with 4 different
values (example: Junior: JR etc)

Write the following queries in SQL. No duplicates should be printed in any of the answers.

i. Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof.
Harshith
ii. Find the names of all classes that either meet in room R128 or have five or more
Students enrolled.
iii. Find the names of faculty members who teach in every room in which some class is
taught.
iv. Find the names of faculty members for whom the combined enrollment of the courses
that they teach is less than five.

SQL> create table student( snum number(5) primary key,sname varchar2(20),major

varchar2(20),slevel varchar2(20),age number(2));

SQL>create table faculty(fid number(5) primary key, fname varchar2(20),deptid number(5));

SQL>create table class(cname varchar2(20),meet_at varchar2(10),room varchar2(10),fid numer(5)

references faculty(fid));

SQL>create table enrolled(snum number(5) references student20(snum),cname varchar2(20),

primary key(snum,cname));

SQL>insert into student values(&snum,’&sname’,’&major’,’&slevel’,&age);

SQL>insert into faculty values(&fid,’&fname’,&deptid);

SQL>insert into class values(‘&cname’,’&meets_at’,’&room’,&fid);

SQL>insert into enrolled values(&snum,’&cname’);

1|P ag e
2019-20 19MCAL38 1NH18MCA39

SQL> select * from student;

SNUM SNAME MAJOR SLEVEL AGE

111 anil cs jr 30
222 sunil cs jr 23
333 barath is jr 25
444 varun is jr 23
555 arun cs sr 25

SQL> select * from class;

CNAME MEETS_AT ROOM FID

C 2pm r122 1221
cpp 1pm r124 1222
java 12pm r126 1223
.net 3pm r128 1224
cobol 1pm r128 1225
cpp 2pm r124 1221
.net 2pm r128 1222
cpp 2pm r126 1221
java 2pm r128 1221

SQL> select * from enrolled;

SNUM CNAME
111 .net
222 Cobol
333 C
444 Java
555 Java
111 C
222 C
444 C
555 C

SQL> select * from faculty;

FID FNAME DEPTID

1221 harshith 500
1222 snk 501
1223 Rc 502
1224 sps 503
1225 va 504
2|P ag e
2019-20 19MCAL38 1NH18MCA39

1.Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof.
Harshith

select distinct s1.sname from student s1, enrolled e, class c, faculty f where c.fid=f.fid and
c.cname=e.cname and s1.snum=e.snum and s1.slevel='jr' and f.fname='harshith';

SNAME
anil
varun
barath
sunil

2 Find the names of all classes that either meet in room R128 or have five or more Students
enrolled.

select distinct cname from class c where room='r128' or c.cname in(select e.cname from enrolled e
group by e.cname having count(*)>=5);

NAME
cobol
java
c
.net

3 Find the names of faculty members who teach in every room in which some class is taught.

select f.fname from faculty f where fid in (select fid from class group by fid having count(distinct
room)=(select count(distinct room) from class));

FNAME
harshith

3|P ag e
2019-20 19MCAL38 1NH18MCA39

4 Find the names of faculty members for whom the combined enrollment of the courses that
they teach is less than five.

select f.fname from faculty f where fid in(select distinct(fid)from class c, enrolled e where
c.cname=e.cname group by fid having count(fid)<5);

FNAME
snk
rc
sps
va

4|P ag e
2019-20 19MCAL38 1NH18MCA39

Exercise 2: Airline Database

The following relations keep track of airline flight information:

Flights (no: integer, from: string, to: string, distance: integer, Departs: time, arrives: time,
price: real)

Aircraft (aid: integer, aname: string, cruisingrange: integer)

Certified (eid: integer, aid: integer)

Employees (eid: integer, ename: string, salary: integer)

Note that the Employees relation describes pilots and other kinds of employees as well; Every
pilot is certified for some aircraft, and only pilots are certified to fly.

Write each of the following queries in SQL.

i. Find the names of aircraft such that all pilots certified to operate them have salaries
more than Rs.80, 000.
ii. Find the names of pilots whose salary is less than the price of the cheapest route from
Bengaluru to Frankfurt.
iii. Find the names of pilots certified for some Boeing aircraft.
iv. Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.

SQL>create table flights(no number(5) primary key, from1 varchar2(20),to1 varchar2(20),distance

number(5),departs varchar2(10),arrives varchar2(10),price number(8));

SQl>create table aircraft(aid number(5) primary key,aname varchar2(20),crusingrange number(5));

SQl>create table employ(eid number(5) primary key,ename varchar2(20),salary number(7));

SQL>create table certified(eid number(5) references employ(eid),aid numbe(5) references

aircraft(aid));

SQL>insert into flights values(&no,’&from1’,’&to1’,&distance,’&departs’,’&arrives’,&price);

SQL>insert into aircraft values(&aid,’&aname’,&cruisingrange);

SQL>insert into employ values(&eid,’&ename’,&salary);

SQL>insert into certified values(&eid,&aid);

5|P ag e
2019-20 19MCAL38 1NH18MCA39

SQL> select * from flights;

NO FROM1 TO1 DISTANCE DEPARTS ARRIVES PRICE

1001 bangalore frankfurt 1000 8am 1pm 90000
1002 bangalore newdelhi 2000 1am 4am 2500
1003 newdelhi bangalore 2000 4am 8am 2500
1004 bangalore pune 500 7pm 9pm 1500
1005 bangalore chennai 150 5pm 7pm 1750
1006 bangalore frankfurt 5000 3am 7pm 95000

SQL> select * from aircraft;

AID ANAME CRUSINGRANGE

500 boeing 1000
501 kingfisher 2500
502 spicejet 500
503 boeing 3000
504 avenger 5000

SQL>select * from certified;

EID AID
1 500
2 501
3 504
5 502
3 503
3 501
3 504

SQL>select * from employ;

EID ENAME SALARY

1 john 85000
2 smith 75000
3 priya 60000
5 albert 85000
4 kennath 75000

6|P ag e
2019-20 19MCAL38 1NH18MCA39

1 Find the names of aircraft such that all pilots certified to operate them have salaries more
than Rs.80, 000

select aname from aircraft where aid in(select c.aid from certified c,employ e where c.eid=e.eid and
e.salary>80000);

ANAME
boeing
spicejet

2 Find the names of pilots whose salary is less than the price of the cheapest route from
Bengaluru to Frankfurt

select distinct e.ename from employ e, certified c where e.eid=c.eid and salary<(select min(price)
from flights where from1 like 'bangalore' and to1 like 'frankfurt');

ENAM
E
albert
john
priya
smith

3 Find the names of pilots certified for some Boeing aircraft.

select distinct(ename) from employ e, certified c,aircraft a where c.eid=e.eid and a.aid=c.aid and
aname= 'boeing';
ENAME
john
priya

4 Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.
select aid from aircraft a where crusingrange>(select max(distance) from flights where
from1='bangalore' and to1='newdelhi');

AID
501
503
504

7|P ag e
2019-20 19MCAL38 1NH18MCA39

Exercise 3: Student Database

Consider the following database of student enrollment in courses & books adopted for each course.

STUDENT (regno: string, name: string, major: string, bdate:date)

COURSE (course #:int, cname:string, dept:string)

ENROLL ( regno:string, course#:int, sem:int, marks:int)

TEXT (book-ISBN:int, book-title:string, publisher:string, author:string)

BOOK _ ADOPTION (course# :int, sem:int, book-ISBN:int)

(i) Create the above tables by properly specifying the primary keys and the foreign keys.
(ii) Enter at least five tuples for each relation.
(iii) Demonstrate how you add a new text book to the database and make this book be
adopted by some department.
(iv) Produce a list of text books (include Course #, Book-ISBN, Book-title) in the
alphabetical order for courses offered by the ‘CS’ department that use more than two
books
(v) List any department that has all its adopted books published by a specific publisher.
(vi) Generate suitable reports.

(i) Create the above tables by properly specifying the primary keys and the foreign keys.

SQL> create table student5(regno varchar2(5) primary key,name varchar2(10),major

varchar2(5),bdate date);

Table created.

SQL> create table course5(course_no number(5) primary key,cname varchar2(10),dept varchar2(10));

Table created.

SQL> create table enroll5(regno varchar2(5) references student(regno),course_no number(5)

references course(course_no),sem number(3),marks number(4));

Table created.

SQL> create table text5(book_isbn number(4) primary key,book_title varchar2(10),publisher

varchar2(10),author varchar2(10));

Table created.

8|P ag e
2019-20 19MCAL38 1NH18MCA39

SQL> create table book_adoption5(course_no number(5) references course(course_no),sem

number(3),book_isbn number(4) references text(book_isbn));

Table created.

(ii) Enter at least five tuples for each relation.

SQL> insert into student5 values('&regno','&name','&major,'&bdate');

SQL> select * from student5;

REGNO NAME MAJOR BDATE

nh101 arun computer 15-Jan-84
nh102 varun computer 25-Feb-84
nh103 vikram computer 11-Dec-84
nh104 suman electrical 12-Dec-84
nh105 simran electrical 1-Dec-84

SQL> insert into course5 values(&course,'&cname','&dept');

SQL> select * from course5;

COURSE_NO CNAME DEPT

1 mca cs
2 bca cs
3 bsc cs
4 mba manag
5 ld ec
6 bca cs
7 dev cs

SQL> insert into enroll5 values('&regno',&course,&sem,&marks);

SQL> select * from enroll5;

REGNO COURSE_NO SEM MARKS

nh101 1 5 98
nh102 2 3 48
nh103 3 4 58
nh104 4 4 78
nh105 5 3 88

9|P ag e
2019-20 19MCAL38 1NH18MCA39

SQL> insert into text5 values(&book_isbn,'&book_title','&publisher','&author');

SQL> select * from text5;

BOOK_ISBN BOOK_TITLE PUBLISHER AUTHOR

1001 inro to ds pearson padma
1002 dbms pearson navathe
1003 java mcgraw herbert
1004 networks pearson faurozan
1005 os mcgraw galvin
1006 c++ mcgraw balaguru
1009 c pearson balaguru
1010 cn mcgraw pearson

SQL> insert into book_adoption5 values(&course,&sem,&book_isbn);

SQL> select * from book_adoption5;

COURSE_NO SEM BOOK_ISBN

1 4 1001
2 4 1002
3 3 1003
4 5 1004
5 1 1005
2 4 1002
1 4 1001
7 4 1004
7 4 1009
1 2 1010
1 3 1006

iii) Demonstrate how you add a new text book to the database and make this book be adopted
by some department.

SQL> insert into text5 values (1007,'cpp','mcgraw','herbert');

1 row created.

SQL>insert into book_adoption5 values (1,3,1007);

1 row created.

10 | P a g e
2019-20 19MCAL38 1NH18MCA39

(iv) Produce a list of text books (include Course #, Book-ISBN, Book-title) in the alphabetical
order for courses offered by the ‘CS’ department that use more than two books

SQL>select c.course_no,cname,t.book_isbn,book_title from course5 c,text5 t,book_adoption5 b

where c.course_no=b.course_no and b.book_isbn=t.book_isbn and dept='cs' and c.course_no
in(select course_no from book_adoption5 group by course_no having count(*) >=2) order by
t.book_title

COURSE_NO CNAME BOOK_ISBN BOOK_TITLE

7 dev 1009 c
1 mca 1006 c++
1 mca 1010 cn
1 mca 1007 cpp
2 bca 1002 dbms
2 bca 1002 dbms
1 mca 1001 inro to ds
1 mca 1001 inro to ds
7 dev 1004 networks

(v) List any department that has all its adopted books published by a specific publisher.

SQL>SELECT DEPT FROM COURSE C,BOOK_ADOPTION B,TEXT T WHERE

C.COURSE_NO=B.COURSE_NO AND T.BOOK_ISBN=B.BOOK_ISBN AND
T.PUBLISHER='pearson';

DEPT
Ec
manag

(vi) Generate suitable reports.

SQL>select c.course_no,c.cname,c.dept,t.book_isbn,t.book_title from course5 c,book_adoption5

b,text5 t where c.course_no=b.course_no and t.book_isbn=t.book_isbn;

COURSE_N CNAM BOOK_IS BOOK_TIT

O E DEPT BN LE
4 mba manag 1002 dbms
2 bca cs 1004 networks
1 mca cs 1005 os
5 ld ec 1006 c++
4 mba manag 1009 c
5 ld ec 1009 c
5 ld ec 1001 inro to ds
2 bca cs 1003 java
11 | P a g e
2019-20 19MCAL38 1NH18MCA39

3 bsc cs 1003 java

7 dev cs 1004 networks

More than 10 rows available. Increase rows selector to view more rows.

Exercise 4: Book Database

The following tables are maintained by a book dealer.

AUTHOR (author-id:int, name:string, city:string, country:string)

PUBLISHER (publisher-id:int, name:string, city:string, country:string)

CATALOG (book-id:int, title:string, author-id:int, publisher-id:int, category-id:int, year:int,

price:int)

CATEGORY (category-id:int, description:string)

ORDER-DETAILS (order-no:int, book-id:int, quantity:int)

i. Create the above tables by properly specifying the primary keys and the foreign keys.
ii. Enter at least five tuples for each relation.
iii. Give the details of the authors who have 2 or more books in the catalog and the price
of the books is greater than the average price of the books in the catalog and the year
of publication is after 2000.
iv. Find the author of the book which has maximum sales.
v. Demonstrate how you increase the price of books published by a specific publisher by
10%.
vi. Generate suitable reports.

i) Create the above tables by properly specifying the primary keys and the foreign
keys.

create table author(author_id number(3) primary key,name varchar2(15),city varchar2(10),country

varchar2(10));

create table publisher(publisher_id number(3) primary key,name varchar2(10),city

varchar2(10),country varchar2(10));

create table category(category_id number(4) primary key,descrip varchar2(10));

create table catalog1(book_id number(4) primary key,title varchar2(10),author_id number(3)

references author(author_id),publisher_id number(3) references

12 | P a g e
2019-20 19MCAL38 1NH18MCA39

publisher(publisher_id),category_id number(4) references category(category_id),year

number(4),price number(5));

create table order_det(order_no number(4) primary key,book_id number(4) references

catalog1(book_id),qty number(4));

ii) Enter at least five tuples for each relation.

insert into author values(&author_id,’&name’,’&city’,’&country’);

select * from author;

AUTHOR_ID NAME CITY COUNTRY

101 Anil palani india
102 Amit kolkata india
103 William shinsui hongkong
104 Akil haridwar india
105 Kotur dharwad india

insert into publisher values(&publisher_id,’&name’,’&city’,’&country’);

select * from publisher;

PUBLISHER_ID NAME CITY COUNTRY

770 Mgraw delhi india
550 pearson london uk
660 Press new york usa
880 united colombo srilanka
990 Tmh mangalore india

insert into catalog values(&book_id,’&title’,&author_id,&publisher_id,&category_id,&year,&price);

select * from catalog;

BOOK_I TITL AUTHOR_ PUBLISHER_ CATEGORY_ YEA PRIC

D E ID ID ID R E
900 cn 101 660 13 2009 660
901 oracle 102 770 14 2008 500
13 | P a g e
2019-20 19MCAL38 1NH18MCA39

905 cloud 101 660 11 2009 700

910 crypto 103 550 14 2009 500
917 dbms 101 770 12 2010 600
902 j2ee 102 550 13 1999 500
904 ada 101 770 13 2006 600

insert into category values(&category_id,’&descrip’);

select * from category;

CATEGORY_ID DESCRIP
11 magazine
12 computer
13 novel
14 poems
15 pgming

Insert into order_det values(&order_no,&book_id,&qty);

Select * from order_det;

ORDER_NO BOOK_ID QTY

1 900 200
2 905 150
3 917 170
4 910 130
5 905 120

iii) Give the details of the authors who have 2 or more books in the catalog and the price of the
books is greater than the average price of the books in the catalog and the year of publication
is after 2000.

select * from author where author_id in(select author_id from catalog1 where (year>2000) and
(catalog1.price > (select avg(price)from catalog1)) group by author_id having count(*)>=2);

AUTHOR_ID NAME CITY COUNTRY

101 anil palani india

14 | P a g e
2019-20 19MCAL38 1NH18MCA39

iv) Find the author of the book which has maximum sales.

select name from author where author_id in (select author_id from catalog1 where book_id in(select
book_id from order_det group by book_id having sum(qty)in (select max(sum(qty)) from order_det
group by book_id)));

NAME
anil

v) Demonstrate how you increase the price of books published by a specific publisher by 10%.

update catalog1 set price=price+(price*0.10) where publisher_id=660;

2 row(s) updated

select * from catalog1;

vi) Generate suitable reports.

select a.author_id,a.name,p.publisher_id,p.name,c.book_id,title from author a,publisher p,catalog1

c,order_det o where a.author_id=c.author_id and p.publisher_id=c.publisher_id and
c.book_id=o.book_id;

BOOK_I TITL AUTHOR_I PUBLISHER_ CATEGORY_ YEA PRIC

D E D ID ID R E
900 cn 101 660 13 2009 726
901 oracle 102 770 14 2008 500
905 cloud 101 660 11 2009 770
910 crypto 103 550 14 2009 500
917 dbms 101 770 12 2010 600
902 j2ee 102 550 13 1999 500
904 ada 101 770 13 2006 600

15 | P a g e
2019-20 19MCAL38 1NH18MCA39

AUTHOR_ID NAME PUBLISHER_ID NAME BOOK_ID TITLE

101 anil 660 Press 900 cn
101 anil 660 Press 905 cloud
101 anil 770 Mgraw 917 dbms
103 william 550 pearson 910 crypto

16 | P a g e
2019-20 19MCAL38 1NH18MCA39

Exercise 5: Bank Database

Consider the following database for a banking enterprise

BRANCH(branch-name:string, branch-city:string, assets:real)

ACCOUNT(accno:int, branch-name:string, balance:real)

DEPOSITOR(customer-name:string, accno:int)

CUSTOMER(customer-name:string, customer-street:string, customer-city:string)

LOAN(loan-number:int, branch-name:string, amount:real)

BORROWER(customer-name:string, loan-number:int)

i. Create the above tables by properly specifying the primary keys and the foreign keys
ii. Enter at least five tuples for each relation
iii.Find all the customers who have at least two accounts at the Main branch.
iv. Find all the customers who have an account at all the branches located in a specific city.
v. Demonstrate how you delete all account tuples at every branch located in a specific
city.
vi. Generate suitable reports.

i) Create the above tables by properly specifying the primary keys and the foreign
keys

create table branch(bname varchar2(10) primary key,bcity varchar2(10),assets number(6));

create table account(accno number(5) primary key,bname varchar2(10) references

branch(bname),balance number(8,2));
create table cust_det(cust_name varchar2(10) primary key,cust_strt varchar2(10),cust_city
varchar2(10));

create table depositor(cust_name varchar2(10) references cust_det(cust_name),accno number(5)

references account(accno));

create table loan(loan_no number(6) primary key,bname varchar2(10) references

branch(bname),amount number(6));

create table borrower(cust_name varchar2(10) references cust_det(cust_name), loan_no

number(6) references loan(loan_no));

17 | P a g e
2019-20 19MCAL38 1NH18MCA39

ii) Enter at least five tuples for each relation

insert into branch values(‘&bname’,’&bcity’,&assets);

select * from branch;

BNAME BCITY ASSETS

peenya bangalore 8765
sahid nagr bhubneswar 9876
col road MP 5643
old town MP 6890
nhce bangalore 6890

insert into account values(&accno,’&bname’,&balance);

select * from account;

ACCNO BNAME BALANCE

19901 peenya 7500
19956 nhce 63999
23856 old town 8567
78231 col road 2000
34518 peenya 6500
25115 peenya 8000

insert into cust_det values(‘&cust_name’,’&cust_strt’,’&cust_city’);

select * from cust_det;

CUST_NAME CUST_STRT CUST_CITY

anil park strt bangalore
amit krpuram blr
ankit kundanhali blr
manish vignan blr
abhinav marathalli blr

18 | P a g e
2019-20 19MCAL38 1NH18MCA39

insert into depositor values(‘&cust_name’,&accno);

select * from depositor;

CUST_NAME ACCNO
abhinav 23856
anil 19901
anil 19956
anil 25115
manish 19956

insert into loan values(&loan_no,’&bname’,&amount);

select * from loan;

LOAN_NO BNAME AMOUNT

36969 peenya 2000
21001 nhce 3477
51234 old town 5986
78911 col road 9950
56091 col road 7600

insert into borrower values(‘&cust_name’,&loan_no);

select * from borrower;

CUST_NAME LOAN_NO
anil 36969
anil 21001
ankit 78911
amit 56091
amit 51234

19 | P a g e
2019-20 19MCAL38 1NH18MCA39

iii) Find all the customers who have at least two accounts at the Main branch.

select cust_name from depositor d, account a where a.accno=d.accno and a.bname='peenya' group by
d.cust_name having count(*)>=2;

CUST_NAME
anil

iv) Find all the customers who have an account at all the branches located in a specific
city

select distinct cust_name from depositor d,account a,branch b where d.accno=a.accno and
b.bname=a.bname and b.bcity='bangalore' group by cust_name having count(distinct
a.bname)>=(select count(distinct bname)from branch where bcity='bangalore');

CUST_NAME
anil

v) Demonstrate how you delete all account tuples at every branch located in a specific city.

delete from depositor where accno in(select accno from account where bname in(select bname
from branch where bcity='bangalore'));

2 row(s) deleted.

delete from account where bname in(select bname from branch where bcity='bangalore');

2 row(s) deleted.

vi) Generate suitable reports.

select a.accno,c.cust_name,cust_strt,a.balance from cust_det c,account a,depositor d where

a.accno=d.accno and c.cust_name=d.cust_name;

ACCNO CUST_NAME CUST_STRT BALANCE

23856 Abhinav marathalli 8567

20 | P a g e