SQL

Question 1

What are aggregate functions in SQL?

Answer 1

Aggregate functions perform calculations on a set of values and return a single value. Common aggregate functions include:

• COUNT(): Returns the number of rows.
• SUM(): Returns the total sum of values.
• AVG(): Returns the average of values.
• MIN(): Returns the smallest value.
• MAX(): Returns the largest value.

Question 2

What is a subquery?

Answer 2

Asubqueryis a query nested within another query. It is often used in theWHERE clauseto filter data based on the results of another query, making it easier to handle complex conditions.

Question 3

What is the difference between the WHERE and HAVING clauses?

Answer 3

• WHERE:Filters rows before any grouping takes place.
• HAVING:Filters grouped data after the GROUP BY clause has been applied.In short, WHERE applies to individual rows, while HAVING applies to groups.

Question 4

What are indexes, and why are they used?

Answer 4

Indexesaredatabase objectsthat improve query performance by allowingfaster retrieval of rows. They function like a book’s index, making it quicker to find specific data without scanning the entire table. However, indexes requireadditional storageand can slightly slow downdata modificationoperations.

Question 5

What is the difference between DELETE and TRUNCATE commands?

Answer 5

• DELETE:Removes rows one at a time and records each deletion in the transaction log, allowing rollback. It can have a WHERE clause.
• TRUNCATE:Removes all rows at once without logging individual row deletions. It cannot have a WHERE clause and is faster than DELETE for large data sets.

Question 6

What is the purpose of the SQL ORDER BY clause?

Answer 6

TheORDER BYclause sorts the result set of a query in eitherascending(default) ordescending order, based on one or more columns. This helps present the data in a more meaningful or readable sequence.

Question 7

What are the differences between SQL and NoSQL databases?

Answer 7

• SQL Databases:
  
  • Use structured tables with rows and columns.
  • Rely on a fixed schema.
  • OfferACIDproperties.
• NoSQL Databases:
  
  • Use flexible, schema-less structures (e.g., key-value pairs, document stores).
  • Are designed for horizontal scaling.
  • Often focus on performance and scalability over strict consistency.

Question 8

What are the types of constraints in SQL?

Answer 8

Common constraints include:

• NOT NULL:Ensures a column cannot have NULL values.
• UNIQUE:Ensures all values in a column are distinct.
• PRIMARY KEY:Uniquely identifies each row in a table.
• FOREIGN KEY:Ensures referential integrity by linking to a primary key in another table.
• CHECK:Ensures that all values in a column satisfy a specific condition.
• DEFAULT:Sets a default value for a column when no value is specified.

Question 9

What is a cursor in SQL?

Answer 9

Acursoris a database object used toretrieve,manipulate, and traverse through rows in a result set one row at a time. Cursors are helpful when performing operations that must be processed sequentially rather than in a set-based manner.

Question 10

What is a trigger in SQL?

Answer 10

Atriggeris a set of SQL statements that automatically execute in response to certain events on a table, such asINSERT,UPDATE, orDELETE. Triggers help maintaindata consistency, enforce business rules, and implement complex integrity constraints.

Question 11

What are NULL values in SQL?

Answer 11

NULLrepresents a missing or unknown value. It is different from zero or an empty string. NULL values indicate that the data is not available or applicable

Question 12

What is a stored procedure?

Answer 12

Astored procedureis a precompiled set of SQL statements stored in thedatabase. It can take input parameters, perform logic and queries, and return output values or result sets. Stored procedures improveperformanceandmaintainabilityby centralizing business logic.

Question 13

What are the main types of SQL commands?

Answer 13

SQL commands are broadly classified into:

• DDL (Data Definition Language):CREATE, ALTER, DROP, TRUNCATE.
• DML (Data Manipulation Language):SELECT, INSERT, UPDATE, DELETE.
• DCL (Data Control Language):GRANT, REVOKE.

Question 14

What is the difference between DDL and DML commands?

Answer 14

1. DDL (Data Definition Language):

These commands are used todefineandmodify the structure of databaseobjects such astables,indexes, andviews. For example, theCREATEcommandcreates a new table, theALTERcommandmodifies an existing table, and theDROPcommandremoves a table entirely.DDLcommands primarily focus on the schema or structure of the database.

Example:

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50)
);

2. DML (Data Manipulation Language):

