Help me answer #6, #8 & #10 please and thank you. CSC 355 Database Systems 601 A
ID: 3709480 • Letter: H
Question
Help me answer #6, #8 & #10 please and thank you.
CSC 355 Database Systems 601 Assignment 3 (4/11) Due 6:00:00pm, Thursday 4/19. Reading: The posted Lecture 5 and Lecture 6 Slides, and Ullman/Widom Sections 6.1-6.2 and 6.4 [Next week: Ullman/Widom Sections 6.2-6.3.] Your task in this assignment is to write a set of SQL queries (I will supply the tables) First, download the script file Books.sql from the dropbox and run it in SQLDeveloper to build the tables you will be querying This script will build and display the contents of the following four tables that store data for a company that sells books to (fictitious) libraries LIBRARY(ID, Name, City, State, Zip), which contains information on libraries that may ordeir books from the company; BOOK(ISBN, Title, Author, Price), which contains information on books sold by the company; BOOKORDER(ID, OrderDate, LibraryID), which contains information on book orders placed with the companv LINEITEM(OrderID, BookISBN, Quantity), which contains information on the books requested in the libraries book orders In additional to the primary keys indicated above, OrderID and BookISBN in LINEITEM are foreign keys referencing ID in BOOKORDER and ISBN in BOOK, respectively, and LibraryID in BOOKORDER is a foreign key referencing ID in LIBRARY. In SQLDeveloper, look at the Columns, Data, and Constraints for each of the four tables before continuing, to be sure that they have been constructed correctly. You might also want to draw the foreign keys and reference arrows into the set of relation schemas given above to be sure that you understand the links among the tables. For each of the following query problems, follow the steps we discussed in class: interpret the problem predict the output by solving it by hand on the needed table(s), write a query to solve the problem, and test the query. (Most of the query problems can be solved with information from just one of the given tables, but some will require joins.) In a separate .sql file (do not modify Books.sql), write a script that contains just the following ten SQL queries (in this order) 1. Give an alphabetical list of the names of all libraries. 2. Give the city, state, and zip of all libraries in New York (NY) and New Jersey (NJ)Explanation / Answer
--1
SELECT Name FROM LIBRARY ORDER BY Name ASC;
--2
SELECT City,State,ZIP FROM LIBRARY WHERE State IN ('NY','NJ');
--3
SELECT Title,ISBN,Price FROM BOOK WHERE Author='Allen Steele' ORDER BY Price DESC;
--4
SELECT name,ZIP FROM LIBRARY WHERE Name LIKE '%Public%';
--5
SELECT Name,COUNT(ID) AS NumberOFLib FROM LIBRARY
GROUP BY NAME HAVING COUNT(ID)>=1
ORDER BY State ASC;
--6
SELECT OrderDate,MIN(ID) FROM BookOrder
GROUP BY OrderDate HAVING COUNT(ID)>=1
ORDER BY OrderDate DESC;
--7
SELECT l.ID,MIN(b.OrderDate) FROM LIBRARY l INNER JOIN BookOrder b
ON l.ID=b.LibraryID
GROUP BY l.ID
ORDER BY l.ID;
--8
SELECT OrderID,COUNT(ORDERID) FROM LINEITEM
GROUP BY BookISBN;
--9
SELECT b.ID,l.Name,l.State FROM LIBRARY l INNER JOIN BookOrder b
ON l.ID=b.LibraryID ORDER BY b.ID;
--10
SELECT b.ISBN,COUNT(l.OrderID)
FROM Book b LEFT OUTER JOIN LINEITEM l
ON b.ISBN=l.BookISBN
GROUP by b.ISBN
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.