Determine which orders haven‘t yet shipped and the name of the customer who placed the order. Sort the results by the date on which the order was placed. Sort data by orderdate with most current date at the top.

Week 4 70 pts

This week assignment has a little of everything: Some CREATE TABLE statements with FOREIGN KEYS, ALTER TABLE, string functions (CONCAT, SUBSTR), UPDATES, copy tables, DISTINCT, WHERE, ORDER BY, and JOINS. It just gets more fun!

1. Write 3 create table statements to create the tables below. Include datatypes as listed and appropriate foreign and primary key syntax. Run your statements to make sure they work. The order you create your tables will make a difference. (10)
• Yellow Key = Primary Key
• Red filled Diamond = Foreign Key
• Blue Filled Diamond = required field or not null
• Blue unfilled Diamond = optional field or null

2. Determine which categories are represented in the current book inventory. List each category only once. (5)

3. List all customers who have been referred to the bookstore by another customer. List each customer’s firstname, lastname, and the number of the customer who made the referral. Do not use ‘Where Referred is > 0’ (5)

4. Determine the length of data stored in the lastname field of the customers table. Make sure each different length value is displayed only once. Sort data in ASCENDING order(5)

5. Copy the books table. Call the new table New_books. Put all the books from the original books table into the new_books table. (5)

6. Add 10% to the cost of all books in the new_books table from the category Family Life. Actually, change the values in the database, not just select and display. (5)

7. Alter the new–books table to include a column called Cat_isbn with data type char. (5)

8. Using string functions, write an update statement that add the following values to Cat_isbn column created in previous problem. CAT_ISBN should contain the first two characters from the category followed by the last 3 digits of the ISBN. Include a dash between the two values. (see picture below). (5)

9. Produce a list of all customers’ names in which the first letter of the first and last names is in uppercase and the rest are in lower case. You will need to nest string functions to make this work. MySQL doesn’t have a Proper string function like some languages. You will need to grab the last characters and make them lowercase and attach them to the first character which is already upper case. (5)

10. What books did author James Austin write? (Show isbn, title, author first and last name concatenated together into one column). (5)

11. Determine which orders haven‘t yet shipped and the name of the customer who placed the order. Sort the results by the date on which the order was placed. Sort data by orderdate with most current date at the top. (5)

12. Write a query to show the isbn, title, pubdate and publisher name for all books in the family life category. (5)

13. Show me the customers from Florida and all the books they ordered. Display the columns as shown below and order the data by last name.. (5)