Week 2-3

The following queries trying to find out average marks in 2 different ways yield the same answer in every case:

(a) sum(marks)/count(*)

(b) average(marks)

• True

• False

Which of the following is a correct manner to convert an “NULLIF(a,b)” to an equivalent “IF(x,y,z)” expression?

• IF(a == null, b, a)

• Cannot convert an nullif() statement to an if() statement

• IF(a=b, NULL, a)

• IF(a is null, a, b)

Find the number of rows in the following relations for T1(ID, name) and T2(grades, name) as shown. SELECT * FROM T1 NATURAL JOIN T2;

Find the error(s) (if any) in the following query:

SELECT * FROM table1 a NATURAL JOIN table2 b

ON a.ID=b.ID;

• No errors, query is correct

• Wrong aliasing

• ON condition must be omitted

• Should be a.ID==b.ID

Choose what would be seen on running this query on MySQL Workbench:

select coalesce(1.0/0.0, NULL," ","DBS");

• NULL

• 1.0/0.0

• DBS

Find the average length of movies belonging to each rental_rate category. Output the rental_rate and the corresponding average length. Write an SQL query for the same. (table name: film)

PS: No need to alias tables/columns. Retain the underscores. All lowercase

How will you output the top 3 students according to marks?

select * from students

order by marks

​ (a)   ​ (b)   ;

• desc
• limit 3
• inc
• limit
• dec
• 3
• asc

What is returned when the following query is executed?

select COALESCE(1/0,NULL,1/0,NULL);

• Error, as there is no non-NULL value

• 1/0

• NULL

Choose the correct alternative:

If we want to return all names having 'a' in their first position and 'e' in their fifth:

select * from table

where name <?>;

• = 'a__%e%'

• = 'a___e%'

• like 'a%e_'

• like 'a___e%'