0

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:

2
Contributors
3
Replies
5
Views
11 Years
Discussion Span
Last Post by Miris_1
1

is this finally right and is there other ways 2 do this?
SELECT *
FROM customers
WHERE state = 'NJ'
/

SELECT *
FROM orders
WHERE shipdate > '01-APR-03'
/

SELECT *
FROM books
WHERE category <> 'FITNESS'
/

SELECT *
FROM customers
WHERE state = 'GA' or state = 'NJ'
ORDER BY lastname
/

SELECT *
FROM orders
WHERE orderdate < '02-APR-03'
/

SELECT *
FROM author
WHERE lname LIKE '%IN%'
ORDER BY lname, fname
/

SELECT *
FROM customers
WHERE referred IS NOT NULL
/

SELECT *
FROM books
WHERE category LIKE '%C%N%'
/

SELECT *
FROM books
WHERE title LIKE '_A_N%'
/

SELECT *
FROM books
WHERE pubdate LIKE '%01'
/

0

8)

SELECT title, category
FROM books
WHERE category IN ('CHILDREN', 'COOKING');

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.