Database Programming with SQL-Section 12 Quiz

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

Section 12 Quiz
(Answer all questions in this section)

1. You need to update the area code of employees that live in Atlanta. Evaluate this partial UPDATE statement:
UPDATE employee
SET area_code = 770

Which of the following should you include in your UPDATE statement to achieve the desired results?

 Mark for Review
(1) Points


WHERE city = 'Atlanta'; (*)


SET city = 'Atlanta';


LIKE 'At%';


UPDATE city = Atlanta;



Correct Correct


2. One employee has the last name of 'King' in the employees table. How many rows will be deleted from the employees table with the following statement?
DELETE FROM employees
WHERE last_name = 'king';

 Mark for Review
(1) Points


All rows with last_name = 'King' will be deleted.


No rows will be deleted, as no employees match the WHERE-clause. (*)


One will be deleted, as there exists one employee named King.


All the rows in the employees table will be deleted.



Incorrect Incorrect. Refer to Section 12 Lesson 2.


3. Using your knowledge of the employees table, what would be the result of the following statement:
DELETE FROM employees; Mark for Review
(1) Points


All rows in the employees table will be deleted if there are no constraints on the table. (*)


Nothing, no data will be changed.


The first row in the employees table will be deleted.


Deletes employee number 100.



Correct Correct


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

 Mark for Review
(1) Points


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


You need to create a report to display the teachers who teach more classes than the average number of classes taught by each teacher. (*)



Incorrect Incorrect. Refer to Section 12 Lesson 2.


5. DELETE statements can use correlated subqueries? (True or False) Mark for Review
(1) Points


True (*)


False



Correct Correct


Page 1 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

6. Multi-table inserts are used when the same source data should be inserted into _____________ target table. Mark for Review
(1) Points


Ten


A very large


More than one (*)


A data warehouse



Incorrect Incorrect. Refer to Section 12 Lesson 3.


7. A multi-table insert statement can insert into more than one table. (True or False?) Mark for Review
(1) Points


True (*)


False



Correct Correct


8. Using MERGE accomplishes an __________ and __________ simultaneously. Mark for Review
(1) Points


INSERT; UPDATE (*)


INSERT; SELECT


UPDATE; SELECT


UPDATE; DELETE



Correct Correct


9. The default value must match the __________ of the column. Mark for Review
(1) Points


Column name


Table


Datatype (*)


Size



Incorrect Incorrect. Refer to Section 12 Lesson 3.


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


True


False (*)



Incorrect Incorrect. Refer to Section 12 Lesson 3.


Previous Page 2 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

11. Insert statements can be combined with subqueries to create more than one row per statement. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


12. What is the quickest way to use today's date when you are creating a new row? Mark for Review
(1) Points


Simply use the keyword DATE in the insert statement.


Use the TODAYS_DATE function.


Use the SYSDATE function. (*)


Simply write today's date in the format 'dd-mon-rrrr'.



Incorrect Incorrect. Refer to Section 12 Lesson 1.


13. Which of the following statements will add a new customer to the customers table in the Global Fast Foods database? Mark for Review
(1) Points


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



Incorrect Incorrect. Refer to Section 12 Lesson 1.


14. Using the INSERT statement and assuming that a column can accept null values, how can you implicitly insert a null value in a column? Mark for Review
(1) Points


It is not possible to implicitly insert a null value in a column.


Use the NULL keyword.


Omit the column in the column list. (*)


Use the ON clause



Incorrect Incorrect. Refer to Section 12 Lesson 1.


15. When inserting rows into a table, all columns must be given values. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 12 Lesson 1.


Previous Page 3 of 3 Summary


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

Section 12 Quiz
(Answer all questions in this section)

1. You want to enter a new record into the CUSTOMERS table. Which two commands can be used to create new rows? Mark for Review
(1) Points


INSERT, CREATE


MERGE, CREATE


INSERT, MERGE (*)


INSERT, UPDATE



Incorrect Incorrect. Refer to Section 12 Lesson 2.


2. One employee has the last name of 'King' in the employees table. How many rows will be deleted from the employees table with the following statement?
DELETE FROM employees
WHERE last_name = 'king';

 Mark for Review
