CSC 355 Database Systems 601 Assignment 3 (4/11) Due 6:00:00pm, Thursday 4/19. R
ID: 3709300 • Letter: C
Question
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
According to CHEGG POLICY we answer the first four subparts of a question, but I tried as much as possible. If you have any doubt please write in comments.
1) Give an alphabetical list of names of all the libraries
select name from library order by name;
2) Give the city,state, and zip of all libraries in New York(NY) and New Jersy(NJ)
select city,state,zip from library where state='NY' or state='NJ';
3) Give the title,ISBN, and price of all books by allensteele, ordered from the book with the lowest price to the one with the highest
select title,ISBN,price from book where author='Allen Steele' order by price desc;
4) Give the names and zips of all libraries whose names contain the word 'Public'
select name,zip from library where name like '%Public%';
5) for each state with at least one library in it, give the name of the state and how many libraries are in the state, give the states in alphabetical order
select state,count(*) "Number of Library" from library group by state having count(*)>=1 order by state;
7) Give the IDs of all libraries that have placed at least one book order, and the date of their first order.
Sort the output by the libraries' IDs
select ID,orderdate from bookorder where id in (select id from lineitem) order by id;
9) For each book order, give the order ID and the name and state of the library that placed that book order. Sort the output by the order ID.
select bo.id,l.name,l.state from bookorder bo,library l where bo.libraryid=l.id order by bo.id;