第四章 数据查询2 (上机)

原创 2013年12月02日 21:14:32


                                                          第四章 数据查询(2)

====================================================================================

create database Beta_DB    --创建数据库

go
use Beta_DB
go
create table employee
(
emp_no char(5) primary key, --员工编号
emp_name char(10) not null, --员工姓名
sex char(1) not null, -- 性别
dept char(4) not null, --部门
title char(6) not null, --职称
date_hired datetime not null,--上任日期
birthday datetime null, --生日
salary int not null, --工资
addr char(50) null, -- 住址
modifier char(5) default (user_name(null)), --操作人
mod_date datetime default (getdate()) -- 操作日期


)


go


create table customer
(
cus_id char(5) primary key , --客户编号
cus_name char(20) not null, --客户姓名
addr char(40) not null, --客户住址
tel_no char(10) not null, --客户电话
zip char(6) null --邮递区号


)


go


create table sales
(
order_no int identity(1000,1) not null,--定单编号
cus_id char(5) not null, --客户编号
sale_id char(5) not null, --业务员编号
toa_amt numeric (9,2) not null, --定单金额
order_date datetime default(getdate()),--定货日期0
ship_date datetime not null,--出货日期
invoice_no char(10) not null,--发票号码
o_static char(2) not null,--定单处理状况
constraint UQ_invoice unique nonclustered (invoice_no)
)


go


create table sale_item
(
order_no int not null, --定单编号
prod_id char(5) not null,--产品编号
sup_id char(5) not null, --供应商编号
qty int not null, --销售数量
unit_price numeric (7,2) not null, --单价
order_date datetime null,--定单日期
constraint pk_sale_item primary key clustered(order_no,prod_id,sup_id) 
)


go


create table stock
(
prod_id char(5) not null, --产品编号
sup_id char(5) not null, --供应商编号
stk_qty int not null, --库存数量
constraint pk_stock primary key clustered(prod_id,sup_id)
)


go


create table pur_item
(
pur_no int not null, --采购定单
prod_id char(5) not null,--产品编号
sup_id char(5) not null,--供应商编号
qty int not null, --采购数量
unit_price numeric(7,2) not null, --单价
pur_date datetime not null, --采购日期
constraint pk_pur_item primary key clustered(pur_no,prod_id,sup_id)
)


go


create table product
(
prod_id char (5) primary key, --产品编号
prod_name char(20) not null --产品名称
)


go


create table supply
(
sup_id char(5) not null, --供应商编号
sup_name char(20) null --供应商名称
)
go


insert into employee values('E0001','王大华','M','业务','经理','1976/10/13','1951/08/01',80000,'上海市',default,default)
insert into employee values('E0003','陈自强','M','会计','科长','1986/09/15','1963/06/09',48000,'南京市',default,default)
insert into employee values('E0014','周小梅','F','业务','职员','1996/03/01','1970/03/28',32000,'上海市',default,default)
insert into employee values('E0009','陈建国','M','管理','科长','1987/04/15','1967/09/01',45000,'天津市',default,default)
insert into employee values('E0017','林光华','M','业务','职员','1995/10/13','1973/08/17',30000,'上海市',default,default)
insert into employee values('E0006','李珠珠','F','管理','经理','1988/01/01','1961/07/12',60000,'北京市',default,default)
insert into employee values('E0002','李庄敬','M','人事','科长','1980/09/15','1958/05/03',80000,'广州市',default,default)
insert into employee values('E0010','王成功','M','信息','职员','1993/02/15','1969/04/15',45000,'北京市',default,default)
insert into employee values('E0013','陈中华','M','业务','职员','1993/02/15','1966/07/01',43000,'天津市',default,default)
insert into employee values('E0008','刘刚', 'M','业务','职员','1994/11/01','1968/08/01',40000,'上海市',default,default)
insert into employee values('E0005','李珊珊','F','会计','职员','1990/03/20','1967/04/25',38000,'上海市',default,default)
insert into employee values('E0011','李小蓉','F','人事','职员','1994/11/01','1971/11/18',30000,'重庆市',default,default)
insert into employee values('E0012','蔡文钦','M','制造','厂长','1984/08/15','1960/07/21',50000,'上海市',default,default)
insert into employee values('E0015','张大山','M','制造','职员','1993/12/15','1968/09/23',35000,'上海市',default,default)
insert into employee values('E0007','吴铁雄','M','信息','科长','1989/10/01','1965/04/18',50000,'武汉市',default,default)
insert into employee values('E0016','方美美','F','业务','职员','1992/05/20','1966/06/23',40000,'上海市',default,default)
insert into employee values('E0004','刘中星','M','制造','经理','1984/05/01','1960/05/23',60000,'上海市',default,default)
insert into employee values('E0019','王仁华','M','信息','经理','1985/09/15','1959/03/24',60000,'上海市',default,default)
insert into employee values('E0020','陈火旺','M','业务','职员','1992/08/01','1964/05/12',43000,'天津市',default,default)
insert into employee values('E0018','林中正','M','管理','总经理','1974/10/01','1953/05/04',100000,'上海市',default,default)
go


