Q: Create an Empty Table While Retaining Its Structure. Then merge the above two lines (the CREATE EMPTY TABLE approach and the result) and give the expected output.

Creating an empty table that retains the structure of an existing table: Method 1: CREATE TABLE AS SELECT with false condition (most portable) Method 2: Using LIKE (PostgreSQL, MySQL) Method 3: TRUNCATE to empty an existing copy Merging result — combining the structure copy step with data population: Key points: WHERE 1=0 is the most database agnostic technique for copying structure without data LIKE in MySQL also copies indexes and constraints; CREATE TABLE AS SELECT does not copy constraints TRUNCATE is faster than DELETE FROM for clearing all rows — it does a bulk deallocation and resets auto increment counters (in MySQL)

Question 1

Write an SQL query to display the names of employees along with the names of their respective managers. If an employee does not have a manager, the manager's name should be displayed as NULL.

Accepted Answer

This is a classic self join problem on an employee table where the manager relationship is stored as a foreign key back to the same table. Table assumption: Solution: Sample output: Key points: A LEFT JOIN (not INNER JOIN) is essential — INNER JOIN would exclude employees without a manager (top level executives), but the requirement says show their manager as NULL e is the employee alias; m is the manager alias — both reference the same table The join condition e.manager id = m.emp id links each employee to their manager's row For employees where manager id IS NULL , the LEFT JOIN produces NULL for all m. columns, satisfying the requirement

Question 2

In a table transactions with columns transaction_id, customer_id, transaction_date, and Amount, write a query to calculate a running total of `amount` for each customer, ordered by `transaction_date`.

Accepted Answer

A running total (cumulative sum) is a classic window function use case. Sample output: Key points: PARTITION BY customer id resets the running total for each new customer ORDER BY transaction date defines the accumulation order within each partition ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the explicit frame — it includes all rows from the start of the partition up to and including the current row The default frame for an ordered window is already UNBOUNDED PRECEDING AND CURRENT ROW , so the ROWS BETWEEN clause can be omitted, but being explicit improves readability

Question 3

You have a table Customer_visits with columns Visit_id, customer_id, and visit_date. Write a query to identify and retain only the latest visit for each customer and delete the older duplicate.

Accepted Answer

Alternative — self join DELETE (works in MySQL which doesn't support CTEs in DELETE targets): Verify after deletion: Key points: ROW NUMBER() with ORDER BY visit date DESC assigns rank 1 to the most recent visit per customer Deleting where rn 1 removes all older visits, leaving exactly one row per customer The self join alternative is simpler but less readable for complex logic Always test the SELECT equivalent of your DELETE query before executing to verify the correct rows are targeted

Question 4

You are given a table named Bookings with columns BookingId, StartDate, and EndDate. Write an SQL query to find pairs of overlapping bookings, where one booking's date range overlaps with another booking's date range.

Accepted Answer

Two date ranges [A.start, A.end] and [B.start, B.end] overlap if and only if A.start <= B.end AND A.end = B.start . Visual overlap check: Sample output: Key points: a.BookingId < b.BookingId ensures each pair appears once and a booking is never paired with itself The overlap condition is the inverse of "no overlap": ranges don't overlap only when A.end < B.start OR B.end < A.start This is a self join with a non equi join condition on date ranges

Question 5

Create an Empty Table While Retaining Its Structure. Then merge the above two lines (the CREATE EMPTY TABLE approach and the result) and give the expected output.

Accepted Answer

Creating an empty table that retains the structure of an existing table: Method 1: CREATE TABLE AS SELECT with false condition (most portable) Method 2: Using LIKE (PostgreSQL, MySQL) Method 3: TRUNCATE to empty an existing copy Merging result — combining the structure copy step with data population: Key points: WHERE 1=0 is the most database agnostic technique for copying structure without data LIKE in MySQL also copies indexes and constraints; CREATE TABLE AS SELECT does not copy constraints TRUNCATE is faster than DELETE FROM for clearing all rows — it does a bulk deallocation and resets auto increment counters (in MySQL)

AppZen Interview Questions

Write an SQL query to display the names of employees along with the names of their respective managers. If an employee does not have a manager, the manager's name should be displayed as NULL.

In a table transactions with columns transaction_id, customer_id, transaction_date, and Amount, write a query to calculate a running total of `amount` for each customer, ordered by `transaction_date`.

You have a table Customer_visits with columns Visit_id, customer_id, and visit_date. Write a query to identify and retain only the latest visit for each customer and delete the older duplicate.

You are given a table named Bookings with columns BookingId, StartDate, and EndDate. Write an SQL query to find pairs of overlapping bookings, where one booking's date range overlaps with another booking's date range.

Create an Empty Table While Retaining Its Structure. Then merge the above two lines (the CREATE EMPTY TABLE approach and the result) and give the expected output.