数据库设计练习[1]

1.declare x int,y int,z intset x=20set y=30set z=x+yprint z2. declare x int,y int,z intset x=40set y=30if xyset z=x-yelseset z=y-xset x=0print zprint xprint y3. declare answer char(2),x intset x=100set answer=case when x800 then Awhen x500 then Bwhen x300 then CELSE DENDPRINT ANSWER4.创建符合如下条件的数据库：数据库的名字为：学生管理数据库数据文件的逻辑文件名为：Students_dat，物理文件名 Students.mdf，存放在 D:TEST 文件夹下；文件的初始大小为：1MB；增长方式为自动增长，每次增加 1MB；日志文件的逻辑文件名为：Students_log，物理文件名 Students.ldf，也存放在 D:TEST 文件夹下；文件的初始大小为：1MB；增长方式为自动增长，每次增加 10。5本题使用 4 题中创建的“学生管理数据库” ，并在此数据库中创建三张表：学生表STUDENT、课程表 COURSE 和学生选修课表 SC，这三张表的结构如下：STUDENT 表结构列名 说明 数据类型 约束SNO 学号 CHAR（7 ） 主键SNAME 姓名 CHAR（10 ） 非空SSEX 性别 CHAR（1 ） 只能取 0 和 1，1男（默认值） 0女SAGE 年龄 TINYINTSDEPT 所在系 CHAR（20 ）COURSE 表结构列名 说明 数据类型 约束CNO 课程号 CHAR（3 ） 主键CNAME 课程名 CHAR（20 ） 非空CCREDIT 学分 TINYINTSEMSTER 学期 TINYINTSC 表结构列名 说明 数据类型 约束SNO 学号 CHAR（7 ） 主键，引用 STUDENT 的外码CNO 课程号 CHAR（3 ） 主键，引用 COURSE 的外码GRADE 成绩 TINYINT下列是建立上述三表的程序CREATE TABLE student( sno char(7) not null,sname char(10) not null,ssex char(1) default 1 constraint PK_SEX check (ssex in(1,0), sage tinyint,sdept char(20),PRIMARY KEY (SNO)GO CREATE TABLE course (cno char(3) not null,cname char(20),ccredit tinyint,semester tinyint,PRIMARY KEY (cno)GoCREATE TABLE sc (sno char(7) not null,cno char(3) not null,grade tinyint,PRIMARY KEY (SNO,CNO),FOREIGN KEY (SNO) REFERENCES student(SNO), FOREIGN KEY (CNO) REFERENCES course(cNO)GoSTUDENT 表结构列名 说明 数据类型 约束SNO 学号 CHAR（7 ） 主键SNAME 姓名 CHAR（10 ） 非空SSEX 性别 CHAR（1 ） 只能取 0 和 1，1男（默认值） 0女SAGE 年龄 TINYINTSDEPT 所在系 CHAR（20 ）SPEC 专业 CHAR(20)COURSE 表结构列名 说明 数据类型 约束CNO 课程号 CHAR（3 ） 主键CNAME 课程名 CHAR（20 ） 非空HOURS 学时 INTPROPERTY课程性质 CHAR（10 ）SC 表结构列名 说明 数据类型 约束SNO 学号 CHAR（7 ） 主键，引用 STUDENT 的外码CNO 课程号 CHAR（3 ） 主键，引用 COURSE 的外码GRADE 成绩 INT1 定义一个用户自己的数据类型：数据类型名为：SNO_TYPE，类型为：CHAR（7） 。2 在 SC 表中添加一个新的的修课类别列：列名为：TYPE ，类型为 CHAR(4)。3 将 COURSE 表的 HOURS 的类型改为 TINYINT。4 删除 COURSE 表的 PROPERTY 列。1.查询年龄在 2023 岁之间的学生的姓名、所在系和年龄。SELECT sname,sdept,sage FROM student WHERE sage=20 AND sage321、查询选修课门数等于或大于 4 门的学生的平均成绩和选课门数。SELECT SNO，平均成绩AVG(GRADE)，修课门数 COUNT（） FROM SC GROUP BY SNO HAVING COUNT(*)422、查询每个学生及其选修课的情况。SELECT FROM STUDENT INNER JOIN SC ON STUDENT.SNOSC.SNOOR SELECT STUDENT.SNO，SNAME，SSEX，SAGE，CNO，GRADE，XBLB FROM STUDENT INNER JOIN SC ON STUDENT.SNOSC.SNO23、查询计算机系学生的选修课情况，要求列出学生的名字、所修课的课程号和成绩。SELECT SNAME，CNO， GRADE FROM STUDENT INNER JOIN SC ON STUDENT.SNOSC.SNO WHERE SDEPT计算机系 24、查询所有选修了 VB 课程的学生的选修课情况，要求列出学生姓名和所在的系。SELECT SNAME，SDEPT FROM STUDENT INNER JOIN SC ON STUDENT .SNO =SC.SNO JOIN COURSE ON COURSE.CNO=SC.CNO WHERE CNAME=vb25、查询信息系选修了 VB 课程的学生的选修课成绩，要求列出学生姓名、课程名和成绩。SELECT SNAME，CNAME ，GRADE FROM STUDENT INNER JOIN SC ON STUDENT .SNO =SC.SNO JOIN COURSE ON COURSE.CNO=SC.CNO WHERE SDEPT信息系 AND CNAME=vb26、查询与刘晨在同一个系学习的学生的姓名和所在的系。SELECT S2.SNAME S2.SDEPT FROM STUDENT S1 JOIN STUDENT S2 ON S1.SDEPTS2.SDEPT WHERE S1.SNAME 刘晨 AND S2.SNAME！刘晨27、查询学生的选修课情况，包括选修了课程的学生和没有选修课的学生。SELECTSTUDENT.SNO,SNAME，CNO，GRADE FROM STUDENT LEFT OUTER JOIN SC ON STUDENT.SNOSC.SNO28、将学生的姓名、修课的课程名和成绩存放在永久表：S_C_G 中。SELECT SNAME，CNAME，GRADE INTO S_C_G FROM STUDENT S JOIN SC ONS.SNO=SC.SNO JOIN COURSE C ON C.CNO=SC.CNO29、将对计算机系学生的查询结果与信息系学生的查询结果合并为一个结果。SELECT SNO，SNAME ，SAGE，SDEPT FROM STUDENT WHERE SDEPT计算机系UNIONSELECT SNO，SNAME ，SAGE，SDEPT FROM STUDENT WHERE SDEPT信息系30、查询年龄最大的三个学生的姓名、年龄及所在的系。SELECT TOP 3 SNAME， SAGE，SDEPT FROM STUDENT ORDER BY SAGE DESCOR SELECT TOP 3 WITH TIES SNAME，SAGE ，SDEPT FROM STUDENT ORDER BY SAGE DESC31、查询 VB 课程考试前三名的学生的姓名和成绩。SELECT TOP3 WITH TIES SNAME,GRADE FROM STUDENT S JOINSCONS.SNO=SC.SNO JOIN COURSE C ONC.CNOSC.CNO WHERE CNAMEVB ORDER BY GRADE DESC32、查询选修了C02课程的且成绩高于此课程的平均成绩的学生的学号和成绩。SELECT SNO，GRADE FROM SC WHERE CNOC02 AND GRADE(SELECT AVG(GRADE) FROM SC WHERE CNOC02 )33、查询与刘晨在同一个系学习的学生。SELECT SNO,SNAME,SDEPT FROM STUDENT WHERE SDEPT IN（SELECT SDEPT FROM STUDENT WHERE SNAME刘晨 ）34、查询成绩为大于 90 分的学生的学号、姓名。SELECTSNO,SNAME FROM STUDENT WHERE SNO IN（SELECT DISTINCT SNO FROM SC WHERE GRADE90）35、查询选修了“数据库基础课程的学生的学号、成绩。SELECTSNO,SNAME FROM STUDENT WHERE SNO IN （SELECT SNO FROM SC WHERE CNO IN （SELECT CNO FROM COURSE WHERE CNAME数据库基础） ）36、将新生记录（95020，陈东，男，信息系，18 岁）插入到 STUDENT 表中。INSERT INTO STUDENT VALUES（95020， 陈东 ， 男，18， 信息系）37、求每个系的学生的平均年龄，并把结果存入数据库表中。CREATE TABLE DEPTAGE （SDEPT CHAR(20)，AVGAGE SMALLINT）INSERT INTO DEPTAGE（SDEPT,AVGAGE） SELECT SDEPT，AVG（SAGE） FROM STUDENT GROUP BY SDEPT38、将所有学生的年龄加 1。UPDATE STUDENT SET SAGESAGE+139、将“9512101”学生的年龄改为 21 岁。UPDATE STUDENT SET SAGE21 WHERE SNO951210140、将计算机系全体学生的成绩加 5 分。UPDATE SC SET GRADEGRADE5 WHERE SNO