数据库设计SQL语法练习（三）答案.doc

《数据库设计SQL语法练习（三）答案.doc》由会员分享，可在线阅读，更多相关《数据库设计SQL语法练习（三）答案.doc（10页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、SQL语法练习（三）（答案）一、设学校环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。现要建立关于系、学生、班级的数据库，关系模式为： 班CLASS：（班号classid、专业名subject、系名deptname、入学年份enrolltime、人数num） 学生STUDENT：（学号studentid、姓名name、年龄age、班号classid） 系 DEPARTMENT：（系号departmentid、系名deptname）试用SQL语言完成以下功能：1、建表，在定义中要求声明： （1）、每个表的主外码。 （2）、每个班级的人数不能超过30人。 （3）、学生的年

2、龄介于15到40岁之间。 （4）、学生姓名不能为空。- 删除表drop table student;drop table class;drop table department;- 建立系表create table department(departmentid varchar2(3) primary key,deptname varchar2(30) not null unique);- 建立班表create table class(classid number(3) primary key,subject varchar2(30),deptname varchar2(30) referen

3、ces department(deptname),enrolltime number(4),num number(2) check (num = 2;B、select deptname from department a where (select count(distinct subject) from class where deptname=a.deptname) = 2;（3）、列出人数大于等于30的系的编号和名字。A、select * from departmentwhere deptname in (select deptname from class group by deptn

4、amehaving sum(num) = 30);B、select * from department c where (select sum(a.num) from class a,department bwhere a.deptname = b.deptname and b.deptname = c.deptname) = 30;4、学校又新增加了一个物理系，编号为006。insert into department values (006,物理);5、学生张三退学，请更新相关的表。 update class set num = num -1 where classid = (select

5、 classid from student where name=张三);delete from student where name=张三;1.将建表命令改为ORACLE语句在ORACLE中建表create table student( -学生表studentid number(3) primary key, -学生编号studentname varchar2(20) -学生的姓名); create table subject( -课程表subjectid char(3) primary key, -课程编号subjectname varchar2(20) -课程的名字);create ta

6、ble grade( -分数表studentid number(3) references student(studentid), -学生idsubjectid char(3) references subject(subjectid), -课程idmark number(3), -分数primary key (studentid,subjectid) -联合主键);insert into student values (101,张三);insert into student values (102,李云);insert into student values (103,未);insert i

7、nto subject values (A01,C+);insert into subject values (A02,ASP);insert into subject values (A03,JAVA);insert into grade values (101,A01,59);insert into grade values (101,A02,72);insert into grade values (101,A03,90);insert into grade values (102,A01,75);insert into grade values (102,A02,91);insert

8、into grade values (103,A01,71); 以上的sql语句写成一个脚本文件 student.sql 运行脚本student.sql2.作如下4题第一问：查询出以下信息学号 学生姓名 课程名称 成绩 （要全部学生信息）select a.studentid as 学号,a.studentname as 学生姓名, b.subjectname as 课程名称, c.mark as 成绩 from student a,subject b,grade c where a.studentid = c.studentid and b.subjectid = c.subjectid;第二

9、问：查询出以下信息学号 学生姓名 课程名称 成绩（只显示每科最高分） select a.studentid as 学号, a.studentname as 学生姓名, b.subjectname as 课程名称, c.mark as 成绩 from student a,subject b,grade c where a.studentid = c.studentid and b.subjectid = c.subjectid and (select count(*) from grade where subjectid=b.subjectid and mark c.mark)=0; (sele

10、ct count(*) from grade where subjectid=b.subjectid and mark c.mark)=0; 同一科目中 分数比我高的人数为0 我就是本科的最高分利用分组函数写的求最高分select a.studentid 学 号,studentname 学生姓名,subjectname 课程名称,mark 成 绩from student a,subject b,grade cwhere a.studentid = c.studentidand b.subjectid = c.subjectidand (subjectname,mark)in (select s

11、ubjectname 课程名称,max(mark) 成 绩from student a,subject b,grade cwhere a.studentid = c.studentidand b.subjectid = c.subjectidgroup by subjectname)第三问：查询出以下信息学号 学生姓名 课程名称 成绩 （成绩大于60时的显示及格，小于60时的显示不及格）select a.studentid 学 号,studentname 学生姓名,subjectname 课程名称,decode(sign(mark-60),-1,不及格,及格) 成 绩from student

12、a,subject b,grade cwhere a.studentid = c.studentidand b.subjectid = c.subjectidsqlserver:select a.studentid 学 号,studentname 学生姓名,subjectname 课程名称,case when mark 1 第五问: 交叉表 (表的转置 行的信息变成列) C+ java asp 总分 张三 59 90 72 221 李云 75 0 91 166 未 71 71 select a.studentname as 学生姓名, sum(decode(b.subjectname,C+,c.mark,0) as C+, sum(decode(b.subjectname,JAVA,c.mark,0) as JAVA, sum(decode(b.subjectname,ASP,c.mark,0) as ASP, sum(c.mark) as 总分 from student a,subject b,grade c where a.studentid = c.studentid and b.subjectid = c.subjectid group by a.studentname ;