Queries


Q. 1)Consider the following Entities & Relationships
Country (con-code,name,capital)
Population (pop-code,population)
Country & Population are related with one-to-one relationship.
Constraints : Primary key and country name should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Give name and population of country whose capital is ‘Delhi’
• Count the number of countries whose population is > 60,00,000
• Find the country details with highest population
• Display country wise population details

/* Creating country table */

CREATE TABLE COUNTRY (CON_CODE NUMBER PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL ENABLE,
CAPITAL VARCHAR2(20) );

insert into country values(1 ,’ India ‘, ‘ Delhi ‘ ) ;
insert into country values(2,’ Bangaladesh ‘,’ Daka ‘ );
insert into country values(3,’ Nepal ‘,’ Kathmandu ‘ );
insert into country values(4, ‘ America ‘, ‘ Woshington ‘ );
insert into country values(5, ‘ Pakistan ‘,’ Lahor ‘ );

/* Creating population table */
CREATE TABLE POPULATION ( POP_CODE NUMBER PRIMARY KEY,
POPULATION NUMBER ) ;

insert into population values(1,7000000000);
insert into population values(2 , 1000000) ;
insert into population values(3 , 600000) ;
insert into population values(4 , 106000000) ;
insert into population values( 5 , 6900000);
1) Give name and population of country whose capital is ‘Delhi’.
select name, population
from population,country
where capital=’Delhi’ and country.con_code=population.pop_code

2) Count the number of countries whose population is > 60,00,000.
select count(name)
from population,country
where country.con_code=population.pop_code and population>6000000

3) Find the country details with highest population.
select country.con_code,name,capital
from country,Population
where country.con_code=population.pop_code and
Population.Population=(select max(population) from population )

4) Display country wise population details.
select name,population
from country,Population
where country.con_code=population.pop_code;

Q. 2)Consider the following Entities & Relationships
Country (con-code,name,capital)
Population (pop-code,population)
Country & Population are related with one-to-one relationship.
Constraints : Primary key and country name should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Find the country details with lowest population
• List the name of countries whose population is between 50,00,000 and 70,00,000
• Find the population of ‘India’
• Display the country details in descending order of population

/* Creating country table */

CREATE TABLE COUNTRY (CON_CODE NUMBER PRIMARY KEY,
NAME VARCHAR2(20) NOT NULL ENABLE,
CAPITAL VARCHAR2(20) );

insert into country values(1 ,’ India ‘, ‘ Delhi ‘ ) ;
insert into country values(2,’ Bangaladesh ‘,’ Daka ‘ );
insert into country values(3,’ Nepal ‘,’ Kathmandu ‘ );
insert into country values(4, ‘ America ‘, ‘ Woshington ‘ );
insert into country values(5, ‘ Pakistan ‘,’ Lahor ‘ );

/* Creating population table */
CREATE TABLE POPULATION ( POP_CODE NUMBER PRIMARY KEY,
POPULATION NUMBER ) ;

insert into population values(1, 7000000000);
insert into population values(2 , 1000000) ;
insert into population values(3 , 600000) ;
insert into population values(4 , 106000000) ;
insert into population values( 5 , 6900000) ;

1) Find the country details with lowest population.
select country.con_code,name,capital
from country,Population
where country.con_code=population.pop_code and
Population.Population=(select min(population) from population )

2) List the name of countries whose population is between 50,00,000 and 70,00,000.
select name
from population, country
where population>=5000000 and
population<=7000000 and
country.con_code=population.pop_code;

3) Find the population of ‘India’.
select population
from population,country
where name=’India’ and country.con_code=population.pop_code
4) Display the country details in descending order of population.
select name, country.con_code,capital,population
from country,population
where country.con_code=population.pop_code
order by population.population desc;
Q. 3) Consider the following Entities & Relationships
Patient (pno, pname, addr)
Bed(bno, roomno, description);
Relationship between Patient and Bed is one to one
Constraints:Primary key.
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Find the name of patients of bed number 103.
• Count the number of bed in room number 3
• Find bed number and room number of patient ‘Mr. Sharma’
• List bed wise patient name along with room number

CREATE TABLE BED(BNO NUMBER PRIMARY KEY,
ROOMNO NUMBER ,
DESCRIPTION VARCHAR2(20));

insert into bed values(100,1,’Non A/C’)
insert into bed values(101,2,’A/C’)
insert into bed values(102,3,’Non A/C’)
insert into bed values(103,3,’Non A/C’)
insert into bed values(104,4,’A/C’)
CREATE TABLE PATIENT
( PNO NUMBER PRIMARY KEY ,
PNAME VARCHAR2(20),
ADDR VARCHAR2(20) )

Insert into patient (100,’Ramesh’,’Pune’)
Insert into patient (101,’Mr.Sharma’,’Shirur’)
Insert into patient (102,’Amol’,’Satara’)
Insert into patient (103,’Pranav’,’Sangli’)

1) Find the name of patients of bed number 103.
select pname
from patient, bed
where patient.pno=bed.bno and bno=103

2) Count the number of bed in room number 3
select count(bno)
from patient, bed
where patient.pno=bed.bno and roomno=3
3)Find bed number and room number of patient ‘Mr. Sharma’
select bno,roomno
from patient, bed
where patient.pno=bed.bno and pname=’Mr.Sharma’

4) List bed wise patient name along with room number.
select bno,pname,roomno
from patient, bed
where patient.pno=bed.bno order by bno ,pname,roomno;

Q. 4) Consider the following Entities & Relationships
Room(Rno, roomtype, Rate)
Guest(Gno,gname,no_of_days)
Room and Guest are related with one to one relationship.
Constraint: Primary Key no_of_days should be > 0
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Find the type of room number 101
• Find the name of guest who have allocated room more than three days
• Display the room information having maximum rate.
• List room wise guest name
CREATE TABLE ROOM(RNO NUMBER PRIMARY KEY,
ROOMTYPE VARCHAR2(20),
RATE NUMBER);

insert into room values(101,’A/C’,1500)
insert into room values(102,’Non A/C’,750)
insert into room values(103,’A/C’,2000)
insert into room values(104,’Non A/C’,1200)

CREATE TABLE GUEST(GNO NUMBER PRIMARY KEY,
GNAME VARCHAR2(20),
NO_OF_DAYS NUMBER CHECK (NO_OF_DAYS>0))

insert into guest values(101, ‘Mr.Bharat’, 3)
insert into guest values(102, ‘Mr.Nilesh’, 4)
insert into guest values(103, ‘Miss.Raj’, 1)
insert into guest values(104, ‘Miss.Sapana’, 2)

1) Find the type of room number 101.
select roomtype
from room
where rno=101
2) Find the name of guest who have allocated room more than three days.
select gname
from guest
where no_of_days >=3;

3) Display the room information having maximum rate.
select rno,roomtype
from room
where rate=(select max(rate) from room)

4)List room wise guest name
select rno,gname
from room,guest
where room.rno=guest.gno;

Q. 5)Consider the following Entities and Relationships
Machine (mno, mname, mtype, mcost)
Part (pno, pnmae, pdesc)
Machine and parts are related with one to many relationships
Constraints : Primary Key, Machine name not be null
Create a RDB in 3NF & construct queries in Oracle 8i
• List all machines having cost > 5000.
• Display machine wise part details.
• Find the name of machine having maximum cost.
• Display the name of all machines having parts ‘wheel’

CREATE TABLE MACHINE (MNO NUMBER PRIMARY KEY,
MNAME VARCHAR(20),
MTYPE VARCHAR(20),
MCOST NUMBER);

insert into machine values (1,’2wheeler’,’A’,20000);
insert into machine values (2,’3wheeler’,’B’,76000);
insert into machine values (3,’4wheeler’,’c’,110000)
insert into machine values (4,’4wheeler’,’D’,130000)
insert into machine values (5,’CNC’,’E’,23000)

CREATE TABLE PART (PNO NUMBER PRIMARY KEY,
MNO NUMBER REFERENCES MACHINE (MNO),
PNAME VARCHAR(20),
PDESC VARCHAR(40));

insert into part values (101,1,’WHEEL’,’ALREADY USED’)
insert into part values (102,2,’GEAR’,’NEW’)
insert into part values (103,3,’WHEEL’,’3 YEAR OLD’)
insert into part values (104,4,’WHEEL’,’PART FOR REPLACE’)
insert into part values (105,5,’HANDEL’,’PART FOR REPLACE’)

1) List all machines having cost > 5000.
select mname
from machine
where mcost>50000

