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
0 comments:
Post a Comment