Oracle SQL摸底测试

感谢您能抽出几分钟时间来参加本次答题，现在我们就马上开始！

1、数据表:
dept:
deptno 部门编号，dname部门名称
emp:
empno雇员编号，ename雇员名称，job职位，mgr经理，sal工资，deptno
列出工资高于本部门平均水平的员工的部门号，姓名，工资，并按部门号排序

答案：SELECT e.deptno, e.ename, e.sal
FROM emp e
INNER JOIN (
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
) avg_salaries ON e.deptno = avg_salaries.deptno
WHERE e.sal > avg_salaries.avg_sal
ORDER BY e.deptno

解释：题目要求列出工资高于本部门平均水平的员工的部门号，姓名，工资，并按部门号排序。首先需要计算出每个部门的平均工资，可以使用子查询和GROUP BY语句实现。然后使用INNER JOIN将emp表与该子查询连接，连接条件为部门号相同。最后在WHERE语句中过滤出工资高于平均水平的员工，并按部门号排序。

2、数据表:
dept:
deptno 部门编号，dname部门名称
emp:
empno雇员编号，ename雇员名称，job职位， mgr经理, sal工资，deptno部门编号
列出工资高于本部门平均工资的员工数和部门号，按部门号排序

答案：可以使用以下SQL语句：
SELECT e.deptno, COUNT(*) AS num_of_employees
FROM emp e
JOIN (
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
) a ON e.deptno = a.deptno
WHERE e.sal > a.avg_sal
GROUP BY e.deptno
ORDER BY e.deptno

解释：题目要求列出工资高于本部门平均工资的员工数和部门号，并按部门号排序。可以使用内连接（JOIN）将emp表与它自身连接，使用子查询计算出每个部门的平均工资，然后筛选出工资高于本部门平均工资的员工，再按部门号进行分组，统计员工数，最后按部门号进行排序。
具体来说，内连接的子查询部分使用GROUP BY语句按部门编号进行分组，然后使用AVG函数计算出每个部门的平均工资。然后将该子查询与emp表进行连接，并在WHERE语句中筛选出工资高于本部门平均工资的员工。最后，使用GROUP BY语句按部门编号进行分组，并使用COUNT函数统计员工数，同时使用ORDER BY语句按部门编号进行排序。

3、数据表:
dept:
deptno部门编号， dname部门名称
emp:
empno雇员编号， ename雇员名称， job职位，mgr经理， sal工资，deptno部门编号
列出低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数。

答案：可以使用以下SQL语句：

SELECT e1.deptno, e1.ename, e1.sal, COUNT(e2.empno) AS num_lower_salaries
FROM emp e1
LEFT JOIN emp e2 ON e1.deptno = e2.deptno AND e1.sal < e2.sal GROUP BY e1.empno HAVING COUNT(e2.empno) >= 5
ORDER BY e1.deptno, e1.sal DESC

解释：

题目要求列出低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数。可以使用左连接（LEFT JOIN）将emp表与它自身连接，连接条件为部门编号相同且工资低于当前员工。然后使用GROUP BY语句按员工编号进行分组，并使用COUNT函数统计出工资低于当前员工的人数。最后使用HAVING语句筛选出工资低于至少5人的员工，并按部门号和工资降序排列。

注意，在SELECT语句中需要选择部门号、姓名、工资和工资低于当前员工的人数，这里使用了COUNT函数。另外，需要使用LEFT JOIN，因为有些员工可能是其部门中的最高工资，因此不满足连接条件，需要使用LEFT JOIN将其包含进来。

4、数据表:
dept:
deptno部门编号，dname 部门名称
emp:
empno雇员编号，ename雇员名称， job职位，mgr经理，sal工资，deptno部门编号
找出部门10中所有经理和部门20中的所有人的详细资料。

答案：可以使用以下SQL语句：
SELECT * FROM emp WHERE deptno = 10 AND job = '经理'
UNION
SELECT * FROM emp WHERE deptno = 20

解释：
题目要求找出部门10中所有经理和部门20中的所有人的详细资料。可以使用两个SELECT语句，分别筛选出符合条件的数据，然后使用UNION运算符将它们合并在一起。在第一个SELECT语句中，使用AND运算符将部门编号和职位组合起来，筛选出部门编号为10且职位为“经理”的数据；在第二个SELECT语句中，只需要筛选出部门编号为20的数据即可。最终的SQL语句就是上面那个。

