Chapter 7 - Modifying Data in Databases with SQL

Release Explanation Oracle has a command to preserve the space; otherwise the default is to release this space back to the database.

By default, TRUNCATE will _____ the space left behind by the deleted rows. 1. Release 2. Keep 3. Double 4. Compress

05/11/2017 Explanation The Formatting will put the value in day-month-year order. Remember that this does not impact the data in the table, only the format of the data.

Examine the following code. If the value for the column startDate is November 5, 2017, what will the result be after formatting? 1. 11/05/2017 2. 05/11/2017 3. 2017/11/05 4. 20170511

35.1230 Explanation The 4 tells the system to show the first four numbers after the decimal point.

Examine the following code. What will the pay rate look like after the FORMAT statement? (Assume the rate in the database is stored as 35.1230335). 1. 36 2. 35.1230335 3. 35.123 4. 35.1230

555-1212 Explanation The pound signs represent the numbers, and the dash separates the values; by having it in quotes, it tells the database to format it like text.

Examine the following code. Which value best represents the formatting of the phone number column? 1. 5551212 2. 555-1212 3. 800-555-1212 4. 8005551212

Rows; columns Explanation PIVOT is used to pivot row data into columnar data.

PIVOT is used to convert _____ into _____ 1. Columns; columns 2. Rows; columns 3. Rows; rows 4. Columns; rows

Bloat Explanation If TRUNCATE is used on a very large table, it can benefit the performance of the database and reduce the bloat of unnecessary/unused space.

Releasing the space of the deleted the rows prevents what? 1. Data integrity issues 2. Lost data 3. Duplicate records 4. Bloat

Deletes Explanation TRUNCATE deletes all rows from the table and frees up the space that those rows had consumed in the database.

TRUNCATE _____ all rows from the table. 1. Merges 2. Deletes 3. Copies 4. Moves

Columns; rows Explanation UNPIVOT transforms data from columns into rows of data.

The UNPIVOT command transforms _____ into _____ 1. Columns; columns 2. Rows; rows 3. Columns; rows 4. Rows; columns

Change or manipulate data in a table Explanation The UPDATE clause is used to change content or fields within records. it should be used with caution since it manipulates data in a table.

The UPDATE clause allows us to: 1. Delete data 2. Trigger data 3. Insert data 4. Change or manipulate data in a table

Update several records at a time Explanation You can update several records at the same time depending on which conditions you specify.

The UPDATE clause can: 1. Modify conditions 2. Select information from the table you want to update 3. Update several records at a time 4. Update only one record at a time

SSN FullName 578389982 Jacqueline Jackson 683809234 Francis Forster 730990231 David Drew Explanation The SQL statement is SELECT * FROM Hiking INTERSECT SELECT * FROM Traveling The INTERSECT set operator will select records which exist in both tables and by inspection there are actually three records which appear in both tables. These records are those belonging to 'JacquelineJackson', 'Francis Forster' and 'David Drew'. As the statement contains 'SELECT *' all fields will be returned - in this case two fields i.e. the SSN and FullName fields will appear in the output. Only one answer meets this requirement.

The diagram below is to be used for this question. The two tables below show the personal details of some people and their hobbies. The first table is named Hiking and contains details of all people who has hiking as a hobby and the second for all with traveling as a hobby. The SQL statement below is executed. Which of the following output shown is the correct output produced by this statement ? SELECT * FROM Hiking INTERSECT SELECT * FROM Traveling a. b. c. d.

SELECT Reading.FullName, Reading.SSN FROM Reading INTERSECT SELECT Hiking.FullName, Hiking.SSN FROM Hiking ORDER BY Reading.FullName Explanation The record set actually shows the common record (considering only the FullName and SSN fields) which appear in both the Reading and Hiking tables.This is actually an intersection set operation, thus the SQL statement must contain the INTERSECT operator. SELECT Reading.FullName, Reading.SSN FROM Reading INTERSECT SELECT Hiking.FullName, Hiking.SSN FROM Hiking ORDER BY Reading.FullName is the correct answer. Though SELECT Reading.* FROM Reading INTERSECT SELECT Hiking.* FROM Hiking ORDER BY Reading.FullName looks almost similar, take note that the number of fields in the Reading table is different from that for the Hiking table. The Reading table has three fields while the Hiking only two. Thus, SELECT Reading.* and SELECT Hiking.* causes a conflict and the statement will result in a syntax error when executed.

The diagram provided below is to be used with this question. A given set operator which acts on both the Hiking and Reading table would produce the record set shown in the last table. Which is the correct SQL statement which can accomplish this ? 1. SELECT Reading.* FROM Reading INTERSECT SELECT Hiking.* FROM Hiking ORDER BY Reading.FullName 2. SELECT Reading.FullName, Reading.SSN FROM Reading UNION SELECT Hiking.FullName, Hiking.SSN FROM Hiking ORDER BY Reading.FullName 3. SELECT Reading.FullName, Reading.SSN FROM Reading INTERSECT SELECT Hiking.FullName, Hiking.SSN FROM Hiking ORDER BY Reading.FullName 4. SELECT Reading.FullName, Reading.SSN FROM Reading EXCEPT SELECT Hiking.FullName, Hiking.SSN FROM Hiking ORDER BY Reading.FullName

