Application Question-1
create table person(driver_id varchar(10),name varchar(10),address varchar(10),primary
key(driver_id));
create table car(regno varchar(10),model varchar(10),year int,primary key(regno));
create table accident(report_number int,accd_date date,location varchar(10),primary
key(report_number));
create table owns(driver_id varchar(10),regno varchar(10),primary
key(driver_id,regno),foreign key(driver_id) references person(driver_id),foreign key(regno)
references car(regno));
create table participated(driver_id varchar(10),regno varchar(10),report_number
int,damage_amount int,primary key(driver_id,regno,report_number),foreign key(driver_id)
references person(driver_id),foreign key(regno) references car(regno),foreign
key(report_number) references accident(report_number));
desc person
desc car
desc participated
desc owns
desc accident
insert into person values('1','Ajay','Delhi');
insert into person values('2','Jay','Pune');
insert into person values('3','Jaya','Pune');
insert into person values('4','Amit','Mumbai');
select * from person;
insert into car values('123','santro',2020);
insert into car values('234','maruti',2021);
insert into car values('345','honda',2021);
insert into car values('456','kia',2022);
select * from car;
insert into accident values(111,'01-Jan-2020','Delhi');
insert into accident values(222,'01-Feb-2021','Pune');
insert into accident values(333,'01-Mar-2021','Mumbai');
insert into accident values(444,'01-May-2022','Delhi');
select * from accident;
insert into owns values('1','123');
insert into owns values('2','234');
insert into owns values('3','345');
insert into owns values('4','456');
select * from owns;
insert into participated values('1','123',111,1000);
insert into participated values('2','234',222,2000);
insert into participated values('3','345',333,3000);
insert into participated values('4','456',444,400);
select * from participated;
update participated set damage_amount=25000 where report_number=222 and
regno='234';
select * from participated;
insert into accident values(555,'10-May-2022','Gurugram');
select * from accident;
select count(distinct o.driver_id) as People from owns o,participated p,accident a where
a.accd_date like
'%22' and o.regno=p.regno and p.report_number=a.report_number;
select count(*) as Totalcars from car c,participated p where c.regno=p.regno and
c.model='santro';
Application question 2
create table student(stud_no number(5) primary key,stud_name varchar2(15));
desc student;
insert into student values(111,'vrishank');
insert into student values(112,'vikarn');
insert into student values(113,'sneh');
insert into student values(114,'medhansh');
insert into student values(115,'sonali');
select * from student;
create table membership(mem_no number(5) primary key,stud_no number(5) references
student(stud_no));
desc membership;
insert into membership values(222,111);
insert into membership values(333,112);
insert into membership values(444,113);
insert into membership values(555,114);
insert into membership values(666,115);
select * from membership;
create table book(book_no number(5) primary key,book_name varchar2(20),author
varchar2(20));
desc book;
insert into book values(1212,'dbms','navathe');
insert into book values(1313,'CA','morris');
insert into book values(1414,'fcit','singh');
insert into book values(1515,'OS','galvin');
insert into book values(1616,'dbms','korth');
select * from book;
create table lss_rec(iss_no number primary key,iss_date date,mem_no number(5)
references membership(mem_no),book_no number(5) references book(book_no));
desc lss_rec;
insert into lss_rec values (45,'01-Jan-2022', 222, 1212);
insert into lss_rec values (46,'25-Jan-2022', 333, 1313);
insert into lss_rec values (47,'10-Feb-2022', 222, 1414);
insert into lss_rec values (48,'15-Feb-2022', 444, 1515);
insert into lss_rec values (49,'01-Mar-2022', 555, 1616);
insert into lss_rec values (50,'10-Jul-2022', 555, 1616);
select * from lss_rec;
-------------------
select s.stud_name, mem.mem_no from student s, membership mem where
mem.stud_no=s.stud_no;
SELECT student.stud_name,membership.mem_no FROM student inner join
membership ON student.stud_no=membership.stud_no;
--------------------
select iss.iss_no, s.stud_name, b.book_name from lss_rec iss, membership mem, student s,
book b
where iss.iss_date='10-Jul-2022' and s.stud_no=mem.stud_no and
mem.mem_no=iss.mem_no and iss.book_no=b.book_no;
select lss_rec.iss_no, student.stud_name,book.book_name from (((lss_rec inner join book
on
lss_rec.book_no=book.book_no) inner join membership on
lss_rec.mem_no=membership.mem_no)
inner join student on membership.stud_no=student.stud_no) where lss_rec.iss_date='10-
Jul-2022';
select s.stud_no, s.stud_name from lss_rec iss, membership mem, student s, book b
where b.author='galvin' and s.stud_no=mem.stud_no and mem.mem_no=iss.mem_no and
iss.book_no=b.book_no;
select * from student where stud_no in(select stud_no from membership where mem_no in
(select mem_no from lss_rec where book_no in(select book_no from book where
author='galvin')));
select s.stud_no, count(iss.iss_no) from lss_rec iss, membership mem, student s, book b
where s.stud_no=mem.stud_no and mem.mem_no=iss.mem_no and
iss.book_no=b.book_no group by s.stud_no;
select b.book_name from lss_rec iss, membership mem, student s, book b
where s.stud_no=113 and s.stud_no=mem.stud_no and mem.mem_no=iss.mem_no and
iss.book_no=b.book_no;
delete from lss_rec where iss_date='10-jul-2022';
create view view31 as
select s.stud_name, b.book_name, iss.iss_no, iss.iss_date from student s, book b, lss_rec iss,
membership mem
where s.stud_no=mem.stud_no and mem.mem_no=iss.mem_no and
iss.book_no=b.book_no;
select * from view31;