2) Display machine wise part details.
select mname,pno,pname,pdesc
from machine,part
where machine.mno=part.mno

3) Find the name of machine having maximum cost.
select mname
from machine
where mcost=(select max(mcost) from machine);
4) Display the name of all machines having parts ‘wheel’
select mname
from machine,part
where machine.mno=part.mno and pname=’WHEEL’

Q. 6)Consider the following Entities and Relationships
Machine (mno, mname, mtype, mcost)
Part (pno, pnmae, pdesc)
Machine and parts are related with one to many relationships
Constraints : Primary Key, Machine name not be null
Create a RDB in 3NF & construct queries in Oracle 8i
• Increase the cost of machine by 10 %
• Display the machine names whose cost is between 50000 to 70000
• List all the parts of ‘CNC’ machine
• Delete the record from part table whose part name is wheel

CREATE TABLE MACHINE (MNO NUMBER PRIMARY KEY,
MNAME VARCHAR(20),
MTYPE VARCHAR(20),
MCOST NUMBER);

insert into machine values (1,’2wheeler’,’A’,20000);
insert into machine values (2,’3wheeler’,’B’,76000);
insert into machine values (3,’4wheeler’,’c’,110000)
insert into machine values (4,’4wheeler’,’D’,130000)
insert into machine values (5,’CNC’,’E’,23000)

CREATE TABLE PART (PNO NUMBER PRIMARY KEY,
MNO NUMBER REFERENCES MACHINE (MNO),
PNAME VARCHAR(20),
PDESC VARCHAR(40));

insert into part values (101,1,’WHEEL’,’ALREADY USED’)
insert into part values (102,2,’GEAR’,’NEW’)
insert into part values (103,3,’WHEEL’,’3 YEAR OLD’)
insert into part values (104,4,’WHEEL’,’PART FOR REPLACE’)
insert into part values (105,5,’HANDEL’,’PART FOR REPLACE’)

1) Increase the cost of machine by 10 %.
update machine set mcost=mcost+mcost/100*10;
2) Display the machine names whose cost is between 50000 to 70000.
Select mname
from machine
where mcost>=50000 and mcost<=100000;

3) List all the parts of ‘CNC’ machine.
Select distinct pname
from part,machine
where mname=’CNC’;
4) Delete the record from part table whose part name is wheel.
delete from part where pname=’WHEEL’;

Q. 7) Consider the following Entities & Relationships
Customer(cno, cname, city)
Car ( Carno, carmodel, color, price)
Customer and car are related with one to many relationships
Constraints: Primary key, Price should be > 0
Create a RDB in 3NF & write queries in Oracle 8i for following.
• list car model and color of car belong to ‘Mr. Jadhav’
• Display the car details having maximum price.
• Count the number of cars belong to ‘Mr. Patil’
• List customer wise car model

/* Creating Customer Table */
CREATE TABLE CUSTOMER (CNO NUMBER PRIMARY KEY,
CNAME VARCHAR2(20),
CITY VARCHAR2(20) );

insert into customer values (1,’Mr.Bharat’,’Pune’);
insert into customer values (2,’Mr.Nilesh’,’Buldhana’);
insert into customer values (3,’Mr.Jadhav’,’Nagar’);
insert into customer values (4,’Mr.Amol’,’Nagpur’);
insert into customer values (5,’Mr.Patil’,’Mumbai’);

/* Creating Car Table */
CREATE TABLE CAR (CARNO NUMBER PRIMARY KEY,
CNO NUMBER REFERENCES CUSTOMER(CNO),
CARMODEL VARCHAR(20),
COLOR VARCHAR(10),
PRICE NUMBER ) ;

insert into car values (1011 , 1 , ‘ Swipt ‘, ‘ Pink ‘ , 500000 ) ;
insert into car values (1012 , 2 ,’ Maruti Suzuki ‘, ‘ White ‘ , 300000 ) ;
insert into car values (1013 , 3 ,’ Innova ‘, ‘ White ‘, 1000000 ) ;
insert into car values (1014 , 4 ,’ Nano ‘, ‘ Black ‘, 100000 ) ;
insert into car values (1015 , 5,’ Sqorpio ‘, ‘ Red ‘, 800000 ) ;

1) list car model and color of car belong to ‘Mr. Jadhav’.
select carmodel,color
from car,customer
where customer.cno=car.cno and cname=’Mr.Jadhav’

2) Display the car details having maximum price.
select carno,carmodel,color
from car
where price=(select max(price) from car);

3) Count the number of cars belong to ‘Mr. Patil’
select count(carno)
from car,customer
where customer.cno=car.cno and cname=’Mr.Patil’;

4) List customer wise car model.
select cname,carmodel
from customer,Car
where customer.cno=Car.cno;
Q. 8) Consider the following Entities & Relationships
Company(cid,cname,cproduct,state)
Branches(bno, city)
Company and Branches are related with one to many relationships
Constraint: Primary Key, Customer Name should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
• List the product names of company ‘MicroTech’
• List all the companies of product keyboard
• List all the branches along with city of company ‘Lenovo’
• Count the number of branches of company ‘Compaq’

CREATE TABLE COMPANY(CID NUMBER PRIMARY KEY,
CNAME VARCHAR2(20) NOT NULL,
CPRODUCT VARCHAR2(20),
STATE VARCHAR2(20))

insert into Company values(1,’MicroTech’,’Online UPS’,’Karnataka’)
insert into Company values(2,’Lenovo’,’Laptop’,’Delhi’)
insert into Company values(3,’Compaq’,’Laptop’,’Gujrat’)
insert into Company values(4,’I Ball’,’keyboard’,’Maharashtra’)
insert into Company values(5,’Microsoft’,’keyboard’,’Delhi’)

CREATE TABLE BRANCHES(BNO NUMBER PRIMARY KEY,
CITY VARCHAR2(20),
CID NUMBER REFERENCES COMPANY(CID))

insert into Branches values(1, ‘Pune’,1)
insert into Branches values(2, ‘Mumbai’,2)
insert into Branches values(3, ‘Banglor’,3)
insert into Branches values(4, ‘Nagpur’,4)
insert into Branches values(5, ‘Nashik’,3)

1) List the product names of company ‘MicroTech’
select cproduct
from company
where cname=’MicroTech’

2) List all the companies of product keyboard
select cname
from company
where cproduct=’keyboard’

3) List all the branches along with city of company ‘Lenovo’
select city
from company,branches
where company.cid=branches.cid and cname=’Lenovo’
4) Count the number of branches of company ‘Compaq’.
select count(city)”No. of branches”
from company,branches
where company.cid=branches.cid and cname=’Compaq’

Q. 9) Consider the following entities & relationships
Department( dno,dname,HOD,location)
Project (pno, pname, status)
Department and Project are related with one to many relationships
Constraints : Primary Key, Project Status Constraints: C – completed, P-Progressive, I-Incomplete
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Find HOD of Computer Department located in ‘Pune’.
• List all projects of mathematics department which are Incomplete
• Display the Project details of Computer Department
• List department wise project along with status

CREATE TABLE DEPARTMENT( DNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(20),
HOD VARCHAR2(20),
LOCATION VARCHAR2(20))

insert into Department values(1,’Computer’,’Mr.Bhave’,’Pune’)
insert into Department values(2,’Commerce’,’Mrs.Bharti’,’Satara’)
insert into Department values(3, ‘mathematics’,’Mr.Bhatia’,’Nashik’)

CREATE TABLE PROJECT(PNO NUMBER PRIMARY KEY,
PNAME VARCHAR2(20),
STATUS VARCHAR2(5) CHECK(STATUS IN(‘C’,’P’,’I’)),
DNO NUMBER REFERENCES DEPARTMENT(DNO))

insert into Project values(1, ‘Clinic Management’, ‘I’,1)
insert into Project values(2, ‘Bankink’, ‘C’,2)
insert into Project values(3, ‘complex alzebra’, ‘I’,3)
insert into Project values(4, ‘Online Resevation’, ‘C’,1)
insert into Project values(5, ‘Inventory’, ‘I’,2)

1) Find HOD of Computer Department located in ‘Pune’.
select HOD
from department
where location=’Pune’ and dname=’Computer’

2) List all projects of mathematics department which are Incomplete.
select pname
from department,project
where project.dno=department.dno and status=’I’ and dname=’mathematics ‘
3)Display the Project details of Computer Department
select pname,status
from department,project
where project.dno=department.dno and dname=’Computer’
4) List department wise project along with status
select dname,pname,status
from department,project
where project.dno=department.dno order by dname;

