检索，存储过程，触发器相关练习。

仓库号城市面积
WH1北京370
WH2上海500
WH3广州200
WH4广州300
WH5天津340
WH6上海350
WH7上海600
WH8天津300
职工号仓库号工资
E1WH22220
E2WH11810
E3WH21850
E5WH31530
E6WH11550
E7WH42300
E8WH42000
E9WH52050
E10WH51900
E11WH62100
E12WH71700
E15WH51780
E16WH11600
职工号供应商号订购单号订购日期
E3S7OR672005-12-4
E1S4OR732006-4-1
E7S4OR762006-4-2
E6S6OR772006-1-21
E3S4OR792005-11-15
E1S6OR802006-2-1
E3S6OR902006-3-12
E3S3OR912006-3-2

|供应商号|供应商名|地址|
|:—|:—-|:—|:—|
|S3|振华电子厂|西安|
|S4|华通电子公司|北京|
|S6|607厂|郑州|
|S7|爱华电子厂|北京|

检索

  1. 检索在北京的供应商的名称。
    1
    select 供应商名 from gys where 地址='北京'
  2. 检索发给供应商S6的订购单号。
    1
    select 订购单号 from dg where 供应商号='S6'
  3. 检索出职工E6发给供应商S6的订购单信息。
    1
    2
    select * from dg where 职工号='E6'and 供应商号='S6'

  4. 检索出向供应商S3发过订购单的职工的职工号和仓库号。
    1
    2
    select zg.职工号,仓库号 from dg join zg on zg.职工号=dg.职工号
     where 供应商号='S3'
  5. 检索出目前与S3供应商没有联系的职工信息。
    1
    2
    select * from zg where 职工号 not in
    (select 职工号 from dg where 供应商号='S3')
  6. 检索出目前没有任何订购单的供应商信息
    1
    2
    select * from gys where 供应商号 not in
     (select distinct 供应商号 from dg)
    1
    2
     SELECT * FROM gys WHERE NOT EXISTS 
    ( SELECT * FROM dg WHERE 供应商号=gys.供应商号 ) 
  7. 检索出和职工E1、E3都有联系的北京的供应商的信息。
    1
    2
    3
    4
    5
    select * from gys where 地址='北京' 
    and 供应商号 in
    (select 供应商号 from dg where 职工号='E1')
    and 供应商号 in
    (select 供应商号 from dg where 职工号='E3')
  8. 检索出目前和华通电子公司有业务联系的每个职工的工资。
    1
    2
    3
    select 职工号,工资 from zg where 职工号 in
    (select 职工号 from dg where 供应商号 in
    (select 供应商号 from gys where 供应商名='华通电子公司'))
  9. 检索出与工资在1220元以下的职工没有联系的供应商名称。
    1
    2
    3
    4
    5
    select 供应商名 from gys 
    where 供应商号 not in
    (select 供应商号 from dg 
    where 职工号 in
    (select 职工号 from zg where 工资<1220))
    1
    2
    3
    4
    select 供应商名 from gys 
    where 供应商号 not in 
    (select 供应商号 from dg join zg on dg.职工号=zg.职工号
    where 工资>2250)
  10. 检索出向S4供应商发出订购单的仓库所在的城市。
    1
    2
    3
    select 城市 from ck where 仓库号 in
    (select 仓库号 from zg where 职工号 in
    (select 职工号 from dg where 供应商号='S4'))
  11. 检索出在上海工作并且向S6供应商发出了订购单的职工号。
    1
    2
    3
    select 职工号 from zg where 仓库号 in
    (select 仓库号 from ck where 城市='上海' and 职工号 in
    (select 职工号 from dg where 供应商号='S6'))
    1
    2
    3
    select dg.职工号 from zg join ck on zg.仓库号=ck.仓库号
    join dg on zg.职工号=dg.职工号
    where 城市='上海' and 供应商号='S6'
  12. 检索出在广州工作并且只向S6供应商发出了订购单的职工号。
    1
    2
    3
    4
    5
    6
    select 职工号 from zg where 仓库号 in
    (select 仓库号 from ck where 城市='广州' 
    and 职工号 in
    (select 职工号 from dg where 供应商号='S6') 
    and 职工号 not in
    (SELECT 职工号 FROM dg WHERE 供应商号!='S6'))
  13. 检索出由工资多于1230元的职工向北京的供应商发出的订购单号。
    1
    2
    3
    select 订购单号 from dg where 职工号 in
    (select 职工号 from zg where 工资>1230) and  供应商号 in
    (select 供应商号 from gys where 地址='北京')
    1
    2
    3
    4
    select 订购单号 from dg where 职工号 in
    (select 职工号 from zg where 工资>2000)
    and 供应商号 in (
    select 供应商号 from gys where 地址='北京')
  14. 检索出仓库的个数
    1
    select count(仓库号) from ck
  15. 检索出有最大面积的仓库信息。
    1
    2
    select * from ck where ck.面积=(
    select max(面积) from ck )
  16. 检索出所有仓库的平均面积。
    1
    select avg(面积) 平均面积  from ck
  17. 检索出向S4供应商发出订购单的那些仓库的平均面积。
    1
    2
    3
    select avg(面积) as 平均面积 from ck where 仓库号 in
    (select 仓库号 from zg where 职工号 in
    (select 仓库号 from dg where 供应商号='S4'))
    1
    2
    3
    4
    select avg(面积) as 平均面积 from ck
    where 仓库号 in 
    (select 仓库号 from zg join dg on zg.职工号=dg.职工号
    where 供应商号='S4')
  18. 检索出每个城市的供应商个数。
    1
    2
    select 地址,count(*) from gys
    group by 地址
  19. 检索出每个仓库中工资多于1220元的职工个数。
    1
    2
    select 仓库号,count(*) from zg where 工资>1220
     group by 仓库号
  20. 检索出和面积最小的仓库有联系的供应商的个数。
    1
    2
    3
    4
    5
    select count(供应商号) from gys where 供应商号 in
    (select 供应商号 from dg where 职工号 in
    (select 职工号 from zg where 仓库号 in
    (select 仓库号 from ck where 面积=
    (select min(面积) from ck))))
    1
    2
    3
    select count(供应商号) from dg where 职工号 in 
    (select 职工号 from zg where 仓库号 in
    (select 仓库号 from ck where 面积=(select min(面积) from ck)))
  21. 检索出工资低于本仓库平均工资的职工信息。
    1
    2
    3
    SELECT * FROM 职工 out WHERE 工资< 
    (SELECT AVG(工资) FROM 职工 inne 
    WHERE 仓库号=out.仓库号) 

