SOLOSOLOKU: Final Exam Semester 2 - Part I(41-50)

Test: Final Exam Semester 2 - Part I (41-50)
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 9
(Answer all questions in this section)

41. Which statement should you use to add a FOREIGN KEY constraint to the DEPARTMENT_ID column in the EMPLOYEES table to refer to the DEPARTMENT_ID column in the DEPARTMENTS table? 

(1) Points

ALTER TABLE employees

MODIFY COLUMN dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);

ALTER TABLE employees

ADD CONSTRAINT dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);

(*)

ALTER TABLE employees

ADD FOREIGN KEY CONSTRAINT dept_id_fk ON (department_id) REFERENCES departments(department_id);


ALTER TABLE employees

ADD FOREIGN KEY departments(department_id) REFERENCES (department_id);


42. You need to add a PRIMARY KEY to the DEPARTMENTS table. Which statement should you use? 

(1) Points

ALTER TABLE departments ADD PRIMARY KEY dept_id_pk (dept_id);

ALTER TABLE departments ADD CONSTRAINT dept_id_pk PK (dept_id);

ALTER TABLE departments ADD CONSTRAINT dept_id_pk PRIMARY KEY (dept_id); (*)

ALTER TABLE departments ADD CONSTRAINT PRIMARY KEY dept_id_pk (dept_id);


43. 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?
(1) Points

ALTER TABLE employees DISABLE 'fk_dept_id_01';

ALTER TABLE employees DISABLE CONSTRAINT 'fk_dept_id_01';

ALTER TABLE employees DISABLE fk_dept_id_01;

ALTER TABLE employees DISABLE CONSTRAINT fk_dept_id_01; (*)

44. What is the highest number of NOT NULL constraints you can have on a table?

(1) Points

5

10

3

You can have as many NOT NULL constraints as you have columns in your table. (*)


45. Which two statements about NOT NULL constraints are true? (Choose two)
(1) Points
(Choose all correct answers)

The Oracle Server creates a name for an unnamed NOT NULL constraint. (*)

A NOT NULL constraint can be defined at either the table or column level.

The NOT NULL constraint requires that every value in a column be unique.

Columns without the NOT NULL constraint can contain null values by default.

You CANNOT add a NOT NULL constraint to an existing column using the ALTER TABLE ADD CONSTRAINT statement. (*)


46. You need to add a NOT NULL constraint to the COST column in the PART table. Which statement should you use to complete this task? 
(1) Points

ALTER TABLE part

MODIFY (cost part_cost_nn NOT NULL);

ALTER TABLE part

MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);

(*)

ALTER TABLE part

MODIFY COLUMN (cost part_cost_nn NOT NULL);

ALTER TABLE part

ADD (cost CONSTRAINT part_cost_nn NOT NULL);


47. You need to ensure that each value in the SEAT_ID column is unique or null. Which constraint should you define on the SEAT_ID column?
(1) Points

CHECK

UNIQUE (*)

NOT NULL

PRIMARY KEY

48. Primary Key, Foreign Key, Unique Key and Check Constraints can be added at which two levels? (Choose two) 
(1) Points
(Choose all correct answers)

Null Field

Table (*)

Row

Dictionary

Column (*)


49. You need to ensure that the LAST_NAME column only contains certain character values. No numbers or special characters are allowed. Which type of constraint should you define on the LAST_NAME column?
(1) Points

CHECK (*)

UNIQUE

NOT NULL

PRIMARY KEY

50. Which statement about constraints is true? 

(1) Points

A single column can have only one constraint applied.

PRIMARY KEY constraints can only be specified at the column level.

NOT NULL constraints can only be specified at the column level. (*)

UNIQUE constraints are identical to PRIMARY KEY constraints.

Test: Final Exam Semester 2 - Part I (41-50)

Oracle Academy Test: Final Exam Semester 2 - Part I (1-10)