Q. 10) Consider the following entities & relationships
Plant ( Plant_Code, Plant_Name,Plant_Cost, Plant_Type)
Nutrients (Ncode, N_Name)
Plant and Nutrients are related with one to many relationship
Constraints : Primary Key, Quantity should be > 0
Plant Type Constraint: F- Flowering and NF – Non-flowering
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Count the number of plants for each plant type
• List all nutrients whose name start with ‘U’
• List all the plants to whom nutrients ‘Urea’ is given
• Display plant wise nutrients given

CREATE TABLE PLANT ( PLANT_CODE NUMBER PRIMARY KEY,
PLANT_NAME VARCHAR2(20),
PLANT_COST NUMBER ,
PLANT_TYPE VARCHAR2(5) CHECK (PLANT_TYPE IN(‘F’,’NF’)))

insert into Plant values ( 1, ‘Wheet’,20000 , ‘NF’)
insert into Plant values ( 2, ‘Bajara’,10000 , ‘F’)
insert into Plant values ( 3, ‘Custurd’,5000 , ‘F’)
insert into Plant values ( 4, ‘Onion’,25000 , ‘NF’)
CREATE TABLE NUTRIENTS (NCODE NUMBER PRIMARY KEY,
N_NAME VARCHAR2(20),
PLANT_CODE NUMBER REFERENCES PLANT(PLANT_CODE))

insert into Nutrients values ( 1, ‘Urea’,1)
insert into Nutrients values ( 2, ‘UKF’,2)
insert into Nutrients values ( 3, ‘Potash’,3)
insert into Nutrients values ( 4, ‘Urea’,4)

1) Count the number of plants for each plant type.
select plant_type,count(plant_name)
from plant
group by plant_type

2)List all nutrients whose name start with ‘U’
select N_Name
from Nutrients
where N_Name like ‘U%’
3)List all the plants to whom nutrients ‘Urea’ is given
select plant_name
from plant,Nutrients
where plant.plant_code=Nutrients.plant_code and N_Name=’Urea’

4) Display plant wise nutrients given
select plant_name,N_Name
from plant,Nutrients
where plant.plant_code=Nutrients.plant_code group by plant_name,N_Name

Q. 11)Consider the following Entities and Relationships
Employee (empno, empname, salary, commission, designation)
Department (deptno, deptname,location)
Relationship between Employee and Department is many-to-one.
Constraints : Primary Key, Salary should be > 0.
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Display all details of employees who are working at ‘Pune’ location
• Display department name wise list of Employees
• Count the number of employees who are working in ‘Computer’ department
• Display maximum salary for every department.

/* Creating Department Table */

CREATE TABLE DEPARTMENT (
DEPTNO NUMBER PRIMARY KEY ,
DEPTNAME VARCHAR2(40) ,
LOCATION VARCHAR2(20));

insert into department values(1,’Computer’,’Pune’) ;
insert into department values(2,’Production’,’Pune’);
insert into department values(3,’Computer’,’Mumbai’) ;
insert into department values(4,’Marketing’,’Mumbai’) ;
insert into department values(5,’Marketing’,’Pune’);
/* Creating Employee Table */

CREATE TABLE EMPLOYEE (EMPNO NUMBER PRIMARY KEY ,
EMPNAME VARCHAR2(40) ,
SALARY NUMBER CHECK (SALARY>0),
COMMISSION NUMBER,
DESIGNATION VARCHAR2(20),
DEPTNO NUMBER REFERENCES DEPARTMENT(DEPTNO) ) ;
insert into employee values(1,’Ramesh’,20000,500,’Manager’,1) ;
insert into employee values(2,’Sandeep’,8000,250,’Employee’,2) ;
insert into employee values(3,’Yogesh’,18000,350,’Employee’,3) ;
insert into employee values(4,’Sanjay’,25000,900,’Manager’,4) ;
insert into employee values(5,’Nilima’,12000,600,’Employee’,5) ;

1) Display all details of employees who are working at ‘Pune’ location
select empno,empname,salary,commission,designation
from department , employee
where department.deptno=employee.deptno
and location=’Pune’;

2) Display department name wise list of Employees.
select deptname,empname
from department , employee
where department.deptno=employee.deptno
order by deptname;
3) Count the number of employees who are working in ‘Computer’ department.
select count(deptname)
from department , employee
where department.deptno=employee.deptno and deptname=’Computer’;

4) Display maximum salary for every department.
select deptname,max(salary)
from Employee,department
where department.deptno=employee.deptno
group by deptname;

Q. 12)Consider the following Entities and Relationships
Movie (mvno, mvname, releaseyear)
Actor (actno, actname)
Relationship between Movie and Actor is many-to-one.
Constraints: Primary Key, releaseyear should be > 0.
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Count all the movie names released in the year 2000.
• Display all actor details of movie ‘Dhoom’
• Display actorwise movie names.
• Display all movies of ‘Abhishek’

CREATE TABLE ACTOR(ACTNO NUMBER PRIMARY KEY,
ACTNAME VARCHAR2(20));

insert into actor values(1,’Abhishek’)
insert into actor values(2,’Hrithik’)
insert into actor values(3,’Kajol’)
insert into actor values(4,’Madhuri’)

CREATE TABLE MOVIE(MVNO NUMBER PRIMARY KEY,
MVNAME VARCHAR2(20),
RELEASEYEAR NUMBER CHECK(RELEASEYEAR>0),
ACTNO NUMBER REFERENCES ACTOR(ACTNO));

insert into movie values(1,’Dhoom’,2003,1)
insert into movie values(2,’DDLG’,1996,3)
insert into movie values(3,’Fiza’,2000,2)
insert into movie values(4,’Devdas’,2002,4)
insert into movie values(5,’Run’,2000,1)

1) Count all the movie names released in the year 2000.
select count(mvname)”No.of Movie”
from movie
where releaseyear=2000;

2) Display all actor details of movie ‘Dhoom’
select actor.actno, actname
from actor,movie
where movie.Actno=actor.actno and mvname=’Dhoom’;

3) Display actorwise movie names.
select actname,mvname
from actor,movie
where movie.actno=actor.actno order by actname;

4) Display all movies of ‘Abhishek’.
select mvname
from actor,movie
where movie.actno=actor.actno and actname=’Abhishek’;

Q. 13)Consider the following Entities and Relationships
Movie (mvno, mvname, releaseyear)
Actor (actno, actname)
Relationship between Movie and Actor is many-to-one.
Constraints: Primary Key, releaseyear should be > 0.
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Display all the movies released after year 2000.
• Count the number of movies in which ‘Hrithik’ has acted.
• Display all the movie names order by released year in ascending order.
• Display movie names released between year 2000 to 2008.
CREATE TABLE ACTOR(ACTNO NUMBER PRIMARY KEY,
ACTNAME VARCHAR2(20));

insert into actor values(1,’Abhishek’)
insert into actor values(2,’Hrithik’)
insert into actor values(3,’Kajol’)
insert into actor values(4,’Madhuri’)
CREATE TABLE MOVIE(MVNO NUMBER PRIMARY KEY,
MVNAME VARCHAR2(20),
RELEASEYEAR NUMBER CHECK(RELEASEYEAR>0),
ACTNO NUMBER REFERENCES ACTOR(ACTNO));

insert into movie values(1,’Dhoom’,2003,1)
insert into movie values(2,’DDLG’,1996,3)
insert into movie values(3,’Fiza’,2000,2)
insert into movie values(4,’Devdas’,2002,4)
insert into movie values(5,’Run’,2000,1)

1) Display all the movies released after year 2000.
select mvname
from movie
where releaseyear>=2000;

2) Count the number of movies in which ‘Hrithik’ has acted.
select count(mvname)”No.of Movie”
from actor,movie
where movie.actno=actor.actno and actname=’Hrithik’

3) Display all the movie names order by released year in ascending order.
select distinct mvname,releaseyear
from actor,movie
order by releaseyear asc;

4) Display movie names released between year 2000 to 2008.
select distinct mvname,releaseyear
from actor,movie
where releaseyear between 2000 and 2008;

Q. 14)Consider the following Entities and Relationships
Employee (empno, empname, salary, commission, designation)
Department (deptno, deptname,location)
Relationship between Employee and Department is many-to-one.
Constraints : Primary Key, Salary should be > 0.
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Display average salary for every designation
• Update commission for every employee by 5 % for all department
• List details of employee who belong to ‘Computer’ department and salary > 20000
• Display all employees details whose designation is manager and salary > 25000

