sql查询练习题含答案
--( 1) 查询20 号部门的所有员工信息。
select * from emp e where e.deptno=20;--（2）查询奖金（COMM ）高于工资（SAL ）的员工信息。
select * from emp where comm>sal;--（3）查询奖金高于工资的20% 的员工信息。
select * from emp where comm>sal*0.2;--（4）查询10号部门中工种为MANAGER 和20 号部门中工种为CLERK 的员工的信息。
select * from emp ewhere （e.deptno=10 and e.job='MANAGER'）or （e.deptno=20 and e.job='CLERK'）--（5）查询所有工种不是MANAGER 和CLERK ，--且工资大于或等于2000 的员工的详细信息。
select * from empwhere job not in（'MANAGER','CLERK'） and sal>=2000;--（6）查询有奖金的员工的不同工种。
select * from emp where comm is not null;--（7）查询所有员工工资和奖金的和。
select （e.sal+nvl（m,0）） from emp e;--（8）查询没有奖金或奖金低于100 的员工信息。
select * from emp where comm is null or comm<100;--（9）查询员工工龄大于或等于10 年的员工信息。
select * from emp where (sysdate-hiredate)/365>=10;--( 10) 查询员工信息，要求以首字母大写的方式显示所有员工的姓名。
select initcap(ename) from emp;select upper(substr(ename,1,1))||lower(substr(ename,2)) from emp;--( 11) 显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，--若月份相同则按入职的年份排序。
select ename,to_char(hiredate,'yyyy') year,to_char(hiredate,'MM') monthfrom emporder by month,year;--(12) 查询在 2 月份入职的所有员工信息。
select * from emp where to_char(hiredate,'MM')='02'--(13) 查询所有员工入职以来的工作期限，用“** 年**月** 日”的形式表示。
select e.e name,floor((sysdate-e.hiredate)/365)『年' ||floor(mod((sysdate-e.hiredate),365)/30)||' 月' ||floor(mod(mod((sysdate-e.hiredate),365),30))||' 日' from emp e;--( 14) 查询从事同一种工作但不属于同一部门的员工信息。
select a.ename,a.job,a.deptno,b.ename,b.job,b.deptnofrom emp a,emp bwhere a.job=b.job and a.deptno<>b.deptno;--( 15) 查询各个部门的详细信息以及部门人数、部门平均工资。
selectd.deptno,count(e.empno),avg(e.sal),d.dname,d.loc from emp e ,dept dwhere e.deptno=d.deptnogroup by d.deptno,d.dname,d.loc--( 16) 查询10 号部门员工以及领导的信息。
select * from emp where empno in(select mgr from emp where deptno=10) or deptno=10;--( 17) 查询工资为某个部门平均工资的员工信息。
select * from empwhere sal in(select avg(sal) from emp group by deptno);--( 18) 查询工资高于本部门平均工资的员工的信息。
select * from emp e1where sal >(select avg(sal) from emp e2 where e2.deptno=e1.deptno);--( 19) 查询工资高于本部门平均工资的员工的信息及其部门的平均工资。
select e.*,a.avgsal from emp e,(select deptno,avg(sal) as avgsal from emp group by deptno) a where a.deptno=e.deptno ande.sal>a.avgsal;--( 20) 统计各个工种的人数与平均工资。
select count(*),e.job,avg(e.sal) from emp egroup by e.job--( 21) 统计每个部门中各个工种的人数与平均工资。
select deptno,job,count(empno),avg(sal) from emp e group by e.deptno,e.job--(22) 查询所有员工工资都大于1000 的部门的信息。
select * from dept where deptno in(select deptno from empwhere deptno not in(select distinct deptno from emp where sal<1000));--（23）查询所有员工工资都大于1000 的部门的信息及其员工信息。
select * from emp e join dept don d.deptno in (select deptno from emp where deptno not in(select distinct deptno from emp where sal<1000)) and d.deptno=e.deptno;--( 24) 查询所有员工工资都在900~3000 之间的部门的信息。
select * from dept where deptnonot in(select deptno from emp where sal not between 900 and 3000);--( 25) 查询所有工资都在900~3000 之间的员工所在部门的员工信息。
select * from emp a where a.deptno in(select distinct e.deptno from emp e where e.sal between 900 and 3000);--( 26) 查询每个员工的领导所在部门的信息。
select d.* from dept d where d.deptno in (select distinct e2.deptno from emp e1,emp e2 where e1.empno=e2.mgr);--( 27) 查询人数最多的部门信息。
select * from deptwhere deptno in(select deptno from (select count(*) count,deptno from emp group by deptno) where count in(select max(count)from (select count(*) count ,deptno from emp group by deptno)));--(28) 查询30 号部门中工资排序前 3 名的员工信息。
select * from(select sal from emp where deptno=30 order by sal desc) e where rownum<4--(29)查询'JONES'员工及所有其直接、间接下属员工的信息。
select e.* from emp e start with ename='JONES' connect by prior empno=mgr;---( 30)查询SCOTT 员工及其直接、间接上级员工的信息。
select e.* from emp e start with ename='SCOTT' connect by prior mgr=empno;--( 31) 以树状结构查询所有员工与领导之间的层次关系。
select substr(sys_connect_by_path(ename,'->'),3),levelfrom empstart with mgr is null connect by prior empno=mgr;--( 32)向emp 表中插入一条记录，员工号为1357，员工名字为oracle，--工资为2050元，部门号为20，入职日期为2002年5月10日。
--( 33)将各部门员工的工资修改为该员工所在部门平均工资加1000。
update emp e set sal=1000+(select avg(sal) from emp where deptno=e.deptno);--(34)查询工作等级为2级，1985 年以后入职的工作地点为DALLAS 的员工编号、--姓名和工资。
select e.ename,e.empno,e.sal from emp e,salgrade s,dept dwhere (e.sal between s.losal and s.hisal)and (s.grade=2)and to_char(e.hiredate,'yyyy')>1985and e.deptno=d.deptnoand d.loc='DALLAS';--35.部门平均薪水最高的部门编号select * from(select avg(sal) avgsal,deptnofrom emp group by deptno order by avgsal desc)where rownum=1;select deptno,avg(sal) from emp group by deptno having avg(sal)=(select max(avg(sal)) avgsalfrom emp group by deptno)--36,部门平均薪水最高的部门名称select d.* from dept d where deptno in(select deptno from emp group by deptno having avg(sal)=(select max(avg(sal)) avgsalfrom emp group by deptno)) --37.平均薪水最低的部门的部门名称select d.* from dept d where deptno in(select deptno from emp group by deptno having avg(sal)=( select min(avg(sal)) avgsal from emp group by deptno))--38.平均薪水等级最低的部门的部门名称select d.dname from dept d where d.deptno in (select a.deptno from(select e.deptno from emp e,salgrade s where (e.sal between s.losal and s.hisal) group by e.deptno order by avg(s.grade)) a where rownum=1);--39.部门经理人中，薪水最低的部门名称select dname from dept where deptno= (select deptno from(select deptno from emp where job='MANAGER' group by deptno order by min(sal)) where rownum=1)--40.比普通员工的最高薪水还要高的经理人名称select ename from emp where sal> (select max(sal) from emp where job not in ('MANAGER','PRESIDENT')) and job='MANAGER' orjob='PRESIDENT'--41.删除重复部门，但是留下一项insert into dept values(70,'RESEARCH','DALLAS') select deptno,dname,rowid from deptdelete from dept d where rowid<>(select min(rowid) from dept where dname=d.dname and d.loc=loc)--42. 更新员工工资为他的主管的工资,奖金update emp e set sal=(select sal from emp where empno=e.mgr), comm=(select comm from emp where empno=e.mgr) update emp e set (sal,comm)=(select sal,comm from emp whereempno=e.mgr) rollback; select * from emp;。
sql练习题及答案
sql练习题及答案SQL练习题及答案在学习SQL（Structured Query Language）时，练习题是非常重要的一部分。
通过练习题，我们可以巩固和应用所学的SQL知识，提高自己的实践能力。
本文将介绍几个常见的SQL练习题，并提供相应的答案，希望对大家的学习有所帮助。
1. 查询员工表中所有员工的姓名和薪水。
答案：```sqlSELECT 姓名, 薪水FROM 员工表;```2. 查询员工表中薪水大于5000的员工的姓名和薪水。
答案：```sqlSELECT 姓名, 薪水FROM 员工表WHERE 薪水 > 5000;```3. 查询员工表中职位为经理的员工的姓名和薪水。
答案：```sqlSELECT 姓名, 薪水FROM 员工表WHERE 职位 = '经理';```4. 查询员工表中薪水在4000到6000之间的员工的姓名和薪水。
答案：```sqlSELECT 姓名, 薪水FROM 员工表WHERE 薪水 BETWEEN 4000 AND 6000;```5. 查询员工表中薪水最高的员工的姓名和薪水。
答案：```sqlSELECT 姓名, 薪水FROM 员工表WHERE 薪水 = (SELECT MAX(薪水) FROM 员工表);```6. 查询员工表中没有分配部门的员工的姓名和薪水。
答案：```sqlSELECT 姓名, 薪水FROM 员工表WHERE 部门 IS NULL;```7. 查询员工表中按照薪水从高到低排列的前5名员工的姓名和薪水。
答案：```sqlSELECT 姓名, 薪水FROM 员工表ORDER BY 薪水 DESCLIMIT 5;```8. 查询员工表中每个部门的员工数量。
答案：```sqlSELECT 部门, COUNT(*) AS 员工数量FROM 员工表GROUP BY 部门;```9. 查询员工表中薪水排名在第3到第5位的员工的姓名和薪水。
SQL语句练习题(包含有多表查询)-答案
SQL语句练习题1、请从表EMP中查找工种是职员CLERK或经理MANAGER的雇员姓名、工资。
select ename,sal from emp where job='CLERK' or job='MANAGER';2、请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、工资、工作。
select ename,deptno,sal,job from emp where deptno between 10 and 30;3、请从表EMP中查找姓名以J开头所有雇员的姓名、工资、职位。
select ename,sal,job from emp where ename like 'J%';4、请从表EMP中查找工资低于2000的雇员的姓名、工作、工资，并按工资降序排列。
select ename,job,sal from emp where sal<=2000 order by sal desc;5、请从表中查询工作是CLERK的所有人的姓名、工资、部门号、部门名称以及部门地址的信息。
select ename,sal,emp.deptno,dname,loc from emp,dept where emp.deptno=dept.deptno and job=’CLERK’;6、查询表EMP中所有的工资大于等于2000的雇员姓名和他的经理的名字。
select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+) and a.sal>=2000;7、查询所有雇员的姓名、SAL与COMM之和。
select ename,sal+nvl(comm,0) “sal-and-comm” from emp;8、查询所有81年7月1日以前来的员工姓名、工资、所属部门的名字select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and hiredate<=to_date(‘1981-07-01’,’yyyy-mm-dd’);9、查询列出来公司就职时间超过24年的员工名单select ename from emp where hiredate<=add_months(sysdate,-288);10、查询于81年来公司所有员工的总收入（SAL和COMM）select sum(sal+nvl(comm,0)) from emp where to_char(hiredate,’yyyy’)=’1981’;11、查询显示每个雇员加入公司的准确时间，按××××年××月××日时分秒显示。
sql查询举例(含答案)
查询练习一、简单查询（无条件查询）：1、查询“学生档案”表中所有的记录SELECT * FORM 学生档案2、查询“学生档案”表中全体学生的姓名、学号、家庭地址SELECT 姓名, 学号, 家庭地址 FROM 学生档案二、有条件查询1、查询“成绩管理”表中语文成绩在80分以下的学生的学号。
SELECT 学号 FROM 成绩管理 WHERE 语文<802、查询“成绩管理”表中语文成绩在80分到90分之间的学生的学号，语文，数学，英语成绩。
SELECT 学号，语文，数学，英语FROM成绩管理WHERE 语文 >= 80 AND 语文<=90==（语文 BETWEEN 80 AND 90）3、查询“成绩管理”表中数学成绩不在75分到85分之间的学生的学号，语文，数学，英语成绩。
SELECT 学号，语文，数学，英语FROM 成绩管理WHERE 数学 NOT BETWEEN 75 AND 854、查询“学生档案”表中李成刚，刘艺梅，郑莉三名学生的信息。
SELECT *FROM 学生档案WHERE 姓名 IN (“李成刚”,“刘艺梅”,“郑莉”)==（姓名 =“李成刚” OR 姓名=“刘艺梅” OR 姓名=“郑莉”）5、查询“学生档案”表中所有姓张的学生的姓名、学号和性别SELECT 姓名，学号，性别 FROM学生档案WHERE 姓名 LIKE “张*”6、查询“学生档案”表中所有姓张且全名为三个汉字的学生的姓名SELECT 姓名FROM 学生档案WHERE姓名 LIKE “张？？”7、查询“学生档案”表中第二个字符为“建”字的学生的学号和姓名SELECT 学号，姓名FROM 学生档案WHERE姓名 LIKE “?建*”8、查询“学生档案”表中家庭住址为“人民路”和“育才路”的学生学号，姓名，性别和家庭住址。
SELECT 学号，姓名，性别，家庭住址FROM 学生档案WHERE家庭住址 LIKE “人民路*” OR家庭住址 LIKE “育才路*”9、查询“学生档案”表中所有团员的学生班级和姓名。
sql查询举例（含答案）
sql查询举例（含答案）查询练习⼀、简单查询（⽆条件查询）：1、查询“学⽣档案”表中所有的记录SELECT * FORM 学⽣档案2、查询“学⽣档案”表中全体学⽣的姓名、学号、家庭地址SELECT 姓名, 学号, 家庭地址 FROM 学⽣档案⼆、有条件查询1、查询“成绩管理”表中语⽂成绩在80分以下的学⽣的学号。
SELECT 学号 FROM 成绩管理 WHERE 语⽂<802、查询“成绩管理”表中语⽂成绩在80分到90分之间的学⽣的学号，语⽂，数学，英语成绩。
SELECT 学号，语⽂，数学，英语FROM成绩管理WHERE 语⽂ >= 80 AND 语⽂<=90==（语⽂ BETWEEN 80 AND 90）3、查询“成绩管理”表中数学成绩不在75分到85分之间的学⽣的学号，语⽂，数学，英语成绩。
SELECT 学号，语⽂，数学，英语FROM 成绩管理WHERE 数学 NOT BETWEEN 75 AND 854、查询“学⽣档案”表中李成刚，刘艺梅，郑莉三名学⽣的信息。
SELECT *FROM 学⽣档案WHERE 姓名 IN (“李成刚”,“刘艺梅”,“郑莉”)==（姓名 =“李成刚” OR 姓名=“刘艺梅” OR 姓名=“郑莉”）5、查询“学⽣档案”表中所有姓张的学⽣的姓名、学号和性别SELECT 姓名，学号，性别 FROM学⽣档案WHERE 姓名 LIKE “张*”6、查询“学⽣档案”表中所有姓张且全名为三个汉字的学⽣的姓名SELECT 姓名FROM 学⽣档案WHERE姓名 LIKE “张？？”7、查询“学⽣档案”表中第⼆个字符为“建”字的学⽣的学号和姓名SELECT 学号，姓名FROM 学⽣档案WHERE姓名 LIKE “?建*”8、查询“学⽣档案”表中家庭住址为“⼈民路”和“育才路”的学⽣学号，姓名，性别和家庭住址。
SELECT 学号，姓名，性别，家庭住址FROM 学⽣档案WHERE家庭住址 LIKE “⼈民路*” OR家庭住址 LIKE “育才路*”9、查询“学⽣档案”表中所有团员的学⽣班级和姓名。
sql查询语句学习测试答案
第一部分SQL查询语句的学习单表查询1、--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、客户ID和雇员ID等字段的值use eeeSELECT订购日期,订单ID,客户ID,雇员IDFROM订单WHERE订购日期BETWEEN'1996-7-1 00:00:00'AND'1996-7-15 23:59:59'2、--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。
条件是“地区等于华北”并且“联系人头衔等于销售代表”。
use eeeSELECT供应商ID,公司名称,地区,城市,电话FROM供应商WHERE地区='华北'AND联系人职务='销售代表'3、--查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。
其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津use eeeSELECT供应商ID,公司名称,地区,城市,电话FROM供应商WHERE地区IN('华东','华南')OR城市='天津'4、--查询“Northwind”示例数据库中位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值use eeeSELECT供应商ID,公司名称,地区,城市,电话FROM供应商WHERE地区IN('华东','华南')多表查询5、--查询订购日期在1996年7月1日至1996年7月15日之间的订单的订购日期、订单ID、相应订单的客户公司名称、负责订单的雇员的姓氏和名字等字段的值，并将查询结果按雇员的“姓氏”和“名字”字段的升序排列，“姓氏”和“名字”值相同的记录按“订单ID”的降序排列use eeeSELECT订购日期,订单ID,公司名称,姓氏,名字FROM订单,雇员,客户WHERE订购日期BETWEEN'1996-7-1 00:00:00'AND'1996-7-15 23:59:59' AND订单.雇员ID =雇员.雇员IDAND订单.客户ID =客户.客户IDORDER BY姓氏,名字ASC,订单ID DESC6、--查询“10248”和“10254”号订单的订单ID、运货商的公司名称、订单上所订购的产品的名称use eeeSELECT订单.订单ID,公司名称,产品名称FROM订单,运货商,产品,订单明细WHERE订单.订单ID IN('10248','10254')AND订单.订单ID =订单明细.订单IDAND订单明细.产品ID =产品.产品IDAND订单.运货商=运货商.运货商ID7、--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称、数量、单价和折扣use eeeSELECT订单.订单ID,产品名称,数量,订单明细.单价,折扣FROM订单,产品,订单明细WHERE订单.订单ID IN('10248','10254')AND订单.订单ID =订单明细.订单IDAND订单明细.产品ID =产品.产品ID8、--查询“10248”和“10254”号订单的订单ID、订单上所订购的产品的名称及其销售金额use eeeSELECT订单.订单ID,产品名称,数量*订单明细.单价*(1-折扣)AS销售金额FROM订单,产品,订单明细WHERE订单.订单ID IN('10248','10254')AND订单.订单ID =订单明细.订单IDAND订单明细.产品ID =产品.产品ID综合查询9、--查询所有运货商的公司名称和电话use eeeSELECT公司名称,电话FROM运货商10、--查询所有客户的公司名称、电话、传真、地址、联系人姓名和联系人头衔use eeeSELECT公司名称,电话,传真,地址,联系人姓名,联系人职务FROM客户11、--查询单价介于10至30元的所有产品的产品ID、产品名称和库存量use eeeSELECT产品ID,产品名称,库存量FROM产品WHERE单价BETWEEN 10 AND 3012、--查询单价大于20元的所有产品的产品名称、单价以及供应商的公司名称、电话use eeeSELECT产品名称,单价,公司名称,电话FROM产品,供应商WHERE单价> 20AND产品.供应商ID =供应商.供应商ID13、--查询上海和北京的客户在1996年订购的所有订单的订单ID、所订购的产品名称和数量use eeeSELECT订单.订单ID,产品名称,数量FROM产品,订单,订单明细,客户WHERE城市IN('上海','北京')AND YEAR(订购日期)=1996AND产品.产品ID =订单明细.产品IDAND订单.订单ID =订单明细.订单IDAND订单.客户ID =客户.客户ID14、--查询华北客户的每份订单的订单ID、产品名称和销售金额use eeeSELECT订单.订单ID,产品名称,数量*订单明细.单价*(1-折扣) AS销售金额FROM产品,订单,订单明细,客户WHERE地区='华北'AND产品.产品ID =订单明细.产品IDAND订单.订单ID =订单明细.订单IDAND订单.客户ID =客户.客户ID15、--按运货商公司名称，统计1997年由各个运货商承运的订单的总数量use eeeSELECT公司名称,COUNT(订单ID)AS订单总数量FROM订单,运货商WHERE YEAR(发货日期)= 1997AND订单.运货商=运货商.运货商IDGROUP BY公司名称16、--统计1997年上半年的每份订单上所订购的产品的总数量use eeeSELECT订单.订单ID,SUM(数量)AS总数量FROM订单,订单明细WHERE订购日期BETWEEN'1997-1-1 00:00:00'AND'1997-6-30 23:59:59' AND订单.订单ID =订单明细.订单IDGROUP BY订单.订单ID17、--统计各类产品的平均价格use eeeSELECT类别名称,SUM(单价*库存量)/SUM(库存量)AS平均价格FROM产品,类别WHERE产品.类别ID =类别.类别ID GROUP BY类别名称18、--统计各地区客户的总数量use eeeSELECT地区,COUNT(客户ID)AS客户数量FROM客户GROUP BY地区第二部分SQL查询强化测试1.找出供应商名称，所在城市use eeeSELECT公司名称,城市FROM供应商2.找出华北地区能够供应海鲜的所有供应商列表。
数据库中SQL查询语句习题含的答案
查询问题：设教学数据库Education有三个关系：学生关系S (SNO t SNAME, AGE, SEX, SDEPT)；学习关系SC (SNO, CNO, GRADE)；课程关系C (CNO, CNAME, CDEPT, TNAME) (5)检索至少选修课程号为C2和C4的学生学号；SELECT SnoFROM SC X, SC YWHERE X. Sno=Y. Sno AND X. Cno= <C2，AND Y. Cno= 'C4，；(1)检索计算机系的全体学生的学号，姓名和性别；(2)检索学习课程号为C2的学生学号与姓名；(3)检索选修课程名为“DS”的学生学号与姓名；(4)检索选修课程号为C2或C4的学生学号；(5)检索至少选修课程号为C2和C4的学生学号；(6)检索不学C2课的学生姓名和年龄；(7)检索学习全部课程的学生姓名；(8)查询所学课程包含学生S3所学课程的学生学号。
(6)检索不学C2课的学生姓名和年龄;(1)检索计算机系的全体学生的学号，姓名和性别;SELECT Sno, Sname, SexFROM SWHERE Sdept CS\(2)检索学习课程号为C2的学生学号与姓名;(3)检索选修课程名为“DS”的学生学号与姓名本查询涉及到学号、姓名和课程名三个属性，分别存放在S和C表中，但S和C表没有直接联系，必须通过SC表建立它们二者的联系。
Cf SC f S 基本思路：(1)首先在C表中找出“DS”课程的课程号Cno；(2)然后在SC表中找出Cno等于第一步给出的Cno集合中药集木元素Cno；(3)最后在S关系中选出Sno等于第二步中Sno集合中某个元素的元组，取出Sno和Sname送入结果表列。
SELECT Sno, SnameFROM SWHERE Sno IN(SELECT SnoFROM SCWHERE Cno IN(SELECT CnoFROM CWHERE Cname= 'DS'));(7)检索学习全部课程的学生姓名；在表S中找学生，要求这个学生学了全部课程。
sql练习题答案
sql练习题答案1. 查询employee表中所有员工的姓名和工资信息。
SELECT Name, SalaryFROM employee;2. 查询employee表中工资大于5000的员工的姓名和工资信息。
SELECT Name, SalaryFROM employeeWHERE Salary > 5000;3. 查询employee表中部门号为10且工资大于3000的员工的姓名和工资信息。
SELECT Name, SalaryFROM employeeWHERE DepartmentID = 10 AND Salary > 3000;4. 查询employee表中部门号为20或30的员工的姓名和工资信息。
SELECT Name, SalaryFROM employeeWHERE DepartmentID IN (20, 30);5. 查询employee表中工资在2000到5000之间的员工的姓名和工资信息。
SELECT Name, SalaryFROM employeeWHERE Salary BETWEEN 2000 AND 5000;6. 查询employee表中所有员工的姓名和入职日期信息，并按照入职日期降序排列。
SELECT Name, HireDateFROM employeeORDER BY HireDate DESC;7. 查询employee表中工资最高的员工的姓名和工资信息。
SELECT Name, MAX(Salary) AS MaxSalaryFROM employee;8. 查询employee表中每个部门的员工数量和平均工资，并按照部门编号升序排列。
SELECT DepartmentID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AvgSalaryFROM employeeGROUP BY DepartmentIDORDER BY DepartmentID ASC;9. 查询employee表中每个部门的员工数量和最高工资，并按照最高工资降序排列。
sql查询题目及答案
1、查询所有数学系学生的信息。
--select * from s where 系='数学系'2、查询李老师所教的课程号、课程名--select 课程号,课程名from c where 教师like '李%'3、查询年龄大于20岁的女同学的学号和姓名。
--select 学号,姓名from s where year(getdate())-year(出生日期)+1>20 and 性别='女'4、查询学号为‘H0301’所选修的全部课程成绩。
--select 成绩from sc where 学号= 'H0301'5、查询平均成绩都在80分以上的学生学号及平均成绩。
--select 学号,AVG(成绩) from sc group by 学号having AVG(成绩)>=806、查询至少有6人选修的课程号。
--select 课程号from sc group by 课程号having count(*)>67、查询C02号课程得最高分的学生的学号--select 学号from sc where 课程号='c02' and 成绩=(select max(成绩) from sc where 课程号='c02')8、查询学号为’J0101’的学生选修的课程号和课程名--select 课程号,课程名from c,sc where 学号='j0101' and c.课程号=sc.课程号9、‘李小波’所选修的全部课程名称。
--Select c.课程名from s,c,sc where s.学号=sc.学号and c.课程号=sc.课程号and 姓名='李小波'10、所有成绩都在70分以上的学生姓名及所在系。
--select 姓名,系from s,sc where s.学号=sc.学号group by 学号having min(成绩)>=7011、英语成绩比数学成绩好的学生--select sc2.学号from c c1,c c2,sc sc1,sc sc2 where c1.课程名='英语'--and c2.课程名='数学' and sc1.成绩>sc2.成绩and sc1.学号=sc2.学号--and c1.课程号=sc1.课程号and c2.课程号=sc2.课程号12、至少选修了两门课及以上的学生的姓名和性别select 姓名,性别from s,sc--where s.学号=sc.学号group by 学号having count(*)>=213、选修了李老师所讲课程的学生人数--select count(*) from C,sc where 教师like '李%' and c.课程号=sc.课程号group by sc.课程号14、‘操作系统’课程得最高分的学生的姓名、性别、所在系--select 姓名,性别,系from s,sc--where s.学号=sc.学号and 成绩=--(select max(成绩) from c,sc where sc.课程号=c.课程号and 课程名='操作系统')15、显示所有课程的选修情况。
SQL高级查询——50句查询（含答案）
SQL⾼级查询——50句查询（含答案）--⼀个题⽬涉及到的50个Sql语句--（下⾯表的结构以给出,⾃⼰在数据库中建⽴表.并且添加相应的数据,数据要全⾯些. 其中Student表中,SId为学⽣的ID）------------------------------------表结构----------------------------------------学⽣表tblStudent（编号StuId、姓名StuName、年龄StuAge、性别StuSex）--课程表tblCourse（课程编号CourseId、课程名称CourseName、教师编号TeaId）--成绩表tblScore（学⽣编号StuId、课程编号CourseId、成绩Score）--教师表tblTeacher（教师编号TeaId、姓名TeaName）-----------------------------------------------------------------------------------问题：--1、查询“001”课程⽐“002”课程成绩⾼的所有学⽣的学号；Select StuId From tblStudent s1Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')>(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')--2、查询平均成绩⼤于60分的同学的学号和平均成绩；Select StuId,Avg(Score) as AvgScore From tblScoreGroup By StuIdHaving Avg(Score)>60--3、查询所有同学的学号、姓名、选课数、总成绩；Select StuId,StuName,SelCourses=(Select Count(CourseId) From tblScore t1 Where t1.StuId=s1.StuId),SumScore=(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId)From tblStudent s1--4、查询姓“李”的⽼师的个数；Select Count(*) From tblTeacher Where TeaName like '李%'--5、查询没学过“叶平”⽼师课的同学的学号、姓名；Select StuId,StuName From tblStudentWhere StuId Not In(Select StuID From tblScore scInner Join tblCourse cu ON sc.CourseId=cu.CourseIdInner Join tblTeacher tc ON cu.TeaId=tc.TeaIdWhere tc.TeaName='叶平')--6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；Select StuId,StuName From tblStudent stWhere (Select Count(*) From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId='001')>0 And(Select Count(*) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId='002')>0--7、查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；Select StuId,StuName From tblStudent st Where not exists(Select CourseID From tblCourse cu Inner Join tblTeacher tc On cu.TeaID=tc.TeaIDWhere tc.TeaName='叶平' And CourseID not in(Select CourseID From tblScore Where StuID=st.StuID))--8、查询课程编号“002”的成绩⽐课程编号“001”课程低的所有同学的学号、姓名；Select StuId,StuName From tblStudent s1Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')> (Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')--9、查询所有课程成绩⼩于60分的同学的学号、姓名；Select StuId,StuName From tblStudent stWhere StuId Not IN(Select StuId From tblScore sc Where st.StuId=sc.StuId And Score>60)--10、查询没有学全所有课的同学的学号、姓名；Select StuId,StuName From tblStudent stWhere (Select Count(*) From tblScore sc Where st.StuId=sc.StuId)<(Select Count(*) From tblCourse)--11、查询⾄少有⼀门课与学号为“1001”的同学所学相同的同学的学号和姓名；------运⽤连接查询Select DistInct st.StuId,StuName From tblStudent stInner Join tblScore sc ON st.StuId=sc.StuIdWhere sc.CourseId IN (Select CourseId From tblScore Where StuId='1001')------嵌套⼦查询Select StuId,StuName From tblStudentWhere StuId In(Select Distinct StuId From tblScore Where CourseId In (Select CourseId From tblScore Where StuId='1001'))--12、查询⾄少学过学号为“1001”同学所有课程的其他同学学号和姓名；Select StuId,StuName From tblStudentWhere StuId In(Select Distinct StuId From tblScore Where CourseId Not In (Select CourseId From tblScore Where StuId='1001')--13、把“SC”表中“叶平”⽼师教的课的成绩都更改为此课程的平均成绩； (从⼦查询中获取⽗查询中的表名，这样也⾏)--创建测试表Select * Into Sc From tblScoregoUpdate Sc Set Score=(Select Avg(Score) From tblScore s1 Where s1.CourseId=sc.CourseId)Where CourseId IN(Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaID=tc.TeaID WHERE TeaName ='叶平')--14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；Select StuID,StuName From tblStudent stWhere StuId <> '1002'AndNot Exists(Select * From tblScore sc Where sc.StuId=st.StuId And CourseId Not In (Select CourseId From tblScore Where StuId='1002')) AndNot Exists(Select * From tblScore Where StuId='1002' And CourseId Not In (Select CourseId From tblScore sc Where sc.StuId=st.StuId))--15、删除学习“叶平”⽼师课的SC表记录；Delete From tblScore Where CourseId IN(Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaId=tc.TeaId Where tc.TeaName='叶平')--16、向SC表中插⼊⼀些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、'002'号课的平均成绩；Insert Into tblScore (StuId,CourseId,Score)Select StuId,'002',(Select Avg(Score) From tblScore Where CourseId='002') From tblScore WhereStuId Not In (Select StuId From tblScore Where CourseId='003')--17、按平均成绩从⾼到低显⽰所有学⽣的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显⽰：学⽣ID,,数据库,企业管理,英语,有效课程数,有效平均分Select StuId,数据库=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='数据库' And sc.StuID=st.StuId),企业管理=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='企业管理' Andsc.StuID=st.StuId),英语=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='英语' Andsc.StuID=st.StuId),有效课程数=(Select Count(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='数据库' or CourseName='企业管理' or CourseName='英语') And sc.StuID=st.StuId),有效平均分=(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='数据库' or CourseName='企业管理' or CourseName='英语') And sc.StuID=st.StuId)From tblStudent stOrder by 有效平均分 Desc--18、查询各科成绩最⾼和最低的分：以如下形式显⽰：课程ID，最⾼分，最低分Select CourseId as 课程ID, 最⾼分=(Select Max(Score) From tblScore sc Where sc.CourseId=cs.CourseId ),最低分=(Select Min(Score) From tblScore sc Where sc.CourseId=cs.CourseId )From tblCourse cs--19、按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序 (百分数后如何格式化为两位⼩数??)Select 课程ID,平均分,及格率 From(Select CourseId as 课程ID, 平均分=(Select Avg(Score) From tblScore sc Where sc.CourseId=cs.CourseId ),及格率=Convert(varchar(10),((Select Count(*) From tblScore sc Where sc.CourseId=cs.CourseId And sc.Score>=60)*10000/(Select Count(*) From tblScore sc Where sc.CourseId=cs.CourseId))/100)+'%'From tblScore cs) as tmpGroup by 课程ID,平均分,及格率Order by 平均分, Convert(float,substring(及格率,1,len(及格率)-1)) Desc--20、查询如下课程平均成绩和及格率的百分数(⽤"1⾏"显⽰): 企业管理（001），马克思（002），OO&UML （003），数据库（004）Select 课程ID=sc.CourseId,课程名称=cs.CourseName,平均成绩=Avg(Score),及格率 =Convert(varchar(10),((Select Count(Score) From tblScore Where CourseId=sc.CourseId AndScore>=60)*10000/Count(Score))/100.0)+'%'From tblScore scInner Join tblCourse cs ON sc.CourseId=cs.CourseIdWhere sc.CourseId like '00[1234]'Group By sc.CourseId,cs.CourseName--21、查询不同⽼师所教不同课程平均分从⾼到低显⽰Select 课程ID=CourseId,课程名称=CourseName,授课教师=TeaName,平均成绩=(Select Avg(Score) From tblScore WhereCourseId=cs.CourseId)From tblCourse csInner Join tblTeacher tc ON cs.TeaId=tc.TeaIdOrder by 平均成绩 Desc--22、查询如下课程成绩第 3 名到第 6 名的学⽣成绩单：企业管理（001），马克思（002），UML （003），数据库（004）格式：[学⽣ID],[学⽣姓名],企业管理,马克思,UML,数据库,平均成绩Select * From(Select Top 6 学⽣ID=StuId,学⽣姓名=StuName,企业管理=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='企业管理' And sc.StuID=st.StuId),马克思=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='马克思' Andsc.StuID=st.StuId),UML=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='UML' Andsc.StuID=st.StuId),数据库=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='数据库' Andsc.StuID=st.StuId),平均成绩=(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='数据库' or CourseName='企业管理' or CourseName='UML'or CourseName='马克思') And sc.StuID=st.StuId),排名=Row_Number() Over(Order by(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='数据库' or CourseName='企业管理' or CourseName='UML'or CourseName='马克思') And sc.StuID=st.StuId) DESC) From tblStudent stOrder by 排名) as tmpWhere 排名 between 3 And 6--23、统计列印各科成绩,各分数段⼈数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]Select 课程ID=CourseId, 课程名称=CourseName,[100-85]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 85 And 100),[85-70]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 70 And 84),[70-60]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 60 And 69),[<60]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score <60)From tblCourse cs--24、查询学⽣平均成绩及其名次Select 学号=st.StuId, 姓名=StuName,平均成绩=sc.AvgScore,名次=(Dense_Rank() Over(Order by sc.AvgScore Desc)) From tblStudent st Inner Join (Select StuId,Avg(Score) as AvgScore From tblScore Group by StuId) as sc On sc.StuId=st.StuIdOrder by 学号--25、查询各科成绩前三名的记录:(不考虑成绩并列情况)Select 学号=StuId,课程号=CourseId,分数=ScoreFrom(Select Row_Number() Over(order by CourseId,Score Desc) as i,* From tblScore) as tmp --得到⼀个临时的排名表,其中i表⽰编号Where i In(Select Top 3 i From (Select Row_Number() Over(order by CourseId,Score Desc) as i,* From tblScore) as t1 Wheret1.CourseId=tmp.CourseId)--26、查询每门课程被选修的学⽣数Select 课程ID=CourseId,选修⼈数=(Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp) From tblCourse cs--27、查询出只选修了⼀门课程的全部学⽣的学号和姓名Select 学号=StuId,姓名=StuNameFrom tblStudent stWhere (Select Count(*) From (Select Distinct CourseId From tblScore Where StuId=st.StuId) as tmp)=1--28、查询男⽣、⼥⽣⼈数Select 男⽣⼈数=(select Count(*) From tblStudent Where StuSex='男'),⼥⽣⼈数=(select Count(*) From tblStudent Where StuSex='⼥')--29、查询姓“张”的学⽣名单Select * From tblStudent Where StuName like '张%'--30、查询同名同性学⽣名单，并统计同名⼈数Select Distinct 学⽣姓名=StuName,同名⼈数=(Select Count(*) From tblStudent s2 Where s2.StuName=st.StuName) From tblStudent st Where (Select Count(*) From tblStudent s2 Where s2.StuName=st.StuName)>=2--31、1981年出⽣的学⽣名单(注：Student表中Sage列的类型是datetime)Select * From tblStudent Where Year(Sage)=1981--32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列Select 课程ID=CourseId,课程名称=CourseName,平均成绩=(Select Avg(Score) From tblScore Where CourseId=cs.CourseId)From tblCourse csOrder by 平均成绩,CourseId Desc--33、查询平均成绩⼤于85的所有学⽣的学号、姓名和平均成绩Select 学号=StuId,姓名=StuName,平均成绩=(Select Avg(Score) From tblScore Where StuId=st.StuId) From tblStudent stWhere (Select Avg(Score) From tblScore Where StuId=st.StuId)>85--34、查询课程名称为“数据库”，且分数低于60的学⽣姓名和分数Select 姓名=StuName,分数=Score From tblScore scInner Join tblStudent st On sc.StuId=st.StuIdInner Join tblCourse cs On sc.CourseId=cs.CourseIdWhere CourseName='数据库' And Score<60--35、查询所有学⽣的选课情况；Select 学号=StuId,选课数=(Select Count(*) From (Select Distinct CourseId From tblScore Where StuId=st.StuId) as tmp)From tblStudent stSelect distinct 姓名=StuName,选修课程=CourseName From tblScore scInner Join tblStudent st On sc.StuId=st.StuIdInner Join tblCourse cs On sc.CourseId=cs.CourseId--36、查询任何⼀门课程成绩在70分以上的姓名、课程名称和分数；Select 姓名=StuName,课程名称=CourseName,分数=Score From tblScore scInner Join tblStudent st On sc.StuId=st.StuIdInner Join tblCourse cs On sc.CourseId=cs.CourseIdWhere Score>=70--37、查询不及格的课程，并按课程号从⼤到⼩排列Select * From tblScore Where Score<60 order by CourseId Desc--38、查询课程编号为003且课程成绩在80分以上的学⽣的学号和姓名；Select StuId,StuName From tblStudentWhere StuId in(Select StuId From tblScore Where CourseId='003' And Score>=80)--39、求选了课程的学⽣⼈数Select 选了课程的学⽣⼈数=Count(*) From tblStudent st Where StuId IN (Select StuID From tblScore)--40、查询选修“叶平”⽼师所授课程的学⽣中，成绩最⾼的学⽣姓名及其成绩Select CourseId,CourseName,该科最⾼学⽣=(Select StuName From tblStudent Where StuId in (Select Top 1 StuID From tblScore Where CourseId=cs.CourseId Order by Score Desc)),成绩=(Select Top 1 Score From tblScore Where CourseId=cs.CourseId Order by Score Desc)From tblCourse cs Inner Join tblTeacher tc ON cs.TeaId=tc.TeaIdWhere TeaName='叶平'--41、查询各个课程及相应的选修⼈数Select 课程ID=CourseId,选修⼈数=(Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp) From tblCourse cs--42、查询不同课程成绩相同的学⽣的学号、课程号、学⽣成绩Select 学号=StuId, 课程号=CourseId, 成绩=Score From tblScore scWhere Exists (Select * From tblScore Where Score=sc.Score And StuId=sc.StuId And CourseId <>sc.CourseId)Order by 学号,成绩--43、查询每门功成绩最好的前两名Select 课程号=CourseId,第1名=(Select Top 1 StuId From tblScore Where CourseId=cs.CourseId Order by Score DESC),第2名=(Select Top 1 StuID From (Select Top 2 StuId,Score From tblScore Where CourseId=cs.CourseId Order by Score DESC) as tmp Order by Score)From tblCourse cs--44、统计每门课程的学⽣选修⼈数（超过10⼈的课程才统计）。
sql查询练习题含答案
--（1）查询20号部门的所有员工信息。
select * from emp e where =20;--（2）查询奖金（COMM）高于工资（SAL）的员工信息。
select * from emp where comm>sal;--（3）查询奖金高于工资的20%的员工信息。
select * from emp where comm>sal*;--（4）查询10号部门中工种为MANAGER和20号部门中工种为CLERK的员工的信息。
select * from emp ewhere =10 and ='MANAGER')or =20 and ='CLERK')--（5）查询所有工种不是MANAGER和CLERK，--且工资大于或等于2000的员工的详细信息。
select * from empwhere job not in('MANAGER','CLERK') and sal>=2000;--（6）查询有奖金的员工的不同工种。
select * from emp where comm is not null;--（7）查询所有员工工资和奖金的和。
select +nvl,0)) from emp e;--（8）查询没有奖金或奖金低于100的员工信息。
select * from emp where comm is null or comm<100;--（9）查询员工工龄大于或等于10年的员工信息。
select * from emp where (sysdate-hiredate)/365>=10;--（10）查询员工信息，要求以首字母大写的方式显示所有员工的姓名。
select initcap(ename) from emp;select upper(substr(ename,1,1))||lower(substr(ename,2)) from emp;--（11）显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，--若月份相同则按入职的年份排序。