SQL Interview Questions and Answers (Level-1)

---

1. What is SQL?

Answer:
SQL stands for Structured Query Language, a standard programming language used for managing and manipulating relational databases. It is widely used in data-driven applications to perform operations such as:

• Creating databases and tables
  
• Inserting, updating, and deleting records
  
• Querying data using SELECT statements
  
• Managing access to data using permissions
  

SQL is supported by popular RDBMS systems like MySQL, Oracle, SQL Server, and PostgreSQL.

---

2. What are the different types of SQL statements?

Answer:
SQL supports various types of statements, categorized as:

• DDL (Data Definition Language): Used to define database structure.
  
  • Examples: CREATE, ALTER, DROP
    
• DML (Data Manipulation Language): Used to manipulate data.
  
  • Examples: SELECT, INSERT, UPDATE, DELETE
    
• DCL (Data Control Language): Used to control access.
  
  • Examples: GRANT, REVOKE
    
• TCL (Transaction Control Language): Used to manage transactions.
  
  • Examples: COMMIT, ROLLBACK, SAVEPOINT
    

These statements are the foundation for managing SQL databases.

---

3. What is DBMS?

Answer:
DBMS (Database Management System) is a software application that manages data and allows users to perform operations such as storing, retrieving, updating, and deleting data.

• It acts as an interface between the user and the database.
  
• Traditional DBMS saves data in files, unlike RDBMS which uses tables.
  

Examples: Microsoft Access, MySQL (as DBMS), dBase, etc.

---

4. What is RDBMS?

Answer:
RDBMS (Relational Database Management System) is an advanced version of DBMS that stores data in the form of related tables.

Key features:

• Data is stored in rows and columns.
  
• Relationships are created using foreign keys.
  
• Ensures data integrity and supports SQL queries.
  

Example: Oracle, SQL Server, MySQL, PostgreSQL

---

5. Why do we use SQL constraints? What are the types of constraints in SQL?

Answer:
SQL constraints are rules applied to columns in a table to ensure the accuracy and integrity of data.

Common SQL Constraints:

1. NOT NULL – Ensures a column cannot have NULL values.
   
2. UNIQUE – Ensures all values in a column are different.
   
3. PRIMARY KEY – A combination of NOT NULL and UNIQUE. Uniquely identifies each row.
   
4. FOREIGN KEY – Enforces referential integrity between two tables.
   
5. CHECK – Ensures values in a column satisfy a specific condition.
   

Constraints can be applied during:

• Table creation (CREATE TABLE)
  
• After creation (ALTER TABLE)

---

6. What are the different types of JOINS in SQL?

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

Types of SQL Joins:

• INNER JOIN: Returns records that have matching values in both tables.
  
• LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table, and matched records from the right table.
  
• RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table, and matched records from the left table.
  
• FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either left or right table.
  
• CROSS JOIN: Returns the Cartesian product of both tables.
  

---

7. What is normalization in SQL?

Answer:
Normalization is the process of organizing data to minimize redundancy and improve data integrity.

Goals of Normalization:

• Eliminate duplicate data
  
• Ensure data dependencies are logical
  
• Make database efficient and easy to maintain
  

---

8. What are the different normal forms in SQL?

Answer:
SQL Normalization is carried out through Normal Forms (NFs):

1. 1NF (First Normal Form):
   
   • Remove duplicate columns.
     
   • Each column must have atomic (indivisible) values.
     
2. 2NF (Second Normal Form):
   
   • Should be in 1NF.
     
   • Remove partial dependency.
     
3. 3NF (Third Normal Form):
   
   • Should be in 2NF.
     
   • Remove transitive dependency.
     
4. 4NF (Fourth Normal Form):
   
   • Should be in 3NF.
     
   • Remove multivalued dependency.
     

---

9. What are Aggregate Functions in SQL?

Answer:
SQL Aggregate Functions perform calculations on a set of values and return a single result.

List of SQL Aggregate Functions:

• AVG() – Returns average value.
  
• COUNT() – Returns number of rows.
  
• MAX() – Returns highest value.
  
• MIN() – Returns lowest value.
  
• SUM() – Returns total value.
  
• FIRST() – Returns the first record value.
  
• LAST() – Returns the last record value.
  

They are commonly used with GROUP BY and HAVING clauses.

---

10. What is an Index in SQL? What are the types of Indexes?

Answer:
An index in SQL is a performance optimization feature that helps speed up the retrieval of records from a table.

Types of SQL Indexes:

1. Clustered Index:
   
   • Alters the physical order of data in the table.
     
   • Only one clustered index per table.
     
   • Faster for range queries.
     
2. Non-Clustered Index:
   
   • Maintains a logical order, not physical.
     
   • Allows multiple indexes per table (up to 999).
     
   • Suitable for lookup operations.
     

