FROM: ORACLE 9i
Give the SQL statements that determine the following:
1. Which customers lives in New Jersey?
SELECT * customer
FROM customers
WHERE state = 'NJ'
2.Which orders were shipped after April 1, 2003
SELECT *
FROM orders
WHERE shipdate to_date('01/04/2003', DD/MM/YYYY')
3. Which books are not in Fitness Category?
SELECT *
FROM books
WHERE category != < 'Fitness'
4.Which customers live in either Georgia or New Jersey? Put the results in ascending order by last name.
SELECT *
FROM customers
WHERE states IN ('GA or 'NJ')
ORDER BY "LName" asce
5. Which orders were place before April 2, 2003?
SELECT *
FROM orders
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.
SELECT lastname
FROM authors
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
FROM customers
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.
SELECT categories
FROM books
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.
SELECT books
FROM title
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.
SELECT *
FROM books
WHERE category = 'Computer' AND pubid = 2001
:sad: :mad: