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

[butterflyTee]

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.

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

[Rahulbatra]

The first question has syntax error on the part of
c.books = record
defined in
SELECT title, name, customer
FROM books NATURAL JOIN publisher
WHERE books.pubid = publisher.pubid
AND c.books = record

The c.books = record is not defined in the query and usage of this results in error.

[sammy]

In the second query placed as
SELECT lastname, firstname, order#
FROM customers c, JOIN orders, o
WHERE c.customer# = o.customer#(+)
ORDER BY c.customer#;

The syntax error lies in the part of JOIN orders, o. It must be written as
JOIN orders o
There must not be a comma placed between table name orders and alias name o and so the correct query for execution must be as below:
SELECT lastname, firstname, order#
FROM customers c, JOIN orders o
WHERE c.customer# = o.customer#(+)
ORDER BY c.customer#;

[Ralph]

The third query given is as below:

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’)

The syntax error occurs in the place JOIN orders. The joins would have been implemented in the table level for all tables using the concept of referential integrity by defining primary key and foreign key on all tables. JOIN must not be specified explicitly by using word JOIN in the syntax. So the corrected query must be as below:

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’)