Database Management & SQL Flashcards

A tobacco retailer's database stores customer loyalty points. Which SQL statement adds 50 points to every customer who has made more than 5 purchases?

Answer: UPDATE customers SET points = points + 50 WHERE purchases > 5

UPDATE with SET and WHERE correctly modifies column values for all rows matching the condition.

What is the purpose of a database VIEW in a tobacco retail management system?

Answer: To provide a virtual table based on a saved SELECT query

A VIEW is a named, saved SQL query that acts as a virtual table, simplifying complex queries for repeated use.

A tobacco shop database query uses INNER JOIN between 'orders' and 'products'. What rows does it return?

Answer: Only rows where product_id matches in both tables

INNER JOIN returns only rows where the join condition is satisfied in both tables.

Which SQL concept groups related operations so they either all succeed or all fail together, protecting a tobacco shop's transaction integrity?

Answer: Transaction

A database transaction ensures ACID properties so a group of SQL operations either commits fully or rolls back entirely.

A tobacconist wants to list all distinct countries of origin from a products table. Which query is correct?

Answer: SELECT DISTINCT country FROM products

SELECT DISTINCT eliminates duplicate values and returns only unique entries for the specified column.

In a retail database, what does the ROLLBACK command do when issued during a transaction?

Answer: Undoes all changes made during the current transaction

ROLLBACK reverses all database changes made within the current transaction, restoring the previous consistent state.

A tobacco retailer uses a stored procedure in their POS database. What is a primary advantage of stored procedures?

Answer: They pre-compile SQL logic server-side for reuse and better performance

Stored procedures are precompiled SQL routines stored on the server, improving performance and enabling code reuse.