Slip no1:Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Emp(eno ,ename ,designation ,salary, Date_Of_Joining)
Dept(dno,dname ,loc)
The relationship between Dept & Emp is one-to-many. Constraints: - Primary Key, ename should not be NULL, salary must be greater than 0.
Soluation:-----------
SQL> create table emp(eno number primary key,ename varchar(20),designation varchar(20),salary number,date_of_joining varchar(20));
Table created.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NOT NULL NUMBER
ENAME VARCHAR2(20)
DESIGNATION VARCHAR2(20)
SALARY NUMBER
DATE_OF_JOINING VARCHAR2(20)
SQL> insert into emp(eno,ename,designation,salary,date_of_joining)
2 values(1,'Mr. Advait','Assistant',54000,'23/03/2002');
1 row created.
SQL> insert into emp(eno,ename,designation,salary,date_of_joining)
2 values(2,'Mr. Roy','ceo',50000,'15/06/2019');
1 row created.
SQL> insert into emp(eno,ename,designation,salary,date_of_joining)
2 values(3,'Mr. Abhay','manager',60000,'10/06/2013');
1 row created.
SQL> insert into emp(eno,ename,designation,salary,date_of_joining)
2 values(4,'Mr. Raghav','manager',420000,'01/03/2003');
1 row created.
SQL> select * from emp;
ENO ENAME DESIGNATION SALARY
---------- -------------------- -------------------- ----------
DATE_OF_JOINING PHONE_NO
-------------------- ----------
1 Mr. Advait Assistant 54000
23/03/2002
2 Mr. Roy ceo 50000
15/06/2019
3 Mr. Abhay manager 60000
10/06/2013
ENO ENAME DESIGNATION SALARY
---------- -------------------- -------------------- ----------
DATE_OF_JOINING PHONE_NO
-------------------- ----------
4 Mr. Raghav manager 420000
01/03/2003
SQL> create table dept(dno number primary key,dname varchar(20),loc varchar(10),eno references emp);
Table created.
SQL> desc dept
Name Null? Type
----------------------------------------- -------- ----------------------------
DNO NOT NULL NUMBER
DNAME VARCHAR2(20)
LOC VARCHAR2(10)
ENO NUMBER
SQL> insert into dept(dno,dname,loc,eno)
2 values(101,'computer','pune',1);
1 row created.
SQL> insert into dept(dno,dname,loc,eno)
2 values(102,'computer science','mumbai',2);
1 row created.
SQL> insert into dept(dno,dname,loc,eno)
2 values(103,'Quqlity','mumbai',3);
1 row created.
SQL>
SQL> insert into dept(dno,dname,loc,eno)
2 values(104,'Account','mumbai',4);
1 row created.
SQL> select * from dept;
DNO DNAME LOC ENO
---------- -------------------- ---------- ----------
101 computer pune 1
102 computer science mumbai 2
103 Quqlity mumbai 3
104 Account mumbai 4
Q.3 Consider the above tables and Execute the following queries:
1. Add column phone_No into Emp table with data type int.
SQL> alter table emp
2 add phone_no int;
Table altered.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NOT NULL NUMBER
ENAME VARCHAR2(20)
DESIGNATION VARCHAR2(20)
SALARY NUMBER
DATE_OF_JOINING VARCHAR2(20)
PHONE_NO NUMBER(38)
2. Delete the details of Employee whose designation is ‘Manager’.
SQL> Delete from emp
2 where designation='manager';
2 rows deleted.
Q4. Consider the above database and execute the following queries: [25 Marks]
1. Display the count of employees department wise.
SQL> select count(emp.eno),dname from emp,dept
2 where emp.eno=dept.eno
3 group by dname;
COUNT(EMP.ENO) DNAME
-------------- --------------------
1 computer science
1 Account
1 computer
1 Quqlity
2. Display the name of employee who is ‘Manager’ of “Account Department”.
SQL> select ename from emp,dept
2 where emp.eno=dept.eno
3 and designation='manager' and dname='Account';
ENAME
--------------------
Mr. Raghav
Mr. Abhay
3. Display the name of department whose location is “Pune” and “Mr. Advait” is working in it
SQL> select dname from emp,dept
2 where emp.eno=dept.eno
3 and loc='pune' and ename='Mr. Advait';
DNAME
--------------------
Computer
4. Display the names of employees whose salary is greater than 50000 and department is “Quality”.
SQL> select ename from emp,dept
2 where emp.eno=dept.eno
3 and salary>50000 and dname='Quqlity';
ENAME
--------------------
Mr. Abhay
5. Update Dateofjoining of employee to ‘15/06/2019’ whose department is ‘computer science’ and name is “Mr. Roy’.
update emp set date_of_joining='15/06/2019'
where ename='Mr.Roy' and dno in(select dno from dept where dname='computer science');
slip no:2--Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Sales_order (ordNo, ordDate)
Client (clientNo, ClientName, addr)
The relationship between Client & Sales_order is one-to-many. Constraints: - Primary Key, ordDate should not be NULL
SQL> create table client(cno varchar(10) primary key,cname varchar(20),addr varchar(20));
Table created.
SQL> desc client
Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NOT NULL VARCHAR2(10)
CNAME VARCHAR2(20)
ADDR VARCHAR2(20)
SQL> insert into client values('CN001','Abhay','Pune');
1 row created.
SQL> insert into client values('CN002','Patil','Pune');
1 row created.
SQL> insert into client values('CN003','Mr.Roy','Pimpri');
1 row created.
SQL> insert into client values('CN004','Raj','Mumbai');
1 row created.
SQL> select * from client;
CNO CNAME ADDR
---------- -------------------- --------------------
CN001 Abhay Pune
CN002 Patil Pune
CN003 Mr.Roy Pimpri
CN004 Raj Mumbai
SQL> create table sales_order(ordno int primary key,ordDate varchar(23) not null,cno varchar(10) references client on delete cascade);
Table created.
SQL> desc sales_order;
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDNO NOT NULL NUMBER(38)
ORDDATE NOT NULL VARCHAR2(23)
CNO VARCHAR2(10)
SQL> insert into sales_order values(1,'23/06/2015','CN001');
1 row created.
SQL> insert into sales_order values(2,'09/03/2019','CN002');
1 row created.
SQL> insert into sales_order values(3,'09/08/2009','CN004');
1 row created.
SQL> insert into sales_order values(4,'09/08/2019','CN002');
1 row created.
SQL> select * from sales_order;
ORDNO ORDDATE CNO
---------- ----------------------- ----------
1 23/06/2015 CN001
2 09/03/2019 CN002
3 09/08/2009 CN004
4 09/08/2019 CN002
Q.3Consider the above tables and execute the following queries:
1. Add column amount into Sales_order table with data type int.
SQL> alter table sales_order
2 add amount int;
Table altered.
SQL> desc sales_order;
Name Null? Type
----------------------------------------- -------- ----------------------------
ORDNO NOT NULL NUMBER(38)
ORDDATE NOT NULL VARCHAR2(23)
CNO VARCHAR2(10)
AMOUNT NUMBER(38)
2. Delete the details of the clients whose names start with ‘A’ character.
SQL> delete from client
2 where cname like'A%';
1 row deleted.
SQL> select * from client;
CNO CNAME ADDR
---------- -------------------- --------------------
CN002 Patil Pune
CN003 Mr.Roy Pimpri
CN004 Raj Mumbai
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Delete sales order details of client whose name is “Patil” and order date is “09/08/2019”.
SQL> delete from sales_order
2 where ordDate='09/08/2019'
3 and cno in(select cno from client where cname='Patil');
1 row deleted.
SQL> select * from sales_order;
ORDNO ORDDATE CNO AMOUNT
---------- ----------------------- ---------- ----------
2 09/03/2019 CN002 100
3 09/08/2009 CN004 100
2)Change order date of client_No ‘CN001’ ‘18/03/2019’.
SQL> update sales_order
2 set ordDate='18/03/2019'
3 where cno='CN001';
0 rows updated.
3) Delete all sales_record having order date is before ‘10 /02/2018’.
SQL> delete from sales_order
2 where ordDate<'20/10/2019';
2 rows deleted.
4)Display date wise sales_order given by clients.
SQL> select ordDate,ordno,amount,cno from sales_order
2 order by ordDate;
no rows selected
5) Update the address of client to “Pimpri” whose name is ‘Mr. Roy’
SQL> update client
2 set addr='pimpri'
3 where cname='Mr.Roy';
1 row updated.
Slip no-3:-Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Hospital (hno ,hname , city, Est_year, addr)
Doctor (dno , dname , addr, Speciality)
The relationship between Hospital and Doctor is one - to – Many Constraints: - Primary Key, Est_year should be greater than 1990.
SQL> create table hospital(hno int primary key,hname varchar(20),city varchar(20),est_year numeric(4) check(est_year>1990),addr varchar(20));
Table created.
SQL> desc hospital;
Name Null? Type
----------------------------------------- -------- ----------------------------
HNO NOT NULL NUMBER(38)
HNAME VARCHAR2(20)
CITY VARCHAR2(20)
EST_YEAR NUMBER(4)
ADDR VARCHAR2(20)
SQL> insert into hospital values(101,'balaji','pune',1993,'kharadi road');
1 row created.
SQL> insert into hospital values(103,'vedant','mumbai',1993,'dharavi');
1 row created.
SQL> insert into hospital values(104,'ruby','pimpri',1993,'kharadi road');
1 row created.
SQL> insert into hospital values(105,'birla','chinchwad',1993,'tyr');
1 row created.
SQL> insert into hospital values(106,'qw','pune',1993,'kalptaru');
1 row created.
SQL> select * from hospital;
HNO HNAME CITY EST_YEAR
---------- -------------------- -------------------- ----------
ADDR
--------------------
101 balaji pune 1993
kharadi road
103 vedant mumbai 1993
dharavi
104 ruby pimpri 1993
kharadi road
HNO HNAME CITY EST_YEAR
---------- -------------------- -------------------- ----------
ADDR
--------------------
105 birla chinchwad 1993
tyr
106 qw pune 1993
kalptaru
SQL> create table doctor(dno int primary key,dname varchar(20),addr1 varchar(20),speciality varchar(20),hno int references hospital on delete cascade);
Table created.
SQL> desc doctor;
Name Null? Type
----------------------------------------- -------- ----------------------------
DNO NOT NULL NUMBER(38)
DNAME VARCHAR2(20)
ADDR VARCHAR2(20)
SPECIALITY VARCHAR2(20)
HNO NUMBER(38)
SQL> insert into doctor values(1,'dr.joshi','pune','skin',104);
1 row created.
SQL> insert into doctor values(2,'dr.mane','nashik','surgeon',103);
1 row created.
SQL> insert into doctor values(3,'dr.patil','pune','gynecologist',101);
1 row created.
SQL> insert into doctor values(4,'dr.Raghav','pune','skin',105);
1 row created.
SQL> insert into doctor values(5,'dr.Abhay','mumbai','internist',104);
1 row created.
SQL> insert into doctor values(6,'dr.joshi','pune','surgeon',106);
1 row created.
SQL> insert into doctor values(7,'dr.Riya','pune','skin',103);
1 row created.
SQL> insert into doctor values(8,'dr.Gawade','pune','head',104);
1 row created.
SQL> select * from doctor;
DNO DNAME ADDR SPECIALITY
---------- -------------------- -------------------- --------------------
HNO
----------
1 dr.joshi pune skin
104
2 dr.mane nashik surgeon
103
3 dr.patil pune gynecologist
101
DNO DNAME ADDR SPECIALITY
---------- -------------------- -------------------- --------------------
HNO
----------
4 dr.Raghav pune skin
105
5 dr.Abhay mumbai internist
104
6 dr.joshi pune surgeon
106
DNO DNAME ADDR SPECIALITY
---------- -------------------- -------------------- --------------------
HNO
----------
7 dr.Riya pune skin
103
8 dr.Gawade pune head
104
8 rows selected.
Q.3Consider the above tables and execute the following queries:
1. Delete addr column from Hospital table.
SQL> alter table hospital
2 drop column addr1;
2. Display doctor name, Hospital name and specialty of doctors from “Pune City” .
SQL> select dname,hname,speciality from doctor,hospital
2 where doctor.hno=hospital.hno
3 and city='pune';
DNAME HNAME SPECIALITY
-------------------- -------------------- --------------------
dr.patil balaji gynecologist
dr.joshi qw surgeon
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Display the names of the hospitals which are located at “Pimpri” city.
SQL> select hname from hospital,doctor
2 where doctor.hno=hospital.hno
3 and city='pimpri';
HNAME
--------------------
ruby
ruby
ruby
2. Display the names of doctors who are working in “Birla” Hospital and city name is “Chinchwad”
SQL> select dname from doctor,hospital
2 where doctor.hno=hospital.hno
3 and hname='birla' and city='chinchwad';
DNAME
--------------------
dr.Raghav
3. Display the specialty of the doctors who are working in “Ruby” hospital.
SQL> select speciality from hospital,doctor
2 where doctor.hno=hospital.hno
3 and hname='ruby';
SPECIALITY
--------------------
skin
internist
head
4. Give the count of doctor’s hospital wise which are located at “Pimple Gurav”.
SQL> select hname,count(dno) from doctor,hospital
2 where doctor.hno=hospital.hno
3 and addr='kharadi road'
4 group by hname;
HNAME COUNT(DNO)
-------------------- ----------
ruby 3
balaji 1
5. Update an address of Doctor to “Pimpri” whose hospital is “Ruby clinic”
SQL> update doctor set addr1='pimpri'
2 where hno in(select hno from hospital where hname='ruby');
3 rows updated.
Slip no-4:Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Patient (PCode, Name, Addr, Disease)
Bed (Bed_No, RoomNo, loc)
Relationship: - There is one-one relationship between patient and bed. Constraints: - Primary key, RoomNo must be greater than Bed_No, Addr should not be null.
SQL> create table patient(pcode int primary key,name varchar(20) not null,addr varchar(20),disease varchar(10));
Table created.
SQL> desc patient;
Name Null? Type
----------------------------------------- -------- ----------------------------
PCODE NOT NULL NUMBER(38)
NAME NOT NULL VARCHAR2(20)
ADDR VARCHAR2(20)
DISEASE VARCHAR2(10)
SQL> insert into patient values(11,'Raghav','pimple gurav','listeria');
1 row created.
SQL> insert into patient values(12,'Abhay','pune','norovirus');
1 row created.
SQL> insert into patient values(13,'Mr.Roy','mumbai','cholera');
1 row created.
SQL> insert into patient values(14,'Sachin','pimple gurav','dengue');
1 row created.
SQL> insert into patient values(15,'Priya','nashik','listeria');
1 row created.
SQL> select * from patient;
PCODE NAME ADDR DISEASE
---------- -------------------- -------------------- ----------
11 Raghav pimple gurav listeria
12 Abhay pune norovirus
13 Mr.Roy mumbai cholera
14 Sachin pimple gurav dengue
15 Priya nashik listeria
SQL> create table bed(bno int primary key,rno int not null,loc varchar(10) not null,pcode int references patient on delete cascade);
Table created.
SQL> desc bed;
Name Null? Type
----------------------------------------- -------- ----------------------------
BNO NOT NULL NUMBER(38)
RNO NOT NULL NUMBER(38)
LOC NOT NULL VARCHAR2(10)
PCODE NUMBER(38)
SQL> insert into bed values(1,105,'pune',11);
1 row created.
SQL> insert into bed values(2,102,'2nd floor',12);
1 row created.
SQL> insert into bed values(3,103,'4th floor',13);
1 row created.
SQL> insert into bed values(4,104,'1st floor',11);
1 row created.
SQL> insert into bed values(5,105,'3rd floor',14);
1 row created.
SQL> insert into bed values(6,106,'2nd floor',15);
1 row created.
SQL> select * from bed;
BNO RNO LOC PCODE
---------- ---------- ---------- ----------
1 105 pune 11
2 102 2nd floor 12
3 103 4th floor 13
4 104 1st floor 11
5 105 3rd floor 14
6 106 2nd floor 15
6 rows selected.
Q.3Consider the above tables and execute the following queries:
1. Display the details of patients who are from “Pimple Gurav”
SQL> select * from patient
2 where addr='pimple gurav';
PCODE NAME ADDR DISEASE
---------- -------------------- -------------------- ----------
11 Raghav pimple gurav listeria
14 Sachin pimple gurav dengue
2. Delete the details of patient whose Bed_No is 1 and RoomNo is 105.
SQL> select * from patient,bed
2 where patient.pcode=bed.pcode
3 and bno=1 and rno=105;
PCODE NAME ADDR DISEASE BNO
---------- -------------------- -------------------- ---------- ----------
RNO LOC PCODE
---------- ---------- ----------
11 Raghav pimple gurav listeria 1
105 pune 11
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Display the count of patient room wise.
SQL> select count(patient.pcode) from patient,bed
2 where patient.pcode=bed.pcode
3 group by rno;
COUNT(PATIENT.PCODE)
--------------------
1
2
1
1
1
2. Display the names of patients who are admitted in room no 101.
SQL> select name from patient,bed
2 where patient.pcode=bed.pcode
3 and rno=102;
NAME
--------------------
Abhay
3. Display the disease of patient whose bed_No is 1
SQL> select disease from patient,bed
2 where patient.pcode=bed.pcode
3 and bno=1;
DISEASE
----------
Listeria
4. Display the room_no and bed_no of patient whose name is “Mr Roy”
SQL> select rno,bno from patient,bed
2 where patient.pcode=bed.pcode
3 and name='Mr.Roy';
RNO BNO
---------- ----------
103 3
5. Give the details of Patient who is admitted on 2nd flr in roomno 102.
SQL> select * from patient,bed
2 where patient.pcode=bed.pcode
3 and loc='2nd floor' and rno=102;
PCODE NAME ADDR DISEASE BNO
---------- -------------------- -------------------- ---------- ----------
RNO LOC PCODE
---------- ---------- ----------
12 Abhay pune norovirus 2
102 2nd floor 12
Slip no-5:Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Customer (cust_no, cust_name, address, city)
Loan (loan_no, loan_amt)
The relationship between Customer and Loan is Many to Many Constraint: Primary key, loan_amt should be > 0.
Connected.
SQL> create table customer(cno int primary key,cname varchar(20) not null,addr varchar(20),city varchar(10));
Table created.
SQL> desc customer
Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NOT NULL NUMBER(38)
CNAME NOT NULL VARCHAR2(20)
ADDR VARCHAR2(20)
CITY VARCHAR2(10)
SQL> insert into customer values(101,'Dhiraj','kharadi','pune');
1 row created.
SQL> insert into customer values(102,'Patil','kalptaru','pimpri');
1 row created.
SQL> insert into customer values(103,'Abhay','west','pimpri');
1 row created.
SQL> insert into customer values(104,'Raghav','rt','nashik');
1 row created.
SQL> insert into customer values(105,'Dhanu','bvh','pune');
1 row created.
SQL> select * from customer;
CNO CNAME ADDR CITY
---------- -------------------- -------------------- ----------
101 Dhiraj kharadi pune
102 Patil kalptaru pimpri
103 Abhay west pimpri
104 Raghav rt nashik
105 Dhanu bvh pune
SQL> create table loan(lno int primary key,lamt int check(lamt>0),cno int references customer on delete cascade);
Table created.
SQL>
SQL> insert into loan values(1,120000,101);
1 row created.
SQL> insert into loan values(2,100000,102);
1 row created.
SQL> insert into loan values(3,30000,103);
1 row created.
SQL> insert into loan values(4,120,104);
1 row created.
SQL> insert into loan values(5,1000000,105);
1 row created.
SQL> select * from loan;
LNO LAMT CNO
---------- ---------- ----------
1 120000 101
2 100000 102
3 30000 103
4 120 104
5 1000000 105
Q.3Consider the above tables and execute the following queries:
1. Add Phone_No column in customer table with data type int.
SQL> alter table customer
2 add phone_no int;
Table altered.
SQL> desc customer
Name Null? Type
----------------------------------------- -------- ----------------------------
CNO NOT NULL NUMBER(38)
CNAME NOT NULL VARCHAR2(20)
ADDR VARCHAR2(20)
CITY VARCHAR2(10)
PHONE_NO NUMBER(38)
2)Delete the details of customer whose loan_amt<1000.
Delete cno,cname,addr,city, from customer
Where customer.cno=loan.cno
And lamt<1000;
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Find details of all customers whose loan_amt is greater than 10 lack.
SQL> select * from customer,loan
2 where customer.cno=loan.cno
3 and lamt>1000000;
no rows selected
2. List all customers whose name starts with 'D' character.
SQL> select * from customer
2 where cname like 'D%';
CNO CNAME ADDR CITY PHONE_NO
---------- -------------------- -------------------- ---------- ----------
101 Dhiraj kharadi pune
105 Dhanu bvh pune
3. List the names of customer in descending order who has taken a loan from Pimpri city.
SQL> select * from customer
2 where city='pimpri'
3 order by cname desc;
CNO CNAME ADDR CITY PHONE_NO
---------- -------------------- -------------------- ---------- ----------
102 Patil kalptaru pimpri
103hay west pimpri
4.Display customer details having maximum loan amount
SQL> select max(lamt) from customer,loan
2 where customer.cno=loan.cno;
MAX(LAMT)
----------
1000000
5.Update the address of customer whose name is “Mr. Patil” and loan_amt is greater than 100000.
update customer set addr='pune'
where cname='patil' and lno in(select lno from laon where lamt>100000);
Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Project (pno, pname, start_date, budget, status) Department (dno, dname, HOD, loc)
The relationship between Project and Department is Many to One. Constraint: Primary key. Project Status Constraints:
C – Completed,
P - Progressive,
I – Incomplete
SQL> create table project(pno int primary key,pname varchar(20),sdate date,budget int,status varchar(20) check(status in('c','i','p')));
Table created.
SQL> desc project;
Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER(38)
PNAME VARCHAR2(20)
SDATE DATE
BUDGET NUMBER(38)
STATUS VARCHAR2(20)
SQL> insert into project values(1,'abc','09/mar/20',2300000,'c');
1 row created.
SQL> insert into project values(2,'xyz','01/apr/18',200000,'i');
1 row created.
SQL> insert into project values(3,'st','23/mar/27',1200000,'p');
1 row created.
SQL> insert into project values(4,'vb','12/feb/20',600000,'c');
1 row created.
SQL> insert into project values(5,'qrt','16/jan/23',3400000,'p');
1 row created.
SQL> select * from project;
PNO PNAME SDATE BUDGET STATUS
---------- -------------------- --------- ---------- --------------------
1 abc 09-MAR-20 2300000 c
2 xyz 01-APR-18 200000 i
3 st 23-MAR-27 1200000 p
4 vb 12-FEB-20 600000 c
5 qrt 16-JAN-23 3400000 p
SQL> create table department(dno int primary key,dname varchar(20),hod varchar(20),loc varchar(20),pno int references project on delete cascade);
Table created.
SQL> desc department
Name Null? Type
----------------------------------------- -------- ----------------------------
DNO NOT NULL NUMBER(38)
DNAME VARCHAR2(20)
HOD VARCHAR2(20)
LOC VARCHAR2(20)
PNO NUMBER(38)
SQL> insert into department values(101,'computer','desai','pune',1);
1 row created.
SQL> insert into department values(102,'commerce','mane','pune',2);
1 row created.
SQL> insert into department values(103,'computer','kadam','pune',3);
1 row created.
SQL> insert into department values(104,'engineering','sam','pune',4);
1 row created.
SQL> select * from department;
DNO DNAME HOD LOC
---------- -------------------- -------------------- --------------------
PNO
----------
101 computer desai pune
1
102 commerce mane pune
2
103 computer kadam pune
3
DNO DNAME HOD LOC
---------- -------------------- -------------------- --------------------
PNO
----------
104 engineering sam pune
4
Consider the above tables and execute the following queries:
1. Drop loc column from department table.
alter table department
drop column loc;
2. Display the details of project whose start_date is before one month and status is “Progressive”
SQL> select * from project
2 where sdate>'12/feb/20' and status='p';
PNO PNAME SDATE BUDGET STATUS
---------- -------------------- --------- ---------- --------------------
3 st 23-MAR-27 1200000 p
5 qrt 16-JAN-23 3400000 p
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Display the names of project and department who are worked on projects whose status is ‘Completed’
SQL>
SQL> Select pname,dname,hod,loc from department,project
2 where department.pno= project.pno
3 and project.status='c';
PNAME DNAME HOD
-------------------- -------------------- --------------------
LOC
--------------------
abc computer desai
pune
vb engineering sam
pune
2. Display total budget of each department.
SQL> Select sum(budget),dname from department,project
2 where department.pno=project.pno
3 group by dname;
SUM(BUDGET) DNAME
----------- --------------------
200000 commerce
600000 engineering
3500000 computer
3. Display incomplete project of each department.
SQL> select pname,status ,count(department.dno) from department,project
2 where department.pno=project.pno
3 and project.status='i'
4 group by status,pname;
PNAME STATUS COUNT(DEPARTMENT.DNO)
-------------------- -------------------- ---------------------
xyz i 1
4. Display all project working under 'Mr.Desai'.
SQL> Select pname from department,project
2 where department.pno=project.pno
3 and hod= 'desai';
PNAME
--------------------
Abc
5.Display department wise HOD.
SQL> select dname,hod from department,project
2 where department.pno=project.pno
3 order by dname;
DNAME HOD
-------------------- --------------------
commerce mane
computer kadam
computer desai
engineering sam
slip no_7:Q3. Consider the following entities and their relationships.
Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Room (roomno, desc, rate)
Guest (gno, gname, no_of_days)
The relationship between Room and Guest is One to One. Constraint: Primary key, no of days should be > 0.
SQL> create table room(rno int primary key,des varchar(20),rate number);
Table created.
SQL> desc room;
Name Null? Type
----------------------------------------- -------- ----------------------------
RNO NOT NULL NUMBER(38)
DES VARCHAR2(20)
RATE NUMBER
SQL> insert into room values(101,’A/C’,1500);
1 row created.
SQL> insert into room values(102,’Non A/C’,750);
1 row created.
SQL> insert into room values(103,’A/C’,2000);
1 row created.
SQL> insert into room values(104,’Non A/C’,1200);
1 row created.
SQL> select * from room;
RNO DES RATE
---------- -------------------- ----------
101 A/C 1500
102 Non A/C 750
103 A/C 2000
104 Non A/C 1200
SQL> create table guest(gno int primary key,gname varchar(20),nod number check (nod>0));
Table created.
SQL> desc guest;
Name Null? Type
----------------------------------------- -------- ----------------------------
GNO NOT NULL NUMBER(38)
GNAME VARCHAR2(20)
NOD NUMBER
SQL> insert into guest values(101,'Mr.Bharat',3);
1 row created.
SQL> insert into guest values(102,'Mr.Nilesh',4);
1 row created.
SQL> insert into guest values(103,'Mr.Advait',7);
1 row created.
SQL> insert into guest values(104,'Miss.Sapana',2);
1 row created.
SQL> select * from guest;
GNO GNAME NOD
---------- -------------------- ----------
101 Mr.Bharat 3
102 Mr.Nilesh 4
103 Mr.Advait 7
104 Miss.Sapana 2
Consider the above tables and execute the following queries:
1. Update the rate of room to 5000 whose type is “AC”
SQL> update room set rate=5000
2 where des='A/C';
2 rows updated.
SQL> select * from room;
RNO DES RATE
---------- -------------------- ----------
101 A/C 5000
102 Non A/C 750
103 A/C 5000
104 Non A/C 1200
2. Display the name of guest who is staying 2 days in roomno 101
select gname from room,guest
where room.rno=guest.rno
and nod=2 and rno=101;
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Display room details according to its rates in ascending order
SQL> select des,rate from room
2 order by des asc;
DES RATE
-------------------- ----------
A/C 5000
A/C 5000
Non A/C 1200
Non A/C 750
2. Display the roomno in which “Mr. Advait” is staying for 7 days
select rno from room,guest
where room.rno=guest.rno
and gname='Mr.Advait' and nod=7;
3. Find no. of AC rooms.
SQL> select count(rno) from room
2 where des='A/C';
COUNT(RNO)
----------
2
4. Find names of guest with maximum room charges.
select gname from room,guest
where guest.rno=room.rno
and rate=(select max(rate) from room);
5. Display guest wise halt days.
Select gname,nod from guest
Order by gname;
SQL> Select gname,nod from guest
2 Order by gname;
GNAME NOD
-------------------- ----------
Miss.Sapana 2
Mr.Advait 7
Mr.Bharat 3
Mr.Nilesh 4
Slip_no 8:Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Book (Book_no, title, author, price, year_published) Customer (cid, cname, addr)
Relation between Book and Customer is Many to Many with quantity as descriptive attribute. Constraint: Primary key, price should be >0;
SQL> create table book(bno int primary key,title varchar(10),author varchar(20),price int check(price>0),yp number);
Table created.
SQL> desc book;
Name Null? Type
----------------------------------------- -------- ----------------------------
BNO NOT NULL NUMBER(38)
TITLE VARCHAR2(10)
AUTHOR VARCHAR2(20)
PRICE NUMBER(38)
YP NUMBER
SQL> insert into book values(101,'dreams','mr.Raj',150,2017);
1 row created.
SQL> insert into book values(102,'life','mr.Raghav',100,2019);
1 row created.
SQL> insert into book values(103,'rt story','mr.Gadhave',190,2011);
1 row created.
SQL> insert into book values(104,'Dad','dr.Sam',200,2001);
1 row created.
SQL> insert into book values(105,'Struggle','mr.Raj',250,2017);
1 row created.
SQL> insert into book values(106,'Joker','Mr. Talore',230,2011);
1 row created.
SQL> select * from book;
BNO TITLE AUTHOR PRICE YP
---------- ---------- -------------------- ---------- ----------
101 dreams mr.Raj 150 2017
102 life mr.Raghav 100 2019
103 rt story mr.Gadhave 190 2011
104 Dad dr.Sam 200 2001
105 Struggle mr.Raj 250 2017
106 Joker Mr. Talore 230 2011
6 rows selected.
SQL> create table customer(cid int primary key,cname varchar(20),addr varchar(20),bno int references book);
Table created.
SQL> desc customer;
Name Null? Type
----------------------------------------- -------- ----------------------------
CID NOT NULL NUMBER(38)
CNAME VARCHAR2(20)
ADDR VARCHAR2(20)
BNO NUMBER(38)
SQL> insert into customer values(1,'Abhay','pune',101);
1 row created.
SQL> insert into customer values(2,'Sam','Mumbai',102);
1 row created.
SQL> insert into customer values(3,'Raghav','pimpri',103);
1 row created.
SQL> insert into customer values(4,'Abhay','mumbai',104);
1 row created.
SQL> insert into customer values(5,'Ganesh','Nashik',105);
1 row created.
SQL> select * from customer;
CID CNAME ADDR BNO
---------- -------------------- -------------------- ----------
1 Abhay pune 101
2 Sam Mumbai 102
3 Raghav pimpri 103
4 Abhay mumbai 104
5 Ganesh Nashik 105
SQL> create table customerbook(bcid int primary key,bno int references book,cid int references customer);
Table created.
SQL> desc customerbook;
Name Null? Type
----------------------------------------- -------- ----------------------------
BCID NOT NULL NUMBER(38)
BNO NUMBER(38)
CID NUMBER(38)
SQL> insert into customerbook values(11,101,1);
1 row created.
SQL> insert into customerbook values(12,102,2);
1 row created.
SQL> insert into customerbook values(13,101,3);
1 row created.
SQL> insert into customerbook values(14,103,1);
1 row created.
SQL> insert into customerbook values(15,106,4);
1 row created.
SQL> select * from customerbook;
BCID BNO CID
---------- ---------- ----------
11 101 1
12 102 2
13 101 3
14 103 1
15 106 4
Consider the above tables and execute the following queries:
1.Display the name of book whose author is “Mr. Gadhave”.
SQL> select title from book
2 where author='mr.Gadhave';
TITLE
----------
rt story
2.Add column EMailId into customer table.
SQL> alter table customer
2 add emailID varchar2(20);
Table altered.
SQL> desc customer;
Name Null? Type
----------------------------------------- -------- ----------------------------
CID NOT NULL NUMBER(38)
CNAME VARCHAR2(20)
ADDR VARCHAR2(20)
BNO NUMBER(38)
EMAILID VARCHAR2(20)
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Display customer details from 'Mumbai'.
SQL> select * from customer
2 where addr='mumbai';
CID CNAME ADDR BNO
---------- -------------------- -------------------- ----------
EMAILID
--------------------
4 Abhay mumbai 104
2. Display author wise details of book.
SQL> select author,title from book
2 order by author;
AUTHOR TITLE
-------------------- ----------
Mr. Talore Joker
dr.Sam Dad
mr.Gadhave rt story
mr.Raghav life
mr.Raj dreams
mr.Raj Struggle
6 rows selected.
3)Display customer name that has purchased more than 3 books.
SQL> select count(book.bno),cname from customer,book,customerbook
2 where customer.cid=customerbook.cid
3 and book.bno=customerbook.bno and book.bno>3
4 group by cname;
COUNT(BOOK.BNO) CNAME
--------------- --------------------
1 Raghav
1 Sam
3 Abhay
3. Display book names having price between 100 and 200 and published year is 2019.
SQL> select book.title from book,customer,customerbook
2 where customer.cid=customerbook.cid
3 and book.bno=customerbook.bno
4 and yp=2019 and price between 100 and 200;
TITLE
----------
life
5. Update the title of book to “DBMS” whose author is “Mr. Talore”.
SQL> update book set title='DBMS'
2 where author='Mr. Talore';
1 row updated.
SQL> select * from book;
BNO TITLE AUTHOR PRICE YP
---------- ---------- -------------------- ---------- ----------
101 dreams mr.Raj 150 2017
102 life mr.Raghav 100 2019
103 rt story mr.Gadhave 190 2011
104 Dad dr.Sam 200 2001
105 Struggle mr.Raj 250 2017
106 DBMS Mr. Talore 230 2011
6 rows selected.
Slip_no:9 Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Property (pno, desc, area, rate)
Owner (owner_name, addr, phno) The relationship between owner and Property is One to Many. Constraint: Primary key, rate should be > 0
SQL> create table property(pno int primary key,des varchar(20) not null,area varchar(20) not null,rate int check(rate>0));
Table created.
SQL> desc property;
Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER(38)
DES NOT NULL VARCHAR2(20)
AREA NOT NULL VARCHAR2(20)
RATE NUMBER(38)
SQL> insert into property values(101,'vegr','nashik',1030000);
1 row created.
SQL> insert into property values(102,'tr','Pune',100000);
1 row created.
SQL> insert into property values(103,'vbh','pune',1030000);
1 row created.
SQL> insert into property values(104,'vsdr','mumbai',20000);
1 row created.
SQL> insert into property values(105,'hjjr','nashik',10000);
1 row created.
SQL> select * from property;
PNO DES AREA RATE
---------- -------------------- -------------------- ----------
101 vegr nashik 1030000
102 tr Pune 100000
103 vbh pune 1030000
104 vsdr mumbai 20000
105 hjjr nashik 10000
SQL> create table owner(name varchar(20),addr varchar(20),phno int,pno int references property);
Table created.
SQL> desc owner;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(20)
ADDR VARCHAR2(20)
PHNO NUMBER(38)
PNO NUMBER(38)
SQL> insert into owner values('Mr.Mane','Mumbai',1762386534,101);
1 row created.
SQL> insert into owner values('Mr.Patil','Mumbai',1762386534,102);
1 row created.
SQL> insert into owner values('Mr.Joshi','Pune',6892386534,103);
1 row created.
SQL> insert into owner values('Mr.Bhagat','Pune',6876783865,101);
1 row created.
SQL> insert into owner values('Mr.Abhay','Pune',6753386534,104);
1 row created.
SQL> select * from owner;
NAME ADDR PHNO PNO
-------------------- -------------------- ---------- ----------
Mr.Mane Mumbai 1762386534 101
Mr.Patil Mumbai 1762386534 102
Mr.Joshi Pune 6892386534 103
Mr.Bhagat Pune 6876783865 101
Mr.Abhay Pune 6753386534 104
Consider the above tables and execute the following queries:
1. Display area of property whose rate is less than 100000
SQL> select area from property
2 where rate>100000;
AREA
--------------------
nashik
pune
2. Give the details of owner whose property is at “Pune”
SQL> select * from owner
2 where addr='Pune';
NAME ADDR PHNO PNO
-------------------- -------------------- ---------- ----------
Mr.Joshi Pune 6892386534 103
Mr.Bhagat Pune 6876783865 101
Mr.Abhay Pune 6753386534 104
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Display area wise property details.
SQL> select area,des from property
2 order by area;
AREA DES
-------------------- --------------------
Pune tr
mumbai vsdr
nashik vegr
nashik hjjr
pune vbh
2. Display property owned by 'Mr.Patil' having minimum rate.
SQL> select min(rate) from property,owner
2 where property.pno=owner.pno
3 and name='Mr.Patil';
MIN(RATE)
----------
100000
3. Delete all properties from “pune” owned by “Mr. Joshi”.
SQL> delete from owner
2 where addr='Pune' and name='Mr.Joshi';
1 row deleted.
SQL> select * from owner;
NAME ADDR PHNO PNO
-------------------- -------------------- ---------- ----------
Mr.Mane Mumbai 1762386534 101
Mr.Patil Mumbai 1762386534 102
Mr.Bhagat Pune 6876783865 101
Mr.Abhay Pune 6753386534 104
4. Update the phone Number of “Mr. Joshi” to 9922112233 who is having property at “Uruli Kanchan”
SQL> update owner set phno=9922112233
2 where addr='Urali Kanchan';
1 row updated.
SQL> select * from owner;
NAME ADDR PHNO PNO
-------------------- -------------------- ---------- ----------
Mr.Mane Mumbai 1762386534 101
Mr.Patil Mumbai 1762386534 102
Mr.Bhagat Pune 6876783865 101
Mr.Abhay Pune 6753386534 104
Mr.Sam Urali Kanchan 9922112233 104
5.Delete column address from Owner table.
Alter table owner
drop column addr;
slip_no-10:Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Employee (emp_no, name, skill, payrate)
Position (posting_no, skill)
The relationship between Employee and Position is Many to Many with day and shift as descriptive attribute. Constraint: Primary key, payrate should be > 0.
Connected.
SQL> create table employee(eno int primary key,name varchar(20),skill varchar(20) not null,payrate int check(payrate>0));
Table created.
SQL> desc employee;
Name Null? Type
----------------------------------------- -------- ----------------------------
ENO NOT NULL NUMBER(38)
NAME VARCHAR2(20)
SKILL NOT NULL VARCHAR2(20)
PAYRATE NUMBER(38)
SQL> insert into employee values(1,'Rghav','manager',23000);
1 row created.
SQL> insert into employee values(2,'Mane','waiter',23000);
1 row created.
SQL> insert into employee values(3,'Priya','ceo',23000);
1 row created.
SQL> insert into employee values(4,'Abhay','chef',23000);
1 row created.
SQL> select * from employee;
ENO NAME SKILL PAYRATE
---------- -------------------- -------------------- ----------
1 Rghav manager 23000
2 Mane waiter 23000
3 Priya ceo 23000
4 Abhay chef 23000
SQL> create table position(pno int primary key,skill varchar(20),eno int references employee);
Table created.
SQL> desc position;
Name Null? Type
----------------------------------------- -------- ----------------------------
PNO NOT NULL NUMBER(38)
SKILL VARCHAR2(20)
ENO NUMBER(38)
SQL> insert into position values(201,'mg',1);
1 row created.
SQL> insert into position values(203,'ceo',2);
1 row created.
SQL> insert into position values(202,'wt',3);
1 row created.
SQL> insert into position values(205,'wdf',4);
1 row created.
SQL> insert into position values(204,'whd',2);
1 row created.
SQL> select * from position;
PNO SKILL ENO
---------- -------------------- ----------
201 mg 1
203 ceo 2
202 wt 3
205 wdf 4
204 whd 2
SQL> create table ep(epno int primary key,eno int references employee,pno int references position);
Table created.
SQL> desc ep;
Name Null? Type
----------------------------------------- -------- ----------------------------
EPNO NOT NULL NUMBER(38)
ENO NUMBER(38)
PNO NUMBER(38)
SQL> insert into ep values(11,1,201);
1 row created.
SQL> insert into ep values(12,2,202);
1 row created.
SQL> insert into ep values(13,2,203);
1 row created.
SQL> insert into ep values(14,3,202);
1 row created.
SQL> insert into ep values(15,1,204);
1 row created.
SQL> select * from ep;
EPNO ENO PNO
---------- ---------- ----------
11 1 201
12 2 202
13 2 203
14 3 202
15 1 204
Consider the above tables and execute the following queries:
1. Display skill of employees name wise.
SQL> select name,skill from employee
2 order by name;
NAME SKILL
-------------------- --------------------
Abhay chef
Mane waiter
Priya ceo
Rghav manager
2)Update the posting of employee to 220 whose skill is “Manager”.
SQL> update position set pno=220
2 where skill='mg';
1 row updated.
SQL> select * from position;
PNO SKILL ENO
---------- -------------------- ----------
220 mg 1
203 ceo 2
202 wt 3
205 wdf 4
204 whd 2
208 manager 2
6 rows selected.
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Find the names and rate of pay of all employees who has allocated a duty.
SQL> select name,payrate from employee;
NAME PAYRATE
-------------------- ----------
Rghav 23000
Mane 23000
Priya 23000
Abhay 23000
2. Give employee number who is working at posting_no. 201, but don’t have the skill of waiter
SQL> select employee.name,employee.skill from employee,position,ep
2 where employee.eno=ep.eno
3 and position.pno=ep.pno
4 and position.pno=201 and employee.skill not in('waiter');
no rows selected
3)Display a list of names of employees who have skill of chef and who has assigned a duty.
select name from employee,position,ep
where employee.eno=ep.eno
and position.pno=ep.pno
and employee.skill='chef';
4. Display shift wise employee details.
SQL> select name,employee.skill from employee,position,ep
2 where employee.eno=ep.eno
3 and position.pno=ep.pno
4 group by employee.skill,name;
no rows selected
5. Update payrate of employees to 20000 whose skill is waiter.
SQL> update employee set payrate=20000
2 where skill='waiter';
1 row updated.
SQL> select * from employee;
ENO NAME SKILL PAYRATE
---------- -------------------- -------------------- ----------
1 Rghav manager 23000
2 Mane waiter 20000
3 Priya ceo 23000
4 Abhay chef 23000
Slip_no:11:Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price)
The relationship between Bill and Menu is Many to Many with quantity as descriptive attribute.
Constraint: Primary key, price should be > 0.
SQL> create table bill(bno int primary key not null,day varchar(10),tbno int,total int);
Table created.
SQL> desc bill;
Name Null? Type
----------------------------------------- -------- ----------------------------
BNO NOT NULL NUMBER(38)
DAY VARCHAR2(10)
TBNO NUMBER(38)
TOTAL NUMBER(38)
SQL> insert into bill values(301,'monday',109,1120);
1 row created.
SQL> insert into bill values(302,'sunday',123,9120);
1 row created.
SQL> insert into bill values(303,'tuesday',122,4200);
1 row created.
SQL> insert into bill values(304,'monday',176,2210);
1 row created.
SQL> select * from bill;
BNO DAY TBNO TOTAL
---------- ---------- ---------- ----------
301 monday 109 1120
302 sunday 123 9120
303 tuesday 122 4200
304 monday 176 2210
SQL> create table menu(dno int primary key not null,ddes varchar(10), price int check(price>0),bno int references bill);
Table created.
SQL> desc menu;
Name Null? Type
----------------------------------------- -------- ----------------------------
DNO NOT NULL NUMBER(38)
DDES VARCHAR2(10)
PRICE NUMBER(38)
BNO NUMBER(38)
SQL> insert into menu values(101,'veg',200,301);
1 row created.
SQL> insert into menu values(102,'non-veg',300,303);
1 row created.
SQL> insert into menu values(103,'non-veg',400,301);
1 row created.
SQL> insert into menu values(104,'veg',250,301);
1 row created.
SQL> insert into menu values(105,'non-veg',800,302);
1 row created.
SQL> insert into menu values(106,'veg',600,304);
1 row created.
SQL> select * from menu;
DNO DDES PRICE BNO
---------- ---------- ---------- ----------
101 veg 200 301
102 non-veg 300 303
103 non-veg 400 301
104 veg 250 301
105 non-veg 800 302
106 veg 600 304
6 rows selected.
SQL> create table bm(bmno int primary key,ddate varchar(10),bno int references bill,mno int references menu);
Table created.
SQL> desc bm;
Name Null? Type
----------------------------------------- -------- ----------------------------
BMNO NOT NULL NUMBER(38)
DDATE VARCHAR2(10)
BNO NUMBER(38)
MNO NUMBER(38)
SQL> insert into bm values(1,'12/02/10',301,102);
1 row created.
SQL> insert into bm values(2,'09/07/19',303,104);
1 row created.
SQL> insert into bm values(3,'02/06/11',302,101);
1 row created.
SQL> insert into bm values(4,'12/02/09',304,102);
1 row created.
SQL> select * from bm;
BMNO DDATE BNO MNO
---------- ---------- ---------- ----------
1 12/02/10 301 102
2 09/07/19 303 104
3 02/06/11 302 101
4 12/02/09 304 102
Consider the above tables and execute the following queries:
1. Display the tableno whose dish_desc is “Veg”.
SQL> select tno from menu,bill,bm
2 where bill.bno=bm.bno
3 and menu.mno=bm.mno
4 and dis='veg';
TNO
----------
123
122
2. Display the special menu of Monday.
SQL> select dis from bill,menu,bm
2 where bill.bno=bm.bno
3 and menu.mno=bm.mno
4 and day='monday';
DIS
----------
non-veg
non-veg
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Display receipt which includes bill_no with Dish description, price, quantity and total amount of each menu.
SQL> select sum(bill.total),menu.dis,menu.price,bm.qunt from bill,menu,bm
2 where bill.bno=bm.bno
3 and menu.mno=bm.mno
4 group by menu.dis,menu.price,bm.qunt;
SUM(TOTAL) DIS PRICE QUNT
--------------- ---------- ---------- ----------
9120 veg 200 102
4200 veg 250 23
1120 non-veg 300 123
2210 non-veg 300 312
2)Find total amount collected by hotel on date 09/07/2019.
SQL> select sum(total) from bill,menu,bm
2 where bill.bno=bm.bno
3 and menu.mno=bm.mno
4 and ddate='09/07/19';
SUM(TOTAL)
----------
4200
3)Count number of menus of billno 301
SQL> select count(dis) from bill,menu,bm
2 where bill.bno=bm.bno
3 and menu.mno=bm.mno
4 and bill.bno=301;
COUNT(DIS)
----------
1
4)Display menu details having price between 100 and 500.
SQL> select dis,price from menu
2 where price between 100 and 500;
DIS PRICE
---------- ----------
veg 200
non-veg 300
non-veg 400
veg 250
5. Display the tableno and day whose bill amount is zero.
SQL> select tno,day from bill
2 where total=0;
no rows selected
slip-no:12 Q3 Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Movies (M_name, release_year, budget)
Actor (A_name, role, charges, A_address)
Producer (producer_id, name, P_address)
Relationship:- Each actor has acted in one or more movie. Each producer has produced many movies but each movie can be produced by more than one producers.
Each movie has one or more actors acting in it, in different roles.
Constraint: Primary key, release_year > 2000, A_address and P_address should not be same.
Consider the above tables and execute the following queries:
1. List the names of movies with the highest budget.
2. Display the details of producer who have produced more than one movie in a year.
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. List the names of movies with the second highest budget 2. List the names of actors who have acted in the maximum number of movies.
3. List the names of movies, produced by more than one producer.
4. List the names of actors who are given with the maximum charges for their movie.
5. List the names of actors who have acted in at least one movie, in which ‘Akshay’ has acted.
Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with appropriate data types and Constraints. [15 Marks]
Driver (driver_id, driver_name, address)
Car (license_no, model, year)
Relation between Driver and Car is Many to Many with date and time as descriptive attribute.
Constraint: Primary key, driver_name should not be null
SQL> create table driver(did int primary key,dname varchar(10),addr varchar(10));
Table created.
SQL> desc driver;
Name Null? Type
----------------------------------------- -------- ----------------------------
DID NOT NULL NUMBER(38)
DNAME VARCHAR2(10)
ADDR VARCHAR2(10)
SQL> insert into driver values(101,'Raghav','pune');
1 row created.
SQL> insert into driver values(102,'ram','mumbai');
1 row created.
SQL> insert into driver values(103,'Abhay','pune');
1 row created.
SQL> insert into driver values(104,'Ganesh','Nanded');
1 row created.
SQL> insert into driver values(105,'Ritik','Nashik');
1 row created.
SQL> select * from driver;
DID DNAME ADDR
---------- ---------- ----------
101 Raghav pune
102 ram mumbai
103 Abhay pune
104 Ganesh Nanded
105 Ritik Nashik
SQL> create table car(lno varchar(10) primary key,model varchar(10),year number,did int references driver);
Table created.
SQL> desc car;
Name Null? Type
----------------------------------------- -------- ----------------------------
LNO NOT NULL VARCHAR2(10)
MODEL VARCHAR2(10)
YEAR NUMBER
DID NUMBER(38)
SQL> insert into car values('DPU123','w12b',1987,101);
1 row created.
SQL> insert into car values('DPU781','SUV300',2019,103);
1 row created.
SQL> insert into car values('DPU231','swif',2001,105);
1 row created.
SQL> insert into car values('DPU018','ty12',1999,102);
1 row created.
SQL> insert into car values('DPU810','nh79',2001,104);
1 row created.
SQL> select * from car;
LNO MODEL YEAR DID
---------- ---------- ---------- ----------
DPU123 w12b 1987 101
DPU781 SUV300 2019 103
DPU231 swif 2001 105
DPU018 ty12 1999 102
DPU810 nh79 2001 104
SQL> create table dc(dco int primary key,did int references driver,lno varchar(10) references car);
Table created.
SQL> desc dc;
Name Null? Type
----------------------------------------- -------- ----------------------------
DCO NOT NULL NUMBER(38)
DID NUMBER(38)
LNO VARCHAR2(10)
SQL> insert into dc values(301,101,'DPU123');
1 row created.
SQL> insert into dc values(302,102,'DPU781');
1 row created.
SQL> insert into dc values(303,103,'DPU123');
1 row created.
SQL> insert into dc values(304,101,'DPU018');
1 row created.
SQL> insert into dc values(305,105,'DPU810');
1 row created.
SQL> select * from dc;
DCO DID LNO
---------- ---------- ----------
301 101 DPU123
302 102 DPU781
303 103 DPU123
304 101 DPU018
305 105 DPU810
Consider the above tables and execute the following queries:
1. Display the name of driver whose license no is “DPU123”.
SQL> select dname from driver,car,dc
2 where driver.did=dc.did
3 and car.lno=dc.lno
4 and car.lno='DPU123';
DNAME
----------
Raghav
Abhay
2. Delete the details of car whose model is “swift”.
SQL> delete from car
2 where model='swif';
1 row deleted.
SQL> select * from car;
LNO MODEL YEAR DID
---------- ---------- ---------- ----------
DPU123 w12b 1987 101
DPU781 SUV300 2019 103
DPU018 ty12 1999 102
DPU810 nh79 2001 104
Q4. Consider the above tables and execute the following queries: [25 Marks]
1. Display details of all persons who are driving ‘Alto’ car
SQL> select dname from driver,car,dc
2 where driver.did=dc.did
3 and car.lno=dc.lno
4 and model='Alto';
DNAME
----------
Ganesh
2.Update model of car to “SUV300” whose manufactured year is 2019.
SQL> update car set model='SUV300'
2 where year=2019;
2 rows updated.
SQL> select * from car;
LNO MODEL YEAR DID
---------- ---------- ---------- ----------
DPU123 w12b 1987 101
DPU781 SUV300 2019 103
DPU018 ty12 1999 102
DPU810 nh79 2001 104
DPU811 Alto 2001 104
DPU701 SUV300 2019 101
6 rows selected.
3.Display car details manufactured before year 2000.
4.In which day ‘Mr. Ram’ drives maximum number of cars.
SQL> select count(car.model),dname from driver,car,dc
2 where driver.did=dc.did
3 and car.lno=dc.lno
4 and dname='ram'
5 group by dname;
COUNT(CAR.MODEL) DNAME
---------------- ----------
1 ram
5.Display total number of drivers who drives car in each year.
SQL> select count(driver.did),year,dname from driver,car,dc
2 where driver.did=dc.did
3 and car.lno=dc.lno
4 group by year,dname;
COUNT(DRIVER.DID) YEAR DNAME
----------------- ---------- ----------
1 1987 Abhay
1 1987 Raghav
1 1999 Raghav
1 2019 ram
2 2001 Ganesh
1 2001 Ritik
6 rows selected.
0 Comments