Oracle Academy my learning

Oracle Academy Test: Final Exam Semester 2 - Part I (1-10)

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 8
(Answer all questions in this section)

Part I of the Semester 2 Final Exam covers Sections 8-9 of Database Programming with SQL.

1. Which of the following SQL statements will create a table called Birthdays with three columns for storing employee number, name and date of birth? 
(1) Points

CREATE table BIRTHDAYS (EMPNO, EMPNAME, BIRTHDATE);

CREATE table BIRTHDAYS (employee number, name, date of birth);

CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Birthdate DATE); (*)

CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Date of Birth DATE);


2. Which statement about table and column names is true? 

(1) Points

Table and column names must begin with a letter. (*)

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.


3. Evaluate this CREATE TABLE statement:

1. CREATE TABLE customer#1 (

2. cust_1 NUMBER(9),

3. sales$ NUMBER(9),

4. 2date DATE DEFAULT SYSDATE);

Which line of this statement will cause an error? 

(1) Points
1

2

3

4 (*)


4. You are creating the EMPLOYEES table. This table should contain the COMMISSION_PCT column and use a value of 10 percent if no commission value is provided when a record is inserted. Which line should you include in the CREATE TABLE statement to accomplish this task?
(1) Points

commission_pct NUMBER(4,2) DEFAULT 0.10 (*)

commission_pct NUMBER(4,2) DEFAULT = 0.10

commission_pct NUMBER(4,2) DEFAULT (0.10)

commission_pct NUMBER(4,2) (DEFAULT, 0.10)


5. Which CREATE TABLE statement will fail?

(1) Points

CREATE TABLE date_1 (date_1 DATE);

CREATE TABLE date (date_id NUMBER(9)); (*)

CREATE TABLE time (time_id NUMBER(9));

CREATE TABLE time_date (time NUMBER(9));

6. Which column name is valid? 
(1) Points

1NUMBER

NUMBER

NUMBER_1$ (*)

1_NUMBER#


7. You are designing a table for the Human Resources department. This table must include a column that contains each employee's hire date. Which data type should you specify for this column? 

(1) Points

CHAR

DATE (*)

TIMESTAMP

INTERVAL YEAR TO MONTH


8. Which data types stores variable-length character data? (Select two).
(1) Points
(Choose all correct answers)

CHAR

NCHAR

CLOB (*)

VARCHAR2 (*)


9. You need to store the HIRE_DATE value with a time zone displacement value and allow data to be returned in the user's local session time zone. Which data type should you use? 
(1) Points

DATETIME

TIMESTAMP

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE (*)


10. 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? 
(1) Points

Zero

Two

Four (*)

Six

Oracle Academy Test: Final Exam Semester 2 - Part I (1-10)

Quiz Test Section 1: Quiz: Entities, Instances, Attributes and Identifiers

Oracle Academy My Learning

Quiz Test Section 1: Entities, Instances, Attributes and Identifiers

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 1
(Answer all questions in this section)


1. What is the purpose of a Unique Identifier? 

(1) Points


To uniquely determine a table and columns within that table.

To identify a specific row within a table, using one or more columns and/or foreign keys.

Create an entity that is unlike any other entity aside from itself.

To identify one unique instance of an entity, by using one or more attributes and/or relationships. (*)

2. Some of the following could be attributes of an ENTITY called PERSON. Select the incorrect attributes for PERSON. (Choose Two) 
(1) Points

(Choose all correct answers)

Age

Freddy Wilson (*)

Name

Priya Hansenna (*)


3. Which of the following statements about attributes are true? (Choose Two) 
(1) Points

(Choose all correct answers)

They describe, qualify, quantify, classify, or specify an entity. (*)

They are often adjectives.

They have a data type such as a number or character string. (*)

They must be single valued unless they belong to more than one entity.

4. Unique Identifiers.... 

(1) Points

distinguish one entity from another

