Parameterised UPDATE and DELETE from code

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which parameterised DELETE removes only the chosen student from Python code?

A cur.execute("DELETE FROM students WHERE studentId = ?", (sid,))

B cur.execute("DELETE FROM students", (sid,))

2. What does the ? do in UPDATE students SET points = ? WHERE studentId = ?

A It is a placeholder that gets filled by a value passed in from code

B It is a wildcard that matches any student in the table

3. After a parameterised DELETE, cur.rowcount is 0. What does this tell you?

A The record was deleted successfully and nothing is left to count

B No record matched the WHERE value, so the record was not found

4. Which UPDATE is safer for changing one student's points from user input?

A cur.execute("UPDATE students SET points = ? WHERE studentId = ?", (pts, sid))

B cur.execute("UPDATE students SET points = " + pts)

5. A user enters studentId 99, which does not exist. What happens when the parameterised DELETE runs?

A It deletes the closest matching student instead

B It runs without an error, but rowcount is 0

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1cur.execute("DELETE FROM students WHERE studentId = ?", (sid,))		AThe WHERE clause with the ? placeholder is missing, so the statement is not limited to one record. DELETE with no WHERE would remove every student.
2if cur.rowcount == 0:		BThe value is joined onto the SQL text instead of using a ? placeholder, which is unsafe and allows SQL injection.
3An UPDATE run from code uses a ? placeholder in WHERE so only the chosen student's points change.		CDeletes only the student whose studentId matches the value in sid, because the ? placeholder is filled safely by the tuple.
4After a DELETE, print a 'student not found' message when no rows were affected.		Dif cur.rowcount == 0: print("Student not found")
5cur.execute("DELETE FROM students", (sid,))		EChecks whether no record was changed, which means the record was not found.
6cur.execute("UPDATE students SET points = 90 WHERE studentId = " + sid)		Fcur.execute("UPDATE students SET points = ? WHERE studentId = ?", (150, 3))

Definitions

DELETE FROM students WHERE studentId = ?

Fill in the Blanks

Complete each line of code by filling in the missing parts.

sql = "DELETE FROM orders WHERE orderId = _" cursor.execute(sql, (order_id,))

sql = "UPDATE orders SET quantity = ? " sql += "_____ orderId = ?" cursor.execute(sql, (qty, order_id))

if cursor.________ == 0: print("Record not found")

if cursor.rowcount == _: print("Record not found")

cursor.execute(sql, (___, order_id)) conn.commit()

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A sql = 'DELETE FROM students'

B sql += ' WHERE studentId = 5'

C cur.execute(sql, (sid,))

D conn.commit()

Question 2

A sql = 'UPDATE products'

B sql += ' SET stock TO ?'

C sql += ' WHERE productId = ?'

D cur.execute(sql, (qty, pid))

Question 3

A cur.execute(sql, pts, sid)

B conn.commit()

C if cur.rowcount == 0:

D print('Record not found')

Question 4

A cur.execute(sql, (sid,))

B if cur.rowcount() == 0:

C print('Record not found')

D else:

E print('Student deleted')

Question 5

A sid = int(input('ID: '))

B cur.execute(sql, (sid,))

C conn.commit()

D if cur.rowcount == '0':

E print('No such student')

Answer Key

Parameterised UPDATE and DELETE from code

Section A: Quick Questions

A — cur.execute("DELETE FROM students WHERE studentId = ?", (sid,))

A — It is a placeholder that gets filled by a value passed in from code

B — No record matched the WHERE value, so the record was not found

A — cur.execute("UPDATE students SET points = ? WHERE studentId = ?", (pts, sid))

B — It runs without an error, but rowcount is 0

Section B: Match the Pairs

cur.execute("DELETE FROM students WHERE studentId = ?", (sid,)) → Deletes only the student whose studentId matches the value in sid, because the ? placeholder is filled safely by the tuple.

if cur.rowcount == 0: → Checks whether no record was changed, which means the record was not found.

An UPDATE run from code uses a ? placeholder in WHERE so only the chosen student's points change. → cur.execute("UPDATE students SET points = ? WHERE studentId = ?", (150, 3))

After a DELETE, print a 'student not found' message when no rows were affected. → if cur.rowcount == 0: print("Student not found")

cur.execute("DELETE FROM students", (sid,)) → The WHERE clause with the ? placeholder is missing, so the statement is not limited to one record. DELETE with no WHERE would remove every student.

cur.execute("UPDATE students SET points = 90 WHERE studentId = " + sid) → The value is joined onto the SQL text instead of using a ? placeholder, which is unsafe and allows SQL injection.

Section C: Definitions

DELETE FROM students WHERE studentId = ? — A parameterised DELETE or UPDATE run from code: the ? in WHERE is filled by a value so only that record changes. cursor.rowcount of 0 means record not found.

Section D: Fill in the Blanks

sql = "DELETE FROM orders WHERE orderId = ?"
cursor.execute(sql, (order_id,))

sql = "UPDATE orders SET quantity = ? "
sql += "WHERE orderId = ?"
cursor.execute(sql, (qty, order_id))

if cursor.rowcount == 0:
    print("Record not found")

if cursor.rowcount == 0:
    print("Record not found")

cursor.execute(sql, (qty, order_id))
conn.commit()

Section E: Spot the Error

B — The id is hard-coded as 5, so there is no ? placeholder. The code would always target student 5, and the (sid,) value would not match any placeholder. It should be sql += ' WHERE studentId = ?'.

B — SQL assigns a new value in SET with =, not the word TO. The line should be sql += ' SET stock = ?'.

A — execute() takes the SQL and ONE sequence of values. The values must be bundled in a tuple: cur.execute(sql, (pts, sid)).

B — rowcount is an attribute of the cursor, not a method, so it has no brackets. It should be if cur.rowcount == 0:.

D — cur.rowcount is an integer, so comparing it with the string '0' is never true and 'record not found' would never show. It should be if cur.rowcount == 0:.