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)

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: Section 12 Quiz

Section 12 Quiz

(Answer all questions in this section)
1. Multi-table inserts can be conditional or unconditional. True or False?
True (*)
False

2. Using MERGE accomplishes an __________ and __________ simultaneously.
INSERT; UPDATE (*)
UPDATE; SELECT
UPDATE; DELETE
INSERT; SELECT

3. A column in a table can be given a default value. This option prevents NULL values 
from automatically being assigned to the column if a row is inserted without a specified value for the column. True or False ?
True (*)
False

4. The MERGE function combines the;
CREATE and UPDATE commands
INSERT and UPDATE commands (*)
ALTER and UPDATE commands
All of the above

5. A DEFAULT value can be specified for a column when the table is created. True or 
false?
True (*)
False

Section 12 Quiz
6. The PRODUCTS table contains these columns:
PROD_ID NUMBER(4)
PROD_NAME VARCHAR2(25)
PROD_PRICE NUMBER(3)

You want to add the following row of data to the PRODUCTS table:

(1) a NULL value in the PROD_ID column
(2) "6-foot nylon leash" in the PROD_NAME column
(3) "10" in the PROD_PRICE column

You issue this statement:

INSERT INTO products
VALUES (null,'6-foot nylon leash', 10);

What row data did you add to the table?
The row was created completely wrong. No data ended up in the correct columns.
The row was created with the correct data in all three columns. (*)
The row was created with the correct data in two of three columns.
The row was created with the correct data in one of the three columns.

7. If the employees table has 7 rows, how many rows are inserted into the copy_emps table with the following statement:
INSERT INTO copy_emps (employee_id, first_name, last_name, salary, department_id)
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees

7 rows, as no WHERE-clause restricts the rows returned on the subquery. (*)
10 rows will be created.
No rows, as the SELECT statement is invalid.
No rows, as you cannot use subqueries in an insert statement.

8. What is the quickest way to use today's date when you are creating a new row?
Use the TODAYS_DATE function.
Use the SYSDATE function. (*)
Simply write today's date in the format 'dd-mon-rrrr'.
Simply use the keyword DATE in the insert statement.

9. Insert statements can be combined with subqueries to create more than one row per statement. True or False?
True (*)
False

10.  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 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 IN customers (id, first_name, last_name, address, city, state, zip, phone_number);
Section 12 Quiz

11. Evaluate this statement:
DELETE FROM customer;

Which statement is true?
The statement deletes the first row in the CUSTOMERS table.
The statement removes the structure of the CUSTOMER table from the database.
The statement deletes all the rows from the CUSTOMER table. (*)
The statement deletes the CUSTOMER column.

12. The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
TEACHERS:
TEACHER_ID NUMBER(5)
NAME VARCHAR2(25)
SUBJECT_ID NUMBER(5)
HIRE_DATE DATE
SALARY NUMBER(9,2)

CLASS_ASSIGNMENTS:
CLASS_ID NUMBER(5)
TEACHER_ID NUMBER(5)
START_DATE DATE
MAX_CAPACITY NUMBER(3)

Which scenario would require a subquery to return the desired results?
You need to display the start date for each class taught by a given teacher.
You need to create a report to display the teachers who were hired more than five years ago.
You need to display the names of the teachers who teach classes that start within the next we
You need to create a report to display the teachers who teach more classes than the average number of classes taught by each teacher. (*)

13. Which of the following represents the correct syntax for an INSERT statement? Mark 
INSERT VALUES INTO customers (3178 J. Smith 123 Main Street Nashville TN 37777;
INSERT INTO customers VALUES '3178' 'J.' 'Smith' '123 Main Street' 'Nashville' 'TN' '377
INSERT INTO customers VALUES ('3178', 'J.', 'Smith', '123 Main Street', 'Nashville', 'TN','37777'); (*)
INSERT customers VALUES 3178, J., Smith, 123 Main Street, Nashville, TN, 37777;

14. What keyword in an UPDATE statement speficies the column that you want to change?
HAVING
SELECT
SET (*)
WHERE

15. When the WHERE clause is missing in a DELETE statement, what is the result?
Nothing. The statement will not execute.
All rows are deleted from the table. (*)
An error message is displayed indicating incorrect syntax.
The table is removed from the database.