Que7

Q. 10)    Consider the following Entities and Relationships                [30 Marks]
Sailor (sid, sname, age)
Boats (bid, bname, color)
Relation between Sailer and Boats is Many to Many with day as descriptive attribute.
Constraint: Primary key, age should be > 0.
Create a Database in 3NF & write queries for following.
•    Display details of all boats sailed by sailor ‘Ram’.
•    Display Sailor names working on blue boat.
•    Count number of boats sailed by each sailor.
•    Find the name of sailor who sailed the boat on both Tuesday & Friday.
•    Display details of the boats which is sailed maximum times on Sundays.

Ans.
create table sailor(
sid int primary key,
sname varchar(10),
age int check(age>0)
);
create table boats(
bid int primary key,
bname varchar(10),
color varchar(10)
);
create table sailor_boats(
sid int references sailor(sid),
bid int references boats(bid),
day varchar(10),
primary key(sid,bid)
);
insert into sailor values(501,'Ram',25);
insert into sailor values(502,'Sam',25);
insert into sailor values(503,'Gansam',25);
insert into sailor values(504,'Rima',25);

insert into boats values(601,'Godavari','blue');
insert into boats values(602,'Andaman','blue');
insert into boats values(603,'Albert','Yellow');
insert into boats values(604,'Areil','Weight');
insert into boats values(605,'Arizona','Black');

insert into sailor_boats values(501,601,'Monday');
insert into sailor_boats values(502,602,'Friday');
insert into sailor_boats values(503,603,'Sunday');
insert into sailor_boats values(504,605,'Tueday');
insert into sailor_boats values(501,604,'Sunday');
select *from sailor;
select *from boats;
select *from sailor_boats;

1]
select bid from sailor_boats  where sid=50;
2]selet
***************************************************
Q. 11)Consider the following Entities and Relationships                [30 Marks]
Supplier (sid, sname, addr)
Parts (pid, pname, pdesc)
Relation between Supplier and Parts is Many to Many with cost as descriptive attribute.
Constraint: Primary key, cost should be > 0.
Create a Database in 3NF & write queries for following.
•    Display Supplier details from 'Mumbai' city.
•    Update cost by 25 % for all parts supplied by supplier ‘Mr. Pawar’.
•    Display all parts supplied by each supplier.
•    Display details of parts which are supplied at maximum price by each supplier.
•    Display all suppliers who supply part ‘wheel’ and also display its cost.
Ans.
create table supplier(
sid int primary key,
sname varchar(10),
addr varchar(10)
);
create table parts(
pid int primary key,
pname varchar2(10),
pdesc varchar(10)
);
create table sup_parts(
sid int references supplier(sid),
pid int references parts(pid),
cost int,
primary key(sid,pid)
);
insert into supplier values(101,'Ram','Pune');
insert into supplier values(102,'Sam','Mumbai');
insert into supplier values(103,'Gamsam','Satara');
insert into supplier values(104,'Balaji','Pune');
insert into supplier values(105,'Tata','Nashik');

insert into parts values(1,'Wheel','Car');
insert into parts values(2,'Handl','Bike');
insert into parts values(3,'Stand','Bike');
insert into parts values(4,'Breks','Bus');

insert into  sup_parts values(101,1,100);
insert into  sup_parts values(102,2,200);
insert into  sup_parts values(103,3,300);
insert into  sup_parts values(104,4,400);

select *from supplier;
select *from parts;
select *from sup_parts;

Que6

Q. 8)    Consider the following Entities and Relationships                [30 Marks]
Bill (billno, day, tableno, total)                   
Menu (dish_no, dish_desc, price)
Relation between Bill and Menu is Many to Many with quantity as descriptive attribute.

Constraint: Primary key, price should be > 0.
Create a Database in 3NF & write queries for following.
•    Display receipt which includes bill_no with Dish description, price, quantity and total amount of each menu.
•    Find total amount collected by hotel on date 08/01/2013
•    Count number of menus of billno 301.
•    Display menu details having   price between 100 and 500. 
•    Display total number of bills collected from each table on 01/12/2013.

Ans
drop table bill;
drop table menu;
drop table  bill_menu;

create table bill(
bno int primary key,
day1 date,
tableno int,
total int
);
create table menu(
dno int primary key,
ddesc varchar(10),
price int check(price>0)
);
create table bill_menu(
bno int references bill(bno),
dno int references menu(dno),
qnt int ,
primary key(bno,dno)
);
insert into bill values(300,'1-dec-2013',23,500);
insert into bill values(301,'8-jan-2013',24,100);
insert into bill values(302,'13-jeb-1013',26,300);
insert into bill values(303,'31-dec-2013',28,600);

insert into  menu values(11,'manturian',20);
insert into  menu values(12,'roti',20);
insert into  menu values(13,'panier',20);
insert into  menu values(14,'rice',20);
insert into  menu values(15,'plane-rice',20);

