CSC 321/621 – Spring 2019

CSC 321/621作业代写、代写SQL编程语言作业、SQL实验作业代做、代写ISBN留学生作业

CSC 321/621 – Spring 2019

Test 1 – Take Home Portion

You may access textbooks and course materials but the WORK SUBMITTED MUST

BE DONE BY YOU AND YOU ALONE! No communication regarding these questions

with anyone real or virtual. No Exceptions. I reserve the right to ask you to explain

your responses orally if I have a question about your response.

I will try to clarify any questions you have. Send your questions in e-mail. I’ll broadcast a

response as quickly as possible. Please submit your responses on paper (printed or

handwritten) at the start of class on Tuesday. The deadline is 12:30pm next Tuesday, 2/19/19.

Setup:

Download the following files: Resources ? Test 1 Materials ? { yadkin.db, make-yadkin.sql } from Sakai.

You will need to open the yadkin.db database in SQLite Studio to write and check queries. The file named

make-yadkin.sql contains the SQL code used to create the database schema. You will only need to open it in

a text editor, not in SQLite Studio.

Explanations regarding Yadkin:

- Regarding the author table. The attribute “SEQUENCE_NUMBER” has to do with the order that the

authors are listed by when a book has multiple authors. Values can be 1, 2, …, for “first author”, “second

author”, …

- The ISBN values are not realistic but don’t worry about it …

- The invent table name is short for “inventory.” It tracks the books available at a branch. Not all books

are available at all branches; furthermore, some books may not be available at any branch.

- The book table has an attribute named PAPERBACK that will contain ‘Y’ if the book is available as a

paperback. The value may also be ‘N’ to indicate it’s a Hardback book or null to indicate unknown.

I. (10 pts) Open make-yadkin.sql in a text editor and/or use sqlite to answer the following questions.

1. List the names of the tables in the yadkin database along with the primary key of each table.

2. List the foreign keys in the database, identifying both the source of the foreign key and the referenced

table and column(s).

3. Use the results of the previous two questions to sketch the database schema. That is, create a

rectangle for each table, identifying the attributes and identifying primary key with an underscore and

drawing lines between tables to represent foreign keys.

II. (30 pts) For each of the prompts below, write an SQL query to retrieve data from or to modify the

“yadkin” database.

In your response, label each query clearly and precede each one with a very brief description of what you

believe your query does. For example you might write: “This query uses a natural join to relate books to

their authors. It then selects those authors who have written more than 10 books.”

Prompts:

a. List the titles of all books published by a publisher located in New York (‘NY’). Don’t repeat

any titles.

b. List the titles of the books that have exactly two authors. Don’t repeat any titles.

c. Find the average price of all the paperback books that are included in the book table. Paperback

books have a value of ‘Y’ in the “paperback” attribute of book.

d. List the titles of the books in the book table that are available in both paperback AND hardback

form.

e. List the names of the books that are out of stock at all branches (a book is out of stock if the

units_on_hand in inventory = 0 at all stores or if just doesn’t show up in the inventory table).

f. List the name(s) of the branch with the smallest number of employees (might not be unique).

g. List the name of every branch along with the total value of the inventory at that branch. The value

of the inventory is determined by the units_on_hand times the book_price.

h. List the name of the branch with the highest inventory value. Your query should work correctly

even if the data in the tables changes.

i. In a single table, list the name of every branch along with a list of all the publishers (from

publisher) and the number of books from that publisher that the branch has in inventory, even if it

is zero. HINT: If there are 5 branches and 10 publishers in the database, the resulting table should

contain 5 x 10 = 50 rows.

j. List the name of every branch along with the name of the lowest price book currently in inventory

at that branch. A book is in inventory at a branch if the units_on_hand for that branch is greater

than 0.

III. SQL View, etc. exercises (10 pts)

i. You may have discovered that the inventory table is “incomplete” in that not every book is listed in the

inventory table for every branch. Let’s fix that. Write an INSERT INTO statement that will make the

inventory table complete. That is, if there is a book that is not listed in the inventory table for a branch

then insert a value of 0 for UNITS_ON_HAND for that book at that branch. After your query executes

the number of rows in inventory should be exactly the product of the number of rows in BOOK and the

number of rows in BRANCH.

ii. Write the SQL statement to create a view on the Yadkin database named book_info containing, for each

unique ISBN, a single row made up of the book_title, publisher_name, book_price, and a count of the

number of authors of the book.

(Grad students only)

iii. Let’s say that instead of “a count of the number of authors” of each book we wanted to create a view that

contained a list of the last names of all the authors for each book. So we want to see, for each unique

ISBN, a single row made up of the book_title, publisher_name, book_price, and a list of the last names of

the authors. Explain why this would be a difficult task to do in SQL. Could a recursive query be used?

Would that be all you need? You don’t need to write any SQL for this question, just your ideas.

http://www.6daixie.com/contents/15/2642.html