3.21 数据库上机

新建教学管理表

CREATE DATABASE jxgl GO USE jxgl Go Create Table Student (Sno CHAR(5) NOT NULL PRIMARY KEY(Sno), Sname VARCHAR(20), Sage SMALLINT CHECK(Sage>=15 AND Sage<=45), Ssex CHAR(2) DEFAULT'男' CHECK(Ssex='男' OR Ssex='女'), Sdept CHAR(2));
Create Table Course (Cno CHAR(2) NOT NULL PRIMARY KEY(Cno), Cname VARCHAR(20), Cpno CHAR(2), Ccredit SMALLINT);
 create table sc (sno char(5) not null constraint s_f foreign key references student(sno), cno char(2) not null, grade smallint check((grade is null)or(grade between 0 and 100)), primary key(sno,cno), foreign key (cno) references course(cno));
 insert into student values('98001','钱横',18,'男','CS');
 insert into student values('98002','王林',19,'女','CS');
 insert into student values('98003','李民',20,'男','IS');
 insert into student values('98004','赵兰',16,'女','MA');
 insert into Course values('1','数据库系统','5',4);
 insert into Course values('2','数学分析',null,2);
 insert into Course values('3','信息系统导论','1',3);
 insert into Course values('4','操作系统_原理','6',3);
 insert into Course values('5','数据结构','7',4);
 insert into Course values('6','数据处理基础',null,4);
 insert into Course values('7','C语言','6',3);
 insert into sc values('98001','1',87);
 insert into sc values('98001','2',67);
 insert into sc values('98001','3',90);
 insert into sc values('98002','2',95);
 insert into sc values('98002','3',88);