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.下面哪一个查询将以小写字母显示各个部门的名称?
a.SELECt LOW (dname) FROM dept;
b.SELECt LOWER (dname) FROM dept;
c.SELECt LOWERCASE (dname) FROM dept;
d.SELECt NOTUPPER (dname) FROM dept;
2.如果Sal列包含各个员工的工资, Comm列包含销售代表所得到的佣金,那么下面哪一个查询将显示各个员工的总工资?选出所有正确的答案.
a.SELECt ename,sal + NVL (comm,0) AS”Gross Salary”FROM emp;
b.SELECt ename,NVL2 (sal + com,sal,comm)”Gross “FROM emp;
c.SELECt ename,NVL (comm,0) + sal FROM emp;
d.SELECt ename,NVL (sal + com,sal) FROM emp;
3.下面哪一个查询将返回在部门10工作的所有人的总工资?选出所有正确的答案.
a.SELECt SUM (sal) FROM emp WHERe deptno = 10;
b.SELECt TOTAL (sal) FROM emp WHERe deptno = 10;
c.SELECt SUM (sal) FROM emp WHERe deptno = 10 GROUP BY deptno;
d.SELECt SUM (sal) FROM emp HAVINg deptno = 10;
e.SELECt SUM (SAL) FROM emp HAVINg deptno = 10 GROUP BY deptno;
4.下面哪一个查询将显示与名为King的员工在同一个部门工作的所有员工的姓名?
a.SELECt ename FROM emp WHERe ename = ‘KING’;
b.SELECt ename FROM emp WHERe deptno = (SELECt deptno FROM emp WHERe ename = ‘KING’;
c.SELECt ename FROM emp WHERe ename = (SELECt deptno FROM emp WHERe ename = ‘KING’);
d.SELECt ename FROM emp WHERe deptno = (SELECt deptno FROM emp WHERe ename = ‘KING’);
5.下面哪一个查询将显示员工的平均月薪至少为1500美元的部门的名称?
a.SELECt dname,AVERAGE (sal)
FROM dept NATURAL JOIN emp
WHERe AVERAGE (sal) > 1500;
b.SELECt dname,AVERAGE (sal)
FROM dept NATURAL JOIN emp
HAVINg AVERAGE (sal) > 1500;
c.SELECt dname,AVG (sal)
FROM dept NATURAL JOIN emp
WHERe AVG (sal) > 1500;
d.SELECt dname,AVG (sal)
FROM dept NATURAL JOIN emp
GROUP BY dname
HAVINg AVG (sal) > 1500;
6.下面哪一个查询将显示各个员工号的第4个数字?
a.SELECt ename,SUBSTr (empno,4,1) FROM emp;
b.SELECt ename,LENGTH (empno,4) FROM emp;
c.SELECt ename,TRUNC (empno,4) FROM emp;
d.SELECt ename,SOUNDEX (empno,4,1) FROM emp;
7.下面哪一个查询将返回公司中所有员工的月薪的总和?
a.SELECt SUM (sal) FROM emp GROUP BY deptno;
b.SELECt SUM (sal) FROM emp ;
c.SELECt SUM (DISTINCT sal) FROM emp;
d.SELECt TOTAL (sal) FROM emp WHERe sal IS NOT NULL;
8.下面哪一个查询将只返回职务为文员(clerk)的那些员工的姓名?
a.SELECt UPPER (ename) FROM emp
WHERe LOWER (job) = ‘CLERK’;
b.SELECt LOWER (ename) FROM emp
WHERe LOWER (job) = ‘CLERK’;
c.SELECt UPPER (ename) FROM emp
WHERe LOWER (job) = ‘clerk’;
d.SELECt LOWER (ename) FROM emp
WHERe UPPER (job) = ‘clerk’;
9.下面哪一种说法是正确的?
a.组函数用来计算每一行的多个值,而单行函数用来计算每一行的惟一的一个值;
b.包含组函数的查询还必须包含GROUP BY子句;
c.组函数对处理的每一组行都返回一个值,而单行函数对处理的每一行返回一个值;
d.在包含单行函数的查询中不能使用HAVINg子句;
10.下面描述的哪一种情况需要使用子查询?
a.你需要知道工资高于员工Blake的工资的所有员工;
b.你需要知道月薪超过1000美元的所有文员的姓名;
c.你需要在部门30工作的所有员工的列表;
d.你需要了解各个部门的所有员工的平均工资;
11.下面哪一个查询将计算当天的日期与聘用一个员工的日期之间的时间?
a.SELECt ename,MONTH_BETWEEN (SYSDATE,hiredate)
FROM emp;
b.SELECt ename,SYSDATE-hiredate
FROM emp;
c.SELECt ename, DIFF (SYSDATE,hiredate)
FROM emp;
d.SELECt ename,TO_DATE (SYSDATE,hiredate)
FROM emp;
12.下面查询之后的哪一个子句将使查询返回一个出错消息?
SELECt ename
FROM emp
WHERe sal>
(SELECt AVG (sal)
FROM emp
GROUP BY deptno);
a.SELECt ename
b.WHERe sal>
c.SELECT AVG (sal)
d.GROUP BY deptno
13.下面哪一个查询将返回职务相同的员工的数量?
a.SELECT COUNT (*),job FROM emp GROUP BY job;
b.SELECt COUNT (job) FROM emp;
c.SELECt COUNT (DISTINCT job) FROM emp;
d.SELECt SUM (job) FROM emp ;
14.下面哪一个查询将显示员工的最低工资?
a.SELECt MIN (ename) FROM emp;
b.SELECt LOW (ename) FROM emp;
c.SELECt LOWER (sal) FROM emp;
d.SELECt MIN (sal) FROM emp;
e.SELECt MIN (sal) FROM emp GROUP BY job;
15.下面哪一个查询将显示与员工Smith在同一个部门工作但是工资高于Smith的所有员工的姓名?
a.SELECt ename FROM emp
WHERe deptno = ‘SMITH’AND sal> ‘SMITH’;
b.SELECt ename FROM emp
WHERe (deptno,sal) >
(SELECt deptno,sal FROM emp
WHERe ename = ‘SMITH’);
c.SELECt ename FROM emp WHERe deptno =
(SELECt deptno FROM emp WHERe ename = ‘SMITH’)
AND sal >(SELECt sal FROM emp WHERe ename = ‘SMITH’);
d.SELECt ename FROM emp
WHERe (deptno,sal) > ANY
(SELECt deptno,sal FROM emp
WHERe ename = ‘SMITH’);
16.下面哪一个运算符是有效的多行运算符?
a.ANY
b.OR
c.=
d.>
17.下面哪些查询将显示在波士顿工作的所有员工的姓名?选择所有正确的答案.
a.SELECt ename FROM emp NATURAL JOIN dept
WHERe loc = ‘BOSTON’;
b.SELECt ename FROM emp WHERe loc = ‘BOSTON’;
c.SELECt ename FROM dept WHERe loc = ‘BOSTON’;
d.SELECt ename FROM emp WHERe deptno =
(SELECt deptno FROM dept WHERe loc ‘BOSTON’);
e.SELECt ename FROM emp WHERe deptno = ‘BOSTON’;
18.下面哪一个运算符与在一个多行子查询中使用IN运算符是等价的?
a. =ANY
b. =ALL
c. >ANY
d. <ANY
19.假定列可以包含NULL值,那么下面哪些查询将显示公司中获得佣金的员工的数量?选出所有正确的答案.
a.SELECt COUNT (comm) FROM emp;
b.SELECt COUNT (comm) FROM emp
WHERe comm IS NULL;
c.SELECt COUNT (*) FROM emp
WHERe comm IS NOT NULL;
d.SELECt COUNT (*) FROM emp
WHERe comm IS NULL;
20.下面哪一项是单行函数?
a.AVERAGE
b.VARIANCE
c.SUM
d.ADD_MONTHS