insert into bill_menu values(300,12,4);
insert into bill_menu values(300,15,2);
insert into bill_menu values(300,11,2);
insert into bill_menu values(301,11,1);
insert into bill_menu values(302,14,1);

select *from bill;
select *from menu;
select *from bill_menu;

************************************************
Q. 9)    Consider the following Entities and Relationships                [30 Marks]
Musician (mno, mname, addr, phno)
Album (title, copy_right_date, format)
Relation between Musicians and Album is One to Many.
Constraint: Primary key.
Create a Database in 3NF & write queries for following.
•    Display all albums composed by ‘A R Rehman’.
•    Display musician details who have composed Audio album.
•    Find all musicians who have composed maximum albums.
•    Display musician wise album details.
•    Display Musian details from 'Pune'

Ans.
create table musician(
mno int primary key,
mname varchar(20),
addr varchar(10),
phone int
);
create table album(
mnno int references musician(mno),
title varchar(30),
crdate date,
format varchar(10)
);
insert into musician values(1,'Shreya Ghoshal','mumbai',1245666);
insert into musician values(2,'Lata Mangeshkar','mumbai',1245666);
insert into musician values(3,'Asha Bhosle','mumbai',1245666);
insert into musician values(4,'A R Rehman','mumbai',1245666);

insert into album values(4,'jay ho','01-jan-2013','Audio');
insert into album values(2,'jana-gana','01-jan-2013','Audio');
insert into album values(2,'parichy','01-jan-2013','Audio');
insert into album values(3,'Ijaazat','01-jan-2013','Audio');
insert into album values(4,'vande matram','01-jan-2013','Audio');
insert into album values(4,'shivaji','01-jan-2013','Audio');
insert into album values(1,'Paheli','01-jan-2005','Audio');
select *from album;
select *from musician;

1]Display all albums composed by ‘A R Rehman’.
//select title from album where mnno in(select mnno from musician where mname='A R Rehman');
2]    Display musician details who have composed Audio album.
//select *from musician where musician.mnno=album.mno and album.format='Audio';
select *from musician where mno in(select mno from album where format='Audio');
3]    Find all musicians who have composed maximum albums.
//select mname from musician where mno in(select mnno,(mnno) from album group by mnno);
4]Display musician wise album details.
//
5]Display Musian details from 'Pune'
//select *from musician where addr='mumbai';

Que 5

Q.6)    Consider the following Entities and Relationships                [30 Marks]
Property (pno, desc, area, rate)
Owner (owner_name, addr, phno)
Relation between owner and Property is One to Many.
Constraint: Primary key, rate should be > 0
Create a Database in 3NF & write queries for following.
•    Display area wise property details.
•    Display property owned by 'Mr.Patil' having minimum rate.
•    Display all properties with owner name that having highest rate of  properties located in Chinchwad area.
•    Display owner wise property detail.
•    Display owner name having maximum no. of properties.
Ans.
create table owner1(
oname varchar2(20) primary key,
addr varchar2(10),
phone int
);
create table property1(
oname varchar2(20) references owner1(oname),
pno int primary key,
desc1 varchar2(10),
area varchar2(10),
rate int
);
insert into owner1 values('Mukesh Ambani','Gujarat',123496867);
insert into owner1 values('Dilip Shanghvi','Gujarat',123496862);
insert into owner1 values('Lakshmi Mittal','Parsi',123496863);
insert into owner1 values('Azim Premji','mumbai',123496865);
insert into owner1 values('Shiv Nadar','mumbai',123496866);
insert into owner1 values('S P Hinduja','mumbai',123496868);
insert into owner1 values('patil','mumbai',123496868);

insert into  property1 values('Mukesh Ambani',111,'1 plot','deccan',1000);
insert into  property1 values('Dilip Shanghvi',222,'2 plot','baramati',20000);
insert into  property1 values('Lakshmi Mittal',333,'3 plot','faltan',3000);
insert into  property1 values('Azim Premji',444,'plot','chinchwad',40000);
insert into  property1 values('S P Hinduja',555,'banglow','chinchwad',6000);
insert into  property1 values('patil',666,'banglow','pune',6000);
insert into  property1 values('patil',888,'plot','pune',9000);

select *from owner1;
select *from property1;

1]Display area wise property details.
select

2]Display property owned by 'Mr.Patil' having minimum rate.

select oname,min(rate) from property1 group by oname having oname='patil';

3]Display all properties with owner name that having highest rate of  properties located in Chinchwad area.

select oname from property1 where area =

(select max(rate) from  property1  group by area having area='chinchwad');
4]Display owner wise property detail.


5]Display owner name having maximum no. of properties.
//////select oname from property1 where    ;

*********************************************
Q. 7)    Consider the following Entities and Relationships                [30 Marks]
Employee (emp_no, name, skill, payrate)
Position (posting_no, skill)
Relation between Employee and Position is Many to Many with day and shift as descriptive attribute.   
      
    Constraint: Primary key, payrate should be > 0.
       Create a Database in 3NF & write queries for following.
