设有关系S、SC、C如下:
S(sno, sname, age, sex, sdept)
C(cno, cname, teacher)
SC(sno, cno, score)
用SQL完成下列功能:
(1)创建学生表S和课程表C(要求标明主码)
(2)创建选修课程情况表SC。(要求标明主码、外码及成绩的范围)
mysql> create table s
-> (
-> sno varchar(9) primary key,
-> sname varchar(12),
-> age varchar(6),
-> sex varchar(6),
-> sdept varchar(7)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> create table c
-> (
-> cno varchar(9) primary key,
-> cname varchar(12),
-> teacher varchar(9)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> create table sc
-> (
-> sno varchar(9) not null,
-> cno varchar(9) not null,
-> score smallint,
-> check(score >= 0 and score <=100),
-> primary key (sno,cno),
-> foreign key(sno) references s(sno),
-> foreign key(cno) references c(cno)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql>
假设有下面两个关系模式:
职工(职工号,姓名,年龄,进校时间,工资,部门号),其中职工号为主码
部门(部门号,部门名称,经理名,电话),其中部门号为主码
用SQL语言定义这两个关系模式,要求在模式中完成以下完整性约束条件的定义:
(1)定义每个模式的主码
(2)定义参照完整性
(3)定义职工年龄不得超过60岁
mysql> create table sdept
-> (
-> sdeptnum smallint primary key,
-> sdeptname varchar(20),
-> manager varchar(12),
-> tel varchar(12)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> create table staff
-> (
-> staffnum smallint primary key,
-> staffname varchar(12),
-> goschooltime date,
-> wage smallint,
-> staffage smallint,
-> sdeptnum smallint,
-> check(staffage <= 60),
-> foreign key(sdeptnum) references sdept(sdeptnum)
-> );
Query OK, 0 rows affected (0.07 sec)