The relation rRM where R indicates roll number and M is the

The relation r(R,M), where R indicates roll number and M is the marks scored, is defined by the following table -

create table if not exists r(

R integer primary key,

M integer not null);

Using a scalar subquery, write an SQL query to find the R\'s and M\'s for all R\'s whose score was 15% or more above the average of the scores. The output should have 2 columns, R and M. List the results in order of scores, M, from highest to lowest.

Hint: for this average, if 10 students received a score of 90 and 1 student received a score of 50, the average score received would be 70. In other words, similar scores only contribute once to the average (90 + 50 = 140, 140/2 = 70)

Solution

Its a simple. Instead of JOINS we use scalar queries. Scalar queries are queries that only selects one column or expression and returns just one row.

Here we are first going to create table r which will contain R and S column.

Query to create table :-

                                      create table if not exists r(     

                                      R integer primary key,

                                      M integer not null);

This is DCL query (Data Creation Language). To retrieve data from database we need to use SELECT command here to select columns which we want.

Here we need two columns R and S as an a output. Query will be

OK. First understand this query. This query is divided into two parts. Here we used two scalar queries in main SELECT command. We have to check two conditions i.e. 15% of marks or more than half of sum of all marks. For that we used

then we need to order it according to M in descending manner.