Scribd
Upload
26 views5 pages

ANSI SQL Interview Questions Guide

Uploaded by

royalminigaming
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
26 views5 pages

ANSI SQL Interview Questions Guide

Uploaded by

royalminigaming
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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 [Link], d.dept_name
FROM Employees e
INNER JOIN Departments d
ON e.dept_id = d.dept_id;

-- LEFT JOIN
SELECT [Link], d.dept_name
FROM Employees e
LEFT JOIN Departments d
ON e.dept_id = d.dept_id;

-- RIGHT JOIN
SELECT [Link], d.dept_name
FROM Employees e
RIGHT JOIN Departments d
ON e.dept_id = d.dept_id;

-- FULL JOIN
SELECT [Link], 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 [Link] >= [Link]
);

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%';

Common questions

Powered by AI

Aggregate functions in SQL, such as AVG(), COUNT(), MAX(), MIN(), and SUM(), are used to perform calculations on a set of values to return a single value. When used with the HAVING clause, they allow filtering of grouped results, unlike the WHERE clause which filters rows before grouping. An example is: SELECT dept_id, AVG(salary) AS avg_salary FROM Employees GROUP BY dept_id HAVING AVG(salary) > 45000;. This filters groups to show only departments with an average salary above 45000 .

To show employees who joined in the last 30 days, SQL uses date functions to calculate the date range. The query is: SELECT name, hire_date FROM Employees WHERE hire_date >= CURRENT_DATE - INTERVAL '30' DAY;. This query utilizes the 'CURRENT_DATE' function to get the current date and subtracts 30 days using 'INTERVAL', illustrating how date functions are critical for filtering data based on time criteria .

The SQL query to list employees who earn more than their department’s average salary is: SELECT name, dept_id, salary FROM Employees e WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = e.dept_id);. This query utilizes a correlated subquery to compare each employee’s salary to the average salary within their specific department, requiring an understanding of subquery scoping .

A RIGHT JOIN in SQL retrieves all records from the right table and the matched records from the left table. If there is no match, NULL values are returned for columns from the left table. It might be used over other joins when the complete set of records from the right table is required regardless of matching conditions, highlighting its utility when joining tables with potentially missing references in the left table .

To retrieve the second highest salary of employees, the SQL query uses a nested SELECT statement. One way to perform this query is by selecting the maximum salary that is less than the maximum salary of the employees, which is achieved using the query: SELECT MAX(salary) FROM Employees WHERE salary < (SELECT MAX(salary) FROM Employees);. This query demonstrates the use of nested queries to rank aggregate data .

To find employees with no department assigned, the query looks for rows where the 'dept_id' field is NULL. This can be accomplished with the following SQL statement: SELECT name FROM Employees WHERE dept_id IS NULL;. This query leverages the understanding of NULL representation in SQL database systems to filter records that have an undefined or missing department id .

Retrieving the highest salary from each department involves grouping the data by department and then using the MAX() function. The SQL query is: SELECT dept_id, MAX(salary) AS highest_salary FROM Employees GROUP BY dept_id;. Here, 'GROUP BY' is used to organize data into dept_id subsets before aggregation functions are applied, which calculates the maximum salary per department .

Constraints in SQL are rules applied to table columns to ensure data integrity. During table creation, constraints such as PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY provide mechanisms to guarantee that data adheres to specific rules. For example, the CHECK constraint ensures numerical data stays within specified bounds and the UNIQUE constraint avoids duplicate entries in a column. These constraints form the backbone of reliable data management by preventing invalid data entry and maintaining relational integrity across tables .

Correlated subqueries differ from simple subqueries as they reference columns from the outer query, which means they are evaluated row by row. In contrast, simple subqueries are standalone and do not rely on the outer query. For example, a correlated subquery is used to find employees with salaries higher than the average salary of their department: SELECT name FROM Employees e WHERE salary > (SELECT AVG(salary) FROM Employees WHERE dept_id = e.dept_id);. Here, the subquery depends on 'dept_id' from the outer query .

To identify duplicate values in a dataset, SQL can use the GROUP BY clause in conjunction with HAVING COUNT() > 1 to filter out groups with duplicate entries: SELECT salary, COUNT(*) FROM Employees GROUP BY salary HAVING COUNT(*) > 1;. Handling duplicates is important to ensure data accuracy and consistency, especially in analysis where duplicates might distort results. Techniques like deduplication, either programmatically or manually, are essential for maintaining data quality .

You might also like