(1) Points


One will be deleted, as there exists one employee named King.


All rows with last_name = 'King' will be deleted.


All the rows in the employees table will be deleted.


No rows will be deleted, as no employees match the WHERE-clause. (*)



Correct Correct


3. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FAST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)

You want to execute one DML statement to change the salary of all employees in department 10 to equal the new salary of employee number 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?

 Mark for Review
(1) Points


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898);


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898 AND department_id = 10);


UPDATE employees
SET salary = SELECT salary FROM employees WHERE employee_id = 89898;


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898)
WHERE department_id = 10;
(*)




Incorrect Incorrect. Refer to Section 12 Lesson 2.


4. The PLAYERS table contains these columns:
PLAYER_ID NUMBER NOT NULL
PLAYER_LNAME VARCHAR2(20) NOT NULL
PLAYER_FNAME VARCHAR2(10) NOT NULL
TEAM_ID NUMBER
SALARY NUMBER(9,2)

You need to increase the salary of each player for all players on the Tiger team by 12.5 percent. The TEAM_ID value for the Tiger team is 5960. Which statement should you use?

 Mark for Review
(1) Points


UPDATE players (salary)
SET salary = salary * 1.125;


UPDATE players
SET salary = salary * .125
WHERE team_id = 5960;


UPDATE players
SET salary = salary * 1.125
WHERE team_id = 5960;
(*)



UPDATE players (salary)
VALUES(salary * 1.125)
WHERE team_id = 5960;



Incorrect Incorrect. Refer to Section 12 Lesson 2.


5. You need to update the area code of employees that live in Atlanta. Evaluate this partial UPDATE statement:
UPDATE employee
SET area_code = 770

Which of the following should you include in your UPDATE statement to achieve the desired results?

 Mark for Review
(1) Points


LIKE 'At%';


WHERE city = 'Atlanta'; (*)


SET city = 'Atlanta';


UPDATE city = Atlanta;



Correct Correct


Page 1 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

6. Which statement below will not insert a row of data into a table? Mark for Review
(1) Points


INSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);


INSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143352, 'Roberts', 'Cameron', DEFAULT);


INSERT INTO student_table
VALUES (143354, 'Roberts', 'Cameron', 6543);


INSERT INTO (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);
(*)




Incorrect Incorrect. Refer to Section 12 Lesson 3.


7. The MERGE function combines the: Mark for Review
(1) Points


CREATE and UPDATE commands


INSERT and UPDATE commands (*)


ALTER and UPDATE commands


All of the above



Incorrect Incorrect. Refer to Section 12 Lesson 3.


8. A multi-table insert statement must have a subquery at the end of the statement. (True or False?) Mark for Review
(1) Points


True (*)


False



Correct Correct


9. The DEFAULT keyword can be used in the following statements: Mark for Review
(1) Points


INSERT and UPDATE (*)


INSERT and DELETE


DELETE and UPDATE


All of the above



Correct Correct


10. Aliases can be used with MERGE statements. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


Previous Page 2 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

11. Insert statements can be combined with subqueries to create more than one row per statement. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


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

 Mark for Review
(1) Points


The row was created completely wrong. No data ended up in the correct columns.


The row was created with the correct data in two of three columns.


The row was created with the correct data in all three columns. (*)


The row was created with the correct data in one of the three columns.



Incorrect Incorrect. Refer to Section 12 Lesson 1.


13. Which statement about the VALUES clause of an INSERT statement is true? Mark for Review
(1) Points


The VALUES clause in an INSERT statement is mandatory in a subquery.


If no column list is specified, the values must be listed in the same order that the columns are listed in the table. (*)


Character, date, and numeric data must be enclosed within single quotes in the VALUES clause.


To specify a null value in the VALUES clause, use an empty string (" ").



Incorrect Incorrect. Refer to Section 12 Lesson 1.


14. You need to add a row to an existing table. Which DML statement should you use? Mark for Review
(1) Points


DELETE


CREATE


INSERT (*)


UPDATE



Incorrect Incorrect. Refer to Section 12 Lesson 1.


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

 Mark for Review
