--create database 
use master;
if exists (select * from sysdatabases where name='student')  drop database student;
create database student;

use student;
--create table s
create table s
(
 sno char(5),
 sname char(10),
 ssex char(2),
 sbirth datetime,
 sdept char(10), 
 primary key(sno)
);

--create table c
create table c
(
 cno char(2),
 cname char(10),
 cpno char(2),
 ccredit int,
 primary key(cno)
);

--create table sc
create table sc
(
 sno char(5),
 cno char(2),
 grade int,
 primary key(sno,cno),
 foreign key(sno) references s(sno),
 foreign key(cno) references c(cno)
);

use student;
--Insert values into tablse s
insert into s values('95001', '李勇', '男', '1986/1/1','CS');
insert into s values('95002', '劉晨', '女', '1985/2/1','IS');
insert into s values('95003', '王敏', '女', '1986/10/4','MA');
insert into s values('95004', '張立', '男', '1985/6/8','IS');

--Insert values into tablse c
insert into c values('1', '數(shù)據(jù)庫(kù)', '2', 4);
insert into c values('2', '數(shù)學(xué)', '', 3);
insert into c values('3', '信息系統(tǒng)', '1', 4);
insert into c values('4', '操作系統(tǒng)', '5', 3);
insert into c values('5', '數(shù)據(jù)結(jié)構(gòu)', '6', 3);
insert into c values('6', 'C語(yǔ)言', '', 2);

--Insert values into tablse sc
insert into sc values('95001', '1', 92);
insert into sc values('95001', '2', 85);
insert into sc values('95001', '3', 87);
insert into sc values('95002', '2', 78);
insert into sc values('95002', '3', 84);