oracle练习及答案.doc

测试一1. SQL*PLUS命令可以控制数据库 (no)2. 下面的语句是否可以执行成功 (yes)select last_name , job_id , salary as salfrom employees;3. 下面的语句是否可以执行成功 (yes)select * from employees;4. 找出下面语句中的错误select employee_id , last_name sal * 12 ANNUAL SALARYfrom employees;列于列之间未用逗号分隔别名应用引号括起来5. 显示表departments的结构，并查询其中的全部数据desc departmentsselect * from departments;6. 显示出表employees中的全部job_id（不能重复）select distinct job_id from employees;7. 显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUTselect EMPLOYEE_ID|,| FIRST_NAME|,|LAST_NAME|,|EMAIL|,|PHONE_NUMBER|,|HIRE_DATE|,|JOB_ID|,|SALARY|,|COMMISSION_PCT|,|MANAGER_ID|,| DEPARTMENT_ID as OUT_PUTfrom employees;测试二1. 查询工资大于12000的员工姓名和工资SELECT FIRST_NAME, salaryFROM employeesWHERE salary 12000;2. 查询员工号为176的员工的姓名和部门号SELECT FIRST_NAME, department_idFROM employeesWHERE employee_id = 176;3. 选择工资不在5000到12000的员工的姓名和工资SELECT FIRST_NAME, salaryFROM employeesWHERE salary NOT BETWEEN 5000 AND 12000;4. 选择雇用时间在1998-02-01到1998-05-01之间的员工姓名，job_id和雇用时间SELECT FIRST_NAME, job_id, hire_dateFROM employeesWHERE hire_date BETWEEN 01-2月-98 AND 01-5月-98;5. 选择在20和50号部门工作的员工姓名和部门号SELECT FIRST_NAME, department_idFROM employeesWHERE department_id IN (20, 50);6. 选择在1994年雇用的员工的姓名和雇用时间SELECT FIRST_NAME, hire_dateFROM employeesWHERE hire_date LIKE %94;7. 选择公司中没有管理者的员工姓名及job_idSELECT FIRST_NAME, job_idFROM employeesWHERE manager_id IS NULL;8. 选择公司中有奖金的员工姓名，工资和奖金SELECT FIRST_NAME, salary, commission_pctFROM employeesWHERE commission_pct IS NOT NULL;9. 选择员工姓名的第三个字母是a的员工姓名SELECT FIRST_NAMEFROM employeesWHERE FIRST_NAME LIKE _a%;10. 选择姓名中有字母a和e的员工姓名SELECT FIRST_NAMEFROM employeesWHERE (FIRST_NAME LIKE %e%a% OR FIRST_NAME LIKE %a%e%)测试三1. 显示系统时间Select sysdate Date from dual2. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）select empno,ename,sal, round(sal*1.20,0) as “new salary” from emp;3. 将员工的姓名按首字母排序，并写出姓名的长度（length）select ename Name ,length(ename) Length from emp order by substr(ename,1,1);4. 查询各员工的姓名，并显示出各员工在公司工作的月份数（worked_month）。select ename,round(months_between(sysdate,hiredate) as worked_month from emp;5. 查询员工的姓名和工资，按下面的形式显示enameSALking$24000select lower(ename)ename,lpad(sal,15,$) sal from emp;6. 查询员工的姓名，以及在公司工作的月份数（worked_month），并按月份数降序排列select ename,round(months_between(sysdate,hiredate) as worked_month from emp order by worked_month desc;7. 做一个查询，产生类似下面的结果 earns monthly but wants Dream SalaryKing earns $24000 monthly but wants $720001.select ename|earns|lpad(sal,length(sal)+1,$)| monthly but wants |lpad(sal*3,length(sal*3)+1,$) Dream Salary from emp;2.select ename | earns|to_char(sal,fm$999,999)|monthly but wants| to_char(sal*3,fm$9,999,999)as Dream Salary from emp;8. 做一个查询，产生类似下面的结果enameHiredateking17-jun-87select ename,hiredate,to_char(hiredate,dd-mon-yy,nls_date_language=American)from emp;9. 做一个查询，产生类似下面的结果Employees_and_their_salarysKing*其中每一个*代表一千元。SELECT rpad(ename, length(ename) + sal/1000 , *)“Employees_and_their_salarys”FROM emp10. 使用decode函数，按照下面的条件：job gradePRESIDENT AMANAGER BANALYST CSALESMAN DCLERK E产生类似下面的结果enameJobGradekingPRESIDENT ASELECT ename , job, decode (job,CLERK, E, SALESMAN , D, ANALYST , C, MANAGER , B, PRESIDENT , A,0) as GRADE FROM emp;11. 将第10题的查询用case函数再写一遍。SELECT ename , job, CASE jobWHEN CLERK THEN EWHEN SALESMAN THEN DWHEN ANALYST THEN CWHEN MANAGER THEN BWHEN PRESIDENT THEN AELSE 0 END GRADEFROM emp;测试四1. 组函数处理多行返回一行（yes）2. 组函数不计算空值（no）3. where子句在分组之前对检索进行过滤 ( yes )4. 查询公司员工工资的最大值，最小值，平均值，总和select max(sal) , min(sal) , avg(sal) , sum(sal)from emp;5. 查询各job_id的员工工资的最大值，最小值，平均值，总和select max(sal) , min(sal) , avg(sal) , sum(sal)from emp group by job;6. 选择具有各个job的员工人数select job , count(*) from emp group by job;7. 查询员工最高工资和最低工资的差距（DIFFERENCE）select max(sal) min(sal) DIFFERENCEfrom emp8. 查询各个管理者手下员工的最低工资，其中最低工资不能低于800，没有管理者的员工不计算在内select mgr , min(sal)from empwhere mgr is not null group by mgrhaving min(sal) = 800;9. 查询所有部门的名字，loc，员工数量和工资平均值select d.dname,d.loc ,count(e.empno),avg(sal)from emp e,dept dwhere e.deptno = d.deptno group by d.dname,d.loc10. 查询公司的人数，以及在1995-1998年之间，每年雇用的人数，结果类似下面的格式total1995199619971998303467SELECT COUNT(*) 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)1998FROM emp;测试五1. 显示所有员工的姓名，部门号和部门名称。SELECT e.ename, e.deptno, d.dnameFROM emp e, dept dWHERE e.deptno = d.deptno (+);2. 查询20号部门员工的job和20号部门的locSELECT DISTINCT e.job, d.locFROM emp e, dept dWHERE d.deptno = d.deptnoAND e.deptno = 20;3. 选择所有有奖金的员工的ename , dname , locSELECT e.ename, d.dname, d.loc FROM emp e, dept dWHERE e.deptno = d.deptnoANDm IS NOT NULL4. 选择在NEW YORK工作的员工的ename , job , deptno , dname Select e.ename , e.job , d.deptno , d.dnameFrom emp e , dept dWhere e.deptno = d.deptno And d.loc =NEW YORK5. 选择所有员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式employeesEmp#managerMgr#SCOTT7788JONES7566select e.enameemployees,e.empno EMP#,w.ename Manager,w.empno Mgr#from emp e,emp w where e.mgr = w.empno(+)测试六1. 查询和FORD相同部门的员工姓名和雇用日期select ename,hiredate,deptno from emp where deptno=(select deptno from emp where ename=FORD )and enameFORD2. 查询工资比公司平均工资高的员工的员工号，姓名和工资。select empno,ename,sal from emp where sal (select avg(sal)from emp )3. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名select empno, ename from empwhere deptno IN (select deptnofrom empwhere ename like %u%);4. 查询在部门的loc为CHICAGO的部门工作的员工的员工号，deptno和jobselect empno,emp.deptno,job from emp,dept where emp.deptno=dept.deptno and dept.loc =CHICAGO5. 查询管理者是king的员工姓名和工资select ename,sal from emp where mgr=(select empno from emp where lower(ename)=king)测试七(答案无)1. 创建一张表，表名为student,属性有id 主键，stuno varchar2(20)学号 唯一，name varchar2(20),sex 只能是男或女 ，password 长度为6-20，sal number(7,2)。2. 显示表student的结构3. 向表中插入数据4. 提交5. 将3号员工的name修改为king6. 将所有工资少于1000的修改成2000.7. 检查所作的修正8. 提交9. 删除所有数据10. 检查所作的修正11. 回滚12. 清空表student表测试八1. 创建表departmentsnametypeidNumber(7)nameVarchar2(25)CREATE TABLE departments(id NUMBER(7),name VARCHAR2(25);2. 将表dept中的数据插入表departments中INSERT INTO departmentsSELECT deptno, dnameFROM dept;3. 创建表employeesnametypeidNumber(7)First_nameVarchar2(25)Last_nameVarchar2(25)Dept_idNumber(7)CREATE TABLE employees(id NUMBER(7),last_name VARCHAR2(25),first_name VARCHAR2(25),dept_id NUMBER(7);4. 将列Last_name的长度增加到50ALTER TABLE employeesMODIFY (last_name VARCHAR2(50);5. 查询数据字典视图user_tables检查刚才的操作SELECT *FROM user_tablesWHERE table_name IN (DEPARTMENTS, EMPLOYEES);6. 将表employees重命名为employees1RENAME employees TO employees1; 7. 在表departments和employees中添加新列test_column，并检查所作的操作alter table employees add(test_column varchar2(50);alter table departments add(test_column varchar2(50);8. 在表departments和employees中将列test_column设置成不可用，之后删除alter table employees set unused(test_column);alter table departments set unused(test_column);9. 直接删除表employees中的列dept_idalter table employees drop column dept_id;10. 给表employees加注释“employees information”Comment on table employees is employees information ;测试九1. 使用表employees创建视图employee_vu，其中包括姓名（EMPLOYEE），员工号（EMPLOYEE_ID），部门号(DEPARTMENT_ID).CREATE OR REPLACE VIEW employees_vu ASSELECT employee_id, last_name employee, department_idFROM employees;2. 显示视图的结构desc employees_vu3. 查询数据字典视图user_views，检查视图的定义SELECT view_name,