Oracle笔试常见选择题A1、答案（每题10分，有多选）：123456789102、1、在EMPLOYEES 和DEPARTMENTS表里检查下列数据。

EMPLOYEESLAST_NAME DEPARTMENT_ID SALARYGetz 10 3000Davis 20 1500King 20 2200Davis 30 5000Kochhar 5000DEPARTMENT_ID DEPARTMENT_NAME10 Sales20 Marketing30 Accounts40 Administration如果你想获得所有的employees，不管他们是否匹配部门表中的部门，那么下面选项中哪个查询语句是正确的？A．SELECT last_name,department_name FROM employees,departments(+);B．SELECT last_name,department_name FROM employees JOIN departments(+);C．SELECT last_name,department_name FROM employees(+) e JOIN departments d ON(e.department_id = d.department_id);D．SELECT last_name,department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id =d.department_id);E．SELECT last_name,department_name FROM employees(+),departments ON (e.department_id = d.department_id);F．SELECT last_name,department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id =d.department_id);2、查看下列EMPLOYEES表的结构。

EMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2(25)LAST_NAME VARCHAR2(25)下面哪3个选项可以插入一行到该表中？A．INSERT INTO employees VALUES (NULL,’John’,’Smith’);B．INSERT INTO employees (first_name,last_name) VALUES (‘John’,’Smith’);C．INSERT INTO employees VALUES(‘1000’,’John’,’NULL’);D．INSERT INTO employees(first_name,last_name,employee_id)VALUES (‘1000’,’John’,’NULL’);E．INSERT INTO employees(employee_id) VALUES (1000);F．INSERT INTO employees(employee_id,first_name,last_name) VALUES (1000,’John’,’ ’);3、检查下列EMPLOYEES表中的数据。

LAST_NAME DEPARTMENT_ID SALARYGetz 10 3000Davis 20 1500King 20 2200Davis 30 5500下面哪3个子查询是正确的？A．select * from employees where salary>(select min(salary) from employees group by department.id);B．select * from employees where salary=(select avg(salary) from employees group by department_id);C．select distinct from department_id from employees where salary>any (select avg(salary) from employees group bydepartment_id);D．select department_id from employees where salary>all (selectavg(salary) from employees group by department_id);E．select last_name from employees where salary>any(select max(salary) from employees group by department_id);F．select department_id from employees where salary>all (select avg(salary) from employees group by avg(salary));4、下面关于视图的说法哪两项是正确的？A．一个视图可以作为只读的被创建。

B．一个视图可以用来连接两个或更多的表而被创建。

C．一个视图在select语句中不能有一个order by 语句D．一个视图在select语句中不能同group by 语句一起创建。

5、检查下列EMPLOYEES表的描述EMP_ID NUMBER(4) NOT NULLLAST_NAME VARCHAR2 (30) NOT NULLFIRST_NAME VARCHAR2 (30)DEPT_ID NUMBER(2)JOB_CAT VARCHAR2 (30)SALARY NUMBER(8,2)下面的选项中哪个说明了在每个部门中每个工作种类付给的是最多数量的工资？A. select dept_id,job_cat,max(salary) from employees wheresalary>max(salary);B. select dept_id,job_cat,max(salary) from employees group bydept_id,job_cat;C. select dept_id,job_cat,max(salary) from employeesD. select dept_id,job_cat,max(salary) from employees group by dept_id;E. select dept_id,job_cat,max(salary) from employees group bydept_id,job_cat,salary;6、检查下面STUDENTS表的描述：STD_ID NUMBER(4)COURSE_ID VARCHAR2(10)START_DATE DATEEND_DATE DATE哪两个选项的函数在START_DATE列是正确的？A. SUM(start_date)B. AVG(start_date)C. COUNT(start_date)D. AVG(start_date,end_date)E. MIN(start_date)F. MAXIMUM(start_date)7、当对一个特殊列定义的时候，下面哪3个选项可以用做DATETIME数据类型？A．TIMESTAMPB．INTERVAL MONTH TO DAYC．INTERVAL　DAY TO SECONDD．INTERVAL YEAR TO MONTHE．TIMESTAMP WITH DATABASE TIMEZONE8、下面哪3个选项的select 语句可以以“$2,000.00”的形式显示2000？A．SELECT TO CNAR （2000，’$#,###.##’）FROM dual;B. SELECT TO CNAR （2000，’$0,000.00’）FROM dual;C. SELECT TO CNAR （2000，’$9,999.00’）FROM dual;D.SELECT TO CNAR （2000，’ $9,999.99’）FROM dual;E. SELECT TO CNAR （2000，’ $2,000.00’）FROM dual;F. SELECT TO CNAR （2000，’ $N,NNN.NN’）FROM dual;9、检查下面的EMPLOYEES表的描述EMP_ID NUMBER(4) NOT NULLLAST_NAME VARCHAR2(30) NOT NULLFIRST_NAME VARCHAR2(30)DEPT_ID NUMBER(2)JOB_CAT VARCHAR2(30)SALARY NUMBER(8,2)下面这几个选项中显示了部门中的ID，员工最少工资，员工最多工资情况（最少工资少于5000，最多工资多于15000）？A．SELECT dept_id,MIN(salary),MAX(salary) FROM employees WHERE MIN(salary)<5000 AND MAX(salary)>15000;B．SELECT dept_id,MIN(salary),MAX(salary) FROM employees GROUP BY dept_id;C．SELECT dept_id,MIN(salary),MAX(salary) FROM employees HAVING MIN(salary)<5000 AND MAX(salary)>15000;D．SELECT dept_id,MIN(salary),MAX(salary) FROM employees GROUP BY dept_id HAVING MIN(salary)<5000 ANDMAX(salary)<15000;E．SELECT dept_id,MIN(salary),MAX(salary) FROM employees GROUP BY dept_id ,salary HAVING MIN(salary)<5000 ANDMAX(salary)>15000;10、在SELECT语句中包含了WHERE语句，在SELECT语句中的哪里可以替换GROUP BY 语句A.直接在SELECT语句的后面B．在WHERE语句的前面C．在FROM语句的前面D．在ORDER BY语句的后面E．在WHERE语句的后面。