update 07/15/2002
IZ0-007 - Introduction to oracle 9i:SQL
Please note that the answers may not be correct.
1.Based on the following conditions, which of the following will make a true condition?
last_name LIKE Ma%
A. MADNESS
B. Man
C. MARY
D. mary
E. Mandy
Ans:B,E
2.What will happen if you call a SQL function with an argument of a datatype other than the datatype expected by a SQL function?
A. the argument will need to be converted explicitly
B. the argument will be rejected
C. the argument will be accepted as is
D. the argument will be converted implicitly
Ans:D
3.Which of the following correctly describe the aggregate functions?
A. None of the choices.
B. It returns a single result row based on single rows
C. It can only appear in ORDER BY clauses
D. It returns a single result row based on groups of rows
E. It cannot appear in select lists
Ans:D
4.What condition can you use for testing that involves pattern matching?
Ans:LIKE
5.Which of the following queries can you use to search for employees with the pattern A_B in their names?
A. SELECT last_name
FROM employees
WHERE last_name LIKE %A_B% ESCAPE ;
B. SELECT last_name
FROM employees
WHERE last_name LIKE A_B% ESCAPE %;
C. SELECT last_name
FROM employees
WHERE last_name LIKE %A\_B% ESCAPE \;
D. SELECT last_name
FROM employees
WHERE last_name LIKE %A\_B% ESCAPE \\;
Ans:C
6.Which of the following SQL statements can calculate and return the absolute value of -33?
A. SELECT ABS(-33) "Absolute" FROM DUAL;
B. SELECT ABS(-33), Absolute FROM DUAL;
C. SELECT ABS(-33) "Absolute" FROM DUAL;
D. SELECT ABS("-33") Absolute FROM DUAL;
Ans:C
7.What condition can you use to test for the existence of rows in a subquery:
Ans:EXISTS
8.Refer to the SQL codes below:
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
FROM employees;
What has been achieved?
A. it calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager
B. it calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee
C. because of a syntax problem, no row will be returned
D. it calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just after the employee
E. it calculates, for each employee in the employees table, the average salary of the employees reporting to his/her respective manager who were hired just before the employee
Ans:B
9.What does the following SQL script do?
SELECT AVG(MAX(salary)) FROM employees GROUP BY department_id;
A. it has a syntax error, the AVG clause is not valid
B. it calculates the average of the maximum salaries of all the departments
C. it has a syntax error, the MAX clause is not valid
D. it has no error, but the GROUP BY clause is not effective
E. it has no error, but the AVG clause is not effective
F. it has no error, but the MAX clause is not effective
Ans:B
10.Which of the following are the valid ordering sequences of an order by clause?
A. ASEC
B. DESC
C. REVERT
D. RANDOM
E. ASC
Ans:B,E
1.A condition in the WHERE clause of which of the following statements is NOT valid?
A. HAVING
B. START WITH
C. DELETE
D. SELECT
E. UPDATE
Ans:A,B
2.A condition in which of the following clauses of the SELECT statement is NOT valid?
A. WHERE
B. None of the choices.
C. START WITH
D. HAVING
E. CONNECT BY
Ans:B
3.Which of the following has been achieved by the following SQL codes?
SELECT * FROM employees
WHERE hire_date
AND salary 3500;
A. compile time error
B. only those hired after 1999 and earning more than $3500 a month are returned
C. only those hired before 1999 and earning more than $3500 a month are returned
D. runtime error
E. only those hired before 1999 and earning less than $3500 a month are returned
Ans:C
4.Which of the following has been achieved by the following SQL codes?
SELECT employee_id FROM employees
WHERE commission_pct = .5 OR salary 23000;
A. it returns employees who have a 50% commission rate or a salary greater than $23,000:
B. it returns employees who have a 50% of a salary greater than $23,000:
C. runtime error
D. invalid syntax
E. it returns employees who have a 50% commission rate and a salary greater than $23,000:
F. it returns employees who have a 50% of a salary less than $23,000:
Ans:A
5.You have the following SQL statements:
SELECT * FROM employees
You want to retrieve those who have salaries between $1000 and $1500. What clause can you use?
A. OR … BETWEEN
B. AND … BETWEEN
C. BETWEEN … AND
D. IN … BETWEEN
E. BETWEEN … OR
Ans:C
6.Which of the following matches are correct?
A. UNION - returns all rows selected by either query.
B. UNION ALL - returns all rows selected by either query, including all duplicates.
C. INTERSECT - returns all distinct rows selected by both queries.
D. MINUS - returns all distinct rows selected by the first query but not the second.
Ans:A,B,C,D
7.A scalar subquery expression can be placed in which of the following places?
A. WHEN conditions of a CASE expression
B. GROUP BY clause
C. HAVING clause
D. RETURNING clause of a DML statement
E. CHECK constraints
Ans:A,B,C,D,E
8.User-defined operators can be created with what SQL statement?
A. CREATE SCHEMA OPER
B. This cannot be done with SQL
C. CREATE OPERATOR
D. CREATE USER-OPER
E. CREATE OPER
Ans:C
9.You can use expressions in which of the following conditions?
A. The VALUES clause of the INSERT statement
B. The SET clause of the UPDATE statement
C. The ORDER BY clause
D. The select list of the SELECT statement
E. A con
