Frequently Asked SQL Interview Questions ~ Interview Questions and Answers

Here are the answers to your SQL-related questions:

General Questions

What is SQL, and what are its main functions?
- Answer: SQL (Structured Query Language) is a standardized programming language used for managing and manipulating relational databases. Its main functions include:
  - Data Querying: Retrieving data from databases using the SELECT statement.
  - Data Manipulation: Inserting, updating, and deleting data (using INSERT, UPDATE, DELETE).
  - Data Definition: Defining database structures (using CREATE, ALTER, DROP).
  - Data Control: Controlling access to data (using GRANT, REVOKE).
Explain the difference between SQL and NoSQL databases.
- Answer: SQL databases are relational and use structured query language for defining and manipulating data. They require a fixed schema and support ACID transactions. NoSQL databases, on the other hand, are non-relational and can handle unstructured or semi-structured data. They offer flexible schemas, horizontal scaling, and are often used for big data and real-time web applications.
What are the different types of SQL commands? Provide examples.
- Answer: SQL commands can be categorized into:
  - DDL (Data Definition Language): Used to define database structures. Examples: CREATE TABLE, ALTER TABLE.
  - DML (Data Manipulation Language): Used to manipulate data. Examples: SELECT, INSERT, UPDATE, DELETE.
  - DCL (Data Control Language): Used to control access to data. Examples: GRANT, REVOKE.
  - TCL (Transaction Control Language): Used to manage transactions. Examples: COMMIT, ROLLBACK.
What is the purpose of the SELECT statement?
- Answer: The SELECT statement is used to query and retrieve data from one or more tables in a database. It allows users to specify which columns to retrieve, the tables to query, and conditions for filtering the results.
What is the difference between INNER JOIN and OUTER JOIN?
- Answer:
  - INNER JOIN: Returns records that have matching values in both tables. If there's no match, the record is not included in the results.
  - OUTER JOIN: Returns all records from one table and the matched records from the other table. If there's no match, NULL values are returned for columns from the table without a match. There are three types: LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

Data Retrieval

How do you retrieve unique values from a column in SQL?
- Answer: Use the DISTINCT keyword.
```
SELECT DISTINCT column_name FROM table_name;
```
What is the purpose of the GROUP BY clause?
- Answer: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, allowing aggregate functions like COUNT, SUM, AVG, etc., to be applied to each group.
Explain how the HAVING clause is different from the WHERE clause.
- Answer: The WHERE clause filters rows before aggregation, while the HAVING clause filters groups after aggregation. HAVING is often used with aggregate functions.
What are aggregate functions? Provide examples.
- Answer: Aggregate functions perform calculations on a set of values and return a single value. Examples include:
  - COUNT(): Counts the number of rows.
  - SUM(): Calculates the sum of a numeric column.
  - AVG(): Computes the average of a numeric column.
  - MIN(): Finds the minimum value.
  - MAX(): Finds the maximum value.

How do you sort results using SQL?

Answer: Use the ORDER BY clause to sort results.


SELECT * FROM table_name ORDER BY column_name ASC;  -- for ascending
SELECT * FROM table_name ORDER BY column_name DESC; -- for descending

Filtering and Conditions

How do you use the LIKE operator in SQL?

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


SELECT * FROM table_name WHERE column_name LIKE 'pattern%';  -- % is a wildcard for any sequence of characters

What is the difference between NULL and an empty string in SQL?
- Answer: NULL represents the absence of a value, whereas an empty string is a valid value that represents a string with zero length. NULL indicates "unknown," while an empty string is known but has no characters.
How do you handle NULL values in SQL queries?
- Answer: Use the IS NULL or IS NOT NULL operators to filter NULL values.
```
SELECT * FROM table_name WHERE column_name IS NULL;
```

Explain the purpose of the CASE statement in SQL.

Answer: The CASE statement is used to perform conditional logic in SQL queries. It allows you to return different values based on specified conditions.


SELECT column_name,
       CASE
           WHEN condition1 THEN result1
           WHEN condition2 THEN result2
           ELSE result_default
       END AS alias_name
FROM table_name;

What is a subquery, and when would you use it?
- Answer: A subquery is a query nested inside another SQL query. It's used when you need to retrieve data that will be used in the main query, such as filtering based on a value from another table.
```
SELECT * FROM table_name WHERE column_name = (SELECT MAX(column_name) FROM another_table);
```

Data Manipulation

