数据库实验答案.doc

实验二1打开数据库SQL Server 2000的查询分析器，用SQL语言建表student，表结构如下图所示：字段名类型长度含义idvarchar17学生编号namevarchar10姓名sexvarchar2性别ageinteger年龄scorenumeric6,2总成绩CREATE TABLE student( id varchar(17),name varchar(10),sex varchar(2),age integer,score numeric(6,2)2用SQL语言向表student插入如下6条记录：A0001赵一男24480.00insert into student values(A0001,赵一,男,24,480.00);.3 把所有学生年龄加一岁。update student set age=age+14 把吴六改名为吴九。update student set name=吴九 where name=吴六5 添加学生李梅，学号H0007。insert into student values(H0007, 李梅, 女, 19, 435.00);6 删除1990年后出生的人。（获取当前年的函数为YEAR（GETDATE（）delet from student where ageyear(getdate()-1990 7.把李梅的性别和年龄分别用女和19岁填充。update studentset sex=女set age=19where name= 李梅7 删除分数低于400的人。delet from student where score4008 添加学生郭八，学号I0008，性别男，年龄20，总分411。insert into student values(I0008, 郭八, 男, 20, 411.00);9 将所有学生的分数按总分为500，换算成百分制。updata student set score=score/500*10010 删除学号为“C0003”的学生。delet form student where id=C000311 将年龄为25岁的学生分数加3。updata student set score=score+3 where age=25实验三1打开数据库SQL Server 2000的查询分析器，用Create Table建表Student，表结构如下所示：字段名类型长度含义IDVarchar20学号NameVarchar10姓名AgeInt年龄DepartmentVarchar30所在系create table student(id varchar(20),name varchar(10),age int,department varchar(30)2用Create Table建表Course，表结构如下所示：字段名类型长度含义CourseIDVarchar15课程号CourseNameVarchar30课程名CourseBeforeVarchar15先行课create table course(courseid varchar(15),coursename varchar(30),coursebefore varchar(15)3用Create Table建表Choose，表结构如下所示：create table choose(id varchar(20),courseid varchar(30),score dec(5,2)4用INSERT语句向表Student中插入3条记录：IDNameAgeDepartment00001张三20计算机系 insert into student values(00001,张三,20,计算机系)insert into student values(00002,李四,19,计算机系)insert into student values(00003,王五,21,计算机系)5INSERT语句向表Course中插入3条记录：CourseIDCourseNameCourseBeforeC1计算机引论NullC2PASCAL语言C1C3数据结构C2 insert into course values(C1,计算机引论, Null ).6用INSERT语句向表Choose中插入7条记录：IDCourseIDScore00001C195insert into choose values(00001,C1,95).7用SELECT语句求计算机系学生的学号和姓名。select department AS 计算机系,id AS 学号,name AS 姓名from student8用SELECT语句求学生的学号、姓名、选的课程名及成绩。select student.id AS 学号, AS 姓名,course.coursename AS 课程,choose.score AS 成绩from student,course,choosewhere student.id=choose.id and course.courseid=choose.courseid9用SELECT语句求C1课程的成绩低于张三的学生的学号和成绩。select id AS 学号,score AS 成绩from choosewhere courseid=C1and score(80)11删除女学生视图。DROP VIEW shoolgirl实验五1、在SQL SERVER 2000任一示例数据库中建立如下三个学生选课相关的表，并输入相应的记录行。create table Student (sno varchar(20), sname varchar(30) , age int, sex varchar(2);create table Course(cno varchar(30) , cname Varchar(15), teacher varchar(30), office varchar(30);create table SC (sno varchar(20), cno varchar(30) , score dec(5,2);学生表Studentsnosnameagesex98601李强20男insert into Student values(98601,李强,20,男);课程表CoursecnocnameteacherofficeC601高等数学周振兴416insert into Course values(C601,高等数学,周振兴,416);选课表SCsnocnoscore98601C60190insert into SC values(98601,C601,90);2、用SQL完成下列查询。（1） 查询“周振兴”老师所授课程的课程号CNO和课程名CNAME。SELECT cno ,cname FROM Course where teacher=周振兴（2） 查询年龄大于21的男学生学号SNO和姓名SNAME。SELECT sno ,sname FROM Student where (sex=男 and age21)（3） 查询至少选修“刘建平”老师所授全部课程的学生姓名SNAME。SELECT SNAME FROM STUDENT WHERE SNO IN (SELECT DISTINCT S1.SNO FROM SC S1,SC S2 WHERE S1.SNO=S2.SNO AND S1.CNO!=S2.CNO AND S1.CNO IN(SELECT CNO FROM COURSE WHERE TEACHER=刘建平) AND S2.CNO IN(SELECT CNO FROM COURSE WHERE TEACHER=刘建平)（4） 查询“刘丽”同学不学课程的课程号。SELECT CNO FROM COURSE WHERE CNO NOT IN (SELECT CNO FROM SC WHERE SNO IN (SELECT SNO FROM STUDENT WHERE SNAME=刘丽)（5） 查询至少选修两门课程的学生学号。SELECT sno FROM SC group by SC.sno having count(cno)=2（6） 查询全部学生都选修的课程的课程号和课程名。SELECT COURSE.CNO,COURSE.CNAME FROM COURSE,SC WHERE COURSE.CNO=SC.CNO GROUP BY COURSE.CNO,COURSE.CNAME HAVING COUNT(SNO)=(SELECT COUNT(SNO) FROM STUDENT)（7） 查询选修课程包含“王志伟”老师所授课程的学生学号。(SELECT DISTINCT SNO FROM SC WHERE CNO IN(SELECT CNO FROM COURSE WHERE TEACHER=王志伟)（8） 查询选修课程号为C601和C603的学生学号。SELECT DISTINCT SNO FROM SC WHERE CNO=C601 OR CNO=C603（9） 查询选修全部课程的学生姓名SELECT STUDENT.SNAME FROM STUDENT,SC WHERE STUDENT.SNO=SC.SNO GROUP BY STUDENT.SNO,STUDENT.SNAME HAVING COUNT(CNO) =(SELECT COUNT(CNO) FROM COURSE);（10）查询选修课程包含学号为98603的学生所修课程的学生学号。 SELECT SNO FROM SC WHERE CNO IN (SELECT CNO FROM SC WHERE SNO=98603) AND SNO != 98603GROUP BY SNO HAVING COUNT(SNO)=(SELECT COUNT(*) FROM SC WHERE SNO=98603)（11）查询选修课程名为“操作系统”的学生学号和姓名。SELECT SNO,SNAME FROM STUDENT WHERE SNO IN(SELECT SNO FROM SC WHERE CNO IN(SELECT CNO FROM COURSE WHERE CNAME=操作系统)（12）查询各学生所有课程的平均成绩。SELECT SNAME,SCORE_AVG FROM (SELECT SNO,AVG(SCORE) SCORE_AVG FROM SC GROUP BY SNO) T INNER JOIN STUDENT ON T.SNO=STUDENT.SNO（13）查询各学生选修课程的总门数。SELECT SNAME,C_NUM FROM (SELECT SNO,COUNT(CNO) C_NUM FROM SC GROUP BY SNO) T INNER JOIN STUDENT ON T.SNO=STUDENT.SNO（14）查询每门课程的平均分。SELECT CNO,AVG(SCORE) SCORE_AVG FROM SC GROUP BY CNO（15）查询选修课程的最低成绩大于等于60 分的学生信息。SELECT * FROM STUDENTWHERE SNO IN(SELECT SNO FROM STUDENT WHERE SNO NOT IN(SELECT SNO FROM SC WHERE SCORE60);（16）查询选修课程的所有成绩均大于等于80 分的学生信息。SELECT * FROM STUDENTWHERE SNO IN(SELECT SNO FROM STUDENT WHERE SNO NOT IN(SELECT SNO FROM SC WHERE SCORE80);实验六1. 建立教学数据库teaching2. 建立教学数据库的三个基本表：S (S#, SNAME, AGE, SEX) 学生（学号，姓名，年龄，性别） SC (S#, C#, GRADE ) 选修（学号，课程号，成绩）C (C#, CNAME, TEACHER) 课程（课程号，课程名，任课教师）create table S(S# varchar(20),SNAME varchar(20),AGE int,SEX varchar(20)create table SC(S# varchar(20),C# varchar(20),GRADE dec(5,2)create table C(C# varchar(20),CNAME varchar(20),TEACHER varchar(20)4. 输入数据基本表S的数据S1 WANG 20 Minsert into S values(S1,WANG,20,M).基本表C的数据C2 MATHS MA insert into C values(C2,MATHS,MA).基本表SC的数据（空格为未修课）S# C#S1S2S3S4S5S6C1808590757090C270Null8560Nullinsert into SC values(S1,C1,80).insert into SC values(S2,C2,Null).4. 数据修改、删除1)把C2课程的非空成绩提高10。UPDATE SC SET GRADE=GRADE*1