6

CREATE TABLE Book_info (
    bookid INT,
    bname VARCHAR(100),
    bauthor VARCHAR(100),
    price DECIMAL(10, 2),
    edition VARCHAR(50),
    publication VARCHAR(100),
    pur_date DATE
);
DESCRIBE Book_info;

CREATE TABLE Student (
    lib_car_num INT,
    stud_name VARCHAR(100),
    class VARCHAR(50),
    branch VARCHAR(50),
    roll_no INT
);
DESCRIBE Student;

CREATE TABLE Issue_table (
    issue_date DATE,
    sub_date DATE,
    bookid INT,
    lib_car_num INT,
    due DATE
);
DESCRIBE Issue_table;


INSERT INTO Book_info VALUES(1, 'Book1', 'Author1', 20, '1st Edition', 'Publication1', DATE '2024-01-01');
INSERT INTO Book_info VALUES(2, 'Book2', 'Author2', 25, '2nd Edition', 'Publication2', DATE '2024-01-02');
INSERT INTO Book_info VALUES(3, 'Book3', 'Author3', 30, '3rd Edition', 'Publication3', DATE '2024-01-03');
INSERT INTO Book_info VALUES(4, 'Book4', 'Author4', 35, '4th Edition', 'Publication4', DATE '2024-01-04');
INSERT INTO Book_info VALUES(5, 'Book5', 'Author5', 40, '5th Edition', 'Publication5', DATE '2024-01-05');
INSERT INTO Book_info VALUES(6, 'Book6', 'Author6', 45, '6th Edition', 'Publication6', DATE '2024-01-06');
INSERT INTO Book_info VALUES(7, 'Book7', 'Author7', 50, '7th Edition', 'Publication7', DATE '2024-01-07');
INSERT INTO Book_info VALUES(8, 'Book8', 'Author8', 55, '8th Edition', 'Publication8', DATE '2024-01-08');
INSERT INTO Book_info VALUES(9, 'Book9', 'Author9', 60, '9th Edition', 'Publication9', DATE '2024-01-09');
INSERT INTO Book_info VALUES(10, 'Book10', 'Author10', 65, '10th Edition', 'Publication10', DATE '2024-01-10');
SELECT *FROM Book_info;

INSERT INTO Student VALUES(1, 'Student1', 'Class1', 'Branch1', 1);
INSERT INTO Student VALUES(2, 'Student2', 'Class2', 'Branch2', 2);
INSERT INTO Student VALUES(3, 'Student3', 'Class3', 'Branch3', 3);
INSERT INTO Student VALUES(4, 'Student4', 'Class4', 'Branch4', 4);
INSERT INTO Student VALUES(5, 'Student5', 'Class5', 'Branch5', 5);
INSERT INTO Student VALUES(6, 'Student6', 'Class6', 'Branch6', 6);
INSERT INTO Student VALUES(7, 'Student7', 'Class7', 'Branch7', 7);
INSERT INTO Student VALUES(8, 'Student8', 'Class8', 'Branch8', 8);
INSERT INTO Student VALUES(9, 'Student9', 'Class9', 'Branch9', 9);
INSERT INTO Student VALUES(10, 'Student10', 'Class10', 'Branch10', 10);
SELECT *FROM Student;

INSERT INTO Issue_table  VALUES('01-JAN-24','10-JAN-24', 2, 1,'20-JAN-24');
INSERT INTO Issue_table  VALUES('20-JAN-24','25-JAN-24', 4, 5,'31-JAN-24');
SELECT *FROM Issue_table;
-------------------------------------------------------------------------------------------------------------------------------------
5. Add primary key constraint and not null constraint

ALTER TABLE Book_info
ADD CONSTRAINT pk_book_id PRIMARY KEY (bookid);
ALTER TABLE Student
ADD CONSTRAINT pk_lib_car_num PRIMARY KEY (lib_car_num);
ALTER TABLE Issue_table
ADD CONSTRAINT pk_issue PRIMARY KEY (issue_date, bookid, lib_car_num);

ALTER TABLE Book_info
MODIFY bname VARCHAR(100) NOT NULL;
ALTER TABLE Student
MODIFY stud_name VARCHAR(100) NOT NULL;
ALTER TABLE Student
MODIFY roll_no INT NOT NULL;

ALTER TABLE Issue_table
MODIFY issue_date DATE NOT NULL;
ALTER TABLE Issue_table
MODIFY bookid INT NOT NULL;
ALTER TABLE Issue_table
MODIFY lib_car_num INT NOT NULL;

6. Add primary key Foreign key constraint
-- Add foreign key constraint for bookid column in Issue_table referencing bookid column in Book_info table
ALTER TABLE Issue_table
ADD CONSTRAINT fk_bookid FOREIGN KEY (bookid) REFERENCES Book_info(bookid);

-- Add foreign key constraint for lib_car_num column in Issue_table referencing lib_car_num column in Student table
ALTER TABLE Issue_table
ADD CONSTRAINT fk_lib_car_num FOREIGN KEY (lib_car_num) REFERENCES Student(lib_car_num) ON UPDATE CASCADE ON DELETE CASCADE;;

1. Find the details of the books issued to the library card number 1.

SELECT i.*, b.bname, b.bauthor
FROM Issue_table i
JOIN Book_info b ON i.bookid = b.bookid
WHERE i.lib_car_num = 1;

2. Give all the information about student and the book issued with ascending order of library card number

SELECT s.*, i.*, b.*
FROM Student s
inner JOIN Issue_table i ON s.lib_car_num = i.lib_car_num
inner JOIN Book_info b ON i.bookid = b.bookid
ORDER BY s.lib_car_num ASC;

3. Find the author, edition, price of book.

SELECT bauthor, edition, price
FROM Book_info;

4. Find the names of the students with dues on the book issue.

SELECT DISTINCT s.stud_name
FROM Student s
INNER JOIN Issue_table i ON s.lib_car_num = i.lib_car_num
WHERE SYSDATE > i.due;



7. Show issue_date of Issue_table in Ascending Order.(order by clause)

SELECT issue_date
FROM Issue_table
ORDER BY issue_date ASC;

8. Show roll_no of Student in descending Order.(order by clause)

SELECT roll_no
FROM Student
ORDER BY roll_no DESC;