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;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s