Shrutika Prashant

JoshiFYBBA(CA)
Roll no:13406
DBMS ASSIGNMENT

SLIP 1
Q 1 Consider the following entities and their relationships. Create a RDB in 3 NF
with appropriate data types and Constraints.

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.

Consider the above tables and Execute the following queries:

1. Add column phone_No into Emp table with data type int.

2. Delete the details of Employee whose designation is ‘Manager’.

Ans:

SQL> create table dept06

2 (dno number (5)primary key ,

3 dname varchar2 (25),

4 loc varchar2 (20));

Table created.

SQL> create table emp06

2 (eno number (5)primary key,

3 ename varchar2(20) not null,

4 designation varchar2 (25),

5 salary number(10,2)check(salary>0),

6 date_of_joining date

7 dno number (5) references dept06 (dno));

SQL> insert into dept06

2 values (101,'manager','pune');

1 row created.

SQL> insert into dept06

2 values(102,'accountant','pimpri');

1 row created.

SQL> insert into dept06

2 values (103,'computer science','dhayri');

1 row created.

SQL> insert into dept06

2 values (104,'quality','pune');

1 row created.

SQL> insert into dept06

2 values (105,'account','chinchwad');

1 row created.

SQL> insert into emp06

2 values(1,'Mr. Advait','manager',54000,'23-mar-2004',101,9325155612);

1 row created.

SQL> insert into emp06

2 values(2,'Mr. Roy','ceo',50000,'15-june-2019',102,9890325284);

1 row created.

SQL> insert into emp06

2 values(3,'Mr. Abhay','manager',60000,'10-oct-2013’,’103',9860156466);

1 row created.

SQL> insert into emp06

2 values(4,'Mr. Raghav','manager',420000,'01-mar-2003',104,9503214716);

1 row created.

SQL> insert into emp06

1 row created.

Q1. Add column phone_No into Emp table with data type int.

SQL> alter table emp06

2 add phone_no number (12);

Table altered.

2. Delete the details of employee whose designation is ‘manager’.

SQL> delete from emp06

2 where designation like 'manager';

3 rows deleted.

1. Display the count of employees department wise.

SQL> select dno, count (eno) from emp06

2 group by dno;

DNO COUNT(ENO)

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

102 1

101 1

104 1

105 1

103 1

2. Display the name of employee who is ‘Manager’ of “Account Department”.

SQl> select ename from EMP06,DEPT06

2 where EMP06.eno=DEPT06.eno

3 and designation like ‘Manager’;

4 and dname like ‘Account Department’;

ENAME

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

MR. advait

3. Display the name of department whose location is “Pune” and “Mr. Advait” is
working in it.

SQL>select dname from EMP06,DEP06

2 where EMP06.eno=DEPT06.eno

3 and loc like ‘ PUNE’ ;

4 and ename like’MR. Advait’;

DNAME

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

account

4. Display the names of employees whose salary is greater than 50000 and
department is “Quality”.

SQL> select ename from EMP06,DEPT06

2 where EMP06.eno=DEPT06.eno

3 and salary>50000

4 and dname like ‘Quality’;

ENO ENAME DESIGNATION SALARY DATE_OF_J

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

DNO PHONE_NO

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

4 Mr. Raghav manager 420000 01-MAR-03

104

5. Update Dateofjoining of employee to ‘15/06/2019’ whose department is

science’ and name is “Mr. Roy’.

SQL> update emp06

2 set date_of_joining = '15-jan-2019'

3 where ename like 'Mr. Roy';

1 row updated.

SLIP 2
Q Consider the following entities and their relationships. Create a RDB in 3 NF
with appropriate data types
and Constraints.

Sales_order (ordNo, ordDate)

Client (clientNo, ClientName, addr)

The relationship between Client & Sales_order is one-to-many. Constraints: -

ANS:

SQL> create table client06

2 (clientno varchar2(5) primary key,

3 clientname varchar2 (25),

4 addr varchar2 (20));

Table created.

SQL> insert into client06

2 values('CN001','Abhay','Pune');

1 row created.

SQL> insert into client06

2 values('CN002','Patil','Pune');

1 row created.

SQL> insert into client06

2 values('CN003','Mr.Roy','Pimpri');

1 row created.

SQL> insert into client06

2 values('CN004','Raj','Mumbai');

1 row created.

SQL> insert into client06

2 values('CN005','joshi' ,'mumbai');

1 row created.

SQL> create table sales_order06

3 orddate date not null,

4 clientno varchar2(5) references client06(clientno));

Table created.

1. Add column amount into Sales_order table with data type int.

SQL> ALTER TABLE SALES_ORDER06

2 ADD AMOUNT NUMBER (10,2);

Table altered.

SQL> insert into sales_order06

2 values(1,'23-june-2015','CN001',15000);

1 row created.

SQL> insert into sales_order06

2 values(2,'09-MAR-2019','CN002',20000);

1 row created.

SQL> insert into sales_order06

2 values(3,'09-AUG-2009','CN004',25000);

1 row created.

SQL> insert into sales_order06

2 values(4,'09-AUG-2019','CN002',38000);

1 row created.

