assignmen sql 1
-- a -> CREATE the tables with appropriate constraints
CREATE Table Student(
Stud_Id INT PRIMARY KEY,
Stud_Name VARCHAR(10)
);
CREATE Table Membership(
Mem_no INT PRIMARY KEY,
Stud_Id INT,
FOREIGN KEY(Stud_Id) REFERENCES Student(Stud_Id) on delete CASCADE
);
CREATE Table Book(
book_no INT PRIMARY KEY,
book_name VARCHAR(10),
author_name VARCHAR(10)
);
CREATE Table IssueRec(
iss_no INT PRIMARY KEY,
iss_date DATE,
mem_no INT,
book_no INT,
FOREIGN KEY(mem_no) REFERENCES Membership(Mem_no) on delete CASCADE,
FOREIGN KEY(book_no) REFERENCES Book(book_no) on DElete CASCADE
);
-- b -> Insert around 10 records
INSERT INTO student VALUES (1,"Harsh");
INSERT INTO student VALUES (2,"Rohit");
INSERT INTO student VALUES (3,"Anju");
INSERT INTO student VALUES (4,"Tushar");
INSERT INTO student VALUES (5,"Aman");
INSERT INTO student VALUES (6,"Sonali");
INSERT INTO student VALUES (7,"Jyoti");
INSERT INTO student VALUES (8,"Rahul");
INSERT INTO student VALUES (9,"Anjali");
INSERT INTO student VALUES (10,"Rohan");
INSERT INTO membership VALUES (401,1);
INSERT INTO membership VALUES (402,2);
INSERT INTO membership VALUES (403,3);
INSERT INTO membership VALUES (404,4);
INSERT INTO membership VALUES (405,5);
INSERT INTO membership VALUES (406,6);
INSERT INTO membership VALUES (407,7);
INSERT INTO membership VALUES (408,8);
INSERT INTO membership VALUES (409,9);
INSERT INTO membership VALUES (410,10);
INSERT INTO book VALUES (5001,"Java","CJDATE");
INSERT INTO book VALUES (5002,"Algorithm","Debasis");
INSERT INTO book VALUES (5003,"Math","Ksrinivas");
INSERT INTO book VALUES (5004,"Science","Pearl");
INSERT INTO book VALUES (5005,"Biology","SKchand");
INSERT INTO book VALUES (5006,"Arthasastr","Chanakya");
INSERT INTO book VALUES (5007,"Mcaent","Agarwal");
INSERT INTO book VALUES (5008,"CompSci","MFreddy");
INSERT INTO book VALUES (5009,"EnjoyLife","Jamesc");
INSERT INTO book VALUES (5010,"Atomic","Rastogi");
INSERT INTO IssueRec VALUES (201,"2021-01-01",405,5002);
INSERT INTO IssueRec VALUES (202,"2021-10-10",408,5003);
INSERT INTO IssueRec VALUES (203,"2021-10-15",408,5005);
INSERT INTO IssueRec VALUES (204,"2021-10-16",403,5006);
INSERT INTO IssueRec VALUES (205,"2021-10-14",409,5001);
INSERT INTO IssueRec VALUES (206,"2021-10-19",410,5007);
INSERT INTO IssueRec VALUES (207,"2021-10-21",401,5004);
INSERT INTO IssueRec VALUES (208,"2021-10-23",402,5009);
INSERT INTO IssueRec VALUES (209,"2021-10-24",403,5009);
SELECT * FROM student;
SELECT * FROM membership;
SELECT * FROM Book;
SELECT * FROM issuerec;
ALTER Table issuerec
add foreign key(mem_no)
references membership(Mem_no) on delete cascade ;
ALTER Table issuerec
add foreign key(book_no)
references book(book_no) on delete cascade;
-- c -> List all student with their membership No
SELECT student.stud_name as Student_Name ,membership.Mem_no as Membership_No
from student
JOIN membership on student.Stud_Id = membership.Stud_Id;
-- e-> List Detail of student borrowed book author is CJDATE
SELECT * FROM student
WHERE Stud_id = (
SELECT stud_Id FROM membership
WHERE Mem_no = (
SELECT mem_no FROM issuerec
WHERE book_no = (
SELECT book_no FROM book
WHERE author_name = "CJDATE"
)
)
);
-- d-> List issue with current date with name nd book name
SELECT issueRec.iss_no,book.book_name,student.Stud_Name
FROM issuerec
JOIN Book ON book.book_no = issueRec.book_no
JOIN membership ON issuerec.mem_no = membership.Mem_no
JOIN Student ON student.Stud_Id = membership.Stud_Id
WHERE issueRec.iss_date = "2021-10-10";
-- e-> Give a count of how many books isuued by students
SELECT COUNT(issueRec.mem_no),student.Stud_name,membership.stud_Id
FROM issuerec
JOIN membership ON membership.Mem_no = issueRec.mem_no
JOIN student ON membership.Stud_Id = student.Stud_Id
GROUP BY issueRec.mem_no;
Comments
Post a Comment