(Answer all questions in this section)
1.
Evaluate this statement:
Which statement about this TRUNCATE
TABLE statement is true?
You can produce the same results by
issuing the 'DROP TABLE employee' statement.
You can issue this statement to retain
the structure of the employees table. (*)
You can reverse this statement by
issuing the ROLLBACK statement.
You can produce the same results by
issuing the 'DELETE employees' statement.
2.
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?
DROP TABLE (*)
DELETE
TRUNCATE TABLE
ALTER TABLE
3. You need to change the name of the EMPLOYEES table
to the EMP table. Which statement should you use?
RENAME employees emp;
RENAME employees TO emp; (*)
ALTER TABLE employees TO emp;
ALTER TABLE employees RENAME TO emp;
4. 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?
ALTER TABLE orders
CHANGE DATATYPE amount TO DEFAULT 250;
ALTER TABLE orders
MODIFY (amount 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);
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)
5.
Comments on tables and columns can be stored for
documentation by:
Embedding /* comment */ within the definition
of the table.
Using the ALTER TABLE CREATE COMMENT
syntax
Using the COMMENT ON TABLE or COMMENT on
COLUMN (*)
Using an UPDATE statement on the
USER_COMMENTS table
6.
The TEAMS table contains these columns:
TEAM_ID
NUMBER(4) Primary Key
TEAM_NAME
VARCHAR2(20)
MGR_ID
NUMBER(9)
The TEAMS table is currently empty. You
need to allow users to include text characters in the manager identification
values. Which statement should you use to implement this?
ALTER teams
MODIFY (mgr_id VARCHAR2(15));
ALTER TABLE teams
MODIFY (mgr_id VARCHAR2(15));
(*)
ALTER TABLE teams
REPLACE (mgr_id VARCHAR2(15));
ALTER teams TABLE
MODIFY COLUMN (mgr_id VARCHAR2(15));
You CANNOT modify the data type of the MGR_ID column.
7.
Evaluate the structure of the EMPLOYEE table:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)
The EMPLOYEE_ID column currently
contains 500 employee identification numbers. Business requirements have
changed and you need to allow users to include text characters in the
identification values. Which statement should you use to change this column's
data type?
ALTER TABLE employee
MODIFY (employee_id VARCHAR2(9));
ALTER TABLE employee
REPLACE (employee_id VARCHAR2(9));
ALTER employee TABLE
MODIFY COLUMN (employee_id
VARCHAR2(15));
You
CANNOT modify the data type of the EMPLOYEE_ID column, as the table is not
empty. (*)
8.
Evaluate the structure of the EMPLOYEE table:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)
Which statement should you use to
increase the LAST_NAME column length to 35 if the column currently contains 200
records?
ALTER employee TABLE
ALTER COLUMN (last_name VARCHAR2(35));
ALTER TABLE employee
RENAME last_name VARCHAR2(35);
ALTER TABLE employee
MODIFY (last_name VARCHAR2(35));
(*)
You CANNOT increase the width of the LAST_NAME column.
9. You need to remove all the rows from the SALES_HIST
table. You want to release the storage space, but do not want to remove the
table structure. Which statement should you use?
The DROP TABLE statement
The ALTER TABLE statement
The DELETE statement
The TRUNCATE TABLE statement (*)
10.
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?
ALTER players TABLE
MODIFY COLUMN first_name VARCHAR2(10);
ALTER players TABLE
MODIFY COLUMN (first_name
VARCHAR2(10));
ALTER TABLE players
RENAME first_name VARCHAR2(10);
ALTER TABLE players
MODIFY (first_name VARCHAR2(10));
(*)
11. To do a logical delete of a column without the
performance penalty of rewriting all the table datablocks, you can issue the
following command:
Alter table modify column
Alter table drop column
Alter table set unused (*)
Drop column 'columname'
12.
Evaluate this CREATE TABLE statement:
CREATE TABLE sales
( sales_id NUMBER(9),
customer_id NUMBER(9),
employee_id NUMBER(9),
description VARCHAR2(30),
sale_date TIMESTAMP WITH LOCAL TIME
ZONE DEFAULT SYSDATE,
sale_amount NUMBER(7,2));
Which business requirement will this
statement accomplish?
Sales identification values could be
either numbers or characters, or a combination of both.
All employee identification values are
only 6 digits so the column should be variable in length.
Description values can range from 0 to
30 characters so the column should be fixed in length
Today's date should be used if no value
is provided for the sale date. (*)
13.
The SPEED_TIME column should store a fractional
second value.
Which data type should you use?
DATE
DATETIME
TIMESTAMP (*)
INTERVAL DAY TO SECOND
14.
The TIMESTAMP data type allows what?
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.
15. A column that will be used to store binary data up
to 4 Gigabytes in size should be defined as which datatype?
LONG
NUMBER
BLOB (*)
LONGRAW
16.
Which data types stores variable-length character
data? Select two. (Choose all correct answers)
CHAR
NCHAR
CLOB (*)
VARCHAR2 (*)
17. You need to store the SEASONAL data in months and
years. Which data type should you use?
DATE
TIMESTAMP
INTERVAL YEAR TO MONTH (*)
INTERVAL DAY TO SECOND
18. You need to store the HIRE_DATE value with a time
zone displacement value and allow data to be returned in the user's local
session time zone. Which data type should you use?
DATETIME
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE (*)
19.
Which CREATE TABLE statement will fail?
CREATE TABLE date_1 (date_1 DATE);
CREATE TABLE date (date_id NUMBER(9));
(*)
CREATE TABLE time (time_id
NUMBER(9));
CREATE TABLE time_date (time
NUMBER(9));
20.
Evaluate this CREATE TABLE statement:
CREATE TABLE line_item ( line_item_id
NUMBER(9), order_id NUMBER(9), product_id NUMBER(9));
You are a member of the SYSDBA role,
but are logged in under your own schema. You issue this CREATE TABLE statement.
Which statement is true?
You created the LINE_ITEM table in the
public schema.
You created the LINE_ITEM table in the
SYS schema.
You created the table in your schema. (*)
You created the table in the SYSDBA
schema.
21.
You want to create a table named TRAVEL that is a
child of the EMPLOYEES table. Which of the following statements should you
issue?
CREATE TABLE travel
(destination_id primary key,
departure_date date, return_date date, emp_id REFERENCES employees (emp_id));
CREATE TABLE travel
(destination_id number primary key,
departure_date date, return_date date, t.emp_id = e.emp_id);
CREATE TABLE travel
(destination_id number primary key,
departure_date date, return_date date, JOIN emp_id number(10) ON employees
(emp_id));
CREATE TABLE travel
(destination_id number primary key,
departure_date date, return_date date, emp_id number(10) REFERENCES employees
(emp_id));
(*)
22.
Which column name is valid?
1NUMBER
NUMBER
NUMBER_1$ (*)
1_NUMBER#
23. 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?
2001_PRODUCTS
PRODUCTS_2001 (*)
PRODUCTS_(2001)
PRODUCTS—2001
24.
Which statement about table and column names is
true?
Table and column names must begin with a
letter. (*)
Table and column names can begin with a
letter or a number.
Table and column names cannot include
special characters.
If any character other than letters or
numbers is used in a table or column name, the name must be enclosed in double
quotation marks.
25. You want to disable the FOREIGN KEY constraint that
is defined in the EMPLOYEES table on the DEPARTMENT_ID column. The constraint
is referenced by the name FK_DEPT_ID_01. Which statement should you issue?
ALTER TABLE employees
DISABLE 'fk_dept_id_01';
ALTER TABLE employees
DISABLE CONSTRAINT 'fk_dept_id_01';
ALTER TABLE employees
DISABLE fk_dept_id_01;
ALTER TABLE employees
DISABLE CONSTRAINT fk_dept_id_01;
(*)
26.
The DEPARTMENTS table contains these columns:
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
Evaluate this statement:
ALTER TABLE employees
ADD CONSTRAINT REFERENTIAL (manager_id)
TO departments(manager_id);
Which statement is true?
The ALTER TABLE statement creates a referential constraint from the
EMPLOYEES table to the DEPARTMENTS table.
The ALTER TABLE statement creates a referential constraint from the
DEPARTMENTS table to the EMPLOYEES table.
The
ALTER TABLE statement fails because the ADD CONSTRAINT clause contains a syntax
error. (*)
The ALTER TABLE statement succeeds, but
does NOT recreate a referential constraint.
27.
Evaluate this statement:
ALTER TABLE employees
ADD CONSTRAINT employee_id PRIMARY KEY;
Which result will the statement
provide?
A syntax error will be returned.
(*)
A constraint will be added to the
EMPLOYEES table.
An existing constraint on the EMPLOYEES
table will be overwritten.
An existing constraint on the EMPLOYEES
table will be enabled.
28. You need to add a PRIMARY KEY to the DEPARTMENTS
table. Which statement should you use?
ALTER TABLE departments
ADD PRIMARY KEY dept_id_pk (dept_id);
ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PK (dept_id);
ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PRIMARY KEY
(dept_id);
(*)
ALTER TABLE departments
ADD CONSTRAINT PRIMARY KEY dept_id_pk
(dept_id);
29.
This SQL command will do what?
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk FOREIGN
KEY(manager_id) REFERENCES employees(employee_id);
Alter the table employees and disable the emp_manager_fk constraint.
Add
a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must
already be an employee. (*)
Add a FOREIGN KEY constraint to the EMPLOYEES table restricting manager
ID to match every employee ID.
Alter table employees and add a FOREIGN
KEY constraint that indicates each employee ID must be unique.
30. You disabled the EMPLOYEE_ID_PK PRIMARY KEY
constraint on the ID column in the EMPLOYEES table and imported 100 records.
You need to enable the constraint and verify that the new and existing ID
column values do not violate the PRIMARY KEY constraint. Evaluate this
statement:
ALTER TABLE employees
ENABLE employee_id_pk;
Which statement is true?
The statement will achieve the desired
result.
The statement will execute, but will
ensure that the new ID values are unique.
The statement will execute, but will
not verify that the existing values are unique.
The statement will NOT execute because it
contains a syntax error. (*)
31.
Evaluate this statement
ALTER TABLE employees
ENABLE CONSTRAINT emp_id_pk;
For which task would you issue this
statement?
To add a new constraint to the
EMPLOYEES table
To disable an existing constraint on
the EMPLOYEES table
To activate a new constraint while
preventing the creation of a PRIMARY KEY index
To activate the previously disabled
constraint on the EMPLOYEE_ID column while creating a PRIMARY KEY index (*)
32.
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
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)
Evaluate this statement:
ALTER TABLE suppliers
DISABLE CONSTRAINT supplier_id_pk
CASCADE;
For which task would you issue this
statement?
To remove all constraint references to
SUPPLIERS table
To drop the FOREIGN KEY constraint on
the PRODUCTS 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 disable any dependent integrity
constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)
33.
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?
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 (*)
To disable the constraint on the PO_NUM
column while creating a PRIMARY KEY index
34. You need to add a PRIMARY KEY constraint on the
EMP_ID column of the EMPLOYEES table. Which ALTER TABLE statement should you
use?
ALTER TABLE employees
ADD CONSTRAINT PRIMARY KEY (emp_id);
ALTER TABLE employees
ADD CONSTRAINT emp_emp_id_pk PRIMARY
KEY(emp_id); (*)
ALTER TABLE employees
MODIFY emp_id PRIMARY KEY;
ALTER TABLE employees
MODIFY CONSTRAINT PRIMARY KEY (emp_id);
35.
Which constraint can only be created at the column
level?
NOT NULL (*)
FOREIGN KEY
UNIQUE
CHECK
36.
Which statement about constraints is true?
A single column can have only one
constraint applied.
PRIMARY KEY constraints can only be specified
at the column level.
NOT NULL constraints can only be
specified at the column level. (*)
UNIQUE constraints are identical to
PRIMARY KEY constraints.
37. You need to ensure that the LAST_NAME column does
not contain null values. Which type of constraint should you define on the
LAST_NAME column?
CHECK
UNIQUE
NOT NULL (*)
PRIMARY KEY
38. You need to ensure that the LAST_NAME column only
contains certain character values. No numbers or special characters are allowed.
Which type of constraint should you
define on the LAST_NAME column?
CHECK (*)
UNIQUE
NOT NULL
PRIMARY KEY
39.
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?
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. (*)
40. You need to ensure that each value in the SEAT_ID
column is unique or null. Which constraint should you define on the SEAT_ID
column?
CHECK
UNIQUE (*)
NOT NULL
PRIMARY KEY
41.
A table can only have one unique key constraint
defined. True or False?
True
False (*)
42.
Which statement about a non-mandatory foreign key
constraint is true?
A foreign key value cannot be null.
A foreign key value must be unique.
A foreign key value must match an existing
value in the parent table.
A foreign key value must either be null
or match an existing value in the parent table. (*)
43. When creating the EMPLOYEES table, which clause
could you use to ensure that salary values are 1000.00 or more?
CONSTRAINT CHECK salary > 1000
CHECK CONSTRAINT (salary >
1000)
CONSTRAINT employee_salary_min CHECK
salary > 1000
CONSTRAINT employee_salary_min CHECK
(salary >= 1000) (*)
CHECK CONSTRAINT employee_salary_min
(salary > 1000)
44.
Which statement about a FOREIGN KEY constraint is
true?
An index is automatically created for a
FOREIGN KEY constraint.
A FOREIGN KEY constraint requires the
constrained column to contain values that exist in the referenced Primary or
Unique key column of the parent table. (*)
A FOREIGN KEY constraint allows that a list of allowed values be checked
before a value can be added to the constrained column.
A FOREIGN KEY column can have a
different data type from the primary key column that it references.
45. You need to create the PROJECT_HIST table. The
table must meet these requirements:
The table must contain the EMPLOYEE_ID
and TASKED_HOURS columns for numeric data.
The table must contain the START_DATE
and END_DATE column for date values.
The table must contain the HOURLY_RATE
and PROJECT_COST columns for numeric data with precision and scale of 5,2 and
10,2 respectively.
The table must have a composite primary
key on the EMPLOYEE_ID and START_DATE columns.
Evaluate this CREATE TABLE statement:
CREATE TABLE project_hist
( employee_id NUMBER,
start_date DATE,
end_date DATE,
tasked_hours NUMBER,
hourly_rate NUMBER(5,2),
project_cost NUMBER(10,2),
CONSTRAINT project_hist_pk PRIMARY
KEY(employee_id, start_date));
How many of the requirements does the
CREATE TABLE statement satisfy?
None of the four requirements
All four of the requirements (*)
Only three of the requirements
Only two of the requirements
46. You need to create a composite primary key
constraint on the EMPLOYEES table. Which statement is true?
The PRIMARY KEY constraint must be
defined at the table level. (*)
A PRIMARY KEY constraint must be defined
for each column in the composite primary key.
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 and for each column in the composite primary key.
47.
How many PRIMARY KEY constraints can be created for
each table?
None
One and only one (*)
One or two
Unlimited
48.
Which of the following best describes the function
of a CHECK constraint?
A CHECK constraint enforces referential data integrity.
A
CHECK constraint defines restrictions on the values that can be entered in a
column or combination of columns. (*)
A CHECK constraint enforces uniqueness of the values that can be entered
in a column or combination of columns.
A CHECK constraint is created
automatically when a PRIMARY KEY constraint is created.
49. You need to enforce a relationship between the
LOC_ID column in the FACILITY table and the same column in the MANUFACTURER
table. Which type of constraint should you define on the LOC_ID column?
UNIQUE
NOT NULL
FOREIGN KEY (*)
PRIMARY KEY
50. Which of the following FOREIGN KEY Constraint
keywords identifies the table and column in the parent table?
RESEMBLES
ON DELETE CASCADE
REFERENTIAL
REFERENCES (*)
No comments:
Post a Comment