answer.sql

一般的数据库系统

一：
use master
create database bbs1
use  bbs1
create table 肖像
   (肖像编号 integer identity(1,1) primary key ,
    图片 image,
    描述 char(10)
)
go
create table 心情图片
   (心情图片编号 integer identity(1,1) primary key,
    图片 image,
    描述 char(20)
)

go
create table 用户类别(
  类别编号  tinyint primary key,
  类别名称 varchar(20) not null,
  权限说明 varchar(50)
)

go
create table 用户
(注册名 varchar(20) primary key,
 密码 varchar(20),
 性别 bit check(性别 in (1,0)) default 1 not null,
 出生日期 smalldatetime not null,
 国家  varchar(30) not null,
 省州  varchar(20),
 市    varchar(20),
 地址  varchar(50),
 email varchar(30),
 爱好  varchar(100),
 取回密码问题 varchar(200) not null,
 取回密码问题答案 varchar(50) not null,  
 肖像  integer references 肖像(肖像编号) not null default 1,
 用户类别  tinyint references 用户类别(类别编号)  not null default 1,
 主题量 integer,
 参与讨论的主题量 integer,
 登录次数 integer,
 上次登录时间 smalldatetime default getdate(),
 IP地址 char(15)
)
go
create table 版块
(
  版块编号  integer primary key,
  版块名称 varchar(30) not null,
  创建日期  smalldatetime default getdate() not null,
  版主注册名 varchar(20) references 用户(注册名) not null,
  版主任职日期 smalldatetime  
)
go
create table 帖子(
  帖子编号 bigint identity(1,1) primary key,
  主题 varchar(200) not null,
  内容  text,
  发帖人 varchar(20) references 用户(注册名) not null,  
  心情图片编号 integer references 心情图片(心情图片编号),
  发帖时间 smalldatetime default getdate(),
  版块编号 integer references 版块(版块编号),
  主题编号 bigint references 帖子(帖子编号), 
  父贴编号 bigint references 帖子(帖子编号),
  层次 integer,
  人气 integer,
  回复数 integer,
  最后回复人 varchar(20) references 用户(注册名) not null,  
  最后回复日期  smalldatetime not null, 
  是否被屏蔽 bit default 0,
  是否精华 bit default 0
)
go
create table 短信
( 短信编号  bigint identity(1,1) primary key,
  发送者   varchar(20) references 用户(注册名) not null,  
  短信标题 varchar(100) not null,
  短信内容 varchar(600),
  接收者   varchar(20) references 用户(注册名) not null,  
  接收者已查看 bit default 0,
  发送日期 smalldatetime default getdate(),
  发送者删除标志 bit default 0,
  接收者删除标志 bit default 0,
  是否发送标志 bit default 0
)


二：
1、use master
create database ckgl
go
use ckgl
create table 仓库(仓库号 char(3) primary key,
                  城市 varchar(30) not null,
                  面积 decimal(10,3) check(面积>0)
                 )
go
create table 职工(职工号 char(4) primary key,
                  姓名 char(8) not null,
                  性别 bit default 1,
                  仓库号 char(3) references 仓库(仓库号),
                  工资 decimal(8,2) check(工资>0)
                 )
go
create table 供应商(供应商号 char(3) primary key,
                    供应商名 varchar(50) not null,
                    地址 varchar(100) not null
                   )
go
create table 订购单(订购单号 char(4) primary key,
                   职工号 char(4) references 职工(职工号),
                   供应商号 char(3) references 供应商(供应商号),
                   订购日期 smalldatetime default getdate(),
                   金额 money)

2、 
insert into 仓库 values('WH1','武汉',1250)
insert into 仓库 values('WH2','武汉',250)
insert into 仓库 values('WH3','广州',2500)
insert into 仓库 values('WH4','上海',2000)
insert into 仓库 values('WH6','北京',2700)
insert into 仓库 values('WH8','北京',700)
insert into 职工 values('E001','王理',1,'WH1',1250)
insert into 职工 values('E002','李度',1,'WH4',2250)
insert into 职工 values('E004','李宁',0,'WH3',3050)
insert into 职工 values('E005','李小双',1,'WH1',850)
insert into 职工 values('E006','陈晨',0,'WH8',5850)
insert into 职工 values('E008','张三',0,'WH2',5850)
insert into 职工 values('E009','张缇',0,'WH4',5850)
insert into 职工 values('E010','宫新',1,'WH3',5850)
insert into 供应商 values('S01','自强工贸公司','北京市朝阳区东长安街3211号')
insert into 供应商 values('S02','新星玩具厂','沈阳市铁西区皇冈街512号')
insert into 供应商 values('S03','布来卡电子厂','武汉市武昌区盐湖路12号')
insert into 供应商 values('S04','重果仪器设备公司','武汉市汉口解放大道125号')
insert into 供应商 values('S06','纯迪机电公司','重庆市奉节县春风大道125号')
insert into 订购单 values('P001','E002','S03','2000.3.5',15435.63)
insert into 订购单 values('P002','E001','S04','2000.5.5',20000.00)
insert into 订购单 values('P003','E001','S03','2000.1.5',30000.00)
insert into 订购单 values('P004','E002','S06','2001.11.5',60000.00)
insert into 订购单 values('P005','E001','S06','2001.7.5',30000.00)
insert into 订购单 values('P006','E008','S01','2002.7.5',80000.00)
insert into 订购单 values('P007','E006','S01','2002.7.5',80000.00)
insert into 订购单 values('P008','E005','S03','2002.7.5',110000.00)
insert into 订购单 values('P009','E004','S02','2002.7.15',120000.00)
insert into 订购单 values('P010','E009','S04','2002.7.15',125000.00)
insert into 订购单 values('P011','E001','S01','2003.1.15',125000.00)
insert into 订购单 values('P012','E002','S06','2003.1.15',128000.00)
insert into 订购单 values('P013','E001','S02','2004.6.15',128000.00)
insert into 订购单 values('P014','E009','S03','2004.8.15',128000.00)
insert into 订购单 values('P015','E010','S02','2005.8.15',130000.00)
insert into 订购单 values('P016','E004','S01','2005.8.5',135000.00)

