学步园

use test000
----select * from Employees
----Insert Into Employees
---- Values('1304','zhangsan',24,'1','销售部','05926188888','集美大学工商管理学院');
-- --查询与冯晓丹同一部门的员工姓名和地址；
 select ename,EADDRESS  from employees where edept in(select edept from Employees where ENAME='冯晓丹' );
----查询比冯晓丹年龄大的员工姓名和部门；
select ename,edept from Employees where EAGE >(select EAGE from Employees where ENAME='冯晓丹');
----查询号员工所销售的商品是由谁来进货的，输出姓名和年龄；
--select ename,eage from employees where eno in
--(select eno from Purchase where gno in
--(select gno from SELL  where ENO ='1301')
--);
----查询不超过财务部所有员工的年龄的非财务部员工姓名和年龄；
--select ename,eage from employees where eage <
--(select MIN(eage) from Employees where Edept='财务部');
----查询销售了“笔记本电脑”的员工姓名和部门（使用带EXISTS的嵌套查询）
--select ename,edept from employees where exists
--(
--	select * from sell where sell.eno=employees.eno and gno=
--	(
--		select gno from GOODS  where gname='笔记本电脑'
--	)
--);
----查询比数码相机进价高的商品名称及当年销售情况；
--select gname,SELL.* from goods,SELL where ginprice >
--(select ginprice from goods where GNAME='数码相机');
----查询没有采购过“MP3播放器”这种商品的员工姓名；
--select ename from Employees  where eno not in
--(
--	select eno from purchase where gno=
--	(
--		select gno from GOODS where GNAME ='MP3播放器'
--	)
--);
----查询今年与供应商“C公司”有过联系的员工姓名以及该员工今年所采购的商品名称；
--select gname,ename from goods,Employees where gno in
--(
--	select gno from purchase where eno in
--	(
--		select Eno  from Purchase where spname='C公司'
--	)
--) and ENO in (select Eno  from Purchase where spname='C公司');
--查询总销售量最少的销售部员工姓名和年龄（没有任何销售记录的员工销售数量为）；
--select ename,eage from Employees where Edept='销售部' and  ENO =
--(
--	select top 1 eno from SELL group by ENO order by SUM(SSUM) 
--);