Top 50 SQL Interview Questions and Answers (Beginner to Intermediate)

Technical SQL Interview Questions for Beginners

Let’s move on to technical questions that test your foundational SQL knowledge.

Tip : When answering technical questions, aim for clear, precise responses. Avoid going off-topic or overexplaining, as it can invite follow-up questions in areas you're less confident about. Stick to what you know well and use examples wherever possible to back up your understanding.

11. What is SQL and why is it used?

SQL (Structured Query Language) is a standardized language used to manage, query and manipulate relational databases. It allows you to retrieve, insert, update and delete data stored in tables. SQL is essential for tasks such as generating reports, handling transactional data, managing large datasets and integrating with backend applications.

12. What are SQL Dialects?

Different database vendors implement their own versions of SQL, known as dialects. While the core syntax remains consistent, each dialect may include unique extensions.

Common SQL Dialects are MySQL, PostgreSQL, Microsoft SQL Server (T-SQL), Oracle (PL/SQL), SQLite.

13. What are the different types of SQL statements?

SQL statements can be grouped into several categories

• DDL (Data Definition Language) : Defines database structure (e.g., CREATE, ALTER, DROP)
• DML (Data Manipulation Language) : Manages data within tables (e.g., INSERT, UPDATE, DELETE)
• DQL (Data Query Language) : Used to fetch data (SELECT)
• DCL (Data Control Language) : Controls access (e.g., GRANT, REVOKE)
• TCL (Transaction Control Language) : Manages transactions (e.g., COMMIT, ROLLBACK, SAVEPOINT)

Understanding the purpose of each category helps clarify how SQL is used across database operations.

14 . What is the difference between WHERE and HAVING?

• WHERE is used to filter rows before aggregation.
• HAVING is used to filter groups after aggregation.

SELECT department, COUNT(*) 
FROM employees 
WHERE status = 'active' 
GROUP BY department 
HAVING COUNT(*) > 10;

In this example, WHERE filters only active employees before grouping, and HAVING ensures each group has more than 10 people.

Tip : Don't use HAVING when WHERE is sufficient. HAVING is more performance-intensive.

15. What are SQL joins? Explain different types.

Joins are used to combine rows from two or more tables based on a related column. The common types include

• INNER JOIN: Returns records that match in both tables
• LEFT JOIN: Returns all rows from the left table and matching ones from the right
• RIGHT JOIN: Returns all rows from the right table and matching ones from the left
• FULL OUTER JOIN / FULL JOIN : Returns all records from both tables, matching rows where possible. If there’s no match, the result will still include the row, with NULL values for the missing side.
• CROSS JOIN : Returns the Cartesian product of two tables, i.e. all combinations of rows.
• SELF JOIN : A table is joined with itself.

Knowing how and when to use each join is critical for building accurate queries in multi-table databases.

16. What is a primary key?

A primary key is a column (or a combination of columns) that uniquely identifies each row in a table. It must be unique and cannot contain NULL values. Every table should have one primary key to ensure data integrity and allow proper relationships between tables.

17. What is a UNIQUE Key in SQL?

A UNIQUE key is a constraint in SQL that ensures all values in a column (or combination of columns) are distinct, meaning no duplicate values are allowed in that column.

Unlike a primary key, a table can have multiple UNIQUE keys, but only one primary key. Also, while a primary key cannot contain NULLs, a UNIQUE key can allow a single NULL value (depending on the database system, like MySQL or PostgreSQL).

18. What is the different between Primary Key and Unique Key?

A Primary Key is a column (or combination of columns) that uniquely identifies each row in a table and does not allow NULL values. A Unique Key also enforces uniqueness across rows but allows one NULL value and is typically used for enforcing alternate or secondary unique constraints.

CREATE TABLE Employees (
  emp_id INT PRIMARY KEY,          -- uniquely identifies each employee
  email VARCHAR(100) UNIQUE,       -- email must be unique, NULL allowed
  phone VARCHAR(15) UNIQUE         -- phone must be unique, NULL allowed
);

