一 实验目的
- 了解SQL语言的特点和基本概念。
- 能够在Access里面熟练地进行单表查询、连接查询、嵌套查询、集合查询等各种SQL查询语句对数据库中的表进行操作。
- 对相同的查询要求，能够使用多种不同的查询方法，并对比其优劣。
二 实验过程
创建查询
- 首先点击左上角的创建
2.png
- 然后点击上方栏目中的查询设计
1.png
-
右击查询选择SQL视图
3.png -
然后就可以在这个地方写SQL语句了
4.png -
编写好SQL语句后再次右击查询，点击保存，并给查询命名
5.png -
点击确定即可出结果
6.png
编写SQL语句
1. Print the name of professors who work in departments that have fewer than 50 phD students.
- 分析需要用得到表格：
- Prof's schema(pname:教师名,danme:所在系名)
- Dept's scehma(dname:系名,numphds:该系博士生人数)
- 过程分析：首先可以从Prof表得到教授们的名字，再通过教授们名字后面的所在系名去Dept表找到对应该系的博士生人数，选出其中博士生人数少于五十人的教授即可。
- SQL语句：SELECT P.pname FROM Prof P WHERE P.dname IN (SELECT D.dname FROM Dept D WHERE D.numphds<50);
q1.png
2. Print the name(s) of student(s) with the lowest gpa.
- 分析需要用得到表格：
- Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
- 过程分析：先使用嵌套和聚合函数得到gpa的最低值，再将学生的gpa与这个最低值进行匹配，得到相应学生的名字即可。
- SQL语句：SELECT S.sname FROM Student S WHERE S.gpa IN (SELECT min(S1.gpa) FROM Student S1);
q2.png
3. For each Computer Sciences class, print the cno, sectno and the average gpa of the student enrolled in the class.
-
分析需要用到的表格：
- Section's schema(dname开课系名，cno课程号，sectno分班号，pname任课教师名)
- Enroll's schema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
- Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
过程分析：连接Enroll,Section,Student表，通过Section表得到Computer Sciences class相应的sectno,cno,再通过这两查找Enroll表得到对应学生的sid，再通过sid在Student表中找到对应学生的gpa求平均值。
sql语句：SELECT Sec.cno,Sec.sectno,ROUND(AVG(S.gpa),2) AS sgpa FROM Section Sec,Student S,Enroll E WHERE Sec.dname='Computer Sciences' AND E.cno=Sec.cno AND E.Sectno=Sec.Sectno AND E.sid=S.sid GROUP BY Sec.sectno,Sec.cno;
q3.png
4. Print the course names, course numbers and section numbers of all classes with less than six students enrolled in them.
- 分析需要用到的表格：
- Course's schema(cno:课程号，cname:课程名，dname:开课系名)
- Section's schema(dname:开课系名，cno:课程号，sectno:分班号，pname:任课教师名)
- Enroll's schema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
- 过程分析：连接Course,Student,Section表，通过Course表得到cname，cno，再通过Section表得到对应的sectno，用sectno在Eroll表中找到加入班级的学生并进行计数，找出总数小于6个学生的班级即可。
- sql语句：SELECT C.cname,C.cno,Sec.sectno FROM Course C,Enroll E,Section Sec WHERE C.dname=Sec.dname AND C.cno=Sec.cno AND Sec.sectno=E.sectno GROUP BY C.cname,C.cno,Sec.sectno HAVING COUNT()<6;*
q4.png
5. Print the name(s) and sid(s) of the student(s) enrolled in the most classes.
- 分析需要用到的表格：
- Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
- Enroll's schema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
- 过程分析：连接Student表和Enroll表，通过Enroll表得到选择课程最多的学生的sid，再通过sid在Student表中找到其name和sid,将学生选择的课程数降序排列，取第一位则是选择课程数最多的学生。
- sql语句:SELECT TOP 1 S.sname,S.sid FROM Student S,Enroll E WHERE E.sid=S.sid GROUP BY S.sid,S.sname ORDER BY COUNT() desc;*
q5.png
6. Print the names of departments that have one or more majors who are under 18 years old.
- 分析需要用到的表格：
- Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
- major's schema(dname:所属系名，sid:学号)
- 过程分析:在student表中找到年龄小于18的学生所在的院系，通过sid在major表中找到对应的department name，可以通过dname来进行分组查询，只需要相应的组中的最小年龄小于18即为满足要求。
- sql语句：SELECT M.dname FROM Major M,Student S WHERE M.sid=S.sid GROUP BY M.dname HAVING MIN(S.year)<18;
q6.png
7. Print the names and majors of students who are taking one of the College Geometry courses. (Hint: You'll need to use the "like" predicate and the string matching character in your query.)
- 分析需要用到的表格：
- Course's schema(cno:课程号，cname:课程名，dname:开课系名)
- major's schema(dname:所属系名，sid:学号)
- Enroll's schema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
- Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
- 过程分析：先通过模糊查询从Course表中得到和College Geometry courses相关的课程的cno和dname，再通过这两个属性在Enroll表中找到对应的学生的sid，再通过sid在major中找到这个学生的major，在Student表中找到这个学生的姓名。
- sql语句：SELECT S.sname,M.dname FROM Student S,Major M WHERE S.sid=M.sid AND S.sid IN (SELECT E.sid FROM Enroll E WHERE E.dname IN (SELECT C.dname FROM Course C WHERE C.cname LIKE 'College Geometry') AND E.cno IN(SELECT C.cno FROM Course C WHERE C.cname LIKE 'College Geometry'));
q7.png
8. For those departments that have no majors taking a College Geometry course, print the department name and the number of PhD students in the department.
- 分析需要用到的表格：
- Course's schema(cno:课程号，cname:课程名，dname:开课系名)\
- Dept's schema(dname:系名，numphds:该系博士生人数)
- 过程分析：通过模糊查询和NOT IN结合从Course表中得到不开设和College Geometry course相关课程的系名，再通过这个系名在Dept表中得到该系对应的博士生人数。
- sql语句：SELECT D.dname,D.numphds FROM Dept D WHERE D.dname NOT IN(SELECT C.dname FROM Course C WHERE C.cname LIKE 'College Geometry');
q8.png
9. Print the names of students who are taking both a Computer Sciences course and a Mathematics course.
- 分析所要用到的表格：
- Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
- Enroll's schema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
- 过程分析：通过Enroll表找到同时选了Computer Sciences course 和 Mathematics course的学生的sid（本来想用INTERSECT实现，但是在网上查阅资料发现Access并不支持INTERSECT），再通过sid在Student表中找到对应的sname
- sql语句：SELECT S.sname FROM Student S WHERE S.sid IN(SELECT E2.sid FROM Enroll E2 WHERE E2.dname='Computer Sciences') AND S.sid IN(SELECT E1.sid FROM Enroll E1 WHERE E1.dname='Mathematics');
q9.png
10. Print the age difference between the oldest and youngest Computer Sciences major(s).
- 分析所要用到的表格：
- Enroll's shema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
- Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
- 过程分析：通过Enroll表找到Computer Sciences major学生的sid，再通过sid在Student表中找到他们的年龄，通过聚合函数找到最大和最小的年龄，求出它们的差即可。
- sql语句：SELECT (MAX(S.age)-MIN(S.age)) AS agedifference FROM Student S WHERE S.sid IN(SELECT E.sid FROM Enroll E WHERE E.dname='Computer Sciences');
q10.png
11. For each department that has one or more majors with a GPA under 1.0, print the name of the department and the average GPA of its majors.
- 分析所要用到的表格：
- Enroll's shema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
- Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
- 过程分析：通过sid连接Student表和Enroll表，通过dname进行分组，用聚合函数求出组里最低的gpa与1.0进行比较看是否符合要求，找到所有符合要求的dname和sid找到Student表中的gpa求出平均值即可。
- sql语句：SELECT E.dname,ROUND(AVG(S.gpa),2) AS avggpa FROM Student S,Enroll E WHERE E.sid=S.sid GROUP BY E.dname HAVING MIN(S.gpa)<1.0;
q11.png
12. Print the ids, names, and GPAs of the students who are currently taking all of the Civil Engineering courses.
- 分析需要用到的表格：
-1. Enroll's shema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)- Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
- 过程分析：选了所有Civil Engineering course的学生,需要用到NOT EXISTS,使用否定之否定查询一个学生没有哪一个Civil Engineering course没有上过，即是上过所有Civil Engineering course的学生，但是后开发现ACCESS不支持EXCEPT，只能使用NOT IN来代替。
- sql语句:SELECT S.sid, S.sname, S.gpa FROM Student S WHERE NOT EXISTS (SELECT C.cno FROM Course C WHERE C.dname='Civil Engineering' AND C.cno NOT IN (SELECT E.cno FROM Enroll E WHERE E.dname='Civil Engineering' AND E.sid=S.sid));
- 一开始是这样写的，会出现语法错误：SELECT S.sid, S.sname, S.gpa FROM Student S WHERE NOT EXISTS ((SELECT C.cno FROM Course C WHERE C.dname='Civil Engineering') NOT IN (SELECT E.cno FROM Enroll E WHERE E.dname='Civil Engineering' AND E.sid=S.sid));不太明白为什么不能这样写
q12.png
在做作业的过程中犯的一些错误及经验
- WHERE后面不能跟聚合函数语句
- 嵌套的里层的语句不能使用外层声明的范围变量
- round(对应数字，保留位数)可以保留相应的小数点位数
- 除了子查询在主查询的FROM子句中使用的EXISTS保留字的情况下可以返回多个字段外，其他情况子查询的SELECT后面只能返回一个字段
5.查询某个具体字段时一定要记得加单引号！
6.Access中似乎没有INTERSECT函数，也不支持EXCEPT函数
- 一点心得体会
刚开始做的时候感觉分析起来挺难的，虽然其中一个原因是刚开始做的时候老师还没有把sql语句的使用讲完，前面几条语句写的比较慢，在分析表与表之间的关系的时候容易搞得很复杂把自己给绕进去了，不过通过自学后面的内容还是成功地提前做了几道题。等到老师讲后面一节课讲到这个内容的时候，理解老师所讲的内容也会更容易理解，学完后也更容易应用，所以后面的sql语句就写得很快。总结出了一条写sql查询语句的方法：
- 通过最终需要得到的结果作为驱动找到所有相关的表
- 从相关的表中找到对应结果的属性直接先写在第一个SELECT的后面
- 从这些需要得到的属性再去倒推表与表之间的关系，进行表与表之间的连接等操作
不得不说写语句这东西真的是熟能生巧，虽然中途也遇到了各种各样的问题，花费了很多的时间，但是确实如老师所说的，写完这12条语句对sql查询的就差不多算是全部掌握了，做完以后确实有一种恍然大悟的感觉，对表与表之间的连接、嵌套等操作也更加清晰了。
