Basic SQL Questions

1. What is SQL?
• SQL stands for Structured Query Language, used to manage and manipulate relational databases.

2. What are the different types of SQL statements?
• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

3. What is a primary key?
• A primary key is a column (or a set of columns) that uniquely identifies each row in a table.

4. What is a foreign key?
• A foreign key is a column that creates a relationship between two tables by referring to the primary key of another table.

5. What is a join? Explain its types.
• A join is used to combine rows from two or more tables based on a related column.
• INNER JOIN: Returns only matching rows.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and matching rows from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and matching rows from the left table.
• FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in one of the tables.
• CROSS JOIN: Returns the Cartesian product of both tables.
• SELF JOIN: A table is joined with itself.

6. What is normalization?
• Normalization is the process of organizing data to minimize redundancy and improve data integrity.

7. What are the different normal forms?
• 1NF (First Normal Form): Ensures that each column contains atomic values, and each record is unique.
• 2NF (Second Normal Form): Achieves 1NF and removes partial dependency.
• 3NF (Third Normal Form): Achieves 2NF and removes transitive dependency.
• BCNF (Boyce-Codd Normal Form): A stronger version of 3NF.

8. What is a unique key?
• A unique key ensures all values in a column are unique, similar to a primary key, but it can have null values.

9. What is an index?
• An index is a database object that improves the speed of data retrieval operations on a table.

10. What is a view?
• A view is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table.

Intermediate SQL Questions

1. What is a stored procedure?
• A stored procedure is a prepared SQL code that you can save and reuse. It can accept parameters and execute multiple SQL statements.

2. What is a trigger?
• A trigger is a set of instructions that automatically executes (or “fires”) in response to certain events on a particular table or view.

3. Explain the difference between DELETE and TRUNCATE.
• DELETE removes specified rows from a table and can be rolled back.
• TRUNCATE removes all rows from a table, resets table identity, and cannot be rolled back.

4. What is a cursor?
• A cursor is a database object used to retrieve, manipulate, and navigate through a result set row by row.

5. What are aggregate functions? Name a few.
• Aggregate functions perform calculations on multiple rows and return a single value.
• Examples: COUNT(), SUM(), AVG(), MIN(), MAX()

6. What is a subquery?
• A subquery is a query within another query. It is used to return data that will be used in the main query.

7. What is the difference between WHERE and HAVING clause?
• WHERE is used to filter rows before any groupings are made.
• HAVING is used to filter groups after the GROUP BY clause.

8. Explain the use of GROUP BY clause.
• GROUP BY groups rows that have the same values in specified columns into summary rows, like “find the number of customers in each country.”

9. What is a composite key?
• A composite key is a primary key composed of multiple columns used to identify a unique row in a table.

10. What is a union?
• UNION combines the result sets of two or more SELECT statements (eliminates duplicates).

Advanced SQL Questions

1. What is the difference between UNION and UNION ALL?
• UNION removes duplicate records.
• UNION ALL includes duplicate records.

2. What is a Common Table Expression (CTE)?
• A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

3. Explain window functions in SQL.
• Window functions perform calculations across a set of table rows related to the current row, allowing you to use row-based calculations without collapsing rows.

4. What is the difference between RANK() and DENSE_RANK()?
• RANK() assigns a rank to each row within a partition, leaving gaps for tied ranks.
• DENSE_RANK() assigns ranks without gaps between tied ranks.

5. Explain the use of the PARTITION BY clause.
• PARTITION BY divides the result set into partitions and applies the window function to each partition.

6. What is database sharding?
• Sharding is a type of database partitioning that separates very large databases into smaller, faster, more easily managed parts called shards.

7. What is a materialized view?
• A materialized view is a database object that contains the results of a query. It is updated periodically based on the query definition.

8. Explain ACID properties in the context of transactions.
• ACID stands for Atomicity, Consistency, Isolation, Durability, ensuring reliable transaction processing in a database.

9. What is the difference between OLTP and OLAP?
• OLTP (Online Transaction Processing) is designed for managing transactional data.
• OLAP (Online Analytical Processing) is designed for analysis and query of large volumes of data.

10. What are the different types of indexes?
• Unique Index, Non-Unique Index, Clustered Index, Non-Clustered Index, Composite Index, Bitmap Index

SQL Performance Tuning Questions

1. What is SQL query optimization?
• SQL query optimization involves improving the performance of SQL queries to reduce their execution time and resource consumption.

