From Coders.Bay Wiki
Jump to navigation Jump to search

SQL Exercise 09[edit]

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
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

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[edit]

Solve the following tasks alone (each one of you)

to the tennis club tables[edit]

  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. persist 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[edit]

  1. 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
  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'.