Database Programming with SQL FINAL Part 2

Section 12
(Answer all questions in this section)

1. What is the quickest way to use today’s date when you are creating a new row? Mark for Review
(1) Points

Use the SYSDATE function. (*)
Use the TODAYS_DATE function.
Simply write today’s date in the format ‘dd-mon-rrrr’.
Simply use the keyword DATE in the insert statement.

Incorrect Incorrect. Refer to Section 12 Lesson 1.
2. Insert statements can be combined with subqueries to create more than one row per statement. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
3. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FAST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)

You want to execute one DML statement to change the salary of all employees in department 10 to equal the new salary of employee number 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?

Mark for Review
(1) Points

UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898)
WHERE department_id = 10;
(*)

UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898 AND department_id = 10);
UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898);
UPDATE employees
SET salary = SELECT salary FROM employees WHERE employee_id = 89898;

Correct Correct
4. Examine the structures of the PRODUCTS and SUPPLIERS tables:
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)

PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, Primary Key
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER Foreign key to SUPPLIER_ID of the SUPPLIERS table
CATEGORY_ID NUMBER
QTY_PER_UNIT NUMBER
UNIT_PRICE NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER

You want to delete any products supplied by the five suppliers located in Atlanta. Which script should you use?

Mark for Review
(1) Points

DELETE FROM products
WHERE supplier_id IN
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = ‘ATLANTA’);
(*)

DELETE FROM suppliers
WHERE supplier_id IN
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = ‘ALANTA’);
DELETE FROM products
WHERE UPPER(city) = ‘ATLANTA’;
DELETE FROM products
WHERE supplier_id =
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = ‘ATLANTA’);

Correct Correct
5. Is the following statement valid, i.e. is it allowed to update rows in one table, based on a subquery from another table?
UPDATE copy_emp
SET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 200);

Mark for Review
(1) Points

No, this statement will return an error.
Yes, this is a perfectly valid statement. (*)
The statement will fail because the subqueries are returning data from different rows.
No, this does nothing.

Correct Correct
====

Section 12
(Answer all questions in this section)

6. Which of the following represents the correct syntax for an INSERT statement? Mark for Review
(1) Points

INSERT VALUES INTO customers (3178 J. Smith 123 Main Street Nashville TN 37777;
INSERT INTO customers VALUES ‘3178’ ‘J.’ ‘Smith’ ‘123 Main Street’ ‘Nashville’ ‘TN’ ‘37777’;
INSERT INTO customers VALUES (‘3178’, ‘J.’, ‘Smith’, ‘123 Main Street’, ‘Nashville’, ‘TN’, ‘37777’); (*)
INSERT customers VALUES 3178, J., Smith, 123 Main Street, Nashville, TN, 37777;

Correct Correct
7. Multi-table inserts are used when the same source data should be inserted into _____________ target table. Mark for Review
(1) Points

A data warehouse
A very large
Ten
More than one (*)

Correct Correct
8. A multi-table insert statement must have a subquery at the end of the statement. (True or False?) Mark for Review
(1) Points

True (*)
False

Correct Correct
Section 13
(Answer all questions in this section)

9. To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
10. The ELEMENTS column is defined as:
NUMBER(6,4)
How many digits to the right of the decimal point are allowed for the ELEMENTS column?

Mark for Review
(1) Points

Zero
Two
Six
Four (*)

Correct Correct

======
Section 13
(Answer all questions in this section)

11. Evaluate this CREATE TABLE statement:
1. CREATE TABLE customer#1 (
2. cust_1 NUMBER(9),
3. sales$ NUMBER(9),
4. 2date DATE DEFAULT SYSDATE);

Which line of this statement will cause an error?

Mark for Review
(1) Points

2
1
4 (*)
3

Correct Correct
12. You want to create a database table that will contain information regarding products that your company released during 2001. Which name can you assign to the table that you create? Mark for Review
(1) Points

PRODUCTS–2001
PRODUCTS_2001 (*)
2001_PRODUCTS
PRODUCTS_(2001)

Correct Correct
13. DCL, which is the acronym for Data Control Language, allows: Mark for Review
(1) Points

The ALTER command to be used.
The CONROL TRANSACTION statement can be used.
A Database Administrator the ability to grant privileges to users. (*)
The TRUNCATE command to be used.

Correct Correct
14. To completely get rid of a table, its contents, its structure, AND release the storage space the keyword is: Mark for Review
(1) Points

TRUNCATE
KILL
DELETE
DROP (*)

Correct Correct
15. You want to issue the following command on a database that includes your company’s inventory information:
ALTER TABLE products SET UNUSED COLUMN color;
What will be the result of issuing this command?

Mark for Review
(1) Points

The column named COLOR in the table named PRODUCTS will be created.
The column named COLOR in the table named PRODUCTS will not be returned in subsequent reads of the table by Oracle, as it has been deleted logically. (*)
The column named COLOR in the table named PRODUCTS will be assigned default values.
The column named COLOR in the table named PRODUCTS will be deleted.

