Review Questions – Chapter 7
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
|
|||
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
|
||
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;
|
||
4.
|
What are the special characters
allowed in a table name? (Choose two answers.)
A. &
B. #
C. @
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.
|
||
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
|
||
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.
|
||
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
|
||
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.
|
||
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.
|
||
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
|
||
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);
|
||
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)
|
||
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
|
||
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.
|
||
16.
|
What is the default precision for
fractional seconds in a TIMESTAMP datatype column?
A. 0
B. 2
C. 6
D. 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
|
||
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;
|
||
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
|
||
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)
|
||
Answers
1.
|
B. When you create a table using
CTAS (CREATE TABLE AS), only the NOT NULL constraints are copied.
|
2.
|
B. A VARCHAR2 datatype should
always specify the maximum length of the column.
|
3.
|
C. The correct statement is C. When
adding only one column, the column definition need not be enclosed in
parentheses.
|
4.
|
B, D. Only three special characters
($, _, and #) are allowed in the table names along with letters and numbers.
|
5.
|
C. 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 _.
|
6.
|
C. 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.
|
7.
|
D. When a default value is
specified in the new column added, the column values for the existing rows
are populated with the default value.
|
8.
|
B. In date arithmetic, adding 1 is
equivalent to adding 24 hours. To add 6 hours to a date value with time, add
0.25.
|
9.
|
D. If you do not specify a length for
a CHAR datatype column, the default length of 1 is assumed.
|
10.
|
A. 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.
|
11.
|
B. 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.
|
12.
|
C. 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.
|
13.
|
B, C. Check constraints cannot
reference the SYSDATE function or other tables.
|
14.
|
A, D, G. 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.
|
15.
|
B. 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.
|
16.
|
C. The default precision is 6
digits. The precision can range from 0 to 9.
|
17.
|
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.
|
18.
|
C. 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.
|
19.
|
C. 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.
|
20.
|
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