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 ������
BalasHapuswow! I appropriate your contribution for trainees. Thank you. keep in touch.
BalasHapusYou need to copy rows from the EMPLOYEE table to the EMPLOYEE_HIST table. What could you use in the INSERT statement to accomplish this task?
BalasHapusA subquery (*)
A SET clause
A function
An ON clause
The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL
Which INSERT statement will execute successfully?
INSERT INTO products(product_id, product_name)
VALUES (2958, 'Cable');
INSERT INTO products
VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE);
INSERT INTO products(product_id, product_name, supplier_id
VALUES (2958, 'Cable', 8690, SYSDATE);
INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700) (*)
Which of the following represents the correct syntax for an INSERT statement?
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
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);
The statement will fail because the subqueries are returning data from different rows.
No, this statement will return an error.
No, this does nothing.
Yes, this is a perfectly valid statement. (*)
What would happen if you issued a DELETE statement without a WHERE clause?
BalasHapusAll the rows in the table would be deleted. (*)
An error message would be returned.
Only one row would be deleted.
No rows would be deleted.
Using MERGE accomplishes an __________ and __________ simultaneously.
UPDATE; SELECT
INSERT; UPDATE (*)
UPDATE; DELETE
INSERT; SELECT
The default value must match the __________ of the column.
Table
Datatype (*)
Size
Column name
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 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, t.emp_id = e.emp_id);
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, emp_id number(10) REFERENCES employees (emp_id)); (*)
Which of the following SQL statements will create a table called Birthdays with three columns for storing employee number, name and date of birth?
CREATE table BIRTHDAYS (EMPNO, EMPNAME, BIRTHDATE);
CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Birthdate DATE); (*)
CREATE table BIRTHDAYS (employee number, name, date of birth);
CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Date of Birth DATE);
CREATE TABLE student_table
BalasHapus(id NUMBER(6),
lname VARCHAR(20),
fname VARCHAR(20),
lunch_num NUMBER(4));
Which of the following statements best describes the above SQL statement:
Creates a table named student_table with four columns: lname, fname, lunch, num
Creates a table named student with four columns: id, lname, fname, lunch_num
Creates a table named student_table with four columns: lname, fname, lunch, num
Creates a table named student_table with four columns: id, lname, fname, lunch_num (*)
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?
Four (*)
Six
Two
Zero
You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?
DATE
CHAR
NUMBER (*)
VARCHAR2
RENAME old_name to new_name can be used to:
Rename a row.
Rename a column.
Rename a table. (*)
All of the above.
You can use DROP COLUMN to drop all columns in a table, leaving a table structure with no columns. True or False?
True
False (*)
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 TABLE players
MODIFY (first_name VARCHAR2(10)); (*)
ALTER players TABLE
MODIFY COLUMN (first_name VARCHAR2(10));
ALTER TABLE players
RENAME first_name VARCHAR2(10);
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
MODIFY (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);
ALTER TABLE orders
CHANGE DATATYPE amount TO 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);
Comments can be added to a table by using the COMMENT ON TABLE statement. The comments being added are enclosed in:
Single quotes ' ' (*)
Double quotes " "
Parentheses ( )
Brackets { }
You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use?
BalasHapusRENAME employees emp;
ALTER TABLE employees TO emp;
RENAME employees TO emp; (*)
ALTER TABLE employees RENAME TO emp
A unique key constraint can only be defined on a not null column. True or False?
True
False (*)
Which constraint can only be created at the column level?
FOREIGN KEY
CHECK
UNIQUE
NOT NULL (*)
Which statement about the NOT NULL constraint is true?
The NOT NULL constraint requires a column to contain alphanumeric values.
The NOT NULL constraint can be defined at either the column level or the table level.
The NOT NULL constraint prevents a column from containing alphanumeric values.
The NOT NULL constraint must be defined at the column level. (*)
Which statement about a non-mandatory foreign key constraint is true?
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 match an existing value in the parent table.
A foreign key value must be unique.
Which clause could you use to ensure that cost values are greater than 1.00?
CONSTRAINT part_cost_ck CHECK (cost > 1.00) (*)
CHECK CONSTRAINT part_cost_ck (cost > 1.00)
CONSTRAINT CHECK cost > 1.00
CONSTRAINT CHECK part_cost_ck (cost > 1.00)
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 drop and recreate the PRIMARY KEY constraint on the PO_NUM column
To disable the constraint on the PO_NUM column while creating a PRIMARY KEY index
To disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column (*)
To create a new PRIMARY KEY constraint on the PO_NUM column
Your manager has just asked you to create a report that illustrates the salary range of all the employees at your company. Which of the following SQL statements will create a view called SALARY_VU based on the employee last names, department names, salaries, and salary grades for all employees? Use the EMPLOYEES, DEPARTMENTS, and JOB_GRADES tables. Label the columns Employee, Department, Salary, and Grade, respectively.
BalasHapusCREATE OR REPLACE VIEW salary_vu
AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
FROM employees e, departments d, job_grades j
WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal; (*)
CREATE OR REPLACE VIEW salary_vu
AS (SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
FROM employees emp, departments d, job grades j
WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal);
CREATE OR REPLACE VIEW salary_vu
AS SELECT e.empid "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
FROM employees e, departments d, job_grades j
WHERE e.department_id = d.department_id NOT e.salary BETWEEN j.lowest_sal and j.highest_sal;
CREATE OR REPLACE VIEW salary_vu
AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
FROM employees e, departments d, job_grades j
WHERE e.department_id equals d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal;
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 disable any dependent integrity constraints on the SUPPLIER_ID column in 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 SUPPLIERS table (*)
Which action can be performed by using DML statements?
BalasHapusDisabling an index
Creating PRIMARY KEY constraints
Deleting records in a table (*)
Altering a table
Which option would you use when creating a view to ensure that no DML operations occur on the view?
WITH READ ONLY (*)
FORCE
NOFORCE
WITH ADMIN OPTION
Evaluate this CREATE VIEW statement:
CREATE VIEW pt_view AS
(SELECT first_name, last_name, status, courseid, subject, term
FROM faculty f, course c
WHERE f.facultyid = c.facultyid);
Which type of view will this statement create?
Inline
Simple
Nested
Complex (*)
Which of the following keywords cannot be used when creating a view?
HAVING
WHERE
ORDER BY
They are all valid keywords when creating views. (*)
In order to query a database using a view, which of the following statements applies?
You can retrieve data from a view as you would from any table. (*)
The tables you are selecting from can be empty, yet the view still returns the original data from those tables.
Use special VIEW SELECT keywords.
You can never see all the rows in the table through the view.
A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?
True (*)
False
Which statement about an inline view is true?
An inline view is a schema object.
An inline view is a complex view.
An inline view is a subquery in the FROM clause, often named with an alias. (*)
An inline view can be used to perform DML operations.
When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?
CACHE (*)
MEMORY
NOCYCLE
NOCACHE
LOAD
Which pseudocolumn returns the latest value supplied by a sequence?
NEXTVAL
CURRVAL (*)
CURRENT
NEXT
Examine the code for creating this sequence:
CREATE SEQUENCE track_id_seq
INCREMENT BY 10
START WITH 1000 MAXVALUE 10000
What are the first three values that would be generated by the sequence?
100010011002
1000, 1010, 1020 (*)
1100, 1200, 1300
0, 1, 2
Which of the following statements best describes indexes and their use?
BalasHapusThey are just copies of data in no particular order.
They contain the column value and pointers to the data in the table, but the data is sorted. (*)
They contain all the rows and columns from the table
None of the above
Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?
ALTER TABLE employees
DROP INDEX last_name_idx;
DROP INDEX last_name_idx; (*)
DROP INDEX last_name_idx(last_name);
DROP INDEX last_name_idx(employees.last_name);
Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created?
A CHECK constraint
An index (*)
A FOREIGN KEY constraint
A PRIMARY KEY constraint
User BOB's schema contains an EMPLOYEES table. BOB executes the following statement:
GRANT SELECT ON employees TO mary WITH GRANT OPTION;
Which of the following statements can MARY now execute successfully? (Choose two)
(Choose all correct answers)
REVOKE SELECT ON bob.employees FROM bob;
SELECT FROM bob.employees; (*)
GRANT SELECT ON bob.employees TO PUBLIC; (*)
DROP TABLE bob.employees;
User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then grants select on the same table to User3. If User1 revokes select privileges from User2, will User3 be able to access the table?
No (*)
Yes
Which of the following statements is true?
Database Links allow users to work on remote database objects without having to log into the other database. (*)
Database Links are never used in the real world.
Database Links can be created by any user of a database. You do not need any special privileges to create them.
Database Links are pointers to another schema in the same database.
User SUSAN creates an EMPLOYEES table, and then creates a view EMP_VIEW which shows only the FIRST_NAME and LAST_NAME columns of EMPLOYEES. User RUDI needs to be able to access employees' names but no other data from EMPLOYEES. Which statement should SUSAN execute to allow this?
GRANT SELECT ON emp_view ONLY TO rudi;
SELECT * FROM emp_view FOR rudi;
GRANT SELECT ON emp_view TO rudi; (*)
CREATE SYNONYM emp_view FOR employees;
You are the database administrator. You want to create a new user JONES with a password of MARK, and allow this user to create his own tables. Which of the following should you execute?
CREATE USER jones IDENTIFIED BY mark;
GRANT CREATE SESSION TO jones;
GRANT CREATE TABLE TO jones; (*)
GRANT CREATE SESSION TO jones;
GRANT CREATE TABLE TO jones;
CREATE USER jones IDENTIFIED BY mark;
GRANT CREATE TABLE TO jones;
CREATE USER jones IDENTIFIED BY mark;
GRANT CREATE SESSION TO jones;