insert into customer values('C0001','ABC股份有限公司','上海市','64284881','116011')
insert into customer values('C0002','康师傅 ','天津市','54685212','116025')
insert into customer values('C0003','联想','北京市','66544654','456131')
insert into customer values('C0004','HP','上海市','46466996','012365')
insert into customer values('C0005','华为','北京市','87434647','789258')
insert into customer values('C0006','海尔','上海市','01554689','741852')
insert into customer values('C0007','华信','上海市','36985214','132798')

go



set identity_insert sales on

go


insert into sales (order_no,cus_id,sale_id,toa_amt,order_date,ship_date,invoice_no,o_static)values('10002','C0002','E0013',22700.00,'1996/11/10','1996/11/17','I000000002','00')


insert into sales (order_no,cus_id,sale_id,toa_amt,order_date,ship_date,invoice_no,o_static)values('10003','C0003','E0014',13960.00,'1996/10/15','1996/10/17','I000000003','00')


insert into sales (order_no,cus_id,sale_id,toa_amt,order_date,ship_date,invoice_no,o_static)values('10004','C0003','E0014',33000.00,'1996/12/10','1996/12/17','I000000004','00')


insert into sales (order_no,cus_id,sale_id,toa_amt,order_date,ship_date,invoice_no,o_static)values('10001','C0001','E0008',60000.00,'1996/10/22','1996/10/24','I000000001','00')


insert into sales (order_no,cus_id,sale_id,toa_amt,order_date,ship_date,invoice_no,o_static)values('10007','C0008','E0008',20000.00,'1996/10/15','1996/10/17','I000000008','00')


go


SET IDENTITY_INSERT SALES OFF

go


INSERT INTO SALE_ITEM VALUES(10001,'P0001','S0001',5,2500.00,'1996/10/22')
INSERT INTO SALE_ITEM VALUES(10001,'P0002','S0001',3,6500.00,'1996/10/22')
INSERT INTO SALE_ITEM VALUES(10001,'P0003','S0001',2,5300.00,'1996/10/22')
INSERT INTO SALE_ITEM VALUES(10001,'P0004','S0001',2,1600.00,'1996/10/22')
INSERT INTO SALE_ITEM VALUES(10002,'P0001','S0002',3,2600.00,'1996/11/10')
INSERT INTO SALE_ITEM VALUES(10002,'P0003','S0001',1,5300.00,'1996/11/10')
INSERT INTO SALE_ITEM VALUES(10002,'P0008','S0004',2,4800.00,'1996/11/10')
INSERT INTO SALE_ITEM VALUES(10003,'P0001','S0002',4,2700.00,'1996/10/15')
INSERT INTO SALE_ITEM VALUES(10003,'P0004','S0003',2,1580.00,'1996/10/15')

go


INSERT INTO STOCK VALUES('P0001','S0001',200)
INSERT INTO STOCK VALUES('P0001','S0002',120)
INSERT INTO STOCK VALUES('P0001','S0004',150)
INSERT INTO STOCK VALUES('P0002','S0001',20)
INSERT INTO STOCK VALUES('P0002','S0002',10)
INSERT INTO STOCK VALUES('P0003','S0001',15)
INSERT INTO STOCK VALUES('P0008','S0002',120)
INSERT INTO STOCK VALUES('P0012','S0007',20)
INSERT INTO STOCK VALUES('P0013','S0003',30)
INSERT INTO STOCK VALUES('P0013','S0005',20)

go


INSERT INTO PUR_ITEM VALUES(50001,'P0001','S0001',20,2000.00,'1996/10/25')
INSERT INTO PUR_ITEM VALUES(50001,'P0008','S0002',25,5000.00,'1996/10/25')
INSERT INTO PUR_ITEM VALUES(50003,'P0001','S0001',10,2150.00,'1996/07/15')
INSERT INTO PUR_ITEM VALUES(50005,'P0008','S0004',30,4500.00,'1996/08/10')

go


