-
Notifications
You must be signed in to change notification settings - Fork 0
/
ananth-library.sql
194 lines (158 loc) · 7.21 KB
/
ananth-library.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
-- -- Done on Microsoft SQL Server Management Studio.
-- -- Allows us to select and execute.
-- -- Create a Schema Diagram before starting
-- Creating Table Publisher (1)
USE [4NM21AI010library];
create table publisher
(
name varchar(10) PRIMARY KEY,
address varchar(10),
phone int,
);
-- Creating Table Book (2)
create table book
(
book_id varchar(5) PRIMARY KEY,
title varchar(20),
pub_name varchar(10),
pub_year int,
FOREIGN KEY(pub_name) references publisher(name) on delete cascade,
);
-- Creating Table Library_Branch (3)
create table library_branch
(
branch_id varchar(5) PRIMARY KEY,
branch_name varchar(10),
address varchar(15),
);
-- Creating Table book_authors (4)
create table book_authors
(
book_id varchar(5),
author_name varchar(15),
PRIMARY KEY(book_id, author_name), -- Both book_id and author name are the primary key.
FOREIGN KEY(book_id) references book(book_id) on delete cascade
);
-- Creating Table book_copies (5)
create table book_copies
(
book_id varchar(5),
branch_id varchar(5),
no_of_copies int,
PRIMARY KEY (book_id, branch_id),
FOREIGN KEY (book_id) references book(book_id) on delete cascade,
FOREIGN KEY (branch_id) references library_branch(branch_id) on delete cascade,
);
-- Creating Table book_lending (6)
create table book_lending
(
book_id varchar(5),
branch_id varchar(5),
card_no varchar(5),
date_out date,
due_date date,
PRIMARY KEY (book_id, branch_id, card_no),
FOREIGN KEY (book_id) references book(book_id) on delete cascade,
FOREIGN KEY (branch_id) references library_branch(branch_id) on delete cascade,
);
-- -- Inserting into tables as per the table creation order
-- Publishers
insert into publisher values('Inedition','Noida', 961235521);
insert into publisher values('Editorist','Indore', 943235521);
insert into publisher values('OverWrite','Nagpur', 961235421);
insert into publisher values('WWWeeb','Bangalore', 961228321);
insert into publisher values('TechStuff','Mumbai', 961232321);
select * from publisher;
-- Books
insert into book values('111','ArtificalDumbness','Inedition',2021);
insert into book values('112','SmallBrainBigBrain','Editorist',2020);
insert into book values('113','LearingSnekLang.','OverWrite',2023);
insert into book values('114','1000IQMoments','Editorist',2020);
insert into book values('115','UrbanDictionary','Inedition',2019);
insert into book values('116','BigSmallTalk','Editorist',2018);
insert into book values('117','AdvancedMemes','Inedition',2018);
select * from book
-- Library Branch
insert into library_branch values('111','GrandOrder','Delhi_N01');
insert into library_branch values('112','Fate','Delhi_N12');
select * from library_branch;
-- Book Author
insert into book_authors values('111','JK_Kidding');
insert into book_authors values('112','JK_Kidding');
insert into book_authors values('113','SirGohan');
insert into book_authors values('114','MoistCritikal');
insert into book_authors values('115','MoistCritikal');
insert into book_authors values('116','JK_Kidding');
insert into book_authors values('117','JK_Kidding');
select * from book_authors;
-- Book_Copies
insert into book_copies values('111','111',10);
insert into book_copies values('112','111',32);
insert into book_copies values('113','111',52);
insert into book_copies values('114','111',65);
insert into book_copies values('115','111',76);
insert into book_copies values('111','112',876);
insert into book_copies values('112','112',456);
insert into book_copies values('113','112',465);
insert into book_copies values('114','112',345);
insert into book_copies values('115','112',764);
insert into book_copies values('116','111',35);
insert into book_copies values('117','111',74);
insert into book_copies values('116','112',45);
insert into book_copies values('117','112',76);
select * from book_copies;
-- Book Lending
insert into book_lending values('111','111','20001','2023-01-01','2023-02-01');
insert into book_lending values('112','112','20001','2023-01-01','2023-02-01');
insert into book_lending values('112','111','20002','2023-02-01','2023-03-01');
insert into book_lending values('113','111','20002','2023-04-01','2023-04-16');
insert into book_lending values('113','111','20003','2023-03-01','2023-04-01');
insert into book_lending values('111','111','20003','2023-03-01','2023-04-01');
insert into book_lending values('112','111','20003','2023-03-01','2023-04-01');
insert into book_lending values('114','111','20003','2023-03-01','2023-04-01');
insert into book_lending values('115','111','20003','2023-03-01','2023-04-01');
insert into book_lending values('114','111','20004','2023-01-12','2023-02-12');
insert into book_lending values('115','112','20005','2023-01-23','2023-02-23');
insert into book_lending values('114','112','20005','2023-03-23','2023-04-23');
insert into book_lending values('113','112','20005','2023-02-23','2023-03-23');
insert into book_lending values('111','112','20005','2023-04-23','2023-05-23');
insert into book_lending values('112','112','20005','2023-02-23','2023-03-23');
insert into book_lending values('112','112','20006','2023-02-01','2023-03-01');
select * from book_lending;
-- All Tables
select * from publisher
select * from book
select * from library_branch
select * from book_authors
select * from book_copies
select * from book_lending;
--1.query to get all the values of bid,pubname etc
select b.book_id,b.title,b.pub_name,ba.author_name,bc.branch_id,bc.no_of_copies from book b,book_authors ba,book_copies bc where b.book_id=bc.book_id and b.book_id=ba.book_id
select distinct card_no from book_lending b where (date_out between '01-jan-2020' and '30-jul-2020')group by card_no having count(*)>0;
--2.Retrive the details of publisher who published more than 3 books
select p.name from publisher p,book b where p.name=b.pub_name group by p.name having count(*)>0;
--3.Retrive the details of publisher without any books
select p.name,p.address,p.phone from publisher p
where not exists(select pub_name from book where(p.name=pub_name))
--4.Retreive the details of authors who have authored more than 1 books
select author_name from book_authors
group by author_name having count(author_name)>1;
--5.retreive the details of books with more than 2 authors
--6.delete a book in BOOK table
delete from book where book_id='114'
--7.create a view of all books and its number of copies that are currently available in the library
--begin transaction;
--commit transaction;
create view available as
(
select book_id,sum(no_of_copies)-(select count(card_no) from book_lending where b.book_id=book_id)as avail_copies from book_copies b group by book_id
);
select *from available;
--5.Retreive the details of publisher who published more than 3 books
Select p.name from publisher p,book b where p.name=b.pub_name group by p.name having COUNT(b.book_id)>3;
--6.retreive the details of publisher whohas not published any book
select p.name from publisher p where p.name not in(select distinct pub_name from book);
--7.Get the particulars of book with more than 3 authors
select b.book_id from book b,book_authors ba where b.book_id=ba.book_id group by b.book_id having count(ba.author_name)>3;
--8.get the particulars of library branch which has zero copies of book with id 112
select branch_id from library_branch b where branch_id not in(select branch_id from book_copies where book_id='112');