CRT Database Management & SQL 3 — Questions and Answers
Question 1: A tobacco shop wants to sort its cigar inventory by price from highest to lowest. Which ORDER BY clause achieves this?
- ORDER BY price ASC
- ORDER BY price DESC (Correct answer)
- ORDER BY price HIGHEST
- SORT BY price DOWN
Correct answer: ORDER BY price DESC
ORDER BY price DESC sorts results in descending (high-to-low) order.
Question 2: Which SQL keyword is used to update the price of a specific tobacco product already in the database?
- MODIFY
- CHANGE
- UPDATE (Correct answer)
- ALTER
Correct answer: UPDATE
The UPDATE statement modifies existing data in one or more rows of a table.
Question 3: A manager runs: SELECT brand, AVG(price) FROM products GROUP BY brand. What does this query return?
- The highest price per brand
- The average price for each brand (Correct answer)
- All products sorted by brand
- The total inventory count per brand
Correct answer: The average price for each brand
AVG() with GROUP BY calculates the average value of a column for each distinct group.
Question 4: Which SQL clause filters results AFTER an aggregation has been performed on a tobacco sales report?
- WHERE
- FILTER
- HAVING (Correct answer)
- AFTER
Correct answer: HAVING
HAVING filters grouped results after aggregation, whereas WHERE filters individual rows before aggregation.
Question 5: In a retail POS database, what is a FOREIGN KEY used for?
- Encrypting sensitive customer data
- Linking a column in one table to the primary key of another table (Correct answer)
- Preventing any data from being deleted
- Creating an index for faster searches
Correct answer: Linking a column in one table to the primary key of another table
A FOREIGN KEY creates a referential link between tables, enforcing relational integrity.
Question 6: A SQL query uses LEFT JOIN between a customers table and a purchases table. What rows are returned?
- Only customers who made purchases
- All customers, even those with no purchases (Correct answer)
- Only purchases with matching customers
- All rows from both tables with no condition
Correct answer: All customers, even those with no purchases
A LEFT JOIN returns all rows from the left table and matched rows from the right table, with NULLs for non-matches.
Question 7: Which SQL command is used to create a new table called 'tobacco_inventory' in a retail database?
- BUILD TABLE tobacco_inventory
- NEW TABLE tobacco_inventory
- CREATE TABLE tobacco_inventory (Correct answer)
- MAKE TABLE tobacco_inventory
Correct answer: CREATE TABLE tobacco_inventory
CREATE TABLE is the DDL command used to define and create a new database table.
CRT Database Management & SQL 3
A tobacco shop wants to sort its cigar inventory by price from highest to lowest.
Which ORDER BY clause achieves this?