Section 12
(Answer
all questions in this section)
1. In a
conditional multi-table insert, you can specify either __________ or
__________. Mark for Review
(1) Points
All;
First (*)
First;
Second
All;
Second
Null;
Default
2. A
multi-table insert statement can insert into more than one table. (True or
False?) Mark for Review
(1) Points
True
(*)
False
3. Which
of the following statements will add a new customer to the customers table in
the Global Fast Foods database?
Mark for Review
(1) Points
INSERT
INTO customers (id, first_name, last_name, address, city, state, zip,
phone_number)
VALUES (145, 'Katie', 'Hernandez', '92 Chico Way', 'Los
Angeles', 'CA', 98008, 8586667641);
(*)
INSERT
IN customers (id, first_name, last_name, address, city, state, zip,
phone_number);
INSERT
INTO customers (id, first_name, last_name, address, city, state, zip,
phone_number)
VALUES ("145", 'Katie', 'Hernandez', '92 Chico
Way', 'Los Angeles', 'CA', "98008", "8586667641");
INSERT
INTO customers
(id 145, first_name 'Katie', last_name 'Hernandez',
address '92 Chico Way', city 'Los Angeles', state 'CA', zip 98008, phone_number
8586667641);
4. You
have been instructed to add a new customer to the CUSTOMERS table. Because the
new customer has not had a credit check, you should not add an amount to the
CREDIT column.
The CUSTOMERS table contains these columns:
CUST_ID NUMBER(10)
COMPANY VARCHAR2(30)
CREDIT NUMBER(10)
POC VARCHAR2(30)
LOCATION VARCHAR2(30)
Which two INSERT statements will accomplish your
objective?
Mark for Review
(1) Points
(Choose
all correct answers)
INSERT
INTO customers
VALUES (cust_id, company, credit, poc, location) (200,
'InterCargo', 0, 'tflanders', 'samerica');
INSERT
INTO customers
VALUES (200, InterCargo, 0, tflanders, samerica);
INSERT
INTO customers
VALUES (200, 'InterCargo', null, 'tflanders',
'samerica');
(*)
INSERT
INTO customers (cust_id, company, poc, location)
VALUES (200, 'InterCargo', 'tflanders', 'samerica');
(*)
5. You
need to remove a row from the EMPLOYEES table. Which statement would you use? Mark for Review
(1) Points
INSERT
with a WHERE clause
DELETE
with a WHERE clause (*)
UPDATE
with a WHERE clause
MERGE
with a WHERE clause
Section 12
(Answer
all questions in this section)
6. 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 UPPER(city) = 'ATLANTA';
DELETE
FROM products
WHERE supplier_id =
(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 supplier_id IN
(SELECT supplier_id FROM suppliers WHERE UPPER(city) =
'ATLANTA');
(*)
7. DELETE
statements can use correlated subqueries? (True or False) Mark for Review
(1) Points
True
(*)
False
8. Which
two commands can be used to modify existing data in a database row? Mark for Review
(1) Points
(Choose
all correct answers)
DELETE
SELECT
UPDATE
(*)
MERGE
(*)
Section 13
(Answer
all questions in this section)
9. 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?
Mark for Review
(1) Points
You
CANNOT modify the data type of the EMPLOYEE_ID column, as the table is not
empty. (*)
ALTER
employee TABLE
MODIFY COLUMN (employee_id VARCHAR2(15));
ALTER
TABLE employee
MODIFY (employee_id VARCHAR2(9));
ALTER
TABLE employee
REPLACE (employee_id VARCHAR2(9));
10. 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?
Mark for Review
(1) Points
ALTER
TABLE teams
REPLACE (mgr_id VARCHAR2(15));
ALTER
TABLE teams
MODIFY (mgr_id VARCHAR2(15));
(*)
ALTER
teams TABLE
MODIFY COLUMN (mgr_id VARCHAR2(15));
ALTER
teams
MODIFY (mgr_id VARCHAR2(15));
You
CANNOT modify the data type of the MGR_ID column.
Section 13
(Answer
all questions in this section)
11. Which
command could you use to quickly remove all data from the rows in a table
without deleting the table itself?
Mark for Review
(1) Points
DROP
TABLE
TRUNCATE
TABLE (*)
ALTER
TABLE
MODIFY
12. 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.
13. The
FLASHBACK QUERY statement can restore data back to a point in time before the
last COMMIT. True or False? Mark for
Review
(1) Points
True
False
(*)
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 'DROP TABLE employee' statement.
You
can issue this statement to retain the structure of the employees table. (*)
You
can produce the same results by issuing the 'DELETE employees' statement.
You
can reverse this statement by issuing the ROLLBACK statement.
15. 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.
Section 13
(Answer
all questions in this section)
16. 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
Two
Zero
Four
(*)
Six
17. I have
a table named School_Friends in my schema. You want to build a table in your
schema named School_Friends. This is ______________, because
____________________________________.
Mark for Review
(1) Points
impossible;
no matter what, there can never be two tables with the same name, even if they
are in separate schemas.
impossible;
School_Friends is a reserved term in SQL.
possible;
my schema is separate from yours, and it is okay for us to have like-named
tables in our separate schemas. (*)
possible;
our data will merge into one table, and we can more easily access our mutual
friends information.
18. Which
column name is valid? Mark for
Review
(1) Points
1NUMBER
NUMBER
NUMBER_1$
(*)
1_NUMBER#
19. Which
CREATE TABLE statement will fail?
Mark for Review
(1) Points
CREATE
TABLE date (date_id NUMBER(9)); (*)
CREATE
TABLE time (time_id NUMBER(9));
CREATE
TABLE time_date (time NUMBER(9));
CREATE
TABLE date_1 (date_1 DATE);
Section 14
(Answer
all questions in this section)
20. A
column defined as NOT NULL can have a DEFAULT value of NULL. True or False? Mark for Review
(1) Points
True
False
(*)
Section 14
(Answer
all questions in this section)
21. A table
must have at least one not null constraint and one unique constraint. True or
False? Mark for Review
(1) Points
True
False
(*)
22. A table
can have more than one UNIQUE key constraint. True or False? Mark for Review
(1) Points
True
(*)
False
23. 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?
Mark for Review
(1) Points
The
statement will NOT execute because it contains a syntax error. (*)
The
statement will execute, but will not verify that the existing values are
unique.
The
statement will achieve the desired result.
The
statement will execute, but will ensure that the new ID values are unique.
24. The
LINE_ITEM table contains these columns:
LINE_ITEM_ID NUMBER PRIMARY KEY
PRODUCT_ID NUMBER(9) FOREIGN KEY references the ID column
of the PRODUCT table
QUANTITY NUMBER(9)
UNIT_PRICE NUMBER(5,2)
You need to disable the FOREIGN KEY constraint. Which
statement should you use?
Mark for Review
(1) Points
ALTER
TABLE line_item
DISABLE CONSTRAINT product_id_fk;
(*)
ALTER
TABLE line_item
ENABLE CONSTRAINT product_id_fk;
ALTER
TABLE line_item
DELETE CONSTRAINT product_id_fk;
ALTER
TABLE line_item
DROP CONSTRAINT product_id_fk;
25. A
Primary Key that is made up of more than one column is called a: Mark for Review
(1) Points
Multiple
Primary Key
Composite
Primary Key (*)
Double
Key
Primary
Multi-Key
None
of the Above
Section 14
(Answer
all questions in this section)
26. The
table that contains the Primary Key in a Foreign Key Constraint is known as: Mark for Review
(1) Points
Child
Table
Mother
and Father Table
Parent
Table (*)
Detail
Table
Section 15
(Answer
all questions in this section)
27. What is
one advantage of using views? Mark for
Review
(1) Points
To
provide restricted data access (*)
To
provide data dependence
To
be able to store the same data in more than one place
28. Which
of the following statements is a valid reason for using a view? Mark for Review
(1) Points
Views
allow access to the data because the view displays all of the columns from the
table.
Views
are used when you only want to restrict DML operations using a WITH CHECK
OPTION.
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. (*)
29. Which
statement would you use to alter a view?
Mark for Review
(1) Points
CREATE
OR REPLACE VIEW (*)
MODIFY
VIEW
ALTER
VIEW
ALTER
TABLE
30. 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)
COMMISSOIN NUMBER(7,2)
HIRE_DATE DATE
Which SELECT statement could be used to display the 10
lowest paid clerks that belong to department 70?
Mark for Review
(1) Points
SELECT
ROWNUM "Ranking", last_name||' ,'||first_name "Employee",
salary "Salary"
FROM (SELECT last_name, first_name, salary
FROM employees
ORDER BY salary)
WHERE ROWNUM <=10 AND job_id LIKE 'CLERK' AND
department_id = 70;
SELECT
ROWNUM "Ranking",last_name||','||first_name "Employee",
salary "Salary"
FROM (SELECT last_name, first_name, salary, job_id
FROM employees
WHERE job_id
LIKE 'CLERK' AND department_id = 70
ORDER BY
salary)
WHERE ROWNUM <=10;
(*)
SELECT
ROWNUM "Ranking", last_name||' ,'||first_name "Employee",
salary "Salary"
FROM (SELECT last_name, first_name, salary, job_id,
dept_id
FROM employees
WHERE ROWNUM
<=10
ORDER BY
salary)
WHERE job_id LIKE 'CLERK' AND department_id = 70;
The
only way is to use the data dictionary.
Section 15
(Answer
all questions in this section)
31. A Top-N
Analysis is capable of ranking a top or bottom set of results. True or False? Mark for Review
(1) Points
True
(*)
False
32. What is
the purpose of including the WITH CHECK OPTION clause when creating a view? Mark for Review
(1) Points
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
make sure that data is not duplicated in the view
33. 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
READ ONLY (*)
FORCE
WITH
ADMIN OPTION
NOFORCE
34. If a
database administrator wants to ensure that changes performed through a view do
not violate existing constraints, which clause should he include when creating
the view? Mark for Review
(1) Points
WITH
CHECK OPTION (*)
FORCE
WITH
READ ONLY
WITH
CONSTRAINT CHECK
Section 16
(Answer
all questions in this section)
35. When
creating a sequence, which keyword or option specifies the minimum sequence
value? Mark for Review
(1) Points
MINVALUE
(*)
CYCLE
NOMAXVALUE
MAXVALUE
Section 16
(Answer
all questions in this section)
36. Which
statement would you use to remove the EMP_ID_SEQ sequence? Mark for Review
(1) Points
DROP
SEQUENCE emp_id_seq; (*)
ALTER
SEQUENCE emp_id_seq;
DELETE
SEQUENCE emp_id_seq;
REMOVE
SEQUENCE emp_id_seq;
37. When
used in a CREATE SEQUENCE statement, which keyword specifies that a range of
sequence values will be preloaded into memory?
Mark for Review
(1) Points
CACHE
(*)
NOCACHE
NOCYCLE
LOAD
MEMORY
38. What
kind of INDEX is created by Oracle when you create a primary key? Mark for Review
(1) Points
UNIQUE
INDEX (*)
NONUNIQUE
INDEX
INDEX
Oracle
cannot create indexes automatically.
39. Indexes
can be used to speed up queries. True or False?
Mark for Review
(1) Points
True
(*)
False
40. Which
of the following is created automatically by Oracle when a UNIQUE integrity
constraint is created?
Mark for Review
(1) Points
An
index (*)
A
PRIMARY KEY constraint
A
FOREIGN KEY constraint
A
CHECK constraint
Section 17
(Answer
all questions in this section)
41. System
privileges are: Mark for Review
(1) Points
A
collection of objects, such as tables, views, and sequences.
Required
to manipulate the content of objects in the database.
Required
to gain access to the database. (*)
Named
groups of related privileges given to a user.
42. Evaluate
this statement:
ALTER USER bob IDENTIFIED BY jim;
Which statement about the result of executing this
statement is true?
Mark for Review
(1) Points
A
new user JIM is created from user BOB's profile.
The
user BOB is assigned the same privileges as user JIM.
The
user BOB is renamed and is accessible as user JIM.
A
new password is assigned to user BOB. (*)
43. Which
of these SQL functions used to manipulate strings is NOT a valid regular
expression function ? Mark for
Review
(1) Points
REGEXP_SUBSTR
REGEXP
(*)
REGEXP_REPLACE
REGEXP_LIKE
44. 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
45. A role
can be granted to another role. True or False?
Mark for Review
(1) Points
True
(*)
False
Section 17
(Answer
all questions in this section)
46. Scott
King owns a table called employees. He issues the following statement:
GRANT select ON employees TO PUBLIC;
Allison Plumb has been granted CREATE SESSION by the DBA.
She logs into the database and issues the following statement:
GRANT ï¾ select ON ï¾ scott_king.employees
TO jennifer_cho;
True or False: Allison's statement will fail.
Mark for Review
(1) Points
True
(*)
False
47. Which
keyword would you use to grant an object privilege to all database users? Mark for Review
(1) Points
PUBLIC
(*)
ALL
ADMIN
USERS
Section 18
(Answer
all questions in this section)
48. If Oracle
crashes, your changes are automatically rolled back. True or False? Mark for Review
(1) Points
True
(*)
False
49. When
you logout of Oracle, your data changes are automatically rolled back. True or
False? Mark for Review
(1) Points
True
False
(*)
Section 19
(Answer
all questions in this section)
50. A
software verification and validation method.
Mark for Review
(1) Points
Documentation
Unit
testing (*)
Software
engineering
Production
Super answer ������
ReplyDeletewow! I appropriate your contribution for trainees. Thank you. keep in touch.
ReplyDelete