Question 1
A school library database has the following tables: BOOK(BookID, Title, Author, Genre, CopiesAvailable) LOAN(LoanID, BookID, StudentID, LoanDate, DueDate) A student returns a book, so the number of available copies for BookID = 7 must increase by 1. Which SQL statement correctly performs this update?No clue? Show me the answer
Correct answerCorrect!
IncorrectStep-by-step walkthrough
Choose a solution method
Method #1Approach 1Step 1: Identify the required operation
We need to modify an existing record in the BOOK table — specifically, increment the
CopiesAvailablecolumn for one particular row. This calls for anUPDATEstatement.Step 2: Apply the correct UPDATE syntax
The correct syntax is
UPDATE TableName SET Column = Expression WHERE condition;. To increment by 1, we useCopiesAvailable = CopiesAvailable + 1, which reads the current value and adds 1 to it.Step 3: Apply the WHERE clause
We must include
WHERE BookID = 7to restrict the update to only that specific book. Without a WHERE clause, all rows in the BOOK table would have their CopiesAvailable incremented.Step 4: Select the correct answer
The statement
UPDATE BOOK SET CopiesAvailable = CopiesAvailable + 1 WHERE BookID = 7;uses valid syntax and correctly targets only BookID = 7.Method #2Approach 2Step 1: Identify what is being tested
The question tests knowledge of UPDATE SET syntax in SQL, including the correct use of expressions and the WHERE clause.
Step 2: Eliminate the incorrect SET syntax
"UPDATE BOOK SET CopiesAvailable + 1 WHERE BookID = 7;" is invalid — the SET clause requires an assignment (
column = expression), not just an expression. This would produce a syntax error.Step 3: Eliminate the INSERT statement
"INSERT INTO BOOK (CopiesAvailable) VALUES (CopiesAvailable + 1) WHERE BookID = 7;" is wrong on two counts: INSERT adds new rows rather than modifying existing ones, and INSERT syntax does not support a WHERE clause.
Step 4: Eliminate the missing WHERE clause
"UPDATE BOOK SET CopiesAvailable = CopiesAvailable + 1;" is syntactically valid but catastrophically wrong — the missing WHERE clause means every book in the table would have its CopiesAvailable incremented.
Step 5: Select the correct answer
The only option with correct UPDATE syntax and a properly targeted WHERE clause is
UPDATE BOOK SET CopiesAvailable = CopiesAvailable + 1 WHERE BookID = 7;.Question 2
A hospital database stores patient records. A patient with PatientID = 304 has been discharged and their record must be removed from the PATIENT table. Which SQL statement correctly removes only that patient's record?No clue? Show me the answer
Correct answerCorrect!IncorrectStep-by-step walkthrough
Choose a solution method
Method #1Approach 1Step 1: Identify the correct SQL command for removing rows
To remove one or more rows from a table, SQL uses the
DELETE FROMstatement. This removes data rows but leaves the table structure intact.Step 2: Apply the WHERE clause to target a specific row
The syntax is
DELETE FROM TableName WHERE condition;. We needWHERE PatientID = 304to ensure only that specific patient's record is deleted.Step 3: Confirm the correct keyword
SQL uses
DELETE, notREMOVE. The correct statement isDELETE FROM PATIENT WHERE PatientID = 304;.Step 4: Select the correct answer
Only
DELETE FROM PATIENT WHERE PatientID = 304;uses the correct keyword, correct syntax, and correctly restricts deletion to one row.Method #2Approach 2Step 1: Identify what is being tested
This question tests knowledge of the DELETE statement and the critical importance of including a WHERE clause to avoid unintended mass deletion.
Step 2: Eliminate DELETE without WHERE
"DELETE FROM PATIENT;" would delete every row in the PATIENT table — all patient records would be permanently erased. This does not target a specific patient.
Step 3: Eliminate DROP TABLE
"DROP TABLE PATIENT WHERE PatientID = 304;" is invalid —
DROP TABLEremoves the entire table structure and data. It also does not support a WHERE clause.Step 4: Eliminate non-existent REMOVE keyword
"REMOVE FROM PATIENT WHERE PatientID = 304;" uses
REMOVE, which is not a valid SQL keyword. The correct keyword isDELETE.Step 5: Select the correct answer
DELETE FROM PATIENT WHERE PatientID = 304;is the only syntactically valid statement that removes exactly one targeted record.