AMCAT SQL and Database Concepts 2

Question 1

What is the difference between DELETE and TRUNCATE in SQL?

Accepted Answer

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

Answer

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.

Question 2

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?

Accepted Answer

PRIMARY KEY constraint (uniqueness)

Answer

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.

Question 3

What is a correlated subquery?

Accepted Answer

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

Answer

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.

Question 4

In database design, what does a foreign key represent?

Accepted Answer

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

Answer

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.

Question 5

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

Accepted Answer

HAVING

Answer

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.

AMCAT Practice Test

AMCAT Practice Test

AMCAT SQL and Database Concepts 2