mid-term-sql.txt

如何用sql建立表

创建表：
供应商表：
Create table S (
Sno char(4) primary key,
Sname char(4),
Status int,
City char(4)
)
零件表：
Create table P (
Pno    char(4) primary key,
Pname  char(8),
Color  char(4),
Weight  int
)
工程项目表：
Create table J (
Jno    char(4) primary key,
Jname  char(8),
City  char(4)
)
供应情况表：
Create table SPJ (
Sno   char(4),
Pno   char(4),
Jno   char(4),
Qty   int,
primary key (Sno,Pno,Jno),
foreign key (Sno) references S(Sno),
foreign key (Pno) references P(Pno),
foreign key (Jno) references J(Jno),
)

插入数据：
insert into S values('S1','精益',20,'天津');
insert into S values('S2','盛锡',10,'北京');
insert into S values('S3','东方红',30,'北京');
insert into S values('S4','丰泰胜',20,'天津');
insert into S values('S5','为民',30,'上海');

alter table S alter column Sname char(8)     插入出问题了，把字符数限制改成8

insert into P values('P1','螺母','红',12);
insert into P values('P2','螺栓','绿',17);
insert into P values('P3','螺丝刀','蓝',14);
insert into P values('P4','螺丝刀','红',14);
insert into P values('P5','凸轮','蓝',40);
insert into P values('P6','齿轮','红',30);

insert into J values('J1','三建','北京');
insert into J values('J2','一汽','长春');
insert into J values('J3','弹簧厂','天津');
insert into J values('J4','造船厂','天津');
insert into J values('J5','机车厂','唐山');
insert into J values('J6','无线电厂','常州');
insert into J values('J7','半导体厂','南京');

insert into SPJ values('S2','P5','J1',400);
insert into SPJ values('S2','P5','J2',100);
insert into SPJ values('S3','P3','J1',200);
insert into SPJ values('S4','P5','J1',100);
insert into SPJ values('S4','P6','J3',300);
insert into SPJ values('S4','P6','J4',200);
insert into SPJ values('S5','P2','J4',100);
insert into SPJ values('S5','P3','J1',200);
insert into SPJ values('S5','P6','J2',200);
insert into SPJ values('S5','P6','J4',500);

完成第二章习题5中的查询：
(1)求供应工程J1零件的供应商号SNO
select distinct SPJ.SNO,S.Sname 
from S,SPJ
where SPJ.Jno='J1' and SPJ.Sno=S.sno

(2)求供应工程J1零件P1的供应商号SNO
select distinct SPJ.SNO,S.Sname 
from S,SPJ
where SPJ.Jno='J1' and SPJ.Pno='P1'and SPJ.Sno=S.sno

(3)求供应工程J1零件为红色的的供应商号SNO
select SPJ.SNO,S.Sname,P.pno,P.pname,P.color 
from S,P,SPJ
where SPJ.Jno='J1' and SPJ.Pno=P.pno and P.color='红' and SPJ.Sno=S.sno

(4)求没有使用天津供应商生产的红色零件的工程号JNO
select distinct J.*,P.Pno,P.color
from J,S,P,SPJ
where SPJ.Jno=J.Jno and SPJ.Pno=P.pno and SPJ.Sno=S.sno and (P.color!='红' and S.city!='天津')

create view  tianjin as
select  S.sno,S.city,P.Pno,P.color,SPJ.Jno
from J,S,P,SPJ
where SPJ.Jno=J.Jno and SPJ.Pno=P.pno and SPJ.Sno=S.sno

/*从视图中去掉天津供应商且红色的纪录， *P.color!='红' and S.city!='天津'*/
Select distinct Jno from Tianjin where color!='红' and  city!='天津'

(5)求至少用了供应商S1所供应的全部零件的工程号JNO
select  distinct JNO
from SPJ SPJZ 
where not exists  
(select * from SPJ SPJX where Sno='S1' and not exists 
(select * from SPJ SPJY where SPJY.Pno=SPJX.Pno and SPJY.Jno=SPJZ.Jno)
)

用SQL完成以下各项操作：
(1)找出所有供应商的姓名和所在的城市
select sname,city from S

(2)找出所有零件的名称、颜色和重量
select Pname,Color,weight from P 

