Oracle query test sample

1. selectcount (*) fromemployeeswherelast_namelike_A %; key: 02. selectcount (*) fromemployeeswhereto_char (hire_date, YYYY) 1998; selectcount (*) Success % 98; key: 23selectto_char (hire_date, YYYY) fromemployees; 3.se

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: 23 select to_char (hire_date, YYYY) from employees; 3.se

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: 23 select 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 rows record 4. select count (*) from employeeswhere (salary> 12000 or salary <1000) and job_id! = 'St _ MAN 'and job_id! = 'Sh _ cler'; number: 6 ===================== select count (*) from employeeswhere salary not between 1000 and 12000and job_id not in ('st _ MAN ', 'sh _ cler'); select salaryfrom employeeswhere salary <1000 and salary> 12000; -- when it determines that 1000 is false, it does not determine that the value later than 12000 is greater. select job_id from employees; select * from employeeswhere job_id in ('st _ MAN ', 'sh _ cler'); -- the job name must be enclosed in single quotes. select count (*) from employeeswhere to_char (hire_date, 'yyyy') = maid (hire_date, 'mm') = 02; key: 3 select count (*) from employeeswhere to_char (hire_date, 'yyyy-mm') = '2017-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 ployees') "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 ployees') EMP_NAMEfrom EMPLOYEES m, employees ewhere m. department_id = 100and m. employee_id = e. manager_id (+); 10 select department_id, count (*) NUMfrom employeeswhere salary> 8000 group by department_id; 9 rows record 11 select department_id, count (*) NUMfrom employeeswhere salary> 5000 group by department_idhaving count (*)> 3; 3 rows record 12 select last_name, salaryfrom employeeswhere salary> (select salary from employees where employee_id = 110) and department_id = 100; 2 rows record 13 select count (*) NUMfrom employeeswhere commission_pct12000and commission_pct is not null); 24 rows record