数据查询

(1) 基本数据查询

a) 分别查询表employees、regions、countries和locations表中的所有行和列

例：Select * from employees;

b) 创建一个查询，显示每个雇员的 last name, job_id, hire_date, 和 employee_id，employee_id显示在第一列， 给 HIRE_DATE 列指定一个别名 STARTDATE 。

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

c) 创建一个查询从EMPLOYEES表中显示唯一的工作代码(job_id)

Select distinct job_id from employees;

d) 创建一个查询，显示收入超过 $12,000 的雇员的名字和薪水。

Select first_name,last_name,salary from employees where salary>12000;

e) 创建一个查询，显示雇员号为 176 的雇员的名字和部门号

Select first_name,last_name,department_id from employees

where employee_id=176;

f) 显示受雇日期在 1998年2月20日 和 1998年5月1日 之间的雇员的名字、岗位(job_id)和受雇日期(hire_date)。按受雇日期顺序排序查询结果。

Select first_name,last_name,job_id,hire_date

From employees

Where hire_date between ’20-2月-98’ and ‘1-5月-98’

Order by hire_date (desc);

g) 显示所有在部门 为20 和 50 的雇员的名字和部门号，并以名字按字母顺序排序。

Select first_name,last_name,department_id

From employees

Where department_id in(20,30)

Order by first_name;

h) 显示每一个在 1994 年受雇的雇员的名字和受雇日期

Select first_name,last_name,hire_date

From employees

Where hire_date like ‘%94’;

i) 对每一个雇员，显示 employee_id、last_name、salary 和 salary 增加 15%，并且表示成整数，列标签显示为 New Salary。

Select first_name,last_name, salary,round(salary*1.15,0) “New Salary”

From employees;

j) 显示所有没有主管经理的雇员的名字和工作岗位。

Select first_name,last_name, job_id

From employees

Where manager_id is null;

k) 显示所有名字中第三个字母是 a 的雇员的名字

Select first_name,last_name

From employees

Where first_name like ‘__a%’;

l) 显示所有名字中有一个 a 和一个 e 的雇员的名字。

Select first_name,last_name

From employees

Where first_name like ‘%a%’ and first_name like ‘%e%’;

m) 写一个查询显示当前日期，列标签显示为 Date。

Select sysdate “date” from dual;

n) 查询表employees的department_id列，department_id为10时显示为“计算机工程系”，为20时显示为“汽车工程系”，其它显示为“其它”，分别用case和decode实现。

Select department_id,case department_id when 10 then '计算机工程系'

When 20 then '汽车工程系'

Else '其它'

End

From employees;

Select department_id,

decode(department_id,10,'计算机工程系',20,'汽车工程系','其它')

From employees;

o) 查询表employees的salary列,如果salary小于3000显示为“低工资”，salary小于5000显示为“中”，其余显示为“高工资”

Select salary,case when salary<3000 then ‘低工资’

When salary<5000 then ‘中’

Else ‘高工资’ end from employees;

(2) 连接查询和子查询

a) 写一个查询显示所有雇员的 last_name、department_id、and department_name

Select e.last_name,d.department_id,d.department_name

From employees e,departments d

Where e.department_id=d.department_id;

b) 写一个查询显示所有有佣金(commission_pcct不为空)的雇员的 last_name、department_name、location_ID 和城市。

Select e.last_name,d.department_name,d.location_id,l.city

From employees e,departments d,locations l

Where e.department_id=d.department_id and d.location_id=l.location_id

And e.commission_pct is not null;

c) 写一个查询显示那些工作在 Toronto(城市名) 的所有雇员的 last_name、job_title、department_id 和 department_name。

Select e.last_name,j.job_title,d.department_id,d.department_name

From employees e,departments d,locations l,jobs j

Where e.department_id=d.department_id and d.location_id=l.location_id

And e.job_id=j.job_id and l.city=’Toronto’;

d) 显示雇员的 last name 和 employee_id 连同他们的经理的 last name 和 manager_id。列标签分别为 Employee、Emp#、Manager 和 Mgr#。

Select a.last_name Employee,a.employee_id Emp#,

b.last_name Manager,b.employee_id Mgr#

From employees a,employees b

Where a.manager_id=b.employee_id;

e) 创建一个查询显示那些在雇员 Davies 之后入本公司工作的雇员的name 和 hire_date。

Select first_name,last_name from employees

Where hire_date>(select hire_date from employees where last_name=’Davies’);

f) 显示所有雇员的最高、最低、合计和平均薪水，列标签分别为：Maximum、Minimum、Sum 和 Average。

Select max(salary) Maximum,min(salary) Minimum,

sum(salary) Sum ,avg(salary) Average

from employees;

g) 写一个查询显示每一工作岗位的人数。

Select job_id,count(*) from employees group by job_id;

h) 写一个查询显示最高和最低薪水之间的差。列标签是 DIFFERENCE。

