SQL Survival Kit: 15 Interview Questions Every Fresher Should Know

SQL plays a critical role in launching your career in tech and data-related fields. Employers value SQL expertise, especially for roles like data analyst or database administrator, where working with databases is essential.

You can stand out by mastering common SQL coding interview questions. Many candidates struggle with SQL queries during interviews, but strong skills can help you solve real-world problems and impress hiring managers.

By preparing for sql technical interview questions for freshers and practicing them with AI interview assistant, you’ll gain the confidence to excel in any data analyst interview.

Key Takeaways

• SQL skills are important for tech and data jobs. Learning SQL can help you get better jobs and higher pay.

• Knowing basic SQL ideas, like primary keys and normalization, is key for managing databases and doing well in interviews.

• Practice writing SQL queries often. Knowing commands like SELECT, JOIN, and GROUP BY will help with common interview questions.

• Use subqueries and stored procedures to make hard SQL tasks easier. These tools improve your problem-solving in real-life situations.

Basic SQL Concepts

What is SQL?

SQL, or Structured Query Language, is the standard language used to interact with relational databases. It allows you to retrieve, manipulate, and manage data efficiently. Whether you're extracting user behavior data or analyzing trends, SQL serves as the backbone for data-driven decision-making.

Industry experts emphasize the importance of understanding SQL for entry-level roles in data management and analysis. It remains the most popular language among data scientists and engineers, even with the emergence of NoSQL technologies.

What is the difference between a primary key and a foreign key in SQL?

Primary keys and foreign keys are fundamental concepts in database design. A primary key uniquely identifies each record in a table. It ensures that no duplicate entries exist and every record is easily accessible.

A foreign key, on the other hand, establishes a relationship between two tables. It references the primary key in another table, creating a link that maintains data integrity. For instance, in an employee database:

• The "EmployeeID" column in the "Employees" table can serve as a primary key.

• The "DepartmentID" column in the "Departments" table can act as a foreign key, connecting employees to their respective departments.

What is normalization, and how does it improve database design?

Normalization is the process of organizing data in a database to reduce redundancy and improve efficiency. It involves dividing large tables into smaller, related tables and defining relationships between them.

This technique enhances database performance and ensures data consistency. For example, normalization reduces anomalies during data updates and minimizes duplication.

By applying normalization, you create a database structure that is easier to maintain and scale. This approach is particularly useful for handling large datasets in industries like healthcare and finance.

What are the main types of SQL commands (DDL, DML, DCL, TCL)?

SQL commands fall into four main categories, each serving a specific purpose in database management. Understanding these categories helps you perform tasks like creating, modifying, and securing databases effectively.

1. DDL (Data Definition Language)
   DDL commands define and modify the structure of a database. These commands allow you to create, alter, or delete database objects like tables and indexes. Common DDL commands include:

   • CREATE: Used to create new database objects.

   • ALTER: Modifies existing database structures.

   • DROP: Deletes database objects permanently.

   For example, to create a table for storing employee data, you can use the following SQL command:

   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       Name VARCHAR(50),
       Department VARCHAR(50)
   );

2. DML (Data Manipulation Language)
   DML commands handle the data within database tables. These commands let you insert, update, delete, or retrieve data. Key DML commands include:

   • INSERT: Adds new records to a table.

   • UPDATE: Modifies existing records.

   • DELETE: Removes records from a table.

   For instance, to retrieve all employees from the "Employees" table, you would use:

   SELECT * FROM Employees;

3. DCL (Data Control Language)
   DCL commands manage access to the database. They help you control who can view or modify data. Common DCL commands include:

   • GRANT: Provides specific permissions to users.

   • REVOKE: Removes permissions from users.

4. TCL (Transaction Control Language)
   TCL commands manage database transactions, ensuring data consistency. These commands are crucial for maintaining the integrity of operations. Key TCL commands include:

   • COMMIT: Saves changes made during a transaction.

   • ROLLBACK: Reverts changes if an error occurs.

What is a database schema, and why is it important?

A database schema serves as the blueprint for your database. It defines how data is organized and specifies the relationships between different tables. Think of it as the architecture that ensures your database operates efficiently.

• A schema organizes data logically, making it easier to retrieve and analyze.

• It enforces constraints, which maintain data integrity and improve performance.

• Proper schema design supports scalability, allowing your database to grow with future needs.

SQL Query Writing

How to write a basic SELECT query in SQL?

