Lab Assignment
To perform the following assignments, refer to the tables created in the JLDB_Build_8.sql script at the beginning of the chapter. Give the SQL statements and output for the following data requests:
1. Which customers live in New Jersey? List each customer's last name, first name, and state.
2. Which orders shipped after April 1, 2009? List each order number and the date it shipped.
3. Which books aren't in the Fitness category? List each book title and category.
4. Which customers live in Georgia or New Jersey? Put the results in ascending order by last name. List each customer's customer number, last name, and state. Write this query in two different ways.
5. Which orders were placed on or before April 1, 2009? List each order number and order date. Write this query in two different ways.
6. List all authors whose last name contains the letter pattern "IN." Put the results in order of last name, then first name. List each author's last name and first name.
7. List all customers who were referred to the bookstore by another customer. List each customer's last name and the number of the customer who made the referral.
8. Display the book title and category for all books in the Children and Cooking categories. Create three different queries to accomplish this task: a) a search pattern operation, b) a logical operator, and c) another operator not used in a or b.
9. Use a search pattern to find any book title with "A" for the second letter and "N" for the fourth letter. List each book's ISBN and title. Sort the list by title in descending order.
10. List the title and publish date of any computer book published in 2005. Perform the task of searching for the publish date by using three different methods: a) a range operator, b) a logical operator, and c) a search pattern operation.