2. How do you optimize SQL queries?
• Indexing, Avoiding unnecessary columns in SELECT, Using joins instead of subqueries, Analyzing execution plans, Avoiding wildcard (%) at the beginning of a LIKE pattern, Using WHERE instead of HAVING

3. What is an execution plan?
• An execution plan is a visual representation of the steps the database takes to execute a query, used for query performance analysis.

4. What is database indexing and why is it important?
• Indexing is a technique to improve the speed of data retrieval operations on a database table at the cost of additional writes and storage space.

5. What is the difference between a clustered and a non-clustered index?
• Clustered index sorts and stores the data rows of the table based on the key values. Each table can have only one clustered index.
• Non-clustered index creates a separate object within the table that points back to the original table rows after sorting.

6. What is query caching?
• Query caching stores the results of a query in memory, allowing subsequent executions of the same query to be retrieved quickly from the cache.

7. Explain database partitioning.
• Database partitioning involves dividing a database into smaller, more manageable pieces to improve performance and manageability.

8. What is the use of the EXPLAIN statement?
• EXPLAIN statement provides information about how MySQL executes statements, helping to analyze and optimize queries.

9. How does indexing affect INSERT operations?
• Indexing can slow down INSERT operations because the database has to update the index every time a row is inserted.

10. What is denormalization?
• Denormalization is the process of combining tables to reduce the number of joins and improve query performance, often at the expense of data redundancy.

Miscellaneous SQL Questions

1. What is a data warehouse?
• A data warehouse is a system used for reporting and data analysis, storing large volumes of historical data for querying and analysis.

2. Explain the difference between data mining and data warehousing.
• Data mining is the process of discovering patterns and knowledge from large amounts of data.
• Data warehousing is the process of compiling and organizing data into one common database.

3. What is ETL?
• ETL stands for Extract, Transform, Load, which are the three processes used to move data from one database to another.

4. What is a fact table?
• A fact table stores quantitative data for analysis and is often at the center of a star schema or snowflake schema in a data warehouse.

5. What is a dimension table?
• A dimension table stores attributes, or dimensions, that describe the objects in a fact table.

6. What is the difference between SQL and PL/SQL?
• SQL is a standard language for accessing and manipulating databases.
• PL/SQL (Procedural Language/SQL) is an extension of SQL used in Oracle databases to write full programs with loops, conditions, and variables.

7. What is referential integrity?
• Referential integrity ensures that relationships between tables remain consistent. When one table has a foreign key to another, referential integrity ensures that the key values always point to valid rows in the referenced table.

8. What is a surrogate key?
• A surrogate key is a unique identifier for an entity, often used as a primary key. It is not derived from application data.

9. Explain the difference between DELETE and DROP.
• DELETE removes rows from a table.
• DROP removes the entire table or database schema from the database.

10. What is a data lake?
• A data lake is a storage repository that holds a vast amount of raw data in its native format until it is needed for analysis.

Advanced SQL Questions Continued

1. What is the use of the COALESCE function?
• COALESCE returns the first non-null value in a list of arguments.

2. What is a recursive CTE?
• A recursive CTE is a common table expression that references itself, allowing recursive queries.

3. What is the difference between VARCHAR and CHAR?
• VARCHAR stores variable-length strings, while CHAR stores fixed-length strings.

4. What is the purpose of the SQL LIMIT clause?
• LIMIT is used to specify the number of records to return.

5. What are window functions and how do they differ from aggregate functions?
• Window functions perform calculations across a set of table rows related to the current row without collapsing the rows into a single output row like aggregate functions.

6. What is the difference between scalar and vector subqueries?
• Scalar subqueries return a single value, while vector subqueries return multiple values.

7. What is the use of the DISTINCT keyword in SQL?
• DISTINCT is used to remove duplicate rows from the result set.

8. What are the advantages of using stored procedures?
• Stored procedures improve performance, ensure code reusability, enhance security, and reduce network traffic.

9. Explain the difference between ROW_NUMBER(), RANK(), and DENSE_RANK().
• ROW_NUMBER() assigns a unique sequential integer to rows within a partition.
• RANK() assigns a rank with gaps for tied ranks.
• DENSE_RANK() assigns ranks without gaps for tied ranks.

10. What is the use of the CASE statement in SQL?
• CASE is used to implement conditional logic in SQL queries.