The SELECT statement is the foundation of SQL queries. It allows you to retrieve data from one or more tables in a database. You specify the columns you want to fetch and the table where the data resides.

Here’s a simple example:

SELECT Name, Age FROM Employees;

This query retrieves the "Name" and "Age" columns from the "Employees" table.

When writing SELECT queries:

• Always specify the columns you need instead of using SELECT *. This improves performance and clarity.

• Use aliases to rename columns for better readability. For example:

  SELECT Name AS EmployeeName, Age AS EmployeeAge FROM Employees;

How to use the WHERE clause for filtering data?

The WHERE clause refines your query results by applying conditions. It helps you retrieve only the rows that meet specific criteria.

For example, to find employees older than 30:

SELECT Name, Age FROM Employees WHERE Age > 30;

You can combine multiple conditions using logical operators like AND, OR, and NOT:

• AND: Filters rows that meet all conditions.

  SELECT Name FROM Employees WHERE Age > 30 AND Department = 'Sales';

• OR: Filters rows that meet at least one condition.

  SELECT Name FROM Employees WHERE Age > 30 OR Department = 'Sales';

• NOT: Excludes rows that meet a condition.

  SELECT Name FROM Employees WHERE NOT Department = 'HR';

What are SQL JOINs, and what are the different types of JOINs?

JOINs combine rows from two or more tables based on a related column. They are crucial for working with relational databases, where data is spread across multiple tables.

The main types of joins in SQL include:

1. INNER JOIN: Returns rows with matching values in both tables.

   SELECT Employees.Name, Departments.DepartmentName  
   FROM Employees  
   INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right table. Missing matches are filled with NULL.

   SELECT Employees.Name, Departments.DepartmentName  
   FROM Employees  
   LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left table. Missing matches are filled with NULL.

   SELECT Employees.Name, Departments.DepartmentName  
   FROM Employees  
   RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

4. FULL JOIN (or FULL OUTER JOIN): Combines rows from both tables, including unmatched rows from each table.

   SELECT Employees.Name, Departments.DepartmentName  
   FROM Employees  
   FULL JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

How to use GROUP BY and HAVING clauses in SQL queries?

The GROUP BY clause organizes rows into groups based on shared values in specified columns. It’s essential for summarizing data and works seamlessly with aggregate functions like COUNT, SUM, AVG, MAX, and MIN. For example, if you want to count the number of employees in each department, you can use the following query:

SELECT Department, COUNT(*) AS EmployeeCount  
FROM Employees  
GROUP BY Department;

This query groups rows by the "Department" column and calculates the total number of employees in each group.

The HAVING clause filters these groups based on conditions applied to aggregated data. Unlike the WHERE clause, which filters individual rows, HAVING works on grouped data. For instance, to find departments with more than 10 employees, you can write:

SELECT Department, COUNT(*) AS EmployeeCount  
FROM Employees  
GROUP BY Department  
HAVING COUNT(*) > 10;

This query first groups rows by department, calculates the employee count for each group, and then filters out groups with fewer than 10 employees.

Here are some advantages of using GROUP BY and HAVING clauses:

• GROUP BY Clause:

  • Groups rows with identical values.

  • Summarizes data efficiently.

  • Works with aggregate functions to compute statistics.

• HAVING Clause:

  • Filters groups based on aggregate conditions.

  • Enables calculations on summarized data.

  • Useful for refining complex queries.

Both clauses complement each other. GROUP BY organizes data into meaningful groups, while HAVING refines the results further. Together, they help you craft efficient SQL queries that answer specific business questions.

How to sort data using the ORDER BY clause in SQL?

The ORDER BY clause arranges query results in ascending or descending order based on one or more columns. Sorting data makes it easier to analyze trends and identify patterns. For example, to list employees by their salaries in descending order, you can use:

SELECT Name, Salary  
FROM Employees  
ORDER BY Salary DESC;

This query retrieves employee names and salaries, sorting them from highest to lowest salary.

You can sort by multiple columns to refine the order further. For instance, to sort employees by department and then by salary within each department, write:

SELECT Name, Department, Salary  
FROM Employees  
ORDER BY Department ASC, Salary DESC;

This query first arranges employees alphabetically by department and then sorts salaries in descending order within each department.

Here are some key points about the ORDER BY clause:

• It defaults to ascending order unless you specify DESC for descending order.

• You can sort by column names, aliases, or even expressions.

• It’s versatile and works with numeric, text, and date data types.

Practical Problem-Solving

How to find the second highest salary in a table using SQL?

