Home » SQL & PL/SQL » SQL & PL/SQL » I keep trying fix these but it keep coming up syntax errors. PLEASE HELP (merged 3 threads)
icon9.gif  I keep trying fix these but it keep coming up syntax errors. PLEASE HELP (merged 3 threads) [message #194601] Fri, 22 September 2006 20:56 Go to next message
butterflyTee
Messages: 4
Registered: September 2006
Junior Member
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')


Embarassed Mad
Re: Is this right..I tried all kind of ways...frustrated...due in 13hrs [message #194602 is a reply to message #194601] Fri, 22 September 2006 21:09 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It appears that you did not like the lack of folks doing your homework for you over on http://www.dbforums.com & decided that rather than do you own work you'd see if you could find some willing sucker here to do your homework assignment.
Too bad for you that you can only spell SQL.
IIRC every one of your attempts had syntax errors.

It also appears that you don't know the difference between SQL & PL/SQL. So, this is posted to the wrong forum here.

HAND & Good Luck.

P.S.
I suggest you pick a different major.

[Updated on: Fri, 22 September 2006 21:29] by Moderator

Report message to a moderator

Re: Is this right..I tried all kind of ways...frustrated...due in 13hrs [message #194605 is a reply to message #194602] Fri, 22 September 2006 23:08 Go to previous messageGo to next message
butterflyTee
Messages: 4
Registered: September 2006
Junior Member
Please you did have 2 knock me down with words..i justed asked and wanted help..And that was rude and mean to tell me to pick a different major
icon1.gif  IS THIS RIGHT AND CAN YOU SHOW ME OTHER WAYS 2 WRITE THIS PLEASE [message #194664 is a reply to message #194601] Sat, 23 September 2006 13:12 Go to previous messageGo to next message
butterflyTee
Messages: 4
Registered: September 2006
Junior Member
Give the SQL statements that determine the following:

Which customers lives in New Jersey?


SELECT *
FROM orders
WHERE shipdate > '01-APR-03'
/

Which orders were shipped after April 1, 2003


SELECT *
FROM orders
WHERE shipdate > '01-APR-03'
/

Which books are not in Fitness Category?

SELECT *
FROM books
WHERE category <> 'FITNESS'
/


Which customers live in either Georgia or New Jersey? Put the results in ascending order by last name.

SELECT *
FROM customers
WHERE state = 'GA' or state = 'NJ'
ORDER BY lastname
/

Which orders were place before April 2, 2003?

SELECT *
FROM orders
WHERE orderdate < '02-APR-03'
/

List all authors whose last name contains the letter pattern "IN". Put the results in order of last name, then first name.
SELECT *
FROM author
WHERE lname LIKE '%IN%'
ORDER BY lname, fname
/


List all customers who were referred to the bookstore by another customer.

SELECT *
FROM customers
WHERE referred IS NOT NULL
/


Use a search pattern to list all books in the Children and Cooking Categories. Do not use any logical operators in the WHERE clause.
SELECT *
FROM books
WHERE category LIKE '%C%N%'
/


Use a search pattern to find any book where the title has an "A" for the second letter in the title, and an "N" for the fourth.

SELECT *
FROM books
WHERE title LIKE '_A_N%'
/

List the title of any computer book that where published in 2001.

SELECT *
FROM books
WHERE pubdate LIKE '%01'
/

Embarassed Mad

[Updated on: Sat, 23 September 2006 13:21]

Report message to a moderator

Re: IS THIS RIGHT AND CAN YOU SHOW ME OTHER WAYS 2 WRITE THIS PLEASE [message #194666 is a reply to message #194664] Sat, 23 September 2006 14:26 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Please stop posting your beginner sql homework assignments as new threads in the pl/sql experts forum. Please continue on this thread in the newbies forum, where they have been merged. You need to test your own code. If it returns the correct results, then it is right. If it produces an error or does not return the correct results, then it is wrong. If you cannot figure out why something is producing an error or returning the wrong results, then you need to post a copy and paste of your attempted run, including the results. It also helps if you provide your table structures and any other releveant information. Please read the sticky at the top of the forum for what we expect. You will not learn anything by having others just do your homework for you. You need to understand how things work and how to figure out why they are not working, by doing things like looking up error codes, and searching for similar examples.

You should not be offended by antecedent's suggestion. Computer programming is not for everyone. What you have posted so far shows that you have no idea what you are doing. I don't know how long you have been trying to do this. If this is your first computer class and you missed some classes due to illness or some such thing, then you may just need to retake the class and start over. If not, you may find that something else suits you better. A lot of us have changed our major one or more times. I think it is better to tell someone when it appears they may be in the wrong field for them than to encourage them to waste time pursuing the wrong path. Good luck with whatever you decide.





Re: IS THIS RIGHT AND CAN YOU SHOW ME OTHER WAYS 2 WRITE THIS PLEASE [message #194667 is a reply to message #194664] Sat, 23 September 2006 14:41 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Which customers lives in New Jersey?


SELECT *
FROM orders
WHERE shipdate > '01-APR-03'
/
WRONG. It looks like you posted the answer to the question below.


Which orders were shipped after April 1, 2003


SELECT *
FROM orders
WHERE shipdate > '01-APR-03'
/
You should use explicit conversions of characters to dates, using TO_DATE rather than rely on implicit conversions, because if the nls_date_format does not match, it will produce an error or incorrect results.


Which books are not in Fitness Category?

SELECT *
FROM books
WHERE category <> 'FITNESS'
/


Which customers live in either Georgia or New Jersey? Put the results in ascending order by last name.

SELECT *
FROM customers
WHERE state = 'GA' or state = 'NJ'
ORDER BY lastname
/


Which orders were place before April 2, 2003?

SELECT *
FROM orders
WHERE orderdate < '02-APR-03'
/
You should use explicit conversions of characters to dates, using TO_DATE rather than rely on implicit conversions, because if the nls_date_format does not match, it will produce an error or incorrect results.



List all authors whose last name contains the letter pattern "IN". Put the results in order of last name, then first name.
SELECT *
FROM author
WHERE lname LIKE '%IN%'
ORDER BY lname, fname
/


List all customers who were referred to the bookstore by another customer.

SELECT *
FROM customers
WHERE referred IS NOT NULL
/


Use a search pattern to list all books in the Children and Cooking Categories. Do not use any logical operators in the WHERE clause.
SELECT *
FROM books
WHERE category LIKE '%C%N%'
/


Use a search pattern to find any book where the title has an "A" for the second letter in the title, and an "N" for the fourth.

SELECT *
FROM books
WHERE title LIKE '_A_N%'
/

List the title of any computer book that where published in 2001.

SELECT *
FROM books
WHERE pubdate LIKE '%01'
/
What data type is pubdate? If it is a date datatype as it should be then you should compare in some manner that does not rely on the nls_date_format matching.



Without knowing your data strucutre and sample data, it is impossible to tell if the rest are correct. You need to test them.

icon1.gif  I keep trying fix these but it keep coming up syntax errors..PLEASE HELP [message #195639 is a reply to message #194601] Fri, 29 September 2006 19:11 Go to previous messageGo to next message
butterflyTee
Messages: 4
Registered: September 2006
Junior Member
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')

Embarassed Mad

Re: I keep trying fix these but it keep coming up syntax errors..PLEASE HELP [message #195641 is a reply to message #195639] Fri, 29 September 2006 19:26 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
The homework tutor is down the hall & 2nd door on the left.
If you get desparate, you could try actually reading the SQL Reference Manual found at http://tahiti.oracle.com.

[Updated on: Fri, 29 September 2006 19:27] by Moderator

Report message to a moderator

Previous Topic: Can I improve create index
Next Topic: getting kup-00554 when creating external table
Goto Forum:
  


Current Time: Fri Dec 09 00:08:31 CST 2016

Total time taken to generate the page: 0.24895 seconds