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 the Jobsorder by 2 desc;19 Row Records 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 + 12000and job_id not in (' St_man ', ' Sh_clerk '); Select Salaryfrom EMPLOYEESW Here salary<1000 and salary>12000;--when it determines that 1000 is false, it does not judge that the back is greater than 12000. Select job_id from Employees;select * from Employeeswhere job_id in (' St_man ', ' Sh_clerk ');--Job title add single quote 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 rows record 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 Line record 11select department_id,count (*) Numfrom employeeswhere Salary>5000grouP by department_idhaving Count (*) >3;3 line record 12select last_name,salaryfrom employeeswhere salary> (select salary from Employees where employee_id=110) and Department_id=100;2 row record 13select count (*) Numfrom employeeswhere commission_pct< All (select distinct commission_pct from employees where Salary>12000and commission_pct are not null); 24 Rows of records
Oracle Query Test Sample Questions