/* Creating Department Table */

CREATE TABLE DEPARTMENT (
DEPTNO NUMBER PRIMARY KEY ,
DEPTNAME VARCHAR2(40) ,
LOCATION VARCHAR2(20));

insert into department values(1,’Computer’,’Pune’) ;
insert into department values(2,’Production’,’Pune’);
insert into department values(3,’Computer’,’Mumbai’) ;
insert into department values(4,’Marketing’,’Mumbai’) ;
insert into department values(5,’Marketing’,’Pune’);
/* Creating Employee Table */

CREATE TABLE EMPLOYEE (EMPNO NUMBER PRIMARY KEY ,
EMPNAME VARCHAR2(40) ,
SALARY NUMBER CHECK (SALARY>0),
COMMISSION NUMBER,
DESIGNATION VARCHAR2(20),
DEPTNO NUMBER REFERENCES DEPARTMENT(DEPTNO) ) ;
insert into employee values(1,’Ramesh’,20000,500,’Manager’,1) ;
insert into employee values(2,’Sandeep’,8000,250,’Employee’,2) ;
insert into employee values(3,’Yogesh’,18000,350,’Employee’,3) ;
insert into employee values(4,’Sanjay’,25000,900,’Manager’,4) ;
insert into employee values(5,’Nilima’,12000,600,’Employee’,5)

1) Display average salary for every designation.
select designation,round(avg(salary),2)
from Employee,department where
department.deptno=employee.deptno
group by designation;
2) Update commission for every employee by 5 % for all department.
update employee set salary=salary+salary*0.5
3) List details of employee who belong to ‘Computer’ department and salary > 20000.
select empno, empname, salary , commission , designation
from employee, department
where employee.deptno=department.deptno
and deptname=’Computer’
and salary >20000;

4) Display all employees details whose designation is manager and salary > 25000
select empno, empname, salary , commission
from employee, department
where employee.deptno=department.deptno
and designation =’Manager’
and salary >25000;

Q. 15)Consider the following Entities & Relationships
Politician(pno,pname,pdesc)
Party(party_code,party_name)
Politician & party are related with many-to-one.
Constraints : Primary key, Foreign key, Party_name Not NULL
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Display party wise politician details
• Display details of all politician of party ‘BJP’
• Count the total number of politicians for each party
• Count the number of politicians having political description as ‘AP’

CREATE TABLE PARTY(PARTY_CODE NUMBER PRIMARY KEY ,
PARTY_NAME VARCHAR2(20) ) ;

insert into party values(1,’Shivsena’)
insert into party values(2,’MNS’)
insert into party values(3,’BJP’)
insert into party values(4,’NCP’)
insert into party values(5,’BSP’)

CREATE TABLE POLITICIAN(PNO NUMBER PRIMARY KEY,
PNAME VARCHAR2(20),
PDESC VARCHAR2(20),
PARTY_CODE REFERENCES PARTY(PARTY_CODE) ) ;

insert into Politician values(1,’Bal Thakare’,’HC’,1)
insert into Politician values(2,’Raj Thakare’,’PD’,2)
insert into Politician values(3,’Adwani’,’AP’,3)
insert into Politician values(4,’Gandhi’,’AP’,4)
insert into Politician values(5,’Uddhav Thakare’,’AP’,1)

1) Display party wise politician details.
Select party_name,pname
from party,Politician
where party.party_code=politician.party_code
group by party_name,pname
order by party_name

2) Display details of all politician of party ‘BJP’
select Pno,pname,pdesc
from party,Politician
where party.party_code=politician.party_code and party_name =’BJP’;

3) Count the total number of politicians for each party.
Select party_name,count(pno)”No. of Politician”
from party,Politician
where party.party_code=politician.party_code
group by party_name

4) Count the number of politicians having political description as ‘AP’.
select count(pno)”pdesc as AP ”
from Politician
where pdesc=’AP’;

Q. 16) Consider the following Entities & Relationships
Person (pno,pname,birthdate,income)
Area (Ano,aname,atype);
Relationship between Person and area is many to one
Constraints :Primary key, Area type may be either rural or urban.
Create a RDB in 3NF & write queries in Oracle 8i for following.
• List the name of all person living in urban area
• List details of all persons whose name start with alphabet ‘M’
• Display the details of person having maximum Income
• Count the number of persons from each type of area.

CREATE TABLE AREA (ANO NUMBER PRIMARY KEY,
ANAME VARCHAR2(20),
ATYPE VARCHAR2(20),
CHECK (ATYPE IN (‘RURAL’,’URBAN’)));

insert into Area values(1,’Ale’,’rural’);
insert into Area values(2,’Nigdi’,’urban’);
insert into Area values(3,’mumbai’,’urban’);
insert into Area values(4,’Belhe’,’rural’);
insert into Area values(5,’Vadgaon’,’rural’);

CREATE TABLE PERSON (PNO NUMBER PRIMARY KEY,
PNAME VARCHAR2(20),
BIRTHDATE VARCHAR2(20),
INCOME NUMBER,
ANO NUMBER REFERENCES AREA(ANO))

insert into Person values(1,’Mahesh’,’13-08-1985′,20000, 1)
insert into Person values(2,’Prakash’,’26-05-1983′,13000, 2)
insert into Person values(3,’Sonali’,’12-01-1987′,9500, 3)
insert into Person values(4,’Samir’,’19-11-1991′,12500, 4)
insert into Person values(5,’Sheetal’,’23-06-1989′,17000, 5)
1) List the name of all person living in urban area.
select pname
from person, area
where person.ano=area.ano and atype=’urban’
2) List details of all persons whose name start with alphabet ‘M’
select pname,birthdate,income
from person
where pname like ‘M%’
3) Display the details of person having maximum Income.
select pname,birthdate,income
from person
where income=(select max(income) from person)

4) Count the number of persons from each type of area.
select atype,count(aname)
from area
group by atype;

Q. 17)Consider the following Entities and Relationships
Employee (empno, empname, city, deptname)
Project (pno, pname, status)
Employee and Project are related with many to many relationships with descriptive attribute no_of_days employee worked on that project
Constraints : Primary Key, Project Status Constraints: C – completed, P-Progressive, I-Incomplete
Create a RDB in 3NF & write queries in Oracle 8i for following.
• List the names of employees working on projects having status “Incomplete”
• Display all project names and total number of employees who are working on that project.
• List the names of the employees who are working on project for more than 20 days.
• Display project wise Employee details

CREATE TABLE EMPLOYEE (EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(20),
CITY VARCHAR2(20),
DEPTNAME VARCHAR2(20))

insert into employee values(1,’nilesh’,’pune’,’computer’)
insert into employee values(2,’bharat’,’sangli’,’production’)
insert into employee values(3,’rajashree’,’nashik’,’computer’)
insert into employee values(4,’snehal’,’junnar’,’inventory’)
insert into employee values(5,’sanjay’,’sangamner’,’production’)
CREATE TABLE PROJECT (PNO NUMBER PRIMARY KEY,
PNAME VARCHAR2(20),
STATUS VARCHAR2(20)
CHECK (STATUS IN (‘C’,’P’,’I’ ) ) )

insert into project values(1,’banking’,’p’)
insert into project values(2,’online reservation’,’c’)
insert into project values(3,’hospital management’,’i’)

CREATE TABLE EMP_PROJ (EMPNO NUMBER REFERENCES EMPLOYEE(EMPNO)PRIMARY KEY,
PNO NUMBER REFERENCES PROJECT(PNO),
NO_OF_DAYS NUMBER );

insert into emp_proj values(1,1,25)
insert into emp_proj values(2,2,17)
insert into emp_proj values(3,3,12)
insert into emp_proj values(4,1,20)
insert into emp_proj values(5,3,5)

1) List the names of employees working on projects having status “Incomplete”.
select ename
from employee,emp_proj,project
where employee.empno=emp_proj.empno and project.pno=emp_proj.pno
and project.status=’I’;

2) Display all project names and total number of employees who are working on that project.
select pname, count(employee.empno)
from employee,emp_proj,project
where employee.empno=emp_proj.empno and project.pno=emp_proj.pno
group by pname;

3) List the names of the employees who are working on project for more than 20 days.
select pname,ename
from employee,emp_proj,project
where employee.empno=emp_proj.empno and project.pno=emp_proj.pno and no_of_days>20;

