Past Paper 1, Section B, Question 15

In the past paper, there is a question that states:

Provide the SQL to display the three most expensive products in the product table (Hint: use a subquery). The results then follow from there.

I am perplexed on how to achieve this using Oracle, as in MSSQL I would simply use:

Language: SQL
SELECT TOP 3 Product_Name, Unit_Price FROM Product ORDER BY Unit_Price DESC

I cannot see how to do this using a subquery however. Any assistance would be greatly appreciated

As far as I know this should do the trick:

SELECT * FROM
(
SELECT Product_Name, Unit_Price FROM Product ORDER BY Unit_Price DESC
)
WHERE rownum <= 3

So we're allowed to use rownum? I thought we couldn't as in the assignments I did this and was marked wrong

think it is similar to A2Q11

select employee_id, last_name, salary from employees e
where 3 > (select count (*) from employees
where e.salary < salary)
order by salary desc;

except for the products it will be:

select product_name , unit_price from products p
where 3 > (select count(*) from products where p.unit_price < unit_price)
order by unit_price desc;

I never got my assignment back, so I don't know. Although I think the rownum solution is a better solution to the problem as only one query needs to be executed, while using the solution as in the assignment the sub-query needs to be executed for every row in the main select.

But hey, that's just my opinion, I would say rather do it as in the assignment solution when you get this in the exam, rather safe than sorry.

Thanks BigSteve, and yea Louis, I'm going to do it the way they show it. No point giving a cleaner solution if you lose out on the marks. (No matter how silly that is in RL)

Hey guys , just noticed something else, in the solutions for the assignments , all the questions that involved doing joins used the older style non ANSI joins instead of the ANSI standard\SQL:1999 compliant joins.
eg:

A2Q7A

non ansi style joining in the where clause
=============================
create table EMPDATA as
select e.employee_id ID, e.first_name ||' '||e.last_name NAME, e.salary SALARY,d.department_name
from employees e, departments d
where e.department_id = d.department_id;

ansi style join unsing the join keyword
===========================

create table EMPDATA as
select e.employee_id ID, e.first_name ||' '||e.last_name NAME, e.salary SALARY,d.department_name
from employees e JOIN departments d
ON e.department_id = d.department_id;

Not sure which style we should use for the exam , the book (SQL fundamentals 1 , Lesson 5 ) does cover the ANSI standard joins ? , does it matter ? , tried to get hold of the lecturer but phone his has been busy the whole day.

They show only the JOIN command in the books, so thats where I'm sticking. I dislike the non-ansi style due to it first creating a cartesian product and then only filtering it.