SQL Queries
a) Use your script from HW Assignment 6 to create the LIBRARY relational database schema in the figure below, in your Oracle schema. (Alternatively, a solution script HW6_SQL_DDL_Library.sql will be provided for that assignment after the submission window closes.)
b) Use the HW7_HW8_SQL_DML_Library.sql script provided on Canvas to populate the LIBRARY tables. If you use your HW Assignment 6 script and get errors because varchar attributes are limited to less characters than used in the insert statements, please extend the allowed size for these attributes, recreate the LIBRARY tables, and run again the script to populate them. Leave the tables in your schema until your assignment is graded in Canvas.
c) Find the names of all authors of books published by "Penguin" that cost more than $10. Make sure each name is included only once in the result.
d) Find the title, publisher and price for all books with an unknown author.
e) The library is setting a summer reading program, where each book values a number of points, based on its length; the formula to determine points for a book is number of pages / 100, rounded up to the nearest integer. Calculate the number of points for each book and list it together with the book id and title. Name the calculated column "no_of_points".
f) Find the title and publisher of all books that are priced between 3 and 6 dollars. Requirements:
- All titles from each publisher must be reported consecutively, and sorted alphabetically from A to Z.
- Publisher names must be all shown in capital letters.
- The report must have the 3 columns: publisher, title, and a new calculated column that should be named "Selling price". A partial result is shown below:
g) How many copies of the book titled "The Lost Tribe" are owned by the library branch whose name is "Newport Branch"? Report the library branch id, name, and number of copies of the book. Separate join conditions from where conditions.
h) Find the card number and name of all members who have borrowed books from the Alexandria library branch. Requirements:
List borrowers ordered by their card number.
Make sure each name is included only once in the result.
Separate join conditions from where conditions.
i) Find the card numbers of all library members who have not borrowed any books from the library. You must use set operations to write this query.
DDL(Data Definition Language)
drop table book_loans;
drop table book_copies;
drop table book;
drop table library_branch;
drop table borrower;
create table library_branch
(branch_id int,
branch_name varchar2(20),
address varchar2(50),
primary key (branch_id));
create table book
(book_id int,
title varchar2(50),
publisher varchar2(30),
author varchar2(30),
price numeric (6,2),
primary key (book_id));
create table book_copies
(book_id int,
branch_id int,
no_of_copies int,
primary key (book_id, branch_id),
foreign key (book_id) references book,
foreign key (branch_id) references library_branch);
create table borrower
(card_no int,
name varchar2(30),
address varchar2(50),
phone varchar2(12),
primary key (card_no));
create table book_loans
(book_id int,
branch_id int,
card_no int,
date_out date,
due_date date,
primary key (book_id, branch_id, card_no),
foreign key (book_id, branch_id) references book_copies,
foreign key (card_no) references borrower);
alter table book add (no_of_pages int);
commit;-- library_branch
insert into library_branch(branch_id, branch_name, address)
values (1, 'Fort Thomas Branch', '1000 Highland Ave, Fort Thomas, KY 41075');
insert into library_branch(branch_id, branch_name, address)
values (2, 'Fort Thomas Branch', '1000 Highland Ave, Fort Thomas, KY 41075');
insert into library_branch(branch_id, branch_name, address)
values (3, 'Fort Thomas Branch', '1000 Highland Ave, Fort Thomas, KY 41075');
insert into library_branch(branch_id, branch_name, address)
values (4, 'Fort Thomas Branch', '1000 Highland Ave, Fort Thomas, KY 41075');-- book
insert into book(book_id, title, publisher, author, price, no_of_pages)
values(1,'The Lost Tribe','Penguin','John Smyth',5.50,275);
insert into book(book_id, title, publisher, author, price, no_of_pages)
values(2,'How to Sew Buttons','Kensington','Jane Do',4.35,50);
insert into book(book_id, title, publisher, author, price, no_of_pages)
values(3,'The Terrible Night','Penguin','John Smyth',7.89,280);
insert into book(book_id, title, publisher, author, price, no_of_pages)
values(4,'Mindy''s Mittens','Scholastic','Eleanor Mellors',5.50,35);
insert into book(book_id, title, publisher, author, price, no_of_pages)
values(5,'Pizza and Donuts Diet','Kensington','Jane Do',10.5,80);
-- book_copies
INSERT INTO book_copies (book_id,branch_id,no_of_copies) VALUES(1,2,2);
INSERT INTO book_copies (book_id,branch_id,no_of_copies) VALUES(1,1,3);
INSERT INTO book_copies (book_id,branch_id,no_of_copies) VALUES(2,2,2);
INSERT INTO book_copies (book_id,branch_id,no_of_copies) VALUES(3,3,2);
INSERT INTO book_copies (book_id,branch_id,no_of_copies) VALUES(4,4,2);
INSERT INTO book_copies (book_id,branch_id,no_of_copies) VALUES(5,1,5);
INSERT INTO book_copies (book_id,branch_id,no_of_copies) VALUES(6,2,2);
INSERT INTO book_copies (book_id,branch_id,no_of_copies) VALUES(7,3,1);-- borrower
INSERT INTO Borrower(card_no,name,address,phone) values(1,'Charlie Brown','27 Main St, Newport','859-555-5123');
INSERT INTO Borrower(card_no,name,address,phone) values(2,'Rachel Rigby','101 Hwy 22, Cold Spring','859-688-7711');
INSERT INTO Borrower(card_no,name,address,phone) values(3,'Nancy Drew','5678 Oak St, Newport','859-555-3467');
INSERT INTO Borrower(card_no,name,address,phone) values(4,'Derek Jones','6789 Ritmo Cir, Cold Spring','859-222-1234');
INSERT INTO Borrower(card_no,name,address,phone) values(5,'Howie Han','111 First Ave, Anderson','513-234-5678');
INSERT INTO Borrower(card_no,name,address,phone) values(6,'Tim Tegulpas','432 Nebraska Ave, Alexandria','859-987-6543');
INSERT INTO Borrower(card_no,name,address,phone) values(7,'Sam Semel','7688 Hedge Ct, Wilder','859-777-9898');
INSERT INTO Borrower(card_no,name,address,phone) values(8,'Evan Mann','4545 Court St, Alexandria','859-899-9090');
INSERT INTO Borrower(card_no,name,address,phone) values(9,'Sally Short','323 Remington St, Alexandria','859-767-8991');
INSERT INTO Borrower(card_no,name,address,phone) values(10,'Bob Biggs','227 South St, Ft. Thomas',null);--book_loans
INSERT INTO book_loans(book_id,branch_id,card_no,date_out,due_date) VALUES(1,1,4,to_date('2020-08-20', 'yyyy-mm-dd'),to_date('2020-09-20', 'yyyy-mm-dd'));
INSERT INTO book_loans(book_id,branch_id,card_no,date_out,due_date) VALUES(2,2,4,to_date('2020-08-20', 'yyyy-mm-dd'),to_date('2020-09-20', 'yyyy-mm-dd'));
INSERT INTO book_loans(book_id,branch_id,card_no,date_out,due_date) VALUES(3,3,4,to_date('2020-08-20', 'yyyy-mm-dd'),to_date('2020-09-20', 'yyyy-mm-dd'));
INSERT INTO book_loans(book_id,branch_id,card_no,date_out,due_date) VALUES(4,4,4,to_date('2020-08-19', 'yyyy-mm-dd'),to_date('2020-09-19', 'yyyy-mm-dd'));
INSERT INTO book_loans(book_id,branch_id,card_no,date_out,due_date) VALUES(5,1,4,to_date('2020-08-19', 'yyyy-mm-dd'),to_date('2020-09-19', 'yyyy-mm-dd'));
INSERT INTO book_loans(book_id,branch_id,card_no,date_out,due_date) VALUES(6,2,2,to_date('2020-09-19', 'yyyy-mm-dd'),to_date('2020-10-19', 'yyyy-mm-dd'));
INSERT INTO book_loans(book_id,branch_id,card_no,date_out,due_date) VALUES(7,3,2,to_date('2020-09-19', 'yyyy-mm-dd'),to_date('2020-10-19', 'yyyy-mm-dd'));
INSERT INTO book_loans(book_id,branch_id,card_no,date_out,due_date) VALUES(8,4,2,to_date('2020-09-18', 'yyyy-mm-dd'),to_date('2020-10-18', 'yyyy-mm-dd'));
INSERT INTO book_loans(book_id,branch_id,card_no,date_out,due_date) VALUES(9,1,2,to_date('2020-09-18', 'yyyy-mm-dd'),to_date('2020-10-18', 'yyyy-mm-dd'));
commit;Solution
Requirements a) and b): Leave the LIBRARY tables in your schema until your assignment is graded in Canvas.
Query c): select author
from book
where publisher='Penguin' AND price>10;
output:
Query d):
select TITLE,PUBLISHER,PRICE from book where AUTHOR='unknown';
Query e):
Select book_id, title, (no_of_pages/100) as no_of_points from book;
Query f):
Select Upper(publisher), title,'Selling_price' || ' ' ||'$' || price as Selling_price from book where price between 3 and 6;
Query g):
select book_copies.branch_id, book.title, book_copies.no_of_copies from book
inner join book_copies on book.book_id = book_copies.book_id
inner join library_branch on library_branch.branch_id = book_copies.branch_id where library_branch.branch_name = 'Newport Branch' and book.title = 'The Lost Tribe';
Query h):
select Borrower.card_no, Borrower.name
from Borrower
inner join book_loans on book_loans.card_no = borrower.card_no
inner join book_copies on book_copies.book_id = book_loans.book_id and book_copies.branch_id = book_loans. branch_id
inner join library_branch on library_branch.branch_id = book_copies.branch_id
where library_branch.branch_name='Alexandria';
Query i):
select card_no from Borrower
minus
select Borrower.card_no
from Borrower
inner join book_loans on book_loans.card_no = borrower.card_no
inner join book_copies on book_copies.book_id = book_loans.book_id and book_copies.branch_id = book_loans. branch_id
inner join library_branch on library_branch.branch_id = book_copies.branch_id
where library_branch.branch_name='Alexandria';
Send Your Requirement Details at:
realcode4you@gmail.com
To get help in any other databases.