0

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.

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


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


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


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

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

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


here are the syntax errors i got


 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
2
Contributors
1
Reply
2
Views
11 Years
Discussion Span
Last Post by alit2002
0

Hi
The first query you list suggests that a column doesn't exist. Check this by typing

SQL> desc books

This will give you a list of columns.

2. I don't think the word join should be between customers and orders. A join would be where customers.id = orders.id

3. you only put 'order by title' There is no ='fitness' on an order by clause.

4 Outer join is represented by (+)

from customers(+),orders

5. select book,sum(money)
from books
group by book.

6. where upper(author)='ADAMS'

7 select col1,col2,col3
from table1, table2
where column_name between value1 and value2;

8. order by can't have values with it

9.no JOIN word in the from clause.


Hope it helps.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.