Database
Programming with SQL 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.
No comments:
Post a Comment