【数据库】关系代数和SQL语句

一

对于教学数据库的三个基本表

学生S(S#,SNAME,AGE,SEX)

学习SC(S#,C#,GRADE)

课程(C#,CNAME,TEACHER)

（1）试用关系代数表达式和SQL语句表示：检索WANG同学不学的课程号

select C# from C
where C# not in(select C# from SCwhere S# in(select S# from Swhere SNAME='WANG'));

（2）试用SQL语句完成：在基本表S中检索每一门成绩都大于80分的学生学号、姓名和性别，并把检索到的值送往一个已存在的基本表STUDENT(S#,SNAME,SEX)

insert into STUDENT(S#,SNAME,SEX)select S#,SNAME,SEX from Swhere not exists(select * from SC whereGRADE < 80 and S.S#=SC.S#);

（3）试用SQL语句完成：检索选修课程包含LIU老师所授课的学生学号

select distinct from scwhere C# in(select C# from Cwhere TEACHER = 'LIU');

（4）将关系代数表达式意思表达出来

检索全部学生都选修的课程的课程号与课程名

二

现有数据库关系如下：

学生(学号，姓名，性别，专业)

课程(课程号，课程名，学分)

学习(学号，课程号，分数)

分别用关系代数表达式和SQL语句实现下列1-5题

1.检索所有选修了课程号为“C112”的课程的学生的学号和分数

select 学号,分数 from 学习where 课程号 = 'C112';

2.检索“英语”专业学生所学课程的信息，包括学号、姓名、课程号和分数

多表查询

select 学生.学号,姓名,课程.课程名,分数
from 学习,学生,课程
where 学生.学号=学习.学号 and 学习.课程号=课程.课程号 and 专业='英语';

3.检索“数据库原理”课程高于90分的所有学生的学号、姓名、专业和分数

 

多表查询

select 学生.学号,姓名,专业,分数 from 学生,学习,学号
where 学生.学号=学习.学号 and 学习.课程号=课程.课程号 and 分数>90 and 课程名='数据库原理';

4.检索没学课程号为C135课程的学生信息，包括学号，姓名和专业

嵌套

select 学号,姓名,专业 from 学生
where 学号 not in
(select 学号 from 学习 where 课程号='C
135');

5.检索至少学过课程号为C135和C219的课程的学生的信息，包括学号、姓名和专业

别名

select 学号,姓名,专业 from 学生 where 学号 in
(select x1.学号 from 学习 x1,学习x2 
where x1.学号=x2.学号 and
x1.课程号='C135' and x2.课程号='C219'
);

存储过程

3.设有图书关系BOOK(BID,BNAME,QTY)，属性依次是书号、书名、库存数量。

规定当一批图书入库时，首先判断是否存在该书，如果存在则更改其库存数量，并通知客户最新的库存数量；否则，插入该书信息（书名暂时为空），并发出“新书入库”的提示信息。

设计一个存储过程来实现此功能，该存储过程有书号(@bid)和入库数量(@s)2个参数。同时，要求设计一个SQL程序验证存储过程的作用

create procedure BOOK_QTY @bid int,@s int
asdelete @bs intif exists (select * from BOOK where BID=@bid)beginupdate BOOK set QTY=QTY+@s where BID=@bidselect @bs=QTY from BOOK where BID=@bidprint '库存数量'+str(@s)endelseinsert into BOOK(BID,QTY) values(@bid,@s)print '新书入库'

四

设有4个关系模式：

供应商关系：S(SNO,SNAME,CITY)，属性依次是供应商号、供应商名和所在城市；

零件关系：P(PNO,PNAME,COLOR)，属性依次是零件号、零件名和颜色；

工程关系：J(JNO,JNAME,CITY)，属性依次是工程号、工程名和所在城市；

供应关系：SPJ(SNO,PNO,JNO,QTY)，属性依次是供应商号、零件号、工程号和数量。

用SQL语句实现下列操作：

1.查询P1号零件的颜色

select COLOR from P where PNAME='P1';

2.查询S1号供应商为J1号工程提供两件的编号和供应数量，查询结果按零件号降序排列

select PNO,QTY from SPJ
where SNO='S1' and JNO='J1'
order by PNO desc;

3.查询由S1号供应商提供红色零件的工程号

select JNO from P join SPJ on(SPJ.PNO=P.PNO)
where SNO='S1' and COLOR='红';

select JNO from SPJ,P
where SNO='S1' and COLOR='红' and SPJ.PNO=P.PNO;

4.查询与其提供零件的供应商所在城市为同一城市的工程号

select J.JNO from J join SPJ on(J.JNO=SPJ.JNO) join S on(S.SNO=SPJ.SNO) where J.CITY=S.CITY;

select J.JNO from S,J,SPJ
where S.SNO=SPJ.SNO and J.JNO=SPJ.JNO and S.CITY=J.CITY;

5.统计所在地为杭州的工程数量

select count(JNO) from J
where CITY='杭州';

6.统计每个供应商提供的零件总数

select SNO,sum(QTY) from SPJ
group by SNO;

7.查询比J1号工程使用的零件数量多的工程号

select JNO from SPJ
group by JNO
having sum(QTY) >
(select sum(QTY) from SPJ where JNO='J1');

8.删除为由S1号供应商提供零件的工程信息

delete from J 
where JNO in
(select JNO from SPJ where SNO='S1');

五

设有3个关系模式：

职工（职工号，姓名，年龄，性别）

公司（公司号，名称，地址）

工作（职工号，公司名，工资）

在定义表结构是，用SQL子句实现下列完整新约束：

（1）职工表中职工号非空且唯一

  primary key(职工号)  

（2）工作表中职工号的值必须是职工表中的有效职工号

   foreign key(职工号) references 职工(职工号)  

（3）职工的工资不能低于800元

   check(工资>=800)  

（4）男职工的年龄在18~55之间

  check(性别='男' and 年龄>=18 and 年龄<=55)  

对上面的3个关系，写出检索金山公司所有职员的姓名和工资的关系代数表达式

六

create table Customers(CustomerID char(6) not null comment '客户编号' primary key,CName varchar(15) not null comment '客户名称',City varchar(15) comment '客户所在城市'
) comment '客户表';create table Goods(GoodID char(6) not null comment '商品编号' primary key,GName varchar(20) not null comment '商品名称',Price money not null comment '单价',Provider varchar(30) comment '供应商',Stocks int comment '库存量',Status bit comment '商品状态'
) comment '商品表';create table Orders(OrderID char(6) not null comment '订单号' primary key,GoodID char(6) not null comment '商品编号' foreign key(GoodID) references Goods(GoodID),CustomerID char(6) not null comment '客户编号' foreign by(CustomerID) references Customers(CustomerID),Quantity int not null comment '订货数量',OrderSum moeny comment '订货金额',OrderDate datetime comment '订货日期'
) comment '订单表';

（1）查找所在城市为西安的客户的信息；

select * from Customers 
where City='西安';

（2）查找商品名称中包含“Computer”的商品的编号、名称及单价；

select GoodID,GName,Price from Goods
where GName like '%Computer%';

（3）查找库存量介于100和500之间的商品的名称、库存量及单价；

select GName,Stocks,Price from Goods
where Stocks between 100 and 500;

（4）查找2011年1月1日至2011年6月30日期间，订货金额大于30000的所有订单的客户姓名、商品名称、单价、订货数量和订货金额；

SELECT c.CName, g.GName, g.Price, o.Quantity, o.OrderSum
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Goods g ON o.GoodID = g.GoodID
WHERE o.OrderDate BETWEEN '2011-01-01' AND '2011-06-30'
AND o.OrderSum > 30000;

select CName,GName,Price,Quantity,OrserSum
from Customers,Goods,Orders
where OrderDate between '2011-01-01' and '2011-06-30' and OrderSum > 30000 and Customers.CustomerID=Orders.CustomerID and Goods.GoodID=Orders.GoodID;

（5）将所有库存量大于500的商品的单价下调25%；

update Goods 
set Price=Price*0.75
where Stocks > 500;

（6）请将（“100661”，“hpLaserJet1020”,1800,“普惠公司”，10，0）插入到商品表中；

insert into Goods values('100661','hpLaserJet1020',1800,'惠普公司',10,0);

（7）授权用户user1对Orders表进行插入和删除操作

grant insert,delete on Market.Orders to 'user1'@'%';