3,'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 )
-- |