Oracle9i笔试题面试题A
Oracle, 数据库
|
EMP表 |
EMP表 |
||
|
列名称 |
定义 |
列名称 |
定义 |
|
Empno |
NUMBER(4),PK |
Deptno |
NUMBER(2) |
|
Ename |
VARCHAR2(10) |
Dname |
VARCHAR2(14) |
|
Job |
VARCHAR2(9) |
Loc |
VARCHAR2(13) |
|
Mgr |
NUMBER(4) |
||
|
Hiredate |
DATE |
||
|
Sal |
NUMBER(7,2) |
||
|
Comm |
NUMBER(7,2) |
||
|
Deptno |
NUMBER(2),FK |
||
- Empno—-Employee
- Ename—-Employee Name
- Job—-Job Title
- Mgr—-Manager of Employee
- Hiredate—-Hire date
- Sal—-Salary(每月)
- Comm—-Commission(销售)
- Deptno—-Department Number
- Dname—-Department Name
- Loc—-Location of Operation
1.下面哪两个查询可以用来确定员工Blake所在部门的名称?
a.SELECT dname FROM dept WHERE ename =(‘BLAKE’)；
b.SELECT d. dname FROM dept d NATURAL JOIN emp e
WHERE e.ename =‘BLAKE’；
c.SELECT dname FROM dept d,emp e
WHERE e.ename =（‘BLAKE’）
AND d.deptno = e.deptno；
d.SELECT dname FROM dept JOIN emp USING (dname)
WHERE ename =‘BLAKE’；
e.SELECT dname FROM dept JOIN emp
ON dept.deptno = emp.deptno
WHERE ename =‘BLAKE’；
2.如果Sal包含各个员工的月薪,那么下面哪一个查询将显示EMP表中的各个员工的年薪?
a.SELECT sal * 12 ‘Annual Salary’FROM emp;
b.SELECT salary * 12 ‘annual FROM emp;
c.SELECT annual sal * 12 FROM emp;
d.SELECT sal * 12 FROM emp;
3.下面哪一个查询将显示存储在EMP表中的所有数据?
a.SELECT * FROM emp;
b.SELECT % FROM emp;
c.SELECT ^ FROM emp;
d.SELECT _ FROM emp;
4.在执行下面的查询时,下面哪能一种说法反映了将会发生的情况?
SELECT ename
FROM emp e,emp m
WHERE e.mgr = m.empno;
a.这个查询将产生一个自我联接,显示各个员工的经理的姓名
b.将显示一个多义性错误,并且不会执行这个语句
c.这个查询将执行一个完全外部联接,并显示没有为其指定经理的员工的姓名
d.这个查询将执行一个右外部联接,显示不是经理的员工的姓名
5.下面哪些关键字可以用来创建一个不等连接?选出所有正确的选项.
a.NATURAL JOIN
b.JOIN… USING
c.OUTER JOIN
d.JOIN… ON
e.以上都不可以—-使用任何JOIN关键字都不能创建不等连接
6.下面哪一个查询只返回包含DEPT表中而没有在EMP表中列出的部门号?
a.SELECT deptno FROM dept NATURAL JOIN emp
WHERE deptno NOT IN emp;
b.SELECT deptno FROM dept MINUS deptno FROM emp;
c.SELECT deptno FROM dept MINUS
SELECT deptno FROM emp;
d.SELECT deptno FROM dept JOIN emp
ON dept.deptno<>emp.deptno;
e.SELECT deptno FROM emp MINUS
SELECT deptno FROM dept;
7.下面哪一个查询将显示名为King的员工的工号?
a.SELECT empno FROM emp WHERE ename = ‘% KING’;
b.SELECT empno FROM emp WHERE ename = ‘_ ING’;
c.SELECT empno FROM emp WHERE ename LIKE KING;
d.SELECT empno FROM emp WHERE ename = KING;
e.有上查询都不对
8.下面哪一个查询将显示销售部在1981年聘用的所有员工?
a.SELECT * FROM emp
WHERE dname =’SALES’AND hiredate LIKE’%81′;
b.SELECT * FROM emp NATURAL JOIN dept
WHERE dname =’SALES’AND hiredate LIKE’%81′;
c.SELECT * FROM emp
WHERE dname =’SALES’OR hiredate LIKE’%81′;
d.SELECT * FROM emp NATURAL JOIN dept
WHERE dname =’SALES’AND hiredate LIKE’%1981′;
9.下面哪一个查询将显示存储在EMP表中的各个员工的姓名和职务?选择所有正确的查询.
a.SELECT ename,job AS”Job Title”FROM emp;
b.SELECT ename,job “Job Title”FROM emp;
c.SELECT ename,job FROM emp;
d.SELECT ename,job ‘Job TITLE’FROM emp;
e.SELECT ename,job ‘Job Title’FROM emp;
10.下面哪一种说法最准确地说明了下面的SELECT语句的结果?
SELECT DISTINCT job,ename FROM emp;
a.结果中返回的每一行都是惟一的
b.各个职务在结果中只显示一次
c.各个职务将只显示一次,并随之显示具有该职务的各个员工的姓名
d.结果将按照员工姓名排序
11.下面哪一个子句用来选择表中的某些列?
a.SELECT
b.FROM
c.WHERE
d.ORDER BY
12.下面哪一个查询将显示年薪至少为10000美元的所有员工的姓名?
a.SELECT ename FROM emp WHERE sal*12>10,000;
b.SELECT ename FROM emp WHERE sal*12>’10,000′;
c.SELECT ename FROM emp WHERE sal*12=>10000;
d.SELECT ename FROM emp WHERE sal*12>=10000.00;
e.上面的语句都不正确
13.下面哪些查询将按照员工姓名的顺序显示各个员工的号码?选出所有正确的查询.
a.SELECT ename,ename FROM emp ORDER BY empno;
b.SELECT ename,ename FROM emp ORDER BY ename;
c.SELECT ename,ename FROM emp ORDER BY 1;
d.SELECT ename,ename FROM emp ORDER BY 2;
e.SELECT ename,ename ORDER BY ename;
14.下面哪一个查询将显示月薪至少为1200美元但是少于2000美元的各个员工的姓名?
a.SELECT ename FROM emp
WHERE sal BETWEEN(1200,2000);
b.SELECT ename FROM emp
WHERE sal BETWEEN 1200 and 2000;
c.SELECT ename FROM emp
WHERE sal>=1200 AND <2000;
d.SELECT ename FROM emp
WHERE sal>=1200 AND sal <2000;
e.SELECT ename FROM emp
WHERE sal>1200 AND sal <2000;
15.下面哪一个子句用来限制查询返回的行?
a.SELECT
b.FROM
c.WHERE
d.ORDER BY
16.下面哪一个运算符用来执行模式搜索?
a.IN
b.BETWEEN
c.IS NULL
d.LIKE
17.下面哪些查询在结果中不包括部门30中的任何员工?选出所有正确的答案.
a.SELECT * FROM emp WHERE deptno! =30;
b.SELECT * FROM emp WHERE deptno<>30;
c.SELECT * FROM emp WHERE deptno^30;
d.SELECT * FROM emp WHERE deptno =30;
18.下面哪一个查询将显示没有佣金的所有员工?
a.SELECT ename FROM emp WHERE comm = NULL;
b.SELECT ename FROM emp WHERE comm IS NULL;
c.SELECT ename FROM emp WHERE comm LIKE NULL;
d.SELECT ename FROM emp WHERE comm LIKE ‘NULL’;
19.下面哪一个查询将返回在销售部或会计部工作并且月薪至少为2000美元的所有员工的姓名?选出所有正确的答案.
a.SELECT ename FROM emp NATURAL JOIN dept
WHERE dname IN(‘SALES’,'ACCOUNTING’)
AND sal>=2000;
b.SELECT ename FROM emp JOIN dept
ON emp.deptno = dept.deptno
WHERE sal >= 2000 AND dname = ‘SALES’
OR dname =’ACCOUNTING’;
c.SELECT ename FROM emp JOIN dept USING(deptno)
WHERE sal >= 2000 AND (dname = ‘SALES’OR dname =
‘ACCOUNTING’);
d.SELECT ename FROM emp NATURAL JOIN dept
WHERE sal >= 2000 AND (dname = ‘SALES’OR dname =
‘ACCOUNTING’);
20.下面哪一个子句用来按照某种顺序显示查询的结果?
a.SELECT
b.FROM
c.WHERE
d.ORDER BY
