Database Chapter 3

Which of these commands will remove every row in a table, but not delete the table itself? Choose one or more answers. A DELETE command with no WHERE clause A DROP TABLE command A TRUNCATE command UPDATE command, setting every column to NULL with no WHERE clause.

A DELETE command with no WHERE clause A TRUNCATE command *TRUNCATE will be faster; but the DELETE with no WHERE clause will also delete all rows

______ is used to make changes to the columns, constraints, tables, etc. that make up the schema. a. alter b. update

ALTER

Which of the following is not a considered to be DML: INSERT DELETE COMMIT UPDATE MERGE

COMMIT

Which of the following are considered "Transaction control" (select all that apply) UPDATE COMMIT ROLLBACK DELETE SAVEPOINT

COMMIT ROLLBACK SAVEPOINT

A ______ provides a value to be inserted when one is not explicitly given in the INSERT statement. SAVEMARK PLACE HOLDER DEFAULT

DEFAULT

An SQL TRUNCATE statement can be undone. T/F

F

The ALTER command is used to make changes to the data in a table. T/F

F

What is the result of: UPDATE cruises SET cruise_name = 'Bahamas', SET start_date = SYSDATE WHERE cruise_id = 1; An error will occur because two columns cannot be updated with one UPDATE For the first record in the CRUISES table whose cruise_id is 1, cruise_name will be set to 'Bahamas' and start_date will be set to the current date For the all records in the CRUISES table whose cruise_id is 1, cruise_name will be set to 'Bahamas' and start_date will be set to the current date

For the all records in the CRUISES table whose cruise_id is 1, cruise_name will be set to 'Bahamas' and start_date will be set to the current date

If a table T3 has four numeric columns (A, B, C, D) and no primary key, which of these statements will succeed? Choose all that apply. INSERT INTO T3 VALUES (3, 6, 7, NULL); INSERT INTO T3 VALUES ('3', '9', '10', '12'); INSERT INTO T3 SELECT * FROM T3; NONE OF THESE INSERT STATEMENTS WILL SUCCEED.

INSERT INTO T3 VALUES (3, 6, 7, NULL); INSERT INTO T3 VALUES ('3', '9', '10', '12'); INSERT INTO T3 SELECT * FROM T3; *The three INSERT statements are legal, although the one with the character values, such as '6', will require the database to perform some implicit type conversion first (from character to numeric data).

CREATE TABLE STUDENT_LIST (STUDENT_ID NUMBER, STUDENT_NAME VARCHAR2(30), STUDENT_PHONE VARCHAR2(20)); INSERT INTO STUDENT_LIST VALUES (1, 'Joe Wookie', 3185551212); The table will create succesfully. What will result from the INSERT statement execution? It will fail because there is no column listing It will fail because the value for STUDENT_PHONE is numeric, and STUDENT_PHONE is a character datatype. It will execute and the table will contain one row of data. It will fail because no primary key has been specified for the table.

It will execute and the table will contain one row of data.

A user named SALLY updates some rows, and asks another user MELVIN to login and check the changes before she commits them. Which of the following statements is true about this situation? (Choose the best answer.) MELVIN can see SALLY's changes but cannot alter them until SALLY commits. MELVIN cannot see SALLY's updates because she has not entered the COMMIT command. SALLY must commit the updates, so that MELVIN can view them, and if necessary roll them back. SALLY must commit the updates, so that MELVIN can view them, but only SALLY can roll them back.

MELVIN cannot see SALLY's updates because she has not entered the COMMIT command.

SALLY updates some rows but does not commit. MELVIN queries the rows that SALLY updated. Which of the following statements is true? (Choose the best answer.) MELVIN will not be able to see the rows that SALLY updated because they will be locked. MELVIN will be able to see the updated values. MELVIN will see the old versions of the rows. MELVIN will see the old data as it was before SALLY issued a SAVEPOINT command.

MELVIN will see the old versions of the rows. *A is wrong because locking is not relevant in this case (rows that are being updated do not lock out people who are doing queries.) B is wrong because SALLY has not commited. D is wrong because SAVEPOINTS are only markers in a transaction, they do not affect updates.

______ returns the next available number in the sequence. GET_NUM AVAIL_VAL NEXTVAL NEXT_VAL

NEXTVAL

CREATE TABLE SHIPS ( Ship_ID NUMBER, Ship_name VARCHAR2(20), Home_port_id NUMBER(4)); What will be the result of the following DML statement: INSERT INTO SHIPS(Ship_name, Ship_ID) VALUES ('Codd Vessel II', 4001); An error because Home_port_id was not given a value One row will be inserted with ship_ID having a value of 4001, ship_name having value 'Codd Vessel II', and Home_port_id will be NULL. An error because the datatypes of the columns and values are misaligned

One row will be inserted with ship_ID having a value of 4001, ship_name having value 'Codd Vessel II', and Home_port_id will be NULL.

What value would successfully complete the statement (one answer): INSERT INTO employees (emp_id, last_name, hire_date) VALUES (256, 'Monroe' ... '11/2/2015' SYSDATE 'November 2, 2015' 1000

SYSDATE

If all of the records in a table need to be removed, a TRUNCATE is faster than a DELETE, especially if the table is large and contains many rows. T/F

T

TRUNCATE is actually a DDL statement, and therefore has an implicit COMMIT. This means it cannot be undone (ROLLBACK). T/F

T

The following statement will remove all rows from the vendors table, but leave the table and index structure intact: TRUNCATE TABLE vendors; T/F

T

The _______ command is used to make changes to the data. a. alter b. update

UPDATE

To delete the data values from one entire column in a table (but not remove the column from the table), you would use the ______ command. DELETE COLUMN DROP COLUMN UPDATE without a WHERE clause ALTER TABLE

UPDATE without a WHERE clause *The UPDATE command without a WHERE clause is the only way to remove values in an entire column. For example: UPDATE shipsSET ship_capacity = NULL;

Consider the ADDRESS table that has the following 3 columns and no rows: ID NUMBER NOT NULL, ZONE NUMBER, ZIP_CODE VARCHAR2(5) COMMIT; INSERT INTO ADDRESS VALUES (1, 1, '94506'); SAVEPOINT ZONE_ADDRESS_1; UPDATE ADDRESS SET ZONE = 2 WHERE ZIP_CODE ='94506'; ROLLBACK; a)ADDRESS will have 1 row with a value of 1 for ZONE. b)ADDRESS will have 1 row with a value of 2 for ZONE. c)the ADDRESS table will have no rows. d)The statements will generate a syntax error.

c)the ADDRESS table will have no rows. *The ROLLBACK statement does not reference the SAVEPOINT name, so it rolls all the way back to the latest COMMIT statement. In this case, the UPDATE and INSERT statements are rolled back, which leaves the ADDRESS table with no rows.

Which of the following are true concerning inserting multiple rows at a time? Select 2 correct answers. the source table must already exist the destination table must be empty This version of the INSERT statement has an implicit COMMIT the columns and datatypes of the source and destination tables must correspond

the source table must already exist the columns and datatypes of the source and destination tables must correspond