几道Oracle题目

1. Which assertion about the following statements is most true?
SELECT name, region_code||phone_number FROM customers;
SELECT name, CONCAT(region_code,phone_number) FROM
customers;
A. If the region_code is NULL, the first statement will not include that
customer’s phone_number.
B. If the region_code is NULL, the second statement will not include
that customer’s phone_number.
C. Both statements will return the same data.
D. The second statement will raise an exception if the region_code is
NULL for any customer.

 

2. Which function(s) accept arguments of any datatype? Select all that apply.
A. SUBSTR
B. NVL
C. ROUND
D. DECODE
E. SIGN

 

3. What will the following query report?
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;
A. The number of employees in each department, including those
without a deptno.
B. The number of employees in each department, except those without
a deptno.
C. The total number of employees, including those without a deptno.
D. The total number of employees, except those without a deptno.

 

4. Why does the following SELECT statement fail?
SELECT colorname Colour, MAX(cost)
FROM itemdetail
WHERE UPPER(colorname) LIKE '%WHITE%'
GROUP BY colour
HAVING COUNT(*) > 20;
A. A GROUP BY clause cannot contain a column alias.
B. The condition COUNT(*) > 20 should be in the WHERE clause.
C. The GROUP BY clause must contain the group functions used in the
SELECT list.
D. The HAVING clause can only contain the group functions used in
the SELECT list.

 

5. Which two operators are not allowed when using an outer join
between two tables?
A. OR
B. AND
C. IN
D. =

 

6. Which line in the following query contains an error?
1 SELECT deptno, ename, sal
2 FROM emp e1
3 WHERE sal = (SELECT MAX(sal) FROM emp
4 WHERE deptno = e1.deptno
5 ORDER BY deptno);
A. Line 2
B. Line 3
C. Line 4
D. Line 5

 

7. Why does the following query fail? (SCOTT and TIGER are valid
schema names in the database.)
SELECT scott.emp.ename, tiger.dept.dname
FROM scott.emp, tiger.dept
WHERE emp.deptno = dept.deptno
ORDER BY 2, 1;
A. You cannot query two tables belonging to two different owners.
B. You should not specify the schema name to qualify the column
names.
C. The ORDER BY clause should have 1, 2 when specifying by position.
D. An alias name not used to query multiple schema owner tables.

 

8. What is wrong with the following cursor declaration?
CURSOR c1 (pempno IN NUMBER (4)) IS
SELECT EMPNO, ENAME
FROM EMP
WHERE EMPNO = pempno;
A. The INTO clause is missing.
B. Inside the cursor definition, the variable should be preceded with
a colon (:pempno).
C. IN cannot be specified in the cursor definition.
D. The size of the datatype should not be specified in the cursor definition.

 

9. Which line of code has an error? EMPNO is the primary key of the
EMP table.
1 DECLARE
2 v_empno NUMBER (4);
3 v_ename VARCHAR2 (20);
4 BEGIN
5 SELECT empno, ename, salary
6 INTO v_empno, v_ename
7 FROM emp
8 WHERE empno = 1234;
9 END;
A. Line 3
B. Line 5
C. Line 6
D. The code has no error.

 

10. Consider the following PL/SQL block. Which line has an error?
1 DECLARE
2 CURSOR c_emp IS SELECT empno, ename, salary
3 FROM emp
4 WHERE salary < 3500;
5 BEGIN
6 FOR r_emp IN c_emp LOOP
7 UPDATE emp
8 SET salary = salary * 1.25
9 WHERE CURRENT OF c_emp;
10 END LOOP;
11 END;
A. Line 2
B. Line 6
C. Line 9
D. The code has no error.