•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the Oracle section within the Web Development category of DaniWeb, a massive community of 370,612 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,076 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our Oracle advertiser:
Views: 1251 | Replies: 2 | Solved
 |
•
•
Join Date: Feb 2006
Posts: 43
Reputation: 
Rep Power: 3
Solved Threads: 0
Light Poster
Am I Doing This Right 2 Bring Up Info 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
:mad:
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
:mad: Last edited by butterflyTee : Sep 20th, 2006 at 12:03 pm.
•
•
Join Date: Feb 2006
Posts: 43
Reputation:
Rep Power: 3
Solved Threads: 0
Light Poster
I Tried It Again, And It Still Not Doing It Way
•
•
Join Date: Feb 2006
Posts: 43
Reputation:
Rep Power: 3
Solved Threads: 0
Light Poster
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'
/
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'
/
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb Oracle Marketplace
Linear Mode
Similar Threads
- .txt/array problems (C++)
- PHP, ASP, ColdFusion, what's your fav? (IT Technologies and Trends)
- Specialgoods.info infection (Viruses, Spyware and other Nasties)
- How I removed hotoffers.info (Viruses, Spyware and other Nasties)
- HOTOFFER.INFO Malware removal (Viruses, Spyware and other Nasties)
- need help copying info from computer to disc (Storage)
- Sequential Access Data Files (Visual Basic 4 / 5 / 6)
Other Threads in the Oracle Forum
- Previous Thread: Is this right...frustrated..please help..due in 10hrs
- Next Thread: 1Z0-007 & 1Z0-147 guidance required !!
