Insert into branch values('B01','BE','DR.
KEERTHI ROY');
Insert into branch values('B02','MCA','DR.SUMITH');
Insert into branch values('B03','BTECH','DR.SHAHEENA');
Insert into branch values('B04','BCA','DR.RAJENDRA');
Insert into branch values('B05','BSC','DR.SHEELA');
Insert into student values('USN01','MANJUNATHA H B','NITTUR','B02','4');
Insert into student values('USN02','RAMESH','BRAHMAVARA','B01','6');
Insert into student values('USN03','ANANYA','UDUPI','B03','6');
Insert into student values('USN04','MAYURA','NITTUR','B01','8');
Insert into student values('USN05','SACHIN','HOSANAGARA','B05','6');
Insert into student values('USN06','SUDHEE','BYNDOOR','B02','2');
Insert into student values('USN07','PRAVEE','SAGARA','B02','2');
Insert into student values('USN08','SRAVES','UDUPI','B03','1');
Insert into student values('USN09','DARSHA','KUNDAPURA','B01','8');
Insert into student values('USN10','RADHAKRISHNA','BHATKAL','B05','6');
Insert into AUTHOR values('A01','Yashavant Kanetkar','INDIA','37');
Insert into AUTHOR values('A02','Donald E. Knuth','USA','39');
Insert into AUTHOR values('A03','E. Balagurusamy','INDIA','35');
Insert into AUTHOR values('A04','Thomas H. Cormen','USA','46');
Insert into AUTHOR values('A05','Roland Backhouse','GERMANY','55');
Insert into AUTHOR values('A06','Stephen G Kochan','UK','32');
Insert into BOOK values ('BK1','Art of Computer Programming','A02','Addison-
Wesley','B01');
Insert into BOOK values ('BK2','Introduction to Algorithms','A04','MIT
Press','B04');
Insert into BOOK values ('BK3','Programming in ANSI C','A03','McGrawHil
Education','B02');
Insert into BOOK values ('BK4','Algorithmic Problem Solving','A05','Wiley','B04');
Insert into BOOK values ('BK5','Let Us C','A01','BPB Publications','B03');
Insert into BOOK values ('BK6','Programming in C','A06','Pearson Edu Ltd','B05');
Insert into BOOK values ('BK7','Programming in java','A06','Pearson Edu
Ltd','B05');
Insert into BOOK values ('BK8','Programming in python','A02','Addison-
Wesley','B02');
Insert into BOOK values ('BK9','Programming in kotlin','A02','Pearson Edu
Ltd','B04');
Insert into BORROW values ('USN02','BK4',TO_DATE('2023-01-02','YYYY-MM-DD'));
Insert into BORROW values ('USN01','BK2',TO_DATE('2024-05-13','YYYY-MM-DD'));
Insert into BORROW values ('USN03','BK5',TO_DATE('2024-05-06','YYYY-MM-DD'));
Insert into BORROW values ('USN04','BK1',TO_DATE('2023-11-26','YYYY-MM-DD'));
Insert into BORROW values ('USN07','BK3',TO_DATE('2023-12-14','YYYY-MM-DD'));
Insert into BORROW values ('USN01','BK1',TO_DATE('2023-12-10','YYYY-MM-DD'));
Insert into BORROW values ('USN06','BK8',TO_DATE('2023-07-05','YYYY-MM-DD'));
Insert into BORROW values ('USN10','BK7',TO_DATE('2024-10-25','YYYY-MM-DD'));
Insert into BORROW values ('USN02','BK8',TO_DATE('2024-10-25','YYYY-MM-DD'));
Insert into BORROW values ('USN07','BK6',TO_DATE('2024-10-25','YYYY-MM-DD'));
//////////////////////////////////////////////////////////////////////////////
i.List the details of Students who are all studying in 2nd sem MCA.
select * from student where sem=2 and branch_id=(select branch_id from branch where
branch_name='MCA');
USN NAME ADDRESS BRA SEM
----- -------------------- -------------------- --- ----------
USN06 SUDHEE BYNDOOR B02 2
USN07 PRAVEE SAGARA B02 2
-----------------------------------------------------------------------------------
---------------------------------------------------------
ii.List the students who are not borrowed any books.
select * from student where usn Not in( select usn from borrow);
USN NAME ADDRESS BRA SEM
----- -------------------- -------------------- --- ----------
USN04 MAYURA NITTUR B01 8
USN05 SACHIN HOSANAGARA B05 6
USN08 SRAVES UDUPI B03 1
USN09 DARSHA KUNDAPURA B01 8
USN10 RADHAKRISHNA BHATKAL B05 6
-----------------------------------------------------------------------------------
---------------------------------------------------------
iii. Display the USN, Student name, Branch_name, Book_name, Author_name,
Books_Borrowed_Date of 2nd sem MCA Students who borrowed books.
select s.usn,s.name,br.branch_name,bor.borrowed_date,b.book_name,a.author_name
from branch br,student s,borrow bor,book b,author a
where s.branch_id=br.branch_id
and s.usn=bor.usn
and b.book_id=bor.book_id
and a.author_id=b.author_id
and br.branch_name='MCA' and s.sem=2;
USN NAME BRANCH_NAM BORROWED_ BOOK_NAME
AUTHOR_NAME
----- -------------------- ---------- ---------- ---------
------------------------------ --------------------
USN07 PRAVEE MCA 14/DEC/23 Programming in ANSI C
E. Balagurusamy
USN06 SUDHEE MCA 05/JUL/23 Programming in python
Donald E. Knuth
USN07 PRAVEE MCA 25/OCT/24 Programming in C
Stephen G Kochan
-----------------------------------------------------------------------------------
---------------------------------------------------------
iv. Display the number of books written by each Author.
select a.author_name,count(b.book_id) as no_of_books
from author a,book b
where a.author_id=b.author_id
group by a.author_name;
AUTHOR_NAME NO_OF_BOOKS
-------------------- -----------
Thomas H. Cormen 1
Yashavant Kanetkar 1
Donald E. Knuth 3
E. Balagurusamy 1
Roland Backhouse 1
Stephen G Kochan 2
-----------------------------------------------------------------------------------
---------------------------------------------------------
v.Display the student details who borrowed more than two books.
select s.usn,s.name,count(bor.usn) as no_of_books_borrowed
from student s,borrow bor
where s.usn=bor.usn
group by s.usn,s.name
having count(bor.usn)>2;
USN NAME NO_OF_BOOKS_BORROWED
----- -------------------- --------------------
USN01 MANJUNATHA H B 4
-----------------------------------------------------------------------------------
---------------------------------------------------------
vi.Display the student details who borrowed books of more than one Author.
select s.usn,s.name,count(distinct b.author_id) as no_of_authors
from student s,book b,borrow bor
where s.usn=bor.usn
and bor.book_id=b.book_id
group by s.usn,s.name
having count(distinct b.author_id)>1;
USN NAME NO_OF_AUTHORS
----- -------------------- -------------
USN01 MANJUNATHA H B 2
-----------------------------------------------------------------------------------
---------------------------------------------------------
vii.Display the Book names in descending order of their names.
select book_name from book order by book_name desc;
BOOK_NAME
------------------------------
Programming in python
Programming in kotlin
Programming in java
Programming in C
Programming in ANSI C
Let Us C
Introduction to Algorithms
Art of Computer Programming
Algorithmic Problem Solving
9 rows selected.
-----------------------------------------------------------------------------------
---------------------------------------------------------
viii.List the details of students who borrowed the books which are all published by
the
same publisher.
select student.usn,student.name,book.publisher, count(book.publisher)
from student,book,borrow
where student.usn=borrow.usn and borrow.book_id=book.book_id group by
student.usn,student.name,book.publisher having count(book.publisher)>1;
select student.usn,student.name,book.publisher, count(book.publisher) as book_count
from student,book,borrow where student.usn=borrow.usn and
borrow.book_id=book.book_id group by
student.usn,student.name,book.book_name,book.publisher having
count(book.publisher)>1;
select student.usn,student.name,book.publisher, count(book.book_name) as book_count
from student,book,borrow where student.usn=borrow.usn and
borrow.book_id=book.book_id group by
student.usn,student.name,book.book_name,book.publisher having
count(book.book_name)>1;
select s.usn,s.name,b.book_name,b.publisher
from student s,book b,borrow br
where s.usn=br.usn
and br.book_id=b.book_id
and b.publisher
in ( select publisher from book group by publisher having count(publisher)>1)
order by s.usn;
USN NAME BOOK_NAME PUBLISHER
----- -------------------- ------------------------------ --------------------
USN01 MANJUNATHA H B Art of Computer Programming Addison-Wesley
USN02 RAMESH Programming in python Addison-Wesley
USN04 MAYURA Art of Computer Programming Addison-Wesley
USN06 SUDHEE Programming in python Addison-Wesley
USN07 PRAVEE Programming in C Pearson Edu Ltd
USN10 RADHAKRISHNA Programming in java Pearson Edu Ltd
6 rows selected.
book,author,borrow inner join student branch.branch_id=student.branch_id
inner join book on branch.branch_id=book_branch_id inner join author on
book.author_id=author.author_id inner join borrow on student.usn=borrow.usn where
sem=2;
SELECT distinct s.usn, s.name, b.book_name, b.publisher
FROM student s
JOIN borrow br ON s.usn = br.usn
JOIN book b ON br.book_id = b.book_id
WHERE b.publisher IN (
SELECT publisher
FROM book
GROUP BY publisher
HAVING COUNT(book_name) > 1
)
ORDER BY s.usn;
SELECT S.* FROM STUDENT WHERE S.USN IN(SELECT BW.USN FROM BORROW BW JOIN BOOK BO
ON BW.Book_id=BO.Book_id GROUP BY BW.USN,BO.Publisher HAVING COUNT(DISTINCT
BO.Publisher) = 1);
