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
2. 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 __________.
Mark for Review
(1)
Points
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. (*)
3. Aliases can be used with MERGE
statements. True or False? Mark for
Review
(1)
Points
True (*)
False
4. Multi-table inserts can be conditional
or unconditional. True or False?
Mark for Review
(1)
Points
True (*)
False
5. A multi-table insert statement can
insert into more than one table. (True or False?) Mark for Review
(1)
Points
True (*)
False
6. 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;
7. 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;
8. 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);
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);
9. 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
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.
10. What
keyword in an UPDATE statement speficies the column that you want to change? Mark for Review
(1)
Points
SET (*)
SELECT
WHERE
HAVING
11. To
return a table summary on the customers table, which of the following is
correct? Mark for Review
(1)
Points
DEFINE customers, or DEF customers
SHOW customers, or SEE customers
DISTINCT customers, or DIST
customers
DESCRIBE customers, or DESC
customers (*)
12. 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?
Mark for Review
(1)
Points
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);
13. 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. (*)
1700 will be inserted into the
manager_id column.
100 will be inserted into the
department_id column.
14. 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 subquery (*)
A function
A SET clause
An ON clause
15. 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 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.
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
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");
2. Which statement about the VALUES clause
of an INSERT statement is true? Mark for
Review
(1)
Points
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. (*)
3. 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.
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. (*)
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.
Omit the column in the column list.
(*)
Use the NULL keyword.
Use the ON clause
5. 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
6. Which statement below will not insert a
row of data into a table? Mark for
Review
(1)
Points
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);
7. Aliases can be used with MERGE
statements. True or False? Mark for
Review
(1)
Points
True (*)
False
8. Multi-table inserts are used when the
same source data should be inserted into _____________ target table. Mark for Review
(1)
Points
A very large
A data warehouse
Ten
More than one (*)
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
10. The
default value must match the __________ of the column. Mark for Review
(1)
Points
Datatype (*)
Table
Column name
Size
11. 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
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';
12. 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 (*)
13. 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? Mark for Review
(1)
Points
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; (*)
14. 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. (*)
15. You
need to remove a row from the EMPLOYEES table. Which statement would you use? Mark for Review
(1)
Points
UPDATE with a WHERE clause
DELETE with a WHERE clause (*)
MERGE with a WHERE clause
INSERT with a WHERE clause
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
2. Multi-table inserts are used when the
same source data should be inserted into _____________ target table. Mark for Review
(1)
Points
A data warehouse
A very large
More than one (*)
Ten
3. 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 __________.
Mark for Review
(1)
Points
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. (*)
4. Aliases can be used with MERGE
statements. True or False? Mark for
Review
(1)
Points
True (*)
False
5. 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? Mark for Review
(1)
Points
True (*)
False
6. 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
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. (*)
7. To return a table summary on the
customers table, which of the following is correct? Mark for Review
(1)
Points
DESCRIBE customers, or DESC
customers (*)
DISTINCT customers, or DIST
customers
SHOW customers, or SEE customers
DEFINE customers, or DEF customers
8. 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?
Mark for Review
(1)
Points
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);
9. 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
10. DML
is an acronym that stands for: Mark
for Review
(1)
Points
Debit Markup Language
Data Markup Language
Data Manipulation Language (*)
Don't Manipulate Language
11. Using
your knowledge of the employees table, what would be the result of the
following statement:
DELETE
FROM employees; Mark for Review
(1)
Points
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.
12. 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
13. 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'; (*)
UPDATE city = Atlanta;
SET city = 'Atlanta';
LIKE 'At%';
14. 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
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'; (*)
15. What
would happen if you issued a DELETE statement without a WHERE clause? Mark for Review
(1)
Points
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.
Which statement about the VALUES clause of an INSERT statement is true? Mark for Review
ReplyDelete(1) Points
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.
To specify a null value in the VALUES clause, use an empty string (" ").
If no column list is specified, the values must be listed in the same order that the columns are listed in the table.(*)
Delete1- Which statement below will not insert a row of data into a table?
ReplyDeleteINSERT INTO student_table (id, lname, fname, lunch_num)
VALUES (143352, 'Roberts', 'Cameron', DEFAULT);
INSERT INTO (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 (143354, 'Roberts', 'Cameron', 6543);
2- When inserting rows into a table, all columns must be given values. True or False?
True
False (*)
3- What is the quickest way to use today's date when you are creating a new row?
Simply use the keyword DATE in the insert statement.
Simply write today's date in the format 'dd-mon-rrrr'.
Use the SYSDATE function. (*)
Use the TODAYS_DATE function.
Examine the structures of the PRODUCTS and SUPPLIERS tables:
ReplyDeleteSUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, Primary Key
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)
PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, Primary Key
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER Foreign key to SUPPLIER_ID of the SUPPLIERS table
CATEGORY_ID NUMBER
QTY_PER_UNIT NUMBER
UNIT_PRICE NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
You want to delete any products supplied by the five suppliers located in Atlanta. Which script should you use?
DELETE FROM suppliers
WHERE supplier_id IN
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ALANTA');
DELETE FROM products
WHERE supplier_id IN
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ATLANTA');
(*)
DELETE FROM products
WHERE supplier_id =
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ATLANTA');
DELETE FROM products
WHERE UPPER(city) = 'ATLANTA';
5. Is the following statement valid, i.e. is it allowed to update rows in one table, based on a subquery from another table?
ReplyDeleteUPDATE 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);
Mark for Review
(1) Points
No, this statement will return an error.
The statement will fail because the subqueries are returning data from different rows.
No, this does nothing.
Yes, this is a perfectly valid statement. (*)
6. 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;
7. In a conditional multi-table insert, you can specify either __________ or __________.
Mark for Review
(1) Points
All; Second
All; First (*)
Null; Default
First; Second
The EMPLOYEES table contains the following columns:
ReplyDeleteEMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)
You need to increase the salary for all employees in department 10 by 10 percent. You also need to increase the bonus for all employees in department 10 by 15 percent. Which statement should you use?
(1/1) Points
UPDATE employees
SET salary = salary * .10, bonus = bonus * .15
WHERE department_id = 10;
UPDATE employees
SET salary = salary * 1.10 AND bonus = bonus * 1.15
WHERE department_id = 10;
UPDATE employees
SET salary = salary * 1.10, bonus = bonus * 1.15
WHERE department_id = 10; (*)
UPDATE employees
SET (salary = salary * 1.10) SET (bonus = bonus * 1.15)
WHERE department_id = 10;