Section 1
(Answer all questions in this section)
1. When you use the SELECT clause to list one or two columns only from a Mark for
table and no WHERE clause, which SQL capability is used?
Review
(1) Points
Projection and Selection
Joining only
Selection only
Projection only (*)
Correct.
2. In a SELECT clause, what is the result of 2 + 3 * 2? Mark for
Review
(1) Points
13
10
8 (*)
Correct.
3. The _______ clause can be added to a SELECT statement to return a Mark for
subset of the data.
Review
(1) Points
WHERE (*)
WHICH
ANYWHERE
EVERY
Correct.
4. All computers in the world speak the same languages, so you only need Mark for
to learn one programming language - Oracle SQL. True or False?
Review
(1) Points
True
False (*)
Correct.
5. The basic storage structure in a Relational Database is a _________: Mark for
Review
(1) Points
Key
Field
Row
Table (*)
Correct
Section 2
(Answer all questions in this section)
6. When using the LIKE condition to search for _ symbols, which Mark for
character can you use as the default ESCAPE option?
Review
(1) Points
&
\ (*)
Incorrect. See Section 2 Lesson 1.
7. Which comparison operator searches for a specified character Mark for
pattern?
Review
(1) Points
LIKE (*)
IS NULL
IN
BETWEEN...AND...
Incorrect. See Section 2 Lesson 1.
8. Which of the following WHERE clauses would not select the number Mark for
10?
Review
(1) Points
WHERE hours IN (8,9,10)
WHERE hours <= 10
WHERE hours <>10 (*)
WHERE hours BETWEEN 10 AND 20
Correct
9. When using the "LIKE" operator, the % and _ symbols can be used to Mark for
do a pattern-matching, wild card search. True or False?
Review
(1) Points
True (*)
False
Correct
10. The PLAYERS table contains these columns: Mark for
PLAYER_ID NUMBER(9)
LAST_NAME VARCHAR2(20) Review
FIRST_NAME VARCHAR2 (20) (1) Points
TEAM_ID NUMBER (4)
MANAGER_ID NUMBER (9)
POSITION_ID NUMBER (4)
Which SELECT statement should you use if you want to display unique
combinations of the TEAM_ID and MANAGER_ID columns?
SELECT team_id, manager_id DISTINCT FROM players;
SELECT team_id, DISTINCT manager_id FROM players;
SELECT team_id, manager_id FROM players;
SELECT * FROM players;
SELECT DISTINCT team_id, manager_id FROM players; (*)
Correct.
11. Evaluate this Mark for Review
SELECT
statement:
(1) Points
SELECT *
FROM
employees
WHERE
department_i
d IN(10, 20,
30)
AND salary >
20000;
Which values
would cause
the logical
condition to
return TRUE?
DEPARTMENT_ID = 20 and SALARY = 20000
DEPARTMENT_ID = 10 and SALARY = 20000
DEPARTMENT_ID = 10 and SALARY = 20001 (*)
DEPARTMENT_ID = null and SALARY = 20001
Correct.
Section 3
(Answer all questions in this section)
12. The conversion function TO_CHAR is a single row function. Mark for
True or False?
Review
(1) Points
True (*)
False
Correct
13. Which of the following is earliest in the rules of precedence? Mark for
Review
(1) Points
Comparison condition
Logical condition
Concatenation operator
Arithmetic operator (*)
Correct
14. Which columns can be added to the ORDER BY clause in the Mark for
following SELECT statement? (Choose Three)
SELECT first_name, last_name, salary, hire_date Review
FROM employees (1) Points
WHERE department_id = 50
ORDER BY ?????;
(Choose all correct answers)
last_name, first_name (*)
The table name, EMPLOYEES, which would then
automatically sort by all columns in the table
All the columns in the database
All columns in the EMPLOYEES table (*)
Any column in the EMPLOYEES table, any expression in
the SELECT list or any ALIAS in the SELECT list (*)
Correct
15. You attempt to query the database with this SQL statement: Mark for
SELECT product_id "Product Number", category_id
"Category", price "Price" Review
FROM products (1) Points
WHERE "Category" = 5570
ORDER BY "Product Number";
This statement fails when executed. Which clause contains a
syntax error?
SELECT product_id "Product Number", category_id
"Category", price "price"
FROM products
ORDER BY "Product Number";
WHERE "Category" = 5570 (*)
Correct.
Section 4
(Answer all questions in this section)
16. Which of the following SQL statements will correctly display the last Mark for
name and the number of weeks employed for all employees in
department 90?
Review
(1) Points
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
(*)
SELECT last_name, # of WEEKS
FROM employees
WHERE department_id = 90;
SELECT last name, (SYSDATE-hire_date)/7 DISPLAY WEEKS
FROM employees
WHERE department id = 90;
SELECT last_name, (SYSDATE-hire_date)AS WEEK
FROM employees
WHERE department_id = 90;
Correct
17. You issue this SQL statement: Mark for
SELECT ROUND (1282.248, -2) FROM dual;
What value does this statement produce? Review
(1) Points
1200
1282
1282.25
1300 (*)
Correct
18. What will the following SQL statemtent display? Mark for
SELECT last_name, LPAD(salary, 15, '$')SALARY
FROM employees; Review
(1) Points
The query will result in an error: "ORA-00923: FROM keyword not
found where expected."
The last name of employees that have a salary that includes a $
in the value, size of 15 and the column labeled SALARY.
The last name and the format of the salary limited to 15 digits to
the left of the decimal and the column labeled SALARY.
The last name and salary for all employees with the format of the
salary 15 characters long, left-padded with the $ and the column
labeled SALARY. (*)
Correct
19. You query the database with this SQL statement: Mark for
SELECT CONCAT(last_name, (SUBSTR(LOWER(first_name), 4)))
"Default Password" Review
FROM employees; (1) Points
Which function will be evaluated first?
CONCAT
SUBSTR
LOWER (*)
All three will be evaluated simultaneously.
Correct
Section 5
(Answer all questions in this section)
20. Which best describes the TO_CHAR function? Mark for
Review
(1) Points
The TO_CHAR function can be used to remove text from column
data that will be returned by the database.
The TO_CHAR function can be used to display dates and numbers
according to formatting conventions that are supported by Oracle.
(*)
The TO_CHAR function can only be used on Date columns.
The TO_CHAR function can be used to specify meaningful column
names in an SQL statement's result set.
Correct
21. A table has Mark for Review
the following
definition:
EMPLOYEES( (1) Points
EMPLOYEE_ID
NUMBER(6)
NOT NULL,
NAME
VARCHAR2(2
0) NOT NULL,
MANAGER_ID
VARCHAR2(6)
)
and contains
the following
rows:
(1001, 'Bob
Bevan', '200')
(200,
'Natacha
Hansen', null)
Will the
folloiwng
query work?
SELECT *
FROM
employees
WHERE
employee_id
=
manager_id;
No, because the datatypes of EMPLOYEE_ID and
MANAGER_ID are different.
Yes, Oracle will perform implicit dataype conversion,
and the query will return one row of data.
No. You will have to re-wirte the statement and perform
explicit datatype conversion.
Yes, Oracle will perform implicit datatype conversion,
but the WHERE clause will not find any matching data.
(*)
Correct
22. You have been asked to create a report that lists all Mark for
customers who have placed orders of at least $2,500. The
report's date should be displayed using this format:
Day, Date Month, Year (For example, Tuesday, 13 April, 2004 Review
). (1) Points
Which statement should you issue?
SELECT companyname, TO_CHAR (sysdate, 'fmDay, dd
Month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
(*)
SELECT companyname, TO_CHAR (sysdate, 'fmdd, dy
month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_DATE (date, 'day, dd month,
yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
SELECT companyname, TO_DATE (sysdate, 'dd, dy
month, yyyy'), total
FROM customers NATURAL JOIN orders
WHERE total >= 2500;
Correct
23. For the given data from Employees (last_name, manager_id) Mark for
what is the result of the following statement:
DATA:( King, null
Kochhar, 100 Review
De Haan, 100 (1) Points
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees
King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
(*)
Invalid statement.
King, Null
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other
King, A N Other
Kochhar, King
De Haan, King
Hunold, Kochhar
Ernst, De Haan
Correct
24. When executed, which statement displays a zero if the Mark for
TUITION_BALANCE value is zero and the HOUSING_BALANCE
value is null?
Review
(1) Points
SELECT NVL(tuition_balance, 0), NVL (housing_balance),
tuition_balance + housing_balance "Balance Due"
FROM student_accounts;
SELECT NVL (tuition_balance + housing_balance, 0)
"Balance Due"
FROM student_accounts;
(*)
SELECT TO_NUMBER(tuition_balance, 0), TO_NUMBER
(housing_balance, 0), tutition_balance +
housing_balance "Balance Due"
FROM student_accounts;
SELECT tuition_balance + housing_balance
FROM student_accounts;
Correct
25. If quantity is a number datatype, what is the result of this Mark for
statement?
SELECT NVL(200/quantity, 'zero') FROM inventory;
Review
(1) Points
The statement fails (*)
Null
zero
ZERO
Correct
Section 6
(Answer all questions in this section)
26. Evaluate this SELECT statement: Mark for
SELECT *
FROM employee e, employee m Review
WHERE e.mgr_id = m.emp_id; (1) Points
Which type of join is created by this SELECT statement?
a left outer join
a self join (*)
a full outer join
a cross join
Correct
27. Which SELECT statement implements a self join? Mark for
Review
(1) Points
SELECT p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id = t.product_id;
SELECT p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id = t.product_id (+);
SELECT p.part_id, t.product_id
FROM part p, part t
WHERE p.part_id = t.product_id;
(*)
SELECT p.part_id, t.product_id
FROM part p, product t
WHERE p.part_id =! t.product_id;
Correct
28. A NATURAL JOIN is based on: Mark for
Review
(1) Points
Columns with the same name
Columns with the same name and datatype (*)
Columns with the same datatype and width
Tables with the same structure
Correct
29. Which statement about a natural join is true? Mark for
Review
(1) Points
Columns with the same names cannot be included in the SELECT
list of the query.
Columns with the same names must have compatible data types.
Columns with the same names must have identical data types.
Columns with the same names must have the same precision and
datatype. (*)
Correct
30. What is another name for a simple join or an inner join? Mark for
Review
(1) Points
Nonequijoin
Equijoin (*)
Self Join
Outer Join
Correct
31. What Mark for Review
types of
joins will
return the (1) Points
unmatche
d values
from both
tables in
the join?
Natural joins
Right outer joins
Full outer joins (*)
Left outer joins
Correct
32. Evaluate this SELECT statement: Mark for
SELECT a.lname || ', ' || a.fname as "Patient", b.lname || ', ' ||
b.fname as "Physician", c.admission Review
FROM patient a (1) Points
JOIN physician b
ON (b.physician_id = c.physician_id)
JOIN admission c
ON (a.patient_id = c.patient_id);
Which clause generates an error?
JOIN admission c
ON (b.physician_id = c.physician_id); (*)
JOIN physician b
ON (a.patient_id = c.patient_id)
Correct
Section 7
(Answer all questions in this section)
33. Nonequijoins are normally used with which of the following? Mark for
(Choose two)
Review
(1) Points
(Choose all correct answers)
ranges of columns
Ranges of rowids
Ranges of text
Ranges of numbers (*)
Ranges of dates (*)
Correct
34. You have been asked to create a report that lists all corporate Mark for
customers and all orders that they have placed. The customers
should be listed alphabetically beginning with the letter 'A', and
their corresponding order totals should be sorted from the Review
highest amount to the lowest amount. (1) Points
Which of the following statements should you issue?
SELECT c.custid, c.companyname, o.orderdate, o. custid,
o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname ASC, amount ASC;
SELECT c.custid, c.companyname, o.orderdate, o. custid,
o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname, amount DESC;
(*)
SELECT c.custid, c.companyname, o.orderdate, o. custid,
o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY amount DESC, companyname;
SELECT c.custid, c.companyname, o.orderdate, o. custid,
o.amount
FROM customers c, orders o
WHERE c.custid = o.custid
ORDER BY companyname, amount;
Correct
35. What is the minimum number of join conditions required to join Mark for
5 tables together?
Review
(1) Points
4 (*)
One more than the number of tables
Correct
Section 8
(Answer all questions in this section)
36. Which group function would you use to display the highest salary value Mark for
in the EMPLOYEES table?
Review
(1) Points
MAX (*)
MIN
AVG
COUNT
Correct
37. Which group functions below act on character, number, and date data Mark for
types? (Choose all that are correct.)
Review
(1) Points
(Choose all correct answers)
COUNT (*)
SUM
MIN (*)
AVG
MAX (*)
Correct
38. The EMPLOYEES table contains these columns: Mark for
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(20) Review
FIRST_NAME VARCHAR2(20) (1) Points
SALARY NUMBER(7,2)
DEPARTMENT_ID NUMBER(9)
You need to display the number of employees whose salary is greater
than $50,000? Which SELECT would you use?
SELECT * FROM employees
WHERE salary > 50000;
SELECT * FROM employees
WHERE salary < 50000;
SELECT COUNT(*)
FROM employees
WHERE salary > 50000;
(*)
SELECT COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY employee_id, last_name, first_name, salary,
department_id;
SELECT COUNT(*)
FROM employees
WHERE salary < 50000;
Correct
Section 9
(Answer all questions in this section)
39. To control the order of rows returned using SET operators, the ORDER Mark for
BY clause is used ______ and is placed in the _____ SELECT statement of
the query.
Review
(1) Points
ONCE; FIRST
IN ALL; LAST
ONCE; LAST (*)
TWICE; FIRST
Correct
40. What will the following SQL Statement do? Mark for
SELECT job_id, COUNT(*)
FROM employees Review
GROUP BY job_id; (1) Points
Displays each job id and the number of people assigned to that job
id (*)
Displays all the employees and groups them by job
Displays only the number of job_ids
Displays all the jobs with as many people as there are jobs
Correct
Section 9
(Answer all questions in this section)
41. What is the best explanation as to why this SQL statement will NOT Mark for
execute?
SELECT department_id "Department", AVG (salary)"Average" Review
FROM employees (1) Points
GROUP BY Department;
Salaries cannot be averaged as not all the numbers will divide
evenly.
The department id is not listed in the departments table.
The GROUP BY clause must have something to GROUP.
You cannot use a column alias in the GROUP BY clause. (*)
Correct
42. The PRODUCTS table contains these columns: Mark for
PRODUCT_ID NUMBER(9) PK
CATEGORY_ID VARCHAR2(10) Review
LOCATION_ID NUMBER(9) (1) Points
DESCRIPTION VARCHAR2(30)
COST NUMBER(7,2)
PRICE NUMBER(7,2)
QUANTITY NUMBER
You display the total of the extended costs for each product category
by location.
You need to include only the products that have a price less than
$25.00.
The extended cost of each item equals the quantity value multiplied by
the cost value.
Which SQL statement will display the desired result?
SELECT category_id, SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY category_id, location_id;
(*)
SELECT SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY location_id;
SELECT category_id, SUM(cost * quantity) TOTAL,location_id
FROM products
WHERE price > 25.00
GROUP BY category_id, location_id;
SELECT SUM(cost * quantity) TOTAL
FROM products
WHERE price < 25.00;
Correct
43. CUBE can be applied to all aggregate functions including AVG, SUM, Mark for
MIN, MAX, and COUNT. True or False?
Review
(1) Points
True (*)
False
Correct
44. If you want to include subtotals and grand totals for all columns Mark for
mentioned in a GROUP BY clause, you should use which of the following
extensions to the GROUP BY clause?
Review
(1) Points
ROLLUP
CUBE (*)
GROUP BY ALL COLUMNS
HAVING
Correct
Section 10
(Answer all questions in this section)
45. Which statement about subqueries is true? Mark for
Review
(1) Points
Subqueries generally execute last, after the main or outer query
executes.
Subqueries should be enclosed in double quotation marks.
Subqueries cannot contain group functions.
Subqueries are often used in a WHERE clause to return values for
an unknown conditional value. (*)
Correct
Section 10
(Answer all questions in this section)
46. Correlated Subqueries must reference the same tables in both the inner Mark for
and outer queries. (True or False?)
Review
(1) Points
True
False (*)
Correct
47. You need to create a SELECT statement that contains a multiple-row Mark for
subquery. Which comparison operator(s) can you use?
Review
(1) Points
BETWEEN AND
IN, ANY, and ALL (*)
LIKE
=, <, and >
Correct
48. Evaluate this SELECT statement: Mark for
Review
SELECT student_id, last_name, first_name (1) Points
FROM student
WHERE major_id NOT IN
(SELECT major_id
FROM majors
WHERE department_head_id = 30 AND title = 'ADJUNCT');
What would happen if the inner query returned a NULL value row?
All the rows in the STUDENT table would be displayed.
No rows would be returned from the STUDENT table. (*)
Only the rows with STUDENT_ID values equal to NULL would be
displayed.
A syntax error would be returned.
Correct
49. The SQL multiple-row subquery extends the capability of the single-row Mark for
syntax through the use of which three comparison operators?
Review
(1) Points
IN, ANY, and ALL (*)
IN, ALL, and EVERY
IN, ANY, and EVERY
IN, ANY, and EQUAL
Correct
50. Single row subqueries may not include this operator: Mark for
Review
(1) Points
<>
ALL (*)
>
Correct
