Database Management & SQL Flashcards

A tobacco shop wants to sort its cigar inventory by price from highest to lowest. Which ORDER BY clause achieves this?

Answer: ORDER BY price DESC

ORDER BY price DESC sorts results in descending (high-to-low) order.

Which SQL keyword is used to update the price of a specific tobacco product already in the database?

Answer: UPDATE

The UPDATE statement modifies existing data in one or more rows of a table.

A manager runs: SELECT brand, AVG(price) FROM products GROUP BY brand. What does this query return?

Answer: The average price for each brand

AVG() with GROUP BY calculates the average value of a column for each distinct group.

Which SQL clause filters results AFTER an aggregation has been performed on a tobacco sales report?

Answer: HAVING

HAVING filters grouped results after aggregation, whereas WHERE filters individual rows before aggregation.

In a retail POS database, what is a FOREIGN KEY used for?

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.

A SQL query uses LEFT JOIN between a customers table and a purchases table. What rows are returned?

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.

Which SQL command is used to create a new table called 'tobacco_inventory' in a retail database?

Answer: CREATE TABLE tobacco_inventory

CREATE TABLE is the DDL command used to define and create a new database table.