sql 实验：表查询-高级

已打代码如下： 问题在后面
CREATE DATABASE SchoolInfo
Create table Department
(
DepID int primary key,
DepName varchar(20) NOT NULL
)
Create table Course
(
CourseID int primary key,
CourseName varchar(20) not null
)
Create table Student
(
StuID char(10) constraint pkStuID primary key
constraint chkStuID check(StuID like '[A,B,Z][0-9][0-9][0-9][0-9][0-9]'),
StuName varchar(20) not null,
StuAge int constraint chkStuAge check(StuAge between 15 and 100),
StuSex char(2) constraint chkStuSex check(StuSex in('男','女'))
constraint defStuSex default '男',
StuCity varchar(20) ,
Stugrade float constraint chkStugrade check(Stugrade between 0 and 100) ,
DepID int constraint fkDepID foreign key references Department(DepID),
)
Create table SC
(
StuID char(10) constraint fkStuID foreign key references Student(StuID) ,
CourseID int constraint fkCourseID foreign key references Course(CourseID),
Constraint pkStuCourse primary key(StuID,CourseID) ,
Score float constraint chkScore check(Score between 0 and 100)
)

已插入数据
问：
写出以下查询语句
1. 要显示计算机系学生的学号，姓名，系号,系名字段。
2. 显示计算机系学生的姓名，课程名及成绩。
3. 以系表为主表匹配学生表，当有学生属于相应的系时，则显示该系的系名，学生学号及学生姓名，如果某个系没有学生，则显示一条包含系名，而学生学号，学生姓名均为NULL的信息。
4. 使用子查询查找和“Tom”同城市的同学姓名。
5. 使用子查询查找选修了“数据库”课程的学生姓名。
6. 要显示院系平均入学成绩大于所有学生平均成绩的记录
7. 查询没有任何一门课程成绩在80分以下的学生的学号和姓名。
8. 查询选修“数据库原理”课程成绩最高的学生姓名
9. 将计算机系所有学生成绩置为0.
10. 查询非“计算机系”的所有年龄小于“计算机系“最小年龄的学生信息。

insert into Department values(001,'物理')
insert into Department values(002,'教育')
insert into Department values(003,'艺术')
insert into Course values(001,'高数')
insert into Course values(002,'英语')
insert into Course values(003,'语文')
insert into student values('A00112','赵',21,'男','北京',22,1)

1.select s.StuID,s.StuName ,i.DepID ,i. DepName
from Student s left join SchoolInfo i on s.DepID=i.DepID
where i.DepName='计算机系'
2.select s.StuID,s.StuName,sc.CourseName as 课程名,sc.Score as 成绩
from Student s
left join SC sc on sc.StuID =s.StuID
left join Course c on c.CourseID =sc.CourseID
where exists(select * from SchoolInfo i where s.DepID=i.DepID
and i.DepName='计算机系' )

1 嵌套: select 姓名 from student where 学号 in (select 学号 from 选课 where 课号 in (select 课号 from 课程 where 课名='数据库原理')) 连接： select 姓名 from 学生 join 选课 on 学生.学号=选课.学好 join 课程 on 选课.课号=课程.课号 where 课名='计算机原理' 2 嵌套 select 学号,年龄,性别,系名 from 学生 where 年龄 >(select max(年龄) from 学生 where 系名=计算机系) 3 连接 select 课名,成绩 from 学生 join 选课 on 学生.学号=选课.学好 join 课程 on 选课.课号=课程.课号 where 姓名='张力'

