OCA/OCP: Introduction to Oracle9i SQL Study Guide
by Chip Dawes and Biju Thomas
Review Questions
1. The STATE table has the following constraints (the constraint status is shown in parentheses):
Primary key pk_state (enabled)
Foreign key COUNTRY table-fk_state (enabled)
Check constraint ck_cnt_code (disabled)
Check constraint ck_st_code (enabled)
Not null constraint nn_st_name (enabled)
You execute the following SQL:
CREATE TABLE STATE_NEW AS SELECT * FROM STATE;
How many constraints will there be in the new table?
A. 0
B. 1
C. 3
D. 5
E. 2
----
When you create a table using CTAS (CREATE TABLE AS), only the NOT NULL constraints are copied.
Ans: B.
2. Which line of code has an error?
1 CREATE TABLE FRUITS_VEGETABLES
2 (FRUIT_TYPE VARCHAR2,
3 FRUIT_NAME CHAR (20),
4 QUANTITY NUMBER);
A. 1
B. 2
C. 3
D. 4
---
A VARCHAR2 datatype should always specify the maximum length of the column.
Ans: B.
3. Which statement successfully adds a new column ORDER_DATE to the table ORDERS?
A. ALTER TABLE ORDERS ADD COLUMN ORDER_DATE DATE;
B. ALTER TABLE ORDERS ADD ORDER_DATE (DATE);
C. ALTER TABLE ORDERS ADD ORDER_DATE DATE;
D. ALTER TABLE ORDERS NEW COLUMN ORDER_DATE TYPE DATE;
----
The correct statement is C. When adding only one column, the column definition need not be enclosed in parentheses.
Ans: C
4. What are the special characters allowed in a table name? (Choose two answers.)
A. &
B. #
C. @
D. $
------
Only three special characters ($, _, and #) are allowed in the table names along with letters and numbers.
Ans: B, D.
5. Consider the following statement:
CREATE TABLE MY_TABLE (
1ST_COLUMN NUMBER,
2ND_COLUMN VARCHAR2 (20));
Which of the following best describes this statement?
A. Tables cannot be created without a defining a primary key. The table definition here is missing the primary key.
B. The reserved word COLUMN cannot be part of the column name.
C. The column names are invalid.
D. There is no maximum length specified for the first column definition. You must always specify a length for character and numeric columns.
E. There is no error in the statement.
----
All identifiers (column names, table names, and so on) must begin with an alphabetic character. An identifier can contain alphabetic characters, numbers, and the special characters $, #, and _.
Ans: C
6. Which dictionary view would you query to list only the tables you own?
A. ALL_TABLES
B. DBA_TABLES
C. USER_TABLES
D. USR_TABLES
----
The USER_TABLES view provides information on the tables owned by the user who has logged on that session. DBA_TABLES will have all the tables in the database, and ALL_TABLES will have the tables owned by you as well as the tables to which you have access. USR_TABLES is not a valid dictionary view.
Ans: C.
7. The STATE table has six rows. You issue the following command:
ALTER TABLE STATE ADD UPDATE_DT DATE DEFAULT SYSDATE;
Which of the following is correct?
A. A new column, UPDATE_DT, is added to the STATE table, and its contents for the existing rows are NULL.
B. Since the table is not empty, you cannot add a new column.
C. The DEFAULT value cannot be provided if the table has rows.
D. A new column, UPDATE_DT, is added to STATE and is populated with the current system date and time.
----
When a default value is specified in the new column added, the column values for the existing rows are populated with the default value.
Ans: D.
8. The HIRING table has the following data:
EMPNO HIREDATE
--------- ----------
1021 12-DEC-00
3400 24-JAN-01
2398 30-JUN-01
What will be result of the following query?
SELECT hiredate+1 FROM hiring WHERE empno = 3400;
A. 4-FEB-01
B. 25-JAN-01
C. N-02
D. None of the above
----
In date arithmetic, adding 1 is equivalent to adding 24 hours. To add 6 hours to a date value with time, add 0.25.
Ans: B
9. What is the default length of a CHAR datatype column, if no length is specified in the table definition?
A. 256
B. 1000
C. 64
D. 1
E. You must always specify a length for CHAR columns.
-----
If you do not specify a length for a CHAR datatype column, the default length of 1 is assumed.
Ans: D
10. Which statement will remove the column UPDATE_DT from table STATE?
A. ALTER TABLE STATE DROP COLUMN UPDATE_DT;
B. ALTER TABLE STATE REMOVE COLUMN UPDATE_DT;
C. DROP COLUMN UPDATE_DT FROM STATE;
D. ALTER TABLE STATE SET UNUSED COLUMN UPDATE_DT;
E. You cannot drop a column from the table.
----
You can use the DROP COLUMN clause with the ALTER TABLE statement to drop a column. There is no separate DROP COLUMN statement or a REMOVE clause in the ALTER TABLE statement. The SET UNUSED clause is used to mark the column as unused. This column can be dropped later using the DROP UNUSED COLUMNS clause.
Ans: A
11. Which option is not available in Oracle when modifying tables?
A. Add new columns
B. Rename existing column
C. Drop existing column
D. All of the above
----
You cannot rename an existing column using the ALTER TABLE statement. To rename the column, you must re-create the table with the new name.
Ans: B.
12. Which one of the following statements will create a primary key for the CITY table with columns STATE_CD and CITY_CD?
A. CREATE PRIMARY KEY ON CITY (STATE_CD, CITY_CD);
B. CREATE CONSTRAINT PK_CITY PRIMARY KEY ON CITY (STATE_CD, CITY_CD);
C. ALTER TABLE CITY ADD CONSTRAINT PK_CITY PRIMARY KEY (STATE_CD, CITY_CD);
D. ALTER TABLE CITY ADD PRIMARY KEY (STATE_CD, CITY_CD);
E. ALTER TABLE CITY ADD PRIMARY KEY CONSTRAINT PK_CITY ON (STATE_CD, CITY_CD);
---
The ALTER TABLE statement is used to create and remove constraints. Option D would work if the keyword CONSTRAINT were included between ADD and PRIMARY.
Ans: C
13. Which of the following check constraints will raise an error? (Choose all that apply.)
A. CONSTRAINT ck_gender CHECK (gender IN ('M', 'F'))
B. CONSTRAINT ck_old_order CHECK (order_date > (SYSDATE - 30))
C. CONSTRAINT ck_vendor CHECK (vendor_id IN (SELECT vendor_id FROM vendors))
D. CONSTRAINT ck_profit CHECK (gross_amt > net_amt)
----
Ans: B, C. Check constraints cannot reference the SYSDATE function or other tables.
14. Consider the datatypes DATE, TIMESTAMP (TS), TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ), INTERVAL YEAR TO MONTH (IY2M), INTERVAL DAY TO SECOND (ID2S). Which operations are not allowed by the Oracle9i database? (Choose all that apply.)
A. DATE + DATE
B. TSLTZ - DATE
C. TSLTZ + IY2M
D. TS * 5
E. ID2S / 2
F. IY2M + IY2M
G. ID2S + IY2M
H. DATE - IY2M
----
You cannot add two DATE datatypes, but you can subtract to find the difference in days. Multiplication and division operators are permitted only on INTERVAL datatypes. When adding or subtracting INTERVAL datatypes, both INTERVAL datatypes should be of the same category.
Ans: A, D, G.
15. A constraint is created with the DEFERRABLE INITIALLY IMMEDIATE clause. What does this mean?
A. Constraint checking is done only at commit time.
B. Constraint checking is done after each SQL statement is executed, but you can change this behavior by specifying SET CONSTRAINTS ALL DEFERRED.
C. Existing rows in the table are immediately checked for constraint violation.
D. The constraint is immediately checked in a DML operation, but subsequent constraint verification is done at commit time.
---
DEFERRABLE specifies that the constraint can be deferred using the SET CONSTRAINTS command. INITIALLY IMMEDIATE specifies that the constraint's default behavior is to validate the constraint for each SQL statement executed.
Ans: B.
16. What is the default precision for fractional seconds in a TIMESTAMP datatype column?
A. 0
B. 2
C. 6
D. 9
----
Ans: C. The default precision is 6 digits. The precision can range from 0 to 9.
17. Which datatype stores the time zone information along with the date value?
A. TIMESTAMP
B. TIMESTAMP WITH LOCAL TIME ZONE
C. TIMESTAMP WITH TIME ZONE
D. DATE
E. Both options B and C
-----
Only TIMESTAMP WITH TIME ZONE stores the time zone information as a displacement from UTC. TIMESTAMP WITH LOCAL TIME ZONE adjusts the time to database's time zone before storing it.
Ans: C.
18. You have a large job that will load many thousands of rows into your ORDERS table. To speed up the loading process, you want to temporarily stop enforcing the foreign key constraint FK_ORDERS. Which of the following statements will satisfy your requirement?
A. ALTER CONSTRAINT FK_ORDERS DISABLE;
B. ALTER TABLE ORDERS DISABLE FOREIGN KEY FK_ORDERS;
C. ALTER TABLE ORDERS DISABLE CONSTRAINT FK_ORDERS;
D. ALTER TABLE ORDERS DISABLE ALL CONSTRAINTS;
----
You can disable constraints by specifying its constraint name. You may enable the constraint after the load and avoid the constraint checking while enabling using the ALTER TABLE ORDERS MODIFY CONSTRAINT FK_ORDERS ENABLE NOVALIDATE; command.
Ans: C
19. You are connected to the database as user JOHN. You need to rename a table named NORDERS to NEW_ORDERS, owned by SMITH. Consider the following two statements:
1. RENAME SMITH.NORDERS TO NEW_ORDERS;
2. ALTER TABLE SMITH.NORDERS RENAME TO NEW_ORDERS;
Which of the following is correct?
A. Statement 1 will work; statement 2 will not.
B. Statements 1 and 2 will work.
C. Statement 1 will not work; statement 2 will work.
D. Statements 1 and 2 will not work
----
RENAME can be used to rename objects owned the user. ALTER TABLE should be used to rename tables owned by another user. To do so, you must have the ALTER privilege on the table or the ALTER ANY TABLE privilege.
Ans: C.
20. Which two declarations define the maximum length of a CHAR datatype column in bytes?
A. CHAR (20)
B. CHAR (20) BYTE
C. CHAR (20 BYTE)
D. BYTE (20 CHAR)
E. CHAR BYTE (20)
---
Ans: A, C. The maximum lengths of CHAR and VARCHAR2 columns can be defined in characters or bytes. BYTE is the default.
No comments:
Post a Comment