SQL Interview Questions for Freshers: A Comprehensive Guide

Structured Query Language (SQL) is the standard language for managing and manipulating databases. As companies continue to rely on data-driven decision-making, the demand for SQL skills is high. This blog provides freshers with a guide to common SQL interview questions, detailed answers, and examples to help you prepare effectively.

1. What is SQL?

Answer: SQL (Structured Query Language) is a domain-specific language used for managing and manipulating relational databases. It allows users to perform various operations, including querying data, updating records, inserting new data, and deleting existing data.

Example: A simple SQL query to select all records from a table named employees would look like this:

SELECT * FROM employees;

2. What are the different types of SQL statements?

Answer: SQL statements can be categorized into several types:

DDL (Data Definition Language): Used to define and manage all database objects. Examples include CREATE, ALTER, DROP.
DML (Data Manipulation Language): Used for manipulating data in existing database objects. Examples include SELECT, INSERT, UPDATE, DELETE.
DCL (Data Control Language): Used to control access to data in the database. Examples include GRANT, REVOKE.
TCL (Transaction Control Language): Used to manage transactions in the database. Examples include COMMIT, ROLLBACK, SAVEPOINT.

Example: To create a new table, you would use a DDL statement:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50)
);

3. What is the difference between `WHERE` and `HAVING` clauses?

Answer: The WHERE clause is used to filter records before any groupings are made, while the HAVING clause is used to filter records after groupings have been made.

WHERE: Filters rows based on specified conditions.
HAVING: Filters groups created by the GROUP BY clause based on aggregate functions.

Example:

SELECT position, COUNT(*) AS num_employees
FROM employees
WHERE position IS NOT NULL
GROUP BY position
HAVING COUNT(*) > 5;

In this example, the WHERE clause filters out any employees with a NULL position before grouping, and the HAVING clause filters groups that have more than 5 employees.

4. What is a primary key?

Answer: A primary key is a unique identifier for each record in a database table. It ensures that no two rows have the same value in the primary key column and cannot contain NULL values.

Example: In the employees table, the id column could be a primary key:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    position VARCHAR(50)
);

5. What is normalization, and why is it important?

Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and establishing relationships between them.

Importance:

Eliminates data redundancy.
Ensures data integrity and consistency.
Makes it easier to maintain and update data.

Example: Instead of storing employee details and their department details in a single table, you can create two tables: employees and departments, linking them with a foreign key.

6. What are joins in SQL? Explain different types of joins.

Answer: Joins are used to combine rows from two or more tables based on a related column between them.

Types of Joins:

INNER JOIN: Returns only the rows with matching values in both tables.
LEFT JOIN (LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table; NULLs for non-matching rows.
RIGHT JOIN (RIGHT OUTER JOIN): Returns all rows from the right table and matched rows from the left table; NULLs for non-matching rows.
FULL JOIN (FULL OUTER JOIN): Returns all rows when there is a match in either left or right table; NULLs for non-matching rows.

Example:

SELECT employees.name, departments.name AS department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

This query retrieves the names of employees and their corresponding department names using an INNER JOIN.

7. What are aggregate functions in SQL?

Answer: Aggregate functions perform calculations on multiple values to return a single value. Common aggregate functions include:

COUNT(): Returns the number of rows that match a specified criterion.
SUM(): Returns the total sum of a numeric column.
AVG(): Returns the average value of a numeric column.
MAX(): Returns the maximum value from a set of values.
MIN(): Returns the minimum value from a set of values.

Example:

SELECT AVG(salary) AS average_salary FROM employees;

This query calculates the average salary of all employees.

8. Explain the difference between `UNION` and `UNION ALL`.

Answer: Both UNION and UNION ALL are used to combine the results of two or more SELECT statements. However, there are key differences:

UNION: Removes duplicate rows from the result set.
UNION ALL: Includes all rows, including duplicates.

Example:

SELECT name FROM employees
UNION
SELECT name FROM contractors;  -- Removes duplicates

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;  -- Keeps duplicates

9. What is a foreign key?

Answer: A foreign key is a column or a set of columns in one table that uniquely identifies a row in another table. It establishes a relationship between the two tables, ensuring referential integrity.

Example:

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

In this example, the department_id in the employees table is a foreign key referencing the id column in the departments table.

10. What is indexing, and why is it important?

Answer: Indexing is a database optimization technique that improves the speed of data retrieval operations on a database table at the cost of additional space and slower write operations.

Importance:

Faster Query Performance: Helps quickly locate and access data.
Improved Sorting and Filtering: Enhances the performance of queries with sorting and filtering conditions.

Example:

CREATE INDEX idx_employee_name ON employees(name);

This query creates an index on the name column of the employees table, speeding up searches based on employee names.

Conclusion

This blog covered fundamental SQL interview questions that freshers may encounter. By understanding these concepts and practicing with real-world examples, you will be well-prepared to tackle SQL interview questions and demonstrate your knowledge effectively. Good luck with your interviews!

Additional Essential JavaScript Interview Questions on Various Topics