存储过程

  1. 创建一个存储过程P1，输入某个仓库号，查询对应仓库的
    信息。

    1
    2
    3
    4
    create proc P1
    @ckh nvarchar(225) 
    as
    select * from ck where 仓库号=@ckh
    1
    exec P1 'WH8'

  2. 创建一个存储过程P2，输入某个仓库号，输出对应仓库的的职工人数。

    1
    2
    3
    4
    5
    create proc P2
    @ckh nvarchar(20)
    as
    select count(职工号) 职工人数 from zg where 仓库号 = @ckh
    group by 仓库号
    1
    exec P2 'WH1'
1
2
3
4
5
6
7
8
9
10
11
12
13
alter TRIGGER tri_salary
ON zhg FOR UPDATE
AS
SELECT * FROM INSERTED JOIN
DELETED ON INSERTED.职工号 = DELETED.职工号
IF UPDATE(工资)
IF EXISTS(SELECT * FROM INSERTED JOIN
DELETED ON INSERTED.职工号 = DELETED.职工号
WHERE INSERTED.工资&lt;DELETED.工资)
begin
print(&#39;新工资值低于原工资，拒绝修改&#39;)
ROLLBACK
end
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE PROCEDURE countzhigong
@ck char(20), @renshu int output
AS
SELECT @renshu = count(职工号)
FROM ck left outer JOIN zhg ON ck.仓库号= zhg.仓库号
WHERE ck.仓库号=@ck
Declare @re int
Execute countzhigong &#39;wh2&#39;, @re output
Print @re
SELECT *
FROM ck left outer JOIN zhg ON ck.仓库号= zhg.仓库号

SELECT * FROM zhg

创建、修改、删除触发器。

  1. 创建一个触发器zhigong1，在对职工表的工资属性修改时触发：每修改一个职工的工资，就检查新工资是否比原工资低，若是低，则恢复原工资而不做修改。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create trigger zhigong1
    on zg for insert,update
    as
    select * from inserted join deleted 
    on inserted.职工号=deleted.职工号
    if update(工资)
    if exists (select * from inserted join 
    deleted on inserted.职工号=deleted.职工号
    where inserted.工资<deleted.工资)
    rollback
    1
    update zg set 工资='3000' where 职工号='E9'
  2. 创建一个触发器zhigong2，在插入新的职工记录时触发：检查新职工记录的仓库号是否在仓库表的仓库号中存在，如果不存在就撤消插入操作。
    1
    2
    3
    4
    5
    6
    alter trigger zhigong2
    on zg for insert
    as
    select * from inserted join deleted on inserted.职工号=deleted.职工号
    if not exists ( select 仓库号 from inserted where 仓库号 in (select 仓库号 from ck ))
    rollback
    1
    insert into zg values('E33','WH2','3000')
  3. 删除以上两个触发器。
    1
    drop trigger zhigong1,zhigong2
  4. 创建一个触发器dinggou1，在对订购表进行插入操作时，输出该职工的所经手的订购单数目。
    1
    2
    3
    4
    5
    6
    7
    create  trigger  dinggou1
    on dg for insert
    as
    select dg.职工号,count(dg.订购单号) as 订购单数目
    from dg join inserted on dg.职工号=inserted.职工号
    where dg.职工号=inserted.职工号
    group by dg.职工号
    1
    2
    insert into dg values
    ('E1','S6','OR98','2018-01-21 00:00:00.000')
许可协议

本文采用 署名-非商业性使用-相同方式共享 4.0 国际 许可协议，转载请注明出处。

快来参与讨论吧~

本站由 VITAN 使用 Stellar 主题创建。
本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议，转载请注明出处。