Linux练习题-SQL语句

练习题

1、导入hellodb.sql生成数据库

mysql -uroot < hellodb.sql，use hellodb

(1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄

select Name,Age from students where Age>25;

(2) 以ClassID为分组依据，显示每组的平均年龄

select ClassID,avg(Age) from students group by ClassID

(3)显示第2题中平均年龄大于30的分组及平均年龄

select ClassID,avg(Age) from students group by ClassID having avg(Age) > 30

(4) 显示以L开头的名字的同学的信息

select * from students where name like 'L%';

(5) 显示TeacherID非空的同学的相关信息

select * from students where TeacherID is not null;

(6) 以年龄排序后，显示年龄最大的前10位同学的信息

select * from students order by age desc limit 10;

(7) 查询年龄大于等于20岁，小于等于25岁的同学的信息

select * from students where age between 20 and 25;

---

2、导入hellodb.sql，以下操作在students表上执行

(1)以ClassID分组，显示每班的同学的人数

 select classID,count(*) as total_num from students group by ClassID;

(2)以Gender分组，显示其年龄之和

select gender,sum(Age) from students group by gender;

(3)以ClassID分组，显示其平均年龄大于25的班级

select ClassID,avg(Age)  from students group by ClassID having avg(Age) > 25;

(4)以Gender分组，显示各组中年龄大于25的学员的年龄之和

select gender,sum(Age) from students where age > 25 group by gender;

(5)显示前5位同学的姓名、课程及成绩

select s.stuid as stu_id,s.name as stu_name,sc.score,c.course from students as s inner join scores sc on s.stuid=sc.stuid inner join courses c on sc.courseid=c.courseid where s.stuid <= 5;

(6)显示其成绩高于80的同学的名称及课程；

select s.stuid as stu_id,s.name as stu_name,sc.score,c.course from students as s inner join scores sc on s.stuid=sc.stuid inner join courses c on sc.courseid=c.courseid where sc.score > 80;

(7)求前8位同学每位同学自己两门课的平均成绩，并按降序排列

select s.stuid,s.name,avg(sc.score) as avg_score from students as s inner join scores sc on s.stuid=sc.stuid group by stuid order by avg_score;

(8)取每位同学各门课的平均成绩，显示成绩前三名的同学的姓名和平均成绩

select s.name,avg(sc.score) from students as s inner join scores sc on s.stuid=sc.stuid group by s.name order by avg(sc.score) desc limit 3;

(9)显示每门课程课程名称及学习了这门课的同学的个数

第一步：经过筛选，决定使用courses表、coc表和students表做查询

第二步：通过联系将三个表联系在了一起，select count(s.stuid),c.course from students as s inner join coc on s.classid=coc.classid inner join courses c on coc.courseid=c.courseid;

第三步，分组，得到的这个表用哪个分组合适？毫无疑问，用course字段合适，将该字段加入分组，select count(s.stuid) as Stu_Num,c.course from students as s inner join coc on s.classid=coc.classid inner join courses c on coc.courseid=c.courseid group by c.course;

(10)显示其年龄大于平均年龄的同学的名字

    有子查询和内连接两种写法，虽然子查询方式便于理解，但由于数据库性能不高在此推荐用内连接的写法

    第一种，子查询：select name,age from students where age > (select avg(Age) from students);

    第二种，内连接写法：slect s.name,age from students as s inner join (select avg(age) as age from students) as ss on s.age > ss.age;

(11)显示其学习的课程为第1、2，4或第7门课的同学的名字

select s.name,a.courseid from students as s inner join (select * from coc where courseid in ('1','2','4','7')) as a on s.classid=a.classid;

(12)显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学

先查询成员数量最少为3个的班级，且每个班的平均年龄，select classid,count(stuid),avg(age) from students group by classid having count(stuid)>=3;

再将学生表的name、age与生成的该表做对比，对比时候用where条件判断每个学生分别对应自己的classid进行比较

(13)统计各班级中年龄大于全校同学平均年龄的同学

select stuid,name,age,classid from students where age > (select avg(age) from students);