Final Exam Oracle Database Programming With SQL

Evaluate this statement: ALTER USER bob IDENTIFIED BY jim; Which statement about the result of executing this statement is true? 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.

A new password is assigned to user BOB.

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? 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;

ALTER TABLE line_item DISABLE CONSTRAINT product_id_fk;

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 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.

ALTER TABLE teams MODIFY (mgr_id VARCHAR2(15));

In a conditional multi-table insert, you can specify either __________ or __________. All; First First; Second All; Second Null; Default

All; First

Which of the following is created automatically by Oracle when a UNIQUE integrity constraint is created? An index A PRIMARY KEY constraint A FOREIGN KEY constraint A CHECK constraint

An index

When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory? CACHE NOCACHE NOCYCLE LOAD MEMORY

CACHE

Which statement would you use to alter a view? CREATE OR REPLACE VIEW MODIFY VIEW ALTER VIEW ALTER TABLE

CREATE OR REPLACE VIEW

Which CREATE TABLE statement will fail? 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);

CREATE TABLE date (date_id NUMBER(9));

A Primary Key that is made up of more than one column is called a: Multiple Primary Key Composite Primary Key Double Key Primary Multi-Key None of the Above

Composite Primary Key

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 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');

DELETE FROM products WHERE supplier_id IN (SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ATLANTA');

You need to remove a row from the EMPLOYEES table. Which statement would you use? INSERT with a WHERE clause DELETE with a WHERE clause UPDATE with a WHERE clause MERGE with a WHERE clause

DELETE with a WHERE clause

Which statement would you use to remove the EMP_ID_SEQ sequence? DROP SEQUENCE emp_id_seq; ALTER SEQUENCE emp_id_seq; DELETE SEQUENCE emp_id_seq; REMOVE SEQUENCE emp_id_seq;

DROP SEQUENCE emp_id_seq;

A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?

False

A table must have at least one not null constraint and one unique constraint. True or False?

False

The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT. True or False?

False

When you logout of Oracle, your data changes are automatically rolled back. True or False?

False

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? Two Zero Four Six

Four

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? (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');

INSERT INTO customers VALUES (200, 'InterCargo', null, 'tflanders', 'samerica'); INSERT INTO customers (cust_id, company, poc, location) VALUES (200, 'InterCargo', 'tflanders', 'samerica');

Which of the following statements will add a new customer to the customers table in the Global Fast Foods database? 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);

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);

When creating a sequence, which keyword or option specifies the minimum sequence value? MINVALUE CYCLE NOMAXVALUE MAXVALUE

MINVALUE

Which column name is valid? 1NUMBER NUMBER NUMBER_1$ 1_NUMBER#

NUMBER_1$

Which keyword would you use to grant an object privilege to all database users? PUBLIC ALL ADMIN USERS

PUBLIC

The table that contains the Primary Key in a Foreign Key Constraint is known as: Child Table Mother and Father Table Parent Table Detail Table

Parent Table

Which of these SQL functions used to manipulate strings is NOT a valid regular expression function? REGEXP_SUBSTR REGEXP REGEXP_REPLACE REGEXP_LIKE

REGEXP

System privileges are: 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.

Required to gain access to the database.

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? 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.

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;

Which command could you use to quickly remove all data from the rows in a table without deleting the table itself? DROP TABLE TRUNCATE TABLE ALTER TABLE MODIFY

TRUNCATE TABLE

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? 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.

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.

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 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.

The statement will NOT execute because it contains a syntax error.

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.

Time to be stored as a date with fractional seconds.

What is the purpose of including the WITH CHECK OPTION clause when creating a view? 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

To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future.

What is one advantage of using views? To provide restricted data access To provide data dependence To be able to store the same data in more than one place

To provide restricted data access

A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?

True

A multi-table insert statement can insert into more than one table. (True or False?)

True

A role can be granted to another role. True or False?

True

A table can have more than one UNIQUE key constraint. True or False?

True

DELETE statements can use correlated subqueries? (True or False)

True

If Oracle crashes, your changes are automatically rolled back. True or False?

True

Indexes can be used to speed up queries. True or False?

True

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?

True

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.

True

What kind of INDEX is created by Oracle when you create a primary key? UNIQUE INDEX NONUNIQUE INDEX INDEX Oracle cannot create indexes automatically.

UNIQUE INDEX

Which two commands can be used to modify existing data in a database row? (Choose all correct answers) DELETE SELECT UPDATE MERGE

UPDATE MERGE

A software verification and validation method. Documentation Unit testing Software engineering Production

Unit testing

Which of the following statements is a valid reason for using a view? 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.

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.

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? WITH CHECK OPTION FORCE WITH READ ONLY WITH CONSTRAINT CHECK

WITH CHECK OPTION

Which option would you use when creating a view to ensure that no DML operations occur on the view? WITH READ ONLY FORCE WITH ADMIN OPTION NOFORCE

WITH READ ONLY

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? 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));

You CANNOT modify the data type of the EMPLOYEE_ID column, as the table is not empty.

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 produce the same results by issuing the 'DELETE employees' statement. You can reverse this statement by issuing the ROLLBACK statement.

You can issue this statement to retain the structure of the employees table.

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 ____________________________________. 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.

possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas.