Home » SQL & PL/SQL » SQL & PL/SQL » Three inner queries in Select Statement
Three inner queries in Select Statement [message #210467] Wed, 20 December 2006 23:34 Go to next message
nids2307
Messages: 8
Registered: December 2006
Location: India
Junior Member
The database scheme consists of four relations:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

Question:
Find the model number of the product (PC, laptop, or printer) with the highest price.
Result set: model

Please suggest the query for this.
Re: Three inner queries in Select Statement [message #210473 is a reply to message #210467] Thu, 21 December 2006 00:14 Go to previous messageGo to next message
Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune
Member
TRY THIS

SELECT MODEL
FROM PC,LAPTOP,PRINTER
WHERE PC.MODEL==LAPTOP.MODEL
AND LAPTOP.MODEL=PRINTER.MODEL
GROUP BY MODEL


BUT THERE MUST B RELATIONSHIP BETWEEN THESE TABLES
Re: Three inner queries in Select Statement [message #210475 is a reply to message #210473] Thu, 21 December 2006 00:19 Go to previous message
nids2307
Messages: 8
Registered: December 2006
Location: India
Junior Member
Hi akash,
We need to fetch the model # from product table by joining three queries in the select statement as inner query with the condition of max price.

I tried this:
SELECT model FROM product where model in
(SELECT model FROM Pc
WHERE price = (SELECT MAX(price) FROM Pc)
UNION
SELECT model FROM Laptop
WHERE price = (SELECT MAX(price) FROM Laptop)
UNION
SELECT model FROM Printer
WHERE price = (SELECT MAX(price) FROM Printer)

but how to take maximum out of the three inner queries?

[Updated on: Thu, 21 December 2006 00:19]

Report message to a moderator

Previous Topic: sqlplus problem with a procedure
Next Topic: How can I get Client IP Address?senthil
Goto Forum:
  


Current Time: Sun Dec 04 20:51:58 CST 2016

Total time taken to generate the page: 0.16000 seconds