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!
- create a table TEST, give your exercise partner all rights on this table and insert records together.
- try to create a deadlock and see how the system reacts to it.
- person A changes a record. Determine at what point in time this change is available to person B.
- perform the following processing:
|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:
|update any record
|change this record
|change this record
|read this record
7. simulate the Inconsistent Analysis Problem!
|Read record A
|Update record C
|read record B
|Update record A
|read Record C
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!
- 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?
- 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
- insert a new record in the PLAYERS table (use your own data)
- change the value 'F' in the column SEX to 'W
- increase all penalties above the average by 20%.
- the player with the number 95 gets the address of the player with the number 6
- deleting all penalties of player 44 from 1980
- persist changes from 1.-5.
- deleting all penalties of players who have played at least once in a team of the second division
- deleting from 7. undoing
- delete all salaries that are lower than 80% of the average salary of the department, set to 80% of the average salary of the department
- delete all employees who have been with the company for more than 35 years
- create a number sequence with the values 50, 60, 70, 80, ...
- insert a new record in the DEPT table with DEPTNO corresponding to the number sequence from 3., DNAME 'HTL' and LOC 'LEONDING'.