These commands deal with theactual data storedwithin database objects. For instance, theINSERTcommandadds rows of data to a table, theUPDATEcommand modifies existing data, and theDELETEcommand removes rows from a table. In short,DMLcommands allow you to query and manipulate the data itself rather than the structure.

Example:

INSERT INTO Employees (ID, Name) VALUES (1, 'Alice');

Question 15

What is the purpose of the ALTER command in SQL?

Answer 15

TheALTERcommand is used tomodify the structureof an existing database object. This command is essential for adapting ourdatabase schemaas requirements evolve.

• Add or drop a column in a table.
• Change a column’s data type.
• Add or remove constraints.
• Rename columns or tables.
• Adjust indexing or storage settings.

Question 16

What is a composite primary key?

Answer 16

A composite primary keyis a primary key made up of two or more columns. Together, these columns must form a unique combination for each row in the table. It’s used when a single column isn’t sufficient to uniquely identify a record.

Example:

Consider an Orders table whereOrderIDandProductIDtogether uniquely identify each record because multiple orders might include the same product, but not within the same order.

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);

Question 17

How is data integrity maintained in SQL databases?

Answer 17

Data integrity refers to theaccuracy,consistency, andreliabilityof the data stored in the database. SQL databases maintain data integrity through several mechanisms:

• Constraints:Ensuring that certain conditions are always met. For example,NOT NULLensures a column cannot have missing values,FOREIGN KEYensures a valid relationship between tables, andUNIQUEensures no duplicate values.
• Transactions:Ensuring that a series of operations either all succeed or all fail, preserving data consistency.
• Triggers:Automatically enforcing rules or validations before or after changes to data.
• Normalization:Organizing data into multiple related tables to minimize redundancy and prevent anomalies.These measures collectively ensure that the data remains reliable and meaningful over time.

Question 18

What are the advantages of using stored procedures?

Answer 18

• Improved Performance:Stored procedures are precompiled and cached in the database, making their execution faster than sending multiple individual queries.
• Reduced Network Traffic:By executing complex logic on the server, fewer round trips between the application and database are needed.
• Enhanced Security:Stored procedures can restrict direct access to underlying tables, allowing users to execute only authorized operations.
• Reusability and Maintenance:Once a procedure is written, it can be reused across multiple applications. If business logic changes, you only need to update the stored procedure, not every application that uses it.

Question 19

What is a UNION operation, and how is it used?

Answer 19

TheUNIONoperator combines the result sets of two or moreSELECTqueriesinto a single result set, removing duplicate rows. This is useful when we need aconsolidated viewof data from multiple tables or queries that have similar structure.

Example:

SELECT Name FROM Customers
UNION
SELECT Name FROM Employees;

Question 20

What is the difference between UNION and UNION ALL?

Answer 20

• UNION:Removes duplicate rows from the result set, ensuring only unique rows are returned.
• UNION ALL:Includes all rows from each query, including duplicates.
• Performance-wise,UNION ALLis faster because it doesn’t require an additional step to remove duplicates.

Example:

SELECT Name FROM Customers
UNION ALL
SELECT Name FROM Employees;

Question 21

How does the CASE statement work in SQL?

Answer 21

TheCASEstatement is SQL’s way of implementingconditional logicin queries. It evaluates conditions and returns a value based on the first condition that evaluates to true. If no condition is met, it can return a default value using theELSEclause.

Example:

SELECT ID,
       CASE
           WHEN Salary > 100000 THEN 'High'
           WHEN Salary BETWEEN 50000 AND 100000 THEN 'Medium'
           ELSE 'Low'
       END AS SalaryLevel
FROM Employees;

Question 22

What are scalar functions in SQL?

Answer 22

Scalar functionsoperate on individual values and return a single value as a result. They are often used for formatting or converting data. Common examples include:

• LEN():Returns the length of a string.
• ROUND():Rounds a numeric value.
• CONVERT():Converts a value from one data type to another.

Example:

SELECT LEN('Example') AS StringLength;

Question 23

What is the purpose of the COALESCE function?

Answer 23

TheCOALESCEfunctionreturns the first non-NULL value from a list of expressions. It’s commonly used to provide default values or handle missing data gracefully.

Example:

SELECT COALESCE(NULL, NULL, 'Default Value') AS Result;

Question 24

What are the differences between SQL’s COUNT() and SUM() functions?

Answer 24

