数据库原理第二次实验

2020-11-12 本文已影响0人楚木巽

一实验目的

了解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.

分析需要用得到表格：
1. Prof's schema(pname:教师名,danme:所在系名)
2. 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.

分析需要用得到表格：
1. 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.

分析需要用到的表格：
1. Section's schema(dname开课系名，cno课程号，sectno分班号，pname任课教师名)
2. Enroll's schema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
3. 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.

分析需要用到的表格：
1. Course's schema(cno:课程号，cname:课程名，dname:开课系名)
2. Section's schema(dname:开课系名，cno:课程号，sectno:分班号，pname:任课教师名)
3. 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.

分析需要用到的表格：
1. Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
2. 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.

分析需要用到的表格：
1. Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
2. 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.)

分析需要用到的表格：
1. Course's schema(cno:课程号，cname:课程名，dname:开课系名)
2. major's schema(dname:所属系名，sid:学号)
3. Enroll's schema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
4. 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.

分析需要用到的表格：
1. Course's schema(cno:课程号，cname:课程名，dname:开课系名)\
2. 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.

分析所要用到的表格：
1. Student's schema(sid:学号，sname:姓名，sex:性别，age:年龄，year:年级，gpa:平均成绩点)
2. 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).

分析所要用到的表格：
1. Enroll's shema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
2. 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.

分析所要用到的表格：
1. Enroll's shema(sid:学号，grade:成绩，dname:开课系名，cno:课程号，sectno:分班号)
2. 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:分班号)
1. 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查询的就差不多算是全部掌握了，做完以后确实有一种恍然大悟的感觉，对表与表之间的连接、嵌套等操作也更加清晰了。

数据库原理第二次实验

一实验目的

二实验过程

创建查询

编写SQL语句

1. Print the name of professors who work in departments that have fewer than 50 phD students.

2. Print the name(s) of student(s) with the lowest gpa.

3. For each Computer Sciences class, print the cno, sectno and the average gpa of the student enrolled in the class.

4. Print the course names, course numbers and section numbers of all classes with less than six students enrolled in them.

5. Print the name(s) and sid(s) of the student(s) enrolled in the most classes.

6. Print the names of departments that have one or more majors who are under 18 years old.

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.)

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.

9. Print the names of students who are taking both a Computer Sciences course and a Mathematics course.

10. Print the age difference between the oldest and youngest Computer Sciences major(s).

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.

12. Print the ids, names, and GPAs of the students who are currently taking all of the Civil Engineering courses.

在做作业的过程中犯的一些错误及经验

猜你喜欢

热点阅读

数据库原理第二次实验

一 实验目的

二 实验过程

创建查询

编写SQL语句

1. Print the name of professors who work in departments that have fewer than 50 phD students.

2. Print the name(s) of student(s) with the lowest gpa.

3. For each Computer Sciences class, print the cno, sectno and the average gpa of the student enrolled in the class.

4. Print the course names, course numbers and section numbers of all classes with less than six students enrolled in them.

5. Print the name(s) and sid(s) of the student(s) enrolled in the most classes.

6. Print the names of departments that have one or more majors who are under 18 years old.

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.)

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.

9. Print the names of students who are taking both a Computer Sciences course and a Mathematics course.

10. Print the age difference between the oldest and youngest Computer Sciences major(s).

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.

12. Print the ids, names, and GPAs of the students who are currently taking all of the Civil Engineering courses.

在做作业的过程中犯的一些错误及经验

猜你喜欢

热点阅读

一实验目的

二实验过程