java面试题经典20例【第七季_常瑞鹏】

以下各个题目将用到上图的4个表，其关联关系如图所示。这个四个表中字段的具体情况参看如下内容：

EMPLOYEES(employee_id number(6) not null,first_name varchar2(20),last_name varchar2(25) not null,email varchar2(25) not null,phone_number varchar2(20),hire_date date not null,job_id varchar2(10) not null,salary number(8,2),commission_pct number(2,2),manager_id number(6),department_id number(4))

 

DEPT(department_id number(4) not null,department_name varchar2(30) not null,manager_id number(6),location_id number(4))

 

locations(location_id number(4) not null,city varchar2(20))

 

job_grades(grade_level varchar2(3),lowest_sal number,highest_sal number)

1.    在下面的SQL语句中有4个错误，请挑选出来。

select employee_id,last_name

sal×12 annual salary from employees;

1last_name和sal之间没有逗号

2表中没有sal列，而是salary

3不应该是×来完成运算，而应该使用*号（+-*/）

4为salary列起别名，应该使用”annual salary”，因为别名含有特殊字符空格

2.    DEPT(department_id,department_name,manager_id,location_id)。显示DEPT表的结构。选择表中的所有数据。

desc dept;

select * from dept;

3. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示EMPLOYEES 表的结构。创建一个查询，显示每个雇员的last name, job code, hire date, 和employee 号，employee号显示在第一列，给 HIRE_DATE列指定一个别名 STARTDATE。

desc employees;

select employee_id,last_name,job_id,hire_date startdate from employees;

4. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。创建一个查询从EMPLOYEES表中显示唯一的工作代码（job_id）。

select distinct job_id from employees;

5. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。进行查询，查询employee_id,last_name,job_id和hire_date，但是要求显示出来的列名为Emp#,Employee,Job和Hire Date。

select employee_id “Emp#”,last_name Employee,job_id Job,hire_date“Hire Date”;

6. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示last_name，用job_ID连接，用逗号和空格分开，用Employee and Title作为列名。

SELECT last_name||', '||job_id "Employee and Title"

FROM    employees;

7. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。创建一个查询从EMPLOYEES表中显示所有数据。用逗号分开列，命名列THE_OUTPUT。

SELECT  employee_id || ',' || first_name || ',' || last_name || ',' || email || ',' || phone_number || ','|| job_ id || ',' || manager_id || ',' || hire_date || ',' || salary || ',' || commission_pct || ',' || department_id  THE_OUTPUT FROM employees;

8. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。创建一个查询，显示收入超过$12,000 的雇员的名字和薪水。

SELECT last_name, salary FROM employees WHERE salary > 12000;

9. EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。创建一个查询，显示雇员号为176 的雇员的名字和部门号

SELECT last_name, department_id FROM employees WHERE employee_id = 176;

10.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示所有薪水不在5000 和 12000 之间的雇员的名字和薪水

SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000;

11.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示受雇日期在1998年2月20日 和1998年5月1日 之间的雇员的名字、岗位和受雇日期。按受雇日期顺序排序查询结果。

SELECT last_name, job_id, hire_date FROM employees WHERE hire_date BETWEEN '20-Feb-1998' AND '01-May-1998' ORDER BY hire_date;

12.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示所有在部门20 和 50 中的雇员的名字和部门号，并以名字按字母顺序排序。

SELECT last_name, department_id FROM employees WHERE department_id IN (20, 50) ORDER BY last_name;

13.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。列出收入在$5,000 和 $12,000 之间，并且在部门 20 或 50 工作的雇员的名字和薪水。将列标题分别显示为 Employee 和 Monthly Salary。

SELECT last_name "Employee", salary "Monthly Salary" FROM employees WHERE salary BETWEEN 5000 AND 12000 AND department_id IN (20, 50);

14.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示每一个在1994 年受雇的雇员的名字和受雇日期

SELECT last_name, hire_date FROM employees WHERE hire_date LIKE '%94';

15.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示所有没有主管经理的雇员的名字和工作岗位。

SELECT last_name, job_id FROM employees WHERE manager_id IS NULL;

16.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示所有有佣金的雇员的名字、薪水和佣金。以薪水和佣金的降序排序数据

SELECT last_name, salary, commission_pct FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC, commission_pct DESC;

17.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示所有名字中第三个字母是a 的雇员的名字。

SELECT last_name FROM employees WHERE last_name LIKE '__a%';

18.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示所有名字中有一个a 和一个 e 的雇员的名字。

SELECT last_name FROM employees WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

19.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示所有工作是销售代表或者普通职员，并且薪水不等于$2,500、$3,500 或 $7,000 的雇员的名字、工作和薪水。

SELECT last_name, job_id, salary FROM employees WHERE job_id IN ('SA_REP', 'ST_CLERK') AND salary NOT IN (2500, 3500, 7000);

20.            EMPLOYEES(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id)。显示所有佣金总计为20% 的雇员的名字、薪水和佣金。

SELECT last_name "Employee", salary "Monthly Salary", commission_pct FROM employees WHERE commission_pct = .20;

 

 

------------------------------------------------------------------