1. COUNT():Counts the number of rows or non-NULL values in a column.

Example:

SELECT COUNT(*) FROM Orders;

2. SUM():Adds up all numeric values in a column.

Example:

SELECT SUM(TotalAmount) FROM Orders;

Question 25

What is the difference between an index and a key in SQL?

Answer 25

1. Index

• Anindexis a database object created tospeed up data retrieval. It stores a sorted reference to table data, which helps the database engine find rows more quickly than scanning the entire table.
• Example:A non-unique index on a column likeLastNameallows quick lookups of rows where the last name matches a specific value.

2.Key

• A key is a logical concept that enforces rules for uniqueness or relationships in the data.
• For instance, aPRIMARY KEYuniquely identifies each row in a table and ensures that no duplicate or NULL values exist in the key column(s).
• AFOREIGN KEYmaintains referential integrity by linking rows in one table to rows in another.

Question 26

How does indexing improve query performance?

Answer 26

Indexing allows thedatabaseto locate and access the rows corresponding to aquery conditionmuch faster than scanning the entire table. Instead of reading each row sequentially, the database uses the index tojump directlyto the relevant data pages. This reduces the number of diskI/O operationsand speeds up query execution, especially for large tables.

Example:

CREATE INDEX idx_lastname ON Employees(LastName);
SELECT * FROM Employees WHERE LastName = 'Smith';

The index onLastNamelets the database quickly find all rows matching ‘Smith’ without scanning every record.

Question 27

What are the trade-offs of using indexes in SQL databases?

Answer 27

Advantages

• Faster query performance, especially for SELECT queries withWHEREclauses, JOIN conditions, or ORDER BY clauses.
• Improved sorting and filtering efficiency.

Disadvantages:

• Increased storage space for the index structures.
• Additional overhead for write operations (INSERT, UPDATE, DELETE), as indexes must be updated whenever the underlying data changes.
• Potentiallyslower bulk data loads or batch inserts due to the need to maintain index integrity.In short, indexes make read operations faster but can slow down write operations and increase storage requirements.

Question 28

What is the difference between clustered and non-clustered indexes?

Answer 28

1. Clustered Index:

• Organizes the physical data in the table itself in the order of the indexed column(s).
• A table can have only oneclustered index.
• Improves range queries and queries that sort data.
• Example: IfEmployeeIDis the clustered index, the rows in the table are stored physically sorted byEmployeeID.

2. Non-Clustered Index:

• Maintains a separate structure that contains a reference (or pointer) to the physical data in the table.
• A table can have multiple non-clustered indexes.
• Useful for specific query conditions that aren’t related to the primary ordering of the data.
• Example: A non-clustered index onLastNameallows fast lookups by last name even if the table is sorted by another column.

Question 29

What are temporary tables, and how are they used?

Answer 29

Temporary tablesare tables that exist only for the duration of asessionor atransaction. They are useful for storing intermediate results, simplifying complex queries, or performing operations on subsets of data without modifying the main tables.

1. Local Temporary Tables:

• Prefixed with#(e.g.,#TempTable).
• Only visible to the session that created them.
• Automatically dropped when the session ends.

2. Global Temporary Tables:

• Prefixed with##(e.g.,##GlobalTempTable).
• Visible to all sessions.
• Dropped when all sessions that reference them are closed.

Example:

CREATE TABLE #TempResults (ID INT, Value VARCHAR(50));
INSERT INTO #TempResults VALUES (1, 'Test');
SELECT * FROM #TempResults;

Question 30

What is a materialized view, and how does it differ from a standard view?

Answer 30

• Standard View:
  
  • A virtual table defined by a query.
  • Does not store data; the underlying query is executed each time the view is referenced.
  • A standard view shows real-time data.
• Materialized View:
  
  • A physical table that stores the result of the query.
  • Data is precomputed and stored, making reads faster.
  • Requires periodic refreshes to keep data up to date.
  • materialized view is used to store aggregated sales data, updated nightly, for fast reporting.

Question 31

How do constraints improve database integrity?

Answer 31

Constraints enforce rules that the data must follow, preventing invalid or inconsistent data from being entered:

