数据库系统概论期末复习资料

FROM ORDER WHERE SNO=“S3”);

或:SELECT ENO,WHNO

FROM EMPLOYEE,ORDER

WHERE EMPLOYEE.ENO=ORDER.ENO AND ORDER.SNO=“S3”; (5).检索出目前与S3供应商没有联系的职工信息。

SELECT ENO,WHNO FROM EMPLOYEE WHERE ENO NOT IN

(SELECT ENO

FROM ORDER WHERE SNO=“S3”);

(6).检索出目前没有任何订购单的供应商信息。

SELECT * FROM SUPPLIER WHERE SNO NOT IN

(SELECT SNO FROM ORDER);

(7).检索出和职工E1、E3都有联系的北京的供应商信息。

SELECT * FROM SUPPLIER

WHERE ADDR=“北京” AND

(EXISTS (SELECT * FROM ORDER WHERE SNO=SUPPIER.SNO AND ENO=“E3”))

AND

(EXISTS (SELECT * FROM ORDER WHERE SNO=SUPPIER.SNO AND ENO=“E6”)); (8).检索出目前和华通电子公司有业务联系的每个职工的工资。 SELECT ENO,SALARY FROM EMPLOYEE WHERE ENO IN

(SELECT ENO FROM ORDER WHERE SNO IN

(SELECT SNO FROM SUPPLIER WHERE ADDR=“华通电子公司”));

或:SELECT ENO,SALARY

FROM EMPLOYEE,ORDER,SUPPLIER

WHERE EMPLOYEE.ENO=ORDER.ENO AND ORDER.SNO=SUPPLIER.SNO AND SUPPLIER.ADDR=“华通电子公司”;

(9).检索出与工资在1220元以下的职工没有联系的供应商的名称。

SELECT SNAME FROM SUPPLIER WHERE SNO NOT IN

(SELECT SNO FROM ORDER WHERE ENO IN

(SELECT ENO FROM EMPLOYEE WHERE SALARY<1220));

(10).检索出向S4供应商发出订购单的仓库所在的城市。

SELECT CITY FROM WAREHOUSE

WHERE WHNO IN (SELECT WHNO FROM EMPLOYEE

WHERE ENO IN (SELECT ENO FROM ORDER WHERE SNO=“S4”)); 或:SELECT CITY

FROM WAREHOUSE,EMPLOYEE,ORDER WHERE WAREHOUSE.WHNO=EMPLOYEE.WHNO

AND EMPLOYEE.ENO=ORDER.ENO AND ORDER.SNO=“S4”; (11).检索出在上海工作并且向S6供应商发出了订购单的职工号。

SELECT ENO FROM EMPLOYEE WHERE WHNO IN

(SELECT WHNO FROM WAREHOUSE WHERE CITY=“上海”) AND (ENO IN SELECT ENO FROM ORDER WHERE SNO=“S6”);

或:SELECT ENO

FROM EMPLOYEE,WAREHOUSE,ORDER WHERE EMPLOYEE.WHNO=WREHOUSE.WHNO AND WREHOUSE.CITY=“上海”

AND EMPLOYEE.ENO=ORDER.ENO AND ORDER.SNO=“S6”;

(12).检索出在广州工作并且只向S6供应商发出了订购单的职工号。

SELECT ENO FROM EMPLOYEE

WHERE (WHNO IN SELECT WHNO FROM WAREHOUSE WHERE CITY=“广州”)

AND (ENO IN SELECT ENO FROM ORDER WHERE SNO=“S6”) AND (NOT EXISTS (SELECT * FROM ORDER

WHERE SNO<>“S6” AND ENO=EMPLOYEE.ENO));

(13).检索出由工资多于1230元的职工向北京的供应商发出的订购单号。

SELECT ONO FROM ORDER

WHERE (ENO IN SELECT ENO FROM EMPLOYEE WHERE SALARY>1230) AND (SNO IN SELECT SNO FROM SUPPLIER WHERE ADDR=“北京” ); 或:SELECT ONO

