Lab 2

Assignment 2

1. Retrieve the required information using SQL language.

Give a database schema for a library management system as the following picture.

1. How many copies of the book titled “********” are owned by the library branch whose name is "******"?
2. How many copies of the book titled "******" are owned by each library branch?
3. Retrieve the names of all borrowers who do not have any books checked out.
4. For each book that is loaned out from the "******" branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address.
5. For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
6. Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.

For each book authored (or co-authored) by "******", retrieve the title a

2.  Consider the following schema and operate the queries

2.select fname,contact_no from student s, department d  where s.dept_id=d.dept_id and d.dept_name="Information Technology";

3.select distinct dept_name from department, student where     department.dept_id=student.dept_id;

4.select dept_name from department where dept_name not in (select distinct dept_name from department, student where department.dept_id=student.dept_id);

    

5.select dept_name from department;

6.select count(roll_no) from student,department where department.dept_name="Electrical" and student.dept_id=department.dept_id;

7.select * from student where fname like "a%";

8.select * from student where gender="M";

    

9.select * from student,department where semester=3 and student.dept_id=department.dept_id;

10. select * from student where gender="f" order by fname;

3.

1. Display product information which are ordered in the same year of its manufacturing year.

2. Display product information which are ordered in the same year of its manufacturing year where vender is „smith‟.

3. Display total no. of orders placed in each year.

4. Display total no. of orders placed in each year by vender Wills.

5. Display the name of all those persons who are venders and customers both.

6. Display total no. of food items ordered every year.

7. Display total no. of food items ordered every year made from Bread.

8. Display list of product_id whose vender and customer is different.

9. Display all those customers who are ordering products of milk by smith.

10. Display total no. of orders by each vender every year.

11. Write a PL-SQL script to compare three given numbers and display them in ascending order.