INSERT INTO PRODUCT VALUES('P0001','16M DRAM')
INSERT INTO PRODUCT VALUES('P0002','14寸显示器')
INSERT INTO PRODUCT VALUES('P0003','1.2GB硬盘')
INSERT INTO PRODUCT VALUES('P0004','3.5寸软驱')
INSERT INTO PRODUCT VALUES('P0005','键盘')
INSERT INTO PRODUCT VALUES('P0006','VGA 显示卡')
INSERT INTO PRODUCT VALUES('P0007','网卡')
INSERT INTO PRODUCT VALUES('P0008','PENTIUM 100 CPU')
INSERT INTO PRODUCT VALUES('P0009','激光打印机')
INSERT INTO PRODUCT VALUES('P0010','8倍光驱')
INSERT INTO PRODUCT VALUES('P0011','计算机字典')
INSERT INTO PRODUCT VALUES('P0012','9600 bit/s MODEM')
INSERT INTO PRODUCT VALUES('P0013','pentium 主板')

go


INSERT INTO SUPPLY VALUES('S0001','联强国际')
INSERT INTO SUPPLY VALUES('S0002','宏基科技')
INSERT INTO SUPPLY VALUES('S0003','大众计算机')
INSERT INTO SUPPLY VALUES('S0005','华硕计算机')
INSERT INTO SUPPLY VALUES('S0006','英业计算机')
INSERT INTO SUPPLY VALUES('S0007','华夏电子')
INSERT INTO SUPPLY VALUES('S0004','联华电子')






---select DATEADD (YY,50,BIRTHDAY) from employee ;
---SELECT DATEPART (DW,birthday)FROM employee;




---select * from employee where contains(emp_name,'王 near 华') 


---select * from employee where contains(addr,'上海市 or 北京市') 
---select * from employee where contains(addr, 'FORMSOF (INFLECTIONAL,市)')


---select * from employee where FREETEXT (addr, '市 区' )


====================================================================================

--查询所有员工信息
select *from employee




-- 查询所有职员的姓名,地址

select emp_name,addr from employee




--查询所有女职员的详细信息
select *from employee where sex='F'



-- 查询公司目前的员工数量
select count(*) '数量' from employee



-- 查询公司中女性员工的数量
select count(*) '女性员工数量' from employee where sex='F'



-- 查询在公司工作超过20年的员工姓名,职务,工资
select  emp_name,title,salary from employee where datediff(yy,date_hired,getdate()) >20



-- 统计公司不同年份入职员工的平均工资,最高工资以及人数
select datepart(yy,date_hired) '入职年份',avg(salary)'平均工资',max(salary)'最高工资',count(*)'人数'
from employee group by datepart(yy,date_hired)


--查询所有客户的信息
select * from customer


-- 查询所有电话为6开头的客户信息
select * from customer where tel_no like '6%'



-- 按每个女性员工55岁男性员工60岁计算查询每个员工的姓名,生日,入职时间以及他(她)的退休时间 ****
select emp_name,birthday,date_hired,case when sex='F'then dateadd(yy,55,date_hired) 
when sex='M' then dateadd(yy,60,date_hired) end as '退休时间' from employee



-- 查询公司中男性员工所占比率 ****
select (select count(*) from employee where sex='M')*1.0/(select count(*) from employee )


-- 查询单价超过2000元,定货数量超过3件,总价值超过20000的商品名称 ****
select distinct prod_name from sales join sale_item on sales.order_no=sale_item.order_no
join product on product.prod_id=sale_item.prod_id where unit_price>=2000 and qty>=3 and
toa_amt>20000



--查询所有销售信息
select *from sales


--查询所有销售项目
select *from sale_item



--查询所有商品项目
select *from pur_item


--查询所有商品编号
select *from stock


--查询商品所有供应名称
select *from supply


--查询所有商品
select *from product


-- 查询上海客户的数量
select count(*) '数量' from customer where addr='上海市'


--cus_name '姓名',cus_id '编号',tel_no '电话',


-- 查询公司96年10月的销售记录
select * from sales where order_date between '10/1/1996' and '10/31/1996'

 
-- 统计公司在不同城市的客户数量
select addr,count(*) '数量'from customer group by addr




-- 按公司客户所在地来统计销售总额
select addr ,sum(toa_amt) '销售总额' from customer join sales on sales.cus_id = customer.cus_id
group by addr




-- 统计公司里所有干部的 姓名,职务,年龄并按照年龄排序
select emp_name,title,datediff(yy,birthday,getdate())'年龄'
from employee where title<>'职员' order by '年龄'




-- 查询公司从96年8月开始单价超过2000块的采购单号和供应商
select sales.order_no,sup_name from sales join sale_item on sales.order_no=sale_item.order_no
join supply on sale_item.sup_id=supply.sup_id where sales.order_date between '8/1/1996' and getdate()and
unit_price >2000




