SQL REVISION TEST 1 WITH ANSWERS

I. Write outputs of the following : (26 X ½=13 M)

​

​

​

(c) SELECT SUBSTR(‘VIJAYAWADA’,-8,5);

(d) SELECT RTRIM(‘ Nidadavolu ‘);

(e) SELECT TRIM(LEADING ‘K’ FROM

‘KKKHyderaKKKbadKKKK’);

​

(f) SELECT INSTR(‘MY STRING’,’TR’) ;

(g) SELECT LENGTH(‘LieuTEnant’);

​

(h) SELECT MID(“NAGAsaki”,3,4);

​

(i) SELECT 30 MOD 11;

(j) SELECT POWER(1,10);

​

(k) SELECT ROUND(836.2345,-2);

(l) SELECT ROUND(876.2375,2);

(m) SELECT ROUND(876.7345,0);

(o) SELECT SQRT(15-29%15);

(t) SELECT DATE(‘2022-12-25 Welcome’);

(u) SELECT YEAR(‘1975-05-22’);

(v) SELECT MONTHNAME(‘1981-11-02’);

​

(w) SELECT DAY(‘2015-06-26 WBB’);

(x) SELECT DAY(‘WBB 2015-06-15’);

(y) SELECT DAYNAME(‘2022-09-25’);

(z) SELECT DAYOFWEEK(‘2022-09-23’);

​

Sunday = 1, Monday = 2, etc

2. Harshit, is learning SQL and confused with the output of following queries on table , predict the output for him: (2)

​

TABLE : LOANS

He has written the following queries:

(i) select (year(curdate)-year(StartDate))*12 as

Installments_over from loans

A) (year(curdate)-year(StartDate))*12

36

48

48

24

24

60

(ii) select CUS_NAME,monthname(Startdate) from

LOANS where 60-age<=0;

​

CUS_NAME monthname(Startdate)

ARYAN MARCH

RADHA JULY

Based on the table given above, help Harshit, write queries for the following:

​

(iii) To display the earliest loan start date.

SELECT MIN(STARTDATE) FROM LOANS;

(iv) To display the names and loan amount of those customers whose loan started

in 'March'.

SELECT CUS_NAME,LOAN_AMT FROM LOANS WHERE

MONTHNAME(STARTDATE) =’MARCH’;

3. Rishita is working on a MySQL table named

'PRODUCTS' having following structure: (2)

i) To fetch last 3 characters of pid.

SELECT SUBSTR(Pid,-3,3) FROM PRODUCTS;

ii) To display the product name in upper case, removing

all trailing and preceding spaces.

SELECT UCASE(TRIM(Pname));

4. A Coaching Institute STAR COACHING ACADEMY has maintained the

record of their students using SQL table “ENGG”. (4)

Write SQL queries for the following:

a) To display the city, sum of fees obtained from

students city-wise in decreasing order of city.

SELECT CITY,SUM(FEE) FROM ENGG GROUP BY CITY DESC;

b) To display the mode and count of students enrolled in each mode.

A) SELECT MODE,COUNT FROM ENGG GROUP BY MODE;

c) To display the sub, average fees from students subject-wise (for those subjects having more than one student)

A) SELECT SUB,AVG(FEE) FROM ENGG

GROUP BY SUB HAVING COUNT(*)>1;

​

d) To display the maximum and minimum fees for each mode of classes.

A) SELECT MODE,MAX(FEE),MIN(FEE) FROM

ENGG GROUP BY MODE;

5. Sahil has recently joined a mobile shop that maintains the database of all mobile stock in the form of relation MobileMaster:

Help him frame the SQL statements for the queries below: (2)

(a) To display the mobile company, mobile name and price in descending order of their price.

A) SELECT M_Company,M_name,M_Price FROM MobileMaster ORDER BY M_Price

DESC;

(b) To display the mobile name and quantity of all mobiles except ‘MB003’

A) SELECT M_name,QTY FROM MobileMaster WHERE M_ID <>'MB003';

SELECT M_name,QTY FROM MobileMaster WHERE M_ID !='MB003';

OR

SELECT M_name,QTY FROM MobileMaster WHERE M_ID NOT IN ('MB003');

(c) To display the mobile details manufactured after the year 2019

A) SELECT * FROM MobileMaster WHERE Year(Mfg_date)>2019;

​

(d) To display the mobile names whose quantity is not null.

A) SELECT M_name FROM MobileMaster WHERE QTY IS NOT NULL;

​

Predict the output for the following queries for Sahil: (2)

(a) Select M_Company, avg(qty) From MobileMaster Group by M_Company

​

(b) Select substr(M_Company,1,3) ,M_Price From MobileMaster Where Qty>10;

​

(c) Select M_Name ,M_Price*.01 as discount From MobileMaster Where

M_Price between 25000 and 35000;

​

(d) select count(*), count(qty), max(Mfg_date) From MobileMaster;

All the Best

​

My Dearest…….Students