Wednesday, January 22, 2025

SQL Interview Questions


ebook - Mastering SQL: A Comprehensive Guide to Database Mastery

1. What is SQL?

Answer: SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases. It allows users to query, insert, update, and delete data.


2. What is the difference between SQL and MySQL?

Answer:

  • SQL is a standard query language used for interacting with databases, whereas MySQL is an open-source relational database management system (RDBMS) that uses SQL as its query language.

  • SQL is used to write queries, and MySQL is a software that executes those queries.


3. What is a Primary Key?

Answer: A Primary Key is a field or combination of fields in a table that uniquely identifies each record in the table. A primary key cannot contain NULL values and must have unique values for each record.


4. What is a Foreign Key?

Answer: A Foreign Key is a column (or set of columns) that establishes a link between the data in two tables. It refers to the Primary Key of another table, enforcing referential integrity.


5. What is a JOIN in SQL?

Answer: A JOIN is used to combine rows from two or more tables based on a related column between them. The common types of joins are:

  • INNER JOIN: Returns only matching rows between the tables.

  • LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matched rows from the right table.

  • RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matched rows from the left table.

  • FULL JOIN: Returns all rows when there is a match in either left or right table.


6. What is the difference between INNER JOIN and LEFT JOIN?

Answer:

  • INNER JOIN returns only the rows that have matching values in both tables.

  • LEFT JOIN returns all rows from the left table, along with matching rows from the right table. If there is no match, it will still include the left table's row with NULL for the right table's columns.


7. What are the different types of indexes in SQL?

Answer:

  • Unique Index: Ensures that all values in a column are unique.

  • Composite Index: An index on two or more columns.

  • Full-text Index: Allows efficient searching of text within string columns.

  • Clustered Index: Orders the data physically in the table based on the indexed column(s).


8. What is a Subquery?

Answer: A Subquery is a query nested inside another query. It is used to retrieve data that will be used by the outer query.

  • Types:

    • Single-row subquery: Returns a single value.

    • Multi-row subquery: Returns multiple values.

    • Correlated subquery: Refers to columns from the outer query.


9. What is normalization?

Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The process involves dividing large tables into smaller, more manageable ones and defining relationships between them. The common normal forms are:

  • 1NF (First Normal Form)

  • 2NF (Second Normal Form)

  • 3NF (Third Normal Form)


10. What is denormalization?

Answer: Denormalization is the process of combining tables to reduce the complexity of SQL queries. It is used to improve query performance by reducing the need for multiple joins at the expense of data redundancy.


11. What is the difference between HAVING and WHERE clauses?

Answer:

  • WHERE filters rows before grouping, and is used with non-aggregated data.

  • HAVING filters groups after the GROUP BY clause and is used with aggregated data.


12. What are aggregate functions in SQL?

Answer: Aggregate functions are used to perform calculations on multiple rows of a table’s column, and return a single value. Common aggregate functions include:

  • COUNT(): Counts rows.

  • SUM(): Sums the values.

  • AVG(): Finds the average value.

  • MIN(): Finds the minimum value.

  • MAX(): Finds the maximum value.


13. What is a GROUP BY clause?

Answer: The GROUP BY clause groups rows that have the same values into summary rows, like "total" or "average." It is often used with aggregate functions such as COUNT(), SUM(), AVG(), etc.


14. What is a UNION in SQL?

Answer: The UNION operator is used to combine the results of two or more SELECT queries into a single result set.

  • UNION removes duplicate rows.

  • UNION ALL includes duplicates.


15. What is the difference between TRUNCATE and DELETE?

Answer:

  • DELETE: Removes rows one at a time and logs each row deletion. It can be rolled back if within a transaction.

  • TRUNCATE: Removes all rows at once without logging individual row deletions. It cannot be rolled back.


16. What is the BETWEEN operator in SQL?

Answer: The BETWEEN operator is used to filter results within a range of values, inclusive of the boundary values.

Example:


SELECT * FROM employees WHERE salary BETWEEN 40000 AND 60000;



17. What are the different types of constraints in SQL?

