ANSI SQL Cheat Sheet + Cognizant SQL Interview Guide
■ ANSI SQL Cheat Sheet (Interview Focus)
1. DDL (Data Definition Language)
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2),
dept_id INT
);
ALTER TABLE Employees ADD COLUMN hire_date DATE;
DROP TABLE Employees;
2. DML (Data Manipulation Language)
INSERT INTO Employees (emp_id, name, salary, dept_id)
VALUES (101, 'John', 50000, 10);
UPDATE Employees SET salary = salary + 5000
WHERE emp_id = 101;
DELETE FROM Employees WHERE emp_id = 101;
3. Basic SELECT
SELECT name, salary
FROM Employees
WHERE salary > 40000
ORDER BY salary DESC;
4. Aggregate Functions + GROUP BY + HAVING
SELECT dept_id, AVG(salary) AS avg_salary, COUNT(*) AS emp_count
FROM Employees
GROUP BY dept_id
HAVING AVG(salary) > 45000;
5. Joins (ANSI Standard)
-- INNER JOIN
SELECT e.name, d.dept_name
FROM Employees e
INNER JOIN Departments d
ON e.dept_id = d.dept_id;
-- LEFT JOIN
SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d
ON e.dept_id = d.dept_id;
-- RIGHT JOIN
SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d
ON e.dept_id = d.dept_id;
-- FULL JOIN
SELECT e.name, d.dept_name
FROM Employees e
FULL JOIN Departments d
ON e.dept_id = d.dept_id;
6. Set Operations
SELECT emp_id FROM Employees
UNION
SELECT emp_id FROM Managers;
SELECT emp_id FROM Employees
INTERSECT
SELECT emp_id FROM Managers;
SELECT emp_id FROM Employees
EXCEPT
SELECT emp_id FROM Managers;
7. Subqueries
-- Simple Subquery
SELECT name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
-- Correlated Subquery
SELECT name
FROM Employees e
WHERE salary > (SELECT AVG(salary)
FROM Employees
WHERE dept_id = e.dept_id);
8. Constraints
CREATE TABLE Departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) UNIQUE,
budget DECIMAL(12,2) CHECK (budget > 0)
);
9. NULL Handling
SELECT name, salary
FROM Employees
WHERE dept_id IS NULL;
SELECT COALESCE(salary, 0) AS salary_with_default
FROM Employees;
10. Common Interview Queries
-- Second highest salary
SELECT MAX(salary)
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);
-- Find employees with no department
SELECT name
FROM Employees
WHERE dept_id IS NULL;
-- Departments with more than 5 employees
SELECT dept_id, COUNT(*)
FROM Employees
GROUP BY dept_id
HAVING COUNT(*) > 5;
■ Cognizant SQL Interview Questions (with Solutions)
Q1. Find the second highest salary
SELECT MAX(salary)
FROM Employees
WHERE salary < (SELECT MAX(salary) FROM Employees);
Q2. Find the Nth highest salary (say 3rd highest)
SELECT salary
FROM Employees e1
WHERE 3 = (
SELECT COUNT(DISTINCT salary)
FROM Employees e2
WHERE e2.salary >= e1.salary
);
Q3. List employees who earn more than the average salary
SELECT name, salary
FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
Q4. Find employees with no department assigned
SELECT name
FROM Employees
WHERE dept_id IS NULL;
Q5. List department-wise highest salary
SELECT dept_id, MAX(salary) AS highest_salary
FROM Employees
GROUP BY dept_id;
Q6. Find departments having more than 5 employees
SELECT dept_id, COUNT(*) AS emp_count
FROM Employees
GROUP BY dept_id
HAVING COUNT(*) > 5;
Q7. Show employees who joined in the last 30 days
SELECT name, hire_date
FROM Employees
WHERE hire_date >= CURRENT_DATE - INTERVAL '30' DAY;
Q8. Retrieve duplicate salaries
SELECT salary, COUNT(*) AS count
FROM Employees
GROUP BY salary
HAVING COUNT(*) > 1;
Q9. Find employees who earn the maximum salary in each department
SELECT name, dept_id, salary
FROM Employees e
WHERE salary = (
SELECT MAX(salary)
FROM Employees
WHERE dept_id = e.dept_id
);
Q10. Display employees who do not have any manager
SELECT name
FROM Employees
WHERE manager_id IS NULL;
Q11. Find the 3rd youngest employee (based on hire_date)
SELECT hire_date, name
FROM Employees e1
WHERE 3 = (
SELECT COUNT(DISTINCT hire_date)
FROM Employees e2
WHERE e2.hire_date <= e1.hire_date
);
Q12. Write a query to fetch common employees from two tables
SELECT emp_id, name
FROM Employees
INTERSECT
SELECT emp_id, name
FROM Managers;
Q13. Retrieve the first 5 employees with highest salaries
SELECT name, salary
FROM Employees
ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;
Q14. Show employees who earn more than their department’s average salary
SELECT name, dept_id, salary
FROM Employees e
WHERE salary > (
SELECT AVG(salary)
FROM Employees
WHERE dept_id = e.dept_id
);
Q15. Find employees whose names start with ‘A’
SELECT name
FROM Employees
WHERE name LIKE 'A%';