How do you insert data into a table using SQL?
- Answer: Use the INSERT INTO statement.
```
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
```
What is the syntax for updating existing records in SQL?
- Answer: Use the UPDATE statement.
```
UPDATE table_name SET column1 = value1 WHERE condition;
```
How do you delete records from a table?
- Answer: Use the DELETE statement.
```
DELETE FROM table_name WHERE condition;
```
Explain the use of the TRUNCATE command.
- Answer: The TRUNCATE command is used to remove all records from a table quickly without logging individual row deletions. It resets any identity columns to their seed value.
```
TRUNCATE TABLE table_name;
```
What is the difference between DELETE and TRUNCATE?
- Answer:
  - DELETE: Removes rows one at a time and logs each deletion, which can be rolled back.
  - TRUNCATE: Removes all rows without logging individual deletions, cannot be rolled back in most systems, and resets identity columns.

Database Design

What are primary keys and foreign keys in SQL?
- Answer:
  - Primary Key: A unique identifier for each record in a table. It cannot contain NULL values.
  - Foreign Key: A field in one table that uniquely identifies a row of another table, creating a relationship between the two tables.
Explain the concept of normalization and denormalization.
- Answer:
  - Normalization: The process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller tables and defining relationships.
  - Denormalization: The process of combining tables to improve read performance by reducing the need for joins, often at the cost of increased redundancy.
What are the different normal forms in database design?
- Answer: The normal forms are:
  - First Normal Form (1NF): Ensures that all columns contain atomic values and each record is unique.
  - Second Normal Form (2NF): Ensures all non-key attributes are fully functionally dependent on the primary key.
  - Third Normal Form (3NF): Ensures that all attributes are not only functionally dependent on the primary key but also independent of other non-key attributes.

How do you create a table in SQL? Provide an example.

Answer: Use the CREATE TABLE statement.


CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    DepartmentID INT
);

What is a composite key?
- Answer: A composite key is a primary key that consists of two or more columns used together to uniquely identify a record in a table.

Transactions and Concurrency

What is a transaction in SQL, and why is it important?
- Answer: A transaction is a sequence of one or more SQL operations treated as a single unit of work. It is important because it ensures data integrity by following the ACID properties (Atomicity, Consistency, Isolation, Durability).
Explain the ACID properties of transactions.
- Answer:
  - Atomicity: Ensures that all operations in a transaction are completed; if one fails, the entire transaction fails.
  - Consistency: Ensures that a transaction takes the database from one valid state to another valid state.
  - Isolation: Ensures that concurrent transactions do not interfere with each other.
  - Durability: Ensures that once a transaction is committed, it remains so, even in the event of a system failure.
How do you manage transactions in SQL?
- Answer: Use the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements to manage transactions.
```
BEGIN TRANSACTION;
-- SQL statements
COMMIT; -- or ROLLBACK; if there is an error
```
What is a deadlock, and how can you prevent it?
- Answer: A deadlock occurs when two or more transactions block each other, preventing each from proceeding. Prevention methods include:
  - Proper transaction design to avoid circular dependencies.
  - Using timeout settings for transactions.
  - Implementing consistent locking order across transactions.
How do isolation levels affect transactions?
- Answer: Isolation levels determine how transaction integrity is visible to other transactions. Common levels include:
  - READ UNCOMMITTED: Allows dirty reads.
  - READ COMMITTED: Prevents dirty reads; only committed data is read.
  - REPEATABLE READ: Prevents dirty and non-repeatable reads.
  - SERIALIZABLE: The strictest level; ensures complete isolation from other transactions.

Indexing and Performance

What is an index in SQL, and why is it used?
- Answer: An index is a database object that improves the speed of data retrieval operations on a table by providing a quick way to look up data. It is similar to an index in a book.
Explain the difference between clustered and non-clustered indexes.
- Answer:
  - Clustered Index: Determines the physical order of data in a table; there can only be one clustered index per table.
  - Non-Clustered Index: Creates a separate structure from the data that contains a pointer back to the data rows; multiple non-clustered indexes can exist on a table.
How do you create an index in SQL?
- Answer: Use the CREATE INDEX statement.
```
CREATE INDEX idx_column_name ON table_name(column_name);
```
What is the impact of indexing on database performance?
- Answer: Indexing improves read performance and speeds up query execution but can slow down write operations (INSERT, UPDATE, DELETE) because the index needs to be updated. It also consumes additional disk space.
How do you optimize SQL queries for better performance?
- Answer: To optimize SQL queries:
  - Use proper indexing.
  - Avoid SELECT *; only select necessary columns.
  - Write efficient JOINs.
  - Filter data as early as possible using WHERE clauses.
  - Analyze query execution plans to identify bottlenecks.