5、数据表:
dept:
deptno 部门编号，dname 部门名称
emp:
empno雇员编号， ename雇员名称， job职位， mgr经理， sal工资，deptno部门编号
找出不收取薪金(薪金为0或空)或收取的薪金低于100的雇员；

答案：SELECT * FROM emp WHERE sal IS NULL OR sal = 0 OR sal < 100;

解释：通过WHERE子句中的逻辑运算符，可以把多个条件组合起来，筛选出符合要求的数据。在本题中，需要找到薪金为0或空，或者薪金低于100的雇员，可以使用OR运算符把这两个条件组合起来。其中，sal IS NULL用于判断薪金是否为空，sal = 0用于判断薪金是否为0，sal < 100用于判断薪金是否低于100。因此，完整的SQL语句就是上面那个。

6、那个查询能实现过滤出employees表commission_pct字段为空的数据；
A、SELECT last_name, job_id, salary, commission_pct FROM employees where commission_pct='';
B、SELECT last_name, job_id, salary, commission_pct FROM employees where commission_pct='NULL';
C、SELECT last_name, job_id, salary, commission_pct FROM employees where commission_pct IS NULL;
D、以上都不对;
答案：C

7、TO DATE(01-JAN-1998) - TO_DATE('01-DEC-1996')SQL命令显示的结果是什么类型?
A、数值
B、字符串
C、日期
D、出现错误
答案：C

8、分析以下SQL命令:
SELECT price FROM inventory WHERE price BETWEEN 1 AND 50 AND (price IN(55, 30,95);
命令执行后的输出结果是?
A、55
B、30
C、95
D、NULL
答案：B

9、commission_pct字段为空，12*commission_pct值那个正确
A、12
B、0
C、语法错误
D、空值
答：D

10、如果在where子句中有两个条件要满足其中之一，应该用以下哪个逻辑符来链接？
A、OR
B、NOT
C、AND
D、NONE
答案：A

11、ROUND 和TRUNC函数的区别？
A、都是将数据按指定的长度截断
B、 TRUNC是四舍五入，ROUND是截断
C、ROUND在截掉数据时有四舍五入的判断，TRUNC直接按要求截掉没有进位
D、都会四舍五入
答案：C

12、执行SELECT SUBSTR('How_long_isapieceofstring?',5,4) FROM DUAL;语句之后会返回什么值？
A、long
B、_lond
C、ring?
D、以上都不对
答案：A

13、ORACLE中空值的说法正确的是?
A、空值是0
B、查询时空值显示的是NULL
C、空值是无效的,未指定的
D、查询时空值显示的是空格
答案：B

14、分析以下的SQL命令:
SELECT i.id_number,m.manufacturer_id
FROM inventory i, inventory m
WHERE i.manufacturer_id = m.region_id_number(+);
该命令中使用的连接条件是什么连接?
A、右连接
B、自连接
C、左连接
D、语法错误
答案：A

15、执行下面的语句会返回什么值：SELECT NVL2(NULLIF('CODA','SID'),'SPANIEL','TERRIER')FROM DUAL?
A、以上都不对
B、TERRIER
C、CODA
D、SPANIEL
答案：D

16、语法上看，下面哪些语句是正确？
A、SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D USING(DEPARTMENT_ID)
B、SELECT * FROM EMPLOYEES JOIN DEPARTMENTS D USING(D.DEPARTMENT_ID)
C、SELECT D.DEPARTMENT_ID FROM EMPLOYEES JOIN DEPARTMENTS D USING(DEPARTMENT_ID)
D、以上都不对
答案：A

17、执行下面的语句会返回什么值SELECT COUNT(*),COUNT(SALARY) FROM EMPLOYEES；假设有10个员工记录，除了一条记录的SALARY字段是空值外，每条记录包含的SALARY值都为100。
A、10和10
B、10和 NULL
C、10和9
D、以上都不对
答案：C

18、哪个查询条件能实现first_name中第三个字符是S的数据
A、WHERE first_name like'**S';
B、WHERE first_name like'__S%;
C、WHERE first_name like 'S%__'
D、WHERE first_name like '_S%';
答案：B

19、用以下哪个子句来限制分组统计结果信息的显示?
A、WHERE
B、GROUP BY
C、ORDER BY
D、HAVING
答案：D