Interview Question


Country: India




Comment hidden because of low score. Click to expand.
5
of 5 vote

This gets the top 5 most expensive books, except the top 5 designation here will admit ties. In other words, if the books are:

A $31.10
B $30.00
C $22.12
D $22.12
E $19.99
F $19.99
G $17.16

This query will select all books A-F because E and F are tied for 5th place. So 6 results will be returned. If, however, we now insert ('H', 22.12), the query will now return A, B, C, D, and H, since E and F will both be pushed to 6th place now.

I would note that this query is likely to be an inefficient way to achieve the result. There are much better ways to do this. In MS SQL Server for example, there's ready-made syntax to do this:

SELECT TOP 5 WITH TIES title
FROM Books
ORDER BY Price DESC

If you didn't have access to that, you a good way to do it would be to select the 5th highest price, and then query for all titles whose price is >= that price:

SELECT title
FROM Books
WHERE
Price >=
(SELECT TOP 1 Price FROM (SELECT TOP 5 Price FROM Books ORDER BY Price DESC) ORDER BY Price ASC)

As compared to the approach in this question statement, the advantage here is that the subqueries are independent of any per-row information, which will lead the SQL engine to evaluate the subquery once before beginning the main query, as opposed to evaluating it for each row.

- eugene.yarovoi January 14, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
1
of 1 vote

It is correlated subquery.. we can also use

select * from books order by price desc limit(0,5);

but if the 5th price is repeated , the query wont consider..
but this correlated subquery uses the condition : price >= 5th expensive price

but how the '5th expensive price' is calculated? it is done by making use of count(*) :)

- cobra January 15, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

how about using unique to avoid repetition ?

- karthik339 January 15, 2013 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

is it the answer?? : It selects a list of books which have no books greater than 5 in number with a greater price compared to itself.

- Manasa January 14, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

your answer is correct

- Nitin Gupta January 15, 2013 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

Yes, the answer is : it will select 5 most expensive books.
But how the query is running i m confused. Can anyone explain

- accessdenied January 15, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

It's not quite the 5 most expensive books -- it's the 5 most expensive books with ties included. See my answer.

- eugene.yarovoi January 15, 2013 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

I can explain how this sentence works. u can take b and t as tuple, in this "select count(*) from book as t where t.price>b.price" we can get how many books's price is higher than t, including itself, then we will choose the book that there are less than five books' price higher than it. This is how this sentence works. I think it is b or t confused you, you can take it as a variable, and its value comes from sql table.

- yingsun1228 January 15, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 vote

5 hightst price books title

- sonesh January 15, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

5 highest books *with ties*.

- eugene.yarovoi January 15, 2013 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

I dont think it will get you the 5 most expensive books because if you think about this again if there are say 15 books and all prices are different then (select count(*) from book as t where t.price>b.price) query is going to return 15 for most expensive query which is not less than 5. so I think this is going to return 5 lowest price books.

- Anonymous January 18, 2013 | Flag Reply
Comment hidden because of low score. Click to expand.
0
of 0 votes

The query does what I indicated in my answer above. I think you're just getting the logic a bit backwards.

You could try running the query to confirm. I've already done that.

- eugene.yarovoi January 19, 2013 | Flag
Comment hidden because of low score. Click to expand.
0
of 0 vote

hello

- penishead April 26, 2015 | Flag Reply


Add a Comment
Name:

Writing Code? Surround your code with {{{ and }}} to preserve whitespace.

Books

is a comprehensive book on getting a job at a top tech company, while focuses on dev interviews and does this for PMs.

Learn More

Videos

CareerCup's interview videos give you a real-life look at technical interviews. In these unscripted videos, watch how other candidates handle tough questions and how the interviewer thinks about their performance.

Learn More

Resume Review

Most engineers make critical mistakes on their resumes -- we can fix your resume with our custom resume review service. And, we use fellow engineers as our resume reviewers, so you can be sure that we "get" what you're saying.

Learn More

Mock Interviews

Our Mock Interviews will be conducted "in character" just like a real interview, and can focus on whatever topics you want. All our interviewers have worked for Microsoft, Google or Amazon, you know you'll get a true-to-life experience.

Learn More