(1) Points


'Public Relations' will be inserted into the manager_name column.


70 will be inserted into the department_id column. (*)


100 will be inserted into the department_id column.


1700 will be inserted into the manager_id column.



Incorrect Incorrect. Refer to Section 12 Lesson 1.


Previous Page 3 of 3 Summary


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

Section 12 Quiz
(Answer all questions in this section)

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

 Mark for Review
(1) Points

(Choose all correct answers)


INSERT INTO customers
VALUES (200, 'InterCargo', null, 'tflanders', 'samerica');
(*)



INSERT INTO customers
VALUES (200, InterCargo, 0, tflanders, samerica);


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



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



Incorrect Incorrect. Refer to Section 12 Lesson 1.


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

 Mark for Review
(1) Points


70 will be inserted into the department_id column. (*)


100 will be inserted into the department_id column.


1700 will be inserted into the manager_id column.


'Public Relations' will be inserted into the manager_name column.



Correct Correct


3. When inserting rows into a table, all columns must be given values. True or False? Mark for Review
(1) Points


True


False (*)



Correct Correct


4. 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
 Mark for Review
(1) Points


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.



Correct Correct


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

 Mark for Review
(1) Points


The row was created with the correct data in two of three columns.


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 one of the three columns.



Correct Correct


Page 1 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

6. 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 ? Mark for Review
(1) Points


True (*)


False



Correct Correct


7. A multi-table insert statement can insert into more than one table. (True or False?) Mark for Review
(1) Points


True (*)


False



Correct Correct


8. Multi-table inserts can be conditional or unconditional. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


9. 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? Mark for Review
(1) Points


True


False (*)



Correct Correct


10. A DEFAULT value can be specified for a column when the table is created. True or false? Mark for Review
(1) Points


True (*)


False



Correct Correct


Previous Page 2 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

11. Evaluate this statement:
DELETE FROM customer;

Which statement is true?

 Mark for Review
(1) Points


The statement deletes the first row in the CUSTOMERS table.


The statement deletes all the rows from the CUSTOMER table. (*)


The statement deletes the CUSTOMER column.


The statement removes the structure of the CUSTOMER table from the database.



Incorrect Incorrect. Refer to Section 12 Lesson 2.


12. Which of the following represents the correct syntax for an INSERT statement? Mark for Review
(1) Points


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


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;



Incorrect Incorrect. Refer to Section 12 Lesson 2.


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

 Mark for Review
(1) Points


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



Incorrect Incorrect. Refer to Section 12 Lesson 2.


14. You need to update the expiration date of products manufactured before June 30th . In which clause of the UPDATE statement will you specify this condition? Mark for Review
(1) Points


The ON clause


The SET clause


The WHERE clause (*)


The USING clause



Incorrect Incorrect. Refer to Section 12 Lesson 2.


15. If the subquery returns one row, how many rows will be deleted from the employees table?
DELETE FROM employees
WHERE department_id =
    (SELECT department_id
     FROM departments
     WHERE department_name LIKE '%Public%');

 Mark for Review
(1) Points


No rows will be deleted.


One row will be deleted, as the subquery only returns one row.


All rows in the employees table of employees who work in the given department will be deleted. (*)


All rows in the employees table will be deleted, no matter the department_id.



Incorrect Incorrect. Refer to Section 12 Lesson 2.


Previous Page 3 of 3 Summary


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

Section 12 Quiz
(Answer all questions in this section)

1. 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 ? Mark for Review
(1) Points


True (*)


False



Correct Correct


2. Which statement below will not insert a row of data into a table? Mark for Review
(1) Points


INSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);


INSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143352, 'Roberts', 'Cameron', DEFAULT);


INSERT INTO student_table
VALUES (143354, 'Roberts', 'Cameron', 6543);


INSERT INTO (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);
(*)




Correct Correct


3. In a conditional multi-table insert, you can specify either __________ or __________. Mark for Review
(1) Points


First; Second


All; First (*)


All; Second


Null; Default



Incorrect Incorrect. Refer to Section 12 Lesson 3.


