(Individual) Take-Home Assignment Set 2
(Writing and executing SQL code for SQL Server 2008 is expected)
Text Chapter 2: Introduction to Structured Query Language
This assignment is a continuation of the previous take-home assignment. Please read the solution for the previous assignment before starting this one. The following are three relations (without any relationship among them) designed for little John’s book store.`
USER_TABLE
(FirstName, LastName, Email, CreditCardNumber, PhoneNumber, ShippingAddress1, ShippingAddress2, ShippingCity, ShippingState, ShippingZip, Password)
BOOK_TABLE
(ISBN, Title, Price, PublicationYear, Description, AvailableCount, AuthorFirstName, AuthorLastName, Category, Publisher)
ORDER_TABLE
(OrderNumber, Email, ISBN, OrderDate, Status, Quantity, OrderCost)
Try to understand the three relations represented above. Try to understand the relationship between them. (Though the relationships are not explicit, try to understand them based on the Column Names.)
1. Name the relations which are independent of others and which are dependent:
2. The bookstore wants to understand their customer base in Atlanta. Please write a SQL query to provide them the list of all the users in Atlanta.
3. The bookstore wants to understand their book collection. Please write a SQL query to provide them count of unique books they have.
4. The bookstore wants to know the number of orders placed for the book with the ISBN '0439136350'. Please write a SQL query to display the ISBN, Book Title and the Number of Orders for that book (NOT the sum of QUANTITY in each ORDER) [Hint: Use Table Joins and GROUP BY]
5. The bookstore wants to find the value of all their books. Can you provide them a simple query to get the answer? [Hint: Use the keyword SUM and read - http://msdn.microsoft.com/en-us/library/ms176019.aspx ]
6. Bonus Question: Little John is a little skeptical about the database design. He feels some deficiency in the database table design. Will you be able to assist him? [Hint: Compare little John’s ORDER table with the ORDER SUMMARY screens of online stores like Amazon, eBay.]