Indexes are automatically created when a PRIMARY KEY or UNIQUE constraint is defined.

11. What is SQL Injection?

Answer:
SQL Injection is a code injection technique used by attackers to insert malicious SQL queries into input fields, potentially accessing or manipulating a database. This vulnerability mainly targets data-driven applications where user inputs are not properly sanitized.

Why It’s Dangerous:

• Unauthorized access to data (e.g., user credentials, credit card info)
  
• Modifying or deleting data
  
• Gaining administrative rights
  

Example of SQL Injection:

sql

SELECT * FROM users WHERE username = ‘admin’ –‘ AND password = ‘123’;

Here, the — comments out the rest of the query, allowing access without a password.

---

12. What is the difference between Primary Key and Unique Key in SQL?

Answer:

Feature	Primary Key	Unique Key
Number allowed	Only one per table	Multiple Unique Keys per table
NULLs allowed	Not allowed	One NULL value allowed
Index type	Clustered Index (default)	Non-Clustered Index (default)
Auto Increment	Supports Auto Increment	Doesn’t support Auto Increment

Primary Key ensures entity integrity, while Unique Key ensures uniqueness of data where duplication isn’t allowed except for NULLs.

---

13. What is ISNULL() in SQL Server?

Answer:
The ISNULL() function is used to check if a value is NULL, and if it is, replace it with a specified value.

Syntax:

sql

ISNULL(expression, replacement_value)

Example:

sql

SELECT ISNULL(NULL, ‘N/A’); — Output: N/A

Useful in reports and dashboards to avoid NULL displays.

---

14. What are Magic Tables in SQL Server?

Answer:
Magic Tables are virtual tables automatically created by SQL Server when DML triggers (INSERT, DELETE, UPDATE) are fired. These are not actual tables but can be referenced within a trigger.

Usage:

• INSERTED table contains new values for INSERT or UPDATE
  
• DELETED table contains old values for DELETE or UPDATE
  

Example: Used inside a TRIGGER to log audit data or enforce business rules.

---

15. What is a Cursor in SQL?

Answer:
A Cursor is a database object used to iterate over result sets row-by-row. It acts like a pointer and is useful when row-level processing is needed.

Types:

• Static
  
• Dynamic
  
• Forward-only
  
• Keyset-driven
  

Basic Cursor Example:

sql

DECLARE cursor_name CURSOR FOR

SELECT name FROM employees;

OPEN cursor_name;

FETCH NEXT FROM cursor_name;

…

CLOSE cursor_name;

DEALLOCATE cursor_name;

❗Use cursors only when necessary due to performance cost.

---

16. How to change the database name in SQL Server?

Answer:

You can rename a SQL Server database using the following steps:

🔹 Using SQL Command:

sql

ALTER DATABASE old_db_name MODIFY NAME = new_db_name;

🔹 Pre-requisites:

• Ensure the database is not in use.
  
• Set it to SINGLE_USER mode if needed.
  

---

17. What is Referential Integrity?

Answer:
Referential Integrity ensures data consistency between two related tables using Primary and Foreign Keys. It prevents orphan records.

Example: If Orders table has a CustomerID referencing Customers table:

• You cannot delete a customer with existing orders.
  
• Foreign key ensures valid reference.
  

---

18. How are exceptions handled in SQL Server programming?

Answer:
SQL Server handles exceptions using TRY…CATCH blocks.

Example:

sql

BEGIN TRY

   — SQL code that might fail

   INSERT INTO Orders VALUES (‘InvalidData’);

END TRY

BEGIN CATCH

   PRINT ‘Error Occurred: ‘ + ERROR_MESSAGE();

END CATCH

Use for error logging, transaction rollback, and handling constraint violations.

---

19. What is an Execution Plan and how is it used?

Answer:
An Execution Plan shows the step-by-step process SQL Server uses to execute a query. It’s helpful for identifying performance bottlenecks and optimizing queries.

Use Cases:

• Analyzing slow queries
  
• Detecting missing indexes
  
• Understanding join strategy
  

View Plan:

• Enable “Include Actual Execution Plan” in SQL Server Management Studio (SSMS).
  
• Use shortcut Ctrl + M before running the query.
  

---

20. How to implement one-to-one, one-to-many, and many-to-many relationships in SQL?

Answer:

Relationship	Implementation
One-to-One	Use two tables; reference via unique foreign key
One-to-Many	Parent table primary key → Child table foreign key
Many-to-Many	Use junction table with foreign keys from both related tables

Example of Many-to-Many:

sql

CREATE TABLE Student_Course (

    StudentID INT,

    CourseID INT,

    PRIMARY KEY (StudentID, CourseID),

    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),

    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

);

The Next 15 Questions-I: SQL