CSE250 Database Management System
Winter Semester 2022
Assignment-2
--------------------------------------------------
(1) Bank-Schema:
create table branch(branch_name char(50) primary key,
branch_city char(50),
assets char(200));
create table customer(customer_name char(50) primary key,
customer_street char(200),
customer_city char(30));
create table account(account_number int primary key,
branch_name char(50) references branch(branch_name),
balance int);
create table loan(loan_number int primary key,
branch_name char(50) references branch(branch_name),
amount int);
create table depositor(customer_name char(50) references customer(customer_name),
account_number int references account(account_number));
create table borrower(customer_name char(50) references customer(customer_name),
loan_number int references loan(loan_number));
--------------------------------------------------
Solution of Exercises:
Write select statements to answer the following questions.
1. Display names of the customers who have account in the city where they live.
Soln: begin
insert into customer values('abc','abcstreet','Snagar');
insert into customer values('pqr','pqrstreet','Rajkot');
insert into customer values('xyz','xyzstreet','Ahmedabad');
end;
/
begin
insert into branch values('SBI','Snagar','Gold');
insert into branch values('BOB','Rajkot','Platinum');
insert into branch values('ADC','Ahmedabad','Silver');
end;
/
select customer.customer_name from customer,branch where
customer.customer_city=branch.branch_city;
select customer.customer_name from branch, account, depositor, customer where
customer.customer_name = depositor.customer_name and depositor.account_no =
account.account_no and account.branch_name = branch.branch_name and
customer.customer_city = branch.branch_city;
2. Display details of customers who are not borrower.
Soln: begin
insert into loan values('123','SBI','2000');
insert into loan values('234','BOB','3000');
insert into loan values('345','ADC','7000');
end;
/
ALTER TABLE loan drop constraint C008325;
begin
insert into borrower values('abc','123');
insert into borrower values('pqr','234');
insert into borrower values('xyz','345');
end;
/
select * from customer where not exists (select * from borrower);
3. Display customer details who have taken loan more than 3 times.
Soln: select * from customer where customer_name in(select customer_name from
customer group by customer_name having count(customer_name)>3);
4. Display pairs of customers who live in the same city.
Soln: select customer.s1,customer.s2 from customer,customer where customer.s1(city)
= customer.s2(city);
SELECT customer.cust_name,
salesman.name, salesman.city
FROM salesman, customer
WHERE salesman.city = customer.city;
SELECT customer.*
FROM table customer
WHERE EXISTS (SELECT 1 FROM table t1 WHERE t1.city = t.city AND t1.name <> t.name);
select c1.customer_name, c2.customer_name from customer c1, customer c2
where c1.customer_city = c2.customer_city and c1.customer_name <>
c2.customer_name; (....)
5. Display details of the customers whose name is the longest.
Soln: select * from customer where length(trim(customer_name))in (select
max(length(trim(customer_name))) from customer);
6. Display details of borrowers who have taken loan from 'Ahmedabad' branch.
Soln: select customer_name,loan_number from borrower,customer,loan where
branch.branch_city = 'Ahmedabad'; (***)
select * from borrower where loan_id in (select loan_number
from loan where branch_name in(select branch_name from branch where branch_city =
'Ahmedabad'));
7. Display details of borrowers who have taken total loan of more than Rs.
50,00,000.
Soln: select customer_name,sum(loan.amount) from borrower, loan group by
customer_name having sum(loan.amount)>5000000;
8. Display total assets of all the branches.
Soln: select sum(assets) from branch;
9. Display details of customers who are depositors.
Soln: selct * from customer where customer_name in(select customer_name from
depositor);
10. Display left outer join of customer and depositor table.
Soln: select
customer.customer_name,customer.customer_street,customer.customer_city,depositor.ac
count_number
from customer left outer join depositor on customer.customer_name =
depositor.customer_name;
(****)(correct but meaning)
11. Display right outer join of account and depositor table.
Soln: select depositor.customer_name, account.account_number,account.balance
from account right outer join depositor on depositor.account_number =
account.account_number;
12. Display account number in which balance is minimum.
Soln: select account_number from account where balance = (select min(balance) from
account);
13. Display account number in which balance is second highest.
Soln: select name from student group by name,tot_cred having tot_cred in (select
max(tot_cred) from student where tot_cred not in (select max(tot_cred) from
student));
14. Display branch name, branch city, account number, customer name and customer
city in
ascending order of customer city and descending order of branch city.
Soln: select
branch.branch_name,branch.branch_city,account.account_number,depositor.customer_nam
e,customer.customer_city from branch inner join account on
account.branch_name=branch.branch_name inner join depositor on
depositor.account_number=account.account_number inner join customer on
customer.customer_name=depositor.customer_name order by
customer.customer_city,branch.branch_city desc;
15. Display total no. of customers in each city.
Soln: select customer_city, count(*) from customer group by customer_city;
16. Display city wise total assets in descending order of total assets.
Soln: select branch_city, sum(assets) from branch group by branch_city order by
sum(assets) desc;
17. Display borrower names with total loan amount taken.
Soln: select customer_name.borrower,sum(amount) from loan;
18. Display details of the customers who are depositors as well as borrower.
Soln: select * from customer where customer_name in(select customer_name from
borrower where customer_name in (select customer_name from depositor));
19. Display inner join of customer, borrower, loan and branch.
Soln: select * from customer
inner join borrower on customer.customer_name = borrower.customer_name
inner join loan on loan.loan_number = borrower.loan_id
inner join branch on loan.branch_name = branch.branch_name;
20. Display union of branch and account. (Use “union” operator)
Soln: select branch_name from branch union branch_name from account;
-----------------------------------------------------------------------------------
-------------------------------------------------------------
SCHEMA FOR UNIVERSITY DATABASE:
create table classroom(building char(50),
room_no int,
capacity int,
primary key (building, room_no));
create table department(dept_name char(50) primary key,
building char(50),
budget int check(budget<=10000));
create table instructor(ID char(20) primary key,
name char(70),
dept_name char(50) references department(dept_name),
salary int check);
ALTER TABLE instructor ADD CONSTRAINT SalaryCheck CHECK(salary>0);
create table course(course_id int primary key,
title char(50),
dept_name char(50) references department(dept_name),
credits float check(1.5<=credits and credits<=4.5));
create table prereq(course_id int references course(course_id),
prereq_id int references course(course_id),
primary key(course_id,prereq_id));
create table student(ID char(20) primary key,
name char(50),
dept_name char(50) references department(dept_name),
tot_cred dec);
create table advisor(s_id char(20) references student(ID),
i_id char(20) references instructor(ID),
primary key(s_id));
create table section(course_id int references course(course_id),
sec_id int,
semester int,
year int,
building char(50),
room_no int ,
time_slot_id int,
foreign key(building,room_no) references
classroom(building,room_no),
primary key(course_id,sec_id,semester,year));
create table time_slot(time_slot_id int,
day char(10),
start_time timestamp,
end_time timestamp,
primary key(time_slot_id,start_time));
create table teaches(ID char(20),
course_id int,
sec_id int,
semester int,
year int,
foreign key(course_id,sec_id,semester,year) references
section(course_id,sec_id,semester,year),
primary key(course_id,sec_id,semester,year));
create table takes(ID char(20) references student(ID),
course_id int,
sec_id int,
semester int,
year int,
grade char(2),
foreign key(course_id,sec_id,semester,year) references
section(course_id,sec_id,semester,year),
primary key(ID,course_id,sec_id,semester,year));
-----------------------------------------------------------------------------------
-------------------------------------------------
Write DML (Insert/Update/Delete/Select) statements to answer the following
questions.
1. Create a new course “CS-001”, titled “Weekly Seminar”, with 2 credits.
Soln: insert into course(course_id,title,credits) values ('CS-001','Weekly
Seminar','2');
2. Create a section of the course “CS-001” in Monsoon 2019, with sec_id of 1.
Soln: insert into section(course_id,sec_id,year) values ('CS-001','1','2019');
3. Enroll every student in the Comp. Sci. department in section 1.
Soln: insert into takes
select ID, 'CS-001', '1', 'Autumn', 2009, null
from student
where dept_name = 'Computer Science';
4. Delete enrollment in section 1 where student name is Chiral.
Soln: delete from takes where sec_id = '1' and ID in (select ID from student where
name='Chiral');
5. Delete all “takes” tuples corresponding to any section of any course with the
word
“database” as a part of the title, ignore case when matching the word with the
title.
Soln: delete from (select * from takes natural join course) where
lower(trim(title))='database';
6. Find courses in which students can enroll themselves without attending any
course.
Soln: select course_id from prereq group by course_id,prereq_id having
prereq_id=null;
7. Find courses in which students have to complete at least two courses.
Soln: select course_id from prereq group by course_id,prereq_id having
count(prereq_id)>=2;
8. Display course names with total no. of prerequisites.
Soln: select course_id,count(prereq_id) from prereq group by course_id,prereq_id;
9. Display names of courses offered by the SEAS department.
Soln: select course_id from course where dept_name='SEAS';
10. Find faculty names who are teaching “data” based courses.
Soln: select name from instructor natural join teaches where course_id='%data';
11. Display total no. of courses offered in the Monsoon Semester 2020.
Soln: select count(course_id) from section where semester='Monsoon' and
year='2020';
12. Find semester name in which highest number of courses were offered.
Soln: select semester from section where course_id in (select course_id from
section where course_id in select(max(count(course_id) from section);(*******)
13. Find names of courses offered on Saturdays in Winter semester 2022.
Soln: select course_id from (section natural join time_slot) where day='Saturday'
and semester='Winter' and year='2022';
14. Display name of the student who has earned second highest credits.
Soln: select name from student group by name,tot_cred having tot_cred in(select
max(tot_cred) from student where tot_cred not in (select max(tot_cred) from
student));
15. Find course names with less than 10 enrolments for the Winter semester 2022.
Soln: select course_id from takes where course_id in (select course_id from takes
group by course_id,id,semester,year having count(id)<10 and semester='Winter' and
year='2022');
select
16. Find semester wise and course wise total enrolments.
Soln: select semester,count(id),course_id from takes group by semester,course_id;
17. Find building names with highest no. of rooms.
Soln: select building from section where room_no in (select room_no from section
where room_no=(count(room_no)); (*****)
select max(count(room_no)) r from section where r in (select
building,count(room_no) from section group by building);
18. Find least capacity room.
Soln: select room_no from classroom where capacity in(select capacity from
classroom group by min(capacity));(****)
select room_no from classroom group by capacity,room_no having
capacity=min(capacity);
19. Display faculty name who has/is taught/teaching average 4 sections in Monsoon
semester 2021.
Soln:
select name from instructor natural join section group by name,section having
avg(count(instructor_id))=4; (***)
20. Change department of Gaurav Goswami and make it same as the department of
Aditya Vaishya.
Soln:
update department set dept_name= (select dept_name from instructor where
name='Aditya Vaishya') where dept_name=(select dept_name from instructor where
name='Gaurav Goswami');
21. Display time slot details of the course Database Management System.
Soln: select time_slot_id,start_time,end_time from section natural join time_slot
where course_id='Database Management System';
22. Find room numbers which have capacity more than average capacity of all the
rooms.
Soln: select room_no from classroom group by capacity,room_no having
capacity>avg(capacity);
23. Find course names which were offered in the years 2020 and 2021.
Soln: select course_id from course natural join section where year=2020 or
year=2021;
24. Find department names where budget is spent is more than allocated budget.
Soln: select dept_name from department where (select salary from instructor) where
in sum(salary)>budget;(*****)
select department.dept_name from department, instructor group by
department.dept_name,department.budget,instructor.salary,instructor.dept_name
having department.dept_name=instructor.dept_name and budget<sum(salary)
select salary,dept_name from department where salary > (select budget from
instructor);(****)
select dept_name from department natural join instructor where salary>budget;
select department.dept_name from department, instructor group by
department.dept_name,department.budget,instructor.salary,instructor.dept_name
having department.dept_name=instructor.dept_name and budget<sum(salary);
25. Find classrooms which are available on Wednesdays during 1 pm to 2:30 pm.
Soln: select room_no from section natural join time_slot where day='Wednesday' and
start_time='1' and end_time='2.30';
(*****)
