先创建表s_emp:
已知有以下表,数据如图s_emp::
CREATE TABLE s_emp( id NUMBER(7) CONSTRAINT s_emp_id_nn NOT NULL, last_name VARCHAR2(25) CONSTRAINT s_emp_last_name_nn NOT NULL, first_name VARCHAR2(25), userid VARCHAR2(8), start_date DATE, comments VARCHAR2(255), manager_id NUMBER(7), title VARCHAR2(25), dept_id NUMBER(7), salary NUMBER(11, 2), commission_pct NUMBER(4, 2), CONSTRAINT s_emp_id_pk PRIMARY KEY (id), CONSTRAINT s_emp_userid_uk UNIQUE (userid), CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20)));
1)查询员工的工资和last_name，对工资进行升序排列
select id,last_name from s_emp order by salary asc;2)查询员工的工资和last_name，对last_name进行降序排列
select salary,last_name from s_emp order by last_name desc;3)查询员工的工资和last_name，先对salary进行降序排列，再对last_name进行升序排列
select salary,last_name from s_emp order by salary desc,last_name asc;4)请举例说明空值的排序规则 升序排列，默认情况下，null值排后⾯ 降序排序，默认情况下，null值排前⾯
5)查询41号部门员工的信息？
select * from s_emp where dept_id=41;6)查询工资大于1000的员工的信息？
select * from s_emp where salary > 1000;7)查询工资大于1000小于1500员工的信息，使用两种方式来做？
select * from s_emp where salary >1000 and salary <1500; select * from s_emp where salary between 1000 and 1500;8）查询41或者42部门员工的信息，使用两种方式来做？
select * from s_emp where dept_id=41 or dept_id=42; select * from s_emp where dept_id in(41,42);9）查询last_name包含n的员工的信息？
select * from s_emp where last_name like '%n%';10）查询last_name包含n或者N的员工的信息？
select * from s_emp where last_name like '%n%' or last_name like '%N%';11)查询last_name第二个字母为g的员工的信息？
select * from s_emp where last_name like '_g%';12)查询last_name最后一个字母为o的员工的信息？
select * from s_emp where last_name like '%o';13)查询last_name以N开头的员工的信息？
select * from s_emp where last_name like 'N%';14)先插入这条数据 insert into s_emp(id,last_name) values(999,'_briup'); commit;
然后查询以_开头的员工的信息
select * from s_emp where last_name like '/_%' escape '/';15)找出部门41中所有经理和部门43中的所有办事员的详细资料 经理职称为:Warehouse Manager 办事员的职称为:Stock Clerk
select * from s_emp where (title='Warehouse Manager' and dept_id =41 ) or (title='Stock Clerk' and dept_id=43);16)找出部门41中所有经理、部门43中所有办事员，既不是经理又不是办事员但其薪金>=2000的所有雇员的详细资料
select * from s_emp where (title='Warehouse Manager' and dept_id =41 ) or (title='Stock Clerk' and dept_id=43) or ((title!='Warehouse Manager' and title!='Stock Clerk')and salary>=2000);17)显示不带有'S'的雇员姓名
select '姓名:' || last_name || '-' || first_name name from s_emp where last_name not like '%S%' and first_name not like '%S%';18)显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select '姓名:' || last_name || '-' || first_name name from s_emp order by start_date asc;19)显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而职位相同时按薪金升序
select '姓名:' || last_name || '-' || first_name name,title,salary from s_emp order by title desc,salary asc;20)查询12个月的年薪大于18000的员工的信息
select * from s_emp where salary*12>18000;21)查询13个月的年薪在10000到14000之间的员工信息
select * from s_emp where salary*13 between 10000 and 14000;22)查询41号部门工资大于1400的员工信息
select * from s_emp where dept_id=41 and salary>1400;23）查询除41,42号部门12个月的年薪大于17000员工的信息
select * from s_emp where (dept_id!=41 and dept_id!=42) and salary*12>17000;