Incorrect Incorrect. Refer to Section 13 Lesson 3.

========
Section 13
(Answer all questions in this section)

16. To do a logical delete of a column without the performance penalty of rewriting all the table datablocks, you can issue the following command: Mark for Review
(1) Points

Alter table modify column
Drop column “columname”
Alter table drop column
Alter table set unused (*)

Incorrect Incorrect. Refer to Section 13 Lesson 3.
17. The data type of a column can never be changed once it has been created. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
18. Which of the following will correctly change the name of the LOCATIONS table to NEW_LOCATIONS? Mark for Review
(1) Points

ALTER TABLE LOCATIONS RENAME NEW_LOCATIONS
MODIFY TABLE LOCATIONS RENAME NEW_LOCATIONS
RENAME LOCATIONS TO NEW_LOCATIONS (*)
None of the above; you cannot rename a table, you can only CREATE, ALTER and DROP a table.

Correct Correct
19. Evaluate this statement:
ALTER TABLE inventory
MODIFY backorder_amount NUMBER(8,2);

Which task will this statement accomplish?

Mark for Review
(1) Points

Changes the definition of the BACKORDER_AMOUNT column to NUMBER(8,2) (*)
Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8 2)
Alters the definition of the BACKORDER_AMOUNT column to NUMBER
Alters the definition of the BACKORDER_AMOUNT column to NUMBER(2,8)
Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8.2)

Correct Correct
Section 14
(Answer all questions in this section)

20. A table must have at least one not null constraint and one unique constraint. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct

===

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14
(Answer all questions in this section)

21. If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
22. A table can only have one unique key constraint defined. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
23. Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table? Mark for Review
(1) Points

REFERENTIAL
ON DELETE CASCADE
RESEMBLES
REFERENCES (*)

Correct Correct
24. Which statement about a non-mandatory foreign key constraint is true? Mark for Review
(1) Points

A foreign key value cannot be null.
A foreign key value must either be null or match an existing value in the parent table. (*)
A foreign key value must be unique.
A foreign key value must match an existing value in the parent table.

Correct Correct
25. The PO_DETAILS table contains these columns:
PO_NUM NUMBER NOT NULL, Primary Key
PO_LINE_ID NUMBER NOT NULL, Primary Key
PRODUCT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCTS table
QUANTITY NUMBER
UNIT_PRICE NUMBER(5,2)

Evaluate this statement:

ALTER TABLE po_details
DISABLE CONSTRAINT product_id_pk CASCADE;

For which task would you issue this statement?

Mark for Review
(1) Points

To disable the constraint on the PO_NUM column while creating a PRIMARY KEY index
To create a new PRIMARY KEY constraint on the PO_NUM column
To drop and recreate the PRIMARY KEY constraint on the PO_NUM column
To disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column (*)

Correct Correct
==========
Section 14
(Answer all questions in this section)

26. You need to display the names and definitions of constraints only in your schema. Which data dictionary view should you query? Mark for Review
(1) Points

USER_CONS_COLUMNS
ALL_CONS_COLUMNS
USER_CONSTRAINTS (*)
DBA_CONSTRAINTS

Correct Correct
Section 15
(Answer all questions in this section)

27. Which option would you use when creating a view to ensure that no DML operations occur on the view? Mark for Review
(1) Points

WITH ADMIN OPTION
WITH READ ONLY (*)
NOFORCE
FORCE

Correct Correct
28. Given the following view, which operations would be allowed on the emp_dept view?
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||’ ‘||e.last_name emp_name,
e.salary,
e.hire_date,
d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id >=50;

Mark for Review
(1) Points

SELECT, INSERT
SELECT, DELETE
SELECT, UPDATE of all columns
SELECT, UPDATE of some columns, DELETE (*)

Incorrect Incorrect. Refer to Section 15 Lesson 2.
29. Which action can be performed by using DML statements? Mark for Review
(1) Points

Disabling an index
Creating PRIMARY KEY constraints
Deleting records in a table (*)
Altering a table

Correct Correct
30. Which of the following is true about ROWNUM? Mark for Review
(1) Points

It is the number assigned to each row returned from a query after it is ordered.
It is the number assigned to each row returned from a query as it is read from the table. (*)
It is the number of rows in a table.
None of the above

Correct Correct
Previous Page 6 of 10 Next Summary

=======================

Section 15
(Answer all questions in this section)

31. You must create a view that will display the name, customer identification number, new balance, finance charge, and credit limit of all customers.
You issue this statement:
CREATE OR REPLACE VIEW CUST_CREDIT_V
AS SELECT c.last_name, c.customer_id, a.new_balance, a.finance_charge, a.credit_limit
FROM customers c, accounts a
WHERE c.account_id = a.account_id WITH READ ONLY;

Which type of SQL command can be issued on the CUST_CREDIT_V view?

Mark for Review
(1) Points

