Test: Database Programming with SQL Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 12
(Answer all questions in this section)
1. If a default value was set for a null column, Oracle sets the column to the default value. However, if no default value was set when the column was created, Oracle inserts an empty space. True or False?
True
False (*)
2. The MERGE statement first tries to update one or more rows in a table that match the criteria; if no row matches the criteria for the update, a new row will automatically be inserted instead. True or False?
True (*)
False
3. Assume all the column names are correct. The following SQL statement will execute which of the following?
INSERT INTO departments
(department_id, department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
'Public Relations' will be inserted into the manager_name column.
100 will be inserted into the department_id column.
1700 will be inserted into the manager_id column.
70 will be inserted into the department_id column. (*)
4. You need to add a row to an existing table. Which DML statement should you use?
INSERT (*)
UPDATE
DELETE
CREATE
5. Which two commands can be used to modify existing data in a database row?
(Choose all correct answers)
UPDATE (*)
MERGE (*)
SELECT
DELETE
Test: Database Programming with SQL Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 12
(Answer all questions in this section)
6. Which of the following statements best describes what will happen to the student table in this SQL statement?
UPDATE students
SET lunch_number =
(SELECT lunch_number
FROM student
WHERE student_id = 17)
WHERE student_id = 19;
Inserts a new row into the students table.
The statement updates the student_table by replacing student id 19's lunch number with student id 17's lunch number. (*)
Does nothing as you cannot use subqueries in UPDATE statements.
Deletes student 17's lunch_number and inserts a new value from student 19.
7. 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?
DELETE FROM products
WHERE supplier_id IN
(SELECT supplier_id FROM suppliers 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 UPPER(city) = 'ATLANTA';
8. Examine the structures of the PLAYERS, MANAGERS, and TEAMS tables:
PLAYERS:
PLAYER_ID NUMBER Primary Key
LAST_NAME VARCHAR2 (30)
FIRST_NAME VARCHAR2 (25)
TEAM_ID NUMBER
MGR_ID NUMBER
SIGNING_BONUS NUMBER(9,2)
SALARY NUMBER(9,2)
MANAGERS:
MANAGER_ID NUMBER Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
TEAM_ID NUMBER
TEAMS:
TEAM_ID NUMBER Primary Key
TEAM_NAME VARCHAR2 (20)
OWNER_LAST_NAME VARCHAR2 (20)
OWNER_FIRST_NAME VARCHAR2 (20)
Which situation would require a subquery to return the desired result?
To display the names of each player on the Lions team
To display the maximum and minimum player salary for each team
To display the names of the managers for all the teams owned by a given owner (*)
To display each player, their manager, and their team name for all teams with an id value greater than 5000
Section 13
(Answer all questions in this section)
9. 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 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 created.
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.
10. Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?
ALTER TABLE
TRUNCATE TABLE (*)
DROP TABLE
MODIFY
Test: Database Programming with SQL Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 13
(Answer all questions in this section)
11. 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.
12. 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));
(*)
13. When should you use the SET UNUSED command?
You should use it when you need a quick way of dropping a column. (*)
Never, there is no SET UNUSED command.
You should only use this command if you want the column to still be visible when you DESCRIBE the table.
You should use it if you think the column may be needed again later.
14. 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 ALTER TABLE statement
The DELETE statement
The DROP TABLE statement
The TRUNCATE TABLE statement (*)
15. A column that will be used to store binary data up to 4 Gigabytes in size should be defined as which datatype?
BLOB (*)
LONG
NUMBER
LONGRAW
Test: Database Programming with SQL Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 13
(Answer all questions in this section)
16. Which data types stores variable-length character data? Select two.
(Choose all correct answers)
CLOB (*)
NCHAR
CHAR
VARCHAR2 (*)
17. Which statement about table and column names is true?
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.
Table and column names must begin with a letter. (*)
18. Once they are created, external tables are accessed with normal SQL statements. (True or False?)
True (*)
False
19. DCL, which is the acronym for Data Control Language, allows:
A Database Administrator the ability to grant privileges to users. (*)
The TRUNCATE command to be used.
The CONROL TRANSACTION statement can be used.
The ALTER command to be used.
Section 14
(Answer all questions in this section)
20. Which statement about constraints is true?
PRIMARY KEY constraints can only be specified at the column level.
UNIQUE constraints are identical to PRIMARY KEY constraints.
A single column can have only one constraint applied.
NOT NULL constraints can only be specified at the column level. (*)
Test: Database Programming with SQL Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 14
(Answer all questions in this section)
21. 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?
NOT NULL constraints CANNOT be defined at the table level. (*)
The CREATE TABLE statement does NOT define a PRIMARY KEY.
The NUMBER data types require precision values.
UNIQUE constraints must be defined at the column level.
22. Which of the following is not a valid Oracle constraint type?
PRIMARY KEY
EXTERNAL KEY (*)
NOT NULL
UNIQUE KEY
23. You can drop a column in a table with a simple ALTER TABLE DROP COLUMN statement, even if the column is referenced in a constraint. True or False?
True
False (*)
24. 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 'fk_dept_id_01';
ALTER TABLE employees
DISABLE CONSTRAINT 'fk_dept_id_01';
ALTER TABLE employees
DISABLE CONSTRAINT fk_dept_id_01;
(*)
25. Which of the following best describes the function of a CHECK constraint?
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.
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. (*)
Test: Database Programming with SQL Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 14
(Answer all questions in this section)
26. 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
Section 15
(Answer all questions in this section)
27. 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 CONSTRAINT CHECK
WITH READ ONLY
28. For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?
Prohibits DML actions without administrator CHECK approval
Prohibits changing rows not returned by the subquery in the view definition. (*)
The view will allow the user to check it against the data dictionary
Allows for DELETES from other tables, including ones not listed in subquery
29. Given the following view, which operations would be allowed on the emp_dept view?
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name,
e.salary,
e.hire_date,
d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id >=50;
SELECT, UPDATE of all columns
SELECT, UPDATE of some columns, DELETE (*)
SELECT, DELETE
SELECT, INSERT
30. Which of these Keywords is typically used with a Top-N Analysis?
Number
Rownum (*)
Rowid
Sequence
Test: Database Programming with SQL Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 15
(Answer all questions in this section)
31. The CUSTOMER_FINANCE table contains these columns:
CUSTOMER_ID NUMBER(9)
NEW_BALANCE NUMBER(7,2)
PREV_BALANCE NUMBER(7,2)
PAYMENTS NUMBER(7,2)
FINANCE_CHARGE NUMBER(7,2)
CREDIT_LIMIT NUMBER(7)
You execute this statement:
SELECT ROWNUM "Rank", customer_id, new_balance
FROM (SELECT customer_id, new_balance FROM customer_finance)
WHERE ROWNUM <= 25
ORDER BY new_balance DESC;
What statement is true?
The statement will not necessarily return the 25 highest new balance values, as the inline view has no ORDER BY clause. (*)
The statement failed to execute because the ORDER BY clause does NOT use the Top-n column.
The statement failed to execute because an inline view was used.
The 25 greatest new balance values were displayed from the highest to the lowest.
32. Evaluate this view definition:
CREATE OR REPLACE VIEW part_name_v
AS SELECT DISTINCT part_name
FROM parts
WHERE cost >= 45;
Which of the following statements using the PART_NAME_V view will execute successfully?
SELECT *
FROM part_name_v;
(*)
DELETE FROM part_name_v
WHERE part_id = 56897;
UPDATE part_name_v
SET cost = cost * 1.23
WHERE part_id = 56990;
INSERT INTO part_name_v (part_id, part_name, product_id, cost)
VALUES (857986, ﾑcylinderﾒ, 8790, 3.45);
33. Given the following CREATE VIEW statement, what data will be returned?
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name,
e.salary,
e.hire_date,
d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id >=50;
First character from employee first_name concatenated to the last_name, the salary, the hire_date, and department_name of all employees working in department number 50.
First character from employee first_name concatenated to the last_name, the salary, the hire_date, and department_id of all employees working in department number 50 or higher.
First character from employee first_name concatenated to the last_name, the salary, the hire_date, and department_id of all employees working in department number 50.
First character from employee first_name concatenated to the last_name, the salary, the hire_date, and the department_name of all employees working in department number 50 or higher. (*)
34. Which option would you use to modify a view rather than dropping it and recreating it?
NOFORCE
FORCE
WITH ADMIN OPTION
CREATE OR REPLACE (*)
Section 16
(Answer all questions in this section)
35. Which dictionary view would you query to display the number most recently generated by a sequence?
USER_OBJECTS
USER_TABLES
USER_CURRVALUES
USER_SEQUENCES (*)
Test: Database Programming with SQL Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 16
(Answer all questions in this section)
36. When creating a sequence, which keyword or option specifies the minimum sequence value?
NOMAXVALUE
MAXVALUE
MINVALUE (*)
CYCLE
37. A sequence is a window through which data can be queried or changed. True or False?
True
False (*)
38. For which column would you create an index?
A column with a large number of null values (*)
A column that is infrequently used as a query search condition
A column that is updated frequently
A column which has only 4 distinct values.
39. Unique indexes are automatically created on columns that have which two types of constraints?
UNIQUE and PRIMARY KEY (*)
PRIMARY KEY and FOREIGN KEY
NOT NULL and UNIQUE
UNIQUE and FOREIGN KEY
40. Indexes can be used to speed up queries. True or False?
True (*)
False
Test: Database Programming with SQL Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 17
(Answer all questions in this section)
41. 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)
GRANT SELECT ON bob.employees TO PUBLIC; (*)
REVOKE SELECT ON bob.employees FROM bob;
DROP TABLE bob.employees;
SELECT FROM bob.employees; (*)
42. Granting an object privilege WITH GRANT OPTION allows the recipient to grant all object privileges on the table to other users. True or False?
True
False (*)
43. Which of the following statements is true?
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 allow users to work on remote database objects without having to log into the other database. (*)
Database Links are pointers to another schema in the same database.
44. Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ?
REGEXP (*)
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_REPLACE
45. Parentheses are not used to identify the sub expressions within the expression. True or False?
True
False (*)
Test: Database Programming with SQL Final Exam
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 17
(Answer all questions in this section)
46. User Kate wants to create indexes on tables in her schema. What privilege must be granted to Kate so that she can do this?
CREATE INDEX
CREATE ANY INDEX
ALTER TABLE
None; users do not need extra privileges to create indexes on tables in their own schema. (*)
47. User CHANG has been granted SELECT, UPDATE, INSERT, and DELETE privileges on the EMPLOYEES table. You now want to prevent Chang from adding or deleting rows from the table, while still allowing him to read and modify existing rows. Which statement should you use to do this?
REVOKE INSERT, DELETE ON employees FROM chang; (*)
REVOKE INSERT AND DELETE ON employees FROM chang;
REVOKE ALL ON employees FROM chang;
REMOVE INSERT, DELETE ON employees FROM chang;
Section 18
(Answer all questions in this section)
48. Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES ('A');
INSERT INTO mytab VALUES ('B');
COMMIT;
INSERT INTO mytab VALUES ('C');
ROLLBACK;
Which rows does the table now contain?
A, B, and C
A and B (*)
C
None of the above
49. User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers; What result will JANE see?
JANE will receive an error message because she is not allowed to query the table while BOB is updating it.
20 (*)
2
22
Section 19
(Answer all questions in this section)
50. A software verification and validation method.
Software engineering
Documentation
Unit testing (*)
Production
No comments:
Post a Comment