•    Find the names and rate of pay all employees who allocated a duty.
•    Give employee number who are working at posting_no. 201, but don’t have the skills of waiter.
•    Display a list of names of employees who have skill of chef and who has assigned a duty.
•    Display emp_no and dates for all employees who are working on Tuesday and at least one other day.
•    Display shiftwise employee details

Ans
drop table emp_position;
drop table emp;
drop table position;

Create table emp(
eno int primary key,
ename varchar2(20),
skill varchar2(10),
payrate int check(payrate>0)
);
create table position(
pno int primary key,
skill varchar2(10)
);
create table emp_position(
eno int references emp(eno),
pno int references position(pno),
day1 varchar(7),
shift char(10) check(shift in('morning','evening')),
primary key(eno,pno)
);
insert into emp values(101,'sachin','watchman',2000);
insert into emp values(102,'pravin','manager',3000);
insert into emp values(103,'prasad','waiter',4000);
insert into emp values(104,'kiran','waiter',5000);
insert into emp values(105,'kartik','chef',7000);

insert into position values(1000,'manager');
insert into position values(1001,'waiter');
insert into position values(1002,'chef');
insert into position values(1003,'watchman');
insert into position values(1004,'account');

insert into emp_position values(101,1003,'Monday','morning');
insert into emp_position values(102,1000,'Tuesday','evening');
insert into emp_position values(103,1001,'Tuesday','morning');
insert into emp_position values(104,1001,'Monday','evening');
insert into emp_position values(105,1002,'Monday','morning');

select *from emp;
select *from position;
select *from emp_position;

1]Find the names and rate of pay all employees who allocated a duty.
select name,rate  from
2]Give employee number who are working at posting_no. 201, but don’t have the skills of waiter.

3]Display a list of names of employees who have skill of chef and who has assigned a duty.

4]Display emp_no and dates for all employees who are working on Tuesday and at least one other day.

5]Display shiftwise employee details

Que3

Q.3]Consider the following Entities and Relationships                [30 Marks]
Project (pno, pname, start_date, budget, status)
Department (dno, dname, HOD)
Relation between Project and Department is Many to One
          Constraint: Primary key.
                      Project Status Constraints: C – completed,
                      P-Progressive, I-Incomplete 
         Create a Database in 3NF & write queries for following.
•    List the project name and department details worked in projects that are ‘Complete’.
•    Display total budget of each department.
•    Display incomplete project of each department
•    Find the names of departments that have total budget greater than 50000   .
•    Display all project working under 'Mr.Desai'.

Ans.

drop table project cascade constraints;
drop table department2 cascade constraints;
create table project(
dno int references department2(dno),
pno int primary key,
pname varchar2(20),
sdate date,
budget int,
status char(1) check(status in ('y','n'))
);

create table department2(
dno int primary key,
dname varchar2(20),
hod varchar2(20)
);

insert into department2 values(101,'computer','james');
insert into department2 values(102,'networking','bill');
insert into department2 values(103,'database','mark');
insert into department2 values(104,'static','steve');

insert into project values(101,11,'image-processing','1-jan-2017',3000,'y');
insert into project values(101,12,'driverless car','1-jan-2017',3000,'y');
insert into project values(103,13,'wather mark','1-jan-2017',3000,'y');
insert into project values(104,14,'mobile jammer','1-jan-2017',3000,'y');
insert into project values(104,15,'mobile jammer','1-jan-2017',3000,'y');
insert into project values(103,16,'airtel shop','1-jan-2017',3000,'n');
insert into project values(103,17,'mobile database','1-jan-2017',3000,'n');
insert into project values(102,18,'router','1-jan-2017',3000,'n');

select *from project;
select *from department2;

Que2

Q. 2)     Consider the following Entities and Relationships                [30 Marks]
Department (dept_no, dept_name, location)
             Employee (emp_no, emp_name, address, salary, designation)
             Relation between Department and Employee is One to Many
Constraint: Primary key, salary should be > 0.
Create a Database in 3NF & write queries for following.
•    Find total salary of all computer department employees.
•    Find the name of department whose salary is above 10000.
•    Count the number of employees in each department.
•    Display the maximum salary of each department.
•    Display department wise employee list.

Ans.

drop table emp cascade constraints;
drop table department cascade constraints;
create table department(
dno int primary key,
dname varchar2(20),
location varchar2(10)
);

create table emp(
dno int references department(dno),
eno int primary key,
ename varchar2(10),
address varchar2(10),
salary int check(salary>0),
designation varchar2(10)
);

desc department;
desc emp;

insert into department values(101,'computer sci','pune');

insert into department values(102,'maths','nashik');
insert into department values(103,'electronic','satara');
insert into department values(104,'static','pune');
insert into department values(105,'engg','pune');
insert into emp values(101,11,'james','mumbai',10000,'staff');
insert into emp values(102,17,'steve','nashik',20000,'hod');
insert into emp values(101,13,'mark','usa',30000,'manager');
insert into emp values(101,14,'bill','pune',40000,'staff');

select *from department;
select *from emp;