4) Display project wise Employee details .
select pname,ename
from employee,emp_proj,project
where employee.empno=emp_proj.empno and project.pno=emp_proj.pno order by pname;

Q. 18)Consider the following Entities and Relationships
Doctor(dno, dname, city)
Hospital (hno, hname, hcity)
The relation between Doctor and Hospital is many-to-many.
Constraints : Primary Key, City should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Display all the details of hospitals located in ‘Pune’ city.
• Display hospital wise doctor’s details.
• Count the number of doctors who are visiting to ‘KEM’ Hospital.
• Display the hospital names whose first character is ‘S’.

/* Creating doctor table */
CREATE TABLE DOCTOR(DNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(20),
CITY VARCHAR2(20) NOT NULL ) ;

Insert into doctor values(1, ‘ Dr.Nilesh ’,’ Nagpur ’ ) ;
Insert into doctor values(2, ‘ Dr.Bharat ’ , ’ Pune ’ ) ;
Insert into doctor values(3, ‘ Dr.Raj ’,’ Nashik ’) ;
Insert into doctor values(4, ‘ Dr.Rupali ’ , ’ Sangali ’) ;

/* Creating hospital table */

CREATE TABLE HOSPITAL(HNO NUMBER PRIMARY KEY,
HNAME VARCHAR2(20),
HCITY VARCHAR2(20),
DNO NUMBER REFERENCES DOCTOR(DNO) ) ;

Insert into hospital values(1 ,’ Sancheti ’, ’Pune’, 1)
Insert into hospital values(2,’ Sasun ’, ’ Pune ’, 1)
Insert into hospital values(3,’ Lokmanya ’,’ Nashik ’, 3)
Insert into hospital values(4,’ Rubi ’,’ Nashik ’, 2 )
Insert into hospital values(5,’ KEM ’, ’ Pune ’, 4)

1) Display all the details of hospitals located in ‘Pune’ city.
select hno,hname,dname
from hospital, doctor
where hcity=’ Pune ‘ and doctor.dno=hospital.dno

2) Display hospital wise doctor’s details.
select hname,doctor.dno,dname,city
from hospital,doctor
where doctor.dno=Hospital.dno;

3) Count the number of doctors who are visiting to ‘KEM’ Hospital.
select count(hno)
from hospital,doctor
where hospital.dno=doctor.dno and hospital.hname=’KEM’

4) Display the hospital names whose first character is ‘S’.
select hno,hname
from hospital
where hname like ‘S%’;

Q. 19) Consider the following Entities and Relationships
Student (rollno, name, marks, class) Teacher (tno, tname)
The relation between Student and Teacher is many-to-many with subject as descriptive attribute.
Constraints : Primary Key, Class has to be FY, SY or TY.
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Count the number of teachers who are teaching subject ‘C’
• Display all details of teachers who is teaching subject ‘DBMS’.
• Display class wise number of students.
• List the teacher names who are teaching to student ‘Amar’.

CREATE TABLE STUDENT (ROLLNO NUMBER PRIMARY KEY ENABLE,
NAME VARCHAR2(20),
MARKS NUMBER,
CLASS VARCHAR2(2), CHECK (CLASS IN (‘FY’,’SY’,’TY ‘ )) );

insert into student values(1,’bharat’,59,’ty’)
insert into student values(2,’nilesh’,90,’sy’)
insert into student values(3,’rupali’,75,’fy’)
insert into student values(4,’raj’,65,’ty’)
insert into student values(5,’amar’,72,’sy’)

CREATE TABLE TEACHER (TNO NUMBER PRIMARY KEY ENABLE,
TNAME VARCHAR2(20) )

insert into teacher values(1,’dr.ram’)
insert into teacher values(2,’prof.shirish’)
insert into teacher values(3,’prof.nitin’)
insert into teacher values(4,’dr.gaikwad’)

CREATE TABLE STUD_TEACHER
(ROLLNO NUMBER REFERENCES STUDENT(ROLLNO) PRIMARY KEY ENABLE,
TNO NUMBER REFERENCES TEACHER(TNO), SUBJECT VARCHAR2(20) );

insert into stud_teacher values(1,1,’dbms’)
insert into stud_teacher values(2,2,’c’)
insert into stud_teacher values(3,3,’c++’)
insert into stud_teacher values(4,4,’hrm’)
insert into stud_teacher values(5,3,’dbms’)

1)Count the number of teachers who are teaching subject ‘C’
select count(teacher.tno)”No of Teacher”
from teacher,student,stud_teacher
where teacher.tno=stud_teacher.tno and student.rollno=stud_teacher.rollno
and subject=’C’;

2)Display all details of teachers who is teaching subject ‘DBMS’.
select TEACHER.TNO,TNAME
from teacher,student,stud_teacher
where teacher.tno=stud_teacher.tno and student.rollno=stud_teacher.rollno
and subject=’DBMS’;

3) Display class wise number of students.
select CLASS, COUNT(NAME)
from STUDENT
GROUP BY CLASS
ORDER BY CLASS;

4) List the teacher names who are teaching to student ‘Amar’.
select TNAME
from teacher,student,stud_teacher
where teacher.tno=stud_teacher.tno and student.rollno=stud_teacher.rollno
and STUDENT.NAME=’Amar’;

Q. 20)Consider the following Entities and Relationships
Book ( bno, bname, pubname)
Author (ano, aname)
The relation between Book and Author is many-to-many.
Constraints : Primary Key, Aname and Pubname should not be null.
Create a RDB in 3NF & construct queries in Oracle 8i.
• Count number of books of each publisher.
• Display all details of the book written by ‘Kanetkar’.
• Display Author-wise list of books.
• Display the book-name whose author-name is ‘Korth’ and publisher name is ‘BPB’.
CREATE TABLE BOOK(BNO NUMBER PRIMARY KEY,
BNAME VARCHAR2(20),
PUBNAME VARCHAR2(20) NOT NULL ENABLE)

insert into book values(1,’DBMS’,’BPB’)
insert into book values(2,’C++’,’TATA HILL’)
insert into book values(3,’C’,’BPB’)
insert into book values(4,’DBMS’,’Nirali’)
CREATE TABLE AUTHOR(ANO NUMBER PRIMARY KEY,
ANAME VARCHAR2(20) NOT NULL ENABLE);
insert into author values(1,’Korth’)
insert into author values(2,’Balguruswamy’)
insert into author values(3,’Kanetkar’)
insert into author values(4,’Gupta’)

CREATE TABLE BOOK_AUTHOR(BNO NUMBER REFERENCES BOOK(BNO),
ANO NUMBER REFERENCES AUTHOR(ANO))

insert into book_author values(1,1)
insert into book_author values(2,2)
insert into book_author values(3,3)
insert into book_author values(4,3)

1) Count number of books of each publisher.
select distinct pubname,count(pubname)
from book
group by pubname

2) Display all details of the book written by ‘Kanetkar’.
select book.bno,bname,pubname
from book,author,book_author
where aname=’Kanetkar’
and author.ano=book_author.ano
and book.bno=book_author.bno;

3) Display Author-wise list of books.
select aname,bname
from book,author,book_author
where author.ano=book_author.ano
and book.bno=book_author.bno
order by aname;

4) Display the book-name whose author-name is ‘Korth’ and publisher name is ‘BPB’.
select bname
from book,author,book_author
where author.ano=book_author.ano
and book.bno=book_author.bno and
aname=’Korth’ and pubname=’BPB’;
Q. 21)Consider the following Entities and Relationships
Doctor(dno, dname, city)
Hospital (hno, hname, hcity)
The relation between Doctor and Hospital is many-to-many.
Constraints : Primary Key, City should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Display doctor wise hospital details.
• Display the doctor details that are living in ‘Pune’ city.
• Count the number hospitals to which ‘Dr. Apte’ is visiting.
• Display all the details of hospitals not located in ‘Pune’ city.
/* Creating doctor table */
CREATE TABLE DOCTOR(DNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(20),
CITY VARCHAR2(20) NOT NULL ) ;

insert into doctor values(1, ‘ dr.nilesh ’,’ nagpur ’ ) ;
insert into doctor values(2, ‘ dr.bharat ’ , ’ pune ’ ) ;
insert into doctor values(3, ‘ dr.raj ’,’ nashik ’) ;
insert into doctor values(4, ‘ dr.rupali ’ , ’ sangali ’) ;
/* Creating hospital table */

CREATE TABLE HOSPITAL(HNO NUMBER PRIMARY KEY,
HNAME VARCHAR2(20),
HCITY VARCHAR2(20),
DNO NUMBER REFERENCES DOCTOR(DNO) ) ;

