SQL classroom exercise – 2
another basic class room exercise for sql!
create table grades
(
id int(5),
name varchar(32),
maths int (3),
physics int(3),
literature int(3)
);
insert into grades values (1, ‘vijay’, 68, 37, 54);
insert into grades values (2, ’subramani’, 96, 89, 92);
insert into grades values (3, ‘richard’, 65, 12, 57);
insert into grades values (4, ‘govinda’, 69, 25, 82);
——————————-
select * from grades;
/*list all the students scored over 90 in their maths paper*/
select name from grades where maths>90;
/*more than 85 in all*/
select name from grades where maths>85 and physics>85 and literature>85;
/*results*/
select *,
maths+physics+literature as total,
(maths+physics+literature)/3 as average,
if ((maths>35 and physics>35 and literature>35), ‘pass’, ‘fail’) as result
from grades;
/*average marks all students*/
select avg(physics) as p, avg(maths) as m, avg(literature)as l from grades;
/*max and min in maths*/
select max(maths), min(maths) from grades;
/*top 3 in maths*/
select * from grades order by maths desc limit 3;
