Exercise 6:
Create a Database in 3NF & write queries for following.
Consider
the following Entities and Relationships
Sales_order(ordNo,
ordDate)
Client
(clientNo, ClientName, addr)
Constraint:
Primary key, ClientName should not be NULL.
A
client can give one or more sales_orders ,but a sales_order belongs to exactly
one
client. Create the relations accordingly, so that the relationship is handled
properly
and
the relations are in normalized form(3 NF) and perform the following tasks.
1.Insert
two client records into client table.
2.Insert
3 sales records for each client.
3.Change
order date of client_No ‘C004’ to 18/03/2019
4.Delete
all sale records having order date before 10 /02/2018.
5.Display
date wise sales_order given by clients.
SQL>create
table client0(cno int primary key,cname varchar(15),addr varchar(15));
SQL>create
table sales_ord0(od_no int primary key,od_date int,cno int);
SQL>insert
into client0 values(1,'ajay','pimpri');
SQL>insert
into sales_ord values(1,'20 Agu',1);
SQL>update
sales_ord set cno=5 where od_date='10 Nov';
SQL>delete
from sales_ord where od_date < '20 Agu';
SQL>select
cname,od_date from client0,sales_ord where client0.cno=sales_ord.cno order by
(od_date);
Consider
the following Entities and Relationships
Customer (cust_no, cust_name, address, city)
Loan (loan_no, loan_amt)
Relation between Customer and Loan is Many to
Many
Constraint: Primary key, loan_amt should be
> 0.
Create a Database in 3NF & write queries
for following.
1.Find
details of all customers whose loan is greater than 10 lakhs.
2.List
all customers whose name starts with 'sa'.
3.List
names of all customers in descending order who has taken a loan in Pimpri
city.
4.Display
customer details having maximum loan amount.
5.Calculate
total of all loan amount
SQL>create
table customer3(cno int primary key,cname varchar(10),addr varchar(15),city
varchar(10));
SQL>insert
into customer3 values(1,'Ajay','pimpri','pune');
SQL>create
table loan3(lno int primary key,lamt int);
SQL>insert
into loan3 values(1,'500000');
SQL>create
table cst_loan(cno int,lno int);
SQL>insert
into cst_loan values(1,1);
SQL>select
cname,lamt from customer3,loan3,cst_loan where customer3.cno=cst_loan.cno and
loan3.lno=cst_loan.lno and lamt > 500000;
SQL>select
cname from customer3 where cname like 'Am%';
SQL>select
cname,city from customer3,loan3,cst_loan
where customer3.cno=cst_loan.cno and loan3.lno=cst_loan.lno and
city='pune' order by cname desc;
SQL>select
sum(lamt) from loan3;
SQL>select
cname,addr,city fromcustomer3,loan3,cst_loan where customer3.cno=cst_loan.cno
and loan3.lno=cst_loan.lno and loan3.lamt=(select max(loan3.lamt)from loan3);
Consider
the following Entities and Relationships
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.
1.Find
total salary of all the employees from computer science dept.
2.Find
the name of department whose average salary is above 10000.
3.Count
the number of employees in each department.
4.Display
the maximum salary of each department.
5.Display
department wise employee list.
6.Increase
Salary of “Managers” by 15%
7.Delete
all Employees who are working as “clerk”.
SQL>create
table department1(dno int primary key,dname varchar(15),loc varchar(15));
SQL>create
table employ1(eno int primary key,ename varchar(15),addr varchar(15),sal
int,designation varchar(15),dno int);
SQL>insert
into department1 values(001,'computer sci','pimpri');
SQL>insert
into employ1 values(1,'ajay','kasba peth','20000','manager',1);
SQL>select
sum(sal),dname from department1,employ1 where department1.dno=employ1.dno and
dname='computer sci' group by dname;
SQL>select
avg(sal),dname from department1,employ1 where department1.dno=employ1.dno2 and sal > '10000' group by dname;
SQL>select
count(eno),dname from
department1,employ1 where
department1.dno=employ1.dno group by dname;
SQL>select
max(sal),dname from department1,employ1
where department1.dno=employ1.dno group by dname;
SQL>select
dname,ename from department1,employ1
where department1.dno=employ1.dno order by dname;
SQL>update
employ1 set sal=sal+0.15 where designation2
='manager';
SQL>delete
from employ1 where designation2
='clerk';
Consider
the following Entities and Relationships
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.
1.List
the project name and department details worked in projects that are
‘Complete’.
2.Display
total budget of each department.
3.Display
incomplete project of each department
4.Find
the names of departments that have budget greater than 50000 .
5.Display
all project working under 'Mr.Desai'.
SQL>create
table project1(pno int primary key,pname varchar(15),start_date
varchar(15),budget int,ststus varchar(15),dno int);
SQL>create
table department01(dno int primary key,dname varchar(15),HOD varchar(15));
SQL>insert
into project1 values(1,'solar system','10.02.2015',50000,'complete',1);
SQL>insert
into department01 values(1,'science','mr.desai');
SQL>
select pname,dname,HOD from department01,project1 where department01.dno=project1.dno and
project1.status='complete';
SQL>select
sum(budget),dname from
department01,project1 where department01.dno=project1.dno group by dname;
SQL>select
pname,status,count(department01.dno) from department01,project1 where
department01.dno=project1.dno and status='incomplete' group by status,pname;
SQL>
select dname from department01,project1 where
department01.dno=project1.dno2 and
budget >= 50000;
SQL>
select pname from department01,project1 where department01.dno=project1.dno and
HOD='mr.desai';
Consider
the following Entities and Relationships
Room
(roomno, desc, rate)
Guest
(gno, gname, no_of_days)
Relation
between Room and Guest is One to One.
Constraint:
Primary key, no of days should be > 0.
Create
a Database in 3NF & write queries for following.
1.Display
room details according to its rates in ascending order.
2.Find
the names of guest who has allocated room for more than 3 days.
3.Find
no. of AC rooms.
4.Display
total amount for NON-AC rooms.
5.Find
names of guest with maximum room charges.
SQL>create
table room3(rno int primary key,description varchar(15),rate int);
SQL>create
table guest3(gno int primary key,gname varchar(15),days int);
SQL>insert
into room3 values(1,'AC',5000);
SQL>select
description,rate from room3 order by description ASC;
SQL>select
gname from guest3,room3 where guest3.gno=room3.rno and days > 10;
SQL>select
count(rno) from room3 where description='AC';
SQL>select
count(rno) from room3 where description='Non Ac';
SQL>select
gname from room3,guest3 where guest3.gno=room3.rno and rate=(select max(rate)
from room3);
Consider
the following Entities and Relationships
Relationbetween Book and Customer 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.
1.Display
customer details from 'Mumbai'.
2.Display
author wise details of book.
3.Display
all customers who have purchased the books published in the year 2013.
4.Display
customer name that has purchased more than 3 books.
5.Display
book names having price between 100 and 200 and published in the year
1.Display
area wise property details.
2.Display
property owned by 'Mr.Patil' having minimum rate.
3.Display
all properties with owner name that having highest rate of properties located
in Chinchwad area.
4.Display
owner name having maximum no. of properties.
5.Delete
all properties from “pune” owned by “ Mr. Joshi”.
6.Display
all the properties from Mumbai owned by “Mr. Patil”.
7 Update the phone
Number of “Mr Talure” to 9923323366 who having property at Pimpri
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.
1.Find
the names and rate of pay all employees who allocated a duty.
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.
Constraint:
Primary key, price should be > 0.
Create
a Database in 3NF & write queries for following.
1.Display
receipt which includes bill_no with Dish description, price, quantity and total
amount of each menu.
2.Find
total amount collected by hotel on date 08/01/2013
3.Count
number of menus of billno 301.
4.Display
menu details having price between 100
and 500.
5.Display
total number of bills collected from each table on 01/12/2013.