Insert into hospital values(1 ,’ Sancheti ’, ’Pune’, 1)
Insert into hospital values(2,’ Sasun ’, ’ Pune ’, 1)
Insert into hospital values(3,’ Lokmanya ’,’ Nashik ’, 3)
Insert into hospital values(4,’ Rubi ’,’ Nashik ’, 2 )
Insert into hospital values(5,’ KEM ’, ’ Pune ’, 4)

1) Display doctor wise hospital details.
select dname,hno,hname,hcity
from doctor,hospital
where doctor.dno=Hospital.dno ;

2) Display the doctor details that are living in ‘Pune’ city.
select doctor.dno, dname, hno, hname, hcity
from doctor,hospital
where doctor.city=’Pune’ and doctor.dno=hospital.dno;

3) Count the number hospitals to which ‘Dr. Apte’ is visiting.
select count(doctor.dno)
from hospital,doctor
where hospital.dno=doctor.dno and doctor.dname=’Dr.Nilesh’

4) Display all the details of hospitals not located in ‘Pune’ city.
select hno,hname,dname
from hospital, doctor
where hcity<>’Pune’ and doctor.dno=hospital.dno;
Q. 22)Consider the following Entities and Relationships
Doctor (dno, dname, city)
Patient (opdno, pat_name, addr, disease)
The relation between Patient and Doctor is many-to-many.
Constraints : Primary Key, Address should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Find the names of patients who are treated by ‘Dr. Apte’.
• Display doctor wise details of patient
• Count the number of patients suffering from ‘Asthma’.
• Find the name of doctors who are treating the ‘Diabetics’ patient.
CREATE TABLE DOCTOR(DNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(20), CITY VARCHAR2(20) NOT NULL ) ;

Insert into doctor values(1, ‘ Dr.Apte ’,’ Nagpur ’ ) ;
Insert into doctor values(2, ‘ Dr.Bharat ’ , ’ Pune ’ ) ;
Insert into doctor values(3, ‘ Dr.Nilesh ’,’ Nashik ’) ;
Insert into doctor values(4, ‘ Dr.Rupali ’ , ’ Sangali ’) ;

CREATE TABLE PATIENT (OPDNO NUMBER PRIMARY KEY,
PAT_NAME VARCHAR2(20),
ADDR VARCHAR2(20) NOT NULL,
DISEASE VARCHAR2(20) );

Insert into patient values(1,’Ramesh’,’Pune’,’Asthma’)
Insert into patient values(2,’Sandeep’,’Shirur’,’Diabetics’ )
Insert into patient values(3,’Amol’,’Satara’,’Asthma’)
Insert into patient values(4,’Pranav’,’Sangli’,’Cancer’)
CREATE TABLE DOCT_PAT (DNO NUMBER REFERENCES DOCTOR(DNO) PRIMARY KEY ,
OPDNO NUMBER REFERENCES PATIENT(OPDNO))

Insert into doct_pat values(1,1)
Insert into doct_pat values(2,2)
Insert into doct_pat values(3,3)
Insert into doct_pat values(4,2)

1) Find the names of patients who are treated by ‘Dr. Apte’.
select pat_name
from patient, doctor, doct_pat
where doctor.dno=doct_pat.dno and patient.opdno=doct_pat.opdno and dname=’Dr.Apte’;

2) Display doctor wise details of patient.
select dname, pat_name
from patient, doctor, doct_pat
where doctor.dno=doct_pat.dno and patient.opdno=doct_pat.opdno
group by dname, pat_name;
3)Count the number of patients suffering from ‘Asthma’.
select count(opdno)
from patient
where disease=’Asthma’;

4) Find the name of doctors who are treating the ‘Diabetics’ patient.
select dname
from patient, doctor, doct_pat
where doctor.dno=doct_pat.dno and patient.opdno=doct_pat.opdno
and disease =’Diabetics’;

Q. 23)Consider the following Entities & Relationships
Student (sno,sname,city,class)
Subject (sub_no,sub_name)
Students & subjects are related with many-to-many with attribute marks.
Constraints: Primary key constraints and sname should not be null,
Class has to be FY, SY or TY.
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Count the number of students for each class
• Display the name of all students who are studying in class ‘TY’
• Display subject wise name of all students
• Display the students from ‘Pune’ city

CREATE TABLE STUDENT (SNO NUMBER PRIMARY KEY ENABLE,
SNAME VARCHAR2(20),
CITY VARCHAR2(10),
CLASS VARCHAR2(2),
CHECK (class IN (‘FY’,’SY’,’TY’)) )

insert into student values(1,’ Sachin ‘,’ Nashik ‘,’SY’)
insert into student values(2,’ Priti ‘,’ Nagar ‘,’FY’)
insert into student values(3,’ Raj ‘,’ Nashik ‘,’TY’)
insert into student values(4,’ Anita ‘,’ Nagpur ‘,’SY’)
insert into student values(5,’ Rajendra ‘,’ Pune ‘,’TY’)
insert into student values(6,’ Ajay ‘,’ Pune ‘,’FY’)

CREATE TABLE SUBJECT (SUB_NO NUMBER PRIMARY KEY ENABLE,
SUB_NAME VARCHAR2(20) )

insert into subject values(1,’DBMS’)
insert into subject values(2,’DS’)
insert into subject values(3,’DBMS’)
insert into subject values(4,’VB’)
insert into subject values(5,’C++’)
insert into subject values(6,’DBMS’)
CREATE TABLE STUD_SUB( SNO NUMBER REFERENCES STUDENT(SNO) PRIMARY KEY,
SUB_NO NUMBER REFERENCES SUBJECT(SUB_NO),
MARKS NUMBER );

insert into stud_sub values(1,1,75)
insert into stud_sub values(2,2,65)
insert into stud_sub values(3,3,85)
insert into stud_sub values(4,4,59)
insert into stud_sub values(5,5,72)
insert into stud_sub values(6,6,39)

1) Count the number of students for each class.
Select class, count(Student.sno)
from student,subject, Stud_sub
where student.sno=stud_sub.sno and subject.sub_no=stud_sub.sub_no
group by class

2) Display the name of all students who are studying in class ‘TY’.
Select sname
from student,subject, Stud_sub
where student.sno=stud_sub.sno and subject.sub_no=stud_sub.sub_no
and class=’TY’

3) Display subject wise name of all students.
Select sub_name,sname
from student,subject, Stud_sub
where student.sno=stud_sub.sno and subject.sub_no=stud_sub.sub_no
group by sub_name, sname;

4) Display the students from ‘Pune’ city.
Select sname
from student,subject, Stud_sub
where student.sno=stud_sub.sno and subject.sub_no=stud_sub.sub_no
and city=’Pune’

Q. 24)Consider the following Entities & Relationships
Student (sno,sname,city,class)
Subject (sub_no,sub_name)
Students & subjects are related with many-to-many with attribute marks.
Constraints: Primary key constraints and sname should not be null,
Class has to be FY, SY or TY.
Create a RDB in 3NF & write queries in Oracle 8i for following.

• Count the number of students studying in ‘FY’ having marks between 60 to 70.
• Display student wise subject details.
• Display the details of all students who are studying ‘DBMS’ subject.
• Update the marks of students to 40 who have scored marks 39.

CREATE TABLE STUDENT (SNO NUMBER PRIMARY KEY ENABLE,
SNAME VARCHAR2(20),
CITY VARCHAR2(10),
CLASS VARCHAR2(2),
CHECK (class IN (‘FY’,’SY’,’TY’)) )

insert into student values(1,’ Sachin ‘,’ Nashik ‘,’SY’)
insert into student values(2,’ Priti ‘,’ Nagar ‘,’FY’)
insert into student values(3,’ Raj ‘,’ Nashik ‘,’TY’)
insert into student values(4,’ Anita ‘,’ Nagpur ‘,’SY’)
insert into student values(5,’ Rajendra ‘,’ Pune ‘,’TY’)
insert into student values(6,’ Ajay ‘,’ Pune ‘,’FY’)

CREATE TABLE SUBJECT (SUB_NO NUMBER PRIMARY KEY ENABLE,
SUB_NAME VARCHAR2(20) )

