sql_exercise

✧（CORE-E-001）综合练习▪定义CORE-F-001——CORE-F-005的综合练习：1.用sqlplus连接数据库时，为什么会出Oracle not available错误？oracle server（即通常所说的数据库）是否启动，ORACLE_SID是否正确设置。

2.找出员工的姓中（last_name）第三个字母是a的员工名字select first_name||' '||last_namefrom s_empwhere last_name like '__a%';3.找出员工名字中含有a和e的select first_namefrom s_empwhere first_name like '%a%' and first_name like '%e%';4.找出所有有提成的员工，列出名字、工资、提出，显示结果按工资从小到大，提成从小到大select first_name,salary,commission_pctfrom s_empwhere commission_pct is not nullorder by salary, commission_pct;5.42部门有哪些职位select distinct dept_id,titlefrom s_empwhere dept_id = 42;6.哪些部门不是Sales部select id,namefrom s_deptwhere name<>'Sales';7.显示工资不在1000到1550之间的员工信息：名字、工资，按工资从大到小排序。

select first_name,salaryfrom s_empwhere salary not between 1000 and 1550order by 2 desc;8.显示职位为Stock Clerk和Sales Representative，年薪在14400和17400之间的员工的信息：名字、职位、年薪。

Select first_name,title,salary*12*(1+nvl(commission_pct,0)/100) ann_salfrom s_empand salary*12*(1+nvl(commission_pct,0)/100) between 14400 and 17400;9.解释select id ，commission_pct from s_emp where commission_pct is null和select id ， commission_pct from s_emp where commission_pct = null 的输出结果。

前者输出的是有提成的员工的id 和commission_pct ，而后者输出：未选定行空值当成无穷大处理，所有空值参与的运算皆为空。

空值与空值并不相等，因为空值不能直接运算。

只能用is null比较10.select语句的输出结果为select * from s_dept; 12行select * from s_emp; 25行select * from s_region; 5行select * from s_customer; 15行……当前用户有多少张表，结果集有多少条记录。

有4张表，结果有57条记录(12+25+5+15)11.判断select first_name , dept_id from s_emp where salary > '1450'是否报错，为什么？不报错，因为’1450’会被自动转换成1450✧（CORE-E-002）综合练习▪定义CORE-F-006——CORE-F-008的综合练习：1.改变NLS_LANG的值，让select to_char(salary*12,’L99,999.99’) from s_emp输出结果的货币单位是￥和$select to_char(salary*12,'L99,999,999')from s_empselect to_char(salary*12,'$99,999,999')from s_emp2.列出每个员工的名字，工资、涨薪后工资（涨幅为8%），元为单位进行四舍五入select first_name,salary,round(salary*1.08)from s_emp3.找出谁是最高领导，将名字按大写形式显示select upper(first_name) from s_empwhere manager_id is null4.Ben的领导是谁（Ben向谁报告）。

select m.first_namefrom s_emp e,s_emp mwhere e.manager_id = m.idand e.first_name = 'Ben'5.Ben领导谁。

（谁向Ben报告）。

select e.first_namefrom s_emp e,s_emp mwhere e.manager_id = m.idand m.first_name = 'Ben'6.哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资select e.first_name,e.salary,m.first_name,m.salaryfrom s_emp e,s_emp mwhere e.manager_id = m.idand e.salary > m.salary7.哪些员工和Biri(last_name)同部门select st_name,e1.dept_idfrom s_emp e1,s_emp e2where e1.dept_id=e2.dept_idand st_name = 'Biri'and st_name <> 'Biri'8.哪些员工跟Smith(last_name)做一样职位select st_name,e1.dept_idfrom s_emp e1,s_emp e2where e1.title=e2.titleand st_name = 'Biri'and st_name <> 'Biri'9.哪些员工跟Biri(last_name)不在同一个部门select st_name,e1.dept_idfrom s_emp e1,s_emp e2where e1.dept_id = e2.dept_id(+)and st_name(+) = 'Biri'and e2.dept_id is null10.哪些员工跟Smith(last_name)做不一样的职位select e1.first_name,e2.first_namefrom s_emp e1,s_emp e2where e1.title(+) = e2.titleand st_name(+) = 'Smith'and st_name is null11.显示有提成的员工的信息：名字、提成、所在部门名称、所在地区的名称select ,,first_name,commission_pctfrom s_emp e,s_dept d,s_region rwhere e.dept_id = d.idand d.region_id = r.idand mission_pct is not null12.显示Operations部门有哪些职位select distinct e.title from s_emp e,s_dept dwhere e.dept_id = d.idand = 'Operations'13.整个公司中，最高工资和最低工资相差多少select max(salary),min(salary),max(salary)-min(salary) result from s_emp14.提成大于0的人数select count(*) from s_empwhere commission_pct > 015.显示整个公司的最高工资、最低工资、工资总和、平均工资，保留到整数位。

