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