• NOT NULL:Ensures that a column cannot contain NULL values.
• UNIQUE:Ensures that all values in a column are distinct.
• PRIMARY KEY:Combines NOT NULL and UNIQUE, guaranteeing that each row is uniquely identifiable.
• FOREIGN KEY:Ensures referential integrity by requiring values in one table to match primary key values in another.
• CHECK:Validates that values meet specific criteria (e.g.,CHECK (Salary > 0)).By automatically enforcing these rules, constraints maintain data reliability and consistency.

Question 32

What is the difference between a local and a global temporary table?

Answer 32

• Local Temporary Table:
  
  • Prefixed with#(e.g.,#TempTable).
  • Exists only within the session that created it.
  • Automatically dropped when the session ends.
• Global Temporary Table:
  
  • Prefixed with##(e.g.,##GlobalTempTable).
  • Visible to all sessions.
  • Dropped only when all sessions referencing it are closed.

Example:

CREATE TABLE #LocalTemp (ID INT);
CREATE TABLE ##GlobalTemp (ID INT);

Question 33

What is the purpose of the SQL MERGE statement?

Answer 33

TheMERGEstatementcombines multiple operations INSERT, UPDATE, and DELETE into one. It is used to synchronize two tables by:

• Inserting rows that don’t exist in the target table.
• Updating rows that already exist.
• Deleting rows from the target table based on conditions

Example:

MERGE INTO TargetTable T
USING SourceTable S
ON T.ID = S.ID
WHEN MATCHED THEN
    UPDATE SET T.Value = S.Value
WHEN NOT MATCHED THEN
    INSERT (ID, Value) VALUES (S.ID, S.Value);

Question 34

How can you handle duplicates in a query without using DISTINCT?

Answer 34

1. GROUP BY:Aggregate rows to eliminate duplicates

SELECT Column1, MAX(Column2)
FROM TableName
GROUP BY Column1;

2.ROW_NUMBER():Assign a unique number to each row and filter by that

WITH CTE AS (
    SELECT Column1, Column2, ROW_NUMBER() OVER (PARTITION BY Column1 ORDER BY Column2) AS RowNum
    FROM TableName
)
SELECT * FROM CTE WHERE RowNum = 1;

Question 35

What are partitioned tables, and when should we use them?

Answer 35

Partitioned tablesdivide data intosmaller, moremanageable segmentsbased on a column’s value (e.g., date or region). Each partition is stored separately, making queries that target a specific partition more efficient. It is used when

• Large tables with millions or billions of rows.
• Scenarios where queries frequently filter on partitioned columns (e.g., year, region).
• To improve maintenance operations, such as archiving older partitions without affecting the rest of the table.

Question 36

What are the ACID properties of a transaction?

Answer 36

ACIDis an acronym that stands for Atomicity, Consistency, Isolation, and Durability—four key properties that ensure database transactions are processed reliably.

1.Atomicity:

• A transaction is treated as a single unit of work, meaning all operations must succeed or fail as a whole.
• If any part of the transaction fails, the entire transaction is rolled back.

2.Consistency:

• A transaction must take the database from one valid state to another, maintaining all defined rules and constraints.
• This ensures data integrity is preserved throughout the transaction process.

3.Isolation:

• Transactions should not interfere with each other.
• Even if multiple transactions occur simultaneously, each must operate as if it were the only one in the system until it is complete.

4.Durability:

• Once a transaction is committed, its changes must persist, even in the event of a system failure.
• This ensures the data remains stable after the transaction is successfully completed.

Question 37

What is the purpose of the WITH (NOLOCK) hint in SQL Server?

Answer 37

• TheWITH (NOLOCK)hint allows a query to read data without acquiring shared locks, effectively reading uncommitted data.
• It can improve performance byreducing contention for locks, especially on large tables that are frequently updated.
• Results may be inconsistent or unreliable, as the data read might change or be rolled back.

Example:

SELECT *
FROM Orders WITH (NOLOCK);

This query fetches data from theOrderstable without waiting for other transactions to release their locks.

Question 38

How do you handle deadlocks in SQL databases?

Answer 38

Deadlocksoccur when two or more transactions hold resources that the other transactions need, resulting in a cycle of dependency that prevents progress. Strategies to handle deadlocks include:

1.Deadlock detection and retry:

• Many database systems have mechanisms to detect deadlocks and terminate one of the transactions to break the cycle.
• The terminated transaction can be retried after the other transactions complete.

2.Reducing lock contention:

• Use indexes and optimized queries to minimize the duration and scope of locks.
• Break transactions into smaller steps to reduce the likelihood of conflicts.