INSERT
DELETE
SELECT (*)
UPDATE

Correct Correct
32. Evaluate this view definition:
CREATE OR REPLACE VIEW part_name_v
AS SELECT DISTINCT part_name
FROM parts
WHERE cost >= 45;

Which of the following statements using the PART_NAME_V view will execute successfully?

Mark for Review
(1) Points

DELETE FROM part_name_v
WHERE part_id = 56897;
SELECT *
FROM part_name_v;
(*)

UPDATE part_name_v
SET cost = cost * 1.23
WHERE part_id = 56990;
INSERT INTO part_name_v (part_id, part_name, product_id, cost)
VALUES (857986, ?cylinder?, 8790, 3.45);

Correct Correct
33. In order to query a database using a view, which of the following statements applies? Mark for Review
(1) Points

You can never see all the rows in the table through the view.
Use special VIEW SELECT keywords.
The tables you are selecting from can be empty, yet the view still returns the original data from those tables.
You can retrieve data from a view as you would from any table. (*)

Correct Correct
34. Which of the following statements is a valid reason for using a view? Mark for Review
(1) Points

Views are used when you only want to restrict DML operations using a WITH CHECK OPTION.
Views allow access to the data because the view displays all of the columns from the table.
Views are not valid unless you have more than one user.
Views provide data independence for infrequent users and application programs. One view can be used to retrieve data from several tables. Views can be used to provide data security. (*)

Correct Correct
Section 16
(Answer all questions in this section)

35. 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);

Which result does this statement accomplish?

Mark for Review
(1) Points

An index named CLIENTS 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.

Correct Correct
====

Section 16
(Answer all questions in this section)

36. All tables must have indexes on them otherwise they cannot be queried. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
37. Which of the following best describes the function of an index? Mark for Review
(1) Points

An index can prevent users from viewing certain data in a table.
An index can reduce the time required to grant multiple privileges to users.
An index can run statement blocks when DML actions occur against a table.
An index can increase the performance of SQL queries that search large tables. (*)

Correct Correct
38. A gap can occur in a sequence because a user generated a number from the sequence and then rolled back the transaction. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
39. Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table? Mark for Review
(1) Points

ALTER TABLE employees ;
CREATE SEQUENCE emp_id_seq;
ALTER SEQUENCE emp_id_seq; (*)
ALTER SEQUENCE emp_id_seq.employee_id;

Incorrect Incorrect. Refer to Section 16 Lesson 1.
40. Which of the following best describes the function of the NEXTVAL virtual column? Mark for Review
(1) Points

The NEXTVAL virtual column displays only the physical locations of the rows in a table.
The NEXTVAL virtual column returns the integer that was most recently supplied by the sequence.
The NEXTVAL virtual column displays the order in which Oracle retrieves row data from a table.
The NEXTVAL virtual column increments a sequence by a predetermined value. (*)

Correct Correct

=====

Section 17
(Answer all questions in this section)

41. Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
42. REGULAR EXPRESSIONS can be used as part of a contraint definition. (True or False?) Mark for Review
(1) Points

True (*)
False

Correct Correct
43. You want to grant user BOB the ability to change other users’ passwords. Which privilege should you grant to BOB? Mark for Review
(1) Points

The DROP USER privilege
The ALTER USER privilege (*)
The CREATE USER privilege
The CREATE PROFILE privilege

Correct Correct
44. Which of the following privileges must be assigned to a user account in order for that user to connect to an Oracle database? Mark for Review
(1) Points

OPEN SESSION
ALTER SESSION
CREATE SESSION (*)
RESTRICTED SESSION

Incorrect Incorrect. Refer to Section 17 Lesson 1.
45. Which of the following statements about granting object privileges is false? Mark for Review
(1) Points

Object privileges can only be granted through roles. (*)
To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.
The owner of an object automatically acquires all object privileges on that object.
An object owner can grant any object privilege on the object to any other user or role of the database.

Correct Correct
====
Section 17
(Answer all questions in this section)

46. Granting an object privilege WITH GRANT OPTION allows the recipient to grant all object privileges on the table to other users. True or False? Mark for Review
(1) Points

True
False (*)

Incorrect Incorrect. Refer to Section 17 Lesson 2.
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 (*)

Incorrect Incorrect. Refer to Section 17 Lesson 2.
Section 18
(Answer all questions in this section)

48. User BOB’s CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;

What result will JANE see?

Mark for Review
(1) Points

JANE will receive an error message because she is not allowed to query the table while BOB is updating it.
20 (*)
2
22

Correct Correct
49. You need not worry about controlling your transactions. Oracle does it all for you. True or False? Mark for Review
(1) Points

True
False (*)

Incorrect Incorrect. Refer to Section 18 Lesson 1.
Section 19
(Answer all questions in this section)

50. Unit testing may be a composite of many different possible cases, or approaches, a user would opt to execute a transaction. True or False? Mark for Review
(1) Points

True (*)
False