4. The default value must match the __________ of the column. Mark for Review
(1) Points


Table


Datatype (*)


Size


Column name



Correct Correct


5. 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? Mark for Review
(1) Points


True


False (*)



Correct Correct


Page 1 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

6. One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2 (20)
HIRE_DATE DATE
SALARY NUMBER(10)

Which UPDATE statement will accomplish your objective?

 Mark for Review
(1) Points


UPDATE employees last_name = 'cooper'
WHERE last_name = 'roper';


UPDATE employees
SET cooper = 'last_name'
WHERE last_name = 'roper';


UPDATE employees
SET last_name = 'roper'
WHERE last_name = 'cooper';


UPDATE employees
SET last_name = 'cooper'
WHERE last_name = 'roper'; (*)



Incorrect Incorrect. Refer to Section 12 Lesson 2.


7. When the WHERE clause is missing in a DELETE statement, what is the result? Mark for Review
(1) Points


The table is removed from the database.


All rows are deleted from the table. (*)


An error message is displayed indicating incorrect syntax.


Nothing. The statement will not execute.



Incorrect Incorrect. Refer to Section 12 Lesson 2.


8. The PLAYERS table contains these columns:
PLAYER_ID NUMBER NOT NULL
PLAYER_LNAME VARCHAR2(20) NOT NULL
PLAYER_FNAME VARCHAR2(10) NOT NULL
TEAM_ID NUMBER
SALARY NUMBER(9,2)

You need to increase the salary of each player for all players on the Tiger team by 12.5 percent. The TEAM_ID value for the Tiger team is 5960. Which statement should you use?

 Mark for Review
(1) Points


UPDATE players (salary)
SET salary = salary * 1.125;


UPDATE players
SET salary = salary * .125
WHERE team_id = 5960;


UPDATE players
SET salary = salary * 1.125
WHERE team_id = 5960;
(*)



UPDATE players (salary)
VALUES(salary * 1.125)
WHERE team_id = 5960;



Correct Correct


9. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FAST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)

You want to execute one DML statement to change the salary of all employees in department 10 to equal the new salary of employee number 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?

 Mark for Review
(1) Points


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898);


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898 AND department_id = 10);


UPDATE employees
SET salary = SELECT salary FROM employees WHERE employee_id = 89898;


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898)
WHERE department_id = 10;
(*)




Correct Correct


10. Evaluate this statement:
DELETE FROM customer;

Which statement is true?

 Mark for Review
(1) Points


The statement deletes all the rows from the CUSTOMER table. (*)


The statement deletes the CUSTOMER column.


The statement deletes the first row in the CUSTOMERS table.


The statement removes the structure of the CUSTOMER table from the database.



Correct Correct


Previous Page 2 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

11. The STUDENTS table contains these columns:
STU_ID NUMBER(9) NOT NULL
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
DOB DATE
STU_TYPE_ID VARCHAR2(1) NOT NULL
ENROLL_DATE DATE

You create another table, named FT_STUDENTS, with an identical structure.You want to insert all full-time students who have a STU_TYPE_ID value of "F" into the new table. You execute this INSERT statement:

INSERT INTO ft_students
   (SELECT stu_id, last_name, first_name, dob, stu_type_id, enroll_date
FROM students
WHERE UPPER(stu_type_id) = 'F');

What is the result of executing this INSERT statement?

 Mark for Review
(1) Points


An error occurs because you CANNOT use a subquery in an INSERT statement.


An error occurs because the INSERT statement does NOT contain a VALUES clause.


All full-time students are inserted into the FT_STUDENTS table. (*)


An error occurs because the FT_STUDENTS table already exists.



Incorrect Incorrect. Refer to Section 12 Lesson 1.


12. You need to copy rows from the EMPLOYEE table to the EMPLOYEE_HIST table. What could you use in the INSERT statement to accomplish this task? Mark for Review
(1) Points


A function


A SET clause


A subquery (*)


An ON clause



Incorrect Incorrect. Refer to Section 12 Lesson 1.


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

 Mark for Review
(1) Points

