数据库系统概论 第三章 课后作业 

  1 ----第3大题
  2 
  3 --建立库
  4 create database SPJbase
  5 use SPJbase
  6 
  7 
  8 --建立表
  9 create table s                 --建立s表
 10 (
 11 sno varchar(5) primary key,
 12 sname varchar(10) not null,
 13 status int not null,
 14 city varchar(10) not null
 15 )
 16 create table p                 --建立p表
 17 (
 18 pno varchar(5) primary key,
 19 pname varchar(10) not null,
 20 color varchar(50) not null,
 21 weight int not null
 22 )
 23 create table j                 --建立j表
 24 (
 25 jno varchar(5) primary key,
 26 jname varchar(10) not null,
 27 city varchar(10) not null
 28 )
 29 create table spj               --建立spj表
 30 (
 31 sno varchar(5),
 32 pno varchar(10) not null,
 33 jno varchar(10) not null,
 34 qty int  not null 
 35 )
 36 
 37 
 38 
 39 --插入数据
 40 
 41 insert into s values('s1','精益',20,'天津')   --插入S表
 42 insert into s values('s2','盛锡',10,'北京')
 43 insert into s values('s3','东方红',30,'北京')
 44 insert into s values('s4','丰泰盛',20,'天津')
 45 insert into s values('s5','为民',30,'上海')
 46 select * from s
 47 
 48 insert into p values('p1','螺母','红',12)     --插入P表
 49 insert into p values('p2','螺栓','绿',17)
 50 insert into p values('p3','螺丝刀','蓝',14)
 51 insert into p values('p4','螺丝刀','红',14)
 52 insert into p values('p5','凸轮','蓝',40)
 53 insert into p values('p6','齿轮','红',30)
 54 select * from p
 55 
 56 insert into j values('j1','三建','北京')      --插入J表
 57 insert into j values('j2','一汽','长春')
 58 insert into j values('j3','弹簧厂','天津')
 59 insert into j values('j4','造船厂','天津')
 60 insert into j values('j5','机车厂','唐山')
 61 insert into j values('j6','无线电厂','常州')
 62 insert into j values('j7','半导体厂','南京')
 63 select * from j
 64 
 65 insert into spj values('s1','p1','j1',200)   --插入spj表
 66 insert into spj values('s1','p1','j3',100)
 67 insert into spj values('s1','p1','j4',700)
 68 insert into spj values('s1','p2','j2',100)
 69 insert into spj values('s2','p3','j1',400)
 70 insert into spj values('s2','p3','j2',200)
 71 insert into spj values('s2','p3','j4',500)
 72 insert into spj values('s2','p3','j5',400)
 73 insert into spj values('s2','p5','j1',400)
 74 insert into spj values('s2','p5','j2',100)
 75 insert into spj values('s3','p1','j1',200)
 76 insert into spj values('s3','p3','j1',200)
 77 insert into spj values('s4','p5','j1',100)
 78 insert into spj values('s4','p6','j3',300)
 79 insert into spj values('s4','p6','j4',200)
 80 insert into spj values('s5','p2','j4',100)
 81 insert into spj values('s5','p3','j1',200)
 82 insert into spj values('s5','p6','j2',200)
 83 insert into spj values('s5','p6','j4',500)
 84 select * from spj
 85 
 86 ---------------------------------------------------------------------------------------------------------------------------------
 87 
 88 
 89 --第4大题
 90 
 91 ----(1)题
 92 select distinct sno from spj where jno ='j1'
 93 
 94 ----(2)题
 95 select distinct sno from spj where jno ='j1'and pno='p1'
 96 
 97 ----(3)题
 98 select distinct sno 
 99 from spj 
100 where jno ='j1'
101       and pno in
102          (select pno 
103           from p 
104           where color='红')
105 
106 ----(4)题
107 select jno
108 from j
109 where not exists
110 (select *
111  from spj,s,p
112  where spj.jno=j.jno
113    and spj.sno=s.sno
114    and spj.pno=p.pno
115    and s.city='天津'
116    and p.color='hong');
117 
118 ----(5)题
119 SELECT JNO 
120 FROM SPJ SPJX
121 WHERE NOT EXISTS
122     (SELECT *
123     FROM SPJ SPJY
124     WHERE SPJY.SNO='S1'AND NOT EXISTS
125     (SELECT *
126     FROM SPJ SPJZ
127     WHERE SPJZ.SNO=SPJX.SNO
128     AND SPJZ.CNO=SPJY.CNO))
129 
130 
131 
132 ---------------------------------------------------------------------------------------------------------------------------------
133 
134 
135 --第5大题
136 
137 ----(1)题
138 select sname,city from s 
139 
140 ----(2)题
141 select pname,color,weight from p
142 
143 (3)
144 SELECT JNO
145 FROM SPJ
146 WHERE SNO='S1';
147 (4)
148 SELECT P.PNAME,SPJ.QTY
149 FROM SPJ,P
150 WHERE SPJ.PNO=P.PNO AND JNO='J2';
151 (5)
152 SELECT DISTINCT PNO
153 FROM SPJ
154 WHERE SNO IN
155     (SELECT SNO
156     FROM S
157     WHERE CITY='上海');
158 
159 ----(6)题
160 select jname from j where jno in(select jno from spj where sno in(select sno from s where city='上海'))
161 
162 ----(7)题
163 select jno
164 from j
165 where not exists
166        (select *
167         from spj,s
168         where spj.jno=j.jno and spj.sno=s.sno and s.city='天津'
169 
170 ----(8)题
171 update p set color='蓝' where color='红'
172 
173 ----(9)题
174 update spj 
175 set sno='S3'
176 WHERE SNO='S5' AND PNO='P6'AND JNO='J4'
177 
178 ----(10)题
179 DELETE 
180 FROM S
181 WHERE SNO='S2';
182 
183 DELETE 
184 FROM SPJ
185 WHERE SNO='S2';
186 
187 ----(11)题
188 insert into spj values ('s2','p4','j6',200)
189 
190 
191 ------------------------------------------------------------------------------------------------------------------------------
192 
193 --第11大题
194 
195 CREATE VIEW J1_SPJ
196 AS
197 SELECT SNO,PNO,QTY
198 FROM SPJ
199 WHERE JNO='J1'
200 (1)
201 SELECT PNO,QTY
202 FROM J1_SPJ
203 (2)
204 SELECT PNO,QTY
205 FROM J1_SPJ
206 WHERE SNO='S1'
207 
208 ------------------------------------------------------------------------------------------------------------------------------
209 
210 --第12大题
211 
212 ----(1)题
213 grant insert on  s to 张勇 with grant option  
214 
215                                 
216 ----(2)题
217 grant update(qty),select on spj to 李天明

 

posted on 2018-03-24 22:42  HBU_DAVID  阅读(1171)  评论(0)  编辑  收藏  举报

导航