sql inner join 连接查询

--求姓名长度至少是三个汉字(使用字符串函数)--且倒数第二个汉字必须是“吉”的员工姓名所销售的商品名称；use test000goselect 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,SELLwhere 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 Employeesinner join SELL on SELL.ENO = Employees.ENOgroup 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.GNAMEgo--在第6个查询的基础上实现：查询没有销售过的商品；select GNO,GOODS.GNAME from GOODS where GOODS.GNO not in( select GNO from SELL);go--查询1301号员工所售商品是由谁来进货的,输出姓名和部门名；select Employees.ENAME,Employees.Edept from Employeesinner join Purchase on Purchase.Eno = Employees.ENOinner join SELL on SELL.GNO=Purchase.Gnowhere SELL.ENO = '1301' group by Employees.ENAME,Employees.Edeptgo--查询销售了“笔记本电脑”的员工姓名；select DISTINCT Employees.ENAME from Employees inner join SELL on SELL.ENO = Employees.ENOinner join GOODS on GOODS.GNO = SELL.GNOwhere 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