Question 9 is the only one correct, rest of them syntax errors..Can anyone help me
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Question 9 is the only one correct, rest of them syntax errors..Can anyone help me

  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Angry Question 9 is the only one correct, rest of them syntax errors..Can anyone help me

    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.

    Code:
    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
    
    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#;
    
    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’) 
    
    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’) 
    
    
    
    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; 
    
    
    
    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 ' 
    
    
    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 
    
    
    
    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') 
    
    
    
    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;
    
    
    
    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 2002
    Location
    Colorado Springs
    Posts
    5,253
    you could help yourself by posting the syntax error message.

    why do you keep putting commas in the join clause?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,477

    Thumbs down


    It seems you are just 'inventing' you own SQL syntax. Please check the SQL Reference Manual to find out the correct SQL syntax.

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  4. #4
    Join Date
    Sep 2006
    Posts
    4
    1 AND c.books = record
    *
    ERROR at line 4:
    ORA-00904: invalid column name

    2

    FROM customers c, JOIN orders, o
    *
    ERROR at line 2:
    ORA-00942: table or view does not exist

    3. ORDER BY title = (‘Fitness’)
    *
    ERROR at line 6:
    ORA-00933: SQL command not properly ended

    4. FROM customers c OUTER JOIN orders o
    *
    ERROR at line 2:
    ORA-00933: SQL command not properly ended


    5. COUNT() and GROUP BY
    *
    ERROR at line 4:
    ORA-00920: invalid relational operator

    6.WHERE author upper(bookauthor.lastname) = ' ADAMS '
    *
    ERROR at line 3:
    ORA-00920: invalid relational operator

    7. FROM books, JOIN promotion ('Shortest Poem')
    *
    ERROR at line 2:
    ORA-00933: SQL command not properly ended

    8.ORDER BY ('Becca Nelson')
    *
    ERROR at line 4:
    ORA-00920: invalid relational operator

    10. FROM state s,JOIN customers c, orders o (' Florida ')
    *
    ERROR at line 2:
    ORA-00933: SQL command not properly ended

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    how's the homework going?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by marist89
    how's the homework going?
    I'm sure somone will be along in a moment to save him the trouble.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Sep 2006
    Posts
    4
    i have have a learning disablity...I was just asking for help

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Get an example from the documentation and compare with your efforts.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

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



Click Here to Expand Forum to Full Width