(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 (cust_id, company, poc, location)
VALUES (200, 'InterCargo', 'tflanders', 'samerica');
(*)



INSERT INTO customers
VALUES (200, 'InterCargo', null, 'tflanders', 'samerica');
(*)




Correct Correct


14. When inserting a new row, the null keyword can be included in the values list for any column that allows nulls. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


15. Using the INSERT statement and assuming that a column can accept null values, how can you implicitly insert a null value in a column? Mark for Review
(1) Points


Use the ON clause


It is not possible to implicitly insert a null value in a column.


Omit the column in the column list. (*)


Use the NULL keyword.



Correct Correct


Previous Page 3 of 3 Summary


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

Section 12 Quiz
(Answer all questions in this section)

1. Which of the following statements will add a new customer to the customers table in the Global Fast Foods database? Mark for Review
(1) Points


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



Correct Correct


2. You need to add a row to an existing table. Which DML statement should you use? Mark for Review
(1) Points


UPDATE


DELETE


INSERT (*)


CREATE



Correct Correct


3. Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause? Mark for Review
(1) Points


No, you can only create one row at a time when using the VALUES clause. (*)


No, there is no such thing as INSERT ... VALUES.


Yes, you can just list as many rows as you want; just remember to separate the rows with commas.



Incorrect Incorrect. Refer to Section 12 Lesson 1.


4. Using the INSERT statement and assuming that a column can accept null values, how can you implicitly insert a null value in a column? Mark for Review
(1) Points


It is not possible to implicitly insert a null value in a column.


Use the NULL keyword.


Use the ON clause


Omit the column in the column list. (*)



Correct Correct


5. 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
 Mark for Review
(1) Points


No rows, as the SELECT statement is invalid.


7 rows, as no WHERE-clause restricts the rows returned on the subquery. (*)


10 rows will be created.


No rows, as you cannot use subqueries in an insert statement.



Correct Correct


Page 1 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

6. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FAST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)

You want to execute one DML statement to change the salary of all employees in department 10 to equal the new salary of employee number 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?

 Mark for Review
(1) Points


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898)
WHERE department_id = 10;
(*)



UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898 AND department_id = 10);


UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898);


UPDATE employees
SET salary = SELECT salary FROM employees WHERE employee_id = 89898;



Correct Correct


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

 Mark for Review
(1) Points


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



Correct Correct


8. To change an existing row in a table, you can use the UPDATE or INSERT statements. True or False? Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 12 Lesson 2.


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

 Mark for Review
(1) Points


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


You need to create a report to display the teachers who teach more classes than the average number of classes taught by each teacher. (*)



Correct Correct


10. You want to enter a new record into the CUSTOMERS table. Which two commands can be used to create new rows? Mark for Review
(1) Points


INSERT, CREATE


MERGE, CREATE


INSERT, MERGE (*)


INSERT, UPDATE



Correct Correct


Previous Page 2 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

11. Using MERGE accomplishes an __________ and __________ simultaneously. Mark for Review
(1) Points


UPDATE; DELETE


INSERT; UPDATE (*)


UPDATE; SELECT


INSERT; SELECT



Correct Correct


12. Which statement below will not insert a row of data into a table? Mark for Review
(1) Points


INSERT INTO (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);
(*)



INSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);


INSERT INTO student_table
VALUES (143354, 'Roberts', 'Cameron', 6543);


INSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143352, 'Roberts', 'Cameron', DEFAULT);



Correct Correct


13. 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? Mark for Review
(1) Points


True


False (*)



Correct Correct


14. The DEFAULT keyword can be used in the following statements: Mark for Review
(1) Points


INSERT and UPDATE (*)


INSERT and DELETE


DELETE and UPDATE


All of the above



Correct Correct


15. Multi-table inserts can be conditional or unconditional. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


Previous Page 3 of 3 Summary


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

Section 12 Quiz
(Answer all questions in this section)

1. When the WHERE clause is missing in a DELETE statement, what is the result? Mark for Review
(1) Points


The table is removed from the database.


An error message is displayed indicating incorrect syntax.


All rows are deleted from the table. (*)


Nothing. The statement will not execute.



Correct Correct


