PROGRAMMING: Section 7

INSERT STATEMENTS
Insert statements can be combined with subqueries to create more than one row per statement. True or False?
True
          
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);     

If the employees table have 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 there is no WHERE-clause on the subquery.
          
To return a table summary on the customers table, which of the following is correct?
DESCRIBE customers, or DESC customers
        
DML is an acronym that stands for:
Data Manipulation Language
          
When inserting rows into a table all columns must be given values. True or False?
False
         
When inserting a new row the null keyword can be included in the values list for any null column. True or False? 
True
          
Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause?          
No, you can only create one row at a time when using the VALUES clause.

What is the quickest way to use today's date when you are creating a new row?
Use the SYSDATE function.


UPDATING COLUMN VALUES AND DELETING ROWS
If you are performing an UPDATE statement with a subquery, it MUST be a correlated subquery?
False
          
To change an existing row in a table, you can use the UPDATE or INSERT statements. True or False?
False
          
Assuming there are no Foreign Keys on the EMPLOYEES table, if the following 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%');
All the rows in the EMPLOYEES table with department_ids matching the department_id returned by the subquery.
          
Using your knowledge of the employees table, what would be the result of the following statement:
DELETE FROM employees;
All rows in the employees table will be deleted if there are no constraints on the table.
          
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;
The statement updates the student_table by replacing student id 19's lunch number with student id 17's lunch number.
          
DELETE statements can use correlated subqueries?
True
          
How many rows will be deleted from the employees table with the following statement?
DELETE FROM employees
WHERE last_name = 'king';
No rows will be deleted, as no employees match the WHERE-clause.
          
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%');
All rows in the employees table which work in the given department will be deleted.
          
Is the following statement valid, i.e. is it allowed to update rows in one table, based on a subquery from another table?
UPDATE copy_emp
SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100)
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200);
Yes, this is a perfectly valid statement.
          

DEFAULT VALUES, MERGE, AND MULTI-TABLE INSERTS
The MERGE function combines the:
INSERT and UPDATE commands
          
A multi-table insert statement can insert into more than one table?
True
          
In developing the Employees table, you create a column called hire_date. You assign the hire_date column a DATE datatype with a DEFAULT value of 0 (zero). A user can come back later and enter the correct hire_date. This is __________.
A bad idea. The default value must match the DATE datatype of the column.
          
Which statement below will not insert a row of data onto a table?
INSERT INTO (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);
          
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?
False
          
The MERGE statement can be used to update rows in one table based on values in another table and if the update fails, then the rows will automatically be inserted instead. True or False?
True
          
A multi-table insert statement must have a subquery at the end of the statement?
True
         
The DEFAULT keyword can be used in the following statements:
INSERT and UPDATE