SQL语句练习06
目录
一、建表
- 一、建立如下学生表(命名格式“姓名拼音_三位学号_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
相关文章
- SQL开发知识:关于表oracle的一些特殊查询sql语句
- SQL设置SQL Server最大连接数及查询语句
- Oracle 利用SQL语句修改分区表(oracle修改分区表)
- 语句执行记录MySQL 查看SQL语句执行记录(mysql查看sql)
- 「MySQL 教程」学习如何拼接 SQL 语句,让你的操作更灵活高效(mysql拼接sql语句)
- Linux环境下执行SQL语句的步骤与方法(linux下执行sql)
- sql探索SQL Server:前六条SQL语句(sqlserver前六条)
- 25字中文文章标题:Oracle跟踪SQL工具使用详解(oracle跟踪sql工具)
- 掌握Oracle中的SQL语句奥妙(oracle中sql语句)
- MySQL的PL/SQL:把数据库中的业务流程变得更简单!(mysql的pl sql)
- MySQL SQL日志技术指南(mysql sql 日志)
- MySQL面试题大全精选SQL技巧考察,必备职场技能(mysql中sql面试题)
- MySQL 中 SQL 比较详解常用语句及其实现方法(mysql中sql比较)
- 如何用Oracle实现行转列的SQL语句(oracle中行转列语法)
- 掌握Oracle数据库操作使用SQL语句(oracle使用语句)
- 的sql语句精益求精Oracle打造优雅的SQL语句(oracle书写好看)
- 语句妙用Oracle将SQL语句拼接妙趣横生(oracle中拼接sql)
- 使用Oracle SQL解答聪明人的问题(oracle sql题)
- sql查询本年、本月、本日记录的语句,附SQL日期函数
- T-SQL查询语句的执行顺序解析
- SQL语句练习实例之五WMS系统中的关于LIFO或FIFO的问题分析
- sqlserver中去除字符串中连续的分隔符的sql语句
- oracle中得到一条SQL语句的执行时间的两种方式