AMCAT SQL and Database Concepts 2 — Questions and Answers
Question 1: What is the difference between DELETE and TRUNCATE in SQL?
- DELETE removes the table structure; TRUNCATE removes only data
- DELETE can use a WHERE clause to remove specific rows; TRUNCATE removes all rows and cannot be filtered (Correct answer)
- TRUNCATE is slower than DELETE because it logs each row
- There is no difference; they are interchangeable
Correct 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.
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?
- FOREIGN KEY constraint
- NOT NULL constraint
- PRIMARY KEY constraint (uniqueness) (Correct answer)
- CHECK constraint
Correct 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.
Question 3: What is a correlated subquery?
- A subquery that runs once and returns a static result used by the outer query
- A subquery that references columns from the outer query and executes once for each row of the outer query (Correct answer)
- A subquery that joins two tables internally
- A subquery that always returns exactly one row
Correct 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.
Question 4: In database design, what does a foreign key represent?
- A column that uniquely identifies each row in its own table
- A column that establishes a link between data in two tables by referencing the primary key of another table (Correct answer)
- A column that stores encrypted values
- A column that automatically increments with each new row
Correct 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.
Question 5: Which SQL clause is used to filter groups after a GROUP BY operation?
- WHERE
- HAVING (Correct answer)
- ORDER BY
- FILTER
Correct 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.
Question 6: What is the result of the following query? SELECT COALESCE(NULL, NULL, 'AMCAT', 'Exam');
- NULL
- AMCAT (Correct answer)
- Exam
- Error: too many arguments
Correct 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.
AMCAT SQL and Database Concepts 2
What is the difference between DELETE and TRUNCATE in SQL?