Sql 练习题 (一)

//创建数据库
CREATE database students;

//使用数据库
use students

//创建表
CREATE table Student 
(
   Sno char(7) primary key,
   Sname char(10) not null,
   Ssex char(2) check(Ssex in('男','女')),
   Sage int ,
   Dept char(20),
 );
  
  
 CREATE table Course 
 (
    Cno char(10) primary key,
    Cname char(20) unique,
    Credit int check(credit>0),
    Semester int 
  );
 
 
 CREATE table SC 
 (
    Sno char(7) ,
    Cno char(10) ,
    Grade int null,
    primary key(Sno,Cno),
    foreign key(Sno) references Student(Sno),
    foreign key(Cno) references Course(Cno)
)

//插入数据
INSERT INTO Student (Sno,Sname,Ssex,Sage,Dept) values
('0811101','李勇','男',21,'计算机系'),
('0811102','刘晨','男',20,'计算机系') ,
('0811103','王敏','女',20,'计算机系') ,
('0811104','张小红','女',19,'计算机系' ),
('0821101','张立','男',20,'信息管理系') ,
('0821102','吴宾','女',19,'信息管理系') ,
('0821103','张海','男',20,'信息管理系' ),
('0831101','钱小平','女',21,'通信工程系' ),
('0831102','王大力','男',20	,'通信工程系' ),
('0831103','张姗姗','女',19	,'通信工程系'); 



INSERT INTO Course(Cno,Cname,Credit,Semester) values
('C001','高等数学',	4 ,	1),
('C002'	,'大学英语-1',	3	,1),
('C003'	,'大学英语-2',	3	,2),
('C004'	,'计算机文化学',	2	,2),
('C005'	,'Java',	2,	3),
('C006','数据库基础'	,4	,5),
('C007'	,'数据结构',	4	,4),
('C008','计算机网络',	4	,4);

INSERT INTO SC(Sno,Cno,Grade) values
('0811101',	'C001',	96),
('0811101',	'C002',	80),
('0811101',	'C003',	84),
('0811101',	'C005',	62),
('0811102',	'C001',	92),
('0811102',	'C002',	90),
('0811102',	'C004',	84),
('0821102',	'C001',	76),
('0821102',	'C004',	85),
('0821102',	'C005',	73),
('0821102',	'C007',	Null),
('0811103',	'C001',	50),
('0811103',	'C004',	80),
('0831101',	'C001',	50),
('0831101',	'C004',	80),
('0831102',	'C007',	Null),
('0831103',	'C004',	78),
('0831103',	'C005',	65);