SQL> INSERT INTO SALES_ORDER06

2 values (5,'10-sep-2008','CN005',50000);

1 row created.

2)Change order date of client_No ‘CN001’ ‘18/03/2019’.

SQL> update sales_order06

2 set ordDate='18-mar-2019'

3 where clientno ='CN001';

1 row updated.

4)Display date wise sales_order given by clients.

2 order by ordDate;

ORDDATE ORDNO AMOUNT CLIEN

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

10-SEP-08 5 50000 CN005

09-AUG-09 3 25000 CN004

09-MAR-19 2 20000 CN002

18-MAR-19 1 15000 CN001

09-AUG-19 4 38000 CN002

5) Update the address of client to “Pimpri” whose name is ‘Mr. Roy’

SQL> update client06

2 set addr='pimpri'

3 where clientname='Mr.Roy';

1 row updated.

1. Delete sales order details of client whose name is “Patil” and order date is
“09/08/2019”.

SQL> delete from sales_order06

2 where ordDate='09-AUG-2019'

3 and cno in(select cno from client where cname='Patil');

1 row deleted.

2. Delete the details of the clients whose names start with ‘A’ character.

SQL> delete from client06

2 where cname like'A%';

1 row deleted.

3) Delete all sales_record having order date is before ‘10 /02/2018’.

SQL> delete from sales_order06

2 where ordDate >10 -FEB-2019';

1 row deleted.
 Slip 3

Q3. Consider the following entities and their relationships. Create a RDB in 3 NF
with appropriate data types and Constraints.

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.

Consider the above tables and execute the following queries:

SQL> create table hospital06

2 (hno number(5) primary key,

3 hname varchar2(20),

4 city varchar2(10),

5 est_year number (4) check (est_year>1990),

6 addr varchar2(25));

Table created.

SQL> insert into hospital06

2 values(101,'balaji','pune',1993,'kharadi road');

1 row created.

SQL> insert into hospital06

2 values(103,'vedant','mumbai',1993,'dharavi');

1 row created.

SQL> insert into hospital06

2 VALUES (104,'ruby','pimpri',1993,'kharadi road');

1 row created.

SQL> insert into hospital06

2 values(105,'birla','chinchwad',1993,'BANER');

1 row created.

SQL> insert into hospital06

2 VALUES (102,'SURYA','RASTA PETH',1997,'DHAYRI');

1 row created.
SQL> create table doctor06

2 (dno number (5) primary key,

3 dname varchar2(20),

4 addr varchar2(25),

5 speciality varchar2 (10),

6 hno number (5) references hospital06 (hno));

Table created.

SQL> insert into doctor06

2 values(1,'dr.joshi','pune','skin',104);

1 row created.

SQL> insert into doctor06

2 values(2,'dr.mane','nashik','surgeon',103);

1 row created.

SQL> insert into doctor06

2 values(4,'dr.Raghav','pune','skin',105);

1 row created.

SQL> insert into doctor06

2 values(5,'dr.Abhay','mumbai','internist',104);

1 row created.

SQL> insert into doctor06

2 values(3,'dr.patil','pune','AYURVEDIC',101);

1 row created.

1. Delete addr column from Hospital table.

SQL> alter table hospital06

2 drop column addr;

Table altered.

2.Display doctor name, Hospital name and specialty of doctors from “Pune City” .

SQL> select dname,hname,speciality from doctor06,hospital06

2 where doctor06.hno=hospital06.hno
 3 and city='pune';

DNAME HNAME SPECIALITY

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

dr.patil balaji AYURVEDIC

1. Display the names of the hospitals which are located at “Pimpri” city

SQL> select hname from hospital06,doctor06

2 where doctor06.hno=hospital06.hno

3 and city='pimpri';

HNAME

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

ruby

ruby

2. Display the names of doctors who are working in “Birla” Hospital and city

name is “Chinchwad”.

SQL> select dname from doctor06,hospital06

2 where doctor06.hno=hospital06.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 hospital06,doctor06

2 where doctor06.hno=hospital06.hno

3 and hname='ruby';

SPECIALITY

----------

skin

internist

4 Give the count of doctor’s hospital wise which are located at “Pimple Gurav”.
SQL> select hname,count(dno) from doctor06,hospital06

2 where doctor06.hno=hospital06.hno

3 and addr=' Pimple Gurav '

4 group by hname;

No rows selected.

5. Update an address of Doctor to “Pimpri” whose hospital is “Ruby clinic”.

SQL> update doctor06 set addr ='pimpri'

2 where hno in(select hno from hospital06 where hname='ruby');

2 rows updated.

Slip 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 patient06

2 (pcode number(5) primary key,

3 name varchar2 (20),

4 addr varchar2 (25) not null,

5 diseases varchar2(10));

Table created.

SQL> INSERT INTO PATIENT06

2 values(11,'Raghav','pimple gurav','ALLERGY');

1 row created.

SQL> INSERT INTO PATIENT06

2 values(12,'Abhay','pune','norovirus');

1 row created.

SQL> INSERT INTO PATIENT06

1 row created.

SQL> INSERT INTO PATIENT06

