Top 20 Questions of Structure Query Language(SQL)

SQL (Structured Query Language) is the standard language used to interact with relational databases. It allows users to create, retrieve, update, and delete data using simple, declarative commands. SQL supports data manipulation (DML), data definition (DDL), and data control (DCL) operations. It is widely used in applications, data analysis, and backend development due to its efficiency and simplicity. Popular databases like MySQL, PostgreSQL, Oracle, and SQL Server all use SQL.

Below are the Top 20 Questions For the Interview Preparation:-


1. What is the difference between DBMS and RDBMS?
Answer:
DBMS stores data in files or simple structures, and doesn't support relationships among data.
RDBMS stores data in tables and uses keys to relate data across tables.
It also enforces ACID properties for reliability and supports complex queries using SQL.

2. What is normalization? Why is it important?
Answer:
Normalization is the process of organizing data to minimize redundancy and dependency.
It breaks large tables into smaller ones and links them using relationships.
It ensures efficient storage and helps maintain data integrity.

3.      What are the different normal forms?
Answer:

The different normal forms are as follows:-

·         1NF: Eliminate repeating groups; ensure atomic values.

·          2NF: Remove partial dependencies (on part of a composite key).

·         3NF: Eliminate transitive dependencies between non-prime attributes.

·         BCNF: Every determinant must be a candidate key (stricter 3NF).

·         4NF: Remove multi-valued dependencies; no multiple independent facts in one table.

·         5NF: Eliminate join dependencies; ensure lossless decomposition of relations.

4.      What is a primary key

Answer:

A primary key is a column or group of columns that uniquely identify a row in a table. It cannot have NULL or duplicate values. Only one primary key is allowed per table, but it can be composite.

5. What is a foreign key?
    Answer:
    A foreign key is a field that refers to the primary key in another table.
    It enforces referential integrity by ensuring that the value exists in the referenced table.
    It allows data across multiple tables to remain consistent.

6.  What is the difference between WHERE and HAVING clauses?
    Answer:
    WHERE is used to filter rows before grouping (used with SELECT).
    HAVING is used to filter groups after aggregation (used with GROUP BY).
    You can't use aggregate functions like COUNT or SUM in WHERE.

7.      What are joins? Name their types.
Answer:
Joins are used to retrieve data from multiple tables based on logical relationships.
Types:

1. INNER JOIN: common records
2. LEFT JOIN: all from left + matched right
3. RIGHT JOIN: all from right + matched left
4. FULL JOIN: all records with matches or NULLs
   
   

8.      What is indexing in a database?
Answer:
Indexing creates a data structure that allows fast retrieval of records based on key values.
It's like a table of contents in a book.
However, it increases write time and consumes extra storage.

9.      What are ACID properties?
Answer:

1. Atomicity: All operations complete or none.
2. Consistency: Data remains valid after transaction.
3. Isolation: Transactions don’t interfere.
4. Durability: Once committed, changes persist even after failure.
   
   

10.  What is a view in SQL?
Answer:
A view is a virtual table based on a SELECT query.
It simplifies complex queries and provides a layer of abstraction.
Views do not store data themselves and can be used for security and data hiding.

11.  What is a transaction in DBMS?
Answer:
A transaction is a sequence of SQL operations that form a single logical unit.
It follows ACID properties and can be committed (saved) or rolled back (undone).
Common SQL: BEGIN, COMMIT, ROLLBACK.

12.  What is SQL injection?
Answer:
SQL injection is a security vulnerability where attackers inject malicious SQL via input fields.
It can allow unauthorized data access or manipulation.
Prevent it by using prepared statements and input validation.

13.  What is the difference between DELETE, TRUNCATE, and DROP?
Answer:

• DELETE: It removes specific rows can be rolled back.
• TRUNCATE: It removes all rows quickly can't be rolled back.
• DROP: It deletes the table and its structure permanently.

14.  What is a candidate key?
Answer:
A candidate key is a column, or set of columns, that can uniquely identify each record.
There can be multiple candidate keys in a table.
One of them is chosen as the primary key.

15. What is the difference between CHAR and VARCHAR?
    Answer:

• CHAR: It is in fixed-length, always uses n characters (pads with spaces).
• VARCHAR: It is of variable-length, uses only required space.
  VARCHAR It is more space-efficient for variable data.

16. What is a composite key?
    Answer:
    A composite key is a primary key made up of two or more columns.
    Together, they uniquely identify a record.
    Useful when a single column can't ensure uniqueness.
    
    

17.  What is a stored procedure?
Answer:
A stored procedure is a saved SQL block that performs specific tasks.
It can accept parameters and return results.
It improves performance and reusability and supports conditional logic.

18.  What is a trigger in DBMS?
Answer:
A trigger is a set of SQL statements that automatically executes when a specific database event occurs.
It works on INSERT, UPDATE, or DELETE operations.
Triggers are useful for enforcing rules or logging actions.

19.  What is a schema in a database?
Answer:
A schema is the blueprint or structure of a database.
It defines tables, fields, relationships, views, indexes, and other elements.
It's like a container for database objects.

20.  Write SQL to find the second highest salary from an Employee table.
Answer:

               SELECT MAX(salary) FROM Employee 

               WHERE salary < (SELECT MAX(salary) FROM Employee);

This query finds the highest salary that is less than the maximum salary — i.e., second highest.