SQL综合查询-学校教务管理系统数据库

一、一个完整的数据查询语句的格式

SELECT 【ALL|DISTINCT】<目标列表达式>【，<目标列表达式2>，...】

FROM <表名或视图名1>【，<表名或视图名2>，...】

【WHERE <元组选择条件表达式>】

【GROUP BY <属性列名>【HAVING <组选择条件表达式>】】

【ORDER BY <目标列名1>【ASC|DESC】【，<目标列名2>【ASC|DESC】,...】】

二、创建一个数据库实例-学校教务管理系统数据库

create database JWGL;

1、学生表 STUDENT（学号SNO，姓名SNAME，性别GENDER，所在班级号CNO）

create table student(
sNo char(12) primary key,
sName char(6),
gender char(2),
cNo char(3),
check(gender in('男','女'))
);

2、班级表CLASS（班级号CNO，所在院系DEPARTMENT，所属专业SPECIALITY，班长学号MONITOR）

create table class(
  cNo char(3) primary key,
  department char(18),
  speciality char(16),
  monitor char(12)
)

3、课程表LESSION（课程号LNO，课程名LNAME，教材名BOOK，学分CREDIT）

create table lession(
   lNo char(3) primary key,
   lName char(16),
   book char(16),
   CREDIT int
);

4、教师表TEACHER（教师编号TID，姓名TNAME，所在院系DEPARTMENT）

create table teacher(
  tId char(5) primary key,
  tName char(6),
  department char(16)
);

5、班级选课表ELECTION（班级号CNO，课程号LNO，教师编号TID，上课年度SYEAR，上课学期SEMESTER）

create table election(
cNo char(3),
lNo char(3),
tId char(5),
sYear int,
semester char(6),
primary key(cNo,lNo)
);

6、学生成绩表GRADE（学生学号SNO，课程号LNO，分数SCORE）

create table grade(
 sNo char(12),
 lNo char(3),
 score int,
 primary key(sNo,lNo)
);

注意：表CREDIN，SCORE，YEAR属性为INT类型，其余为CHAR类型

三、查询所有班长的学号，姓名，所在班级号和所学专业

select sNo,sName,student.cNo,speciality
from student,class
where student.sNo=class.Monitor

四、查询2024年度讲授过两门或两门以上课程的教师编号和所授的课程号

方法一：

select distinct e1.tid,e1.lNo
from election as e1,election as e2
where e1.sYear=2024 and e1.tId=e2.tId
and (e1.lNo<>e2.lNo or e1.cNo<>e2.cNo) and e2.sYear=2024

方法二：

select distinct tId,lNo
from election
where sYear=2024 and tId in(
select tId
from election
where sYear=2024
group by tId having count(*)>=2
);

五、统计“数计学院”所有教师的教师编号，教师姓名，2024年度教授的总课程数和总学分数，按总学分数从低到高排列

select teacher.tId,teacher.tName,总课程数=count(election.lNo),总学分数=sum(credit)
from teacher,election,lession
where teacher.tId=election.tId and election.lNo=lession.lNo
and sYear=2024
group by teacher.tId,teacher.tName
order by 4;

六、查询选修了“复合材料”但没有选修“计算机网络”的班级号，所属专业和该班学生人数。

select class.cNo 班级号,speciality 专业,count(sNo) 人数
from class,student
where student.cNo=class.cNo and
   class.cNo in(
   select election.cNo
   from election,lession
   where election.lNo=lession.lNo and lName='复合材料'
   )
   and class.cNo not in(
   select election.cNo
   from election,lession
   where election.lNo=lession.lNo and lName='计算机网络'
   )
group by class.cNo,speciality

七、创建一个视图v1，给出所有“数计学院”学生的学号，姓名，性别，所在班级号和选修“计算机网络”课程的分数（在此题中不需要显示属于数计学院但未选修计算机网络这门课程的学生）

方法一：

create view v1
  as select student.sNo,sName,gender,cNo,score
     from student,lession,grade
     where student.sNo=grade.sNo and grade.lNo=lession.lNo and lName='计算机网络'
	 and student.cNo in(
	 select cNo
	 from class
	 where department='数计学院'
	 )

方法二：

create view v2
 as select student.sNo,sName,gender,class.cNo,score
    from student,grade,class,lession
	where student.sNo=grade.sNo and
	      student.cNo=class.cNo and
		  department='数计学院' and
		  grade.lNo=lession.lNo and
		  lName='计算机网络'

八、查询“数计学院”学生中“计算机网络”课程分数最高的学生学号，姓名和所得分数

select sNo,sName,score
from v1
where score=(
select max(score)
from v1
)

九、查询出不学“计算机网络”课程的专业名称

select speciality
from class
except
select speciality
from class,lession,election
where election.lNo=lession.lNo and lName='计算机网络'
and election.cNo=class.cNo;

错误示例：

select speciality
from class,lession,election
where election.lNo=lession.lNo and lName<>'计算机网络'
and election.cNo=class.cNo;

错误原因：该SELECT语句将不仅仅学“计算机网络”这门课程的专业名称也进行了输出

十、小结

1、SELECT语句实现的查询功能是SQL语言的核心和重点。

2、SQL语言可满足用户对数据库的不同查询需求。

3、SQL语言是高度非过程化的语言。

4、用SQL语言表达的对数据库的操作，最终由DBMS来对语句进行查询分析并优化执行，用户只需按语句格式正确表达操作需求即可