Stored Procedures and Functions

What is a stored procedure, and how does it differ from a function?
- Answer: A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. Unlike functions, stored procedures do not return a value and can perform operations such as modifying database state.
How do you create a stored procedure in SQL?
- Answer: Use the CREATE PROCEDURE statement.
```
CREATE PROCEDURE ProcedureName
AS
BEGIN
    -- SQL statements
END;
```
Explain the use of parameters in stored procedures.
- Answer: Parameters allow you to pass values into stored procedures, making them more flexible. They can be defined as input (IN), output (OUT), or both (INOUT).
```
CREATE PROCEDURE ProcedureName @Param1 INT
AS
BEGIN
    -- SQL statements using @Param1
END;
```
What are triggers, and how are they used in SQL?
- Answer: A trigger is a special type of stored procedure that automatically executes in response to certain events on a particular table (e.g., INSERT, UPDATE, DELETE). They are used to enforce business rules or maintain data integrity.
How do you handle errors in stored procedures?
- Answer: Use TRY...CATCH blocks in SQL Server to handle exceptions.
```
BEGIN TRY
    -- SQL statements
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH;
```

Advanced Topics

What is a view in SQL, and why would you use one?
- Answer: A view is a virtual table based on the result set of a SELECT query. It simplifies complex queries, provides a layer of security by restricting access to certain data, and can aggregate data from multiple tables.
Explain the concept of a CTE (Common Table Expression).
- Answer: A CTE is a temporary result set defined within the execution scope of a single SQL statement, making it easier to organize complex queries. It is defined using the WITH clause.
```
WITH CTE_Name AS (
    SELECT column_name FROM table_name
)
SELECT * FROM CTE_Name;
```
What are window functions, and how do they differ from regular aggregate functions?
- Answer: Window functions perform calculations across a set of rows related to the current row. Unlike aggregate functions that return a single value for a group, window functions return a value for each row while maintaining access to the individual row's data.
```
SELECT column_name, SUM(column_name) OVER (PARTITION BY another_column) AS WindowSum
FROM table_name;
```
How do you implement row-level security in SQL?
- Answer: Row-level security (RLS) can be implemented using security policies that restrict which rows can be returned based on the user executing the query. This feature is available in some databases like SQL Server.
```
CREATE SECURITY POLICY PolicyName
ADD FILTER PREDICATE PredicateName ON table_name;
```
What is a materialized view, and how does it differ from a regular view?
- Answer: A materialized view is a database object that contains the results of a query and is stored on disk. It requires manual refreshing to update the data. In contrast, a regular view is a virtual table that does not store data and reflects the current state of the underlying tables.

Miscellaneous

How can you import and export data using SQL?
- Answer: Data can be imported and exported using various tools and commands, such as:
  - BULK INSERT: For importing data from a file.
  - BCP: A command-line utility for bulk copy.
  - SQL Server Management Studio (SSMS) import/export wizard.
  - INSERT INTO ... SELECT: To copy data between tables.
Explain the concept of database sharding.
- Answer: Database sharding is a method of distributing data across multiple databases (shards) to improve performance and scalability. Each shard holds a portion of the data, reducing the load on any single database server.
What are SQL injection attacks, and how can you prevent them?
- Answer: SQL injection is a code injection technique where an attacker can execute arbitrary SQL code on a database by inserting malicious SQL statements into input fields. Prevention methods include:
  - Using parameterized queries or prepared statements.
  - Validating and sanitizing user inputs.
  - Limiting database permissions.
How do you perform backup and recovery in SQL?
- Answer: Backup and recovery are performed using commands like:
  - Backup: BACKUP DATABASE database_name TO DISK = 'path_to_backup_file';
  - Restore: RESTORE DATABASE database_name FROM DISK = 'path_to_backup_file'; Backup schedules and strategies can be configured to meet specific recovery point objectives (RPO).
What tools and frameworks can be used for database management and development?
- Answer: Common tools and frameworks include:
  - Database Management Systems: MySQL, PostgreSQL, SQL Server, Oracle.
  - ORM Frameworks: Entity Framework, Dapper, Hibernate.
  - Development Tools: SQL Server Management Studio (SSMS), pgAdmin, MySQL Workbench, Azure Data Studio.

These answers cover essential SQL concepts and practices that can help you in interviews or when working with SQL databases!

Interview Questions and Answers

Friday, 1 November 2024

Frequently Asked SQL Interview Questions