GRANT update ON inventory TO joe WITH GRANT OPTION;
Which task has been accomplished? (Points: 3)
Only a system privilege was given to user JOE.
Only an object privilege was given to user JOE.
User JOE was granted all privileges on the object.
Both an object privilege and a system privilege were given to user JOE.
2. (TCO A) Which privilege is a system privilege? (Points: 3)
Alter
Execute
References
Create any table
3. (TCO A) Which condition must be met for a user to give SELECT
privileges on the CUSTOMER table to everyone using the PUBLIC keyword?
(Points: 3)
A) Must have SELECT privileges
B) Must own the CUSTOMER table
C) Must have been granted the PUBLIC privilege
D) Must have been granted the SELECT privilege WITH GRANT OPTION
Either B or D
4. (TCO A) You query the database with this command:
SELECT name
FROM employee
WHERE name LIKE '_z_%';
Which names are displayed? (Points: 3)
Names starting with "z"
Names starting with "z" or "Z"
Names containing "z" as the second letter
Names containing "z" as any letter except the first
5. (TCO A) The TEACHER table contains these columns:
ID NUMBER(9)
SALARY NUMBER (7,2)
SUBJECT_ID NUMBER(3)
You need to create a SQL script that will prompt the user to input an
id number and a percent increase value. Each teacher's salary should
be multiplied by the percent increase provided.
Which SQL*Plus script would you use to achieve the desired results? (Points: 3)
UPDATE teacher SET salary = salary *&increase;
UPDATE teacher SET salary = salary*&increase WHERE id=&id;
UPDATE teacher SET salary = salary *&increase WHERE subject_id= ¼ject_id;
6. (TCO A) Which SELECT statement is an equijoin query between two
tables? (Points: 3)
SELECT region.region_name, employee.salary
FROM region, employee
where region.id = employee.region_no;
SELECT region.region_name, employee.salary
FROM region, employee
where region.id = employee.region_no(+);
SELECT region.region_name, employee.salary
FROM region, employee
where employee.salary BETWEEN region.avg_salary AND region.max_salary
SELECT region.region_name, employee_info.last_name
FROM employee region, employee employee_info
WHERE employee_info.id >= region.manager_id;
where region.id = employee.region_no
7. (TCO A) Which character function would you use to return a
specified portion of a character string? (Points: 3)
Concat
Substr
Length
Initcap
8. (TCO A) Which character function would you use to return a
specified portion of a character string? (Points: 3)
CONCAT
SUBSTR
LENGTH
INITCAP
9. (TCO B) Review this SELECT statement:
SELECT ename, emp_number, salary
FROM employee
WHERE dept_number = (SELECT dept_number
FROM department
WHERE location IN('ORLANDO', 'CHICAGO'))
Why may this statement return an error? (Points: 3)
A multiple-row subquery returns one row.
A multiple-column subquery returns one column.
A single-row subquery returns more than one row.
A multiple-row query uses a single-row subquery.
A single-row query uses a multiple-row subquery that returns
only one row.
10. (TCO B) You query the database with this command:
SELECT name, salary, dept_id
FROM employee
WHERE salary >
(SELECT AVG(salary)
FROM employee
WHERE dept_no =
(SELECT dept_no
FROM employee
WHERE last_name =
(SELECT last_name
FROM employee
WHERE salary > 50000)));
Which SELECT clause is evaluated last? (Points: 3)
SELECT dept_no
SELECT last_name
SELECT AVG(salary)
SELECT name, salary, dept_id
11. (TCO B) In which situation would it be most appropriate to use a
self join query? (Points: 3)
The EMPLOYEE and REGION tables have corresponding columns.
The EMPLOYEE and REGION tables have no corresponding columns.
The EMPLOYEE table has two columns that correspond to each other.
The EMPLOYEE table column corresponding to the REGION table
column contains null values for rows that need to be displayed.
12. (TCO C) What happens when you update a table without a WHERE
clause? (Points: 3)
The statement will not execute.
Only the rows specified will be updated.
All of the rows in the table will be updated.
The statement will execute, but no changes will be made.
13. TCO C: You logged onto the database to update the INVENTORY table.
After your session began, you issued three UPDATE commands and then
you issued an ALTER table command to add a column constraint. You were
about to issue the COMMIT command when the system crashed. Which
changes were made to the INVENTORY table? (Points: 3)
Only the UPDATE commands
Only the ALTER TABLE command
Both the UPDATE commands and the ALTER TABLE command
None
14. (TCO C) Which command causes a transaction to end? (Points: 3)
UPDATE
DELETE
INSERT
ROLLBACK
15. (TCO C) Which statement would not cause an implicit COMMIT to
occur? (Points: 3)
CREATE
DROP
ALTER
SAVE
16. TCO C: Which of the following is an advantage of using SAVEPOINTS?
(Points: 3)
It prevents inconsistent data.
It allows one to group code into manageable units.
It prevents one from duplicating a primary key.
It locks rows and prevents other users from updating the same row.
1. TCO D: Examine the table instance chart for the MANUFACTURER table.
Which command would you use to create an index for the
manufacturer_name column? (Points: 3)
CREATE INDEX manufacturer(manufacturer_
CREATE INDEX manufacturer_manufacturer_
CREATE INDEX manufacturer(manufacturer_
manufacturer_manufacturer_
CREATE INDEX manufacturer_manufacturer_
manufacturer(manufacturer_
2. TCO D: When creating a user in Oracle, which of the following
decisions should be made? (Points: 3)
Assigning a temporary tablespace
Assigning a default tablespace
Assigning a password
All of the above
3. TCO D: With which option could a view be created to prevent a user
from updating rows in the base table that are not accessible to the
view? (Points: 3)
Group function
GROUP BY clause
DISTINCT command
WITH CHECK OPTION
4. TCO D: What action could cause an integrity constraint error? (Points: 3)
Adding a primary key
Deleting a foreign key
Adding a parent record
Deleting a parent record
5. TCO D: The TRANSACTION table has six columns. Since you often query
the table with a join to the SALE table, you created an index on five
of the columns in the TRANSACTION table. Which result will occur?
(Points: 3)
Inserts to the table will be slower.
The speed of deletes will be increased.
The size of the TRANSACTION table will be increased.
All queries on the TRANSACTION table will be faster if the it
does not contain a large number of NULL values.
6. TCO D: Which command would you use to remove the MAN_CON_NAME_IDX
index? (Points: 3)
DROP man_con_name_idx;
DELETE man_con_name_idx;
DROP INDEX man_con_name_idx;
DELETE INDEX man_con_name_idx;
7. TCO D: Evaluate this command:
ALTER TABLE inventory
DISABLE CONSTRAINT inventory_id_number_pk CASCADE;
Which task would this command accomplish? (Points: 3)
Delete only the primary key
Disable only the primary key
Alter all dependent integrity constraints
Disable all dependent integrity constraint
8. (TCO D) You issue this command:
SELECT emp_id_seq.CURRVAL
FROM SYS.dual;
Which value(s) is displayed? (Points: 3)
Values of the EMP_ID_SEQ column
Current value of the EMP_ID_SEQ index
Current value of the EMP_ID_SEQ cursor
Current value of the EMP_ID_SEQ sequence
9. TCO D: You issue this command:
SELECT emp_id_seq.CURRVAL
FROM SYS.dual;
Which value(s) is displayed? (Points: 3)
Values of the EMP_ID_SEQ column
Current value of the EMP_ID_SEQ index
Current value of the EMP_ID_SEQ cursor
Current value of the EMP_ID_SEQ sequence
10. (TCO D) You issue this command:
ALTER USER BILL IDENTIFIED BY THE CAT;
Which task has been accomplished? (Points: 3)
A new user has been added.
The user name has been changed.
The user password has been changed.
A password has been added to the user account.
11. TCO E: Given this procedure:
PROCEDURE dept_salary
(v_bonusIN BOOLEAN,
v_raise IN BOOLEAN,
v_issue_check IN OUT BOOLEAN)
IS
BEGIN
v_issue_check := v_bonus OR v_raise;
END;
If v_bonus = TRUE and v_raise = NULL, which value is assigned to
v_issue_check? (Points: 3)
True
False
Null
None
12. (TCO E) Evaluate this PL/SQL block
DECLARE
v_lower NUMBER := 2;
v_upper NUMBER := 100;
v_count NUMBER := 1;
BEGIN
FOR i IN v_lower..v_lower LOOP
INSERT INTO test(results)
VALUES (v_count);
v_count := v_count + 1;
END LOOP;
END;
How many times will the executable statements inside the FOR LOOP
execute? (Points: 3)
0
1
2
98
100
13. TCO E: Evaluate this PL/SQL block:
DECLARE
v_low NUMBER :=1;
v_high NUMBER :=6;
v_count NUMBER :=2;
BEGIN
FOR i IN v_low..v_high LOOP
v_count :=v_count +1;
END LOOP;
END;
How many times will the loop execute? (Points: 3)
0
1
4
6
7
14. (TCO F) The TRANSACTION table has six columns. Since you often
query the table with a join to the SALE table, you created an index on
five of the columns in the TRANSACTION table. Which result will occur?
(Points: 6)
Inserts to the table will be slower.
The speed of deletes will be increased.
The size of the TRANSACTION table will be increased.
All queries on the TRANSACTION table will be faster if it does
not contain a large number of NULL values.
15. TCO H: Review this SELECT statement:
SELECT department "Departments", MAX(salary) "Max Salaries"
FROM employee
WHERE department IN(20, 30, 400)
GROUP BY Departments
HAVING MAX(salary) > 60000;
Why does this statement fail? (Points: 4)
A GROUP BY clause cannot contain a column alias.
The condition "MAX(salary) > 60000" should reside in the WHERE clause.
The GROUP BY clause must contain the group function(s) used in
the SELECT list.
The HAVING clause cannot contain the group function(s) used in
the SELECT list.
16. TCO H: Which clause could you use to restrict the values returned
by a group function? (Points: 4)
WHERE
HAVING
ORDER BY
A group function cannot be restricted.
17. (TCO H ) You create a Top-N query report for the Marketing
Department that displays the account numbers and new balance of the
1500 accounts that have the lowest new balance. The results are sorted
by the new balance from lowest to highest.
Which two SELECT statement clause is included in your query? (Points: 4)
inner query: WHERE ROWNUM
outer query: SELECT account_id "Account", new_balance "Current
Bal", ROWNUM "Rank"
inner query: ORDER BY new_balance
inner query: SELECT account_id, new_balance ROWNUM
1. (TCO A) Given the Zip column defined as VARCHAR2(5), what, if
anything, is wrong with the following SQL statement?
SELECT zip + 100
FROM zipcode;
(Points: 15)
2. (TCO B) Set operators allow specialized operations on tables.
Descibe the INTERSECT operator, and give an example how it could be
used effectively. Discuss or show by example how an equijoin would
accomplish the same result. (Points: 20)
3. (TCO C) Your developers are complaining that the database is
losing data. You are assigned to investigate the problem. After
interviewing the team, you determine that the following actions
occurred.
Delete from emp where deptno = 10;
create table test as select * from emp;
rollback;
delete from dept where deptno = 20;
truncate table test;
rollback;
Write an explanation to management that explains why the data was lost
even though a rollback was executed.
(Points: 20)
4. (TCO D) What are the differences between an object privilege and a
system privilege with respect to grants? Include the WITH GRANT OPTION
and the WITH ADMIN option in your response. (Points: 20)
5. TCO D: You have just granted select access to your STUDENTS table
to all accounts. Your account is D111111. Johnny states he cannot find
your table with the query:
Select * from Students;
1. Explain the reason for this issue.
2. Rewrite the query so Johnny can select from your table.
3. Create an object that allows other users to access the students
table using the above command.
(Points: 20)
1. (TCO F) Evaluate the following query and explain the access method
that the optimizer will use to access the rows in each table.
UPDATE enrollment e
SET final_grade =
(SELECT NVL(AVG(numeric_grade),0)
FROM grade
WHERE e.student_id = student_id AND e.section_id = section_id)
WHERE student_id = 1000 AND section_id = 2000
0 rows updated.
------------------------------
| Id | Operation |
Name |
|-----------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | ENROLLMENT |
| 2 | INDEX UNIQUE SCAN | ENR_PK |
| 3 | SORT AGGREGATE |
|
| 4 | TABLE ACCESS BY INDEX ROWID | GRADE |
| 5 | INDEX RANGE SCAN | GR_PK |
------------------------------
(Points: 20)
2. (TCO F) You are the DBA of a development project. Your developers
are arguing about whether to write a query using a join, a subquery, a
correlated subquery, or a set operator. Each faction claims one method
performs better then the others. Write a brief synopsis that tells the
developer how to prove whether one query performs better than another.
Include the different factors that affect performance for queries.
(Points: 20)
3. TCO F: Explain in detail how indexes improve query performance for
large tables when the indexed column is used in the WHERE clause of
the query. (Points: 20)
4. TCO G: What are some significant issues when using triggers? Be
sure to explain your answers clearly, accurately, and concisely.
(Points: 20)
5.
(TCO H) A table called CUSTOMER_ORDER contains 5,993 rows with an
order total of $10,993,333.98 based on the orders from 4,500
customers. Based on this scenario, how many rows do you think the
following query returns?
SELECT SUM(order_amount) AS "Order Total" FROM customer_order;
Provide an explanation for your answer that indicates you understand
the concept of aggregations.
