Create the following views using the Where clause; Do not use joins! 5. List all

Question

Create the following views using the Where clause; Do not use joins!

5. List all books along with their publishers by book number, title, publisher number, name, and address. Orderby book title.

6. List all books from each publisher with the publisher number, name, book number, title, and publication date.Use grouping to prevent unnecessarily repeating publisher information. Order by publisher name.

7. List the books owned by each city library with the library number, name, book number, and title.

8. List the branches associated with each city library with the library number, name, branch number, branchname, and branch address. Do not repeat city information unnecessarily.

COUNTY LIBRARY SYSTEM Use the following relations for the SQL queries for this project. D, LIBRARY NAME, STREETLADDRESS, CITY, STATE, ZIPCODE, PHONE, LIBRARY LIBRARY MANAGER NAME) BRANCH BRANCH ID, BRANCH NAME, STREET ADDRESS, CITY, STATE, ZIPCODE, PHONE, BRANCH MANAGER, LIBRARYLID) FK L BRARY ID LIBRARY (PUBLISHER ID, PUBLISHER NAME, STREETLADDRESS, STATE ZIPCODE, PHONE) CITY, PUBLISHER BOOK BOOK D, BOOK TITLE, PUBLICATION DATE, PUBLISHER ID, PRECIS) FK PUBLISHER ID PUBLISHER (AUTHORID, FIRST NAME, LAST NAME, DATE oF BIRTH, DATE OF DEATH) AUTHOR BOOK AUTHOR BOOK D. AUTHOR ID FK BOOK ID BOOK FK AUTHOR ID AUTHOR COPY ID, BOOK D, COST, BRANCH ID) COPY FK BOOK ID BOOK FK BRANCH ID BRANCH CATEGORY CATEGORY ID, CATEGORY NAME

Explanation / Answer

scenario explanation:
-----------------------
we can relate tables either with joins or using where condition
the main difference between these two scenarios is we can have left right or full outer joins using Joins
but where as using where condition you would get only inner join

At first it does the cross join
create view BookPublisher as
select [book_id],[book_title],[publisher_id],[publisher_name],
from BOOK b,PUBLISHER p
where b.publisher_id=p.publisher_id
order by book_title

query explanation:
--------------------
At first it does the cross join when we say select * from BOOK b,PUBLISHER p and when we use where condition
it considers as inner join.by default order by takes ascending order you can use "desc" to get descending book_tile

create view Publisher as
select [publisher_id],[publisher_name],[book_id],[book_title],max(publication_date) publication_date
from PUBLISHER p,BOOK b
where b.publisher_id=p.publisher_id
group by [publisher_id],[publisher_name],[book_id],[book_title]
order by publisher_name

query explanation:
--------------------
Here we have linked publisher and book which are related by foreign key publisher id in Book Table which is a primmary key in
Publisher table and they are expecting grouping to prevent unnecessary repeated publisher information
so we have used grouping on publisher and books and aggregated based on max date of book published date.
since each book can have multiple published dates. by this we can get publisher's books details with latest published date

create view BOOKLibrary as
select br.city,l.library_id,l.library_name,b.book_name,b.book_title
from BOOK b,library l,copy c,branch br
where b.publisher_id=l.publisher_id and c.book_id=b.book_id and br.branch_id=c.branch_id
br.library_id=l.library_id

query explanation:
--------------------
Here we have linked city,library,copy and branch tables to get the library and book details and their respective branch details.
Book table is related with publisher, Copy table is a linked table which links Book table and Branch table
this way we have linked all four tables to fetch the resultant data

create view BranchLibrary as
select l.library_id,l.library_name,br.branch_id,br.branch_name,br.street_address
from library l,branch br
where br.library_id=l.library_id

query explanation:
--------------------
here we have linked library and branch to get the libraries and their branches which again is a straight forward
query same as first view

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.