2 values(14,'Sachin','pimple gurav','dengue');

1 row created.

SQL> INSERT INTO PATIENT06

2 values(15,'Priya','nashik','listeria');

1 row created.

SQL> create table bed06

2 (BED_NO number (5) primary key,

3 ROOMNO NUMBER(5),

4 LOC VARCHAR2(10));

Table created.

SQL> INSERT INTO BED06

2 values(1,105,'pune',11);

1 row created.

SQL> INSERT INTO BED06

2 values(2,102,'2nd floor',12);

1 row created.

SQL> INSERT INTO BED06

2 values(3,103,'4th floor',13);

1 row created.

SQL> INSERT INTO BED06

2 values(4,104,'1st floor',11);

1 row created.

SQL> INSERT INTO BED06

2 values(5,101,'3rd floor',14);

1 row created.

1. Display the details of patients who are from “Pimple Gurav”.

2 where addr='pimple gurav';

PCODE NAME ADDR DISEASES

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

11 Raghav pimple gurav ALLERGY

14 Sachin pimple gurav dengue

1. Display the count of patient room wise.

COUNT(PATIENT06.PCODE)

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

2. Display the names of patients who are admitted in room no 101.

SQL> select name from patient06,bed06

2 where patient06.pcode=bed06.pcode

3 and roomno=101;

NAME

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

Sachin

3. Display the disease of patient whose bed_No is 1.

SQL> select diseases from patient06,bed06

2 where patient06.pcode=bed06.pcode

3 and bed_no = 1;

DISEASES

----------

ALLERGY

4. Display the room_no and bed_no of patient whose name is “Mr Roy”.

SQL> select roomno,bed_no from patient06

3 and name='Mr.Roy';

RoomNO Bed_no

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

103 3

5. Give the details of Patient who is admitted on 2 nd flr in roomno 102.

SQL> select * from patient06,bed06

2 where patient06.pcode=bed06.pcode

3 and loc='2nd floor' and roomno=102;

PCODE NAME ADDR DISEASES BED_NO

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

ROOMNO LOC PCODE

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

12 Abhay pune norovirus 2

102 2nd floor 12

2. Delete the details of patient whose Bed_No is 1 and RoomNo is 105.

SQL> select * from patient06,bed06

2 where patient06.pcode=bed06.pcode

3 and bed_no=1 and roomno=105;

PCODE NAME ADDR DISEASE BNO

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

RNO LOC PCODE

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

11 Raghav pimple gurav allergy 1

105 pune 11

Slip 5

Q3. Consider the following entities and their relationships. Create a RDB in 3 NF
with appropriate data types and Constraints.

Customer (cust_no, cust_name, address, city)

The relationship between Customer and Loan is Many to Many

Constraint: Primary key, loan_amt should be > 0.

Consider the above tables and execute the following queries:

SQL> create table customer06

2 (cno number (20) primary key,

3 cname varchar2(20) not null,

4 addr varchar2 (20),

5 city varchar2 (10));

Table created.

SQL> insert into customer06

2 values(101,'Dhiraj','kharadi','pune');

1 row created.

SQL> insert into customer06

2 values(102,'Patil','kalptaru','pimpri');

1 row created

SQL> insert into customer06

2 values(103,'Abhay','west','pimpri');

1 row created.

SQL> insert into customer06

2 values(104,'Raghav','rt','nashik');

1 row created.

SQL> insert into customer06

2 values(105,'Dhanu','bvh','pune');

1 row created.

SQL> create table loan06

2 (lno number (5) primary key,

3 lamt number (10,2) check (lamt > 0),

4 cno number (38) references customer06 (cno));

SQL> INSERT INTO LOAN06

2 values(1,120000,101);

1 row created.

SQL> INSERT INTO LOAN06

2 values(2,100000,102);

1 row created.

SQL> INSERT INTO LOAN06

2 values(3,30000,103);

1 row created.

SQL> INSERT INTO LOAN06

2 values(4,120000,104);

1 row created.

SQL> INSERT INTO LOAN06

2 values(5,100000,105);

1 row created.

1. Add Phone_No column in customer table with data type int.

SQL> alter table customer06

2 add phone_no number (10);

Table altered.

1. Find details of all customers whose loan_amt is greater than 10 lakh.

SQL> select * from customer06,loan06

2 where customer06.cno=loan06.cno

3 and lamt>1000000;

No rows selected

2. List all customers whose name starts with ‘d’ character.

SQL> select * from customer06

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 customer06

2 where city='pimpri'

3 order by cname desc;

CNO CNAME ADDR CITY PHONE_NO

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

102 Patil kalptaru pimpri

103 Abhay west pimpri

4. Display customer details having maximum loan amount.

SQL> select max(lamt) from customer06,loan06

2 where customer06.cno=loan06.cno;

MAX(LAMT)

----------

120000

5. Update the address of customer whose name is “Mr. Patil” and loan_amt is

greater than 100000.

SQL> update loan06

2 set lamt = 120000

3 where lno = 2;

1 row updated.

SQL> update customer06

2 set addr = 'pimple gurav'

3 where cname like 'Patil';

1 row updated.