-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL Query
349 lines (308 loc) · 13 KB
/
SQL Query
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
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
--/* ======================= TABLES ========================*/
DROP TABLE tbl_publisher;
CREATE TABLE tbl_publisher (
publisher_PublisherName CHAR(100) NOT NULL,
publisher_PublisherAddress CHAR(200),
publisher_PublisherPhone CHAR(50)
);
DROP TABLE tbl_book;
CREATE TABLE tbl_book (
book_BookID INT UNIQUE,
book_Title CHAR(100) ,
book_PublisherName CHAR(100),
PRIMARY KEY (book_BookID)
);
DROP TABLE tbl_library_branch;
CREATE TABLE tbl_library_branch (
library_branch_BranchID INT UNIQUE,
library_branch_BranchName CHAR(100),
library_branch_BranchAddress CHAR(200),
PRIMARY KEY (library_branch_BranchID)
);
DROP TABLE tbl_borrower;
CREATE TABLE tbl_borrower (
borrower_CardNo INT UNIQUE ,
borrower_BorrowerName VARCHAR(100) ,
borrower_BorrowerAddress VARCHAR(200),
borrower_BorrowerPhone VARCHAR(50),
PRIMARY KEY(borrower_CardNo)
);
DROP TABLE tbl_book_loans;
CREATE TABLE tbl_book_loans (
book_loans_LoansID INT,
book_loans_BranchID INT,
book_loans_BookID INT,
book_loans_CardNo INT,
book_loans_DateOut VARCHAR(50),
book_loans_DueDate VARCHAR(50),
PRIMARY KEY (book_loans_LoansID)
);
--/* Use GETDATE() to retrieve the date values for Date out. Use DATEADD for the DueDate*/
DROP TABLE tbl_book_copies;
CREATE TABLE tbl_book_copies (
book_copies_CopiesID INT,
book_copies_BookID INT,
book_copies_BranchID INT,
PRIMARY KEY (book_copies_CopiesID)
);
DROP TABLE tbl_book_authors;
CREATE TABLE tbl_book_authors (
book_authors_AuthorID INT,
book_authors_BookID INT,
book_authors_AuthorName VARCHAR(50)
);
--/*======================== END TABLES ======================*/
--/*==================== POPULATING TABLES ======================*/
--adding names of publishers
INSERT INTO tbl_publisher
(publisher_PublisherName, publisher_PublisherAddress, publisher_PublisherPhone)
VALUES
('DAW Books', '375 Hudson Street, New York, NY 10014', '212-366-2000'),
('Viking', '375 Hudson Street, New York, NY 10014', '212-366-2000'),
('Signet Books', '375 Hudson Street, New York, NY 10014', '212-366-2000'),
('Chilton Books', 'Not Available', 'Not Available'),
('George Allen & Unwin', '83 Alexander Ln, Crows Nest NSW 2065, Australia', '+61-2-8425-0100'),
('Alfred A. Knopf', 'The Knopf Doubleday Group Domestic Rights, 1745 Broadway, New York, NY 10019', '212-940-7390'),
('Bloomsbury', 'Bloomsbury Publishing Inc., 1385 Broadway, 5th Floor, New York, NY 10018', '212-419-5300'),
('Shinchosa', 'Oga Bldg. 8, 2-5-4 Sarugaku-cho, Chiyoda-ku, Tokyo 101-0064 Japan', '+81-3-5577-6507'),
('Harper and Row', 'HarperCollins Publishers, 195 Broadway, New York, NY 10007', '212-207-7000'),
('Pan Books', '175 Fifth Avenue, New York, NY 10010', '646-307-5745'),
('Chalto & Windus', '375 Hudson Street, New York, NY 10014', '212-366-2000'),
('Harcourt Brace Jovanovich', '3 Park Ave, New York, NY 10016', '212-420-5800'),
('W.W. Norton', ' W. W. Norton & Company, Inc., 500 Fifth Avenue, New York, New York 10110', '212-354-5500'),
('Scholastic', '557 Broadway, New York, NY 10012', '800-724-6527'),
('Bantam', '375 Hudson Street, New York, NY 10014', '212-366-2000'),
('Picador USA', '175 Fifth Avenue, New York, NY 10010', '646-307-5745');
select * from tbl_publisher;
--adding books and their authors
INSERT INTO tbl_book
(book_BookID, book_Title, book_PublisherName)
VALUES
('1', 'The Name of the Wind', 'DAW Books'),
('2', 'It', 'Viking'),
('3', 'The Green Mile', 'Signet Books'),
('4', 'Dune', 'Chilton Books'),
('5', 'The Hobbit', 'George Allen & Unwin'),
('6', 'Eragon', 'Alfred A. Knopf'),
('7', 'A Wise Mans Fear', 'DAW Books'),
('8', 'Harry Potter and the Philosophers Stone', 'Bloomsbury'),
('9', 'Hard Boiled Wonderland and The End of the World', 'Shinchosa'),
('10', 'The Giving Tree', 'Harper and Row'),
('11', 'The Hitchhikers Guide to the Galaxy', 'Pan Books'),
('12', 'Brave New World', 'Chalto & Windus'),
('13', 'The Princess Bride', 'Harcourt Brace Jovanovich'),
('14', 'Fight Club', 'W.W. Norton'),
('15', 'Holes', 'Scholastic'),
('16', 'Harry Potter and the Chamber of Secrets', 'Bloomsbury'),
('17', 'Harry Potter and the Prisoner of Azkaban', 'Bloomsbury'),
('18', 'The Fellowship of the Ring', 'George Allen & Unwin'),
('19', 'A Game of Thrones', 'Bantam'),
('20', 'The Lost Tribe', 'Picador USA');
-- populating the library branches
INSERT INTO tbl_library_branch
(library_branch_BranchID, library_branch_BranchName, library_branch_BranchAddress)
VALUES
('1', 'Sharpstown', '32 Corner Road, New York, NY 10012'),
('2', 'Central', '491 3rd Street, New York, NY 10014'),
('3', 'Saline', '40 State Street, Saline, MI 48176'),
('4', 'Ann Arbor', '101 South University, Ann Arbor, MI 48104');
INSERT INTO tbl_borrower
(borrower_BorrowerName, borrower_BorrowerAddress, borrower_BorrowerPhone, borrower_CardNo)
VALUES
('Joe Smith', '1321 4th Street, New York, NY 10014', '212-312-1234', '1'),
('Jane Smith', '1321 4th Street, New York, NY 10014', '212-931-4124', '2'),
('Tom Li', '981 Main Street, Ann Arbor, MI 48104', '734-902-7455', '3'),
('Angela Thompson', '2212 Green Avenue, Ann Arbor, MI 48104', '313-591-2122', '4'),
('Harry Emnace', '121 Park Drive, Ann Arbor, MI 48104', '412-512-5522', '5'),
('Tom Haverford', '23 75th Street, New York, NY 10014', '212-631-3418', '6'),
('Haley Jackson', '231 52nd Avenue New York, NY 10014', '212-419-9935', '7'),
('Michael Horford', '653 Glen Avenue, Ann Arbor, MI 48104', '734-998-1513', '8');
INSERT INTO tbl_book_loans
(book_loans_LoansID, book_loans_BookID, book_loans_BranchID, book_loans_CardNo, book_loans_DateOut, book_loans_DueDate)
VALUES
('1', '1', '1', '1', '1/1/18', '2/2/18'),
('2', '2', '1', '2', '1/1/18', '2/2/18'),
('3', '5', '1', '3' , '1/1/18', '2/2/18'),
('4', '2', '1', '4', '1/1/18', '2/2/18'),
('5', '11', '1', '5', '1/3/18', '2/3/18'),
('6', '2', '1', '7', '1/3/18', '2/3/18'),
('7', '1', '1', '7', '1/3/18', '2/3/18'),
('8', '3', '1', '7', '1/3/18', '2/3/18'),
('9', '7', '1', '7', '1/3/18', '2/3/18'),
('11', '1', '1', '5', '1/3/18', '2/3/18'),
('12', '2', '1', '5', '12/12/17', '1/12/18'),
('13', '2', '1', '5', '12/12/17', '1/12/17'),
('14', '4', '1', '5', '2/3/18', '3/3/18'),
('15', '20', '1', '8', '1/5/18', '2/5/18'),
('16', '4', '1', '7', '1/5/18', '2/5/18'),
('17', '9', '1', '7', '1/3/18', '2/3/18'),
('18', '2', '1', '7', '1/7/18', '2/7/18'),
('19', '12', '4', '3', '1/7/18', '2/7/18'),
('20', '12', '0', '3', '1/7/18', '2/7/18'),
('21', '13', '0', '3', '1/7/18', '2/7/18'),
('22', '20', '2', '3', '12/12/17', '1/12/18'),
('23', '16', '3', '3', '1/9/18', '2/9/18'),
('24', '13', '2', '3', '1/3/18', '2/3/18'),
('25', '9', '2', '5', '1/3/18', '2/3/18'),
('26', '3', '4', '5', '1/3/18', '2/3/18'),
('27', '4', '4', '5', '1/3/18', '2/3/18'),
('28', '3', '1', '5', '1/3/18', '2/3/18'),
('29', '3', '4', '5', '1/3/18', '2/3/18'),
('30', '6', '2', '5', '1/3/18', '2/3/18'),
('31', '3', '2', '5', '1/3/18', '2/3/18'),
('32', '7', '2', '5', '12/12/17', '1/12/18'),
('33', '4', '3', '5', '1/3/18', '2/3/18'),
('34', '9', '2', '5', '1/12/18', '2/12/18'),
('35', '3', '3', '5', '1/3/18', '2/3/18'),
('36', '1', '3', '5', '1/3/18', '2/3/18'),
('37', '2', '2', '5', '1/4/18', '2/4/18'),
('38', '3', '3', '5', '1/15/18', '2/15/18'),
('39', '14', '3', '3', '1/15/18', '2/15/18'),
('40', '8', '2', '2', '1/1/18', '2/2/18'),
('41', '15', '1', '2', '1/1/18', '2/2/18'),
('42', '20', '1', '2', '2/2/17', '3/2/18'),
('43', '19', '1', '2', '1/3/18', '2/3/18'),
('44', '14', '1', '2', '1/12/18', '2/12/18'),
('45', '19', '1', '2', '1/13/18', '2/13/18'),
('46', '17', '1', '2', '1/13/18', '2/13/18'),
('47', '11', '1', '2', '1/14/18', '2/14/18'),
('48', '10', '1', '5', '1/15/18', '2/15/18'),
('49', '9', '4', '4', '1 / 15 / 18', '2 / 15 / 18'),
('50', '5', '4', '4', '1/19/18', '2/19/18'),
('51', '4', '4', '4', '1 / 19 / 18', '2 / 19 / 18'),
('52', '2', '4', '4', '1/22/18', '2/22/18');
INSERT INTO tbl_book_copies
(book_copies_CopiesID, book_copies_BookID, book_copies_BranchID)
VALUES
('1', '1', '4'),
('2', '1', '4'),
('3', '1', '1'),
('4', '1', '2'),
('5', '2', '3'),
('6', '2', '2'),
('7', '2', '3'),
('8', '2', '3'),
('9', '2', '3'),
('10', '8', '2'),
('11', '8', '2'),
('12', '8', '2'),
('13', '8', '2'),
('14', '8', '4'),
('15', '8', '4'),
('16', '8', '4'),
('17', '8', '3'),
('18', '1', '4'),
('19', '1', '4'),
('20', '1', '4'),
('21', '2', '4'),
('22', '2', '4'),
('23', '17', '2'),
('24', '17', '2'),
('25', '17', '2'),
('26', '17', '3'),
('27', '17', '3'),
('28', '17', '4'),
('29', '17', '3'),
('40', '17', '1'),
('41', '17', '2'),
('42', '17', '2'),
('43', '17', '2'),
('44', '17', '2'),
('45', '2', '1'),
('46', '2', '1'),
('47', '2', '1'),
('48', '2', '1'),
('49', '2', '1'),
('50', '2', '2'),
('51', '3', '2'),
('52', '3', '2'),
('53', '3', '2'),
('54', '3', '2'),
('55', '3', '3'),
('56', '3', '3'),
('57', '3', '3'),
('58', '3', '3'),
('59', '9', '3'),
('60', '9', '3'),
('61', '9', '3'),
('62', '9', '3'),
('63', '9', '3'),
('64', '9', '3'),
('65', '9', '3'),
('66', '9', '3'),
('67', '9', '3'),
('68', '12', '4'),
('69', '12', '4'),
('70', '12', '4'),
('71', '12', '1'),
('72', '12', '1'),
('73', '12', '1'),
('74', '12', '1'),
('75', '12', '1'),
('76', '11', '1'),
('77', '11', '1'),
('78', '11', '1'),
('79', '11', '1'),
('80', '11', '3'),
('81', '7', '2'),
('82', '7', '1'),
('83', '7', '1'),
('84', '7', '3'),
('85', '7', '1'),
('86', '7', '3'),
('87', '7', '4'),
('88', '7', '1'),
('89', '7', '1'),
('90', '7', '2');
SELECT * FROM tbl_book_copies;
INSERT INTO tbl_book_authors
(book_authors_AuthorID, book_authors_BookID, book_authors_AuthorName)
VALUES
('1', '1', 'Patrick Rothfuss'),
('2', '2', 'Stephen King'),
('3', '3', 'Stephen King'),
('4', '4', 'Frank Herbert'),
('5', '5', 'J.R.R. Tolkien'),
('6', '6', 'Christopher Paolini'),
('1', '6', 'Patrick Rothfuss'),
('7', '8', 'J.K. Rowling'),
('8', '9', 'Haruki Murakami'),
('10', '10', 'Shel Silverstein'),
('11', '11', 'Douglas Adams'),
('12', '12', 'Aldous Huxley'),
('13', '13', 'William Goldman'),
('14', '14', 'Chuck Palahniuk'),
('15', '15', 'Louis Sachar'),
('16', '16', 'J.K. Rowling'),
('17', '17', 'J.K. Rowling'),
('18', '18', 'J.R.R. Tolkien'),
('19', '19', 'George R.R. Martin'),
('20', '20', 'Mark Lee');
SELECT * FROM tbl_book_authors;
--/*============================== END POPULATING TABLES ==============================*/
--/* =================== STORED PROCEDURE QUERY QUESTIONS =================================== */
--/* #1- name of author of book titled "The Lost Tribe"*/
Select book_authors_AuthorName from tbl_book_authors INNER JOIN tbl_book ON book_BookID = book_authors_BookID and book_Title = "The Lost Tribe";
--/* #2- name of of the book are owned by each library branch = "Sharpstown"? */
select book_Title from tbl_book where book_BookID in (select distinct book_copies_BookID from tbl_book_copies INNER JOIN tbl_library_branch ON library_branch_BranchName = "Sharpstown");
--/* #3- Retrieve the names of all borrowers who do not have any books checked out. */
SELECT borrower_BorrowerName FROM tbl_borrower WHERE borrower_CardNo NOT IN (SELECT book_loans_CardNo FROM tbl_book_loans);
--/* #4- For each book that is loaned out from the "Sharpstown" branch and whose DueDate is 1/3/18, retrieve the book title, the borrower's name, and the borrower's address. */
Select book_Title, borrower_BorrowerName, borrower_BorrowerAddress from (select * from tbl_book INNER JOIN (SELECT * from tbl_borrower INNER JOIN tbl_book_loans ON borrower_CardNo = book_loans_CardNo) ON book_BookID = book_loans_BookID);
--/* #5- For each library branch, retrieve the branch name and names of books loaned out from that branch.*/
select distinct library_branch_BranchName, book_Title from (tbl_book INNER JOIN tbl_book_loans ON book_loans_BookID = book_BookID) INNER JOIN tbl_library_branch ON library_branch_BranchID =book_loans_BranchID ;
--/* #6- Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out. */
--select * from tbl_book_loans;
Create Procedure getCountTable
as
select book_loans_CardNo, count( book_loans_CardNo) as number from tbl_book_loans group by 1)
go;
// executing the procedure
Exec getCountTable;
select borrower_BorrowerName, borrower_BorrowerAddress, number from (cnt INNER JOIN tbl_borrower) where number > 5;
--/* #7- For each book authored by "Stephen King", retrieve the title and the id of copies owned by the library branch whose name is "Central".*/
select book_copies_CopiesID, book_Title from (tbl_book INNER JOIN (tbl_book_authors INNER JOIN (select * from tbl_book_copies where book_copies_BranchID = (select library_branch_BranchID from tbl_library_branch where library_branch_BranchName = "Central")) ON book_authors_BookID = book_copies_BookID and book_authors_AuthorName = "Stephen King") ON book_copies_BookID = book_BookID);
-- /* #8 Give the list of the Book loans issued on date = "1/3/18"?*/
select book_Title from tbl_book where book_BookID in (select book_loans_BookID from tbl_book_loans where book_loans_DateOut = "1/3/18");
--/*#9 Number of books in branch name "Sharpstown"*/
select Count(book_copies_CopiesID) from tbl_book_copies where book_copies_BranchID = (select library_branch_BranchID from tbl_library_branch where library_branch_BranchName = "Sharpstown");