Finding the second highest salary is a common challenge in SQL practical interview questions. It tests your ability to write efficient queries for real-world scenarios. You can solve this problem using advanced SQL techniques like Common Table Expressions (CTE) or ranking functions such as ROW_NUMBER() and DENSE_RANK().

Here’s an example using DENSE_RANK() to find the second highest salary:

WITH RankedSalaries AS (
    SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
    FROM Employees
)
SELECT Salary
FROM RankedSalaries
WHERE Rank = 2;

This query ranks salaries in descending order and retrieves the one with a rank of 2. These techniques simplify salary analysis and provide insights into pay structures, which are valuable for HR and financial planning.

How to count the number of employees in each department using SQL?

Counting employees by department is a fundamental SQL skill. It’s often used in technical assessments to evaluate your ability to group and summarize data. You can achieve this using the GROUP BY clause along with the COUNT() function.

Here’s an example query:

SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

This query groups employees by department and calculates the total count for each group. The results might look like this:

This method is widely used in business intelligence to monitor workforce distribution and optimize resource allocation.

How to retrieve duplicate records from a table in SQL?

Identifying duplicate records is crucial for maintaining data quality. SQL provides powerful tools like ranking functions to detect duplicates. For example, you can use the DENSE_RANK() function to identify duplicate rows based on specific columns.

Here’s a query to find duplicates in an "Employees" table:

SELECT Name, COUNT(*) AS DuplicateCount
FROM Employees
GROUP BY Name
HAVING COUNT(*) > 1;

This query groups rows by the "Name" column and filters out groups with only one record. Deduplication improves data accuracy, which is essential for effective data management.

How to delete duplicate rows from a table in SQL?

Duplicate rows can clutter your database and lead to inaccurate results during analysis. Removing these duplicates is a common task in SQL practical interview questions. You can achieve this by using the ROW_NUMBER() function along with a Common Table Expression (CTE).

Here’s an example:

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name, Department ORDER BY EmployeeID) AS RowNum
    FROM Employees
)
DELETE FROM Employees
WHERE EmployeeID IN (
    SELECT EmployeeID FROM CTE WHERE RowNum > 1
);

This query assigns a unique row number to each duplicate group based on the "Name" and "Department" columns. It then deletes all rows except the first one in each group.

Alternatively, you can use a temporary table to remove duplicates:

CREATE TABLE TempEmployees AS
SELECT DISTINCT * FROM Employees;

DELETE FROM Employees;

INSERT INTO Employees
SELECT * FROM TempEmployees;

DROP TABLE TempEmployees;

This method creates a new table with unique rows, clears the original table, and reinserts the cleaned data.

How to find records that exist in one table but not in another using SQL?

You may need to identify records present in one table but missing from another. This is a common scenario when comparing datasets or validating data integrity. SQL provides the LEFT JOIN and NOT EXISTS methods to solve this problem.

Using LEFT JOIN:

SELECT A.*
FROM TableA A
LEFT JOIN TableB B ON A.ID = B.ID
WHERE B.ID IS NULL;

This query retrieves rows from "TableA" where no matching "ID" exists in "TableB".

Using NOT EXISTS:

SELECT *
FROM TableA A
WHERE NOT EXISTS (
    SELECT 1
    FROM TableB B
    WHERE A.ID = B.ID
);

This approach checks for the absence of matching rows in "TableB". It is often more efficient for large datasets.

Both methods are useful for tasks like identifying missing transactions or validating data migrations.

Advanced Topics

What are indexes in SQL, and how do they improve query performance?

Indexes in SQL act like a roadmap for your database, helping you locate data quickly without scanning the entire table. They optimize query performance by creating shortcuts for data retrieval. For example, when searching for a specific customer ID in a table with millions of records, an index can reduce execution time significantly.

Indexes are especially useful for:

• Columns frequently used in WHERE clauses or JOIN operations.

• Sorting data with ORDER BY clauses.

• Aggregating data with GROUP BY clauses.

Here’s a table that highlights scenarios where indexes improve performance:

While indexes speed up SELECT queries, they can slow down INSERT, UPDATE, and DELETE operations. To maximize their benefits, consider these tips:

1. Use indexes for columns with a wide range of values, like product IDs.

2. Avoid indexing columns with many NULL values.

3. Store nonclustered indexes on a separate disk from the table filegroup for better performance.

What are ACID properties, and why are they important in SQL databases?

ACID properties ensure that your database transactions are processed reliably and maintain data integrity. These principles—Atomicity, Consistency, Isolation, and Durability—are the backbone of SQL databases.

