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

    Post Unanswered: Am I Doing These Data Commands Right To Bring Up Ifo? Due In 13hrs

    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 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, 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, 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
    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, orders, orderitems, books
    WHERE "Profit"
    ORDER BY "Profit" desc;


    6. Which book was written by an author with the last name Adams?
    SELECT title, authorid
    FROM books, bookauthor
    WHERE author ('ADAMS')


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

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


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

    SELECT lastname, firstname, title, authorid
    FROM books, bookauthor
    WHERE authors
    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 lastname, firstname, state, books, order#
    FROM state s, customers c, orders o
    WHERE c. customer# AND s. customer# = o. customer#

    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, customers c, orders o ('Florida')
    WHERE c. customer# AND s. customer# = o. customer#
    ORDER BY ('Computers')



    THANK YOU IN ADVANCE FOR YOUR HELP

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. no, there is no table called c
    2. no, wrong ORDER BY and no selection based on shipdate
    3. no, wrong SELECT columns, ORDER BY is incorrect
    4. no, wrong SELECT columns, ORDER BY is incorrect
    5. no, wrong SELECT columns, WHERE clause is incorrect, ORDER BY is incorrect
    6. no, JOIN is incorrect, WHERE clause is incorrect
    7. no, FROM clause is incorrect, WHERE clause missing
    8. no, JOIN is incorrect, WHERE clause is incorrect, ORDER BY is useless
    9. no, books table not referenced, WHERE clause is incorrect
    10. no, FROM clause is incorrect, ORDER BY is incorrect

    i am afraid you would get a mark of 0 out of 10

    but do not despair -- next time, with a little more work done in advance of the day it's due, i'm sure you'll do better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What school are you in? What year are you in and what's your major?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Apr 2006
    Posts
    13
    florida community college at jacksonville..junior

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you have access to an Oracle environment?

    Because if you did, then you could test the executions...becuse you would see a lot of problems....what version of ORACLE is this for?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Brett Kaiser
    what version of ORACLE is this for?
    SELECT PARSE(VERSION FROM LINETEXT)
    FROM POSTS
    WHERE POST# = 1
    AND LINE# = 1

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2006
    Posts
    13
    ORACLE 9i: SQL WITH AN INTRODUCTION TO PL/SQL...USING THE ORACLE SQL *PLUS
    Last edited by butterflyTee; 09-22-06 at 15:20.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by butterflyTee
    can you run it...
    you are asking us to run your queries for you?

    not gonna happen

    besides every one of them will fail

    you cannot correct this situation by trying to understand the error messages, you need to go back and try to understand the basic concepts of a simple SQL statement and its main clauses: SELECT, FROM, WHERE, ORDER BY
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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