insert into subject values(1,’DBMS’)
insert into subject values(2,’DS’)
insert into subject values(3,’DBMS’)
insert into subject values(4,’VB’)
insert into subject values(5,’C++’)
insert into subject values(6,’DBMS’)
CREATE TABLE STUD_SUB( SNO NUMBER REFERENCES STUDENT(SNO) PRIMARY KEY,
SUB_NO NUMBER REFERENCES SUBJECT(SUB_NO),
MARKS NUMBER );

insert into stud_sub values(1,1,75)
insert into stud_sub values(2,2,65)
insert into stud_sub values(3,3,85)
insert into stud_sub values(4,4,59)
insert into stud_sub values(5,5,72)
insert into stud_sub values(6,6,39)
1) Count the number of students studying in ‘FY’ having marks between 60 to 70.
Select count(student.sno)”No of Student”
from student, Stud_sub
where student.class=’FY’ and marks between 60 and 70
and student.sno=stud_sub.sno

2) Display student wise subject details.
Select sname,sub_name
from student,subject, Stud_sub
where student.sno=stud_sub.sno and subject.sub_no=stud_sub.sub_no
group by sname, sub_name

3) Display the details of all students who are studying ‘DBMS’ subject.
Select student.sno,sname,city,class
from student,subject, Stud_sub
where student.sno=stud_sub.sno and subject.sub_no=stud_sub.sub_no
and sub_name=’DBMS’

4) Update the marks of students to 40 who have scored marks 39.
update stud_sub set marks=40 where marks=39;

Q. 25)Consider the following Entities & Relationships
Student (sno,sname,city,class)
Subject (sub_no,sub_name)
Students & subjects are related with many-to-many with attribute marks.
Constraints: Primary key constraints and sname should not be null,
Class has to be FY, SY or TY.
Create a RDB in 3NF & write queries in Oracle 8i for following
• Display the city wise student’s details
• Find maximum marks scored in subject ‘DBMS’
• Display all the details of students who have scored marks less than 60
• Display students who are studying in class ‘SY’ and living in the city ‘Nagpur’
CREATE TABLE STUDENT (SNO NUMBER PRIMARY KEY ENABLE,
SNAME VARCHAR2(20),
CITY VARCHAR2(10),
CLASS VARCHAR2(2),
CHECK (class IN (‘FY’,’SY’,’TY’)) )

insert into student values(1,’ Sachin ‘,’ Nashik ‘,’SY’)
insert into student values(2,’ Priti ‘,’ Nagar ‘,’FY’)
insert into student values(3,’ Raj ‘,’ Nashik ‘,’TY’)
insert into student values(4,’ Anita ‘,’ Nagpur ‘,’SY’)
insert into student values(5,’ Rajendra ‘,’ Pune ‘,’TY’)
insert into student values(6,’ Ajay ‘,’ Pune ‘,’FY’)

CREATE TABLE SUBJECT (SUB_NO NUMBER PRIMARY KEY ENABLE,
SUB_NAME VARCHAR2(20) )

insert into subject values(1,’DBMS’)
insert into subject values(2,’DS’)
insert into subject values(3,’DBMS’)
insert into subject values(4,’VB’)
insert into subject values(5,’C++’)
insert into subject values(6,’DBMS’)
CREATE TABLE STUD_SUB( SNO NUMBER REFERENCES STUDENT(SNO) PRIMARY KEY,
SUB_NO NUMBER REFERENCES SUBJECT(SUB_NO),
MARKS NUMBER );

insert into stud_sub values(1,1,75)
insert into stud_sub values(2,2,65)
insert into stud_sub values(3,3,85)
insert into stud_sub values(4,4,59)
insert into stud_sub values(5,5,72)
insert into stud_sub values(6,6,39)
1) Display the city wise student’s details.
Select city,student.sno,sname,class
from student,subject, Stud_sub
group by city,student.sno,sname , class
order by city;

2) Find maximum marks scored in subject ‘DBMS’
Select max(marks)
from subject,stud_sub
where sub_name=’DBMS’

3)Display all the details of students who have scored marks less than 60.
Select student.sno,sname,city,class
from student,subject, Stud_sub
where student.sno=stud_sub.sno and subject.sub_no=stud_sub.sub_no and marks<60

4) Display students who are studying in class ‘SY’ and living in the city ‘Nagpur’.
Select student.sno,sname
from student,subject, Stud_sub
where student.sno=stud_sub.sno and subject.sub_no=stud_sub.sub_no
and class=’SY’ and City=’Nagpur’

Q. 26)Consider the following Entities & Relationships
Wholesaler(wno,wname,addr,city)
Product(pno,pname)
Wholesaler & Product are related with many-to-many relationship.
Constraints : Primary key, pname should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Display wholesaler wise product details
• Count the number of products sold by wholesaler ‘Dev Enterprise’
• Display the details of wholesalers living in the ‘Mumbai’ City
• Display the wholesaler details of product keyboard

CREATE TABLE WHOLESALER(WNO NUMBER PRIMARY KEY,
WNAME VARCHAR2(20),
ADDR VARCHAR2(20),
CITY VARCHAR2(20))

insert into Wholesaler values(1,’Sanjay’,’21,MG Road’,’Nagar’)
insert into Wholesaler values(2,’Sangeeta’,’JM Road’,’Shivajinagar’)
insert into Wholesaler values(3,’Omkar’,’Near KTHM college’,’Nashik’)
insert into Wholesaler values(4,’Rupali’,’Behind SBI ATM’,’Mumbai’)
insert into Wholesaler values(5,’Dev Enterprise’,’Lakshmi Road’,’Mumbai’)
CREATE TABLE PRODUCT(PNO NUMBER PRIMARY KEY,
PNAME VARCHAR2(20) NOT NULL)

insert into product values(1,’Monitor’)
insert into product values(2,’Keyboard’)
insert into product values(3,’Mouse’)
insert into product values(4,’Assemble PC’)
insert into product values(5,’UPS’)

CREATE TABLE WHOL_PROD(WNO NUMBER REFERENCES WHOLESALER(WNO) PRIMARY KEY ,
PNO NUMBER REFERENCES PRODUCT(PNO))

insert into whol_prod values(1,1)
insert into whol_prod values(2,2)
insert into whol_prod values(3,3)
insert into whol_prod values(4,2)
insert into whol_prod values(5,5)

1) Display wholesaler wise product details.
select pname,wname, addr, city
from product, Wholesaler,whol_prod
where product.pno=whol_prod.pno and Wholesaler.wno=whol_prod.wno
order by pname;

2) Count the number of products sold by wholesaler ‘Dev Enterprise’.
select count(pname)”No. of product”
from product, Wholesaler,whol_prod
where product.pno=whol_prod.pno and Wholesaler.wno=whol_prod.wno
and wname=’Dev Enterprise’

3) Display the details of wholesalers living in the ‘Mumbai’ City.
Select wname, addr, city
from Wholesaler
where city=’Mumbai’

4) Display the wholesaler details of product keyboard.
Select wname, addr, city
from product, Wholesaler,whol_prod
where product.pno=whol_prod.pno and Wholesaler.wno=whol_prod.wno
and pname=’Keyboard’
Q. 27)Consider the following Entities & Relationships
Item (item_no,name,quantity)
Sup(no,name,addr,city,phone)
Item & sup are related with many-to-many relationships with rate, discount.
Constraints: Primary key and item qty > 5 and rate > 0.
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Find the rate and discount of the item keyboard
• Display item names in ascending order of quantity
• Display the details of all suppliers from ‘Pune’ city
• Count the number of items supplied by supplier ‘Mr. Bhatia’
CREATE TABLE ITEM(ITEM_NO NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
QUANTITY NUMBER CHECK(QUANTITY>5))

alter table Item add(rate number check(rate>0))

insert into item values(1,’keyboard’,15,600)
insert into item values(2,’Mouse’,550,300)
insert into item values(3,’Monitor’,50,4200)
insert into item values(4,’Hard disk’,40,2500)

CREATE TABLE SUP(NO NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
ADDR VARCHAR2(40),
CITY VARCHAR2(10),
PHONE NUMBER(12))

insert into Sup values(1,’Mr. Bhatia’,’ABC chowk’,’Pune’,9860940890)
insert into Sup values(2,’Mr. Patil’,’Shaniwar peth’,’Nashik’,9963578123)
insert into Sup values(3,’Mr. Jadhav’,’JM Road’,’Pune’,9922364563)
insert into Sup values(4,’Mr. Deshpande’,’Tilak Road,Bandra’,’Mumbai’,9270569878)
insert into Sup values(5,’Mr. Chaudhari’,’Apte Marg’,’Nashik’,9456569231)

CREATE TABLE ITEM_SUP(SNO NUMBER PRIMARY KEY REFERENCES SUP(NO),
I_NO NUMBER REFERENCES ITEM(ITEM_NO),
DISCOUNT NUMBER)

insert into item_Sup values(1,1,10)
insert into item_Sup values(2,2,5)
insert into item_Sup values(3,3,7)
insert into item_Sup values(4,4,6)
insert into item_Sup values(5 , 1, 6.5)

1)Find the rate and discount of the item keyboard
select rate , discount
from item, sup, item_sup
where item.item_no=item_sup.i_no and
sup.no=item_sup.sno and
item.name=’keyboard’

2) Display item names in ascending order of quantity.
select name
from item
order by quantity ASC

3) Display the details of all suppliers from ‘Pune’ city.
Select no,sup.name,addr
from sup
where city=’Pune’

4)Count the number of items supplied by supplier ‘Mr. Bhatia’.
select count(item_no)
from item, sup,item_sup
where item.item_no=item_sup.i_no and
sup.no=item_sup.sno and
sup.name=’Mr. Bhatia’

Q. 28)Consider the following Entities & Relationships
Item (item_no,name,quantity)
Sup(no,name,addr,city,phone)
Item & sup are related with many-to-many relationships with rate, discount.
Constraints: Primary key and item qty > 5 and rate > 0.
Create a RDB in 3NF & write queries in Oracle 8i for following.
• List all the details of items having quantity > 500
• Increase the 5% rate of the item mouse
• Display the item details supplied by the supplier ‘Mr. Patil’
• Count the number of suppliers from each city

CREATE TABLE ITEM(ITEM_NO NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
QUANTITY NUMBER CHECK(QUANTITY>5))

alter table Item add(rate number check(rate>0))

insert into item values(1,’keyboard’,15,600)
insert into item values(2,’Mouse’,550,300)
insert into item values(3,’Monitor’,50,4200)
insert into item values(4,’Hard disk’,40,2500)

CREATE TABLE SUP(NO NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
ADDR VARCHAR2(40),
CITY VARCHAR2(10),
PHONE NUMBER(12))

insert into Sup values(1,’Mr. Bhatia’,’ABC chowk’,’Pune’,9860940890)
insert into Sup values(2,’Mr. Patil’,’Shaniwar peth’,’Nashik’,9963578123)
insert into Sup values(3,’Mr. Jadhav’,’JM Road’,’Pune’,9922364563)
insert into Sup values(4,’Mr. Deshpande’,’Tilak Road,Bandra’,’Mumbai’,9270569878)
insert into Sup values(5,’Mr. Chaudhari’,’Apte Marg’,’Nashik’,9456569231)

CREATE TABLE ITEM_SUP(SNO NUMBER PRIMARY KEY REFERENCES SUP(NO),
I_NO NUMBER REFERENCES ITEM(ITEM_NO),
DISCOUNT NUMBER)

insert into item_Sup values(1,1,10)
insert into item_Sup values(2,2,5)
insert into item_Sup values(3,3,7)
insert into item_Sup values(4,4,6)
insert into item_Sup values(5 , 1, 6.5)

1) List all the details of items having quantity > 500.
select item_no,name
from item
where quantity>500;
2) Increase the 5% rate of the item mouse.
update item set rate=rate+rate*0.5 where name=’Mouse’

3) Display the item details supplied by the supplier ‘Mr. Patil’.
select item_no,item.name,discount
from item, sup,item_sup
where item.item_no=item_sup.i_no and
sup.no=item_sup.sno and
sup.name=’Mr. Patil’

4) Count the number of suppliers from each city.
select sup.city, count(no)
from sup
group by city;
Q. 29) Consider the following Entities & Relationships
Teacher (tno, tname, collegename,dept)
Exam(examtno, examname)
Teacher and Exam related with many to many relationships.
Constraints: Primary key, examname should not be null
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Find department of teacher ‘Mrs. Jadhav’ of D.Y.Patil College
• List all teachers who have given SET examination.
• Count the number of teachers of ‘MJC’ College
• List College wise teacher name with examination given by teacher

CREATE TABLE TEACHER (TNO NUMBER PRIMARY KEY,
TNAME VARCHAR2(20),
COLLEGENAME VARCHAR2(40),
DEPT VARCHAR2(20))

insert into Teacher values(1,’Mrs. Jadhav’,’D.Y.Patil College’,’Computer’)
insert into Teacher values(2,’Mr.Chaudhari’,’MJC’,’Mathematics’)
insert into Teacher values(3,’Mr.Deshpande’,’MMCC’,’Commerce’)
insert into Teacher values(4,’Mrs.Gaikwad’,’IMCD’,’Computer’)
CREATE TABLE EXAM(EXAMTNO NUMBER PRIMARY KEY,
EXAMNAME VARCHAR2(20) NOT NULL)

insert into exam values(1,’SET’)
insert into exam values(2,’NET’)
insert into exam values(3,’GET’)
CREATE TABLE TEACHER_EXAM(TNO NUMBER REFERENCES TEACHER(TNO) PRIMARY KEY,
EXAMTNO NUMBER REFERENCES EXAM(EXAMTNO))

insert into teacher_exam values(1,1)
insert into teacher_exam values(2,2)
insert into teacher_exam values(3,3)
insert into teacher_exam values(4,1)

1) Find department of teacher ‘Mrs. Jadhav’ of D.Y.Patil College.
select dept
from teacher
where tname=’Mrs. Jadhav’ and collegename=’D.Y.Patil College’
2) List all teachers who have given SET examination.
select tname
from teacher,exam,teacher_exam
where teacher.tno=teacher_exam.tno and
exam.examtno=teacher_exam.examtno and examname=’SET’
3) Count the number of teachers of ‘MJC’ College
select count(tno)
from teacher
where collegename=’MJC’

4) List College wise teacher name with examination given by teacher.
select collegename, tname, examname
from teacher,exam,teacher_exam
where teacher.tno=teacher_exam.tno and exam.examtno=teacher_exam.examtno
group by collegename, tname, examname;
Q. 30) Consider the following Entities & Relationships
Student(Rollno, sname, birthdate)
Course(Cno, Cname, Course_fee, Duration)
Student and Course are related with many to many relationships.
Constraints: Primary key,Course fee should be > 0
Create a RDB in 3NF & write queries in Oracle 8i for following.
• Count the number of courses joined by ‘Nilesh’
• List the name of all students who have joined for course c++
• Display details of course having maximum fee.
• List course wise Student names along with course fee and duration

CREATE TABLE STUDENT
(ROLLNO NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
BIRTHDATE VARCHAR2(2) )

insert into student values(1,’Sunil’,’13-03-1987′)
insert into student values(1,’ Nilesh’ ,’ 11-08-1985’)
insert into student values(1,’ Rupali ‘,’ 1-11-1991’)
insert into student values(1,’Raj’,’27-07-1988′)
insert into student values(1,’Amar’,’4-3-1989′)

CREATE TABLE COURSE(CNO NUMBER PRIMARY KEY,
CNAME VARCHAR2(20),
COURSE_FEE NUMBER CHECK (COURSE_FEE>0),
DURATION NUMBER)

insert into Course values(1, ‘C++’, 2500, ‘3month’)
insert into Course values(2, ‘Core & Adva. Java’, 7500, ‘6Month’)
insert into Course values(3, ‘.NET’, 4500, ‘4 Month’)
insert into Course values(4, ‘ADT’, 3000, ‘3 Month’)
Create table stud_Course(rollno number references student(rollno) primary key,
Cno number references course(cno))

insert into stud_Course values(1,1)
insert into stud_Course values(2,2)
insert into stud_Course values(3,3)
insert into stud_Course values(4,4)
insert into stud_Course values(5,1)

1) Count the number of courses joined by ‘Nilesh’
select count(course.cno)
from Course, student,stud_course
where student.rollno=stud_course.rollno and course.cno=stud_course.cno
and name=’Nilesh’

2) List the name of all students who have joined for course c++.
select name
from Course, student,stud_course
where student.rollno=stud_course.rollno and course.cno=stud_course.cno
and cname=’C++’
3) Display details of course having maximum fee.
select cno,cname,course_fee,Duration
from Course
where course_fee=(select max(course_fee) from course)
4) List course wise Student names along with course fee and duration.
select cname,name,course_fee,duration
from Course, student,stud_course
where student.rollno=stud_course.rollno and course.cno=stud_course.cno
group by cname,name,course_fee,duration order by cname;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s