Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Posts
    13

    Red face Unanswered: Question 9 is the only one that work,rest of them coming up syntax errors please help

    USING:ORACLE 9i

    For each of the following tasks, determine (a) the SQL statement needed to perform the stated task using the traditional approach and (b) the SQL statement needed to perform the stated task the JOIN keyword.

    1. A list that displays the title of each book & the name and phone number of the person at the publisher's office whom you would need to contact to record each book.

    SELECT title, name, customer
    FROM books NATURAL JOIN publisher
    WHERE books.pubid = publisher.pubid
    AND c.books = record

    2. Determine which orders have not yet shipped & the name of the customer that placed each order. Sort results by dates on the orders was placed.

    SELECT lastname, firstname, order#
    FROM customers c, JOIN orders, o
    WHERE c.customer# = o.customer#(+)
    ORDER BY c.customer#;

    3. List the customer number & names of all individuals have purchased books in the Fitness Category.

    SELECT title, firstname, lastname
    FROM customers,JOIN orders, orderitems, books
    WHERE customers.customer#=orders.customer#
    AND orders.order#=orderitems.order#
    AND orderitems.isbn=books.isbn
    ORDER BY title = (‘Fitness’)


    4. Determine book Jake Lucas has purchased.

    SELECT lastname, firstname, order#
    FROM customers c OUTER JOIN orders o
    WHERE lastname = 'Lucas' and firstname = 'Jake'
    ON c.customer# = o.customers#
    ORDER BY c.customers# = (‘Jake Lucas’)



    5. Determine profit of each book sold to Jake Lucas. Sort results by date of order. If more than 1 book was ordered, have results sorted by profit amount in descending order.

    SELECT lastname, firstname, order#
    FROM customers, JOIN orders, orderitems, books
    WHERE "Profit"
    COUNT() and GROUP BY
    ORDER BY "Profit" desc;



    6. Which book was written by an author with the last name Adams ?

    SELECT title, authorid
    FROM books, JOIN bookauthor
    WHERE author upper(bookauthor.lastname) = ' ADAMS '


    7. What gift will a customer who orders the book Shortest Poems receive?

    SELECT title, customer, gift
    FROM books, JOIN promotion ('Shortest Poem')
    ON retail BETWEEN minretail AND maxretail



    8. Identify the author(s) of books ordered by Becca Nelson


    SELECT lastname, firstname, title, customers authorid
    FROM books,JOIN customers, bookauthor
    WHERE customers
    ORDER BY ('Becca Nelson')



    9. Display list of all books in BOOKS table. If a book has been ordered by a customer, also list the corresponding order number(s) & state which the customer resides.

    SELECT title, o.order#, state
    FROM books b LEFT JOIN orderitems i
    ON b.isbn=i.isbn
    left JOIN orders o
    ON o.order#=i.order#
    left JOIN customers c
    ON o.customer#=c.customer#
    ORDER BY title;



    10. Produce a list of all customers live in the state of Florida & ordered books about computers.

    SELECT lastname, firstname, state, books, order#
    FROM state s,JOIN customers c, orders o (' Florida ')
    WHERE c. customer# AND s. customer# = o. customer#
    ON o.customer#=c.customer#
    ORDER BY ('Computers')


  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    So go visit http://tahiti.oracle.com & read the Fine SQL Reference Manual to see what correct SQL syntax really is; rather than making fantasy guesses for this HOMEWORK assignment.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is the third thread you have posted, butterflyTee, with homework questions

    we do not provide answers to homework questions

    please do not start another thread unless you change the way you ask the questions

    incorrect way: here are my questions, please help

    correct way: i have tried to answer this question like this (show your attempt) and i did it like this because (show your reasons why you think it is the correct approach) and the results that it gave are like this (show the results that your query produces) and i cannot understand why i am getting the wrong results

    if you are getting syntax errors, then you simply must go to the manual and find out why yourself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    A cool one

    SELECT lastname, firstname, order#
    FROM customers, JOIN orders, orderitems, books
    WHERE "Profit"
    COUNT() and GROUP BY
    ORDER BY "Profit" desc;

  5. #5
    Join Date
    Apr 2006
    Posts
    13

    Post ok, is this correct

    #1
    SELECT title, contact, phone
    FROM books, publisher
    WHERE books.pubid = publisher.pubid
    /
    SELECT title, contact, phone
    FROM books NATURAL JOIN publisher
    /
    #2
    SELECT firstname, lastname, order#
    FROM customers, orders
    WHERE customers.customer# = orders.customer#
    AND shipdate IS NULL
    ORDER BY orderdate
    /
    SELECT firstname, lastname, order#
    FROM customers JOIN orders
    ON customers.customer# = orders.customer#
    WHERE shipdate IS NULL
    ORDER BY orderdate
    /
    #3
    SELECT c.customer#, firstname, lastname
    FROM customers c, orders o, orderitems i, books b
    WHERE c.customer# = o.customer#
    AND o.order# = i.order#
    AND i.isbn = b.isbn
    AND category = 'FITNESS'
    /
    SELECT customer#, firstname, lastname
    FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books
    WHERE category = 'FITNESS'
    /
    #4
    SELECT DISTINCT title
    FROM customers c, orders o, orderitems i, books b
    WHERE c.customer# = o.customer#
    AND o.order# = i.order#
    AND i.isbn = b.isbn
    AND firstname = 'JAKE'
    AND lastname = 'LUCAS'
    /
    SELECT DISTINCT title
    FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books
    WHERE firstname = 'JAKE'
    AND lastname = 'LUCAS'
    /
    #5
    SELECT title, retail-cost
    FROM customers c, orders o, orderitems i, books b
    WHERE c.customer# = o.customer#
    AND o.order# = i.order#
    AND i.isbn = b.isbn
    AND firstname = 'JAKE'
    AND lastname = 'LUCAS'
    ORDER BY orderdate, retail-cost desc
    /
    SELECT title, retail-cost
    FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books
    WHERE firstname = 'JAKE'
    AND lastname = 'LUCAS'
    ORDER BY orderdate, retail-cost desc
    /
    #6
    SELECT title
    FROM books, bookauthor, author
    WHERE books.isbn = bookauthor.isbn
    AND bookauthor.authorid = author.authorid
    AND lname = 'ADAMS'
    /
    SELECT title
    FROM books JOIN bookauthor ON books.isbn = bookauthor.isbn
    JOIN author ON bookauthor.authorid = author.authorid
    WHERE lname = 'ADAMS'
    /
    #7
    SELECT gift
    FROM books, promotion
    WHERE retail BETWEEN minretail AND maxretail
    AND title = 'SHORTEST POEMS'
    /
    SELECT gift
    FROM books JOIN promotion
    ON retail BETWEEN minretail AND maxretail
    WHERE title = 'SHORTEST POEMS'
    /
    #8
    SELECT lname, fname, title
    FROM books b, orders o, orderitems i, customers c, bookauthor t, author a
    WHERE c.customer# = o.customer#
    AND o.order# = i.order#
    AND i.isbn = b.isbn
    AND b.isbn = t.isbn
    AND t.authorid = a.authorid
    AND firstname = 'BECCA'
    AND lastname = 'NELSON'
    /
    SELECT lname, fname, title
    FROM customers c JOIN orders o ON c.customer# = o.customer#
    JOIN orderitems i ON o.order# = i.order#
    JOIN books b ON i.isbn = b.isbn
    JOIN bookauthor t ON b.isbn = t.isbn
    JOIN author a ON t.authorid = a.authorid
    WHERE firstname = 'BECCA'
    AND lastname = 'NELSON'
    /
    #9
    SELECT title, o.order#, state
    FROM books b, orders o, orderitems i, customers c
    WHERE c.customer#(+) = o.customer#
    AND o.order#(+) = i.order#
    AND i.isbn(+) = b.isbn
    /
    SELECT title, o.order#, state
    FROM books b OUTER JOIN orderitems i OUTER JOIN orders o OUTER JOIN customers c
    ON c.customer# = o.customer#
    AND o.order# = i.order#
    AND i.isbn = b.isbn
    /
    #10
    SELECT customers.customer#
    FROM books, orders, orderitems, customers
    WHERE customers.customer# = orders.customer#
    AND orders.order# = orderitems.order#
    AND orderitems.isbn = books.isbn
    AND state = 'FL'
    AND category = 'COMPUTER'
    /
    SELECT customer#
    FROM books NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN customers
    WHERE state = 'FL'
    AND category = 'COMPUTER'
    /

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I believe you DO have a test schema, don't you? So execute all those statements and you'll see whether those queries work correctly or not.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •