Data Manipulation Flashcards
(15 cards)
What does CRUD stand for and which SQL commands perform each operation?
CREATE: INSERT INTO - READ: SELECT - UPDATE: UPDATE - DELETE: DELETE FROM. These are the four fundamental data manipulation operations.
Write the complete syntax for inserting a single record into a table.
INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …); - Column names in parentheses - VALUES keyword required - Values must match column order positionally
How do you insert multiple records in a single INSERT statement?
INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …), (value1, value2, value3, …), (value1, value2, value3, …); - Separate each record’s values with commas - Each record in its own parentheses
What happens to columns not mentioned in an INSERT statement?
They automatically take NULL values by default for all inserted records. This assumes the column allows NULL values or has a default constraint.
You’re inserting data: INSERT INTO users (name, email) VALUES (‘John’, 25, ‘john@email.com’). What’s wrong?
Value count doesn’t match column count. Should be: INSERT INTO users (name, email) VALUES (‘John’, ‘john@email.com’); - Values must match columns positionally - Extra value (25) causes error
Write the syntax to delete specific records from a table.
DELETE FROM table_name WHERE condition; - WHERE clause specifies which records to delete - Without WHERE, ALL records are deleted - No column names needed in DELETE
What’s the critical difference between these two commands? DELETE FROM users WHERE age > 65; vs DELETE FROM users;
First deletes only users over 65. Second deletes ALL users in the table! Always double-check WHERE clauses - missing WHERE in DELETE is dangerous.
Write the complete syntax for updating records in a table.
UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; - SET keyword required - Multiple columns separated by commas - WHERE clause specifies which records to update
What happens if you forget the WHERE clause in an UPDATE statement?
ALL records in the table get updated with the new values! Always verify WHERE clause before running UPDATE commands to avoid mass data corruption.
Fix this UPDATE statement: UPDATE products SET price 29.99 WHERE id = 5;
UPDATE products SET price = 29.99 WHERE id = 5; - Missing equals sign (=) after price - SET requires column = value format
You need to insert a user with name ‘Sarah’ and email ‘sarah@test.com’ into a users table that has columns: id, name, email, created_date. Write the INSERT statement.
INSERT INTO users (name, email) VALUES (‘Sarah’, ‘sarah@test.com’); - Don’t need to specify id (likely auto-increment) or created_date (likely has default) - Only specify columns you’re providing values for
What’s positional matching in INSERT statements and why is it critical?
Values must be in the same order as the columns listed. INSERT INTO table (col1, col2) VALUES (val1, val2) - val1 goes to col1, val2 goes to col2. Wrong order = wrong data in wrong columns.
Compare the risk levels: INSERT vs UPDATE vs DELETE. Which is most dangerous and why?
DELETE without WHERE is most dangerous (loses all data). UPDATE without WHERE is very dangerous (corrupts all data). INSERT is safest (only adds data, doesn’t destroy existing records).
You want to delete all products with price = 0 and update all remaining products to have a 10% price increase. Write both statements.
- DELETE FROM products WHERE price = 0; 2. UPDATE products SET price = price * 1.10 WHERE price > 0; - Delete first, then update remaining - Use WHERE in both for safety
What are the three critical safety rules for data manipulation commands?
- Always use WHERE clauses with UPDATE and DELETE 2. Test conditions with SELECT first 3. Backup data before mass operations - These prevent accidental data loss or corruption
