DBMS Practical Slips Solution

DBMS Practical Slips

Slip1

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.

•Find details of all customers whose loan is greater than 10 lakhs.
SQL>select Customers.cust_name,Customers.address from Customers,Loan,Customer_Loan    where  Customers.cust_no=Customer_Loan.cust_no and  Loan.loan_no= Customer_Loan. loan_no  and Loan.loan_amt>=10000;

•List all customers whose name starts with 'ba'.
SQL>Select * from Customers  Where  cust_name like 'ba%';

•List names of all customers in descending order who has taken a loan in Nasik city.
SQL>select Customers.cust_name,Customers.address from Customers,Loan,Customer_Loan    where   Customers.cust_no=Customer_Loan.cust_no and  Loan.loan_no= Customer_Loan. loan_no  and Customers.city='nashik'  order by  Customers.cust_name desc  ;

•Display customer details having maximum loan amount.
SQL>select Customers.cust_name,Customers.address from Customers,Loan,Customer_Loan    where Customers.cust_no=Customer_Loan.cust_no and  Loan.loan_no= Customer_Loan. loan_no  and   Loan.loan_amt =(select max(Loan.loan_amt) from Loan ) ;

•Calculate total of all loan amount.
SQL>select sum(Loan.loan_amt) from Loan;

Slip2

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.

•Find total salary of all computer department employees.

SQL>select sum(Employee.salary),dept_name from Department,Employee where Department. dept_no=Employee.dept_no and dept_name='Computer' group by dept_name;

•Find the name of department whose salary is above 10000.
SQL>select  dept_name,salary from Department, Employee where Department. dept_no  = Employee. dept_no  and salary >10000;

•Count the number of employees in each department.
SQL>select count(emp_no ), dept_name  from Department, Employee where Department. dept_no  = Employee. dept_no  group by dept_name  ;

•Display the maximum salary of each department.
SQL>select max(salary),dept_name from Department, Employee  where Department. dept_no  = Employee. dept_no  group by dept_name  ;

•Displaydepartment wise employee list.
SQL>select emp_name, dept_name  from Department, Employee where Department. dept_no  = Employee. dept_no;

Slip3

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,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’.
SQL>Select  p1name, ddname, hodd, locationd from department1, project1 where   department1. dno1= project1.dno1 and  project1.status='C';

•Display total budget of each department.
SQL>Select sum(budget), ddname from  department1, project1 where department1. dno1= project1.dno1  group by ddname;

•Display incomplete project of each department
SQL>select p1name,status ,count(department1. dno1) from department1, project1 where   department1. dno1= project1.dno1 and project1.status='I'  group by status,p1name    ;

•Find the names of departments that have budget greater than 50000   .
SQL>select ddname from  department1, project1 where  department1. dno1= project1.dno1 and budget >=50000  ;

•Displayall project working under 'Mr.Desai'.
SQL>Select  p1name  from department1, project1 where   department1. dno1= project1.dno1 and hodd= 'Mr.Desai';  

Slip4

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.

•Display room details according to its rates  in ascending order.
SQL>Select desc,rate from room  order by desc ASC;

•Find the names of guest who has allocated room for more than 3 days.
SQL>Select  gname  from guest,room where guest.gno=room.rno where   no_of_days>3;

•Find no. of AC rooms.
SQL>select count(rno) from room where desc=’AC’;

•Display total amount for NON-AC rooms.
SQL>Select Sum(rate) from room where desc=’Non-AC’;

•Find names of guest with maximum room charges.
SQL>select gname from room,guest where guest.gno=room.rno  and rate = (select max(rate) from room);

Slip5

Consider the following Entities and Relationships

 Book (Book_no, title, author, price, year_published)

Customer (cid, cname, addr)

Relation between 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.

•Display customer details from 'Mumbai'.
SQL>Select * from Customerb where addr='mumbai';

•Display author wise details of book.
SQL>Select author ,title from BOOK order by author;

•Display all customers who have purchased the books published in the year 2013.
SQL>Select * from Customerb,BOOK, Customerb_BOOK where  Customerb.cid=Customerb_BOOK.cid and BOOK.BNO= Customerb_BOOK.BNO and year_published='2013';

•Display customer name that has purchased more than 3 books.
SQL>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>3 group by cname;

•Displaybook names having price between 100 and 200 and published in the year 2013.
SQL>Select BOOK.title from Customerb,BOOK, Customerb_BOOK  where  price>=100 and   price<=200 and Customerb.cid=Customerb_BOOK.cid and BOOK.BNO= Customerb_BOOK.BNO;