数据库SQL查询表的建立

建学生表

Create table student

         (snochar(9) primary key,

         Snamechar(20) unique,

Ssex char(2),

Sage smallint,

Sdept char(20)

);

 

Create table course

         (cnochar(4) primary key,

         Cnamechar(40) no null,

         Cpnochar(4),

         Ccreditsmallint,

         Foreignkey (cpno) references course(cno)

         );

 

Create table sc

         (snochar(9),

         Cnochar(4),

         Gradesmallint,

         Primarykey(sno, cno),

         Foreignkey(sno) references student(sno),

         Foreignkey(cno) references course(cno)

);

 

Insert

Into student(sno,sname,ssex,sage,sdept)

Values('201215121','李勇','男',20,'CS')；

INSERT

INTO Student (Sno,Sname,Ssex,Sage,Sdept)

VALUES ('201215122','刘晨','女',19,'CS');

INSERT

INTO Student (Sno,Sname,Ssex,Sage,Sdept)

VALUES ('201215123','王敏','女',18,'MA');

INSERT

INTO Student (Sno,Sname,Ssex,Sage,Sdept)

VALUES ('201215125','张立','男',19,'IS');

 

Insert

Into course(cno,cname,cpno,ccredit)

Values(‘2’,’数学’,’null’,’2’);

Insert

Into course(cno,cname,cpno,ccredit)

Values(‘6’,’数据处理’,’null’,’2’);

Insert

Into course(cno,cname,cpno,ccredit)

Values(‘4’,’操作系统’,’6’,’3’);

insert

into course (cno,cname,cpno,ccredit)

values('7','PSCAL语言','6','4');

insert

into course (cno,cname,cpno,ccredit)

values('5','数据结构','7','4');

insert

into course (cno,cname,cpno,ccredit)

values('1','数据库','5','4');

insert

into course (cno,cname,cpno,ccredit)

values('3','信息系统','1','4');

 

insert

into sc(sno,cno,grade)

values(‘200215121’,’1’,’92’);

insert

into sc (sno,cno,grade)

values('200215121','2','85');

insert

into sc (sno,cno,grade)

values('200215121','3','88');

insert

into sc (sno,cno,grade)

values('200215122','2','90');

insert

into sc (sno,cno,grade)

values('200215122','3','80');