5 Explanation The correct answer is 5. The EXCEPT set operator is the difference set operator in MS SQL. Here, the statement which uses the EXCEPT operator has the Reading table in the first SELECT sub-statement and the Hiking table in the second SELECT sub-statement. Thus, this statement will list all records which exist in the Reading table but not in the Hiking table ( based only on the FullName and SSN fields ). By inspection, you would notice that the record having the value of 'Francis Forster' is the only record which appears in both tables, thus it does not appear in the result set and the result set has exactly five rows.

The figure below is to be used with this question. The SQL statement below is executed. How many rows of records will be returned ? SELECT Reading.FullName, Reading.SSN FROM Reading EXCEPT SELECT Hiking.FullName, Hiking.SSN FROM Hiking ORDER BY Reading.FullName 1. 1 2. 6 3. 5 4. 0

Mask Explanation It's a mask because it displays something other than what is beneath: A numeric value of 3.33343288483 could be displayed as 3.3 by using a mask of ###,###,###.#

The formatting command, e.g, ###,###,###.##, can also be called a _____. 1. Veneer 2. Cover 3. Cloak 4. Mask

Consolidate a table's information Explanation The purpose of the SQL PIVOT command is to consolidate a table's information. It doesn't join, and it doesn't remove, information.

The purpose of the SQL PIVOT command is to? 1. Consolidate a table's information 2. Join a tables information with another 3. Remove information from a table 4. None of the answers are correct.

Which new value to specify Explanation The SET clause in the UPDATE clause takes the requested column and changes in to the new value that you specify.

The word SET in the UPDATE clause specifies: 1. Which second table should be selected 2. The table requiring updating 3. Which new value to specify 4. The record to select

SELECT * FROM Table1 UNION SELECT * FROM Table2 UNION SELECT * FROM Table3 ORDER BY ID Explanation The correct statement to use is SELECT * FROM Table1 UNION SELECT * FROM Table2 UNION SELECT * FROM Table3 ORDER BY ID Here, the rows from all tables are to be combined, thus the UNION operator is the only SET operator which can do this. As repetitions are not required, the UNION ALL operator is not required. Using the UNION operator by itself would achieve the desired results.

There are three identical tables (with non-identical data) named Table1,Table2 and Table3 and each table has two fields, namely ID nchar(5) and Value nchar(50). The records in all tables are to be combined and listed without repetitions and the ordering must be by the ID field in ascending order. Which SQL statement containing a SET operator can accomplish this ? 1. SELECT * FROM Table1 MINUS SELECT * FROM Table2 MINUS SELECT * FROM Table3 ORDER BY ID 2. SELECT * FROM Table1 EXCEPT SELECT * FROM Table2 EXCEPT SELECT * FROM Table3 ORDER BY ID 3. SELECT * FROM Table1 INTERSECT SELECT * FROM Table2 INTERSECT SELECT * FROM Table3 ORDER BY Table1.ID 4. SELECT * FROM Table1 UNION SELECT * FROM Table2 UNION SELECT * FROM Table3 ORDER BY ID

The name of the table that requires updating Explanation Proceeding with the update, we have the table that is to be changed. UPDATE table SET column1 = expression1, column2 = expression2, {WHERE conditions};

What directly follows the word UPDATE in the UPDATE clause? 1. The record that you are working with 2. The name of the table that requires updating 3. The column to update 4. The condition that must be met for the update to occur

The numbers start over Explanation The nice thing about TRUNCATE is that auto-numbers restart, which sets everything back to the beginning.

What happens to sequentially-numbered (auto-numbered) fields after TRUNCATE? 1. They are removed and you need to re-add the column 2. They increment from the last number entered 3. The numbers start over 4. They restart but at 100

The view of the data is changed, not the data itself Explanation The FORMAT only presents the data in a different format, without changing the underlying data.

What happens to the data in the table after a FORMAT statement is run? 1. The view of the data is changed, not the data itself 2. The data is copied into a temp table 3. The data is converted to the new format 4. The data is deleted

Some Explanation When UNPIVOT is executed, it restores some of the information affected by PIVOT. It cannot restore any information that was aggregated (summed or similar).

When UNPIVOT is executed, it restores _____ of the information affected by PIVOT. PIVOT is NOT related to 1. UNPIVOT 2. None 3. All 4. Some

REMOVE Explanation The four main SQL operations are SELECT, UPDATE, INSERT and DELETE, not REMOVE.

Which of the following is NOT one of the four basic SQL operations? 1. SELECT 2. UPDATE 3. INSERT 4. REMOVE

Specifying the table you want to update Explanation The table that you update will be in a database but you must first know which table that you want to select. In each table, you might update several records depending on which conditions you list.

Which of the following is mandatory in the use of an UPDATE clause? 1. Selecting the database 2. Specifying a SELECT statement 3. Selecting two where clauses 4. Specifying the table you want to update

TRUNCATE TABLE shopping_cart Explanation Unlike DELETE, the WHERE clause cannot be used with TRUNCATE, as it removes all rows and frees up space.

Which of the following is the correct syntax to remove the table shopping_cart? 1. TRUNCATE shopping_cart WHERE 0 = 0 2. TRUNCATE TABLE shopping_cart 3. TRUNCATE DELETE shopping_cart 4. TRUNC shopping_cart

COMBINE Explanation There is no set operator known as COMBINE. The other suggested answers are all valid set operators.

Which of the following operator is not a set operator supported by SQL systems ? 1. COMBINE 2. INTERSECT 3. UNION 4. EXCEPT or MINUS (i.e. difference)