数据库之数据查询

一、实验目的

1．掌握查询语句的语法格式，理解查询思想。

2．熟练掌握单表查询与集合查询。

3．重点掌握连接查询，理解嵌套查询的执行过程。

二、实验内容

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

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

select pname,color from p where weight>=10 and weight<=20; 

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

select avg(weight) from p; 

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

select p.*,s.sno from s,p where s.sno='s3';

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

select spj.sno,count(distinct spj.pno) from spj group by sno;

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

select spj.sno,count(distinct spj.pno) from spj group by sno having count(distinct spj.pno)>2;

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

select p.* from p where pname like '螺%';

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

select spj.jno,count(distinct spj.sno) from spj group by jno;

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

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

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

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

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

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

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

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

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

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

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

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

select sc.cno,count(sno),max(sc.grade) from sc group by sc.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 Student.Sno,Student.Sname,sum(Course.Credit)
from Student,Course,SC
where Student.Sno=SC.Sno and SC.Cno=Course.Cno and Scollege='信息工程学院' 
group by Student.Sno,Student.Sname;

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

select student.sname,course.cno,sc.grade 
from sc,student,course 
where sc.sno=student.sno and sc.cno=course.cno and course.cno in(
select cno from tc where tno in(
(select tno from teacher where tname = '吴春燕')));

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

select s1.sno sno1 from sc s1,sc s2 where s1.sno=s2.sno and s1.cno in
(select cno from course where cname='中间件技术' ) 
and s2.cno in (select cno from course where cname='Java EE技术');

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

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

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

select sno,sname from student where sno in(
select sno from sc group by sno having count(distinct sc.cno)>5);

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

select sname,sno from student s1 where to_number((sysdate-sbirth)/365) >
(select avg(to_number((sysdate-sbirth)/365))
from student s2
where s2.scollege = s1.scollege);

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

select tname from teacher
where not exists (select tno from tc where tno=teacher.tno);

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

select * from student where to_number((sysdate-sbirth)/365) > all
(select to_number((sysdate-sbirth)/365) from student where smajor = '计算机科学与技术');

注意：本实验是用的oracle语法。