21. How to select UNIQUE records from a table in SQL?
Answer:
Use the DISTINCT keyword or GROUP BY clause.
Example using DISTINCT:
sql
SELECT DISTINCT Department FROM Employees;
Example using GROUP BY:
sql
SELECT Department FROM Employees GROUP BY Department;
22. How to delete DUPLICATE records from a table in SQL?
Answer:
Use ROW_NUMBER() to identify duplicates:
sql
WITH Duplicates AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY EmployeeID, Name, Salary ORDER BY EmployeeID) AS rn
FROM Employee
)
DELETE FROM Duplicates WHERE rn > 1;
Alternate Method Using ROWID (in Oracle):
sql
DELETE FROM Employee
WHERE ROWID NOT IN (
SELECT MIN(ROWID)
FROM Employee
GROUP BY EmployeeID, Name, Salary
);
23. How to read TOP 5 records from a table in SQL?
Answer:
🔹 SQL Server:
sql
SELECT TOP 5 * FROM Employees;
🔹 MySQL:
sql
SELECT * FROM Employees LIMIT 5;
🔹 Oracle (using ROWNUM):
sql
SELECT * FROM (
SELECT * FROM Employees
) WHERE ROWNUM <= 5;
🔹 PostgreSQL:
sql
SELECT * FROM Employees LIMIT 5;
24. How do you read the last 5 records from a table using SQL?
Answer:
In standard SQL, there’s no direct concept of “last rows” unless the table has a column that defines the order — such as a primary key or timestamp. Let’s assume we have a column like department_id for ordering in a DEPARTMENTS table.
Here’s a general approach using ORDER BY and LIMIT (in MySQL/PostgreSQL):
sql
SELECT *
FROM DEPARTMENTS
ORDER BY department_id DESC
LIMIT 5;
If you’re using Oracle, which doesn’t support LIMIT, use a subquery with ROWNUM:
sql
SELECT *
FROM (
SELECT *
FROM DEPARTMENTS
ORDER BY department_id DESC
)
WHERE ROWNUM <= 5;
This gets the last 5 records based on the highest department_id.
25. How to find the employee with the second highest salary without using analytic functions?
Answer:
We need to exclude the highest salary and then find the maximum among the remaining salaries. Here’s one way to do it:
sql
SELECT MAX(salary)
FROM EMPLOYEES
WHERE salary < (
SELECT MAX(salary) FROM EMPLOYEES
);
To get the employee details with that salary:
sql
SELECT *
FROM EMPLOYEES
WHERE salary = (
SELECT MAX(salary)
FROM EMPLOYEES
WHERE salary < (SELECT MAX(salary) FROM EMPLOYEES)
);
This avoids using window functions and still finds the correct result.
26. How to find the employee with the third highest salary (without using analytic functions)?
Answer:
To find the third maximum salary, it’s better to use a subquery that eliminates the top 2 salaries:
sql
SELECT MAX(salary)
FROM EMPLOYEES
WHERE salary < (
SELECT MAX(salary)
FROM EMPLOYEES
WHERE salary < (
SELECT MAX(salary)
FROM EMPLOYEES
)
);
To get the full employee record with the third highest salary:
sql
SELECT *
FROM EMPLOYEES
WHERE salary = (
SELECT MAX(salary)
FROM EMPLOYEES
WHERE salary < (
SELECT MAX(salary)
FROM EMPLOYEES
WHERE salary < (
SELECT MAX(salary)
FROM EMPLOYEES
)
)
);
Although nested, this helps avoid using analytic functions.
27. What is a Trigger in SQL?
Answer:
A trigger is a block of SQL code that automatically executes when certain events occur on a table. These events can be:
- INSERT
- UPDATE
- DELETE
Example:
sql
CREATE TRIGGER trg_before_insert
BEFORE INSERT ON EMPLOYEES
FOR EACH ROW
BEGIN
— some logic
END;
Triggers are useful for enforcing business rules or auditing changes automatically.
28. What is a CHECK Constraint in SQL?
Answer:
The CHECK constraint ensures that a column contains only values that satisfy a specific condition.
Example:
sql
CREATE TABLE EMPLOYEES (
id INT,
salary INT CHECK (salary > 0)
);
This ensures that salary must always be greater than 0.
29. What is Database White Box Testing?
Answer:
Database White Box Testing involves testing internal database structures, including:
- Data Integrity (like ACID properties)
- Triggers
- Stored Procedures
- Views
- Referential Integrity
- Code Coverage (like condition, decision, and statement coverage)
It ensures that the logic inside the database works as intended and handles all conditions.
30. What are Nested Triggers in SQL?
Answer:
Nested triggers are triggers that fire other triggers.
For example:
- A trigger on table A performs an INSERT into table B.
- Table B also has a trigger, which then fires due to this insert.
These are useful, but can lead to infinite loops if not handled carefully.
31. How do you get the active user count for daily cohorts?
Answer:
Assume you have users and sessions tables.
Here’s the general idea:
sql
SELECT u.signup_date AS cohort_date, COUNT(DISTINCT s.user_id) AS active_users
FROM users u
JOIN sessions s ON u.user_id = s.user_id
WHERE DATE(s.session_date) = DATE(u.signup_date)
GROUP BY u.signup_date;
This returns the number of users active on the day they signed up, known as daily cohorts.
32. How do you get users whose first purchase was at least $50?
Answer:
Assume a transactions table with user_id, spend, and transaction_date.
Using RANK() to find first purchase:
sql
WITH ranked AS (
SELECT user_id, spend,
RANK() OVER (PARTITION BY user_id ORDER BY transaction_date) AS rnk
FROM transactions
)
SELECT user_id
FROM ranked
WHERE rnk = 1 AND spend >= 50;
This fetches users whose earliest purchase was at least $50.
33. How do you get users and total products bought by latest transaction date?
Answer:
Assume a transactions table with user_id, product_id, and transaction_date.
Step 1 – Find latest date & total products for each user:
sql
WITH user_summary AS (
SELECT user_id,
MAX(transaction_date) AS latest_date,
COUNT(DISTINCT product_id) AS product_count
FROM transactions
GROUP BY user_id
)
SELECT latest_date,
COUNT(user_id) AS user_count,
SUM(product_count) AS total_products
FROM user_summary
GROUP BY latest_date;
This groups users based on latest transaction date.
34. How do you get the percentage of time spent sending vs opening snaps by age group?
Answer:
Assume a table snap_activity with user_id, activity_type (send/open), time_spent, and age_bucket.
Step 1 – Aggregate send/open time by age:
sql
WITH send_time AS (
SELECT age_bucket, SUM(time_spent) AS total_send
FROM snap_activity
WHERE activity_type = ‘send’
GROUP BY age_bucket
),
open_time AS (
SELECT age_bucket, SUM(time_spent) AS total_open
FROM snap_activity
WHERE activity_type = ‘open’
GROUP BY age_bucket
)
SELECT s.age_bucket,
total_send,
total_open,
ROUND((total_send * 100.0) / (total_send + total_open), 2) AS send_pct,
ROUND((total_open * 100.0) / (total_send + total_open), 2) AS open_pct
FROM send_time s
JOIN open_time o ON s.age_bucket = o.age_bucket;
This gives percentage breakdown for send/open per age group.
35. How to find top-rated businesses with only 4 or 5 star reviews?
Answer:
Assume a reviews table with business_id and stars.
sql
WITH review_summary AS (
SELECT business_id,
COUNT(*) AS total_reviews,
SUM(CASE WHEN stars IN (4,5) THEN 1 ELSE 0 END) AS top_reviews
FROM reviews
GROUP BY business_id
)
SELECT
COUNT(*) AS top_rated_count,
ROUND((COUNT(*) * 100.0) / (SELECT COUNT(DISTINCT business_id) FROM reviews), 2) AS percentage
FROM review_summary
WHERE total_reviews = top_reviews;
This finds businesses where all reviews are 4 or 5 stars, and calculates the percentage out of total businesses.