distinguish one instance of an entity from all other instances of that entity (*)

distinguish all entities in a database

distinguishes nothing


5. In the following statements, find two examples of ENTITY: Instance. (Choose Two) 
(1) Points
(Choose all correct answers)

DAIRY PRODUCT: cow (*)

VEGETABLE: grows

BOOK: Biography of Mahatma Gandhi (*)

TRAIN: runs

6. An entity may have which of the following? 

(1) Points

experiences

instances (*)


tables

none of the above


7. Entities are usually verbs. True or False? 

(1) Points

True

False (*)

8. A/an _________ is a piece of information that in some way describes an entity. It is a property of the entity and it quantifies, qualifies, classifies or specifies the entity. 
(1) Points

ERD

Process

Table

Attribute (*)

9. All of the following would be instances of the entity PERSON except which? 

(1) Points

David Jones

Male (*)

Angelina Rosalie

Grace Abinajam

10. Which of the following entities most likely contains invalid attributes? 

(1) Points


Entity: Home. Attributes: Number of Bedrooms, Owner, Address, Date Built

Entity: Pet. Attributes: Name, Birthdate, Owner

Entity: Car. Attributes: Owner Occupation, Owner Salary, Speed (*)

Entity: Mother. Attributes: Name, Birthdate, Occupation, Salary


11. In a physical data model, an attribute is represented as a/an 

(1) Points

Column (*)

Row

Instance

Foreign Key


12. The word "Volatile" means.... 

(1) Points

Changing constantly; unstable (*)

Static; unlikely to change

Large quantity

Limited quantity

Quiz Test Section 1: Entities, Instances, Attributes and Identifiers

Section 1 Quiz Test: Group By and Having Clauses, ROLLUP and CUBE Operations, and Grouping Sets

Oracle Academy My Learning

Section 1 Quiz Test: Group By and Having Clauses, ROLLUP and CUBE Operations, and Grouping Sets

Test: Section 1 Quiz Group By and Having Clauses
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 1 Quiz
(Answer all questions in this section)

1. Which of the following SQL statements could display the number of people with the same last name: Mark for Review

(1) Points

SELECT first_name, last_name, COUNT(employee_id)

FROM EMPLOYEES

GROUP BY last_name;


SELECT employee_id, COUNT(last_name)

FROM EMPLOYEES

GROUP BY last_name;



SELECT last_name, COUNT(last_name)

FROM EMPLOYEES

GROUP BY last_name;

(*)



SELECT employee_id, DISTINCT(last_name)

FROM EMPLOYEES

GROUP BY last_name;



2. The use of GROUP BY GROUPING SETS(....) can speed up the execution of complex report statements? (True or False) Mark for Review

(1) Points


True (*)

False

3. The following is a valid statement:

SELECT MAX(AVG(salary))

FROM employees

GROUP BY department_id;

True or False?

Mark for Review
(1) Points

True (*)

False

4. Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.

SELECT COUNT(last_name), grade, gender

FROM STUDENTS

GROUP_BY ?????;

Mark for Review

(1) Points

last_name

last_name, grade

grade, gender (*)

last_name, gender


5. Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)

FROM employees

GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))

What data will this query generate?

Mark for Review
(1) Points


Total salaries for (department_id, job_id) and (department_id, manager_id) (*)

Total salaries for (department_id, job_id, manager_id)

Total for (job_id, manager_id)

The statement will fail.


6. If you want to include subtotals and grant totals for all columns mentioned in a GROUP BY clause you should use which of the following extensions to the GROUP BY clause? Mark for Review
(1) Points
ROLLUP

CUBE (*)

GROUP BY ALL COLUMNS

HAVING

7. Is the following statement correct?
SELECT department_id, AVG(salary)

FROM employees;


Mark for Review
(1) Points

No, because a GROUP BY department_id clause is needed (*)


No, because the SELECT clause cannot contain both individual columns and group functions

