数据库实习二--数据查询

实验内容 

1．在实验一创建的s，p，j和spj表中完成以下查询：

供应商表s（sno, sname, city）

零件表p（pno, pname, color, weight）

工程项目表j（jno, jname, city）

供应情况表 spj（sno, pno, jno, qty）

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

select pname,color
from p
where weight between 10 and 20;

（2） 查询所有零件的平均重量

select avg(weight)
from p;

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

select p.*
from spj,p
where spj.pno=p.pno and sno='s3';

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

select sno,count(distinct pno)
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)
from spj
group by jno;

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

select pname
from spj,p
where spj.pno=p.pno
group by spj.pno,pname
having sum(qty)>1000 and sum(qty)<2000;

2．在实验一创建的student，course，teacher，sc和tc五张表中完成以下查询：

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

select cname 课程名, cproperty 课程性质, credit 学分, chour 学时
from course;

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

select sno,sname,smajor,scollege
from student
where smajor like '%大数据%';

 （3) 查询“计算机科学与技术”专业学生的姓名和年龄。（年龄属性列需要计算得到）

select sname,'2024'-to_char(sbirth,'yyyy') as age
from student
where smajor = '计算机科学与技术';

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

select *
from student
where smajor = '软件工程' and to_char(sbirth,'yyyy')='1995'
order by sbirth;

（5）查询每门课程的修课人数和考试最高分，列出课程号，选修课程人数和该课程的最高成绩。

select cno,count(sno),max(grade)
from sc
group by cno;

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

select cname,chour,cterm 
from course
where cproperty='选修' and cno in 
(select cno
from sc
group by cno
having count(sno)>60);

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

select scollege,count(sno)
from student
group by scollege;

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

select sc.sno,sname,sum(credit)
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno and scollege='信息工程学院' and grade>60
group by sc.sno,sname;

 （9）查询吴春燕老师所授课程的选课和成绩信息，要求列出该老师所授课程的课程名，选课的学生姓名和课程成绩。

select cname,sname,grade
from sc,teacher,course,student,tc
where course.cno=sc.cno and student.sno=sc.sno and teacher.tno=tc.tno and course.cno=tc.cno and tname='吴春燕'
group by cname,sname,grade;

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

select  sc.sno
from student,sc,course
where student.sno= sc.sno and sc.cno = course.cno and cname='中间件技术'
intersect
select  sc.sno
from student,sc,course
where student.sno = sc.sno and sc.cno = course.cno and cname='Java EE技术';

❌
select sno
from sc,course
where sc.cno = course.cno and cname='中间件技术'
intersect
select sno
from sc,course
where sc.cno = course.cno and cname='java EE技术';

（11）查询1994年1月1日以前出生的学生的姓名和专业。

select sname,smajor
from student
where to_char(sbirth,'yyyy')<'1994';

经过以上语句查询结果共71行，再由查询语句
select sname,smajor
from student
where to_char(sbirth,'yyyy')>='1994';
可知查询结果共388行，与学生表学生总数459行相符；

 （12）查询选修了5门以上课程的学生学号和姓名。

select sc.sno,sname
from student,sc
where student.sno=sc.sno
group by sc.sno,sname
having count(cno)>5
order by sno;  //原版，为方便比照不同的查询方法的查询结果


//嵌套查询，原版
select sno,sname
from student
where student.sno in
(select sno
from  sc
group by sno
having count(*)>5)
order by sno;

select sc.sno,sname
from student,sc，course
where student.sno=sc.sno and course.cno=sc.cno and course.cproperty='选修'
group by sc.sno,sname
having count(sc.cno)>5
order by sno;   //更新后，查询结果为空


//验证
select sc.sno,count(sc.cno)
from sc,course
where sc.cno=course.cno and course.cproperty='选修'
group by sno
order by sno;

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

select sname,'2024'- to_char(sbirth,'yyyy') as age
from student s1
where '2024'- to_char(sbirth,'yyyy')<
(select avg('2024'- to_char(sbirth,'yyyy'))
from student s2
where s1.scollege=s2.scollege);

查询结果共229行信息，修改上述语句将不低于平均年龄的信息显示出来，不低于平均年龄的学生信息共230行，两者共计459行与student表中总行数相对应；

 （14）查询一门课也没有带的教师姓名。

select tname
from teacher
where tno in 
(select tno
from teacher
minus
select tno
from tc);

未代课老师共12位，通过以下语句
select count(distinct tno)
from tc;
可知授课的老师共22位，再通过
select count(distinct tno)
from teacher;
可知老师共34位,语句验证成功；

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

select *
from student
where sbirth<
(select min(sbirth)
from student
where smajor='计算机科学与技术')
order by sbirth desc;   //为方便检查查询结果


专业名为“计算机科学与技术”的学生经过语句
select min(sbirth)
from student
where smajor='计算机科学与技术';
查询后可知最大的学生为‘1992/9/15’,在上述语句中比该生还要大的学生共14行，经过语句
select *
from student
where sbirth>=
(select min(sbirth)
from student
where smajor='计算机科学与技术')
order by sbirth desc;
查询可知比该生年龄小的学生共445行，与学生总数459相对应；