SQL Techniques: Finding Duplicate Records, TRUNCATE vs DELETE, Primary vs Candidate Keys, Second Highest Salary, and Common Interview Queries

To find duplicate records in a database table, define duplication (e.g., same name and phone) and use ANSI SQL GROUP BY with HAVING to filter groups having a count greater than one.

mysql> select name, count(name) from contacts group by name, phone having count(name) > 1;

The TRUNCATE command is faster than DELETE, does not log row-by-row deletions, cannot be rolled back unless supported, does not fire triggers, resets identity columns, and is a DDL operation, whereas DELETE is slower, logs each row, can be rolled back, fires triggers, supports WHERE clauses, and is DML.

Candidate keys and primary keys both uniquely identify rows and have UNIQUE and NOT NULL constraints; a primary key is a chosen candidate key, making it a subset of candidate keys.

-- Example of candidate key vs primary key explanation (no code needed)

To retrieve the second highest salary, use subqueries with NOT IN or < operator, or employ LIMIT to order salaries descending and select the second row.

SELECT max(salary) FROM Employee WHERE salary NOT IN (SELECT max(salary) FROM Employee);
SELECT max(salary) FROM Employee WHERE salary < (SELECT max(salary) FROM Employee);
SELECT salary FROM (SELECT salary FROM Employee ORDER BY salary DESC LIMIT 2) AS emp ORDER BY salary LIMIT 1;

The article also presents twelve common SQL interview queries, covering topics such as employees earning more than their managers, highest-paid employees per department, departments with fewer than three members, total salaries per department, and more, each accompanied by sample SQL statements.

-- Example query: employees with salary higher than their manager
SELECT a.emp_name FROM Employee a JOIN Employee b ON a.mngr_id = b.emp_id WHERE a.salary > b.salary;

-- Highest salary per department
SELECT a.emp_name, a.dept_id FROM Employee a JOIN (SELECT dept_id, MAX(salary) AS max_salary FROM Employee GROUP BY dept_id) b ON a.dept_id = b.dept_id AND a.salary = b.max_salary;

-- Departments with fewer than 3 employees
SELECT dept_id, COUNT(emp_name) AS "Number of Employee" FROM Employee GROUP BY dept_id HAVING COUNT(emp_name) < 3;

-- All departments with employee count
SELECT b.dept_name, COUNT(a.dept_id) AS "Number of Employee" FROM Employee a FULL OUTER JOIN Department b ON a.dept_id = b.dept_id GROUP BY b.dept_name;

-- Employees without a manager in the same department
SELECT a.emp_name FROM Employee a JOIN Employee b ON a.mngr_id = b.emp_id WHERE a.dept_id != b.dept_id;

-- Total salary per department
SELECT b.dept_name, SUM(a.salary) AS "Total Salary" FROM Employee a FULL OUTER JOIN Department b ON a.dept_id = b.dept_id GROUP BY b.dept_name;