【SQL简单例子3】SQL语句

一、简单需求

1、查询sc表中成绩的最大值，最小值和总和（列要命名）
2、在sc表中查询各门课的平均值和选修人数（列要命名）
3、在student表中查询所有姓张的同学中，男生和女生的人数(列要命名) 。
4、在student表中查询学生总人数大于3个人的专业的专业名和总人数，并按照总人数降序排序 (列要命名)
5、在student表中查询姓张的同学多于2人的专业的专业名和张姓同学的人数，并按照人数升序排序(列要命名)
6、查询选修了课程的学生的姓名、性别、课程号和成绩。
7、查询成绩在60分以上的同学的学号、姓名、所选课的课程号和成绩。
8、查询每门课程的课程名、课程号、成绩以及选修这些课程的学生的学号(包含未被选择的课程)
9、查询每门课程的课程名、课程号、成绩以及选修课程的学生的学号(包含未被选择的课程和未选课的学生)
10、查询学生的学号和姓名以及他们选修的课程的课程名和成绩。(不包含未被选择的课程和未选课的学生)
11、使用无关子查询，查询与王一同性别的同学的信息
12、使用无关子查询，在选修了’c001’这门课的所有同学中，查询成绩高于该门课的平均成绩的同学的学号和成绩
13、使用无关子查询，在选修了’c001’这门课的所有同学中，查询成绩高于该门课的平均成绩的同学的姓名和成绩
14、使用无关子查询，在student表中查询年龄最大(出生日期最小)的同学的姓名、学号和出生日期
15、使用无关子查询，查询选课门数比郑丽同学的选课门数多的同学的学号和选课门数。

二、实现过程

1.select max(score) as ‘最大值’,min(score) as ‘最小值’,sum(score) as ‘总和’ from sc;

2.select cno,avg(score) as ‘平均成绩’,count(*) as ‘人数’ from sc group by cno;

3.select ssex,count(*)as '人数’from student where sname like ‘张%’ group by ssex ;

4.select specialty,count() as ‘人数’ from student group by specialty having count()>3;

5.select specialty,count() as ‘人数’ from student where sname like ‘张%’ group by specialty having count()>2;

6.select sname,ssex,cno,score from student s join sc on s.sno = sc.sno;

7.select s.sno,sname,cno,score from student s join sc on s.sno = sc.sno where score>60;

8.select cname,c.cno,score,sno from course c left join sc on c.cno = sc.cno;

9.select cname,c.cno,score,s.sno from course c full join sc on c.cno = sc.cno full join student s on s.sno = sc.sno;

10.select s.sno,sname,cname,score from student s join sc on s.sno = join course c on c.cno = sc.cno;

11.select *from student where ssex = (select ssex from student where sname = ‘王一’);

12.select sno,score from sc where cno = ‘c001’ and score>(select avg(score) from sc where cno = ‘c001’)

13.select sname,score from sc join student s on sc.sno = s.sno where cno = ‘c001’ and score>(select avg(score) from sc where cno = ‘c001’);

14.select sname,sno,birthday from student where birthday< all(select birthday from student);

15.select sno,count()from sc group by sno having count()>all(select count(*)from sc where sno in (select sno from student where sname=‘郑丽’)
);