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

    Unanswered: have i done this correct and i need help with #7

    i have done and attachment
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    i WOULD STRONGLY SUGGEST THAT YOU DO NOT OPEN AN UNKNOWN WORD DOCUMENT. If butterflytee wants to send something, save it as a flat text. A word document can have viruses.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    No risk for me. I took Rudy's advice for serious (post #6 here).

  4. #4
    Join Date
    Apr 2006
    Posts
    13
    1. Provide a SQL statement that displays the FIRSTNAME and LASTNAME of each customer separated by a comma. The FIRSTNAME and LASTNAME should be capitalized and concatenated together with a column alias of “Full Name”. The output should be sorted in ascending order by LASTNAME.

    SELECT FIRSTNAME||','||LASTNAME "FULL NAME"
    FROM CUSTOMERS;



    FULL NAME
    BONITA,MORALES
    RYAN,THOMPSON
    LEILA,SMITH
    THOMAS,PIERSON
    CINDY,GIRARD
    MESHIA,CRUZ
    TAMMY,GIANA
    KENNETH,JONES
    JORGE,PEREZ
    JAKE,LUCAS
    REESE,MCGOVERN
    WILLIAM,MCKENZIE
    NICHOLAS,NGUYEN
    JASMINE,LEE
    FULL NAME
    STEVE,SCHELL
    MICHELL,DAUM
    BECCA,NELSON
    GREG,MONTIASA
    JENNIFER,SMITH
    KENNETH,FALAH
    20 rows selected.




    2. Provide a SQL statement that displays the current date in the format MM/DD/YY.

    SELECT TO_CHAR( '10/25/06')
    FROM DUAL

    TO_CHAR(
    10/25/06



    3. Provide a SQL statement that displays the TITLE and publisher NAME of each book in the “COMPUTER” CATEGORY.

    SELECT TITLE, CATEGORY
    FROM BOOKS, PUBLISHER
    WHERE CATEGORY = 'COMPUTER'

    TITLE CATEGORY
    DATABASE IMPLEMENTATION COMPUTER
    HOLY GRAIL OF ORACLE COMPUTER
    HANDCRANKED COMPUTERS COMPUTER
    E-BUSINESS THE EASY WAY COMPUTER
    DATABASE IMPLEMENTATION COMPUTER
    HOLY GRAIL OF ORACLE COMPUTER
    HANDCRANKED COMPUTERS COMPUTER
    E-BUSINESS THE EASY WAY COMPUTER
    DATABASE IMPLEMENTATION COMPUTER
    HOLY GRAIL OF ORACLE COMPUTER
    HANDCRANKED COMPUTERS COMPUTER
    E-BUSINESS THE EASY WAY COMPUTER
    DATABASE IMPLEMENTATION COMPUTER
    HOLY GRAIL OF ORACLE COMPUTER
    TITLE CATEGORY
    HANDCRANKED COMPUTERS COMPUTER
    E-BUSINESS THE EASY WAY COMPUTER
    DATABASE IMPLEMENTATION COMPUTER
    HOLY GRAIL OF ORACLE COMPUTER
    HANDCRANKED COMPUTERS COMPUTER
    E-BUSINESS THE EASY WAY COMPUTER
    20 rows selected.




    4. Provide a SQL statement that displays the CUSTOMER# and LASTNAME for each customer along with the ORDER# of any order that the customer placed. The statement should include all customers including those that have not placed any orders.

    SELECT LASTNAME,ORDER#
    FROM CUSTOMERS C, ORDERS O
    WHERE C.CUSTOMER#=O.CUSTOMER#
    ORDER BY O.ORDER#

    LASTNAME ORDER#
    GIRARD 1000
    LUCAS 1001
    MCGOVERN 1002
    MORALES 1003
    FALAH 1004
    MONTIASA 1005
    SMITH 1006
    GIANA 1007
    PIERSON 1008
    GIRARD 1009
    SMITH 1010
    LUCAS 1011
    NELSON 1012
    LEE 1013
    LASTNAME ORDER#
    GIANA 1014
    FALAH 1015
    SMITH 1016
    SCHELL 1017
    MORALES 1018
    MONTIASA 1019
    JONES 1020
    21 rows selected.


    5. Provide a SQL statement that displays the total QUANTITY of books ordered.

    SELECT CUSTOMER#, FIRSTNAME, LASTNAME
    FROM ORDERS NATURAL JOIN ORDERITEMS NATURAL JOIN CUSTOMERS
    HAVING SUM(QUANTITY)=ALL
    (SELECT MAX(SUM(QUANTITY))
    FROM ORDERS NATURAL JOIN ORDERITEMS GROUP BY CUSTOMER#)
    GROUP BY CUSTOMER#, FIRSTNAME, LASTNAME;

    CUSTOMER# FIRSTNAME LASTNAME
    1007 TAMMY GIANA





    6. Provide a SQL statement that displays the TITLE of all books that COST more than the average cost of all books.

    SELECT TITLE,AVG(COST)
    FROM BOOKS
    GROUP BY TITLE



    TITLE AVG(COST)
    BIG BEAR AND LITTLE DOVE 5.32
    BODYBUILD IN 10 MINUTES A DAY 18.75
    BUILDING A CAR WITH TOOTHPICKS 37.8
    COOKING WITH MUSHROOMS 12.5
    DATABASE IMPLEMENTATION 31.4
    E-BUSINESS THE EASY WAY 37.9
    HANDCRANKED COMPUTERS 21.8
    HOLY GRAIL OF ORACLE 47.25
    HOW TO GET FASTER PIZZA 17.85
    HOW TO MANAGE THE MANAGER 15.4
    PAINLESS CHILD-REARING 48
    REVENGE OF MICKEY 14.2
    SHORTEST POEMS 21.85
    THE WOK WAY TO COOK 19
    14 rows selected.


    7. Provide a SQL statement that displays each SHIPSTATE along with the number of orders shipped to each state. Include only those states that more than 3 orders were shipped to.




    8. Provide a SQL statement that displays each book CATEGORY in the database. The category should only appear once in the output.


    SELECT TITLE, CATEGORY
    FROM BOOKS

    TITLE CATEGORY
    BODYBUILD IN 10 MINUTES A DAY FITNESS
    REVENGE OF MICKEY FAMILY LIFE
    BUILDING A CAR WITH TOOTHPICKS CHILDREN
    DATABASE IMPLEMENTATION COMPUTER
    COOKING WITH MUSHROOMS COOKING
    HOLY GRAIL OF ORACLE COMPUTER
    HANDCRANKED COMPUTERS COMPUTER
    E-BUSINESS THE EASY WAY COMPUTER
    PAINLESS CHILD-REARING FAMILY LIFE
    THE WOK WAY TO COOK COOKING
    BIG BEAR AND LITTLE DOVE CHILDREN
    HOW TO GET FASTER PIZZA SELF HELP
    HOW TO MANAGE THE MANAGER BUSINESS
    SHORTEST POEMS LITERATURE
    14 rows selected.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. incorrect
    you've concatented them in the wrong order, and you're missing the ORDER BY clause

    2. incorrect
    you are showing a string, not the current date
    hint: look up the CURRENT_DATE function

    3. incorrect
    you are not showing the title, and you have many duplications
    hint: your join is incorrect

    4. incorrect
    you are not showing the lastname
    hint: you need a LEFT OUTER JOIN

    5. incorrect
    you are not showing the total quantity

    6. incorrect
    you are not calculating the overall average correctly

    7. this one's really tough

    8. incorrect
    you are not showing each category only once
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Hint, for number seven look at group by and having clauses
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    Apr 2006
    Posts
    13
    For #7 it keeps coming up missing expression in line 1


    SELECT <shipstate>, <order_to_state>
    FROM <tablename>
    GROUP BY <state>
    HAVING COUNT(<order_to_state>)>3

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    If you used valid SQL syntax, no errors would be reported.
    Angle brackets ("<" & ">") are NOT valid as your are trying (ab)use them.
    Valid SQL syntax can be found at http://tahiti.oracle.com in the fine SQL Reference manual.
    You just might learn something if you took the time to Read The Fine Manual above.
    Last edited by anacedent; 10-27-06 at 20:44.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by butterflyTee
    2. Provide a SQL statement that displays the current date in the format MM/DD/YY.

    SELECT TO_CHAR( '10/25/06')
    FROM DUAL

    TO_CHAR(
    10/25/06
    BT, think about the answer you gave to this question.

    Possibly on the day you posted it, that was the current date, although I'm puzzled about what you think is the difference between '10/25/06' and TO_CHAR('10/25/06') - it's like the difference between 'banana' and TO_CHAR('banana'). What do you think TO_CHAR() does? However, don't you think it is more likely that the question is about getting the current date whenever the query is executed?

Posting Permissions

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