Section 12 Quiz Database Programming With SQL

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 No rows, as the SELECT statement is invalid. 10 rows will be created. No rows, as you cannot use subqueries in an insert statement. 7 rows, as no WHERE-clause restricts the rows returned on the subquery.

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

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. 70 will be inserted into the department_id column. 1700 will be inserted into the manager_id column. 100 will be inserted into the department_id column.

70 will be inserted into the department_id column.

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); 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. 70 will be inserted into the department_id column.

70 will be inserted into the department_id column.

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 great idea. When a new employee record is entered, if no hire_date is specified, the 0 (zero) will be automatically specified. A great idea. When new employee records are entered, they can be added faster by allowing the 0's (zeroes) to be automatically specified. Both a and b are correct. A bad idea. The default value must match the DATE datatype of the column.

A bad idea. The default value must match the DATE datatype of the column.

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 great idea. When a new employee record is entered, if no hire_date is specified, the 0 (zero) will be automatically specified. A great idea. When new employee records are entered, they can be added faster by allowing the 0's (zeroes) to be automatically specified. Both a and b are correct. A bad idea. The default value must match the DATE datatype of the column.

A bad idea. The default value must match the DATE datatype of the column.

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? A subquery A function A SET clause An ON clause

A subquery

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? 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. An error occurs because you CANNOT use a subquery in an INSERT statement.

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

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%'); One row will be deleted, as the subquery only returns one row. All rows in the employees table will be deleted, no matter the department_id. All rows in the employees table of employees who work in the given department will be deleted. No rows will be deleted.

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

Using your knowledge of the employees table, what would be the result of the following statement: DELETE FROM employees; Deletes employee number 100. 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.

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

What would happen if you issued a DELETE statement without a WHERE clause? All the rows in the table would be deleted. Only one row would be deleted. No rows would be deleted. An error message would be returned.

All the rows in the table would be deleted.

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

DELETE FROM employees WHERE employee_id = 348;

You need to remove a row from the EMPLOYEES table. Which statement would you use? UPDATE with a WHERE clause DELETE with a WHERE clause MERGE with a WHERE clause INSERT with a WHERE clause

DELETE with a WHERE clause

To return a table summary on the customers table, which of the following is correct? DEFINE customers, or DEF customers SHOW customers, or SEE customers DISTINCT customers, or DIST customers DESCRIBE customers, or DESC customers

DESCRIBE customers, or DESC customers

To return a table summary on the customers table, which of the following is correct? DESCRIBE customers, or DESC customers DISTINCT customers, or DIST customers SHOW customers, or SEE customers DEFINE customers, or DEF customers

DESCRIBE customers, or DESC customers

DML is an acronym that stands for: Debit Markup Language Data Markup Language Data Manipulation Language Don't Manipulate Language

Data Manipulation Language

The default value must match the __________ of the column. Datatype Table Column name Size

Datatype

To change an existing row in a table, you can use the UPDATE or INSERT statements. True or False?

False

Which statement below will not insert a row of data into a table? INSERT INTO student_table VALUES (143354, 'Roberts', 'Cameron', 6543); INSERT INTO (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 (id, lname, fname, lunch_num) VALUES (143354, 'Roberts', 'Cameron', 6543);

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

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 IN customers (id, first_name, last_name, address, city, state, zip, phone_number); 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");

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

Which of the following represents the correct syntax for an INSERT statement? 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;

INSERT INTO customers VALUES ('3178', 'J.', 'Smith', '123 Main Street', 'Nashville', 'TN', '37777');

The PRODUCTS table contains these columns: PRODUCT_ID NUMBER NOT NULL PRODUCT_NAME VARCHAR2 (25) SUPPLIER_ID NUMBER NOT NULL LIST_PRICE NUMBER (7,2) COST NUMBER (5,2) QTY_IN_STOCK NUMBER(4) LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL Which INSERT statement will execute successfully? INSERT INTO products VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE); INSERT INTO products(product_id, product_name) VALUES (2958, 'Cable'); INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock) VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700) INSERT INTO products(product_id, product_name, supplier_id VALUES (2958, 'Cable', 8690, SYSDATE);

INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock) VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700)

The PRODUCTS table contains these columns: PRODUCT_ID NUMBER NOT NULL PRODUCT_NAME VARCHAR2 (25) SUPPLIER_ID NUMBER NOT NULL LIST_PRICE NUMBER (7,2) COST NUMBER (5,2) QTY_IN_STOCK NUMBER(4) LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL Which INSERT statement will execute successfully? INSERT INTO products(product_id, product_name) VALUES (2958, 'Cable'); INSERT INTO products(product_id, product_name, supplier_id VALUES (2958, 'Cable', 8690, SYSDATE); INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock) VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700) INSERT INTO products VALUES (2958, 'Cable', 8690, 7.09, 4.04, SYSDATE);

INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock) VALUES(2958, 'Cable', 8690, 7.09, 4.04, 700)

The DEFAULT keyword can be used in the following statements: INSERT and UPDATE INSERT and DELETE DELETE and UPDATE All of the above

INSERT and UPDATE

Which statement about the VALUES clause of an INSERT statement is true? To specify a null value in the VALUES clause, use an empty string (" "). Character, date, and numeric data must be enclosed within single quotes in the VALUES clause. 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.

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

Multi-table inserts are used when the same source data should be inserted into _____________ target table. A data warehouse A very large More than one Ten

More than one

Multi-table inserts are used when the same source data should be inserted into _____________ target table. A very large A data warehouse Ten More than one

More than one

Using the INSERT statement and assuming that a column can accept null values, how can you implicitly insert a null value in a column? It is not possible to implicitly insert a null value in a column. Omit the column in the column list. Use the NULL keyword. Use the ON clause

Omit the column in the column list.

Using the INSERT statement and assuming that a column can accept null values, how can you implicitly insert a null value in a column? 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

Omit the column in the column list.

What keyword in an UPDATE statement speficies the column that you want to change? SET SELECT WHERE HAVING

SET

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

To display the names of the managers for all the teams owned by a given owner

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

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

A multi-table insert statement can insert into more than one table.

True

Aliases can be used with MERGE statements. True or False?

True

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

True

Multi-table inserts can be conditional or unconditional. True or False?

True

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

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? UPDATE employees SET cooper = 'last_name' WHERE last_name = 'roper'; UPDATE employees last_name = 'cooper' WHERE last_name = 'roper'; UPDATE employees SET last_name = 'cooper' WHERE last_name = 'roper'; UPDATE employees SET last_name = 'roper' WHERE last_name = 'cooper';

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

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? UPDATE employees last_name = 'cooper' WHERE last_name = 'roper'; UPDATE employees SET last_name = 'roper' WHERE last_name = 'cooper'; UPDATE employees SET cooper = 'last_name' WHERE last_name = 'roper'; UPDATE employees SET last_name = 'cooper' WHERE last_name = 'roper';

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

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? 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); 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) WHERE department_id = 10;

One of your employees was recently married. Her employee ID is still 189, however, her last name is now Rockefeller. Which SQL statement will allow you to reflect this change? INSERT INTO my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189; INSERT my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189; UPDATE INTO my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189; UPDATE my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;

UPDATE my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;

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? WHERE city = 'Atlanta'; UPDATE city = Atlanta; SET city = 'Atlanta'; LIKE 'At%';

WHERE city = 'Atlanta';

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

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