建立学生课程数据库SQL.docVIP

建立学生课程数据库SQL create database MYDB on (name = mydbdata, filename = d:\MyDB.mdf ) log on (name = mydblog, filename = d:\MyDBlog.ldf ) use mydb create table Student ( Sno char(9) primary key, Sname char(20) unique, Ssex char(2), Sage smallint, Sdept char(20) ) create table Course ( Cno char(4) primary key, Cname char(20) not null, Cpno char(4), Credit smallint, foreign key(Cpno) references Course(Cno)) create table SC ( Sno char(9), Cno char(4), Grade smallint, primary key(Sno,Cno), foreign key(Sno) references Student(Sno), foreign key(Cno) references Course(Cno)) insert into student values(1,李勇,男,20,CS) insert into student values(2,刘晨,女,19,CS) insert into student values(3,王敏,女,18,MA) insert into student values(4,张立,男,19,IS) insert into Course values(1,数据库,null,4) insert into Course values(2,数学,null,2) insert into Course values(3,信息系统,null,4) insert into Course values(4,操作系统,null,3) insert into Course values(5,数据结构,null,4) insert into Course values(6,数据处理,null,2) insert into Course values(7,C语言,null,4) update Course set cpno = 5 where cno = 1 update Course set cpno = 1 where cno = 3 update Course set cpno = 6 where cno = 4 update Course set cpno = 7 where cno = 5 update Course set cpno = 6 where cno = 7 insert into sc values(1,1,92) insert into sc values(1,2,85) insert into sc values(1,3,88) insert into sc values(2,2,90) insert into sc values(2,3,80) 1、求哪些学生还没有被分配系? 2、求CS系中所有男同学的学生姓名和年龄。 3、我校开设的课程中哪些课程名以“数据”两个字开头？ 4、求哪些学生的姓名中第2个字是“立”？ 5、求哪些学生的成绩为优秀，求出该学生的学号及相应的课程号。 6、求既不是CS系，也不是MA系的学生中年龄不小于20的学生姓名。 1、select * from student where sdept is null 2、select sname,sage from student where sdept = cs and ssex = 男 3、select cname from course where cname like 数据% 4、select * from student where sname like _立% 5、select sno,cno from sc where grade = 90 6、select sname from student where (sdept not in (cs,ma) or sdept is null) and sage = 20 或 select sname fro