作业1

1.查询所在学院的学院名称含有“会计”的学生学号，姓名以及所在班级

SELECT studentNo,studentName,className FROM student a,class b WHERE a.classNo=b.classNo AND className LIKE institute = '%会计%';

2.查询姓名是“黄勇”的同学的学号，选修课的名称以及分数

SELECT a.studentName,a.studentNo,c.courseName,b.score FROM student a,score b,course c WHERE a.studentNo=b.studentNo AND b.courseNo=c.courseNo AND a.studentName = '黄勇';

3.查询总共有多少门不同的课程

SELECT count(courseNo) 课程数 FROM course;

4.统计每个班级的学生人数

SELECT count(studentName) FROM student GROUP BY classNo;

5.统计每个学院的学生人数

SELECT count(a.studentNo),b.institute FROM student a,class b WHERE a.classNo=b.classNo GROUP BY institute ; #第一种做法
SELECT a.institute,count(b.studentName) FROM class a LEFT OUTER JOIN
student b ON a.classNo=b.classNo GROUP BY institute; #第二种做法

6.统计每学期开设课程的门数以及上课的学生人数

SELECT c.courseName,count(a.studentNo) FROM student a,score b,course c WHERE a.studentNo=b.studentNo AND b.courseNo=c.courseNo GROUP BY b.courseNo;

7.使用外连接的方法查询选修了“数据库系统原理”同学的名字

SELECT a.courseName,c.studentName FROM course a,score b,student c WHERE
a.courseNo=b.courseNo AND b.studentNo=c.studentNo AND courseName = '数据库系统原理';