Subqueries using Oracle

Question

I have been working on this problem for about 8 hours with no success. Does anyone have any ideas?

Problem: Using the correct tables, create a sub query using either join operation you wish that will list the customer number, first and last name concatenated together, and city for all customers who have placed an order for the most expensive book (based on retail price). Give the combined customer names column and alias of "Customer Name".

Here is my code:

SELECT book_customer.customerid, lastname || ', ' || firstname AS "Customer Name", city
FROM book_customer, book_order, order_items 
    WHERE book_customer.customerid = book_order.customerid
        AND book_order.orderid = order_items.orderid
            AND bookid =
            (
            SELECT MAX(retail) 
                FROM books 
                    GROUP BY bookid
            )
;

Result:

 SQL> SELECT book_customer.customerid, lastname || ', ' || firstname AS "Customer Name", city
  2   FROM book_customer, book_order, order_items 
  3    WHERE book_customer.customerid = book_order.customerid
  4     AND book_order.orderid = order_items.orderid
  5      AND bookid =
  6      (
  7      SELECT MAX(retail) 
  8       FROM books 
  9        GROUP BY bookid
 10      )
 11  ;
    SELECT MAX(retail)
    *
ERROR at line 7:
ORA-01427: single-row subquery returns more than one row

I also tried this:

SELECT customerid, lastname || ', ' || fistname AS "Customer Name", city
FROM book_customer, book_order
    WHERE book_customer.customerid = book_order.customerid
        AND orderid = 
        (
        SELECT orderid
            FROM order_items
                AND bookid =
                 (
                 SELECT bookin
                    FROM books
                        GROUP BY bookid
                            HAVING MAX(retail)
                  )
        )
;

Result:

  SQL> SELECT customerid, lastname || ', ' || fistname AS "Customer Name", city
  2   FROM book_customer, book_order
  3    WHERE book_customer.customerid = book_order.customerid
  4      AND orderid = 
  5      (
  6      SELECT orderid
  7       FROM order_items
  8         AND bookid =
  9          (
 10          SELECT bookid
 11            FROM books
 12              GROUP BY bookid
 13                HAVING MAX(retail)
 14           )
 15      )
 16  ;
       AND bookid =
       *
ERROR at line 8:
ORA-00907: missing right parenthesis

ERD

Book_Customer TABLE

CustomerID              PK
Lastname
Firstname
Address
City
State
Zip
Referred

Book_Order TABLE

OrderID               PK
CustomerID            FK
OrderDate
ShipDate
ShipStreet
ShipCity
ShipState
ShipZip

Order_Items TABLE

OrderID               PK/FK
ItemNum               PK/FK
BookID
Quantity

Books TABLE

BookID                PK
ISBN 
Title
PubDate
PubID                 FK
Cost
Retail
Category

Any help would be much appreciated.

In you second example there is a comma at lastname, || - try removing it. — Scary Wombat
– Scary Wombat, Commented Aug 19, 2013 at 8:05
The first error message is quite obvious: there're more than one column in your tables that named "customerid", so you'll have to explicitly point out which "customerid" you want. — Passerby
– Passerby, Commented Aug 19, 2013 at 8:13
Thanks, missed that. Sorry, I'm not as experienced as you, but thanks for point out the obvious. — NewtonEntropy
– NewtonEntropy, Commented Aug 19, 2013 at 8:17

CristiC · Accepted Answer · 2013-08-19 08:13:47Z

1

Try this one:

SELECT
Book_Customer.CustomerID, 
Book_Customer.lastname || ', ' || Book_Customer.firstname AS "Customer Name", 
Book_Customer.city
FROM Order_Items 
JOIN Book_Order ON Order_Items.OrderID = Book_Order.OrderID
JOIN Book_Customer ON Book_Customer.CustomerID = Book_Order.CustomerID
JOIN Books ON Books.BookID = Order_Items.BookID
WHERE Books.Retail = (SELECT MAX(Retail) FROM Books)

answered Aug 19, 2013 at 8:13

CristiC

22.7k13 gold badges62 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NewtonEntropy Over a year ago

That worked! Thanks. When the server comes back up, I'm going to try and see if I can get it to work using the joins I was trying to use for future reference. Thanks for the help!

NewtonEntropy Over a year ago

My bad about the edits, I'm a wee bit tired (I've been at this for about 14 hours now) and thought that was how you updated the progress.

Martin Milan · Accepted Answer · 2013-08-19 08:14:05Z

0

You need to break the task up...

The first step is to identify which book you're dealing with - something like this:

Select BookID from books where RowNum=1 order by retail desc

That essentially becomes your sub query, which you can then use in the Joins to gather the data you need.

I could solve the whole problem for you, but that won't teach you anything - these things are always fiddly, and it's only by fiddling that you'll learn...

answered Aug 19, 2013 at 8:14

Martin Milan

6,4002 gold badges35 silver badges44 bronze badges

2 Comments

Martin Milan Over a year ago

A lot of the other answers can return data for multiple books (in the event that more than one book shares the maximum retail price). I'm not sure, given that you've been told to use subqueries and joins, that this what your instructors will be looking for...

NewtonEntropy Over a year ago

Trust me, I have fiddled.

Harshit · Accepted Answer · 2013-08-19 08:20:21Z

For the First Error Please try below query

 SQL> SELECT bc.customerid, lastname || ', ' || firstname AS "Customer Name", city
2   FROM book_customer bc, book_order bo, order_items  oi
3    WHERE book_customer.customerid = book_order.customerid
4     AND book_order.orderid = order_items.orderid
5      AND bookid =
6       (
 7      SELECT MAX(retail) 
 8       FROM books 
 9        GROUP BY bookid
10      )

Please Provide the tablename.column name in your query because same column can exist in more than one table used in query.I cam see that CustomerId is present in more than one table. You can also use alias names.Like I have done above

For second problem I have provided the comment where there is error:-

SELECT customerid, lastname || ', ' || fistname AS "Customer Name", city
2   FROM book_customer, book_order
 3    WHERE book_customer.customerid = book_order.customerid
 4      AND orderid = 
 5      (
 6      SELECT orderid
7       FROM order_items
8         AND bookid =--no where caluse here it should be WHERE bookid=
9          (
10          SELECT bookin
 11            FROM books
 12              GROUP BY bookid
 13                HAVING MAX(retail)
   14           )
 15      )

Dba · Accepted Answer · 2013-08-19 08:21:09Z

0

Try this,

 SELECT a.customerid, a.lastname || ', ' || a.firstname AS Customer_Name, a.city
   FROM book_customer a, 
        book_order b, 
        order_items c 
  WHERE a.customerid = b.customerid
   AND b.orderid = c.orderid
   AND c.bookid =
 (
      SELECT MAX(retail) 
        FROM books 
      GROUP BY bookid
 );

answered Aug 19, 2013 at 8:21

Dba

6,6791 gold badge26 silver badges33 bronze badges

Collectives™ on Stack Overflow

Subqueries using Oracle

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Related