select max(salary),min(salary),sum(salary),round(avg(salary)) from s_emp16.整个公司有多少个领导select count(distinct manager_id)from s_emp17.列出在同一部门入职日期晚但工资高于其他同事的员工：名字、工资、入职日期select distinct e1.first_name,e1.start_date,e1.salaryfrom s_emp e1,s_emp e2where e1.dept_id = e2.dept_idand e1.start_date > e2.start_dateand e1.salary > e2.salary✧（CORE-E-003）综合练习▪定义CORE-F-009——CORE-F-011的综合练习：1.各个部门平均、最大、最小工资、人数，按照部门号升序排列select dept_id,avg(salary),max(salary),min(salary),count(*)from s_empgroup by dept_idorder by dept_id2.各个部门中工资大于1500的员工人数select dept_id,count(*)from s_empwhere salary > 1500group by dept_id3.各个部门平均工资和人数，按照部门名字升序排列select max() dname,max() rname,avg(e.salary),count(*) from s_emp e,s_dept d,s_region rwhere e.dept_id = d.idand d.region_id = r.idgroup by dept_idorder by dname,rname4.列出每个部门中有同样工资的员工的统计信息，列出他们的部门号，工资，人数select e1.dept_id,avg(e1.salary),count(*)from s_emp e1 ,s_emp e2where e1.dept_id = e2.dept_idand e1.salary = e2.salaryand e1.first_name <> e2.first_namegroup by e1.dept_id或：select dept_id,salary,count(*)from s_empgroup by dept_id,salaryhaving count(*) >= 25.该部门中工资高于1000的员工数量超过2人，列出符合条件的部门：显示部门名字、地区名称select max(),max()from s_emp e,s_dept d,s_region rwhere e.dept_id = d.idand d.region_id = r.idand salary > 1000group by dept_idhaving count(*) > 2或：select ,from s_dept d,s_region r, (select dept_id from s_empwhere salary > 1000group by dept_idhaving count(*) > 2) awhere a.dept_id = d.idand d.region_id = r.id6.哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）select first_name,salaryfrom s_empwhere salary > (select avg(salary) from s_emp)order by salary desc7.哪些员工的工资，介于32和33部门（33高些）平均工资之间select first_name,salaryfrom s_empwhere salary between (select avg(salary) from s_empwhere dept_id = 32)and (select avg(salary) from s_empwhere dept_id = 33)8.所在部门平均工资高于1500的员工名字select first_name,dept_id,salaryfrom s_empwhere dept_id in (select dept_id from s_empgroup by dept_idhaving avg(salary) > 1500)9.列出各个部门中工资最高的员工的信息：名字、部门号、工资select first_name,salary,dept_idfrom s_empwhere (dept_id,salary) in (select dept_id,max(salary) from s_emp group by dept_id)10.最高的部门平均工资值的是多少select max(avg(salary)) from s_empgroup by dept_id11.哪些部门的人数比32号部门的人数多select dept_id,count(*)from s_empgroup by dept_idhaving count(*) > (select count(*) from s_empwhere dept_id = 32)12.Ben的领导是谁（非关联子查询）select first_namefrom s_empwhere id in (select manager_idfrom s_empwhere first_name = 'Ben')13.Ben领导谁（非关联子查询）select first_namefrom s_empwhere manager_id in (select idfrom s_empwhere first_name = 'Ben')14.Ben的领导是谁（关联子查询）select first_name from s_emp owhere exists (select 1 from s_emp iwhere first_name = 'Ben'and i.manager_id = o.id)15.Ben领导谁（关联子查询）select first_name from s_emp owhere exists (select 1 from s_emp iwhere first_name = 'Ben'and i.id = o.manager_id)16.列出在同一部门共事，入职日期晚但工资高于其他同事的员工：名字、工资、入职日期（关联子查询）select first_name from s_emp owhere exists (select 1 from s_emp iwhere i.dept_id = o.dept_idand o.start_date>i.start_dateand o.salary>i.salary)17.哪些员工跟Biri(last_name)不在同一个部门（非关联子查询）select first_name from s_empwhere dept_id not in (select dept_idfrom s_empwhere last_name = 'Biri')18.哪些员工跟Biri(last_name)不在同一个部门（关联子查询）select last_name from s_emp owhere not exists (select 1 from s_emp iwhere i.dept_id = o.dept_id and st_name = 'Biri') 19.Operations部门有哪些职位（非关联子查询）select distinct title from s_empwhere dept_id in (select id from s_deptwhere name = 'Operations') 20.Operations部门有哪些职位（关联子查询）select distinct title from s_emp ewhere exists (select 1 from s_dept dwhere e.dept_id = d.idand name = 'Operations')✧CORE-E-004）综合练习▪定义CORE-F-012——CORE-F-017的综合练习：1.工资在1500至3000之间的员工，各自工作的年限（四舍五入，降序）。