Section 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?
An ON
clause
A
function
A
subquery (*)
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?
INSERT
INTO customers
VALUES (200, 'InterCargo', null, 'tflanders', 'samerica');
(*)
INSERT
INTO customers
VALUES (200, InterCargo, 0, tflanders, samerica);
INSERT
INTO customers
VALUES (cust_id, company, credit, poc, location) (200,
'InterCargo', 0, 'tflanders', 'samerica');
INSERT
INTO customers (cust_id, company, poc, location)
VALUES (200, 'InterCargo', 'tflanders', 'samerica');
(*)
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. (*)
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?
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.
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
10 rows
will be created.
7 rows,
as no WHERE-clause restricts the rows returned on the subquery. (*)
No
rows, as you cannot use subqueries in an insert statement.
No
rows, as the SELECT statement is invalid.
A
DEFAULT value can be specified for a column when the table is created. True or
false?
True
(*)
False
In a conditional multi-table
insert, you can specify either __________ or __________.
All;
Second
All;
First (*)
First;
Second
Null;
Default
The MERGE function combines
the:
CREATE
and UPDATE commands
INSERT
and UPDATE commands (*)
ALTER
and UPDATE commands
All of
the above
Aliases can be used with MERGE
statements. True or False?
True
(*)
False
Which statement below will not
insert a row of data into a table?
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);
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;
You need to remove a row from the
EMPLOYEES table. Which statement would you use?
MERGE
with a WHERE clause
INSERT
with a WHERE clause
DELETE
with a WHERE clause (*)
UPDATE
with a WHERE clause
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
rows in the EMPLOYEES table will be deleted, regardless of their department_id.
One row
will be deleted, as the subquery only returns one row.
No rows
will be deleted.
All the
rows in the EMPLOYEES table with department_ids matching the department_id
returned by the subquery. (*)
You want to enter a new record
into the CUSTOMERS table. Which two commands can be used to create new rows?
INSERT,
CREATE
MERGE,
CREATE
INSERT,
MERGE (*)
INSERT,
UPDATE
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. (*)
The
first row in the employees table will be deleted.
Deletes
employee number 100.
Nothing,
no data will be changed.
No comments:
Post a Comment