实验三 数据查询

一、实验目的
1．掌握查询语句的语法格式，理解查询思想。
2．熟练掌握单表查询与集合查询。
3．重点掌握连接查询，理解嵌套查询的执行过程。
二、实验内容
1．在project数据库的s，p，j和spj表中完成以下查询：

1. 查询零件重量在10-20之间（包括10和20）的零件名和颜色。

SELECT pname, color
FROM p
WHERE weight BETWEEN 10 AND 20;

2. 查询所有零件的平均重量。

SELECT AVG(weight) AS average_weight
FROM p;

3. 查询供应商s3供应的零件信息。

SELECT p.pno, p.pname, p.color, p.weight
FROM p
JOIN spj ON p.pno = spj.pno
WHERE spj.sno = 's3';

4. 查询各个供应商号及其供应了多少类零件。

SELECT sno, COUNT(DISTINCT pno) AS parts_count
FROM spj
GROUP BY sno;

5. 查询供应了2类以上零件的供应商号。

SELECT sno
FROM spj
GROUP BY sno
HAVING COUNT(DISTINCT pno) > 2;

6. 查询零件名以“螺”字开头的零件信息。

SELECT *
FROM p
WHERE pname LIKE '螺%';

7. 查询给每个工程供应零件的供应商的个数。

SELECT jno, COUNT(DISTINCT sno) AS count
FROM spj
GROUP BY jno;

8. 查询供应总量在1000—2000之间（包括1000和2000）的零件名称。

SELECT p.pname
FROM p
JOIN spj ON p.pno = spj.pno
GROUP BY p.pno
HAVING SUM(qty) BETWEEN 1000 AND 2000;

2．在aam数据库的student，course，teacher，sc和tc五张表中完成以下查询：

1. 查询所有课程的课程名，课程性质，学分和学时属性列，要求在查询结果中用中文重新命名属性列的列名。

SELECT Cno AS 课程号, Cname AS 课程名, Cproperty AS 课程性质, Credit AS 学分, Chour AS 学时
FROM Course;

2. 查询学生所在专业的专业名包含“大数据”三个字的所有学生学号，姓名、专业名和所在学院。

SELECT S.Sno, S.Sname, S.Smajor, S.Scollege
FROM Student S
WHERE S.Smajor LIKE '%大数据%';

3. 查询“计算机科学与技术”专业学生的姓名和年龄。

SELECT S.Sname, TIMESTAMPDIFF(YEAR, S.Sbirth, CURDATE()) AS age
FROM Student S
WHERE S.Smajor = '计算机科学与技术';

4. 查询“软件工程”专业1995年出生的学生信息，查询结果按学生出生的时间先后排序。

SELECT *
FROM Student S
WHERE S.Smajor = '软件工程' AND Sbirth LIKE "1995%"
ORDER BY Sbirth;```

5. 查询每门课程的修课人数和考试最高分，列出课程号，选修课程人数和该课程的最高成绩。
```sql
SELECT C.Cno, COUNT(DISTINCT SC.Sno) AS students_count, MAX(SC.Grade) AS highest_grade
FROM Course C
JOIN SC ON C.Cno = SC.Cno
GROUP BY C.Cno;

6. 查询课程性质是选修，并且选修人数在60人以上的课程名、课程学时和开设学期。

SELECT C.Cname, C.Chour, C.Cterm
FROM Course C
JOIN SC ON C.Cno = SC.Cno
GROUP BY C.Cno
HAVING COUNT(DISTINCT SC.Sno) > 60 AND C.Cproperty = '选修';

7. 统计每个学院的学生人数。

SELECT S.Scollege, COUNT(*) AS student_count
FROM Student S
GROUP BY S.Scollege;

8. 查询信息工程学院所有学生已修课程的总学分，要求列出学号、姓名和总学分。

SELECT S.Sno, S.Sname, SUM(C.Credit) AS total_credits
FROM Student S
JOIN SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE S.Scollege = '信息工程学院'
GROUP BY S.Sno, S.Sname;

9. 查询同时选修了“中间件技术”和“Java EE技术”两门选修课的学生学号。

SELECT DISTINCT SC.Sno AS 学号
FROM SC
JOIN Course ON SC.Cno = Course.Cno
WHERE Course.Cname = '中间件技术' 
  AND SC.Sno IN (SELECT Sno FROM SC JOIN Course ON SC.Cno = Course.Cno WHERE Course.Cname = 'Java EE技术');```

10. 查询1994年1月1日以前出生的学生的姓名和专业。
```sql
SELECT S.Sname, S.Smajor
FROM Student S
WHERE S.Sbirth < '1994-01-01';

11. 查询总共修了20门以上课程（包括必修课和选修课）的学生学号和姓名。

SELECT S.Sno, S.Sname
FROM Student S
JOIN SC ON S.Sno = SC.Sno
GROUP BY S.Sno, S.Sname
HAVING COUNT(DISTINCT SC.Cno) > 20;

12. 查询比本院学生平均年龄小的学生信息，要求列出姓名与年龄。

SELECT S.Sname, TIMESTAMPDIFF(YEAR, S.Sbirth, CURDATE()) AS age
FROM Student S
WHERE TIMESTAMPDIFF(YEAR, S.Sbirth, CURDATE()) < (SELECT AVG(TIMESTAMPDIFF(YEAR, S2.Sbirth, CURDATE())) FROM Student S2);

13. 查询一门课也没有带的教师姓名。

SELECT T.Tname
FROM Teacher T
WHERE T.Tno NOT IN (SELECT DISTINCT TC.Tno FROM TC);

14. 查询比所有“计算机科学与技术”专业学生年龄都大的学生。

SELECT S.Sname, TIMESTAMPDIFF(YEAR, S.Sbirth, CURDATE()) AS age
FROM Student S
WHERE age > ALL (SELECT TIMESTAMPDIFF(YEAR, S2.Sbirth, CURDATE()) FROM Student S2 WHERE S2.Smajor = '计算机科学与技术');