3.Using proper isolation levels:

• In some cases, lower isolation levels can help reduce locking.
• Conversely, higher isolation levels (like Serializable) may ensure a predictable order of operations, reducing deadlock risk.

4.Consistent ordering of resource access:

• Ensure that transactions acquire resources in the same order to prevent cyclical dependencies.

Question 39

What is a database snapshot, and how is it used?

Answer 39

Adatabase snapshotis a read-only, static view of a database at a specific point in time.

• Reporting:Allowing users to query a consistent dataset without affecting live operations.
• Backup and recovery:Snapshots can serve as a point-in-time recovery source if changes need to be reversed.
• Testing:Providing a stable dataset for testing purposes without the risk of modifying the original data.

Example:

CREATE DATABASE MySnapshot ON
(
    NAME = MyDatabase_Data,
    FILENAME = 'C:\Snapshots\MyDatabase_Snapshot.ss'
)
AS SNAPSHOT OF MyDatabase;

Question 40

What is a live lock, and how does it differ from a deadlock?

Answer 40

1. Live Lock

• Occurs when two or more transactions keep responding to each other’s changes, but no progress is made.
• Unlike a deadlock, the transactions are not blocked; they are actively running, but they cannot complete.

2.Deadlock

• Occurs when transactions are blocked waiting for each other to release locks.
• No progress can be made unless one of the transactions is terminated

Question 41

What are the considerations for indexing very large tables?

Answer 41

1. Indexing Strategy:

• Focus on the most frequently queried columns or those involved inJOINand WHERE conditions.
• Avoid indexing every column, as it increases storage and maintenance costs.

2. Index Types:

• Use clustered indexes for primary key lookups and range queries.
• Use non-clustered indexes for filtering, ordering, and covering specific queries.

3. Partitioned Indexes:

• If the table is partitioned, consider creatinglocal indexesfor each partition. This improves manageability and can speed up queries targeting specific partitions.

4. Maintenance Overhead:

• Index rebuilding and updating can be resource-intensive. Plan for regular index maintenance during off-peak hours.
• Monitor index fragmentation and rebuild indexes as necessary to maintain performance.

5. Monitoring and Tuning:

• Continuously evaluate query performance using execution plans and statistics.
• Remove unused or rarely accessed indexes to reduce maintenance costs.

6. Indexing large tables requires a careful approach to ensure that performance gains from faster queries outweigh the costs of increased storage and maintenance effort.

Question 42

What are the best practices for writing optimized SQL queries?

Answer 42

1. Write Simple, Clear Queries:

• Avoid overly complex joins andsubqueries.
• Use straightforward, well-structured SQL that is easy to read and maintain.

2. Filter Data Early:

• Apply WHERE clauses as early as possible to reduce the amount of data processed.
• Consider using indexed columns in WHERE clauses for faster lookups.

3.Avoid SELECT*:**

• Retrieve only the columns needed. This reduces I/O and improves performance.

4. Use Indexes Wisely:

• Create indexes on columns that are frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses.
• Regularly review index usage and remove unused indexes.

5. Leverage Query Execution Plans:

• Use execution plans to identify bottlenecks, missing indexes, or inefficient query patterns.

6. Use Appropriate Join Types:

• Choose INNER JOIN, LEFT JOIN, or OUTER JOIN based on the data relationships and performance requirements.

7. Break Down Complex Queries:

• Instead of a single monolithic query, use temporary tables or CTEs to process data in stages.

8. Optimize Aggregations:

• Use GROUP BY and aggregate functions efficiently.
• Consider pre-aggregating data if queries frequently require the same computations.

9. Monitor Performance Regularly:

• Continuously analyze query performance and fine-tune as data volumes grow or usage patterns change.

Question 43

What is the purpose of the SQL PIVOT operator?

Answer 43

ThePIVOT operatortransforms rows into columns, making it easier to summarize or rearrange data for reporting.

Example:

Converting a dataset that lists monthly sales into a format that displays each month as a separate column.

SELECT ProductID, [2021], [2022]
FROM (
    SELECT ProductID, YEAR(SaleDate) AS SaleYear, Amount
    FROM Sales
) AS Source
PIVOT (
    SUM(Amount)
    FOR SaleYear IN ([2021], [2022])
) AS PivotTable;