mysql练习题99 

 1 一、查询每个专业的学生人数
 2 SELECT COUNT(*)
 3 FROM student
 4 GROUP BY majorid;
 5 
 6 二、查询参加考试的学生中，每个学生的平均分、最高分
 7 SELECT avg(score),MAX(score),studentno
 8 FROM result
 9 GROUP BY stduentno;
10 
11 三、查询姓张的每个学生的最低分大于60的学号、姓名
12 SELECT s.studentid,s.studentname,MIN(score)
13 FROM student s
14 JOIN result r
15 on s.studentno=r.studentno
16 where s.studentname LIKE '张%'
17 GROUP BY studentno
18 HAVING min(score)>60;
19 
20 四、查询专业生日在“1988-1-1”后的学生姓名、专业名称
21 SELECT studentname,majorname,borndate
22 FROM student s
23 join major m
24 on s.majorid=m.majorid
25 where DATEDIFF(borndate,'1988-1-1')>0;
26 
27 #五、查询每个专业的男生人数和女生人数分别是多少
28 #方式一：
29 SELECT COUNT(*) 个数,majorid,sex
30 FROM student
31 GROUP BY sex,majorid;
32 
33 方式二：
34 SELECT majorid,
35 (SELECT COUNT(*) FROM student where sex='男' and majorid=s.majorid) 男,
36 (SELECT COUNT(*) FROM student where sex='女' and majorid=s.majorid) 女
37 FROM student s
38 GROUP BY majorid 
39 
40 
41 六、查询专业和张翠山一样的学生的最低分
42 SELECT MIN(score)
43 FROM result
44 where studentno in (
45 SELECT studentno
46 FROM student
47 WHERE mojorid=(SELECT majorid
48 FROM student 
49 where studentname='张翠山'));
50 
51 七、查询大于60分的学生的姓名、密码、专业名
52 SELECT studentname,loginpwd,majorname
53 FROM student  s
54 join result r
55 on s.studentno=r.studentno
56 JOIN major m
57 on s.majorid=m.majorid
58 where r.score>60;
59 
60 八、按邮箱位数分组，查询每组的学生个数
61 SELECT COUNT(*)
62 FROM student
63 GROUP BY LENGTH(email);
64 
65 九、查询学生名、专业名、分数
66 SELECT studentname,majorname,score
67 FROM student s
68 JOIN major m
69 on s.majorid=m.majorid
70 JOIN s.studentno=r.studentno;
71 
72 十、查询哪个专业没有学生，分别用左连接和右连接实现
73 SELECT majorid,majorname
74 from major m
75 LEFT JOIN student s
76 on m.majorid=s.majorid
77 where s.studentno is null
78 
79 十一、查询没有成绩的学生人数
80 SELECT COUNT(*)
81 FROM student s
82 LEFT JOIN result r on s.studentno=r.studentno
83 where r.id is null

 

posted @ 2020-07-12 10:38  自律即自由-  阅读(406)  评论(0)  编辑  收藏  举报