1. 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)
2. 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
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
SQL Programming in atlanta usaSQL Server Reporting Services training - We offer SQL Server certification SQL training and courses in the United States and Canada. Register today!
ReplyDelete