oracle 查詢測試樣題

標籤：

1.select count(*) from employees where last_name like '_A%';key:02.select count(*)from employeeswhere to_char(hire_date,'YYYY')=1998;select count(*)from employeeswhere hire_date like '%98';key:23select to_char(hire_date,'YYYY') from employees;3.select job_title, max_salary-min_salary as "SAL_DIEF"from jobsorder by max_salary-min_salary desc;select job_title,(max_salary-min_salary) as "SAL_DIEF"from jobsorder by 2 desc;19行記錄4.select count(*)from employeeswhere (salary>12000 or salary<1000)and job_id !='ST_MAN' and job_id!='SH_CLERK' ;number:6================select count(*)from employeeswhere salary not between 1000 and 12000and job_id not in('ST_MAN','SH_CLERK') ;select salaryfrom employeeswhere salary<1000 and salary>12000;--當它判斷1000為假時就不判斷後面的大於12000了.select job_id from employees;select * from employeeswhere job_id in('ST_MAN','SH_CLERK');--工作崗位名稱要加單引號5.select count(*)from employeeswhere to_char(hire_date,'YYYY')=1999and to_char(hire_date,'mm')=02;key:3select count(*)from employeeswhere to_char(hire_date,'YYYY-MM')='1999-02';6.select last_name,salary,decode(trunc(salary/1500),0,'A',   1,'B',                   2,'C',     'D') Gradefrom employeeswhere last_name like'%s';7.select d.department_id,d.department_name,l.cityfrom departments d,locations lwhere d.department_id in(10,40,90)and d.location_id=l.location_id;8.select l.city,c.country_name,r.region_namefrom locations l,regions r,countries cwhere l.location_id=1000 and l.country_id=c.country_idand c.region_id=r.region_id;9.select  m.last_name "MAN_NAME",nvl(e.last_name,'NO EMPLOYEES') "EMP_NAME"from employees m,employees ewhere m.department_id=100and m.employee_id=e.manager_id(+);10行記錄select  m.last_name MAN_NAME,nvl(e.last_name,'NO EMPLOYEES') EMP_NAMEfrom employees m,employees ewhere m.department_id=100and m.employee_id=e.manager_id(+);10select department_id,count(*) NUMfrom employeeswhere salary>8000group by department_id;9行記錄11select department_id,count(*) NUMfrom employeeswhere salary>5000group by department_idhaving count(*)>3;3行記錄12select last_name,salaryfrom employeeswhere salary>(select salary from employees where employee_id=110)and department_id=100;2行記錄13select count(*) NUMfrom employeeswhere commission_pct<all(select distinct commission_pct from employees where salary>12000and commission_pct is not null);24行記錄

oracle 查詢測試樣題