Q

1: Which query produces the following output from table marks

table name : marks rnk 1 2 3 4

Output rnk 1 3 6 10

1. select rnk from (select b.rnk as alpha,sum(a.rnk) as rnk from (select * from marks) a join (select * from marks) b on a.rnk <= b.rnk group by 1 )

2. select rnk from (select b.rnk as alpha,sum(a.rnk) as rnk from (select * from marks) a join (select * from marks) b on a.rnk > b.rnk group by 1 )

3. select rnk from (select b.rnk as alpha,sum(a.rnk) as rnk from (select * from marks) a join (select * from marks) b on a.rnk = b.rnk group by 1 )

4. select rnk from (select b.rnk as alpha,avg(a.rnk) as rnk from (select * from marks) a join (select * from marks) b on a.rnk <= b.rnk group by 1 )
   
   

This was a question asked in an interview. And I didn't even new the topic related to this. I failed the test but I really want to know which topics should I cover so I can be more prepared for future. The answer is first I guess but I don't understand what's going on in this query. Sorry for the bad title but I was unable to even express my thoughts Thanks in advance and sorry for my bad english.

CodePudding user response：

Which query produces the following output from table marks

Correct answer - none.

1. The subquery must be assigned with an alias. There is no outer subquery alias in each of these queries - i.e. all 4 queries will produce syntax error like 'Every derived table must have its own alias'.

2. If you fix these errors then there is another problem - the queries does not contain ORDER BY clause. So the output rows ordering is not defined (is not deterministic), and even when the query produces needed rows then the ordering of these rows may not match to shown one.

3. If you fix this problem then the query #1 will produce desired output.

CodePudding user response：

The answer is #1.
(We had to add an alias for the sub-query: Z)
We use 2 aliases for the same table and join them so that each row in a is joined to all rows less than itself in b.
We then return a.rnk which is like an id and the sum of b.rnk which is therefore a running total.
Akina is right that there is no order by in the query so there is no guarantee that the order will be the same. (The question was not "how can we garantie this result" but, which query could produce this output")
As you had a problem with this question I suggest that you need to find an SQL tutorial and start from the basics. There are a number of good tutorials out there.

create table marks (rnk int);
insert into marks values (1),(2),(3),(4);

✓

✓

select rnk from 
 ( select 
     b.rnk as alpha,
     sum(a.rnk) as rnk 
   from 
   (select * from marks) a 
   join (select * from marks) b
   on a.rnk <= b.rnk group by 1 
 )z;

| rnk |
| --: |
|   1 |
|   3 |
|   6 |
|  10 |

db<>fiddle here