Answer:

  • PRIMARY KEY: Uniquely identifies each record.

  • FOREIGN KEY: Enforces referential integrity between tables.

  • UNIQUE: Ensures all values in a column are unique.

  • CHECK: Ensures that all values in a column satisfy a condition.

  • DEFAULT: Provides a default value when no value is specified.

  • NOT NULL: Ensures that a column does not accept NULL values.


18. What is the LIKE operator in SQL?

Answer: The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

  • % represents zero or more characters.

  • _ represents a single character.

Example:

SELECT * FROM employees WHERE name LIKE 'J%';



19. What is a VIEW in SQL?

Answer: A VIEW is a virtual table created by a query that selects data from one or more tables. It simplifies complex queries and can be used for security purposes, limiting access to specific columns or rows.


20. What is an AUTO_INCREMENT in SQL?

Answer: AUTO_INCREMENT is used to automatically generate a unique number when a new record is inserted into a table. It is commonly used for primary keys.


21. What is the difference between CHAR and VARCHAR?

Answer:

  • CHAR: A fixed-length string data type. If a string is shorter than the defined length, it will be padded with spaces.

  • VARCHAR: A variable-length string data type. It only uses the amount of space needed for the string.


22. What is a Stored Procedure?

Answer: A Stored Procedure is a precompiled collection of one or more SQL statements that can be executed together. It is stored in the database and can be reused, making it efficient and reducing the need for repetitive SQL code.


23. What is ACID in SQL?

Answer: ACID stands for:

  • Atomicity: Ensures that all operations within a transaction are completed successfully.

  • Consistency: Ensures that the database is in a valid state before and after the transaction.

  • Isolation: Ensures that concurrent transactions do not interfere with each other.

  • Durability: Ensures that once a transaction is committed, it is permanent.


24. What is the difference between IN and EXISTS?

Answer:

  • IN: Checks if a value matches any value in a list or subquery.

  • EXISTS: Checks if a subquery returns any rows. It is often used for checking the existence of rows.


25. What are Triggers in SQL?

Answer: A Trigger is a stored procedure that automatically executes (or "fires") when certain events occur on a particular table or view, such as INSERT, UPDATE, or DELETE.


26. What is the ROW_NUMBER() function in SQL?

Answer: The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set, starting from 1 for the first row in each partition.


27. What is the difference between RANK() and DENSE_RANK()?

Answer:

  • RANK(): Assigns a rank to each row, with gaps in the rank values when there are ties.

  • DENSE_RANK(): Assigns a rank to each row without gaps in the rank values when there are ties.

28. What is the difference between IS NULL and IS NOT NULL?

Answer:

  • IS NULL: Checks if a column contains a NULL value.

  • IS NOT NULL: Checks if a column does not contain a NULL value.


29. What is the purpose of the COALESCE function?

Answer: The COALESCE function returns the first non-NULL value from a list of expressions. It is commonly used to handle NULL values in queries.

Example:

SELECT COALESCE(NULL, NULL, 'First Non-null Value');


30. What is a CASE statement in SQL?

Answer: The CASE statement allows you to perform conditional logic in a SQL query. It evaluates a set of conditions and returns a corresponding result.

Example:

SELECT
    name,
    CASE
        WHEN age >= 18 THEN 'Adult'
        ELSE 'Minor'
    END as age_group
FROM people;


31. What is the DISTINCT keyword used for in SQL?

Answer: The DISTINCT keyword is used to remove duplicate rows from the result set. It ensures that only unique values are returned.

Example:

SELECT DISTINCT country FROM customers;


32. What is the difference between TRUNCATE and DELETE in terms of performance?

Answer:

  • TRUNCATE is faster than DELETE because it does not log individual row deletions and does not scan the entire table row by row.

  • DELETE can be slower, especially with large datasets, because it removes rows one by one and logs each deletion.


33. What is the EXPLAIN keyword in SQL?

Answer: The EXPLAIN keyword provides information about how SQL queries are executed, including the query execution plan, indexes used, and the number of rows processed. It helps in query optimization.


34. What is a Self Join in SQL?

Answer: A Self Join is a join where a table is joined with itself. It is often used when a table contains hierarchical data, such as an employee-manager relationship.

