SQL练习答案

(1)查询100号部门的所有员工信息select * from employees where department_id=100Select * from 表名 where 列名某项数据。

根据某项数据查询所有数据(2)查询所有职位编号为“SA_MAN”的员工的员工号、员工名和员工部门号select employee_id,first_name,last_name,department_id from employees where job_id='SA_MAN'(3)select employee_id,salary,salary*commission_pct,salary*(1+nvl(commission_pct,0)) from employees--nvl2(commission_pct,salary*(1+commission_pct),salary)(4)select * from employees where commission_pct>0.2(5)select * from employeeswhere (department_id=40 and job_id='AD_ASST')or(department_id=20 and job_id='SA_REP')(6)select * from employeeswhere job_id not in('Stock Manager','Purchasing Manager') ANDsalary>=2000(7)select job_id,job_titlefrom jobs where job_id in(select distinct job_id from employees where commission_pct is not null)(8)select * from employeeswhere commission_pct is null or salary*commission_pct<100-- where salary*nvl(commission_pct,0)<100(9)select * from employees where hire_date=last_day(hire_date)-1(10)select * from employees where (sysdate-hire_date)/365>10(11)select employee_id,initcap(first_name),initcap(last_name) fromemployees(12)select * from employees where length(first_name)=6(13)select * from employees where first_name not like '%S%'(14)select * from employees where first_name like '_M%'(15)select substr(first_name,1,3) fname,substr(last_name,1,3) lnamefrom employees;(16)select replace(first_name,'s','S') from employees(17) select first_name,last_name,hire_date from employeesorder by hire_date(18)select first_name,last_name,job_id,salary,salary*commission_pctfrom employees order by job_id desc ,salary(19)select first_name,last_name,job_id,hire_date,extract(year from hire_date) y,extract(month from hire_date) mfrom employees order by m,y(20)select * from employees where extract(month from hire_date)=2(21)select(22)select * from departmentswhere exists(select * from employees where department_id=departments.department_id) (23)select * from employees where salary>(select salary from employees where employee_id=100)(24)select e.first_name,st_name,m.first_name,st_namefrom employees e join employees mon e.manager_id=m.employee_id(25)select e.*,m.hire_datefrom employees e join employees mon e.manager_id=m.employee_id and e.hire_date<m.hire_date(26)select employees.*,departments.*from employees right join departmentson employees.employee_id=departments.manager_id(27)select employees.*,departments.*from employees RIGHT join departmentson employees.department_id=departments.department_id(28)select employees.*,departments.*from employees LEFT join departmentson employees.department_id=departments.department_id(29)selectemployee_id,first_name,last_name,department_name,job_title,salary,salary*commiss ion_pctfrom employees e JOIN departments d ON e.department_id=d.department_idJOIN jobs j ON e.job_id=j.job_id/--from employee e,departments d,jobs j--where e.departent_id=d.department_id and e.job_id=j.job_id(30)select * from jobs where job_id in(SELECT job_id from employeesgroup by job_id having min(salary)>5000 )(31) select e.*,d.*from employees e join departments don e.department_id=d.department_id and d.department_id in(select department_id from employees group by department_id having avg(salary)<6000) )(32)select * from employees where department_id in(select department_id from departments where department_name='Sales') (33)select * from employees where salary>(select avg(salary) from employees)(34)select * from employees where job_id=(select job_id from employees where employee_id=150)(35)select first_name,salary from employees where salary in( --=any select salary from employees where department_id=30)(36)select first_name,salary from employees where salary>all(select salary from employees where department_id=30)-->(select max(salary) from employees where department_id=30)(37)select department_id,count(*),avg(salary),avg( trunc( (sysdate-hire_date)/365,0) ) from employeesgroup by department_id(38)select e1.employee_id,e1.job_id,e1.department_idfrom employees e1 join employees e2on e.job_id=e.job_id and e.department_id!=e.department_idorder by e.job_id(39)select d.department_id,department_name,num,avgsalfrom departments d join(select department_id,count(*) num,avg(salary) avgsalfrom employees group by department_id) deptstaton d.department_id=deptstat.department_id(40)select job_id,min(salary) from employees group by job_id(41)select department_id,job_id,max(salary) from employeesgroup by department_id,job_id(42)select department_id,count(*),avg(salary) from employeesgroup by department_id(43)select * from employees where salary in(select avg(salary) from employees group by department_id)(44)select * from employees e where salary>(select avg(salary) from employees where department_id=e.department_id)(45)select employee_id,first_name,salary,e.department_id,avgsalfrom employees e JOIN (select department_id,avg(salary) avgsalfrom employees group by department_id) don e.department_id=d.department_id-- select employee_id,salary,department_id,avg(salary) over(partition by department_id) avgsal from employees(46) select * from employees where salary>any(select salary from employees where department_id=50)--select * from employees where salary> (-- select min(salary) from employees where department_id=50)(47)select job_id,count(*),avg(salary) from employeesgroup by job_id(48)select department_id,job_id,count(*),avg(salary) fromemployees group by department_id,job_id(49)select * from employeeswhere (salary,nvl(commission_pct,0))in(select salary,nvl(commission_pct,0) from employeeswhere department_id=10)(50)select * from employees where department_id in(select department_id from employeesgroup by department_id having count(*)>10)(51)select * from departments where department_id in(select department_id from employeesgroup by department_id having min(salary)>10000))(52)select employee_id,salary,e.department_id,department_namefrom employees e join departments don e.department_id=d.department_id and e.department_id in(select department_id from employeesgroup by department_id having min(salary)>5000)(53)select * from departments where department_id in(select department_id from employees group by department_idhaving min(salary)>=4000 and max(salary)<=8000)(54)select * from departments where department_id in(select department_id from employees group by department_idhaving count(*)>=all( select count(*) from employees group by department_id ) )with d as(select department_id,count(*) num from employeesgroup by department_id)select * from departments where department_id in(select department_id from d where num>=all(select num from d))(55)select rownum r,employee_id,first_name,last_name,salaryfrom (select employee_id,first_name,last_name,salary from employeeswhere department_id=30 order by salary desc)datasourcewhere rownum<=3(60)update employees e set salary=1000+(select avg(salary) from employees where department_id=e.department_id)(1)beginfor v_emp in (select * from employees) loopdbms_output.put_line(v_emp.employee_id||' '||v_emp.first_name||' '||v_st_name||' '||v_emp.department_id);end loop;end;(2)declarecursor c_emp is select * from employees e where salary>(select avg(salary) from employeeswhere department_id=e.department_id);beginfor v_emp in c_emp loopdbms_output.put_line(v_emp.employee_id||' '||v_emp.first_name||' '||v_st_name||' '||v_emp.department_id);end loop;end;(3)declarecursor c_emp is select e.employee_id eid,e.first_name efname,st_name elname,e.department_id edid,m.employee_id mid,m.first_name mfname,st_name mlname,m.department_id mdidfrom employees e left join employees mon e.manager_id=m.employee_id;beginfor v_emp in c_emp loopdbms_output.put_line(v_emp.eid ||' '||v_emp.efname||' '||v_emp.elname||' '||v_emp.edid ||' '||v_emp.mid ||' '||v_emp.mfname||' '||v_emp.mlname ||' '||v_emp.mdid);end loop;end;/(4)declarev_emp employees%rowtype;beginselect * into v_emp from employees where last_name='Smith';dbms_output.put_line(v_emp.employee_id||' '||v_emp.first_name||' '||v_st_name||' '||v_emp.salary||' '||v_emp.department_id);exceptionwhen no_data_found theninsert into employees values(2010,null,'Smith','smith@',null,to_date('2000-10-5','yyyy-mm-dd'),'AD_VP',7500,NULL,NULL,50);when too_many_rows thenfor v_emp in (select * from employees where last_name='Smith') loop dbms_output.put_line(v_emp.employee_id||' '||v_emp.first_name||' '||v_st_name||' '||v_emp.salary||' '||v_emp.department_id); end loop;end;(5)declarev_inc employees.salary%type;beginfor v_emp in (select * from employees) loopcasewhen v_emp.job_id in('AD_PRES','AD_VP','AD_ASST') THEN v_inc:=1000; when v_emp.job_id in('FI_MGR','FI_ACCOUNT') THEN v_inc:=800;when v_emp.job_id in('AC_MGR','AC_ACCOUNT') THEN v_inc:=700;when v_emp.job_id in('SA_MAN','SA_REP') THEN v_inc:=600;when v_emp.job_id in('PU_MAN','PU_CLERK')THEN v_inc:=500;when v_emp.job_id in('ST_MAN','ST_CLERK','SH_CLERK') THEN v_inc:=400; when v_emp.job_id in('IT_PROG','MK_MAN','MK_REP') THEN v_inc:=300; else v_inc:=200;end case;update employees set salary=salary+v_incwhere employee_id=v_emp.employee_id;end loop;end;(6)declarev_job_id jobs.job_id%type;v_maxsal number;v_minsal number;beginselect job_id into v_job_id from employees where employee_id=201; select min_salary,max_salary into v_minsal,v_maxsal from jobswhere job_id=v_job_id;if 8000 between v_minsal and v_maxsal thenupdate employees set salary=8000 where employee_id=201;end if;exceptionwhen no_data_found thendbms_output.put_line('the employee doex not exist!');end;(1)select * from employees where department_id=100(2)select employee_id,first_name,last_name,department_idfrom employees where job_id='SA_MAN'(3)select employee_id,salary,salary*commission_pct,salary*(1+nvl(commission_pct,0)) from employees--nvl2(commission_pct,salary*(1+commission_pct),salary)(4)select * from employees where commission_pct>0.2(5)select * from employeeswhere (department_id=40 and job_id='AD_ASST')or(department_id=20 and job_id='SA_REP')(6)select * from employeeswhere job_id not in('Stock Manager','Purchasing Manager') ANDsalary>=2000(7)select job_id,job_titlefrom jobs where job_id in(select distinct job_id from employees where commission_pct is not null)(8)select * from employeeswhere commission_pct is null or salary*commission_pct<100-- where salary*nvl(commission_pct,0)<100(9)select * from employees where hire_date=last_day(hire_date)-1(10)select * from employees where (sysdate-hire_date)/365>10(11)select employee_id,initcap(first_name),initcap(last_name) fromemployees(12)select * from employees where length(first_name)=6(13)select * from employees where first_name not like '%S%'(14)select * from employees where first_name like '_M%'(15)select substr(first_name,1,3) fname,substr(last_name,1,3) lnamefrom employees;(16)select replace(first_name,'s','S') from employees(17) select first_name,last_name,hire_date from employeesorder by hire_date(18)select first_name,last_name,job_id,salary,salary*commission_pctfrom employees order by job_id desc ,salary(19)select first_name,last_name,job_id,hire_date,extract(year from hire_date) y,extract(month from hire_date) mfrom employees order by m,y(20)select * from employees where extract(month from hire_date)=2(21)select(22)select * from departmentswhere exists(select * from employees where department_id=departments.department_id)(23)select * from employees where salary>(select salary from employees where employee_id=100)(24)select e.first_name,st_name,m.first_name,st_namefrom employees e join employees mon e.manager_id=m.employee_id(25)select e.*,m.hire_datefrom employees e join employees mon e.manager_id=m.employee_id and e.hire_date<m.hire_date(26)select employees.*,departments.*from employees right join departmentson employees.employee_id=departments.manager_id(27)select employees.*,departments.*from employees RIGHT join departmentson employees.department_id=departments.department_id(28)select employees.*,departments.*from employees LEFT join departmentson employees.department_id=departments.department_id(29)selectemployee_id,first_name,last_name,department_name,job_title,salary,salary*commiss ion_pctfrom employees e JOIN departments d ON e.department_id=d.department_idJOIN jobs j ON e.job_id=j.job_id/--from employee e,departments d,jobs j--where e.departent_id=d.department_id and e.job_id=j.job_id(30)select * from jobs where job_id in(SELECT job_id from employeesgroup by job_id having min(salary)>5000 )(31) select e.*,d.*from employees e join departments don e.department_id=d.department_id and d.department_id in(select department_id from employees group by department_id having avg(salary)<6000) )(32)select * from employees where department_id in(select department_id from departments where department_name='Sales') (33)select * from employees where salary>(select avg(salary) from employees)(34)select * from employees where job_id=(select job_id from employees where employee_id=150)(35)select first_name,salary from employees where salary in( --=any select salary from employees where department_id=30)(36)select first_name,salary from employees where salary>all(select salary from employees where department_id=30)-->(select max(salary) from employees where department_id=30)(37)select department_id,count(*),avg(salary),avg( trunc( (sysdate-hire_date)/365,0) ) from employeesgroup by department_id(38)select e1.employee_id,e1.job_id,e1.department_idfrom employees e1 join employees e2on e.job_id=e.job_id and e.department_id!=e.department_idorder by e.job_id(39)select d.department_id,department_name,num,avgsalfrom departments d join(select department_id,count(*) num,avg(salary) avgsalfrom employees group by department_id) deptstaton d.department_id=deptstat.department_id(40)select job_id,min(salary) from employees group by job_id(41)select department_id,job_id,max(salary) from employeesgroup by department_id,job_id(42)select department_id,count(*),avg(salary) from employeesgroup by department_id(43)select * from employees where salary in(select avg(salary) from employees group by department_id)(44)select * from employees e where salary>(select avg(salary) from employees where department_id=e.department_id)(45)select employee_id,first_name,salary,e.department_id,avgsalfrom employees e JOIN (select department_id,avg(salary) avgsalfrom employees group by department_id) don e.department_id=d.department_id-- select employee_id,salary,department_id,avg(salary) over(partition by department_id) avgsal from employees(46) select * from employees where salary>any(select salary from employees where department_id=50)--select * from employees where salary> (-- select min(salary) from employees where department_id=50)(47)select job_id,count(*),avg(salary) from employeesgroup by job_id(48)select department_id,job_id,count(*),avg(salary) fromemployees group by department_id,job_id(49)select * from employeeswhere (salary,nvl(commission_pct,0))in(select salary,nvl(commission_pct,0) from employeeswhere department_id=10)(50)select * from employees where department_id in(select department_id from employeesgroup by department_id having count(*)>10)(51)select * from departments where department_id in(select department_id from employeesgroup by department_id having min(salary)>10000))(52)select employee_id,salary,e.department_id,department_namefrom employees e join departments don e.department_id=d.department_id and e.department_id in(select department_id from employeesgroup by department_id having min(salary)>5000)(53)select * from departments where department_id in(select department_id from employees group by department_idhaving min(salary)>=4000 and max(salary)<=8000)(54)select * from departments where department_id in(select department_id from employees group by department_idhaving count(*)>=all( select count(*) from employees group by department_id ) )with d as(select department_id,count(*) num from employeesgroup by department_id)select * from departments where department_id in(select department_id from d where num>=all(select num from d))(55)select rownum r,employee_id,first_name,last_name,salaryfrom (select employee_id,first_name,last_name,salary from employeeswhere department_id=30 order by salary desc)datasourcewhere rownum<=3(60)update employees e set salary=1000+(select avg(salary) from employees where department_id=e.department_id)。