Practice set for DBA Lecture 4. Read the full lesson first: DML and Transaction Control in Oracle on eLecturesAI.
Reading a topic and knowing a topic are two different things. The quickest way to find out which one you have is to answer questions without looking at your notes.
This page has 15 MCQs, 5 short questions and a score guide, all based on DBA Lecture 4: the families of SQL, INSERT, UPDATE and DELETE, transactions, COMMIT and ROLLBACK, who sees your changes, SAVEPOINT and automatic commits.
- Click the option you think is correct. It turns green if you are right and red if you are wrong.
- Read the explanation that opens under the question.
- If you got it wrong, use the Revise link to go back to that exact part of the lesson.
- Check your score at the end and follow the guide.
Section 1: SQL families and DML
Q1Which family of SQL do the commands INSERT, UPDATE and DELETE belong to?
Show answer
Answer: B. DML changes the data in tables. DDL defines structure, SELECT only reads, and COMMIT, ROLLBACK and SAVEPOINT control transactions.
Revise: The families of SQL
Q2Which statement about saving changes is correct?
Show answer
Answer: D. DDL commands such as CREATE TABLE commit by themselves. DML commands wait until you COMMIT (or ROLLBACK).
Revise: The families of SQL
Q3The student table has 3 rows (Ali, Sara and Hamza). What does UPDATE student SET marks = 0; do?
Show answer
Answer: C. No WHERE means all rows. SQL*Plus even reports it: 3 rows updated.
Q4What is the safest habit before running DELETE FROM student WHERE id = 4;?
Show answer
Answer: A. A SELECT with the same WHERE shows the rows you are about to change. If it shows only the rows you want, run the DELETE.
Q5Why is it safer to name the columns in an INSERT, as in INSERT INTO student (id, name, marks) VALUES (...)?
Show answer
Answer: C. Naming the columns means the command does not depend on their order in the table.
Revise: INSERT, UPDATE and DELETE
Section 2: Transactions, COMMIT and ROLLBACK
Q6What is a transaction?
Show answer
Answer: A. A transaction groups changes that must succeed together. Issuing a library book needs two changes, and both must happen or neither.
Revise: What is a transaction?
Q7Issuing a book needs two changes: reduce the copies and record who took it. Only the first change happens. What is the problem?
Show answer
Answer: B. This is why both changes must be grouped in one transaction: together or not at all.
Revise: What is a transaction?
Q8What does COMMIT do?
Show answer
Answer: D. COMMIT makes the changes permanent and visible to everyone. ROLLBACK is the command that cancels them.
Revise: COMMIT and ROLLBACK
Q9You ran DELETE FROM student WHERE id = 1; by mistake and have not committed. What should you do?
Show answer
Answer: B. Before the commit, ROLLBACK cancels the change. If you had run COMMIT instead, Ali would be gone for good.
Revise: COMMIT and ROLLBACK
Q10What makes it possible for ROLLBACK to bring old values back?
Show answer
Answer: A. Before Oracle changes a row it keeps the old values in undo data. In the library story, that is the photocopy you keep before erasing a register entry.
Revise: COMMIT and ROLLBACK
Section 3: Who sees changes, and how to control them
Q11Before I COMMIT, who can see my changes?
Show answer
Answer: C. Uncommitted work is like the rough register: only the librarian sees it. After COMMIT it is copied to the official register that everyone reads.
Revise: Who sees my changes?
Q12I updated the row of book 101 and did not commit. Another user tries to update the same row. What happens to them?
Show answer
Answer: D. A row changed by an open transaction cannot be changed by another user until the first user commits or rolls back. Long-open transactions therefore block others.
Revise: Open transactions block others
Q13A session crashes or is lost before the user commits. What happens to the uncommitted changes?
Show answer
Answer: B. Oracle rolls back uncommitted work when a session ends abnormally, so half-finished transactions do not become permanent.
Revise: Who sees my changes?
Q14You INSERT Noor, run SAVEPOINT after_noor, INSERT Bilal, and then run ROLLBACK TO after_noor. What remains?
Show answer
Answer: A. ROLLBACK TO undoes only what came after the savepoint. Noor stays, but the transaction is still open until you COMMIT.
Revise: SAVEPOINT: a checkpoint
Q15You INSERT Ayesha without committing, then run CREATE TABLE test (x NUMBER);, then ROLLBACK. What happens to Ayesha’s row?
Show answer
Answer: C. A DDL command commits the transaction automatically, so the ROLLBACK that follows has nothing left to undo.
Revise: Automatic commits
Short questions (write the answers in your own words)
Don’t just read these. Write each answer on paper in 2-3 lines, then compare.
S1. Compare DDL and DML in terms of saving. Give two commands for each.
Show model answer
DDL (for example CREATE and ALTER) defines or changes structure and saves automatically. DML (for example INSERT and UPDATE) changes the data and does not save by itself: the changes wait for COMMIT, or can be cancelled with ROLLBACK.
S2. Explain a transaction using the library example of issuing a book. Why must both changes be in one transaction?
Show model answer
Issuing a book needs two changes: reduce the number of copies and record who took the book. If only the first happens, a copy is gone and nobody is recorded, so the book disappears. A transaction groups both changes so that they succeed together (COMMIT) or not at all (ROLLBACK).
S3. What happens if you run an UPDATE without a WHERE clause, and how do you prevent it?
Show model answer
Every row in the table is changed, for example all students get 0 marks. To prevent it, run a SELECT with the same WHERE first and check that it shows only the rows you want, then run the UPDATE or DELETE and compare the rows-updated message with what you expected. If you have not committed yet, ROLLBACK can still undo the mistake.
S4. Explain SAVEPOINT using the example of Noor and Bilal. What would a plain ROLLBACK do?
Show model answer
After INSERT Noor we run SAVEPOINT after_noor, then INSERT Bilal. ROLLBACK TO after_noor undoes only Bilal, so Noor stays, and the transaction is still open until COMMIT. A plain ROLLBACK would remove both Noor and Bilal.
S5. Why does a DBA worry about transactions that stay open, and which two habits help?
Show model answer
An open transaction holds the rows it changed, so other users who want the same rows must wait, and a transaction left open for hours blocks them. Good habits: finish every transaction with COMMIT or ROLLBACK, and never rely on EXIT in SQL*Plus, because a normal EXIT commits pending changes.
Check your score
Count your correct MCQs (out of 15):
| Score | What it means | What to do next |
|---|---|---|
| 14 – 15 | Excellent. The concepts are solid. | Move on to the next lecture. |
| 11 – 13 | Good, with a few gaps. | Re-read only the sections linked under the questions you missed. |
| 8 – 10 | Half-way there. | Read the lesson again slowly, then retry this quiz tomorrow. |
| Below 8 | The basics are not settled yet. | Read the full lesson once more, trace the library register example from memory, then retake the quiz. |
Go back to the full lesson: DML and Transaction Control in Oracle →
Frequently asked questions
Are these MCQs enough for the exam?
They cover the whole of Lecture 4, but real exams also ask you to write short answers and trace small examples. Practice the five short questions above as well.
What is the best way to use an MCQ quiz?
Answer first, check later. Reading the answer without trying the question gives a false feeling of knowing it.
Where do I learn the topic if I got most answers wrong?
Start with the lesson on eLecturesAI: DML and Transaction Control in Oracle. Each question above also links to the exact section to revise.
Written by Engnr.Dr. Muhammad Tahir Dlbar
Leave a Reply