3、
(1)查询职工“张三”的工资；
   select 工资 from 职工 where 姓名='张三'
(2)查询在“上海”的仓库的信息;
   select * from 仓库 where 城市='上海'
(3)查询供应商为公司的供应商信息；
   select * from 供应商 where 供应商名 like '%公司'
(4)查询各员工的工资总额、平均工资、最高工资、最低工资；
   select sum(工资),avg(工资),max(工资),min(工资) from 职工
(5)查询仓库总数和总面积；
   select count(*),sum(面积) from 仓库
(6)查询有职工工资超过5000元的仓库个数；
   select count(distinct 仓库号) from 职工 where 工资>2500
(7)查询在各城市的仓库数及其总面积；
   select 城市,count(*),sum(面积) from 仓库 group by  城市
(8)查询有哪些城市的仓库面积之和大于500平方米（例如：武汉有三个仓库，面积和为850m2）；
   select 城市 from 仓库 group by 城市 having sum(面积)>500
(9)查询各个仓库中工资多于1200元的职工个数;
   select 仓库号,count(*) from 职工 where 工资>1200 group by 仓库号
(10)查询向供应商“S03”发过订单的职工号和他所在的仓库号;
   select 职工号,仓库号 from 职工 where 职工号 in(select 职工号 from 订购单 where 供应商号='S03')
(11)查询职工“张三”所签发的所有订单的订单号及其订购日期；
   select 订购单号,订购日期 from 订购单,职工 where 职工.职工号=订购单.职工号 and 姓名='张三'
(12)查询和职工E001和E005都签订过订单的供应商信息;
   select * from 供应商 where 供应商号 in(select 供应商号 from 订购单 where 职工号='E001')  and 供应商号 in(select 供应商号 from 订购单 where 职工号='E005')
(13)查询供应商“S03”的历年的订购单个数和订购总金额，并按年份升序排列；（提示：使用 group by year(订购日期)）   
   select year(订购日期),count(*),sum(金额) from 订购单 where 供应商号='S03' group by year(订购日期) order by year(订购日期) 
(14)查询哪些城市至少有一个仓库的职工的工资低于800元；
   select distinct 城市 from 仓库 where 仓库号 in (select 仓库号 from 职工 where 工资<800)
(15)查询所有职工的工资都多于1210元的仓库的信息；
   select * from 仓库 where 仓库号 not in (select 仓库号 from 职工 where 工资<1210)
(16)查询和职工”E004”挣同样多工资的所有职工；
   select 职工号,姓名 from 职工 where 工资=(select 工资 from 职工 where 职工号='E004') and  职工号!='E004'
(17)查询每个职工经手的具有最高总金额的订购单信息,并按职工号升序排列 。
   select * from 订购单 outerTable where 金额>=all(select 金额 from 订购单 innerTable where innertable.职工号=outerTable.职工号 ) order by 职工号
(18)查询给所有供应商都发过订单的员工的信息。
   select * from 职工 E where not exists(select * from 供应商 S where 供应商号 not in (select 供应商号 from 订购单 where 职工号=E.职工号 and 供应商号=S.供应商号)  )
(19)为仓库号为“W01”的仓库增加一名职工“张三”，其工资为1000，其职工号为“E025”；
   insert into 职工(职工号,姓名,仓库号,工资) values('E025','张三','W01',1000)
(20)将工资在1000元以下的职工的工资增加10%，在1000元以上的增加8%；
   update 职工 set 工资=工资*1.08 where 工资>=1000
   update 职工 set 工资=工资*1.10 where 工资<=1000 
(21)将上一年度签订的订购单的总金额位居前3名的职工的工资增加500元；
   update 职工 set 工资=工资+500 where 职工号  in (select top 3 职工号  from  订购单 where year(订购日期)=year(getdate())-1 group by 职工号 order by sum(金额) desc )
(22)将供应商”S03”的名称改为”鹏达电子元件公司”，地址该位“武汉市解放大道234号”；