-- 查询公司目前库存商品的名称和数量
select prod_name,sum(stk_qty) from product a join stock b on a.prod_id=b.prod_id
group by prod_name


-- 查询公司采购金额超过10万的定单信息(包括定单号、厂家、商品名)
select pur_no,sup_name,prod_name from pur_item a join supply b on a.sup_id=b.sup_id
join product c on a.prod_id=c.prod_id where a.qty*a.unit_price>100000




-- 查询每个月公司的销售额
select datepart(mm,order_date),sum(toa_amt) from sales
group by datepart(mm,order_date)


-- 查询库存量前三名的产品名称
select top 3 prod_name from stock a join product b on b.prod_id=a.prod_id
group by prod_name order by sum(a.stk_qty) desc 




-- 查询库存商品销售金额在第三到第六的供货商的信息 *******
select supply.* from sale_item
join supply on sale_item.sup_id=supply.sup_id join sales on sales.order_no=sale_item.order_no
join stock on stock.prod_id=sale_item.prod_id where
toa_amt between (select sum(toa_amt)from sales) and (select top 3 sum(toa_amt) from sales)


-- 查询同一类型产品有两家以上供货商的产品编号以及供货商的数量
select product.prod_name,count(sup_name) '数量'from supply
join stock on stock.sup_id=supply.sup_id
join product on product.prod_id =stock.prod_id 
group by product.prod_name
having count(sup_name)>2




-- 统计公司各种产品的销售金额(需要区分不同的厂家)
select c.prod_id,b.sup_id,sum(toa_amt)from sale_item a
join supply b on a.sup_id=b.sup_id
join product c on c.prod_id = a.prod_id 
join sales d on d.order_no = a.order_no
group by c.prod_id, b.sup_id




-- 查询公司在96年10月的定单,计算每日定单金额,并按照定单金额排序
select datepart(dd,order_date),sum(toa_amt) from sales
where order_date between '10/1/1996' and '10/31/1996'
group by datepart(dd,order_date)
order by sum(toa_amt)


-- 查询公司中王姓员工的信息
select * from employee where emp_name like '王%'




-- 查询一笔销售记录中包含有两条明细记录的销售总帐记录
select distinct sales.* from sales join sale_item on sales.order_no=sale_item.order_no
group by sales.order_no,cus_id,sale_id,toa_amt,sales.order_date,ship_date,invoice_no,o_static
having count(*)>2 




-- 查询销售总表和销售明细表中不符合参照关系的数据(定单编号为参照字段)
select order_no from sales except 
select order_no from sale_item




-- 查询每个员工的工资以及应该交纳的个人所得税金额(40000以下不交,40000---49999 5%   50000—59999 7% 60000以上 10%)
select emp_name ,salary ,case
when salary<40000 then salary*0.0 
when salary between 40000 and 49999 then salary*0.05
when salary between 50000 and 59999 then salary*0.07
when salary >=60000 then salary*0.10
end price
from employee




-- 查询公司中所有姓名有三个字的员工信息
select * from employee where rtrim(emp_name) like '___' 




-- 生成公司销售的明细表 要求表中需要表现的信息为(定单号,销售员姓名,销售产品,供伙商名称,销售金额)
select b.order_no,emp_name,prod_name,sup_name,toa_amt
from sale_item a right join sales b on a.order_no=b.order_no
join product c on c.prod_id = a.prod_id join supply d on d.sup_id = a.sup_id
join employee e on e.emp_no=b.sale_id




-- 在采购明细表中查询 同类产品在不同时间进货差价 超过200元的产品及供货商名称
select product.prod_name,s1.sup_name,a.unit_price,a.pur_date,s2.sup_name,b.unit_price,b.pur_date
from pur_item a join pur_item b on a.pur_no=b.pur_no and a.unit_price - b.unit_price>200
join supply s1 on s1.sup_id=a.sup_id join supply s2 on s1.sup_id=s2.sup_id
join product on product.prod_id=a.prod_id where a.pur_date>b.pur_date




-- 查询在同一天进入公司的员工信息
select * from employee a join employee b on a.date_hired=b.date_hired
where a.emp_no>b.emp_no




--查询公司所有客户在公司的定货情况
select * from sales
right join customer on sales.cus_id=customer.cus_id




-- 查询由公司女业务员所接回的定单
select emp_name,order_no,cus_id,sale_id,toa_amt,order_date,ship_date,invoice_no,o_static from sales a
join employee b on a.sale_id=b.emp_no
where b.sex='F'