No, because the AVG function cannot be used on the salary column

Yes

8. Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)

FROM employees

GROUP BY GROUPING SETS(.......);

Select the correct GROUP BY GROUPING SETS clause from the following list:

Mark for Review

(1) Points

GROUP BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id), (department_id, manager_id)

GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)

GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id)) (*)

GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary), (manager_id, job_id))


9. How would you alter the following query to list only employees where more than one employee exists with the same last_name:

SELECT last_name, COUNT(employee_id)

FROM EMPLOYEES

GROUP BY last_name;

Mark for Review

(1) Points


SELECT last_name, COUNT(employee_id)

FROM EMPLOYEES

WHERE COUNT(*) > 1

GROUP BY last_name


SELECT last_name, COUNT(last_name)

FROM EMPLOYEES

GROUP BY last_name

HAVING COUNT(last_name) > 1;

(*)


SELECT last_name, COUNT(last_name)

FROM EMPLOYEES

GROUP BY last_name

EXISTS COUNT(last_name) > 1;


SELECT employee_id, DISTINCT(last_name)

FROM EMPLOYEES

GROUP BY last_name

HAVING last_name > 1;

10. Is the following statement correct:

SELECT first_name, last_name, salary, department_id, COUNT(employee_id)

FROM employees

WHERE department_id = 50

GROUP BY last_name, first_name, department_id;

Mark for Review

(1) Points


Yes

No, beause you cannot have a WHERE-clause when you use group functions.


No, because the statement is missing salary in the GROUP BY clause (*)

Yes, because Oracle will correct any mistakes in the statement itself

11. Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)

FROM employees

GROUP BY ROLLUP(department_id, manager_id)

What extra data will this query generate?

Mark for Review
(1) Points

Subtotals for department_id, and grand totals for salary.


Subtotals for department_id, job_id and grand totals for salary.

Subtotals for department_id, job_id, manager_id and grand totals for salary.

The statement will fail. (*)

Section 1 Quiz Test: Group By and Having Clauses, ROLLUP and CUBE Operations, and Grouping Sets

Oracle Academy section 11 Quiz: Introduction to Relational Database Concepts

Oracle Academy section 11 Quiz: Introduction to Relational Database Concepts 

Test: Section 11 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 11 Quiz
(Answer all questions in this section)

1. One or more columns in a primary key can be null. True or False?
True
False (*)
2. Foreign keys cannot be null when:
It is part of a primary key. (*)
It refers to another table.
It contains three or more columns.
3. A foreign key always refers to a primary key in the same table. True or False?
True
False (*)
4. The explanation below defines which constraint type:
A primary key must be unique, and no part of the primary key can be null.
Entity integrity. (*)
Referential integrity.
Column integrity.
User-defined integrity.
5. The explanation below defines which constraint type:
A column must contain only values consistent with the defined data format of the column.
Entity integrity.
Referential integrity.
Column integrity. (*)
User-defined integrity.
6. Column integrity refers to:
Columns always having values.
Columns always containing positive numbers.
Columns always containing values consistent with the defined data format. (*)
Columns always containing text data less than 255 characters.
7. The explanation below is an example of what constraint type:
The value in the dept_no column of the EMPLOYEES table must match a value in the dept_no column in the DEPARTMENTS table.
Entity integrity.
Referential integrity. (*)
Column integrity.
User-defined integrity.
8. Identify all of the correct statements that complete this sentence: A primary key is: (Choose Three)
A single column that uniquely identifies each row in a table. (*)
A set of columns that uniquely identifies each row in a table. (*)
A set of columns and keys in a single table that uniquely identifies each row in a single table. (*)
Only one column that cannot be null.
9. The explanation below is an example of what constraint type:
If the value in the balance column of the ACCOUNTS table is below 100, we must send a letter to the account owner which will require extra programming to enforce.
Entity integrity.
Referential integrity.
Column integrity.
User-defined integrity. (*)
10. A table does not have to have a primary key. True or False?
True (*)
False
Oracle Academy section 11 Quiz: Introduction to Relational Database Concepts 

