Module 8

10. What is one of the purpose of flashback queries?

A feature that allows you to look at values of a query of a specific time in the past. Helpful if a user performs an unintended but committed DML change.

9. When would an UPDATE statement affect data in more than one table?

By using a subquery.

Delete all rows from the GRADE_CONVERSION table. Then select all the date from the table, issue a ROLLBACK command, and explain the outcome.

DELETE FROM grade_conversion SELECT* FROM grade_conversion ROLLBACK

Delete the row inserted in exercise a in Lab 11.1 from the GRADE_TYPE table.

DELETE FROM grade_type WHERE grade_type_code = 'EC'

2. A statement-level rollback ends a transaction. T/F

False

3. The SELECT command always places locks on retrieved rows. T/F

False, Queries never place locks on rows

4. Oracle achieves read consistency by reading uncommitted data. T/F

False. A read of uncommitted data is called a "dirty read."

1. A DML command automatically issues a commit. T/F

False; Only DDL or DCL commands issue implicit commits.

5. Uncommitted changes can be seen by all users. T/F

False; Only committed changes can be seen by all users. The session issuing the change can always see the change.

3. An INSERT statement can insert only one row at a time into a table. T/F

False; You can insert multiple rows by selecting from another table.

Explain what is wrong with the following INSERT statement. Hint: It is not the value course_no_seq.NEXTVAL, which inserts from a sequence, thus generating a unique number.

INSERT INTO course (course_no, description, cost, created_date, modified_date, created_by, modified_by) VALUES (course_no_seq.NEXTVAL, 'Intro to Linux', 1295, SYSDATE, SYSDATE, 'AliceRischert', 'AliceRischert')

Write and execute an INSERT statement to insert a row into the GRADE_TYPE table for a grade type of 'Extra Credit'', identified by the code 'EC'. Issue a COMMIT command afterward.

INSERT INTO grade_type (grade_type_code, description, created_by, created_date, modified_by, modified_date) VALUES ('EC', 'Extra Credit', USER, SYSDATE, USER, SYSDATE)

4. What is a statement-level rollback? Does it end a transaction?

If one individual statement fails in a series of DML statements, only this statement is rolled back and Oracle issues an implicit SAVEPOINT. The entire transaction ends when a ROLLBACK or COMMIT occurs.

3. What is the difference between INSERT ALL and INSERT FIRST statements?

Insert all is unconditional. Only has the WHERE condition that determines the rows to be selected. Insert first evaluates the WHEN clauses in order. If the first condition is true, the row is inserted and the subsequent rows are not tested.

5. Which command you use to combine INSERT, UPDATE, and DELETE operations?

MERGE

1. Give two cases that an INSERT statement would fail.

Primary Key Violation - you're trying to insert a record into a table which already has a row with the same primary key. Foreign Key Violation - your trying to insert a row into a "child" table, yet the "parent" doesn't exist.

What is the effect of the following statement? DELETE FROM enrollment WHERE student_id NOT IN (SELECT student_id FROM student s, zipcode z WHERE s.zip=z.zip AND z.city = 'Brooklyn' AND z.state='NY')

The statement deletes enrollment for all students except who live in Brooklyn, NY.

What does the following query accomplish? UPDATE enrollment e SET final_grade = (SELECT AVG(numeric_grade) FROM grade g WHERE e.student_id=g.student_id AND e.section_id=g.section_id), modified_date=SYSDATE. modified_by = 'Your name here' WHERE student_id IN (SELECT student_id FROM student WHERE last_name like 'S%')

This query updates the FINAL_GRADE, MODIFIED_BY columns of the ENROLLMENT table for students with last names starting with the letter S. The computed average grade is based on the individual grades received by the student for the respective section.

1. The Data tab allows you to perform all the major DML commands, using SQL Developer's user interface. T/F

True

2. There is no syntax error in the following UPDATE statement. UPDATE grade_type SET description='Exams' WHERE grade_type_code IN ('FI', 'MT')

True

2. To extract data containing multiple Oracle user accounts, you can use the SQL Developers Tools, Database Export menu. T/F

True

3. The Auto-fit All Columns on Header menu option in the data grid adjusts the width of the column headers. T/F

True

4. A COMMIT or ROLLBACK command ends a transaction. T/F

True

4. The filter box in the Data tab requires the entry of the WHERE keyword. T/F

True

5. SQL Developers Export Data option allows you to selectively export certain rows and columns. T/F

True

6. A transaction is a logical unit of work. T/F

True

7. A COMMIT or ROLLBACK command would release locks on rows, true or false?

True

8. A query statement doesn't place locks on rows, true or false?

True

1. It is possible to restore rows that have been deleted using the DELETE command. T/F

True. If the rows have not been committed to the database, you can restore them by using ROLLBACK command.

5. Oracle releases the lock of a row after the session issues a COMMIT or ROLLBACK command. T/F

True. If the same session issues a DCL or DDL command instead of a ROLLBACK or COMMIT, it will force an implicit commit and therefore release the lock on the row.

Update the first name from Rick to Nick for the instructor with the ID 104.

UPDATE instructor SET first_name='Nick' WHERE instructor_id = 109 AND first_name = 'Rick

Write and execute an UPDATE statement to update the phone numbers of instructors from 2125551212 to 212-555-1212 and the MODIFIED_BY and MODIFIED_DATE columns with the user logged in and today's date, respectively. Write a SELECT statement to prove the update worked correctly. Do not issue a COMMIT command.

UPDATE instructor SET phone = '212-555-1212', modified_by = USER, modified_date=SYSDATE WHERE phone='2125551212' SELECT instructor_id, phone, modified_by, modified_date FROM instructor

A) Using an UPDATE statement, change the location to B111 for all sections where the location is currently L210

UPDATE section SET location = 'B111' WHERE location = 'L210'

B)Update the MODIFIED_BY column is with the user login name and update the MODIFIED_DATE column with a date of March, 31, 2009, using the TO_DATE function for all the rows updated in exercise a.

UPDATE section SET modified_by= USER, modified_date=TO_DATE('31-MAR-2009', 'DD-MON-YYYY') WHERE location ='B111'

6. When would a DELETE statement delete rows from more than one table?

With DELETE CASCADE. If the foreign key constraint specifies the ON DELETE CASCADE option, the deletion of a parent row automatically deletes the associated child rows.

2. Are column names required when you insert data into a table?

Yes. Book says it is good practice to include a column list in case of future changes.

C) Update instructor Irene Willig's zip code to 90210. What do you observe?

changing the zip code to a value that does not exist in the ZIPCODE table results in a referential integrity constraint error.

What is the result of the following statement? MERGE INTO enrollment e USING(SELECT AVG(numeric_grade) final_grade, section_id, student_id FROM grade GROUP BY section_id, student_id) g ON (g.section_id=e.section_id AND g.student_id=e.student_id) WHEN MATCHED THEN UPDATE SET e.final_grade=g.final_grade WHEN NOT MATCHED THEN INSERT (e.student_id, e.section_id, e.enroll_date, e.final_grade, e.created_by, e.created_date, e.modified_date, e.modified_by) VALUES (g.section_id, g.student_id, SYSDATE, g.final_grade, 'MERGE', SYSDATE, SYSDATE, 'MERGE')

the merge statement updates the column FINAL_GRADE to the average grade per student and section, based on the GRADE table. If the section and the student are not found in the ENROLLMENT table, the MERGE command inserts the row.