SQL 3

SQL Exercise 09

Under ORACLE, SET AUTOCOMMIT ON performs a COMMIT after each SQL statement. This setting is to be switched off for the following exercises with SET AUTOCOMMIT OFF. Note that a transaction end must then be explicitly executed by the COMMIT command. Perform the following exercises in teams of 2!

1. create a table TEST, give your exercise partner all rights on this table and insert records together.
2. try to create a deadlock and see how the system reacts to it.
3. person A changes a record. Determine at what point in time this change is available to person B.
4. perform the following processing:

User A	User B
read record A	performs the same action In addition, one field of this record is changed
	end transaction B
change record A
end transaction A

a) Is this processing sequence feasible? b) Modify the example so that User B changes another record. Consider- what locking mechanisms does Oracle use?

5. Determine how the system reacts when both transactions insert the same record (with and without PRIMARY KEY).

6. reproduce the following situation:

User A	User B
update any record
	change this record
ROLLBACK
	COMMIT
change this record
	read this record

7. simulate the Inconsistent Analysis Problem!

User A	User B
Read record A
	Update record C
read record B
	Update record A
read Record C
	COMMIT

Modify the example in such a way that for user A the condition of the data sets A, B and C of a certain time is indicated!

8.

• User A selects a dataset according to a certain criterion.

• User B changes the criterion according to which the selection was made.

• User A changes a value in the previously selected data set.

What happens? What could be done so that between selection and change exactly this selected record cannot be changed by another user?

9.

• User B creates a table with a foreign key that references User A's table.
• User A should now revoke the right that user B can set a reference to his table. Which values can user B insert in the foreign key column afterwards?

SQL Exercise 10

Solve the following tasks alone (each one of you)

to the tennis club tables

1. insert a new record in the PLAYERS table (use your own data)
2. change the value 'F' in the column SEX to 'W
3. increase all penalties above the average by 20%.
4. the player with the number 95 gets the address of the player with the number 6
5. deleting all penalties of player 44 from 1980
6. commit changes from 1.-5.
7. deleting all penalties of players who have played at least once in a team of the second division
8. deleting from 7. undoing

to EMP-DEPT

1. delete all salaries that are lower than 80% of the average salary of the division.
2. delete all employees who have been with the company for more than 35 years
3. create a number sequence with the values 50, 60, 70, 80, ...
4. insert a new record in the DEPT table with DEPTNO corresponding to the number sequence from 3., DNAME 'HTL' and LOC 'LEONDING'.