Section
12
(Answer all questions in this section)
(Answer all questions in this section)
1. The
DEFAULT keyword can be used in the following statements: Mark for Review
(1) Points
(1) Points
INSERT
and UPDATE (*)
INSERT and DELETE
DELETE and UPDATE
All of the above
INSERT and DELETE
DELETE and UPDATE
All of the above
Incorrect
Incorrect. Refer to Section 12 Lesson 3.
2. Multi-table inserts can be conditional or unconditional. True or False? Mark for Review
(1) Points
2. Multi-table inserts can be conditional or unconditional. True or False? Mark for Review
(1) Points
True (*)
False
False
Correct
Correct
3. You need to update the area code of employees that live in Atlanta. Evaluate this partial UPDATE statement:
UPDATE employee
SET area_code = 770
3. You need to update the area code of employees that live in Atlanta. Evaluate this partial UPDATE statement:
UPDATE employee
SET area_code = 770
Which of
the following should you include in your UPDATE statement to achieve the
desired results?
Mark for
Review
(1) Points
(1) Points
UPDATE
city = Atlanta;
SET city = ‘Atlanta’;
LIKE ‘At%’;
WHERE city = ‘Atlanta’; (*)
SET city = ‘Atlanta’;
LIKE ‘At%’;
WHERE city = ‘Atlanta’; (*)
Correct
Correct
4. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)
4. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)
You need
to increase the salary for all employees in department 10 by 10 percent. You
also need to increase the bonus for all employees in department 10 by 15
percent. Which statement should you use?
Mark for
Review
(1) Points
(1) Points
UPDATE
employees
SET salary = salary * .10, bonus = bonus * .15
WHERE department_id = 10;
UPDATE employees
SET (salary = salary * 1.10) SET (bonus = bonus * 1.15)
WHERE department_id = 10;
UPDATE employees
SET salary = salary * 1.10 AND bonus = bonus * 1.15
WHERE department_id = 10;
UPDATE employees
SET salary = salary * 1.10, bonus = bonus * 1.15
WHERE department_id = 10;
(*)
SET salary = salary * .10, bonus = bonus * .15
WHERE department_id = 10;
UPDATE employees
SET (salary = salary * 1.10) SET (bonus = bonus * 1.15)
WHERE department_id = 10;
UPDATE employees
SET salary = salary * 1.10 AND bonus = bonus * 1.15
WHERE department_id = 10;
UPDATE employees
SET salary = salary * 1.10, bonus = bonus * 1.15
WHERE department_id = 10;
(*)
Incorrect
Incorrect. Refer to Section 12 Lesson 2.
5. One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2 (20)
HIRE_DATE DATE
SALARY NUMBER(10)
5. One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2 (20)
HIRE_DATE DATE
SALARY NUMBER(10)
Which
UPDATE statement will accomplish your objective?
Mark for
Review
(1) Points
(1) Points
UPDATE
employees
SET last_name = ‘cooper’
WHERE last_name = ‘roper’; (*)
UPDATE employees
SET last_name = ‘roper’
WHERE last_name = ‘cooper’;
UPDATE employees last_name = ‘cooper’
WHERE last_name = ‘roper’;
UPDATE employees
SET cooper = ‘last_name’
WHERE last_name = ‘roper’;
SET last_name = ‘cooper’
WHERE last_name = ‘roper’; (*)
UPDATE employees
SET last_name = ‘roper’
WHERE last_name = ‘cooper’;
UPDATE employees last_name = ‘cooper’
WHERE last_name = ‘roper’;
UPDATE employees
SET cooper = ‘last_name’
WHERE last_name = ‘roper’;
Correct
Correct
=============
=============
6. You
need to remove a row from the EMPLOYEES table. Which statement would you use?
Mark for Review
(1) Points
(1) Points
DELETE
with a WHERE clause (*)
INSERT with a WHERE clause
UPDATE with a WHERE clause
MERGE with a WHERE clause
INSERT with a WHERE clause
UPDATE with a WHERE clause
MERGE with a WHERE clause
Correct
Correct
7. Assume all the column names are correct. The following SQL statement will execute which of the following?
INSERT INTO departments
(department_id, department_name, manager_id, location_id)
VALUES (70, ‘Public Relations’, 100, 1700);
7. Assume all the column names are correct. The following SQL statement will execute which of the following?
INSERT INTO departments
(department_id, department_name, manager_id, location_id)
VALUES (70, ‘Public Relations’, 100, 1700);
Mark for
Review
(1) Points
(1) Points
70 will
be inserted into the department_id column. (*)
100 will be inserted into the department_id column.
1700 will be inserted into the manager_id column.
‘Public Relations’ will be inserted into the manager_name column.
100 will be inserted into the department_id column.
1700 will be inserted into the manager_id column.
‘Public Relations’ will be inserted into the manager_name column.
Correct
Correct
8. The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL
8. The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL
Which
INSERT statement will execute successfully?
Mark for
Review
(1) Points
(1) Points
INSERT
INTO products
VALUES (2958, ‘Cable’, 8690, 7.09, 4.04, SYSDATE);
INSERT INTO products(product_id, product_name)
VALUES (2958, ‘Cable’);
INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
VALUES(2958, ‘Cable’, 8690, 7.09, 4.04, 700) (*)
INSERT INTO products(product_id, product_name, supplier_id
VALUES (2958, ‘Cable’, 8690, SYSDATE);
VALUES (2958, ‘Cable’, 8690, 7.09, 4.04, SYSDATE);
INSERT INTO products(product_id, product_name)
VALUES (2958, ‘Cable’);
INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
VALUES(2958, ‘Cable’, 8690, 7.09, 4.04, 700) (*)
INSERT INTO products(product_id, product_name, supplier_id
VALUES (2958, ‘Cable’, 8690, SYSDATE);
Correct
Correct
Section 13
(Answer all questions in this section)
Section 13
(Answer all questions in this section)
9. Which
data types stores variable-length character data? Select two. Mark for Review
(1) Points
(1) Points
(Choose
all correct answers)
NCHAR
VARCHAR2 (*)
CHAR
CLOB (*)
VARCHAR2 (*)
CHAR
CLOB (*)
Correct
Correct
10. The TIMESTAMP data type allows what? Mark for Review
(1) Points
10. The TIMESTAMP data type allows what? Mark for Review
(1) Points
Time to
be stored as an interval of years and months.
Time to be stored as a date with fractional seconds. (*)
Time to be stored as an interval of days to hours, minutes and seconds.
None of the above.
Time to be stored as a date with fractional seconds. (*)
Time to be stored as an interval of days to hours, minutes and seconds.
None of the above.
Correct
Correct
==============
11. Once they are created, external tables are accessed with normal SQL statements. (True or False?) Mark for Review
(1) Points
==============
11. Once they are created, external tables are accessed with normal SQL statements. (True or False?) Mark for Review
(1) Points
True (*)
False
False
Correct
Correct
12. When creating a new table, which of the following naming rules apply. (Choose three) Mark for Review
(1) Points
12. When creating a new table, which of the following naming rules apply. (Choose three) Mark for Review
(1) Points
(Choose
all correct answers)
Must be
between 1 to 30 characters long (*)
Must begin with a letter (*)
Must contain ONLY A – Z, a – z, 0 – 9, _ (underscore), $, and # (*)
Can have the same name as another object owned by the same user
Must be an Oracle reserved word
Must begin with a letter (*)
Must contain ONLY A – Z, a – z, 0 – 9, _ (underscore), $, and # (*)
Can have the same name as another object owned by the same user
Must be an Oracle reserved word
Incorrect
Incorrect. Refer to Section 13 Lesson 1.
13. CREATE TABLE bioclass
(hire_date DATE DEFAULT SYSDATE,
first_name varchar2(15),
last_name varchar2(15));
The above CREATE TABLE statement is acceptable, and will create a Table named bioclass that contains a hire_date, first_name, and last_name column. True or False?
13. CREATE TABLE bioclass
(hire_date DATE DEFAULT SYSDATE,
first_name varchar2(15),
last_name varchar2(15));
The above CREATE TABLE statement is acceptable, and will create a Table named bioclass that contains a hire_date, first_name, and last_name column. True or False?
Mark for
Review
(1) Points
(1) Points
True (*)
False
False
Correct
Correct
14. Evaluate this statement:
Which statement about this TRUNCATE TABLE statement is true? Mark for Review
(1) Points
14. Evaluate this statement:
Which statement about this TRUNCATE TABLE statement is true? Mark for Review
(1) Points
You can
produce the same results by issuing the ‘DELETE employees’ statement.
You can reverse this statement by issuing the ROLLBACK statement.
You can issue this statement to retain the structure of the employees table. (*)
You can produce the same results by issuing the ‘DROP TABLE employee’ statement.
You can reverse this statement by issuing the ROLLBACK statement.
You can issue this statement to retain the structure of the employees table. (*)
You can produce the same results by issuing the ‘DROP TABLE employee’ statement.
Correct
Correct
15. You can use the ALTER TABLE statement to: Mark for Review
(1) Points
15. You can use the ALTER TABLE statement to: Mark for Review
(1) Points
Add a
new column
Modify an existing column
Drop a column
All of the above (*)
Modify an existing column
Drop a column
All of the above (*)
Incorrect
Incorrect. Refer to Sectio
===========
Section 13
(Answer all questions in this section)
===========
Section 13
(Answer all questions in this section)
16. Your
supervisor has asked you to modify the AMOUNT column in the ORDERS table. He
wants the column to be configured to accept a default value of 250. The table
contains data that you need to keep. Which statement should you issue to
accomplish this task? Mark for Review
(1) Points
(1) Points
DELETE
TABLE orders;
CREATE TABLE orders
(orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,
customerid varchar2(5) REFERENCES customers (customerid),
orderdate date,
amount DEFAULT 250)
ALTER TABLE orders
CHANGE DATATYPE amount TO DEFAULT 250;
DROP TABLE orders;
CREATE TABLE orders
(orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,
customerid varchar2(5) REFERENCES customers (customerid),
orderdate date,
amount DEFAULT 250);
ALTER TABLE orders
MODIFY (amount DEFAULT 250);
(*)
CREATE TABLE orders
(orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,
customerid varchar2(5) REFERENCES customers (customerid),
orderdate date,
amount DEFAULT 250)
ALTER TABLE orders
CHANGE DATATYPE amount TO DEFAULT 250;
DROP TABLE orders;
CREATE TABLE orders
(orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,
customerid varchar2(5) REFERENCES customers (customerid),
orderdate date,
amount DEFAULT 250);
ALTER TABLE orders
MODIFY (amount DEFAULT 250);
(*)
Incorrect
Incorrect. Refer to Section 13 Lesson 3.
17. The previous administrator created a table named CONTACTS, which contains outdated data. You want to remove the table and its data from the database. Which statement should you issue? Mark for Review
(1) Points
17. The previous administrator created a table named CONTACTS, which contains outdated data. You want to remove the table and its data from the database. Which statement should you issue? Mark for Review
(1) Points
ALTER
TABLE
DELETE
TRUNCATE TABLE
DROP TABLE (*)
DELETE
TRUNCATE TABLE
DROP TABLE (*)
Correct
Correct
18. The PLAYERS table contains these columns:
PLAYER_ID NUMBER(9) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
TEAM_ID NUMBER(4)
SALARY NUMBER(9,2)
18. The PLAYERS table contains these columns:
PLAYER_ID NUMBER(9) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
TEAM_ID NUMBER(4)
SALARY NUMBER(9,2)
Which
statement should you use to decrease the width of the FIRST_NAME column to 10
if the column currently contains 1500 records, but none are longer than 10
bytes or characters?
Mark for
Review
(1) Points
(1) Points
ALTER
TABLE players
MODIFY (first_name VARCHAR2(10));
(*)
ALTER TABLE players
RENAME first_name VARCHAR2(10);
ALTER players TABLE
MODIFY COLUMN (first_name VARCHAR2(10));
ALTER players TABLE
MODIFY COLUMN first_name VARCHAR2(10);
MODIFY (first_name VARCHAR2(10));
(*)
ALTER TABLE players
RENAME first_name VARCHAR2(10);
ALTER players TABLE
MODIFY COLUMN (first_name VARCHAR2(10));
ALTER players TABLE
MODIFY COLUMN first_name VARCHAR2(10);
Incorrect
Incorrect. Refer to Section 13 Lesson 3.
19. RENAME old_name to new_name can be used to: Mark for Review
(1) Points
19. RENAME old_name to new_name can be used to: Mark for Review
(1) Points
Rename a
row.
Rename a column.
Rename a table. (*)
All of the above.
Rename a column.
Rename a table. (*)
All of the above.
Incorrect
Incorrect. Refer to Section 13 Lesson 3.
Section 14
(Answer all questions in this section)
Section 14
(Answer all questions in this section)
20. The
DEPARTMENTS table contains these columns:
DEPARTMENT_ID NUMBER, Primary Key
DEPARTMENT_ABBR VARCHAR2(4)
DEPARTMENT_NAME VARCHAR2(30)
MANAGER_ID NUMBER
DEPARTMENT_ID NUMBER, Primary Key
DEPARTMENT_ABBR VARCHAR2(4)
DEPARTMENT_NAME VARCHAR2(30)
MANAGER_ID NUMBER
The
EMPLOYEES table contains these columns:
EMPLOYEE_ID
NUMBER
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
JOB_ID NUMBER
MANAGER_ID NUMBER
SALARY NUMBER(9,2)
HIRE_DATE DATE
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
JOB_ID NUMBER
MANAGER_ID NUMBER
SALARY NUMBER(9,2)
HIRE_DATE DATE
Evaluate
this statement:
ALTER
TABLE employees
ADD CONSTRAINT REFERENTIAL (manager_id) TO departments(manager_id);
ADD CONSTRAINT REFERENTIAL (manager_id) TO departments(manager_id);
Which
statement is true?
Mark for
Review
(1) Points
(1) Points
The
ALTER TABLE statement creates a referential constraint from the EMPLOYEES table
to the DEPARTMENTS table.
The ALTER TABLE statement succeeds, but does NOT recreate a referential constraint.
The ALTER TABLE statement fails because the ADD CONSTRAINT clause contains a syntax error. (*)
The ALTER TABLE statement creates a referential constraint from the DEPARTMENTS table to the EMPLOYEES table.
The ALTER TABLE statement succeeds, but does NOT recreate a referential constraint.
The ALTER TABLE statement fails because the ADD CONSTRAINT clause contains a syntax error. (*)
The ALTER TABLE statement creates a referential constraint from the DEPARTMENTS table to the EMPLOYEES table.
Correct
Correct
=====
Section
14
(Answer all questions in this section)
(Answer all questions in this section)
21.
Examine the structures of the PRODUCTS and SUPPLIERS tables.
PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
LIST_PRICE NUMBER (7,2)
COST NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
REORDER_QTY NUMBER
PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
LIST_PRICE NUMBER (7,2)
COST NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
REORDER_QTY NUMBER
SUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)
SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)
Evaluate
this statement:
ALTER
TABLE suppliers
DISABLE CONSTRAINT supplier_id_pk CASCADE;
DISABLE CONSTRAINT supplier_id_pk CASCADE;
For
which task would you issue this statement?
Mark for
Review
(1) Points
(1) Points
To
remove all constraint references to SUPPLIERS table
To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table
To drop the FOREIGN KEY constraint on the PRODUCTS table
To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)
To remove all constraint references to the PRODUCTS table
To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table
To drop the FOREIGN KEY constraint on the PRODUCTS table
To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)
To remove all constraint references to the PRODUCTS table
Correct
Correct
22. You need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true? Mark for Review
(1) Points
22. You need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true? Mark for Review
(1) Points
The
PRIMARY KEY constraint must be defined for the first column of the composite
primary key.
The PRIMARY KEY constraint must be defined at the table level. (*)
The PRIMARY KEY constraint must be defined at the table level and for each column in the composite primary key.
A PRIMARY KEY constraint must be defined for each column in the composite primary key.
The PRIMARY KEY constraint must be defined at the table level. (*)
The PRIMARY KEY constraint must be defined at the table level and for each column in the composite primary key.
A PRIMARY KEY constraint must be defined for each column in the composite primary key.
Correct
Correct
23. The number of check constraints that can be defined on a column is: Mark for Review
(1) Points
23. The number of check constraints that can be defined on a column is: Mark for Review
(1) Points
10
5
100
There is no limit (*)
5
100
There is no limit (*)
Incorrect
Incorrect. Refer to Section 14 Lesson 2.
24. Which statement about the NOT NULL constraint is true? Mark for Review
(1) Points
24. Which statement about the NOT NULL constraint is true? Mark for Review
(1) Points
The NOT
NULL constraint can be defined at either the column level or the table level.
The NOT NULL constraint prevents a column from containing alphanumeric values.
The NOT NULL constraint must be defined at the column level. (*)
The NOT NULL constraint requires a column to contain alphanumeric values.
The NOT NULL constraint prevents a column from containing alphanumeric values.
The NOT NULL constraint must be defined at the column level. (*)
The NOT NULL constraint requires a column to contain alphanumeric values.
Correct
Correct
25. Evaluate this CREATE TABLE statement:
CREATE TABLE customers
(customer_id NUMBER,
customer_name VARCHAR2(25),
address VARCHAR2(25),
city VARCHAR2(25),
region VARCHAR2(25),
postal_code VARCHAR2(11),
CONSTRAINT customer_id_un UNIQUE(customer_id),
CONSTRAINT customer_name_nn NOT NULL(customer_name));
25. Evaluate this CREATE TABLE statement:
CREATE TABLE customers
(customer_id NUMBER,
customer_name VARCHAR2(25),
address VARCHAR2(25),
city VARCHAR2(25),
region VARCHAR2(25),
postal_code VARCHAR2(11),
CONSTRAINT customer_id_un UNIQUE(customer_id),
CONSTRAINT customer_name_nn NOT NULL(customer_name));
Why does
this statement fail when executed?
Mark for
Review
(1) Points
(1) Points
The
NUMBER data types require precision values.
UNIQUE constraints must be defined at the column level.
The CREATE TABLE statement does NOT define a PRIMARY KEY.
NOT NULL constraints CANNOT be defined at the table level. (*)
UNIQUE constraints must be defined at the column level.
The CREATE TABLE statement does NOT define a PRIMARY KEY.
NOT NULL constraints CANNOT be defined at the table level. (*)
Correct
Correct
=========
Section 14
(Answer all questions in this section)
Section 14
(Answer all questions in this section)
26.
Which statement about constraints is true? Mark for Review
(1) Points
(1) Points
NOT NULL
constraints can only be specified at the column level. (*)
PRIMARY KEY constraints can only be specified at the column level.
A single column can have only one constraint applied.
UNIQUE constraints are identical to PRIMARY KEY constraints.
PRIMARY KEY constraints can only be specified at the column level.
A single column can have only one constraint applied.
UNIQUE constraints are identical to PRIMARY KEY constraints.
Correct
Correct
Section 15
(Answer all questions in this section)
Section 15
(Answer all questions in this section)
27.
Unlike tables, views contain no data of their own. True or False? Mark for
Review
(1) Points
(1) Points
True (*)
False
False
Correct
Correct
28. A view can contain a select statement with a subquery. True or False? Mark for Review
(1) Points
28. A view can contain a select statement with a subquery. True or False? Mark for Review
(1) Points
True (*)
False
False
Correct
Correct
29. Which of the following keywords cannot be used when creating a view? Mark for Review
(1) Points
29. Which of the following keywords cannot be used when creating a view? Mark for Review
(1) Points
HAVING
WHERE
ORDER BY (*)
They are all valid keywords when creating views.
WHERE
ORDER BY (*)
They are all valid keywords when creating views.
Incorrect
Incorrect. Refer to Section 15 Lesson 1.
30. When you drop a table referenced by a view, the view is automatically dropped as well. True or False? Mark for Review
(1) Points
30. When you drop a table referenced by a view, the view is automatically dropped as well. True or False? Mark for Review
(1) Points
True
False (*)
False (*)
Correct
Correct
========
Section 15
(Answer all questions in this section)
========
Section 15
(Answer all questions in this section)
31.
Evaluate this CREATE VIEW statement:
CREATE VIEW sales_view
AS SELECT customer_id, region, SUM(sales_amount)
FROM sales
WHERE region IN (10, 20, 30, 40)
GROUP BY region, customer_id;
CREATE VIEW sales_view
AS SELECT customer_id, region, SUM(sales_amount)
FROM sales
WHERE region IN (10, 20, 30, 40)
GROUP BY region, customer_id;
Which
statement is true?
Mark for
Review
(1) Points
(1) Points
You can
only insert records into the SALES table using the SALES_VIEW view.
The CREATE VIEW statement generates an error.
You cannot modify data in the SALES table using the SALES_VIEW view. (*)
You can modify data in the SALES table using the SALES_VIEW view.
The CREATE VIEW statement generates an error.
You cannot modify data in the SALES table using the SALES_VIEW view. (*)
You can modify data in the SALES table using the SALES_VIEW view.
Correct
Correct
32. What is the purpose of including the WITH CHECK OPTION clause when creating a view? Mark for Review
(1) Points
32. What is the purpose of including the WITH CHECK OPTION clause when creating a view? Mark for Review
(1) Points
To make
sure that data is not duplicated in the view
To make sure that the parent table(s) actually exist
To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)
To keep views form being queried by unauthorized persons
To make sure that the parent table(s) actually exist
To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)
To keep views form being queried by unauthorized persons
Incorrect
Incorrect. Refer to Section 15 Lesson 2.
33. Examine the view below and choose the operation that CANNOT be performed on it.
CREATE VIEW dj_view (last_name, number_events) AS
SELECT c.last_name, COUNT(e.name)
FROM d_clients c, d_events e
WHERE c.client_number = e.client_number
GROUP BY c.last_name
Mark for Review
(1) Points
33. Examine the view below and choose the operation that CANNOT be performed on it.
CREATE VIEW dj_view (last_name, number_events) AS
SELECT c.last_name, COUNT(e.name)
FROM d_clients c, d_events e
WHERE c.client_number = e.client_number
GROUP BY c.last_name
Mark for Review
(1) Points
DROP
VIEW dj_view;
CREATE OR REPLACE dj_view (last_name, number_events) AS
SELECT c.last_name, COUNT (e.name)
FROM d_clients c, d_events e
WHERE c.client_number=e.client_number
GROUP BY c.last_name;
SELECT last_name, number_events FROM dj_view;
INSERT INTO dj_view VALUES (‘Turner’, 8); (*)
CREATE OR REPLACE dj_view (last_name, number_events) AS
SELECT c.last_name, COUNT (e.name)
FROM d_clients c, d_events e
WHERE c.client_number=e.client_number
GROUP BY c.last_name;
SELECT last_name, number_events FROM dj_view;
INSERT INTO dj_view VALUES (‘Turner’, 8); (*)
Incorrect
Incorrect. Refer to Section 15 Lesson 2.
34. For a View created using the WITH CHECK OPTION keywords, which of the following statements are true? Mark for Review
(1) Points
34. For a View created using the WITH CHECK OPTION keywords, which of the following statements are true? Mark for Review
(1) Points
Allows
for DELETES from other tables, including ones not listed in subquery
Prohibits DML actions without administrator CHECK approval
Prohibits changing rows not returned by the subquery in the view definition. (*)
The view will allow the user to check it against the data dictionary
Prohibits DML actions without administrator CHECK approval
Prohibits changing rows not returned by the subquery in the view definition. (*)
The view will allow the user to check it against the data dictionary
Incorrect
Incorrect. Refer to Section 15 Lesson 2.
Section 16
(Answer all questions in this section)
Section 16
(Answer all questions in this section)
35.
Evaluate this statement:
SELECT po_itemid_seq.CURRVAL
FROM dual;
SELECT po_itemid_seq.CURRVAL
FROM dual;
What
does this statement accomplish?
Mark for
Review
(1) Points
(1) Points
It sets
the current value of the PO_ITEM_ID_SEQ sequence to the value of the PO_ITEMID
column.
It displays the current value of the PO_ITEM_ID_SEQ sequence. (*)
It resets the current value of the PO_ITEM_ID_SEQ sequence.
It displays the next available value of the PO_ITEM_ID_SEQ sequence.
It displays the current value of the PO_ITEM_ID_SEQ sequence. (*)
It resets the current value of the PO_ITEM_ID_SEQ sequence.
It displays the next available value of the PO_ITEM_ID_SEQ sequence.
Correct
Correct
============
============
Section
16
(Answer all questions in this section)
(Answer all questions in this section)
36. You
created the LOCATION_ID_SEQ sequence to generate sequential values for the
LOCATION_ID column in the MANUFACTURERS table. You issue this statement:
ALTER TABLE manufacturers
MODIFY (location_id NUMBER(6));
ALTER TABLE manufacturers
MODIFY (location_id NUMBER(6));
Which
statement about the LOCATION_ID_SEQ sequence is true?
Mark for
Review
(1) Points
(1) Points
The
sequence is unchanged. (*)
The sequence is deleted and must be recreated.
The current value of the sequence is reset to zero.
The current value of the sequence is reset to the sequence’s START WITH value.
The sequence is deleted and must be recreated.
The current value of the sequence is reset to zero.
The current value of the sequence is reset to the sequence’s START WITH value.
Incorrect
Incorrect. Refer to Section 16 Lesson 1.
37. Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;
37. Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;
Which
statement is true?
Mark for
Review
(1) Points
(1) Points
The
sequence will start with 1. (*)
The sequence will continue to generate values after reaching its maximum value.
The sequence preallocates values and retains them in memory.
The sequence has no maximum value.
The sequence will continue to generate values after reaching its maximum value.
The sequence preallocates values and retains them in memory.
The sequence has no maximum value.
Incorrect
Incorrect. Refer to Section 16 Lesson 1.
38. All tables must have indexes on them otherwise they cannot be queried. True or False? Mark for Review
(1) Points
38. All tables must have indexes on them otherwise they cannot be queried. True or False? Mark for Review
(1) Points
True
False (*)
False (*)
Correct
Correct
39. Evaluate this statement:
CREATE INDEX sales_idx ON oe.sales (status);
39. Evaluate this statement:
CREATE INDEX sales_idx ON oe.sales (status);
Which
statement is true?
Mark for
Review
(1) Points
(1) Points
The
CREATE INDEX statement fails because of a syntax error.
The CREATE INDEX statement creates a unique index.
The CREATE INDEX creates a function-based index.
The CREATE INDEX statement creates a nonunique index. (*)
The CREATE INDEX statement creates a unique index.
The CREATE INDEX creates a function-based index.
The CREATE INDEX statement creates a nonunique index. (*)
Correct
Correct
40. The CLIENTS table contains these columns:
CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY
LAST_NAME VARCHAR2(15)
FIRST_NAME VARCHAR2(10)
CITY VARCHAR2(15)
STATE VARCHAR2(2)
40. The CLIENTS table contains these columns:
CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY
LAST_NAME VARCHAR2(15)
FIRST_NAME VARCHAR2(10)
CITY VARCHAR2(15)
STATE VARCHAR2(2)
You want
to create an index named ADDRESS_INDEX on the CITY and STATE columns of the
CLIENTS table. You execute this statement:
CREATE
INDEX clients
ON address_index (city, state);
ON address_index (city, state);
Which
result does this statement accomplish?
Mark for
Review
(1) Points
(1) Points
An index
named CLIENTS is created on the CITY and STATE columns.
An index named ADDRESS_INDEX is created on the CITY and STATE columns.
An index named CLIENTS_INDEX is created on the CLIENTS table.
An error message is produced, and no index is created. (*)
An index named ADDRESS_INDEX is created on the CITY and STATE columns.
An index named CLIENTS_INDEX is created on the CLIENTS table.
An error message is produced, and no index is created. (*)
Correct
Correct
================
================
Section
17
(Answer all questions in this section)
(Answer all questions in this section)
41. User
Kate wants to create indexes on tables in her schema. What privilege must be
granted to Kate so that she can do this? Mark for Review
(1) Points
(1) Points
CREATE
INDEX
CREATE ANY INDEX
ALTER TABLE
None; users do not need extra privileges to create indexes on tables in their own schema. (*)
CREATE ANY INDEX
ALTER TABLE
None; users do not need extra privileges to create indexes on tables in their own schema. (*)
Correct
Correct
42. What system privilege must be held in order to login to an Oracle database? Mark for Review
(1) Points
42. What system privilege must be held in order to login to an Oracle database? Mark for Review
(1) Points
CREATE
LOGIN
CREATE SESSION (*)
CREATE LOGON
No special privilege is needed; if your username exists in the database, you can login.
CREATE SESSION (*)
CREATE LOGON
No special privilege is needed; if your username exists in the database, you can login.
Correct
Correct
43. Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ? Mark for Review
(1) Points
43. Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ? Mark for Review
(1) Points
REGEXP
(*)
REGEXP_REPLACE
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_REPLACE
REGEXP_LIKE
REGEXP_SUBSTR
Correct
Correct
44. REGULAR EXPRESSIONS does exactly the same as LIKE–no more and no less. (True or False?) Mark for Review
(1) Points
44. REGULAR EXPRESSIONS does exactly the same as LIKE–no more and no less. (True or False?) Mark for Review
(1) Points
True
False (*)
False (*)
Correct
Correct
45. Which statement would you use to add privileges to a role? Mark for Review
(1) Points
45. Which statement would you use to add privileges to a role? Mark for Review
(1) Points
ASSIGN
CREATE ROLE
ALTER ROLE
GRANT (*)
CREATE ROLE
ALTER ROLE
GRANT (*)
Correct
Correct
Section
17
(Answer all questions in this section)
(Answer all questions in this section)
41. User
Kate wants to create indexes on tables in her schema. What privilege must be
granted to Kate so that she can do this? Mark for Review
(1) Points
(1) Points
CREATE
INDEX
CREATE ANY INDEX
ALTER TABLE
None; users do not need extra privileges to create indexes on tables in their own schema. (*)
CREATE ANY INDEX
ALTER TABLE
None; users do not need extra privileges to create indexes on tables in their own schema. (*)
Correct
Correct
42. What system privilege must be held in order to login to an Oracle database? Mark for Review
(1) Points
42. What system privilege must be held in order to login to an Oracle database? Mark for Review
(1) Points
CREATE
LOGIN
CREATE SESSION (*)
CREATE LOGON
No special privilege is needed; if your username exists in the database, you can login.
CREATE SESSION (*)
CREATE LOGON
No special privilege is needed; if your username exists in the database, you can login.
Correct
Correct
43. Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ? Mark for Review
(1) Points
43. Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ? Mark for Review
(1) Points
REGEXP
(*)
REGEXP_REPLACE
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_REPLACE
REGEXP_LIKE
REGEXP_SUBSTR
Correct
Correct
44. REGULAR EXPRESSIONS does exactly the same as LIKE–no more and no less. (True or False?) Mark for Review
(1) Points
44. REGULAR EXPRESSIONS does exactly the same as LIKE–no more and no less. (True or False?) Mark for Review
(1) Points
True
False (*)
False (*)
Correct
Correct
45. Which statement would you use to add privileges to a role? Mark for Review
(1) Points
45. Which statement would you use to add privileges to a role? Mark for Review
(1) Points
ASSIGN
CREATE ROLE
ALTER ROLE
GRANT (*)
CREATE ROLE
ALTER ROLE
GRANT (*)
Correct
Correct
Section 17
(Answer all questions in this section)
Section 17
(Answer all questions in this section)
46.
User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then
grants select on the same table to User3. If User1 revokes select privileges
from User2, will User3 be able to access the table? Mark for Review
(1) Points
(1) Points
No (*)
Yes
Yes
Correct
Correct
47. When a user is logged into one database, he is restricted to working with objects found in that database. True or False? Mark for Review
(1) Points
47. When a user is logged into one database, he is restricted to working with objects found in that database. True or False? Mark for Review
(1) Points
True
False (*)
False (*)
Correct
Correct
Section 18
(Answer all questions in this section)
Section 18
(Answer all questions in this section)
48. If
UserB has privileges to see the data in a table, as soon as UserA has entered
data into that table, UserB can see that data. True or False? Mark for Review
(1) Points
(1) Points
True
False (*)
False (*)
Incorrect
Incorrect. Refer to Section 18 Lesson 1.
49. COMMIT saves all outstanding data changes? True or False? Mark for Review
(1) Points
49. COMMIT saves all outstanding data changes? True or False? Mark for Review
(1) Points
True (*)
False
False
Correct
Correct
Section 19
(Answer all questions in this section)
Section 19
(Answer all questions in this section)
50.
Testing is done by programmers. True or False? Mark for Review
(1) Points
(1) Points
True (*)
False
False
No comments:
Post a Comment