Select max(salary)-min(salary) DIFFERENCE from employees;

i) 显示经理号和经理付给雇员的最低薪水。排除那些经理未知的人。排除最低薪水小于等于 $6,000 的组。按薪水降序排序输出。

Select manager_id,min(salary) from employees

Where manager_id is not null

Group by manager_id

Having min(salary)<=6000;

j) 创建一个查询显示雇员总数，和在 1995、1996、1997 和 1998受雇的雇员人数。创建适当的列标题。

Select count(*) total,

Sum(decode(to_char(hire_date,’YYYY’),’1995’,1,0)) “1995”,

Sum(decode(to_char(hire_date,’YYYY’),’1996’,1,0)) “1996”,

Sum(decode(to_char(hire_date,’YYYY’),’1997’,1,0)) “1997”,

Sum(decode(to_char(hire_date,’YYYY’),’1998’,1,0)) “1998”

From employees;

k) 写一个查询显示与 Zlotkey 在同一部门的雇员的 last name 和 hire date，结果中不包括 Zlotkey。

Select last_name,hire_date from employees

Where last_name<>’Zlotkey’

And department_id=(select department_id from employees where last_name=’Zlotkey’);

l) 创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。

Select employee_id,first_name,last_name from employees

Where salary>(select avg(salary) from employees)

Order by salary;

(3) 数据维护

a) 运行下面语句来构造MY_EMPLOYEE表。

CREATE TABLE my_employee

(id NUMBER(4) CONSTRAINT my_employee_id_nn NOT NULL,

last_name VARCHAR2(25),

first_name VARCHAR2(25),

userid VARCHAR2(8),

salary NUMBER(9,2));

b) 从下面的样本数据中添加第一行数据到MY_EMPLOYEE表中，在INSERT子句中不要字段列表。

Insert into my_employee values(1,’Patel’,’Ralph’,’rpatel’,895);

c) 用前面的列表中样本数据的第二行组装MY_EMPLOYEE表，这次在INSERT子句中显式地列出字段列表。

Insert into my_employee (id, last_name,first_name, userid, salary)

values(2,’Dancs’,’Betty’,’bdancs’,860);

d) 插入表中的其它数据

e) 确认你添加到表中的数据。

Select * from my_employee;

f) 使得数据添加永久化

Commit;

g) 改id为3的雇员的名字为Drexler

Update my_employee set last_name=’Drexler’ where id=3;

h) 改变所有薪水少于900的雇员的工资为1000。

Update my_employee set salary=1000 where salary<900;

i) 从MY_EMPLOYEE表中删除Betty Dancs。

Delete from my_employee where id=2;

j) 提交所有未决的改变。

Commit;

k) 在事务的过程中标记一个中间点。

SAVEPOINT step_18;

清空整个表。

DELETE

FROM my_employee;

确认表是空的。

SELECT * FROM my_employee;

丢弃最近的DELETE操作，而不丢弃前面的插入操作。

ROLLBACK TO step_18;

确认新行还是完整的。

附录：表的结构

表COUNTRIES的结构：

字段名称 类型 中文含义

COUNTRY_ID CHAR(2) 国家编码

COUNTRY_NAME VARCHAR2(40) 国家名称

REGION_ID NUMBER 地区编码

表REGIONS的结构：

名称 类型 中文含义

REGION_ID NUMBER 地区编码

REGION_NAME VARCHAR2(25) 地区名称

表LOCATIONS的结构：

名称 类型 中文含义

LOCATION_ID NUMBER(4) 地点编码

STREET_ADDRESS VARCHAR2(40) 街道地址

POSTAL_CODE VARCHAR2(12) 邮政编码

CITY VARCHAR2(30) 所属城市

STATE_PROVINCE VARCHAR2(25) 所属州

COUNTRY_ID CHAR(2) 国家编码

表DEPARTMENTS的结构：

名称 类型 中文含义

DEPARTMENT_ID NUMBER(4) 部门编号

DEPARTMENT_NAME VARCHAR2(30) 部门名称

MANAGER_ID NUMBER(6) 管理员编号

LOCATION_ID NUMBER(4) 地点编号

表EMPLOYEES的结构：

名称 类型 中文含义

EMPLOYEE_ID NUMBER(6) 雇员编码

FIRST_NAME VARCHAR2(20) 名

LAST_NAME VARCHAR2(25) 姓

EMAIL VARCHAR2(25) Email地址

PHONE_NUMBER VARCHAR2(20) 电话号码

HIRE_DATE DATE 受雇日期

JOB_ID VARCHAR2(10) 工作岗位

SALARY NUMBER(8,2) 薪水

COMMISSION_PCT NUMBER(2,2) 佣金

MANAGER_ID NUMBER(6) 管理员编号

DEPARTMENT_ID NUMBER(4) 部门编号

数据库查询语句