-- 查询公司中姓名相同的员工并按照员工编号显示员工信息 ******
select * from employee a
join employee b on a.emp_name=b.emp_name
where a.emp_no<b.emp_no
order by a.emp_no






-- 查询公司中目前业绩还没有超过2万的业务员 ********
select emp_name from sales
left join employee on emp_no=sale_id
group by emp_name
having sum(toa_amt)<20000




select * from sales




-- 查询仓库中还没有销售过的产品信息
select * from stock a
left join sale_item c on c.prod_id=a.prod_id
where c.order_no is null


select * from sale_item
select * from product
select * from stock


-- 查询公司员工的平均年龄
select avg(datediff(yy,birthday,getdate()))'平均年龄' 
from employee






-- 查询没有在公司订购产品的客户名单
select cus_name from customer a
left join sales b on a.cus_id=b.cus_id
where b.order_no is null


select * from customer
select * from sales




-- 按照供货商来统计公司的销售榜
select sup_name,isnull (sum(qty*unit_price),0) s from sale_item
right join supply on supply.sup_id=sale_item.sup_id
group by sup_name

order by s desc


====================================================================================


                                                                                                          2013.12.02 晚


版权声明:本文为博主原创文章,未经博主允许不得转载。

实现查询当前联系人应用中联系人的id和姓名

1、布局文件
  • zk673820543
  • zk673820543
  • 2016年01月14日 22:58
  • 827

Android开发获取联系人信息&根据姓名查找电话%根据电话查找姓名

最近使用到联系人查找的功能并直接拨号,查看自己之前写的代码,感觉着实麻烦。还是觉得整理成博客比较好,于是就整理了一下。 一,获取全部联系人并装到集合中 ①联系人工具类 /** * 获取联系人信息 ...
  • wangjiangjun0815
  • wangjiangjun0815
  • 2017年02月22日 16:40
  • 285

数据库原理--第四章作业(2013211534 刘凡)

3.针对上题建立的表,完成如下查询: 建表如下: (1)查询所有“佐丹奴”服装的服装ID,型号,颜色和价格。 答: (2)查询购买了“美津浓”服装的顾客姓名和电话。 答 (3)查询...
  • oppo1594345184
  • oppo1594345184
  • 2015年04月20日 10:39
  • 603

SQL sever 数据库统计查询和组合查询

商品(编号,品名,进价,库存,售价,厂商编号)  顾客(卡号,姓名,电话,积分) 厂商(编号,厂址,名称、电话) 销售(顾客卡号,商品编号,数量,日期) 根据上面基本表的信息完成下列查询。 ...
  • qq_35155205
  • qq_35155205
  • 2016年05月29日 14:46
  • 518

航空公司VIP客户查询【PAT】

航空公司VIP客户查询 典型的Hash操作
  • linsheng9731
  • linsheng9731
  • 2014年03月30日 23:21
  • 2235

Android通讯录模糊查询搜索(号码,姓名,首字母简拼,全拼),批量选取联系人

Android通讯录模糊查询搜索(号码,姓名,首字母简拼,全拼),批量选取联系人     公司最近的项目中遇到一个需求需要读取通讯录联系人,并需要支持对联系人的模糊查询及批量选取,网上找了好几个例子,...
  • cuixbo
  • cuixbo
  • 2014年12月03日 14:54
  • 2821

【转】数据库基本知识:(十)数据操作 · 查 · (三)使用子查询访问和修改数据

子查询和连接查询一样提供了使用单个查询访问多个表中的数据的方法。子查询在其他结果的基础上提供一种有效地方式来表示WHERE子句的条件。子查询是一个SELECT语句,它定义在SELECT、INSERT、...
  • hundan_520520
  • hundan_520520
  • 2017年10月26日 16:24
  • 237

android2.2根据电话号码查询联系人姓名的方法

public class AndroidTest extends Activity { private static final String TAG = "AndroidTest"; p...
  • ameyume
  • ameyume
  • 2010年12月11日 00:41
  • 9711

利用ContentProvider实现查看、添加系统联系人

一、ContentProvider是不同应用程序之间进行数据交换的标准API 开发ContentProvider的步骤: 1、定义自己的ContentProvider类继承Android提供的Co...
  • u011521890
  • u011521890
  • 2015年09月28日 21:04
  • 958

数据库查询实验60习题答案

数据库SQL语句查询
  • DT_Zhangshuo
  • DT_Zhangshuo
  • 2016年11月23日 17:09
  • 1618
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:第四章 数据查询2 (上机)
举报原因:
原因补充:

(最多只允许输入30个字)