Oracle数据库数据查询语句示例（包含大部分常用语句）

目标

• 掌握查询语句的语法格式与查询思想。
• 熟练掌握单表查询与集合查询。
• 重点掌握连接查询，理解嵌套查询的执行过程。

本文用到的关系模式

学生表Student(Sno,Sname,Ssex,Snation,Spolitical,Sbirth,Scollege,Smajor，Sclass)
课程表Course (Cno,Cname,Credit，Cproperty，Chour，Cterm)
选课表SC(Sno,Cno,Grade)
教师表Teacher（Tno，Tname，Tsex，Tbirth，Ttitle，Tcollege）
授课表TC（Id，Tno，Cno，Sclass，Semester，TimePlace）

• “学生”关系Student，由学号（Sno）、姓名（Sname）、性别（Ssex）、民族（Snation）、政治面貌（Spolitical）、出生日期（Sbirth）、学院（Scollege）、专业（Smajor）和班级（Sclass）组成。
• “课程”关系Course，由课程号（Cno）、课程名（Cname）、学分（Credit）、课程性质（Cproperty）、学时（Chour）和开设学期（Cterm）组成。
• “选课”关系SC由学号（Sno）、课程号（Cno）、成绩（Grade）组成。
• “教师”关系teacher，由职工号（Tno）、姓名（Tname），性别（Tsex），出生日期（Tbirth），职称（Ttitle）和所在学院（Tcollege）组成。
• “授课”关系TC，由课序号（Id），职工号（Tno），课程号（Cno），授课班级（Sclass），授课学期（Semester）和上课时间地点（TimePlace）组成。

供应商表s（sno, sname, city）
零件表p（pno, pname, color, weight）
工程项目表j（jno, jname, city）
供应情况表 spj（sno, pno, jno, qty）

• 供应商表 s 由供应商代码（sno）、供应商姓名（sname）、供应商所在城市（city）组成；
• 零件表 p 由零件代码（pno）、零件名（pname）、颜色（color）、重量（weight）组成；
• 工程项目表 j 由工程项目代码（jno）、工程项目名（jname）、工程项目所在城市（city）组成；
• 供应情况表 spj 由供应商代码（sno）、零件代码（pno）、工程项目代码（jno）、供应数量组成（qty），表示某供应商供应某种零件给某工程项目的数量为qty。

语句示例

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

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

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

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

select avg(weight) 
from p；

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

select * from p
where pno in
      (select pno from spj
       where sno = 's3')；

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

select sno, count(distinct pno) classes --classes是起的别名,这里是省略的写法
from spj
group by sno；

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

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

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

select * from p
where pname like '螺%';--因为编码的问题，这里可能查出来空表

新增以下环境变量可以解决这个问题。

• 变量名 NLS_LANG
• 变量值 SIMPLIFIED CHINESE_CHINA.ZHS16GBK

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

select pno, count(distinct sno) as snum --这里是起别名完整的写法
from spj
group by pno;

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

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

2．在创建的表Student、Course、SC、Teacher、TC表中完成以下查询

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

select cname, chour, cterm
from course
where cno in
      (select cno
       from sc
       group by cno having sum(sno) > 60);

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

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

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

• 包含未选课学生信息：

select student.sno, sname, sum(credit) as SumCredit
from student
left join sc on sc.sno = student.sno
left join course on course.cno = sc.cno
group by student.sno, sname;

使用了外连接，所以可以看到没有选课的学生的信息

• 不包含未选课学生信息：

select student.sno, sname, sum(credit) as SumCredit
from student, sc, course
where student.sno = sc.sno and sc.cno = course.cno
group by student.sno, sname;

没有选课的学生被忽略

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

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

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

select distinct sname
from student, sc
where student.sno = sc.sno
and sc.sno in
  (select sno from sc, course 
   where sc.cno = course.cno and cname = '中间件技术'
   and sno IN             
     (SELECT sno from sc, course
      where sc.cno = course.cno and cname = 'Java EE技术'
    )
  )

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

select sname, smajor
from student 
where sbirth <= to_date('19940101','YYYYMMDD');

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

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

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

select sname, trunc(months_between(sysdate, sbirth) / 12) as age
from student x
where to_number(to_char(sbirth,'yyyymmdd')) > 
(select avg(to_number(to_char(sbirth,'yyyymmdd')))
                from student y
                where x.scollege = y.scollege                             
               );

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

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

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

select *
from student
where sbirth < all
      (select sbirth
       from student
       where smajor= '计算机科学与技术'
      );

后记

最近刚学数据库操作，如果有错误或者哪里还有不足，希望大佬可以指出。