Shamal CodeCraft Dreams: DBMS SOLVES PRACTICAL SLIPS

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

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)

--------------------

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

102 commerce mane pune

103 computer kadam pune

DNO DNAME HOD LOC

---------- -------------------- -------------------- --------------------

PNO

----------

104 engineering sam pune

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

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)

----------

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

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)

----------

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.

Shamal CodeCraft Dreams

Monday, May 17, 2021

DBMS SOLVES PRACTICAL SLIPS

7 comments:

Popular Posts