CREATE  DATABASE  SchoolInfo ; Create table Department (   DepID int primary key,   DepName varchar(20) NOT NULL ); Create table Course (   CourseID int primary key,   CourseName varchar(20) not null ); Create table Student (  StuID char(10) constraint pkStuID primary key                              constraint chkStuID check(StuID like '[A,B,Z][0-9][0-9][0-9][0-9][0-9]'),  StuName varchar(20) not null,  StuAge int constraint chkStuAge check(StuAge between 15 and 100),  StuSex char(2) constraint chkStuSex check(StuSex in('男','女'))                  constraint defStuSex default '男',  StuCity varchar(20) ,  Stugrade float constraint chkStugrade check(Stugrade between 0 and 100) ,  DepID int constraint fkDepID foreign key references Department(DepID), ); Create table SC (   StuID char(10) constraint fkStuID foreign key references Student(StuID) ,   CourseID int constraint fkCourseID foreign key references Course(CourseID),                 Constraint pkStuCourse primary key(StuID,CourseID) ,   Score float constraint chkScore check(Score between 0 and 100) ); --已插入数据 --问： --写出以下查询语句 --1.   要显示计算机系学生的学号，姓名，系号,系名字段。 SELECt SS.STUID, SS.STUNAME, DD.DEPID, DD.DEPNAME   FROM STUDENT SS, DEPARTMENT DD  WHERe SS.DEPID = DD.DEPID    AND DD.DEPNAME = '计算机系'; --2.   显示计算机系学生的姓名，课程名及成绩。 SELECt SS.STUNAME, CC.COURSENAME, SSCC.SCORE   FROM STUDENT SS, DEPARTMENT DD, SC SSCC, COURSE CC  WHERe SS.DEPID = DD.DEPID    AND SS.STUID = SSCC.STUID    AND SSCC.COURSEID = CC.COURSEID    AND DD.DEPNAME = '计算机系'; --3.   以系表为主表匹配学生表，当有学生属于相应的系时，则显示该系的系名，学生学号及学生姓名，如果某个系没有学生，则显示一条包含系名，而学生学号，学生姓名均为NULL的信息。 SELECt DD.DEPNAME, SS.STUID, SS.STUNAME   FROM DEPARTMENT DD, STUDENT SS   --左连接  WHERe DD.DEPID = SS.DEPID(+); --4.   使用子查询查找和“Tom”同城市的同学姓名。 SELECt SS.STUNAME   FROM STUDENT SS  WHERe SS.STUCITY = (SELECt STUCITY FROM STUDENT WHERe STUNAME = "Tom"); --5.   使用子查询查找选修了“数据库”课程的学生姓名。 SELECt STUNAME   FROM STUDENT  WHERe STUID IN        (SELECt STUID           FROM SC SSCC          WHERe SSCC.COURSEID =                (SELECt COURSEID FROM COURSE WHERe COURSENAME = '数据库')); --6.   要显示院系平均入学成绩大于所有学生平均成绩的记录 SELECt DD.DEPID, DD.DEPNAME   FROM DEPARTMENT DD, STUDENT ST  WHERe DD.DEPID = ST.DEPID  GROUP BY ST.DEPID HAVINg AVG(ST.STUGRADE) > (SELECt AVG(SS.STUGRADE) FROM STUDENT SS); --7.   查询没有任何一门课程成绩在80分以下的学生的学号和姓名。 SELECt SS.STUID, SS.STUNAME   FROM STUDENT SS  WHERe SS.STUID NOT IN        (SELECt SSCC.STUID FROM SC SSCC WHERe SSCC.SCORE < 80); --8.   查询选修“数据库原理”课程成绩最高的学生姓名 SELECt SS1.STUNAME   FROM STUDENT SS1, SC SSCC1, COURSE CC1  WHERe SS1.STUID = SSCC1.STUID    AND SSCC1.COURSEID = CC1.COURSEID    AND CC1.COURSENAME = '数据库原理'    AND SSCC1.SCORE = (SELECt MAX(SSCC.SCORE)                         FROM SC SSCC, COURSE CC                        WHERe SSCC.COURSEID = CC.COURSEID                          AND CC.COURSENAME = '数据库原理'                        GROUP BY SSCC.COURSEID); --9.   将计算机系所有学生成绩置为0. UPDATe SC SSCC1    SET SSCC1.SCORE = 0  WHERe SSCC1.STUID IN (SELECT SS.STUID                          FROM STUDENT SS, DEPARTMENT DD                         WHERe SS.DEPID = DD.DEPID                           AND DD.DEPNAME = '计算机系'); --10. 查询非“计算机系”的所有年龄小于“计算机系“最小年龄的学生信息。 SELECt ST.*   FROM STUDENT ST, DEPARTMENT DE  WHERe ST.DEPID = DE.DEPID    AND DD.DEPNAME <> '计算机系'    AND ST.STUID < (SELECt MIN(SS.STUAGE)                      FROM STUDENT SS, DEPARTMENT DD                     WHERe SS.DEPID = DD.DEPID                       AND DD.DEPNAME = '计算机系');看看是不是你要的 累死俺了