oralce 经典习题系列-查询

oralce 经典习题系列

数据库数据类型

The problem

1在已经创建的employee表中进行如下操作：
（1）计算所有女员工（‘F’）的工龄。
（2）使用ROWNUM查询从第3条记录开始的记录。
（3）查询销售人员（SALSEMAN）的最低工资。
（4）查询名字以字母N或者S结尾的记录。
（5）查询在BeiJing工作的员工的姓名和职务。
（6）使用左连接方式查询employee和dept表。
（7）查询所有2001～2005年入职的员工的信息，查询部门编号为20和30的员工信息并使用UNION合并两个查询结果。
（8）使用LIKE查询员工姓名中包含字母a的记录。
（9）使用REGEXP_LIKE函数查询员工姓名中包含T、C或者M三个字母中任意一个的记录。

The premise condition

创建数据库并插入数据代码

--创建一个部门表
create table dept(
  d_no number(11) GENERATED BY DEFAULT AS IDENTITY primary key,
  d_name varchar(50) not null,
  d_location varchar(100) 
);

--创建一个雇员表
create table employee (
  e_no number(11) primary key,
  e_name varchar2(100) not null,
  e_gender varchar2(1) not null,
  dept_no number(11) not null,
  e_job varchar(100) not null,
  e_salary number(11) not null,
  hireDate date,
  CONSTRAINT fk_dept foreign key(dept_no) REFERENCES dept(d_no) 
);
--插入部门数据
insert into dept values(10,'Accounting','ShanHai');
insert into dept values(20,'Research','BeiJing');
insert into dept values(30,'SALES','ShenZhen');
insert into dept values(40,'OPERATIONS','Fujian');
--插入雇员信息
insert into employee values(1001,'SMITH','m',20,'CLERK',800,'2005-11-12');
insert into employee values(1002,'ALLEN','f',30,'SALESMAN',1600,'2003-05-12');
insert into employee values(1003,'WARD','f',30,'SALESMAN',1250,'2003-05-12');
insert into employee values(1004,'JONES','m',20,'MANAGER',2975,'1998-05-18');
insert into employee values(1005,'MARTIN','m',30,'SALESMAN',1250,'2001-06-12');
insert into employee values(1006,'BLAKE','f',30,'MANAGER',2850,'1997-02-15');
insert into employee values(1007,'CLARK','m',30,'MANAGER',2450,'2002-09-12');
insert into employee values(1008,'SCOTT','m',20,'ANALYST',3000,'2003-05-12');
insert into employee values(1009,'KING','f',10,'PRESIDENT',5000,'1995-01-01');
insert into employee values(1010,'TURNER','f',30,'SALESMAN',1500,'1997-10-12');
insert into employee values(1011,'ADAMS','m',20,'CLERK',1100,'1999-10-05');
insert into employee values(1012,'JAMES','m',30,'CLERK',950,'2008-06-15');

The answer

1.1 实现代码select to_number(to_char(sysdate,'yyyy'))-to_number(to_char(hireDate,'yyyy')) as years,e_no,e_name from employee where e_gender ='f';这里暂时按整体年份算，要精确也可以，转化为天数计算。
1.2 代码实现select * from (select rownum as no,a.* from employee a) where no>3 ;
1.3 代码实现select min(e_salary) from employee where e_job = 'SALESMAN';
1.4 代码实现select * from employee where substr(e_name,1,1)='N' or substr(reverse(e_name),1,1)='S' ;
1.5 代码实现select a.e_name ,a.e_job from employee a inner join dept b on a.DEPT_NO =b.D_NO where b.D_LOCATION ='BeiJing';;
1.6 代码实现select a.*,b.* from employee a left join dept b on a.DEPT_NO =b.D_NO;
1.7 代码实现select * from employee where to_char(hiredate,'yyyy') BETWEEN '2001' and '2005' union select * from employee where dept_no in (20,30);
1.8 代码实现select * from employee where e_name like '%A%';
1.9 代码实现select * from employee where regexp_like(e_name,'[TCM]');