Repository navigation
Expand file tree
/
Copy pathTCL_Assignment_Answers.py
More file actions
137 lines (97 loc) · 2.64 KB
/
Copy pathTCL_Assignment_Answers.py
File metadata and controls
137 lines (97 loc) · 2.64 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
1. Start a transaction and update marks of student with id = 2 to 80, then commit the changes.
START TRANSACTION;
UPDATE students
SET marks = 80
WHERE id = 2;
COMMIT;
2. Start a transaction, delete the student whose id = 3, and rollback the transaction.
START TRANSACTION;
DELETE FROM students
WHERE id = 3;
ROLLBACK;
________________________________________
3. Insert a new student (4, 'Kiran', 88) and commit the transaction.
START TRANSACTION;
INSERT INTO students (id, name, marks)
VALUES (4, 'Kiran', 88);
COMMIT;
________________________________________
4. Start a transaction, update marks of id = 1 to 95, create a savepoint, then update marks of id = 2 to 60. Rollback only the second update.
START TRANSACTION;
UPDATE students
SET marks = 95
WHERE id = 1;
SAVEPOINT sp1;
UPDATE students
SET marks = 60
WHERE id = 2;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;
________________________________________
5. Write a query to demonstrate the use of SAVEPOINT and ROLLBACK TO SAVEPOINT.
START TRANSACTION;
UPDATE students
SET marks = 90
WHERE id = 1;
SAVEPOINT sp1;
UPDATE students
SET marks = 70
WHERE id = 2;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;
________________________________________
6. Start a transaction, insert two records, then rollback the entire transaction.
START TRANSACTION;
INSERT INTO students (id, name, marks)
VALUES (5, 'Rahul', 85);
INSERT INTO students (id, name, marks)
VALUES (6, 'Anu', 90);
ROLLBACK;
________________________________________
7. Start a transaction, update multiple rows, and commit only if all updates are successful.
START TRANSACTION;
UPDATE students
SET marks = 85
WHERE id = 1;
UPDATE students
SET marks = 90
WHERE id = 2;
UPDATE students
SET marks = 95
WHERE id = 3;
COMMIT;
________________________________________
8. What will happen if you execute a ROLLBACK after COMMIT? Write a query to demonstrate this.
START TRANSACTION;
UPDATE students
SET marks = 100
WHERE id = 1;
COMMIT;
ROLLBACK;
________________________________________
9. Write a TCL query to undo only part of a transaction using SAVEPOINT.
START TRANSACTION;
UPDATE students
SET marks = 75
WHERE id = 1;
SAVEPOINT sp1;
UPDATE students
SET marks = 65
WHERE id = 2;
ROLLBACK TO SAVEPOINT sp1;
COMMIT;
________________________________________
10. Write SQL queries showing the difference between COMMIT and ROLLBACK.
COMMIT
START TRANSACTION;
UPDATE students
SET marks = 90
WHERE id = 1;
COMMIT;
COMMIT: Permanently saves the changes.
ROLLBACK
START TRANSACTION;
UPDATE students
SET marks = 50
WHERE id = 1;
ROLLBACK;