SOLOSOLOKU: Database Programming with SQL Final Exam

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

SOLOSOLOKU: Design Section 4 Quiz

Database Design - Student - English

Test: Section 4 Quiz

Review your answers, feedback, and question scores below.
An asterisk (*) indicates a correct answer.

Section 4 Quiz

(Answer all questions in this section)

1. A business rule such as "All accounts must be paid in full within 10 days 
of billing" is best enforced by:

Making the relationship between CUSTOMER and PAYMENT fully 

mandatory and 1:1 on both sides.

Creating a message to be printed on every bill that reminds the 
customer to pay within ten days.

Making the payment attribute mandatory.

Creating additional programming code to identify and report accounts past due. (*)

2. A business rule such as "We only ship goods after customers have completely 
paid any outstanding balances on their account" is best enforced by:

Making the payment attribute optional.

Creating additional programming code to verify no goods are 
shipped until the account has been settled in full. (*)

Making the payment attribute null.

We need to trust our customers, and we know they will pay some day.

3. Why is it important to identify and document business rules?

It allows you to create a complete data model and then check it for accuracy. (*)

It allows you to improve the client's business.

It ensures that the data model will automate all manual processes.

None of the above

4. How would you model a business rule that states that on a student's birthday, 
he does not have to attend his classes?

Use a subtype

Make the attribute Birthdate mandatory

Use a supertype

You cannot model this. You need to document it (*)

5. Business rules are important to data modelers because:

A. They capture all of the needs, processes, and required functionality of the business. (*)

B. All Business rules are easily implemented in the ERD diagram.

C. The data modeler must focus on structural rules, because they are easily represented diagrammatically and eliminate other rules that involve extra procedures or programming.

D. Both A and C are true.

Test: Section 4 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 4 Quiz
(Answer all questions in this section)

6. Can all constraints be modeled on an ER diagram?

No, but you just explain them to the users so they can enforce them
No, in which case you should let the database administrator handle them
No, and those that cannot be modeled should be listed on a separate document
to be handled programmatically (*)
Yes, all constraints must be modeled and shown on the ER diagram

7. Why is it important to identify and document structural rules?

Ensures we know what data to store and how that data works together. (*)
Ensures nothing. There are no benefits to be gained from 
documenting your Structural Business Rules. We need to concentrate on the Procedural Business Rules only.
Ensures we know what processes are in place and how to program them.
All of the Above.

8. A supertype can only have two subtypes and no more. True or False?

True
False (*)

Incorrect Incorrect. Refer to Section 4 Lesson 1.


9. All ER diagrams must have one of each of the following: (Choose two)
(Choose all correct answers)
One or more Entities (*)
At least one supertype and subtype
Relationships between entities (*)
Arcs

10. A subtype is drawn on an ERD as an entity inside the "softbox" of the supertype. True or False?
True (*)
False

Test: Section 4 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 4 Quiz
(Answer all questions in this section)

11. A subtype is shown on an ERD as an entity with a one to many relationship to the supertype. True or False?
True
False (*)

Incorrect Incorrect. Refer to Section 4 Lesson 1.


12. Which of the following is true about subtypes?  
Subtypes must be mutually exclusive. (*)
Subtypes must not be mutually exclusive.
Subtypes should not be exhaustive.
One instance of a supertype may belong to two subtypes.

Incorrect Incorrect. Refer to Section 4 Lesson 1.

13. A Supertype can have only one subtype. True or False?  
True
False (*)

Incorrect Incorrect. Refer to Section 4 Lesson 1.

14. A subtype can have a relationship not shared by the supertype. True or False?
True (*)
False

15. All instances of a subtype may be an instance of the supertype but does not have to. True or False?

True
False (*)