2. If you are performing an UPDATE statement with a subquery, it MUST be a correlated subquery? (True or False) Mark for Review
(1) Points


True


False (*)



Incorrect Incorrect. Refer to Section 12 Lesson 2.


3. You need to update both the DEPARTMENT_ID and LOCATION_ID columns in the EMPLOYEES table using one UPDATE statement. Which clause should you include in the UPDATE statement to update multiple columns? Mark for Review
(1) Points


The SET clause (*)


The WHERE clause


The USING clause


The ON clause



Correct Correct


4. You need to delete a record in the EMPLOYEES table for Tim Jones, whose unique employee identification number is 348. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(5) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
PHONE NUMBER(10)

Which DELETE statement will delete the appropriate record without deleting any additional records?

 Mark for Review
(1) Points


DELETE *
FROM employees
WHERE employee_id = 348;


DELETE FROM employees
WHERE last_name = jones;


DELETE 'jones'
FROM employees;


DELETE FROM employees
WHERE employee_id = 348;
(*)




Incorrect Incorrect. Refer to Section 12 Lesson 2.


5. If the subquery returns one row, how many rows will be deleted from the employees table?
DELETE FROM employees
WHERE department_id =
    (SELECT department_id
     FROM departments
     WHERE department_name LIKE '%Public%');

 Mark for Review
(1) Points


All rows in the employees table of employees who work in the given department will be deleted. (*)


One row will be deleted, as the subquery only returns one row.


No rows will be deleted.


All rows in the employees table will be deleted, no matter the department_id.



Correct Correct


Page 1 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

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?

 Mark for Review
(1) Points


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.


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. (*)



Correct Correct


7. Insert statements can be combined with subqueries to create more than one row per statement. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


8. When inserting a new row, the null keyword can be included in the values list for any column that allows nulls. True or False? Mark for Review
(1) Points


True (*)


False



Correct Correct


9. Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause? Mark for Review
(1) Points


No, there is no such thing as INSERT ... VALUES.


No, you can only create one row at a time when using the VALUES clause. (*)


Yes, you can just list as many rows as you want; just remember to separate the rows with commas.



Correct Correct


10. The STUDENTS table contains these columns:
STU_ID NUMBER(9) NOT NULL
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
DOB DATE
STU_TYPE_ID VARCHAR2(1) NOT NULL
ENROLL_DATE DATE

You create another table, named FT_STUDENTS, with an identical structure.You want to insert all full-time students who have a STU_TYPE_ID value of "F" into the new table. You execute this INSERT statement:

INSERT INTO ft_students
   (SELECT stu_id, last_name, first_name, dob, stu_type_id, enroll_date
FROM students
WHERE UPPER(stu_type_id) = 'F');

What is the result of executing this INSERT statement?

 Mark for Review
(1) Points


All full-time students are inserted into the FT_STUDENTS table. (*)


An error occurs because you CANNOT use a subquery in an INSERT statement.


An error occurs because the FT_STUDENTS table already exists.


An error occurs because the INSERT statement does NOT contain a VALUES clause.



Correct Correct


Previous Page 2 of 3 Next Summary


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

Section 12 Quiz
(Answer all questions in this section)

11. The MERGE function combines the: Mark for Review
(1) Points


CREATE and UPDATE commands


INSERT and UPDATE commands (*)


ALTER and UPDATE commands


All of the above



Correct Correct


12. Multi-table inserts are used when the same source data should be inserted into _____________ target table. Mark for Review
(1) Points


A data warehouse


Ten


More than one (*)


A very large



Correct Correct


13. A multi-table insert statement can insert into more than one table. (True or False?) Mark for Review
(1) Points


True (*)


False



Correct Correct


14. 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? Mark for Review
(1) Points


True


False (*)



Correct Correct


15. Which statement below will not insert a row of data into a table? Mark for Review
(1) Points


INSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143352, 'Roberts', 'Cameron', DEFAULT);


INSERT INTO student_table
VALUES (143354, 'Roberts', 'Cameron', 6543);


INSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);


INSERT INTO (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);
(*)




Correct Correct


Previous Page 3 of 3 Summary