AMCAT SQL and Database Concepts Flashcards

What is the difference between DELETE and TRUNCATE in SQL?

Answer: DELETE can use a WHERE clause to remove specific rows; TRUNCATE removes all rows and cannot be filtered

DELETE is a DML command that can remove specific rows using a WHERE clause and logs each deletion individually. TRUNCATE is a DDL command that removes all rows from a table at once without logging individual row deletions, making it faster but unable to filter specific rows.

Consider the following SQL: CREATE TABLE Orders (order_id INT PRIMARY KEY, customer_id INT, amount DECIMAL(10,2)); Which constraint is violated if you try to insert two rows with the same order_id?

Answer: PRIMARY KEY constraint (uniqueness)

A PRIMARY KEY constraint enforces two rules: uniqueness (no duplicate values) and NOT NULL (no null values). Inserting two rows with the same order_id violates the uniqueness aspect of the PRIMARY KEY constraint.

What is a correlated subquery?

Answer: A subquery that references columns from the outer query and executes once for each row of the outer query

A correlated subquery references one or more columns from the outer query, creating a dependency. It must be re-evaluated for each row processed by the outer query, unlike a non-correlated subquery which executes independently just once.

In database design, what does a foreign key represent?

Answer: A column that establishes a link between data in two tables by referencing the primary key of another table

A foreign key is a column (or set of columns) in one table that references the primary key of another table. It establishes a referential integrity constraint, ensuring that values in the foreign key column must exist in the referenced primary key column.

Which SQL clause is used to filter groups after a GROUP BY operation?

Answer: HAVING

HAVING is used to filter groups created by GROUP BY based on aggregate conditions (like COUNT, SUM, AVG). WHERE filters individual rows before grouping occurs. ORDER BY sorts results. FILTER is not a standard SQL clause for this purpose.

What is the result of the following query? SELECT COALESCE(NULL, NULL, 'AMCAT', 'Exam');

Answer: AMCAT

COALESCE returns the first non-NULL argument from its list. It evaluates arguments left to right: the first argument is NULL, the second is NULL, the third is 'AMCAT' (not NULL), so it returns 'AMCAT' without evaluating further arguments.