Understand SQL: DELETE vs TRUNCATE, VIEW vs TABLE, CROSS JOIN

employees

+----+--------+---------+
| id | name   | dept_id |
+----+--------+---------+
| 1  | Alice  | 10      |
| 2  | Bob    | 20      |
| 3  | Carol  | 10      |
| 4  | Dave   | 30      |
+----+--------+---------+

departments

+---------+-------------+
| dept_id | dept_name   |
+---------+-------------+
| 10      | Sales       |
| 20      | Marketing   |
| 30      | Engineering |
+---------+-------------+

Scenario

Amazon BIE interview: assessing SQL fundamentals and understanding of relational concepts.

Question

Compare DELETE and TRUNCATE; when would you use each?

Contrast a VIEW with a physical TABLE; what are pros/cons?

What does a CROSS JOIN do, and in what real-world scenario would you apply it?

Hints

Discuss transactional logging, constraints, persistence, and Cartesian product size considerations.

DELETE vs TRUNCATE, VIEW vs TABLE, and CROSS JOIN example

You are interviewing for a BIE role and are being assessed on SQL fundamentals and relational concepts.

1. Conceptually compare DELETE and TRUNCATE: how do they differ in behavior (logging, constraints, triggers, rollback) and when would you use each?
2. Contrast a VIEW with a physical TABLE: what are the main pros/cons and use cases of each?
3. Using the tables below, write a SQL query that uses a CROSS JOIN to return all combinations of employees and departments, showing employee_id, employee_name, dept_id, and dept_name.

Table employees

column	type	constraints
id	INTEGER	PRIMARY KEY, NOT NULL
name	VARCHAR	NOT NULL
dept_id	INTEGER	NOT NULL, FOREIGN KEY REFERENCES departments(dept_id)

Sample data:

dept_id	id	name
10	1	Alice
20	2	Bob
10	3	Carol
30	4	Dave

Table departments

column	type	constraints
dept_id	INTEGER	PRIMARY KEY, NOT NULL
dept_name	VARCHAR	NOT NULL

Sample data:

dept_id	dept_name
10	Sales
20	Marketing
30	Engineering

Expected output

Conceptual components: DELETE removes rows matching an optional WHERE clause; it is row-by-row, fully logged, fires DELETE triggers, respects foreign keys, and can be rolled back in a transaction. Use DELETE when removing a subset of data or when triggers/auditing must run. TRUNCATE removes all rows by deallocating data pages; it is typically minimally logged (implementation-dependent), does not allow WHERE, often resets identity/sequence values, does not fire row-level DELETE triggers, and is restricted when the table is referenced by foreign keys (for example, you cannot TRUNCATE departments here while employees references it). Use TRUNCATE to quickly empty staging or work tables when safe and allowed.

A VIEW is a stored SELECT definition (logical object); it computes results at query time, providing abstraction, reuse, and security by exposing only needed columns/rows. Performance depends on the underlying query unless using special materialized/indexed forms. A TABLE physically stores data with indexes and constraints and is the primary persistence object for reads/writes.

For the CROSS JOIN part, the correct query should return the Cartesian product of employees and departments (4 × 3 = 12 rows) with employee_id, employee_name, dept_id, and dept_name as shown below.

dept_id	dept_name	employee_id	employee_name
10	Sales	1	Alice
20	Marketing	1	Alice
30	Engineering	1	Alice
10	Sales	2	Bob
20	Marketing	2	Bob
30	Engineering	2	Bob
10	Sales	3	Carol
20	Marketing	3	Carol
30	Engineering	3	Carol
10	Sales	4	Dave
20	Marketing	4	Dave
30	Engineering	4	Dave

Hints

1. Think about how DELETE logs each row change versus how TRUNCATE deallocates entire data pages.
2. Consider how foreign keys and triggers might prevent or behave differently under TRUNCATE versus DELETE.
3. Views are stored SELECT definitions that do not store data themselves (unless materialized); tables physically store data.
4. A CROSS JOIN returns the Cartesian product of two tables; be mindful that the number of rows multiplies (m × n).