Key differences between Primary Key and Unique Key

23. What are aggregate functions in SQL?

Aggregate functions perform calculations on a set of values and return a single result. Common ones include

• COUNT() – returns the number of rows
• SUM() – adds up values
• AVG() – returns the average
• MAX() / MIN() – find the highest or lowest value

These are often used with GROUP BY to generate grouped summaries

24. What is a subquery?

A subquery is a query nested inside another SQL query. It can be used in SELECT, FROM or WHERE clauses to retrieve or filter data based on dynamic conditions. Subqueries can be

• Correlated – refers to outer query values and runs once per row
• Non-correlated – runs independently and returns a single result or set

25. Explain Non-Correlated Subquery

A non-correlated subquery executes independently of the outer query. It runs once and its result is used by the outer query.

Example : Find employees whose salary is higher than the average salary.

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

• Here, the inner query calculates the average salary of all employees.
• The outer query fetches the names and salaries of employees earning above average.
• The subquery does not depend on each row of the outer query.

26. Explain Correlated Subquery

A correlated subquery depends on the outer query for its values. It executes once for each row processed by the outer query.

Example : Find employees who earn more than the average salary in their own department.

SELECT e1.name, e1.department, e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department
);

• The subquery calculates the average salary per department.
• For each employee in the outer query (e1), the subquery (e2) calculates the department's average salary.
• This is a correlated subquery because the inner query refers to e1.department from the outer query.

27. What are the key applications of SQL?

Some key applications of SQL

• Data Retrieval – Extract data using SELECT queries.
• Data Modification – Add, update or delete records with INSERT, UPDATE and DELETE.
• Database Management – Create or alter tables and schemas using DDL commands.
• Access Control – Grant or revoke user permissions with GRANT and REVOKE
• Transaction Handling – Ensure data integrity with COMMIT, ROLLBACK etc.
• Data Analysis – Perform aggregations and reports using functions like SUM(), GROUP BY.
• ETL and Migration – Used in data cleaning, transformation and system migrations.

28. What is a database?

A database is an organized collection of data that is stored and managed electronically. It allows data to be easily accessed, managed, updated and retrieved when needed.

29. What are Key Features of a Database?

• Data Storage : Databases store large volumes of structured data efficiently.
• Data Retrieval : You can quickly search and fetch data using queries like SQL.
• Data Manipulation : Supports adding, updating and deleting data easily.
• Data Integrity : Ensures accuracy and consistency using constraints like PRIMARY KEY, FOREIGN KEY etc.
• Security : Databases offer access control, encryption and authentication to protect data.
• Concurrency Control : Allows multiple users to access data at the same time without conflicts.
• Backup and Recovery : Enables automatic backups and restores in case of failure or loss.
• Data Independence : You can change the structure of data (schema) without affecting the application logic.
• ACID Compliance : Guarantees reliable transactions with Atomicity, Consistency, Isolation and Durability.
• Scalability : Databases can scale up to handle more users or data volume.

Example Use Case : An e-commerce app uses a database to manage products, users, orders and payments securely and efficiently.

30. What is DBMS?

A DBMS (Database Management System) is software that creates, manages and interacts with databases. It provides tools and interfaces that allow users and applications to store, retrieve, update and delete data in a structured and secure way.

In simple terms, a DBMS acts as a middle layer between the user and the database, handling all the behind-the-scenes operations.

31. What is RDBMS?

An RDBMS (Relational Database Management System) is a type of DBMS that stores data in tabular form using rows and columns and manages relationships between those tables using keys (like primary keys and foreign keys).

In an RDBMS, each table is related to others through relational integrity rules, allowing complex queries and structured data management.

32. What are the differences Between DBMS and RDBMS

