SQL练习与Learning 数据库脚本

<< SQL上机实验步骤及举例 | 首 页 | 如何通过用户定义函数显示逻辑类型的属性 >> 2007-11-27 SQL练习与Learning 数据库脚本 - [ 数据库 ] create database learningSys go use learningSys go create table student ( sid char ( 8 ) primary key , sname varchar ( 20 ) not null, department varchar ( 20 ) not null, gender bit , age smallint ) go create table course ( cid char ( 3 ) primary key , cname varchar ( 20 ) not null, department varchar ( 20 ) not null ) go create table learning ( sid char ( 8 ) not null, cid char ( 3 ) not null, score decimal ( 4 , 1 ) ) go -- alter table learning drop constraint PK_learning -- go ALTER TABLE learning ADD CONSTRAINT PK_learning PRIMARY KEY ( sid , cid ) go ALTER TABLE learning ADD CONSTRAINT FK_learning_course FOREIGN KEY ( cid ) REFERENCES course ( cid ), CONSTRAINT FK_learning_student FOREIGN KEY ( sid ) REFERENCES student ( sid ) go insert into student values ( '001' , ' 张三 ' , ' 计算机系 ' , 1 , 18 ) insert into student values ( '002' , ' 李四 ' , ' 计算机系 ' , 1 , 18 ) insert into student values ( '003' , ' 王五 ' , ' 计算机系 ' , 1 , 18 ) insert into student values ( '004' , ' 陈涛 ' , ' 计算机系 ' , 1 , 18 ) insert into student values ( '005' , ' 李平 ' , ' 计算机系 ' , 1 , 18 ) insert into student values ( '006' , ' 陈海峰 ' , ' 计算机系 ' , 1 , 18 ) insert into student values ( '007' , ' 刘小翔 ' , ' 计算机系 ' , 1 , 18 ) insert into student values ( '008' , ' 易建联 ' , ' 计算机系 ' , 1 , 18 ) insert into student values ( '009' , ' 王玮 ' , ' 会计系 ' , 0 , 21 ) insert into student values ( '010' , ' 张华凤 ' , ' 会计系 ' , 0 , 22 ) insert into course values ( 'c01' , 'MIS' , ' 计算机系 ' ) insert into course values ( 'c02' , 'Word' , ' 计算机系 ' ) insert into course values ( 'c03' , 'English' , ' 外语系 ' ) insert into course values ( 'c04' , 'Mathmatics' , ' 数学系 ' ) insert into course values ( 'c05' , 'MBA' , ' 工商管理系 ' ) insert into course values ( 'c06' , ' 哲学 ' , ' 社科系 ' ) insert into course values ( 'c07' , ' 会计 ' , ' 会计学 ' ) insert into learning values ( '001' , 'c02' , 88 ) insert into learning values ( '001' , 'c03' , 70.5 ) insert into learning values ( '001' , 'c04' , 77 ) insert into learning values ( '001' , 'c05' , 64 ) insert into learning values ( '001' , 'c06' , 50 ) insert into learning values ( '001' , 'c07' , 83 ) insert into learning values ( '002' , 'c01' , 100 ) insert into learning values ( '002' , 'c02' , 99.5 ) insert into learning values ( '002' , 'c03' , 80 ) insert into learning values ( '002' , 'c04' , 88 ) insert into learning values ( '003' , 'c01' , 84 ) insert into learning values ( '003' , 'c02' , 48 ) insert into learning values ( '003' , 'c03' , 55 ) insert into learning values ( '003' , 'c04' , 77 ) insert into learning values ( '004' , 'c03' , 80 ) insert into learning values ( '005' , 'c04' , 21 ) insert into learning values ( '005' , 'c05' , 84 ) insert into learning values ( '005' , 'c02' , 48 ) insert into learning values ( '002' , 'c07' , 95 ) update course set department = ' 会计系 ' where cid = 'c07' update student set department = ' 会计系 ' where sid = '006' or sid = '005' update student set gender = 0 where sid = '002' or sid = '001' -- 求计算机系的所有学生数据 select * from student where department = ' 计算机系 ' -- 求所开课程的课程号,课程名 select cid 课程号 , cname 课程名 from course -- 求计算机系年龄不到20岁的所有男生 select * from student where age < 20 and gender=1 -- 求已被学生所选课的课号 select DISTINCT cid from learning -- 求计算机系和会计系的所有女生 select * from student where department in ( ' 计算机系 ' , ' 会计系 ' ) and gender = 0 -- 求选了课程的所有学生 select * from student where sid in ( select sid from learning ) select distinct s . sid , s . sname , s . gender , s . age from student as s , learning as l where s . sid = l . sid -- 求选了课号为 “c01” ,并且成绩在分以上的所有学生学号 select sid from learning where cid = 'c01' and score > 80 -- 求同时选了课号为 “c02” 或 “c03” 的学生学号和两门课的成绩 select la . sid 学号 , la . score c02_ 成绩 , lb . score c03_ 成绩 from learning as la , learning as lb where la . sid = lb . sid and la . cid = 'c02' and lb . cid = 'c03' -- 求选了会计系所开课程的学生姓名,所在系 select s . sname , s . department from student as s inner join learning as l on ( s . sid = l . sid ) inner join course as c on ( l . cid = c . cid ) where c . department = ' 会计系 ' Tag: SQLServer DB SQL Sam @ 19:57:33 | 引用 0 | 编辑 评论 老师,数据库原理与设计对我们这些一点基础都没有的同学,真的好难。。能不能再说慢点。。或者是课后题都给我们标准答案,让我们去背诵,我觉得这样的效率可能高点。 工商管理 ( http:// 发表评论 姓名: E-mail: 地址:

原始快照:web.archive.org