DBMS-PART4

Q3) Attempt any Four of the following:                                                             [16] 

 

a) Consider the following Entities and Relationships & solve the queries: 

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

 • Find total salary of all Electronic department employees.

 • Find the name of department whose salary is above 20000.

1.      Select sum(salary)from Department , Employee where Department.dept_no =Employee.dept_no and dept_name=’Electronic’;

2.      Select dname from Department , Employee where Department.dept_no =Employee.dept_no and salary >20000.

 

 b) Consider the following Entities and Relationships & solve the queries:

Book (Book_no, title, author, price, year_published) Customer (cid, cname, addr) Relation between Book and Customer is Many to Many. Constraint: Primary key, price should be >0. 

• Display author wise details of book. 

• Display customer name that has purchased more than 4 books.

1.      Select title from Book where order by author;

2.      select count(BOOK.BNO),cname from  customerb,BOOK,customerb_BOOK where customerb.cid=customerb_BOOK,cid and  BOOK.BNO=customerb_BOOK.BNO and BOOK.BNO>4 GROUP BY cname;

 

c) Consider the following Entities and Relationships & solve the queries: 

Account (ano, branchname, balance) Customer (cust_no, cust name, street, city) Relation between Account and Customer is Many to Many. Constraint: Primary key, balance should be > 500

. • Display customer details with balance between 100000 and 200000.

Sql>select * from account,customer,ac where account.ano=ac.ano and customer.cno=ac.cno and balance=(select balance from account where balance=100000 and balance=200000);

 • Display customers having more than two accounts in Chinchwad branch.

sql>select cname,branchname from account,customer,ac where account.ano=ac.ano and customer.cno=ac.cno group by cname ,baranchname haveing count(cname)>2 and branchname='chichwad';

 

d)Consider the following entities and their relationship : 

Item (item no, name, quantity) Sup (no, name, addr, city, phone-no) 

Item and sup are related with many-to-many relationship with rate, discount. 

Constraints : primary key and item qty > 5 and rate > 0

•  Display supplier name in descending order of quantity.

Select Sup.name from Item , Sup  Where Item.item_no=IS.item_no and Sup.no=IS.no order by quantity DESC;

• Display item name in descending order.

Select Item.name from Item , Sup  Where Item.item_no=IS.item_no and Sup.no=IS.no order by Item.name DESC’

 

e)Consider relational database : 

Customer (cust-no, cust-name, address, city) 

Loan (loan-no, loan-amt, loan-date, cust-no) 

Customer and loan are related with one to many relationships.

• Display customers with loan amounts less than 1,00,000. 

sql>select customer.cust_name,customers.address from  customers,loan,customer_loan where customers.cust_no=loan.cust_no and Loan.loan_amt<100000;

• List loan details of customer name as ‘‘Mr. Patil’’.

sql>select customer.cust_name,customers.address,loan.loan-amt from  customers,loan where customers.cust_no=loan.cust_no and cname =’Mr.Patil’;

  f)Consider the following Entities and Relationships and solve the queries :     Donor

(donor_no, donor_name, city)

Blood_Donation (bid, blood_group, quantity, date_of_collection) 

Relation between Donor and Blood_Donation is One to Many. Constraint : Primary key, blood_group should not be null.

 

• Display total blood quantity collected on 25th December 2013.

SELECT SUM(quantity) FROM Blood_Donation WHERE date_of_collection = '2013-12-25';

 • Display total blood donated by each donor

SELECT d.donor_no, d.donor_name, d.city, COALESCE (SUM(b.quantity), 0) FROM Donor d LEFT JOIN Blood_Donation b ON d.donor_no = b.donor_no GROUP BY d.donor_no, d.donor_name, d.city;

g)Consider the following Entities and Relationships and solve the queries : Musician (mno, mname, addr, phno) 

Album (title, copy_right_dae, format)

 Relation between Musicians and Album is One to Many. Constraint : Primary key. 

•Display all albums composed by ‘A R Rehman’. 

SELECT title, copy_right_date, format  FROM  Musician , Album WHERE m.mname = 'A R Rahman' and  Musician.mno = Album.mno;

•Display musician details who have composed Audio album.

SELECT DISTINCT mno,mname, addr, phno FROM Musician , Album WHERE a.format = 'Audio' and  m.mno = a.mno;

 h)Consider the following Entities and Relationships & write queries for following. 

Property (pno, desc, area, rate)

Owner (owner_name,addr, phno)

 Relation between owner and Property is One to Many. 

• Display owner details having rate of property less than Rs. 20,00,000.

SELECT owner_name, addr, phno FROM Owner , Property WHERE rate < 2000000 and Property.pno =Owner.pno;

 • Display owner name having maximum no. of properties.      

SELECT  max(pno), owner_name  FROM Owner , Property WHERE Property.pno and Owner.pno group by owner_name;

i)Consider the following entities and their relationship

Movie (movie_no, movie_name] release year) 

Actor (Actno, Actname). 

Movie and Actor are related with many to many relationship : 

·    Insert a row in Actor table.

INSERT INTO Actor VALUES (1, 'AkshayKumar');

·        Display all the actor details of movie ‘PK’

SELECT  * FROM Movie ,Actor, MovieActor WHERE Movie.movie_name = 'PK' and Movie. movie_no= MovieActor .movie_no and Actor.Actno = MovieActor . Actno ;