(3)找出使用供应商S1所供应零件的工程号码
select Jno from SPJ where Sno='S1'

(4)找出工程项目J2使用各种零件的名称及其数量
select P.Pname,SPJ.Qty from P,SPJ where P.Pno=SPJ.Pno and Jno='J2'

(5)找出上海厂商供应的所有零件号码
select distinct SPJ.Pno from S,SPJ where S.Sno=SPJ.Sno and S.City='上海'

(6)找出使用上海产的零件工程名称
select distinct SPJ.Jno,J.Jname from S,SPJ,J where SPJ.Jno=J.Jno and S.City='上海'

(7)找出没有使用天津产的零件的工程号码
select distinct Jno,J.Jname from J where not exists ( select * from SPJ where SPJ.Jno=J.Jno and Sno in
(select Sno from S where S.City='天津'));

(8)把全部红色零件的颜色改称蓝色
update P set color='蓝' where color='红'

(9)由S5供给J4的零件P6改为由S3供应，请作必要的修改
update SPJ set Sno='S3' where Sno='S5' and Jno='J4' and Pno='P6'

(10)从供应商关系中删除S2的纪录，并从供应情况关系中删除相应的纪录
delete
(11)请将（S2，J6，P4，200）插入供应情况
insert into SPJ(Sno,Jno,Pno,Qty) values ('S2','J6','P4',200)
/*insert into S Values('S2','盛锡','10','北京')   刚才删除了S2，必须插入S2遵循参照完整性*/
数据库安全性
今有二个关系模式
职工（职工号，姓名，年龄，职务，工资，部门号）
部门（部门号，名称，经理名，地址，电话）
请用SQL的grant 和 revoke语句（加上视图机制）完成以下授权定义或存取控制功能：
(a)用户王明对二个表有select权力；
grant select on  职工 to 王明;
grant select on  部门 to 王明;

revoke select on  职工 from 王明;
revoke select on  部门 from 王明;
(b)用户李勇对二个表有insert和delete权力；
grant insert,delete on  职工 to 李勇;
grant insert,delete on  部门 to 李勇;

revoke insert,delete on  职工 from 李勇;
revoke insert,delete on  部门 from 李勇;
(c)每个职工只对自己的纪录有select权力； 
Grant select on 职工 when user()=NAME to ALL
需了解SQL200的扩展语句，不支持when User（）

(d)用户刘星对职工表有select权力，对工资字段具有更新权力；
grant update(工资),select on  职工 to 刘星;

revoke update(工资),select on  职工 from 刘星;
(e)用户张新具有修改这二个表的结构的权力；---------不支持
grant alter table  to 张新  /*不能授予或废除特权 ALTER TABLE */
grant create table  to 张新  /*可以执行*/

(f)用户周平具有对两个表所有权力（读，插，改，删数据），并具有给其他用户授权的权力。
grant select,insert,update,delete on  职工 to 周平 with grant option;
grant select,insert,update,delete on  部门 to 周平 with grant option;

revoke select,insert,update,delete on  职工 from 周平 cascade;
revoke select,insert,update,delete on  部门 from 周平 cascade;
(g)用户杨兰具有从每个部门职工中select最高工资，最低工资，平均工资的权利，他不能查看每个人的工资。
Create view 部门工资 as select 职工.部门号 , max(工资) 最工资, min(工资) 最低工资,avg(工资) 平均工资 from 部门,职工 where 职工.部门号=部门.部门号
group by 职工.部门号      /*无分号*/

Grant select on 部门工资 to 杨兰;
数据库完整性
假设有下面两个关系模式
职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；
部门（部门号，名称，经理名，电话），其中部门号为主码
(1)定义每个模式的主码；
(2)定义参照完整性；
(3)定义职工年龄不得超过60岁；
先建部门，因为职工表的部门要参照部门表的部门，参照完整性
create table 部门(
部门号 char(8),
名称 char(8),
经理名  char(8),
电话 char(8),
primary key (部门号)
);

create table 职工(
职工号 char(8),
姓名 char(8),
年龄  int check(年龄<60),
职务  char(8),
工资  float,
部门号 char(8),
primary key (职工号),
foreign key (部门号) references 部门(部门号)
)