FROM: ORACLE 9i
Give the SQL statements that determine the following:
1. Which customers lives in New Jersey?
SELECT * customer
WHERE state = 'NJ'
2.Which orders were shipped after April 1, 2003
WHERE shipdate to_date('01/04/2003', DD/MM/YYYY')
3. Which books are not in Fitness Category?
WHERE category != < 'Fitness'
4.Which customers live in either Georgia or New Jersey? Put the results in ascending order by last name.
WHERE states IN ('GA or 'NJ')
ORDER BY "LName" asce
5. Which orders were place before April 2, 2003?
WHERE shipdate to_date('01/02/2003', DD/MM/YYYY')
6. List all authors whose last name contains the letter pattern "IN". Put the results in order of last name, then first name.
WHERE lastname LIKE '%in%'
ORDER BY "Lastname", "Firstname"
7. List all customers who were referred to the bookstore by another customer.
SELECT lastname, firstname, referred
WHERE referred IS NOT NULL
ORDER BY referred;
8. Use a search pattern to list all books in the Children and Cooking Categories. Do not use any logical operators in the WHERE clause.
WHERE IN ('Children and Cooking')
9. Use a search pattern to find any book where the title has an "A" for the second letter in the title, and an "N" for the fourth.
WHERE substr(title, 2, 1) = 'A' and substr(title, 4, 1) = 'N'
10. List the title of any computer book that where published in 2001.
WHERE category = 'Computer' AND pubid = 2001