sql inner join 连接查询

--求姓名长度至少是三个汉字(使用字符串函数)
--且倒数第二个汉字必须是“吉”的员工姓名所销售的商品名称；
use test000
go
select Employees.ENAME,GOODS.GNAME from Employees,SELL,GOODS where Employees.ENO = SELL.ENO 
and GOODS.GNO=SELL.GNO and len(Employees.ENAME)>=3  and substring(Employees.ENAME,len(Employees.ENAME)-1,1)='吉';
go
--查询不姓“陈”的员工销售的商品信息以及销售员工的姓名、销售数量和销售日期，按商品零售价排降序；
select Employees.ENAME,GOODS.GNO,GOODS.GNAME,SELL.SSUM,SELL.STIME from GOODS,Employees,SELL
where Employees.ENO = SELL.ENO and GOODS.GNO=SELL.GNO and Employees.EADDRESS not like '陈%';
go
--查询与冯晓丹同一部门的员工信息；
select * from Employees where Edept in 
(
	select Edept from Employees where ENAME = '冯晓丹' group by Edept
)

go
--求年龄大于’ 冯晓丹’ 的所有员工的姓名、部门和年龄；
select ENAME,Edept,EAGE from Employees where EAGE > 
(
	select EAGE from Employees where ENAME = '冯晓丹'
) 


go
--查询销总共售出20件商品以上的员工的姓名；
select DISTINCT Employees.ENAME from Employees,SELL where Employees.ENO = SELL.ENO and SELL.SSUM>20 
select Employees.ENAME,SUM(SELL.SSUM) as '销总共售出' from Employees
inner join SELL on SELL.ENO = Employees.ENO
group by SELL.ENO,Employees.ENAME 

go
--查询所有商品及其销售情况（也显示没有销售记录的商品信息）；
Select GOODS.GNO,GOODS.GNAME,SUM(SELL.SSUM) as '销售总数' from Goods 
Left Join SELL on Goods.GNO = SELL.GNO  group by Goods.GNO,GOODS.GNAME
go
--在第6个查询的基础上实现：查询没有销售过的商品；
select GNO,GOODS.GNAME from GOODS where GOODS.GNO not in( select GNO from SELL);
go
--查询1301号员工所售商品是由谁来进货的,输出姓名和部门名；
select Employees.ENAME,Employees.Edept from Employees
inner join Purchase on Purchase.Eno = Employees.ENO
inner join SELL on SELL.GNO=Purchase.Gno
where SELL.ENO = '1301' group by Employees.ENAME,Employees.Edept


go
--查询销售了“笔记本电脑”的员工姓名；
select DISTINCT Employees.ENAME from Employees 
inner join SELL on SELL.ENO = Employees.ENO
inner join GOODS on GOODS.GNO = SELL.GNO
where GOODS.GNAME = '笔记本电脑'


go
--查询赵飞燕所进货物的总金额，按进货价计算
select Purchase.Gno,Purchase.Pnum*GOODS.GINPRICE from Purchase 
inner join GOODS on Purchase.Gno=GOODS.GNO 
inner join Employees on Employees.ENO = Purchase.Eno where Employees.ENAME = '赵飞燕'
go
--查询冯晓丹当年当月的销售总金额，按零售价*数量计算，getdate（）函数取当前时间
select Employees.ENAME,SUM(GOODS.GOUTPRICE * SELL.SSUM) as '销售总金额' from Employees 
inner join SELL on Employees.ENO=SELL.ENO inner join GOODS on GOODS.GNO=SELL.GNO 
where Employees.ENAME = '冯晓丹' and YEAR(SELL.STIME)=YEAR(GETDATE()) and MONTH(SELL.STIME)=MONTH(GETDATE())
group by Employees.ENAME