Both DBMS (Database Management System) and RDBMS (Relational Database Management System) are systems designed to manage data efficiently, but they differ significantly in terms of structure, functionality and use cases.

Note : All RDBMS are DBMS, but ❌ not all DBMS are RDBMS.

33. What is a Table in SQL?

A table is a collection of data organized into rows and columns. Each table represents a specific entity, such as customers, products, employees or orders.

Think of a table like an Excel sheet, each row is a record and each column is a type of information.

34. What is a Field in SQL?

A field is a column in a table. It defines a single type of data that will be stored for every row in that table. Fields = Columns -> define what kind of data will be stored in each record.

For example in the Customers table, columns would be CustomerId ,Name, Email etc

35. How to Create a Table in SQL?

To create a table in SQL, you use the CREATE TABLE statement. It defines the table name, the columns (fields) and their respective data types.

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

36. What are constraints in SQL and why use them?

Constraints enforce rules on the data in a table to ensure accuracy and integrity. Common constraints include

• PRIMARY KEY: Uniquely identifies each row
• FOREIGN KEY: Maintains referential integrity
• NOT NULL: Prevents null values
• UNIQUE: Prevents duplicate values
• CHECK: Validates conditions
• DEFAULT: Sets a default value if none is provided

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE NOT NULL
);

38. Why to use Indexes?

Indexes make data access faster and more efficient. Without an index, SQL has to scan the entire table row by row (called a full table scan) to find matching data. With an index, it can jump directly to the required rows, making queries much faster.

39. What are key Benefits of Using Indexes

• Faster Data Retrieval : Speeds up SELECT queries, especially on large tables.
• Improved Performance : Enhances read operations in applications, reducing response time.
• Efficient Sorting and Filtering : Helps in ORDER BY, GROUP BY and WHERE clause queries.
• Better Use of Joins : Speeds up joins by indexing foreign and primary keys.
• Enforcing Uniqueness : Unique indexes prevent duplicate entries in a column (like email).

40. What is Trade-off using Indexs?

While indexes improve read/query speed, they come with some trade-offs

• Slower Write Operations : INSERT, UPDATE and DELETE take longer because indexes need to be updated too.
• Increased Storage : Indexes consume additional disk space.
• Maintenance Overhead : More indexes = more effort to manage and keep them optimized.
• Diminishing Returns : Too many indexes can confuse the query planner and hurt performance.

Use indexes only where needed - on columns often used in WHERE, JOIN or ORDER BY. Blindly indexing every column can hurt performance and increase storage usage.

42. How to Create Indexes in SQL?

CREATE TABLE Customers (
    CustomerId INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    INDEX idx_lastname (LastName)
);

CREATE INDEX idx_email
ON Customers (Email);

43. What all different types of indexes in SQL

45. What is a view in SQL?

A view is a virtual table based on the result of a query. It simplifies complex joins and restricts data access.

CREATE VIEW active_users AS
SELECT name, email FROM users WHERE is_active = 1;

46. What is denormalization?

Denormalization is the reverse of normalization, adding redundant data to reduce the complexity of joins and speed up reads. It's often used in read-heavy systems like reporting or analytics databases.

47. What is a SQL operator?

A SQL operator is a symbol or keyword used in SQL queries to perform actions like comparisons, calculations or filtering. SQL Operator are commonly used with the WHERE clause to define conditions.

48. What is an Alias?

An alias is a temporary name given to a column or table in a query to make results cleaner or improve readability.

SELECT first_name AS name
FROM employees;

SELECT e.name, d.name
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;

49. What is a Clause in SQL?

A clause is a keyword or condition used to define how SQL queries should behave , especially for filtering or structuring results.

Common clauses include

• WHERE – filter rows
• ORDER BY – sort results
• GROUP BY – group data
• HAVING – filter groups
• LIMIT – limit rows

50. How to delete a table from a database?

To delete a table and all its data permanently, use the DROP TABLE statement.

DROP TABLE table_name;