SQL语句练习06

目录

一、建表

二、SQL语句练习

一、建表

• 一、建立如下学生表(命名格式“姓名拼音_三位学号_week8student”，如LBJ_023_week8student）

create table LYL_116_week8student(SNO int primary key, SNAME char(8) not null unique,SEX char(2), DEPTNO int foreign key references LYL_116_week8dept(DEPTNO)) 

• 二、建立如下课程表(命名格式“姓名拼音_三位学号_week8course”，如LBJ_023_week8course）

create table LYL_116_week8course(CNO int, CNAME char(20) not null,TNO int,CREDIT int, primary key(CNO,TNO), foreign key (TNO) references LYL_116_week8teacher(TNO)) 

• 三、建立如下选课表(命名格式“姓名拼音_三位学号_week8sc”，如LBJ_023_week8sc）

create table LYL_116_week8sc(SNO int,CNO int,GRADE int, primary key(SNO,CNO), foreign key (SNO) references LYL_116_week8student(SNO), foreign key (CNO) references LYL_116_week8course(CNO)) 

• 四、建立如下教师表(命名格式“姓名拼音_三位学号_week8teacher”，如LBJ_023_week8teacher）

create table LYL_116_week8teacher(TNO int primary key, TNAME char(8) not null, DEPTNO int foreign key references LYL_116_week8dept(DEPTNO)) 

• 五、建立如下系表(命名格式“姓名拼音_三位学号_week8dept”，如LBJ_023_week8dept）

create table LYL_116_week8dept(DEPTNO int primary key, DEPTNAME char(20) not null) 

二、SQL语句练习

• 1）在Student表中加入属性SAGE(INT型)

alter table LYL_116_week8student add SAGE int

• 2）将Student表中的属性SAGE类型改为SMALLINT型

alter table LYL_116_week8student  alter column SAGE smallint

• 3）在Student表上建立关于SNO的唯一索引

alter table LYL_116_week8student add unique(SNO)

• 4）在Course表上建立关于CNO降序的唯一索引

alter table LYL_116_week8course add unique(CNO desc)

• 5）在五张表中插入如右侧的数据

insert into LYL_116_week8student values(1001,'张天','男',10,20) insert into LYL_116_week8student values(1002,'李兰','女',10,21) insert into LYL_116_week8student values(1003,'陈铭','男',10,21) insert into LYL_116_week8student values(1004,'刘茜','女',20,21) insert into LYL_116_week8student values(1005,'马朝阳','男',20,22) 

insert into LYL_116_week8course values(1,'数据结构',101,4) insert into LYL_116_week8course values(2,'数据库',102,4) insert into LYL_116_week8course values(3,'离散数学',103,4) insert into LYL_116_week8course values(4,'C语言程序设计',101,2)

insert into LYL_116_week8sc values(1001,1,80) insert into LYL_116_week8sc values(1001,2,85) insert into LYL_116_week8sc values(1001,3,78) insert into LYL_116_week8sc values(1002,1,78) insert into LYL_116_week8sc values(1002,2,82) insert into LYL_116_week8sc values(1003,1,92) insert into LYL_116_week8sc values(1004,1,87) insert into LYL_116_week8sc values(1004,4,90) insert into LYL_116_week8sc values(1005,1,85) insert into LYL_116_week8sc values(1005,4,92)

insert into LYL_116_week8teacher values(101,'张星',10) insert into LYL_116_week8teacher values(102,'李珊',10) insert into LYL_116_week8teacher values(103,'赵天应',10) insert into LYL_116_week8teacher values(104,'李田',20)

insert into LYL_116_week8dept values(10,'计算机') insert into LYL_116_week8dept values(20,'信息') 

• 6）查询全体学生的姓名和所在的系名

select s.SNAME'姓名',d.DEPTNAME'所在系名' from LYL_116_week8student s, LYL_116_week8dept d where s.DEPTNO = d.DEPTNO

• 7）查询数据结构这门课的平均成绩

select avg(sc.GRADE)'数据结构的平均成绩'  from LYL_116_week8sc sc,LYL_116_week8course c where sc.CNO = c.CNO and c.CNAME='数据结构' group by sc.CNO

• 8）为计算机系的学生记录建立一个视图CS_STUDENT

create view CS_STUDENT as select s.SNO,s.SNAME,s.SEX,s.DEPTNO,d.DEPTNAME from LYL_116_week8student s,LYL_116_week8dept d where s.DEPTNO=d.DEPTNO and d.DEPTNAME='计算机'

• 9）利用视图，列出所有计算机学生的姓名，选课名和成绩

select cs.SNAME,c.CNAME,sc.GRADE from CS_STUDENT cs,LYL_116_week8sc sc,LYL_116_week8course c where c.CNO=sc.CNO and cs.SNO = sc.SNO

• 10）查询所有女生的姓名

select s.SNAME from LYL_116_week8student s where s.SEX='女'

• 11）查询成绩在80到89之间的所有学生选课记录，查询结果按成绩的降序排列

select * from LYL_116_week8sc sc where sc.GRADE between 80 and 89 order by sc.GRADE desc

• 12）查询各个系的学生人数

select count(s.DEPTNO)'人数',d.DEPTNAME'系名' from LYL_116_week8student s,LYL_116_week8dept d where s.DEPTNO=d.DEPTNO group by s.DEPTNO,d.DEPTNAME

• 13）查询信息系年龄在21岁以下（含21岁）的女生姓名及其年龄

select s.SNAME,s.SAGE from LYL_116_week8dept d,LYL_116_week8student s where d.DEPTNAME='信息' and s.SEX='女' and s.SAGE<=21

• 14）查询选修总学分在10学分以上的学生姓名

select s.SNAME'选修总学分在10分以上' from LYL_116_week8course c,LYL_116_week8sc sc,LYL_116_week8student s where c.CNO=sc.CNO and s.SNO=sc.SNO  group by s.SNAME having sum(c.CREDIT)>10

• 15）查询各门课程取得最高成绩的课程号、学生姓名及其成绩

select m.CNO,s.SNAME,m.maxG from LYL_116_week8sc sc2,LYL_116_week8student s, (select sc1.CNO,max(sc1.GRADE)'maxG' from LYL_116_week8sc sc1 group by sc1.CNO)m where m.CNO=sc2.CNO and sc2.GRADE=m.maxG and sc2.SNO=s.SNO

• 16）查询选修了学号为1002的学生选修的全部课程的学生学号

select distinct SNO from LYL_116_week8sc sc1 where not exists(   select * from LYL_116_week8sc sc2   where sc2.SNO='1002'   and not exists(     select * from LYL_116_week8sc sc3     where sc3.SNO = sc1.SNO     and sc3.CNO = sc2.CNO))

• 17）查询选修了张星老师开设的全部课程的学生姓名

select s.SNAME from LYL_116_week8student s where not exists (   select * from LYL_116_week8course c   where c.TNO in(     select t.TNO from LYL_116_week8teacher t     where t.TNAME='张星'     and not exists(       select * from LYL_116_week8sc sc       where sc.SNO=s.SNO and sc.CNO=c.CNO)) )

• 18）查询选修张星老师数据结构课的学生的姓名和成绩

select s.SNAME,sc1.GRADE from LYL_116_week8sc sc1,LYL_116_week8student s where sc1.CNO in  (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and sc1.SNO = s.SNO

• 19）将张星老师数据结构课的学生成绩全部加2分

update LYL_116_week8sc set GRADE = GRADE+2 where CNO in (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and SNO in ( select sc1.SNO from LYL_116_week8sc sc1,LYL_116_week8student s where sc1.CNO in  (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and sc1.SNO = s.SNO)

• 20）再次查询选修张星老师数据结构课的学生的姓名和成绩

select s.SNAME,sc1.GRADE from LYL_116_week8sc sc1,LYL_116_week8student s where sc1.CNO in  (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and sc1.SNO = s.SNO

• 21）查询马朝阳同学的所有选课记录

select * from LYL_116_week8student s,LYL_116_week8sc sc where s.SNAME='马朝阳' and s.SNO=sc.SNO

• 22）删除马朝阳同学的所有选课记录

delete from LYL_116_week8sc where SNO in (select SNO from LYL_116_week8student where SNAME='马朝阳')

• 23）再次查询马朝阳同学的所有选课记录

select * from LYL_116_week8student s,LYL_116_week8sc sc where s.SNAME='马朝阳' and s.SNO=sc.SNO