Understand SQL: DELETE vs TRUNCATE, VIEW vs TABLE, CROSS JOIN
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates a candidate's understanding of core SQL concepts and data-manipulation competencies, covering the behavior of data-deletion operations, distinctions between logical and physical data structures, and join semantics.
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.
Tables
employees(id INTEGER, name VARCHAR, dept_id INTEGER)
departments(dept_id INTEGER, dept_name VARCHAR)
Hints
- Think about how DELETE logs each row change versus how TRUNCATE deallocates entire data pages.
- Consider how foreign keys and triggers might prevent or behave differently under TRUNCATE versus DELETE.