Member Avatar for butterflyTee

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
  • 2K Views
  • 9 Years Discussion Span
  • Latest Post Latest Post by Miris_1

Dani AI

Generated

Nice progress, . A few Oracle 9i gotchas are tripping you up, mainly dates, case, and quoted identifiers. Do not rely on implicit string-to-date conversion. Use ANSI date literals or TO_DATE with a mask. Also, quoted column names become case-sensitive identifiers, so avoid "Lastname" unless the column was created that way.

-- Shipped strictly after 2003-04-01 (date-safe and NLS-proof)
WHERE TRUNC(shipdate) > DATE '2003-04-01'

-- Placed strictly before 2003-04-02
WHERE orderdate < DATE '2003-04-02'

String comparisons in Oracle are case-sensitive. Normalize both sides, and watch for NULLs when testing inequality.

-- Last names containing IN (case-insensitive)
WHERE UPPER(lname) LIKE '%IN%'
ORDER BY lname, fname

-- Not in Fitness, but also handle NULL categories
WHERE NVL(UPPER(category), 'N/A') <> 'FITNESS'

For state membership, prefer IN, and let ASC be implicit. Use bind variables to keep things reusable and index-friendly.

-- Two states, ordered by last name
WHERE state IN (:s1, :s2)
ORDER BY lastname

On Q8, is right that membership tests avoid logical operators. Another pattern that also avoids OR is a tiny inline lookup you can join to:

SELECT b.title
FROM books b
JOIN (SELECT 'CHILDREN' AS cat FROM dual
      UNION ALL SELECT 'COOKING' FROM dual) t
  ON UPPER(b.category) = t.cat

For the 2001 publications, compare dates, not strings or IDs:

-- Computer books published in 2001
WHERE UPPER(category) = 'COMPUTER'
  AND pubdate >= DATE '2001-01-01'
  AND pubdate <  DATE '2002-01-01'

Bonus tip: for "referred by another customer," verify the ref actually exists:

WHERE EXISTS (
  SELECT 1 FROM customers c2
  WHERE c2.customer_id = c1.referred_by
)
Member Avatar for butterflyTee

I Tried It Again, And It Still Not Doing It Way

Member Avatar for butterflyTee

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'
/

Member Avatar for Miris_1
8)

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.