SQL (Structured Query Language) is a critical skill for any developer, database administrator, or data analyst. Interviewers frequently assess SQL proficiency through both theoretical questions and practical problem-solving tasks. This blog covers the most commonly asked SQL interview questions with answers and examples, ensuring you’re fully prepared for your interview. By reading this post, you’ll gain the knowledge and confidence needed to excel in your SQL interviews.
Answer:SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows users to:
Retrieve data from databases (using queries).
Insert, update, and delete records.
Create and modify database structures.
Control access to the database.
In modern-day applications, SQL plays a vital role in handling vast amounts of structured data efficiently.
Answer:SQL commands are grouped into five categories:
DDL (Data Definition Language): Used to define and manage database schema.
Commands: CREATE, ALTER, DROP, TRUNCATE
DML (Data Manipulation Language): Used to manage data within tables.
Commands: SELECT, INSERT, UPDATE, DELETE
DCL (Data Control Language): Used for granting and revoking database permissions.
Commands: GRANT, REVOKE
TCL (Transaction Control Language): Used to manage transactions.
Commands: COMMIT, ROLLBACK, SAVEPOINT
DQL (Data Query Language): Used to query data.
Command: SELECT
Knowing these categories ensures clarity when writing SQL scripts and understanding their functionalities.
Answer:A primary key is a column (or a set of columns) that uniquely identifies each row in a table. Primary keys must have the following properties:
Unique values.
Non-null values.
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(50),
department VARCHAR(50)
);Note: A table cannot have multiple primary keys but can have a composite primary key (combination of two or more columns).
Primary keys play a crucial role in maintaining data integrity and establishing relationships between tables.
WHERE and HAVING?Answer:
WHERE: Filters rows before grouping is applied.
HAVING: Filters groups after the grouping is applied.
Example:
-- Using WHERE to filter rows before grouping
SELECT department, COUNT(*) AS total_employees
FROM employees
WHERE department != 'HR'
GROUP BY department;
-- Using HAVING to filter groups
SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;This distinction is critical for writing accurate and efficient SQL queries.
INNER JOIN and OUTER JOIN?Answer:
INNER JOIN: Returns rows with matching values in both tables.
OUTER JOIN: Returns all rows from one or both tables, with NULL in non-matching rows.
Example:
-- INNER JOIN
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;
-- LEFT OUTER JOIN
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;Understanding joins is essential for combining data from multiple tables effectively.
Answer:A foreign key is a column (or set of columns) in one table that refers to the primary key in another table. It establishes a relationship between the two tables and ensures referential integrity.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);Foreign keys prevent invalid data from being entered into a related table and maintain consistency.
DELETE and TRUNCATE?Answer:
DELETE: Deletes specific rows from a table and can include a WHERE clause. It is slower and logged in the transaction log.
TRUNCATE: Removes all rows from a table without logging individual row deletions. Faster than DELETE but does not allow filtering.
Example:
-- DELETE with a condition
DELETE FROM employees WHERE department = 'HR';
-- TRUNCATE removes all rows
TRUNCATE TABLE employees;Choosing between these commands depends on whether you need to retain control over deletions or prioritize performance.
Answer:Indexing improves the speed of data retrieval operations on a database by creating a data structure for faster search.
Example:
CREATE INDEX idx_employee_name
ON employees (name);Proper indexing can significantly boost query performance, especially for large datasets.
Answer:Normalization organizes data in a database to reduce redundancy and improve integrity. Types:
1NF (First Normal Form): Ensures atomic values and uniqueness.
2NF (Second Normal Form): Removes partial dependencies.
3NF (Third Normal Form): Removes transitive dependencies.
BCNF (Boyce-Codd Normal Form): Handles more complex dependencies.
Normalization ensures that databases are efficient, maintainable, and free of anomalies.
Answer:Aggregate functions perform a calculation on a set of values and return a single value.
Examples:
COUNT(): Counts rows.
SUM(): Adds values.
AVG(): Calculates average.
MIN(): Finds the smallest value.
MAX(): Finds the largest value.
Example:
SELECT COUNT(*) AS total_employees, AVG(salary) AS average_salary
FROM employees;Using aggregate functions simplifies analysis and reporting tasks.
Answer:Use the GROUP BY and HAVING clauses to identify duplicates.
Example:
SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;Identifying duplicates is essential for maintaining data integrity and accuracy.
Answer:A subquery is a query nested inside another query.
Example:
-- Find employees with salaries above the average salary
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);Subqueries enable more dynamic and powerful query building.
UNION and UNION ALL?Answer:
UNION: Combines results from two queries and removes duplicates.
UNION ALL: Combines results from two queries without removing duplicates.
Example:
-- UNION removes duplicates
SELECT name FROM employees
UNION
SELECT name FROM managers;
-- UNION ALL keeps duplicates
SELECT name FROM employees
UNION ALL
SELECT name FROM managers;Understanding these operators helps in combining data effectively based on requirements.
Answer:
Use indexing for frequently queried columns.
Avoid SELECT *; specify only the required columns.
Use joins instead of subqueries.
Optimize WHERE conditions.
Analyze query execution plans.
Query optimization ensures efficient database performance, particularly for high-traffic applications.
This comprehensive guide covers all the important SQL interview questions, ensuring you don’t need to look elsewhere. With clear explanations and examples, you can confidently tackle SQL interviews. SQL proficiency is an indispensable skill in the tech industry, and thorough preparation can set you apart from other candidates. Bookmark this page, practice the examples, and elevate your SQL expertise to excel in your interviews!