用SQL语言实现按需查询.doc

用SQL语言实现按需查询 一、实验目的 1熟练掌握用SELECT书写简单查询 2熟练掌握用SELECT书写连接查询 3熟练掌握用SELECT书写嵌套查询 4.熟练掌握INSERT、 UPDATE 、DELETE的用法 二、实验内容 根据实验四建立的数据库，参照上图，完成如下查询。 1以图5-1的数据库为例，用SQL完成以下检索： 检索在北京的供应商的名称。select 供应商名 from 供应商 where 地址=北京select 订单号from 订单where 供应商号=s4select 订单.* from 订单where 订单.职工号=e1 and 订单.供应商号=s4select 订单.职工号, 职工.仓库号from 订单,职工where 订单.供应商号=s7 and 职工.职工号=订单.职工号select DISTINCT 职工.* from 职工,订单 where 职工.职工号= 订单.职工号and 订单.供应商号not likes3select * from 供应商where 供应商号not in(select 供应商号 from 订单where 供应商号is not null)SELECT * FROM 供应商WHERE 供应商.供应商号in ( SELECT 订单.供应商号FROM 订单WHERE 订单.职工号=e1) and 供应商.供应商号in( SELECT 订单.供应商号FROM 订单WHERE 订单.职工号=e3) and 供应商.地址like 北京select 职工.* from 职工where 职工.职工号in (select 订单.职工号from 订单where 订单.供应商号= (select 供应商号from 供应商where 供应商名=华佗公司) )select 供应商名from 供应商where 供应商号in (select 订单.供应商号from 订单where 订单.职工号in (select 职工.职工号from 职工where 工资1220)) select 订单.职工号from 订单where 订单.供应商号= s4 and 订单.职工号in (select 职工.职工号from 职工where 职工.仓库号in (select 仓库号from 仓库where 城市=上海))select * from 订单where exists (select * from 订单where 供应商号s4) SELECT 职工号FROM 职工WHERE 仓库号IN (SELECT 仓库号FROM 仓库WHERE 城市=广州)AND 职工号IN(SELECT 职工号FROM 订单WHERE 供应商号=S4 and 职工号 not in(select 职工号FROM 订单WHERE 供应商号S4 )) select 订单号from 订单where 订单.职工号in (select 职工号from 职工where 工资1230) and 订单.供应商号in(select 供应商号from 供应商where 地址=北京)select count(*) from 仓库select * from 仓库where 面积=(select max(面积) from 仓库)(select avg(面积) from 仓库)select avg(面积) from 仓库where 仓库号in (select 仓库号from 职工where 职工号in(select 职工号from 订单where 供应商号=s4))select 地址,count(*) from 供应商 group by 地址select 仓库号,count(仓库号) from 职工 where 工资1220 group by 仓库号select count(*) from 订单where 职工号in (select 职工号from 职工where 仓库号=(select 仓库号from 仓库where 面积=(select min(面积) from 仓库)))select 职工号from 职工a where 工资 (select avg(工资) from 职工where 职工.仓库号=a.仓库号)insert 供应商values (s9,智通公司,沈阳)delete from 供应商where 供应商号not in (select 供应商号from 订单where 订单.供应商号is not null)delete from 订单where 职工号in (select 职工号from 职工where 仓库号in(select 仓库号from 仓库where 城市=上海))update 仓库set 面积=面积+100 where