Example:

SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;



35. What is the difference between TRIGGERS and STORED PROCEDURES?

Answer:

  • Triggers: Automatic actions that are executed in response to certain events (such as INSERT, UPDATE, or DELETE).

  • Stored Procedures: A set of SQL statements that can be executed explicitly by the user, not automatically.


36. What is a Normalized database?

Answer: A Normalized database is designed to reduce redundancy and dependency by organizing tables and their relationships in such a way that data is stored efficiently, ensuring that updates do not lead to anomalies.


37. What are Window Functions in SQL?

Answer: Window Functions perform calculations across a set of table rows that are related to the current row. Common window functions include ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), and LEAD().


38. What is the LIMIT clause used for in SQL?

Answer: The LIMIT clause is used to specify the maximum number of records to return in the result set. It is mostly used in queries when you want to restrict the number of rows fetched.

Example:

SELECT * FROM employees LIMIT 10;


39. What is the INTO keyword used for in SQL?

Answer: The INTO keyword is used to insert the results of a query into a new table or variable. It is used in SELECT INTO queries.

Example:

SELECT * INTO new_table FROM employees WHERE department = 'HR';


40. What is the REPLACE function in SQL?

Answer: The REPLACE function is used to replace all occurrences of a specified substring within a string with another substring.

Example:
SELECT REPLACE('Hello World', 'World', 'SQL');


41. What is SQL Injection and how can you prevent it?

Answer: SQL Injection is a security vulnerability that allows attackers to execute arbitrary SQL code on the database. It can be prevented by using:

  • Prepared Statements with parameterized queries.

  • Stored Procedures.

  • Escaping user inputs.

  • Using ORM (Object-Relational Mapping).


42. What are Common Table Expressions (CTE) in SQL?

Answer: A Common Table Expression (CTE) is a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It improves the readability and maintainability of complex queries.

Example:

WITH department_sales AS (
    SELECT department, SUM(sales) AS total_sales
    FROM employees
    GROUP BY department
)
SELECT * FROM department_sales;


43. What is the difference between CHAR and VARCHAR data types in SQL?

Answer:

  • CHAR is a fixed-length string data type, meaning it always stores the specified number of characters, padding with spaces if needed.

  • VARCHAR is a variable-length string data type, meaning it only uses the space required for the actual data.


44. What is a Transaction in SQL?

Answer: A Transaction is a unit of work that is performed in a database. It is typically a series of SQL queries that must be executed together. Transactions ensure data integrity using the ACID properties (Atomicity, Consistency, Isolation, Durability).


45. What is INDEX and why is it used in SQL?

Answer: An Index is a database object that improves the speed of data retrieval operations on a table. It is created on one or more columns of a table to quickly locate data without scanning the entire table.


46. Can you describe a scenario or business requirement where using a self-join would be necessary?

Answer: A self-join would be necessary in a scenario where you need to retrieve data that involves a relationship between rows within the same table. For example, when working with an employee table where each employee has a manager, you would need a self-join to display each employee along with their respective manager's name.

Example scenario:

An organization’s employee table contains the following columns: employee_id, employee_name, and manager_id (which references employee_id of their manager). A self-join would be used to match each employee to their manager.


47. What is the ALTER statement in SQL?

Answer: The ALTER statement is used to modify an existing database object, such as a table. It can be used to add, delete, or modify columns in a table.

Example:

ALTER TABLE employees ADD COLUMN department VARCHAR(50);


48. What is the difference between ROLLBACK and COMMIT in SQL?

Answer:

  • ROLLBACK: Undoes the changes made by a transaction. It reverts the database to its previous state.

  • COMMIT: Saves all changes made during the current transaction.


49. What is Data Integrity in SQL?

Answer: Data Integrity refers to the accuracy and consistency of data within a database. SQL uses constraints such as primary keys, foreign keys, unique constraints, and check constraints to enforce data integrity.


50. What is Schema in SQL?

Answer: A Schema is a collection of database objects, such as tables, views, indexes, and procedures. It defines the structure of the database.



No comments:

Search This Blog