Halaman

Thursday, August 8, 2019

Answer Sections 12 Quiz Database Programming with SQL 2019 Learner - English


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

Final Exam Java Programming 2019 Learner - English

Final Exam