oracle练习及答案讲课稿.docx
《oracle练习及答案讲课稿.docx》由会员分享,可在线阅读,更多相关《oracle练习及答案讲课稿.docx(12页珍藏版)》请在冰豆网上搜索。
oracle练习及答案讲课稿
oracle练习及答案
测试一
1.SQL*PLUS命令可以控制数据库(no)
2.下面的语句是否可以执行成功(yes)
selectlast_name,job_id,salaryassal
fromemployees;
3.下面的语句是否可以执行成功(yes)
select*fromemployees;
4.找出下面语句中的错误
selectemployee_id,last_name
sal*12ANNUALSALARY
fromemployees;
列于列之间未用逗号分隔
别名应用引号括起来
5.显示表departments的结构,并查询其中的全部数据
descdepartments
select*fromdepartments;
6.显示出表employees中的全部job_id(不能重复)
selectdistinctjob_id
fromemployees;
7.显示出表employees的全部列,各个列之间用逗号连接,列头显示成OUT_PUT
selectEMPLOYEE_ID||','||FIRST_NAME||','||LAST_NAME||','||EMAIL||','||PHONE_NUMBER||','||HIRE_DATE||','||JOB_ID||','||SALARY||','||COMMISSION_PCT||','||MANAGER_ID||','||DEPARTMENT_IDas"OUT_PUT"
fromemployees;
测试二
1.查询工资大于12000的员工姓名和工资
SELECTFIRST_NAME,salary
FROMemployees
WHEREsalary>12000;
2.查询员工号为176的员工的姓名和部门号
SELECTFIRST_NAME,department_id
FROMemployees
WHEREemployee_id=176;
3.选择工资不在5000到12000的员工的姓名和工资
SELECTFIRST_NAME,salary
FROMemployees
WHEREsalaryNOTBETWEEN5000AND12000;
4.选择雇用时间在1998-02-01到1998-05-01之间的员工姓名,job_id和雇用时间
SELECTFIRST_NAME,job_id,hire_date
FROMemployees
WHEREhire_dateBETWEEN'01-2月-98'AND'01-5月-98';
5.选择在20和50号部门工作的员工姓名和部门号
SELECTFIRST_NAME,department_id
FROMemployees
WHEREdepartment_idIN(20,50);
6.选择在1994年雇用的员工的姓名和雇用时间
SELECTFIRST_NAME,hire_date
FROMemployees
WHEREhire_dateLIKE'%94';
7.选择公司中没有管理者的员工姓名及job_id
SELECTFIRST_NAME,job_id
FROMemployees
WHEREmanager_idISNULL;
8.选择公司中有奖金的员工姓名,工资和奖金
SELECTFIRST_NAME,salary,commission_pct
FROMemployees
WHEREcommission_pctISNOTNULL;
9.选择员工姓名的第三个字母是a的员工姓名
SELECTFIRST_NAME
FROMemployees
WHEREFIRST_NAMELIKE'__a%';
10.选择姓名中有字母a和e的员工姓名
SELECTFIRST_NAME
FROMemployees
WHERE(FIRST_NAMELIKE'%e%a%'ORFIRST_NAMELIKE'%a%e%')
测试三
1.显示系统时间
Selectsysdate"Date"fromdual
2.查询员工号,姓名,工资,以及工资提高百分之20%后的结果(newsalary)
selectempno,ename,sal,round(sal*1.20,0)as“newsalary”fromemp;
3.将员工的姓名按首字母排序,并写出姓名的长度(length)
selectename"Name",length(ename)"Length"fromemporderbysubstr(ename,1,1);
4.查询各员工的姓名,并显示出各员工在公司工作的月份数(worked_month)。
selectename,
round(months_between(sysdate,hiredate))asworked_monthfromemp;
5.查询员工的姓名和工资,按下面的形式显示
ename
SAL
king
$$$$$$$$$$24000
selectlower(ename)ename,lpad(sal,15,'$')salfromemp;
6.查询员工的姓名,以及在公司工作的月份数(worked_month),并按月份数降序排列
selectename,round(months_between(sysdate,hiredate))asworked_monthfromemp
orderbyworked_monthdesc;
7.做一个查询,产生类似下面的结果
earnsmonthlybutwants
DreamSalary
Kingearns$24000monthlybutwants$72000
1.selectename||'earns'||lpad(sal,length(sal)+1,'$')||'monthlybutwants'||lpad(sal*3,length(sal*3)+1,'$')"DreamSalary"fromemp;
2.selectename||'earns'||to_char(sal,'fm$999,999')||'monthlybutwants'
||to_char(sal*3,'fm$9,999,999')as"DreamSalary"fromemp;
8.做一个查询,产生类似下面的结果
ename
Hiredate
king
17-jun-87
selectename,hiredate,to_char(hiredate,'dd-mon-yy','nls_date_language=''American''')fromemp;
9.做一个查询,产生类似下面的结果
Employees_and_their_salarys
King*****
其中每一个*代表一千元。
SELECTrpad(ename,length(ename)+sal/1000,'*')
“Employees_and_their_salarys”
FROMemp
10.使用decode函数,按照下面的条件:
jobgrade
PRESIDENTA
MANAGERB
ANALYSTC
SALESMAND
CLERKE
产生类似下面的结果
ename
Job
Grade
king
PRESIDENT
A
SELECTename,job,decode(job,
'CLERK','E',
'SALESMAN','D',
'ANALYST','C',
'MANAGER','B',
'PRESIDENT','A',
'0')asGRADEFROMemp;
11.将第10题的查询用case函数再写一遍。
SELECTename,job,CASEjob
WHEN‘CLERK'THEN'E'
WHEN'SALESMAN'THEN'D'
WHEN'ANALYST'THEN'C'
WHEN'MANAGER'THEN'B'
WHEN'PRESIDENT'THEN'A'
ELSE'0'ENDGRADE
FROMemp;
测试四
1.组函数处理多行返回一行(yes)
2.组函数不计算空值(no)
3.where子句在分组之前对检索进行过滤(yes)
4.查询公司员工工资的最大值,最小值,平均值,总和
selectmax(sal),min(sal),avg(sal),sum(sal)
fromemp;
5.查询各job_id的员工工资的最大值,最小值,平均值,总和
selectmax(sal),min(sal),avg(sal),sum(sal)
fromempgroupbyjob;
6.选择具有各个job的员工人数
selectjob,count(*)
fromempgroupbyjob;
7.查询员工最高工资和最低工资的差距(DIFFERENCE)
selectmax(sal)–min(sal)DIFFERENCE
fromemp
8.查询各个管理者手下员工的最低工资,其中最低工资不能低于800,没有管理者的员工不计算在内
selectmgr,min(sal)
fromemp
wheremgrisnotnullgroupbymgr
havingmin(sal)>=800;
9.查询所有部门的名字,loc,员工数量和工资平均值
selectd.dname,d.loc,count(e.empno),avg(sal)
fromempe,deptd
wheree.deptno=d.deptno
groupbyd.dname,d.loc
10.查询公司的人数,以及在1995-1998年之间,每年雇用的人数,结果类似下面的格式
total
1995
1996
1997
1998
30
3
4
6
7
SELECTCOUNT(*)total,
SUM(DECODE(TO_CHAR(hiredate,'YYYY'),1995,1,0))"1995",
SUM(DECODE(TO_CHAR(hiredate,'YYYY'),1996,1,0))"1996",
SUM(DECODE(TO_CHAR(hiredate,'YYYY'),1997,1,0))"1997",
SUM(DECODE(TO_CHAR(hiredate,'YYYY'),1998,1,0))"1998"
FROMemp;
测试五
1.显示所有员工的姓名,部门号和部门名称。
SELECTe.ename,e.deptno,d.dname
FROMempe,deptd
WHEREe.deptno=d.deptno(+);
2.查询20号部门员工的job和20号部门的loc
SELECTDISTINCTe.job,d.loc
FROMempe,deptd
WHEREd.deptno=d.deptno
ANDe.deptno=20;
3.选择所有有奖金的员工的ename,dname,loc
SELECTe.ename,d.dname,d.loc
FROMempe,deptd
WHEREe.deptno=d.deptno
AND
mISNOTNULL
4.选择在NEWYORK工作的员工的ename,job,deptno,dname
Selecte.ename,e.job,d.deptno,d.dname
Fromempe,deptd
Wheree.deptno=d.deptno
Andd.loc=‘NEWYORK'
5.选择所有员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
employees
Emp#
manager
Mgr#
SCOTT
7788
JONES
7566
selecte.ename"employees",e.empno"EMP#",w.ename"Manager",
w.empno"Mgr#"
fromempe,empw
wheree.mgr=w.empno(+)
测试六
1.查询和FORD相同部门的员工姓名和雇用日期
selectename,hiredate,deptnofromemp
wheredeptno=(selectdeptnofromempwhereename='FORD')
andename<>'FORD'
2.查询工资比公司平均工资高的员工的员工号,姓名和工资。
selectempno,ename,salfromempwheresal>(selectavg(sal)fromemp)
3.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
selectempno,enamefromemp
wheredeptnoIN(selectdeptno
fromemp
whereenamelike'%u%');
4.查询在部门的loc为CHICAGO的部门工作的员工的员工号,deptno和job
selectempno,emp.deptno,jobfromemp,deptwhereemp.deptno=dept.deptno
anddept.loc='CHICAGO'
5.查询管理者是king的员工姓名和工资
selectename,salfromemp
wheremgr=(selectempnofromempwherelower(ename)='king')
测试七(答案无)
1.创建一张表,表名为student,属性有id主键,stunovarchar2(20)学号唯一,namevarchar2(20),sex只能是男或女,password长度为6-20,salnumber(7,2)。
2.显示表student的结构
3.向表中插入数据
4.提交
5.将3号员工的name修改为king
6.将所有工资少于1000的修改成2000.
7.检查所作的修正
8.提交
9.删除所有数据
10.检查所作的修正
11.回滚
12.清空表student表
测试八
1.创建表departments
name
type
id
Number(7)
name
Varchar2(25)
CREATETABLEdepartments
(idNUMBER(7),
nameVARCHAR2(25));
2.将表dept中的数据插入表departments中
INSERTINTOdepartments
SELECTdeptno,dname
FROMdept;
3.创建表employees
name
type
id
Number(7)
First_name
Varchar2(25)
Last_name
Varchar2(25)
Dept_id
Number(7)
CREATETABLEemployees
(idNUMBER(7),
last_nameVARCHAR2(25),
first_nameVARCHAR2(25),
dept_idNUMBER(7));
4.将列Last_name的长度增加到50
ALTERTABLEemployees
MODIFY(last_nameVARCHAR2(50));
5.查询数据字典视图user_tables检查刚才的操作
SELECT*
FROMuser_tables
WHEREtable_nameIN('DEPARTMENTS','EMPLOYEES');
6.将表employees重命名为employees1
RENAMEemployeesTOemployees1;
7.在表departments和employees中添加新列test_column,并检查所作的操作
altertableemployeesadd(test_columnvarchar2(50));
altertabledepartmentsadd(test_columnvarchar2(50));
8.在表departments和employees中将列test_column设置成不可用,之后删除
altertableemployeessetunused(test_column);
altertabledepartmentssetunused(test_column);
9.直接删除表employees中的列dept_id
altertableemployeesdropcolumndept_id;
10.给表employees加注释“employeesinformation”
Commentontableemployeesis'employeesinformation';
测试九
1.使用表employees创建视图employee_vu,其中包括姓名(EMPLOYEE),员工号(EMPLOYEE_ID),部门号(DEPARTMENT_ID).
CREATEORREPLACEVIEWemployees_vuAS
SELECTemployee_id,last_nameemployee,department_id
FROMemployees;
2.显示视图的结构
descemployees_vu
3.查询数据字典视图user_views,检查视图的定义
SELECTview_name,text
FROMuser_views;
4.查询视图中的全部内容
SELECT*FROMemployees_vu;
5.将视图中的数据限定在部门号是80的范围内
CREATEVIEWdept80AS
SELECTidempno,last_nameemployee,
Dept_iddeptno
FROMemployees
WHEREid=80
WITHCHECKOPTIONCONSTRAINTemp_dept_80;
6.将视图改变成只读视图
CREATEORREPLACEVIEWdept80AS
SELECTemployee_idempno,last_nameemployee,
department_iddeptno
FROMemployees
WHEREdepartment_id=80
WITHREADONLY;
测试十
7.如果用户能过登陆到数据库,至少需要哪种权限?
是系统权限还是对象权限
CREATESESSION系统权限
8.创建表需要哪种权限
CREATETABLE
9.将表departments的查询权限分配给用户system
GRANTselect
ONdepartments
TOsystem;
10.从system处收回刚才赋予的权限。
Revokeselect
ONdepartments
fromsystem;
11.创建角色dvp,并将权限赋予该角色
CREATEPROCEDURE
CREATESESSION
CREATETABLE
CREATESEQUENCE
CREATEVIEW
CREATEROLEdvp;
GrantCREATEPROCEDURE,
CREATESESSION,
CREATETABLE,
CREATESEQUENCE,
CREATEVIEW
Todvp;