最新oracle习题及答案Word格式.docx
- 文档编号:21514746
- 上传时间:2023-01-30
- 格式:DOCX
- 页数:7
- 大小:17.63KB
最新oracle习题及答案Word格式.docx
《最新oracle习题及答案Word格式.docx》由会员分享,可在线阅读,更多相关《最新oracle习题及答案Word格式.docx(7页珍藏版)》请在冰豆网上搜索。
9.选择员工姓名的第三个字母是a的员工姓名
||first_namefromemployeeswherelast_name||'
||first_namelike'
___a%'
10.选择姓名中有字母a和e的员工姓名
||first_namefromemployeeswherelast_name||first_namelike'
%a%e%'
orlast_name||first_namelike'
%e%a%'
多表查询
11.显示所有员工的姓名,部门号和部门名称。
Selecte.last_name,d.department_id,d.department_namefromemployeese,departmentsdwhere(e.department_id=d.department_id);
12.查询90号部门员工的job_id和90号部门的location_id
Selecte.job_id,d.location_idfromemployeese,departmentsdwheree.department_id=d.deparement_idandd.department_id=90;
13.选择所有有奖金的员工的
last_name,department_name,location_id,city
Selecte.last_name,d.department_name,l.location_id,cityfromemployeese,departmentsd,locationslwheree.department_id=d.department_idANDd.location_id=l.location_idANDcommission_pctisnotnull;
14.选择在Toronto工作的员工的
last_name,job_id,department_id,department_name
Selecte.last_name,e.job_id,d.department_id,d.department_namefromemployeese,departmentsd,locationslwheree.department_id=d.department_idANDd.location_id=l.location_idANDl.city='
Toronto'
15.选择所有员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
employees
Emp#
manager
Mgr#
kochhar
101
king
100
Selecte.employee_id"
employees"
e.last_name"
Emp#"
d.manager_id"
Mgr#"
d.last_name"
manger"
fromemployeese,employeesdwheree.manager_id=d.employee_id(+);
6.查询各部门员工姓名和他们的同事姓名,结果类似于下面的格式
Department_id
Last_name
colleague
20
fay
hartstein
Selecte.department_id"
Department_id"
d.last_name"
Last_name"
e.last_name"
colleague"
fromemployeesejoinemployeesdon(d.department_id=e.department_id)whered.last_name<
>
e.last_name;
分组查询
16.组函数处理多行返回一行(true)
17.组函数不计算空值(false)
18.where子句在分组之前对检索进行过滤(true)
19.查询公司员工工资的最大值,最小值,平均值,总和
Selectmax(salary),min(salary),avg(salary),sum(salary)fromemployees;
20.查询各job_id的员工工资的最大值,最小值,平均值,总和
Selectmax(salary),min(salary),avg(salary),sum(salary)fromemployeesgroupbyjob_id;
21.选择具有各个job_id的员工人数
Selectjob_id,count(*)fromemployeesgroupbyjob_id;
22.查询员工最高工资和最低工资的差距(DIFFERENCE)
Selectmax(salary)-min(salary)"
DIFFERENCE"
fromemployees;
23.查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
Selectmanager_id,min(salary)fromemployeeswheremanager_idisnotnullgroupbymanager_idhavingmin(salary)>
=6000;
24.查询所有部门的名字,location_id,员工数量和工资平均值
Selectd.department_name,d.location_id,count(e.employee_id),avg(e.salary)fromemployeese,departmentsdwheree.department_id(+)=d.department_idgroupbyd.location_id,d.department_name;
25.查询公司的人数,以及在1995-1998年之间,每年雇用的人数,结果类似下面的格式
total
1995
1996
1997
1998
30
3
4
6
7
Selectcount(employee_id),to_char(hire_date,'
yyyy'
)"
y"
count(*)fromemployeeswhereto_char(hire_date,'
)between1995and1998groupbyto_char(hire_date,'
)orderbyy;
Select
子查询
26.查询和zlotkey相同部门的员工姓名和雇用日期
Selectlast_name,hire_date,department_idfromemployeeswheredepartment_id=(selectdepartment_idfromemployeeswherelower(last_name)='
zlotkey'
);
27.查询工资比公司平均工资高的员工的员工号,姓名和工资。
Selectemployee_id,last_name,salaryfromemployeeswheresalary>
(selectavg(salary)fromemployees);
28.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
Selectemployee_id,last_name,department_idfromemployeeswheredepartment_id=any(selectdepartment_idfromemployeeswherelower(last_name)like'
%u%'
);
29.查询在部门的location_id为1700的部门工作的员工的员工号,department_id和job_id
Selectemployee_id,department_id,job_idfromemployeeswheredepartment_id=any(selectdepartment_idfromdepartmentswherelocation_id=1700);
30.查询管理者是king的员工姓名和工资
Selectlast_name,salaryfromemployeeswheremanager_id=(selectemployee_idfromemployeeswherelast_name='
King'
创建和管理表
31.创建表dept
name
Null?
type
id
Number(7)
Varchar2(25)
Createtabledept
(idNumber(7),namevarchar(25));
32.将表departments中的数据插入表dept中
Insertintodept(
SELECTdepartment_id,department_name
FROMdepartments);
33.创建表emp
First_name
Dept_id
Createtableemp(idNumber(7),First_namevarchar(25),Last_namevarchar(25),Dept_idnumber(7));
34.将列Last_name的长度增加到50
Altertableempmodify(last_namevarchar2(50));
35.查询数据字典视图user_tables检查刚才的操作
Select*fromuser_tables;
36.根据表employees创建employees2
Createtableemployees2as
select*fromemployees;
37.删除表emp
Droptableemp;
38.将表employees2重命名为emp
Renameemployees2toemp;
39.在表dept和emp中添加新列test_column,并检查所作的操作
Altertableempadd(test_columnvarchar(10));
单行函数
40.显示系统时间
Selectto_char(sysdate'
DD-MON-YYYY'
)fromdual;
41.查询员工号,姓名,工资,以及工资提高百分之20%后的结果(newsalary)
Selectemployee_id,last_name||'
||first_name,salary,salary*1.2newsalaryfromemployees;
42.将员工的姓名按首字母排序,并写出姓名的长度(length)
Selectlast_name,length(last_name)fromemployeesorderbysubstr(last_name,1,1)desc;
43.查询各员工的姓名,并显示出各员工在公司工作的月份数(worked_month)。
Selectlast_name||"
||first_name,months_between(sysdate,hire_date)worked_monthfromemployees;
44.查询员工的姓名和工资,按下面的形式显示
SALARY
$$$$$$$$$$24000
Selectlower(last_name)"
last_name"
lpad(salary,15,'
$'
Salary"
45.查询员工的姓名,以及在公司工作的月份数(worked_month),并按月份数降序排列
||first_name,months_between(sysdate,hire_date)worked_monthfromemployeesorderbyworked_monthdesc;
46.做一个查询,产生下面的结果
<
last_name>
earns<
salary>
monthlybutwants<
salary*3>
DreamSalary
Kingearns$24000monthlybutwants$72000
Select'
||'
earns'
||lpad(salary,6,'
)||'
monthlybutwants'
||lpad(salary*3,6,'
DreamSalary"
47.做一个查询,产生下面的结果add_month(6)+4
Hire_date
reiew
17-jun-87
Monday,thetwenty-firstofDecember,1987
hire_date,to_char(add_months(hire_date,6)+4,'
DAY,MONTH,YYYY'
Hire_date"
fromemployeeswherelast_namelike'
9做一个查询,产生下面的结果
Employees_and_their_salarys
King***************************
其中每一个*代表一千元。
Selectrpad(last_name,salary/1000+length(last_name),'
)fromemployeeswherelast_namelike'
48.使用decode函数,按照下面的条件:
jobgrade
AD_PRESA
ST_MANB
IT_PROGC
SA_REPD
ST_CLERKE
产生下面的结果
Job_id
Grade
AD_PRES
A
Selectlast_name,job_id,
Decode(job_id,'
AD_PRES'
'
A'
ST_MAN'
B'
IT_PROG'
C'
SA_REP'
D'
ST_CLERK'
E'
)Gradefromemployees;
49.将第9题的查询用case函数再写一遍。
Casejob_idwhen'
then'
When'
then'
end"
Grade"
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 最新 oracle 习题 答案