Database Lab 05





 Download All  Questions And SQL Files Import SQL Files To Your Database

Download Sql


Database Schemas:
Student(Stud_no : string, Stud_name: string)
Membership(Mem_no: string, Stud_no: string)
Book(book_no: string, book_name: string, author: string)
Iss_rec(iss_no: integer, iss_date: date, Mem_no: string, book_no: string)

For the above schema, perform the following—            
1.           Create the tables defined by the above schemas. Impose constraints to check the student no is started with ‘C’; take present date as the default value for iss_date. Defined foreign key for at least two tables.
2.            Insert around 10 records in each of the tables
3.            List all the student and Book name, Author issued on a specific date
4.            List  the details of students who borrowed book whose author is Tanenbum
5.            Give a count of how many books have  been borrowed by each student
6.            List the students who reached the borrowed limit 3
7.            Give a list of books taken by student with stud_no as C105
8.            List the book  details which are issued  as of today.




Query 1:

SELECT * FROM student WHERE stud_no LIKE 'c%'





Query 3:

SELECT  stud_name,membership.mem_no,book_name,author,iss_date FROM student, membership,book,iss_rec WHERE
book.book_no=iss_rec.book_no AND
membership.mem_no=iss_rec.mem_no AND
student.stud_no=membership.stud_no AND
iss_date='2016-08-01'


Query 4:

SELECT  stud_name,membership.mem_no,book_name,author,iss_date FROM student, membership,book,iss_rec WHERE
book.book_no=iss_rec.book_no AND
membership.mem_no=iss_rec.mem_no AND
student.stud_no=membership.stud_no AND
author='Tanenbum'

Query 5:

SELECT  stud_name,membership.mem_no,book_name,COUNT(iss_rec.book_no)AS Number,iss_date FROM student, membership,book,iss_rec WHERE
book.book_no=iss_rec.book_no AND
membership.mem_no=iss_rec.mem_no AND
student.stud_no=membership.stud_no 
GROUP BY iss_rec.mem_no

Query 6:

SELECT stud_name,membership.mem_no,book_name,COUNT(iss_rec.book_no)AS Number,iss_date FROM student, membership,book,iss_rec WHERE
book.book_no=iss_rec.book_no AND
membership.mem_no=iss_rec.mem_no AND
student.stud_no=membership.stud_no 
GROUP BY iss_rec.mem_no

HAVING COUNT(iss_rec.book_no)=3

 Query 7:

SELECT  stud_name,membership.mem_no,book.book_no,book_name,author,iss_date FROM student, membership,book,iss_rec WHERE
book.book_no=iss_rec.book_no AND
membership.mem_no=iss_rec.mem_no AND
student.stud_no=membership.stud_no AND
student.stud_no='105'


Query 8:

SELECT  book.book_no,book_name,author,CURRENT_DATE() AS Today FROM book,iss_rec WHERE
book.book_no=iss_rec.book_no AND
iss_date=CURRENT_DATE




Share on Google Plus
Earn @ Wap4dollar.Com

About Ashadullah Shawon

I am Ashadullah Shawon. I am a Software Engineer. I studied Computer Science and Engineering (CSE) at RUET. I Like To Share Knowledge.
    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment