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 removes the table structure; TRUNCATE removes only data

Answer

TRUNCATE is slower than DELETE because it logs each row

Answer

There is no difference; they are interchangeable

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

FOREIGN KEY constraint

Answer

NOT NULL constraint

Answer

CHECK 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 subquery that runs once and returns a static result used by the outer query

Answer

A subquery that joins two tables internally

Answer

A subquery that always returns exactly one row

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 column that uniquely identifies each row in its own table

Answer

A column that stores encrypted values

Answer

A column that automatically increments with each new row

Question 5

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

Accepted Answer

HAVING

Answer

WHERE

Answer

ORDER BY

Answer

FILTER

Question 6

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

Accepted Answer

AMCAT

Answer

NULL

Answer

Exam

Answer

Error: too many arguments