MOST commonly asked SQL interview questions!

1. WHAT IS A TRIGGER? WHEN DO YOU USE THEM?

- A trigger is a database object that automatically performs an action when a certain event occurs in the database. Events could be INSERT, UPDATE, or DELETE operations on a table. Triggers are used to enforce business rules, data integrity, or to log changes.

2. WHAT IS A VIEW IN SQL? WHEN SHOULD YOU USE IT?

- A view is a virtual table based on the result of a SQL query. It presents data from one or more tables in a structured format. Views can simplify complex queries, provide a level of security by hiding certain columns or rows, and abstract away complexity for end-users.

3. WHAT IS A TRANSACTION? HOW DO YOU USE IT?

- A transaction is a sequence of SQL operations that are executed as a single unit. Transactions ensure data integrity by allowing multiple operations to either all succeed or all fail. They are used when you need to perform multiple related operations as a single logical unit, such as transferring funds between bank accounts.

4. WHAT IS AN INDEX?

- An index is a database structure that improves the speed of data retrieval operations on a table. It is created on one or more columns of a table and provides a quick lookup mechanism for finding rows matching a specific value or set of values.

5. DIFFERENCE BETWEEN PRIMARY KEY, FOREIGN KEY, UNIQUE KEY

- A primary key uniquely identifies each record in a table and ensures data integrity by enforcing uniqueness and providing a reference point for relationships.

- A foreign key establishes a relationship between two tables by referencing the primary key of another table. It ensures referential integrity and maintains consistency across related tables.

- A unique key ensures that all values in a column or set of columns are distinct or unique within a table. Unlike a primary key, a table can have multiple unique keys.

6. Difference between Relational DBMS vs Non-relational

- Relational database management systems (RDBMS) organize data into structured tables with predefined relationships between them, following the principles of the relational model.

- Non-relational databases (NoSQL) store and retrieve data in formats other than tabular relations, such as key-value pairs, document-based, columnar, or graph formats. NoSQL databases offer more flexibility but may sacrifice some of the ACID properties of RDBMS.

7. CLUSTERED INDEX VS NON-CLUSTERED INDEX

- A clustered index determines the physical order of data in a table, meaning the rows are stored on disk in the order of the index key. Each table can have only one clustered index.

- A non-clustered index is a separate structure from the actual table data and contains pointers to the corresponding table rows. A table can have multiple non-clustered indexes.

8. DIFFERENCE BETWEEN SQL FUNCTION AND STORED PROCEDURE

- SQL functions return a single value based on input parameters and are typically used within SQL statements. Stored procedures are a set of SQL statements that can perform multiple operations and can accept input parameters, and they may or may not return values.

9. AGGREGATE FUNCTION

- Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include SUM, AVG, COUNT, MAX, and MIN.

10. JOIN QUERY (INNER JOIN, OUTER JOIN, ETC.)

• Join queries are used to combine rows from two or more tables based on a related column between them. Inner joins return only the rows that match between the tables, while outer joins return all rows from one or both tables, with NULLs for unmatched rows. Other types of joins include left join, right join, and full outer join.