Here’s a table summarizing these properties:

What is a subquery in SQL, and how is it used?

A subquery in SQL is a query nested inside another query. It allows you to perform complex operations by breaking them into smaller, manageable parts. Subqueries are often used to filter data, calculate values, or retrieve information from related tables.

For example, to find employees earning more than the average salary, you can use a subquery:

SELECT Name, Salary  
FROM Employees  
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

This query calculates the average salary using the subquery and filters employees based on that value.

Subqueries can appear in:

• SELECT statements: Retrieve calculated values.

• WHERE clauses: Filter data based on conditions.

• FROM clauses: Create temporary tables for further analysis.

What is the difference between correlated and non-correlated subqueries in SQL?

Correlated and non-correlated subqueries serve different purposes in SQL, and understanding their differences can help you write efficient queries.

A correlated subquery depends on the outer query for its execution. It runs repeatedly for each row processed by the outer query. For example, if you want to find employees whose salaries exceed the average salary of their department, you can use a correlated subquery:

SELECT Name, Salary  
FROM Employees E  
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = E.DepartmentID);

This subquery references the "DepartmentID" column from the outer query, making it correlated.

A non-correlated subquery, on the other hand, operates independently of the outer query. It executes once and provides a result that the outer query uses. For instance, to find employees earning more than the company’s average salary, you can write:

SELECT Name, Salary  
FROM Employees  
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Here, the subquery calculates the average salary without relying on any column from the outer query.

When comparing these subqueries, consider their performance and use cases. Correlated subqueries can simplify filtering and aggregation but often perform poorly on large datasets due to repeated execution. Non-correlated subqueries are faster and better suited for scenarios where the subquery result remains constant.

What is a stored procedure in SQL, and how does it differ from a function?

A stored procedure in SQL is a precompiled set of SQL statements that you can execute as a single unit. It simplifies repetitive tasks, such as inserting data or updating records. Stored procedures improve performance because the database compiles them once and reuses the execution plan.

For example, a stored procedure to update employee salaries might look like this:

CREATE PROCEDURE UpdateSalary  
    @DepartmentID INT,  
    @PercentageIncrease FLOAT  
AS  
BEGIN  
    UPDATE Employees  
    SET Salary = Salary * (1 + @PercentageIncrease)  
    WHERE DepartmentID = @DepartmentID;  
END;

You can call this procedure whenever you need to update salaries for a specific department.

A function in SQL, however, performs calculations and returns a single value or a table. Unlike stored procedures, functions cannot modify database objects. For instance, a function to calculate the average salary might look like this:

CREATE FUNCTION GetAverageSalary()  
RETURNS FLOAT  
AS  
BEGIN  
    RETURN (SELECT AVG(Salary) FROM Employees);  
END;

You can use this function in queries to retrieve the average salary.

Here’s how stored procedures differ from functions:

• Purpose: Stored procedures handle tasks like data manipulation, while functions focus on calculations.

• Return Type: Procedures may return multiple values or none, whereas functions always return a single value or table.

• Usage: Procedures execute independently, while functions integrate into queries.

FAQ

1. How can you practice SQL effectively for interviews?

Focus on solving real-world problems. Use platforms like LeetCode or HackerRank to practice SQL challenges. Write queries for sample datasets and analyze their results. Use an AI interview assistant to build your confidence and improve your problem-solving skills.

2. What is the best way to learn SQL for beginners?

Start with basic concepts like SELECT, WHERE, and JOIN clauses. Use free online tutorials or courses. Experiment with small datasets to understand how queries work. Gradually move to advanced topics like subqueries and stored procedures.

3. Can SQL help you in non-technical roles?

Yes, SQL is valuable for roles like marketing and business analysis. It helps you extract insights from data, track trends, and make informed decisions. Learning SQL enhances your ability to work with data-driven tools like Excel and Tableau.

4. How do you debug SQL queries during interviews?

Break the query into smaller parts. Test each part individually to identify errors. Use tools like SQL Server Management Studio or MySQL Workbench to visualize query results. Debugging shows your analytical skills and attention to detail.

5. Is SQL knowledge enough to secure a data analyst role?

SQL is essential but not sufficient. Combine it with skills like Excel, Python, or R for data analysis. Learn visualization tools like Power BI or Tableau. Employers value candidates who can analyze and present data effectively.

See Also

Freshers' Guide to Java Interview Success: 20 Must-Know Questions