FROM ORDER,EMPLOYEE,SUPPLIER

WHERE ORDER.ENO= EMPLOYEE.ENO AND EMPLOYEE.SALARY>1230

AND ORDER.SNO= SUPPLIER.SNO AND SUPPLIER. ADDR=“北京”;

(14).检索出仓库的个数。

SELECT COUNT(*) FROM WAREHOUSE; (15).检索出有最大面积的仓库信息。

SELECT *

FROM WAREHOUSE OUTER

WHERE OUTER.SIZE=(SELECT MAX(SIZE) FROM WAREHOUSE INNER); (16).检索出所有仓库的平均面积。

SELECT AVG(SIZE) FROM WAREHOUSE;

(17).检索出向S4供应商发出订购单的那些仓库的平均面积。

SELECT AVG(SIZE) FROM WAREHOUSE

WHERE WHNO IN (SELECT WHNO FROM EMPLOYEE

WHERE ENO IN (SELECT ENO FROM ORDER WHERE SNO=“S4”));

(18).检索出每个城市的供应商个数。

SELECT CITY,COUNT(SNO) FROM SUPPLIER GROUP BY CITY;

(19).检索出每个仓库中工资多于1220元的职工个数。

SELECT WHNO,COUNT(ENO) FROM EMPLOYEE WHERE SALARY>1220 GROUP BY WHNO;

或:SELECT WHNO,COUNT(ENO)

FROM EMPLOYEE GROUP BY WHNO

HAVING SALARY>1220;

(20).检索出和面积最小的仓库有联系的供应商的个数。

SELECT COUNT(DISTINCT SNO) FROM ORDER WHERE ENO IN

SELECT ENO FROM EMPLOYEE

WHERE WHNO IN

SELECT WHNO FROM WAREHOUSE OUTER

WHERE OUTER.SIZE=SELECT MIN(SIZE)

FROM WAREHOUSE INNER;

(21).检索出工资低于本仓库平均工资的职工信息。

SELECT * FROM EMPLOYEE OUTER

WHERE OUTER.SALARY<(SELECT AVG(SALARY) FROM EMPLOYEE INNER WHERE INNER.WHNO=OUTER.WHNO GROUP BY WHNO);

13.以下面的数据库为例,用SQL完成以下更新操作。关系模式如下:

仓库(仓库号,城市,面积)←→ WAREHOUSE(WMNO,CITY,SIZE) 职工(仓库号,职工号,工资)←→ EMPLOYEE(WHNO,ENO,SALARY)

订购单(职工号,供应商号,订购单号,订购日期)←→ ORDER(SNO,SNO,ONO,DATE) 供应商(供应商号,供应商名,地址)←→ SUPPLIER(SNO,SNAME,ADDR) (1).插入一个新的供应商元组(S9,智通公司,沈阳)。

INSERT INTO SUPPLIER VALUES(S9,智通公司,沈阳); (2).删除目前没有任何订购单的供应商。

DELETE FROM SUPPLIER

WHERE NOT EXISTS (SELECT * FROM ORDER WHERE ORDER.SNO=SUPPLIER.SNO); 或:DELETE FROM SUPPLIER

WHERE SNO NOT IN (SELECT SNO FROM ORDER); (3).删除由在上海仓库工作的职工发出的所有订购单。

DELETE FROM ORDER

WHERE ENO IN (SELECT ENO FROM EMPLOYEE

WHERE WHNO IN {SELECT WHNO FROM WAREHOUSE WHERE CITY=“上海”}};

(4).北京的所有仓库增加100m2的面积。

UPDATE WAREHOUSE

SET SIZE=SIZE+100 WHERE CITY=“北京”; (5).给低于所有职工平均工资的职工提高5%的工资。

UPDATE EMPLOYEE OUTER

SET OUTER.SALARY=OUTER.SALARY*1.05

WHERE OUTER.SALARY

联系客服:779662525#qq.com(#替换为@)