New 100 T-SQL Interview Questions

Introduction

T-SQL, or Transact-SQL, is a powerful programming language used for managing and querying data in Microsoft SQL Server. If you’re preparing for a T-SQL interview, it’s essential to be familiar with common interview questions. These questions often cover topics like data manipulation, database design, stored procedures, functions, and performance optimization. By understanding T-SQL concepts and practicing these questions, you can demonstrate your proficiency in working with SQL databases and showcase your problem-solving skills. So, let’s dive into the world of T-SQL interview questions and explore the exciting possibilities this language offers for data management and analysis.

Basic Questions

1. What is T-SQL?

T-SQL (Transact-SQL) is a Microsoft’s proprietary extension of SQL used for querying and manipulating data in SQL Server databases.

Example:

-- T-SQL SELECT statement
SELECT FirstName, LastName FROM Employees WHERE Department = 'HR';

2. What are the differences between SQL and T-SQL?

SQL is a standard language for managing relational databases, while T-SQL is a specific implementation by Microsoft for SQL Server.

Example (SQL):

-- Standard SQL SELECT statement
SELECT column1, column2 FROM table_name WHERE condition;

Example (T-SQL):

-- T-SQL SELECT statement
SELECT column1, column2 FROM table_name WHERE condition;

3. Can you name at least five commands that can manipulate text in the T-SQL code?

1. LEN: Returns the length of a string.

SELECT LEN('Hello'); -- Output: 5

2. SUBSTRING: Extracts a portion of a string.

SELECT SUBSTRING('Hello World', 7, 5); -- Output: "World"

3. CHARINDEX: Finds the starting position of a substring within a string.

SELECT CHARINDEX('o', 'Hello'); -- Output: 5

4. UPPER: Converts a string to uppercase.

SELECT UPPER('hello'); -- Output: "HELLO"

5. LOWER: Converts a string to lowercase.

SELECT LOWER('Hello'); -- Output: "hello"

4. Is it possible to import data directly from T-SQL commands without using SQL Server Integration Services? If so, what are the commands?

Yes, it is possible to import data directly using T-SQL commands like BULK INSERT and OPENROWSET.

Example using BULK INSERT:

BULK INSERT dbo.MyTable
FROM 'C:\Data\MyData.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);

5. What is ‘GO’ in T-SQL?

‘GO’ is a batch separator used in T-SQL to execute multiple batches of SQL statements together.

Example:

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

INSERT INTO Employees (ID, Name)
VALUES (1, 'John Doe')
GO

6. What is the difference between the DELETE statement and the TRUNCATE statement?

The DELETE statement is used to remove specific rows from a table, while the TRUNCATE statement is used to remove all rows from a table.

Example using DELETE:

DELETE FROM Employees WHERE Department = 'HR';

Example using TRUNCATE:

TRUNCATE TABLE Employees;

7. When should you use COALESCE() & ISNULL() Functions?

Use COALESCE() when you want to return the first non-null expression among the arguments, and use ISNULL() when you want to replace a null value with a specified replacement.

Example using COALESCE():

SELECT COALESCE(FirstName, 'Unknown') FROM Employees;

Example using ISNULL():

SELECT ISNULL(FirstName, 'Unknown') FROM Employees;

8. What is a sub-query?

A subquery, also known as a nested query or inner query, is a query that’s embedded within the WHERE or HAVING clause of another SQL query. Subqueries can return individual values or a list of records; depending on how they’re used, they can act like SELECT, FROM, or WHERE clauses.

For instance, suppose you have a table of employees and you want to find all employees who earn more than the average salary. You could use a subquery to first calculate the average salary, and then use that value in a comparison to find all employees earning more than that amount:

SELECT employee_name, employee_salary 
FROM employees
WHERE employee_salary > (SELECT AVG(employee_salary) FROM employees);

In this example, the subquery (SELECT AVG(employee_salary) FROM employees) calculates the average salary of all employees. This subquery is executed first, and its result is used in the outer query to find all employees with a salary greater than this average value.

9. What are the types of XML indexes in SQL Server?

In SQL Server, there are two types of XML indexes: Primary XML Index and Secondary XML Index.

Example of creating a Primary XML Index:

CREATE PRIMARY XML INDEX IX_Primary_XML ON dbo.MyTable(XmlColumn);

Example of creating a Secondary XML Index:

CREATE XML INDEX IX_Secondary_XML ON dbo.MyTable(XmlColumn) USING XML INDEX IX_Primary_XML FOR PATH;

10. What is SQL Server?

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is designed to manage and store information in a structured way, using tables that can relate to each other. SQL Server uses the SQL (Structured Query Language) to query and manipulate the data stored in these databases.

Here are some key features of SQL Server:

1. Relational Database Management: SQL Server supports the creation and management of relational databases. These databases can store a large amount of data in tables, and the data can be accessed using SQL queries.
2. Scalability and Performance: SQL Server is designed to handle high loads and large amounts of data, making it suitable for everything from small applications to large enterprise systems. It offers various features to improve performance, such as indexing and partitioning.
3. Security: SQL Server provides robust security features, such as encryption and user authentication, to protect sensitive data. It also provides advanced features like row-level security and dynamic data masking to control access to data.
4. Business Intelligence (BI) Tools: SQL Server includes a suite of business intelligence tools, including SQL Server Analysis Services (SSAS), SQL Server Reporting Services (SSRS), and SQL Server Integration Services (SSIS), which provide data analytics, reporting, and data integration capabilities.
5. Data Replication and Backup: SQL Server provides data replication, backup, and recovery features to ensure that data is safe and available. This includes log shipping, database mirroring, and always-on availability groups.
6. Support for Structured and Unstructured Data: While SQL Server is primarily used for storing structured data, it also supports the storage of unstructured and semi-structured data, such as XML, JSON, and text files.

11. What new error handling commands were introduced with SQL Server 2005 and beyond? What commands did they replace? How are these commands used?

SQL Server 2005 introduced the TRY…CATCH construct to handle errors in T-SQL code. It replaced the older error handling approach using @@ERROR and @@ROWCOUNT.

Example of using TRY…CATCH:

BEGIN TRY
    -- Your T-SQL code that might throw an error
    INSERT INTO MyTable (ID) VALUES (1);
END TRY
BEGIN CATCH
    -- Error handling code
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH

12. What is TOP in T-SQL?

The TOP keyword in T-SQL (Transact-SQL, which is Microsoft’s implementation of SQL used in SQL Server) is used to limit the number of rows returned by a query. This can be particularly useful when dealing with large tables where you’re only interested in a subset of rows.

Here’s a basic usage of the TOP keyword:

SELECT TOP 10 * FROM Employees;

This query will return the first 10 rows from the Employees table.

You can also use TOP with a percentage. For example:

SELECT TOP 10 PERCENT * FROM Employees;

This will return the top 10% of rows from the Employees table.

The TOP keyword can be used with INSERT, UPDATE, DELETE, and MERGE statements as well, not just SELECT statements. It’s important to note that when using TOP, the rows returned are not guaranteed to be in any particular order unless you also specify an ORDER BY clause. For example:

SELECT TOP 10 * FROM Employees ORDER BY Salary DESC;

This query will return the 10 employees with the highest salaries.

13. What is the difference between a table and a view?

14. What is a stored procedure?

A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit.

Example of creating a stored procedure:

CREATE PROCEDURE GetEmployeesByDepartment
    @Department VARCHAR(50)
AS
BEGIN
    SELECT FirstName, LastName FROM Employees WHERE Department = @Department;
END

15. What is a trigger in SQL?

A trigger in SQL is a special type of stored procedure that automatically executes when certain events (e.g., INSERT, UPDATE, DELETE) occur on a table.

Example of creating a trigger:

CREATE TRIGGER AfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    -- Trigger logic here
    INSERT INTO LogTable (Event) VALUES ('Employee added');
END

16. What is the difference between a function and a stored procedure?

17. What is a cursor, and when should it be used?

A cursor is a database object that allows you to traverse and process individual rows returned by a query.

Example of using a cursor:

DECLARE @EmployeeID INT;
DECLARE EmployeeCursor CURSOR FOR
SELECT ID FROM Employees;

OPEN EmployeeCursor;
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Process the row with @EmployeeID
    -- ...

    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID;
END

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Cursors should be used sparingly, as they can negatively impact performance. They are suitable for scenarios where row-by-row processing is necessary.

18. What is the use of the COALESCE function?

The COALESCE function is used to return the first non-null expression among its arguments.

Example:

SELECT COALESCE(FirstName, LastName, 'Unknown') AS FullName FROM Employees;

19. What is the STUFF function, and how does it differ from the REPLACE function?

The STUFF function is used to replace a portion of a string with another substring, while the REPLACE function replaces all occurrences of a substring with another substring.

Example using STUFF:

SELECT STUFF('Hello World', 7, 5, 'Universe'); -- Output: "Hello Universe"

Example using REPLACE:

SELECT REPLACE('Hello World', 'World', 'Universe'); -- Output: "Hello Universe"

20. What is a CTE (Common Table Expression)?

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

Example of using a CTE:

WITH DepartmentCTE AS (
    SELECT ID, Name FROM Departments WHERE Location = 'New York'
)
SELECT * FROM DepartmentCTE;

21. What is the difference between UNION and UNION ALL?

Both UNION and UNION ALL are used to combine the results of two or more SELECT queries. The difference is that UNION removes duplicates, while UNION ALL keeps all rows, including duplicates.

Example using UNION:

SELECT FirstName FROM Employees WHERE Department = 'HR'
UNION
SELECT FirstName FROM Contractors WHERE Department = 'HR';

Example using UNION ALL:

SELECT FirstName FROM Employees WHERE Department = 'HR'
UNION ALL
SELECT FirstName FROM Contractors WHERE Department = 'HR';

22. What is a deadlock, and how can it be avoided?

A deadlock is a situation where two or more transactions are unable to proceed because each holds a lock that the other transaction needs.

Example of a simple deadlock scenario:

-- Transaction 1
BEGIN TRAN
UPDATE Employees SET Salary = Salary + 1000 WHERE ID = 1;

-- Transaction 2
BEGIN TRAN
UPDATE Employees SET Salary = Salary + 500 WHERE ID = 1;
UPDATE Employees SET Salary = Salary + 500 WHERE ID = 2;

23. What is a transaction, and what are ACID properties?

A transaction is a sequence of one or more SQL operations that are treated as a single unit of work. ACID properties are characteristics that ensure the reliability and consistency of transactions.

ACID properties stand for:

• Atomicity: Ensures that a transaction is treated as a single indivisible unit. It either fully completes or fully fails.
• Consistency: Ensures that a transaction brings the database from one valid state to another.
• Isolation: Ensures that each transaction is executed in isolation from other transactions, as if it’s the only one running.
• Durability: Ensures that once a transaction is committed, its changes are permanent and will survive system failures.

24. What is the difference between CHAR, VARCHAR, and VARCHAR(MAX) data types?

• CHAR: Fixed-length character data type, always reserves the specified storage space, pads with spaces if the data is shorter.
• VARCHAR: Variable-length character data type, only uses as much storage as needed, no padding with spaces.
• VARCHAR(MAX): Similar to VARCHAR but allows for larger storage, up to 2GB.

Example:

CREATE TABLE MyTable (
    ID INT,
    Name CHAR(10),
    Description VARCHAR(50),
    LongDescription VARCHAR(MAX)
);

25. What is the difference between a clustered and a non-clustered index?

• Clustered Index: Determines the physical order of data rows in a table. Each table can have only one clustered index. Generally, it is the primary key.
• Non-clustered Index: Provides a separate structure that stores the index data and contains a pointer to the actual data rows.

Example of creating a clustered index:

CREATE CLUSTERED INDEX IX_Employees_ID ON Employees (ID);

Example of creating a non-clustered index:

CREATE INDEX IX_Employees_Department ON Employees (Department);

26. What is a self-join, and why would you use one?

A self-join is a join operation where a table is joined with itself.

Example:
Consider a “Employees” table with columns (ID, Name, ManagerID) where ManagerID refers to another employee in the same table.

To find the employees and their respective managers:

SELECT e.Name AS EmployeeName, m.Name AS ManagerName
FROM Employees e
JOIN Employees m ON e.ManagerID = m.ID;

27. What is the difference between a primary key and a unique key?

Both primary key and unique key enforce uniqueness in a column or a set of columns, but there are differences:

• Primary Key: A primary key is used to uniquely identify each row in a table. It also implies the column is not nullable and creates a clustered index by default (in SQL Server).
• Unique Key: A unique key ensures the values in a column or set of columns are unique, but it allows one NULL value.

Example of creating a primary key:

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

Example of creating a unique key:

CREATE TABLE Departments (
    ID INT,
    Name VARCHAR(50) UNIQUE
);

28. What is a foreign key?

A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a link between the two tables.

Example of creating a foreign key:

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


 FOREIGN KEY (DepartmentID) REFERENCES Departments(ID)
);

29. What is the difference between a temp table and a table variable?

Example of creating a temp table:

CREATE TABLE #TempTable (ID INT, Name VARCHAR(50));

Example of creating a table variable:

DECLARE @TableVariable TABLE (ID INT, Name VARCHAR(50));

30. What is the difference between ISNULL and NULLIF?

• ISNULL: Replaces a NULL value with a specified replacement value.
• NULLIF: Compares two expressions and returns NULL if they are equal, otherwise returns the first expression.

Example using ISNULL:

SELECT ISNULL(ColumnName, 'N/A') FROM MyTable;

Example using NULLIF:

SELECT NULLIF(ColumnName, 0) FROM MyTable;

Intermediate Questions

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

A local temporary table is only visible within the scope of the current session, and it is automatically dropped when the session is closed. A global temporary table, on the other hand, is visible to all sessions, and it is dropped when the last session referencing it is closed.

Example of creating and using local and global temporary tables:

-- Local temporary table
CREATE TABLE #LocalTempTable (ID INT, Name VARCHAR(50));

INSERT INTO #LocalTempTable VALUES (1, 'John'), (2, 'Jane');

-- Global temporary table
CREATE TABLE ##GlobalTempTable (ID INT, Name VARCHAR(50));

INSERT INTO ##GlobalTempTable VALUES (1, 'Mike'), (2, 'Emily');

32. What are the different types of subqueries?

There are three types of subqueries in T-SQL:

1. Scalar subquery: A subquery that returns a single value.
2. Single-row subquery: A subquery that returns a single row.
3. Multi-row subquery: A subquery that returns multiple rows.

Example of a scalar subquery:

SELECT FirstName, (SELECT MAX(Salary) FROM Employees) AS MaxSalary
FROM Employees;

Example of a single-row subquery:

SELECT FirstName, LastName
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

Example of a multi-row subquery:

SELECT FirstName, LastName
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

33. What is the difference between IN and EXISTS?

The IN operator is used to specify multiple values in a subquery or a list, while the EXISTS operator is used to check for the existence of rows returned by a subquery.

Example of using the IN operator:

SELECT FirstName, LastName
FROM Employees
WHERE DepartmentID IN (1, 3, 5);

Example of using the EXISTS operator:

SELECT FirstName, LastName
FROM Employees AS e
WHERE EXISTS (
    SELECT 1
    FROM Departments AS d
    WHERE d.DepartmentID = e.DepartmentID AND d.Location = 'New York'
);

34. How can you improve the performance of a query?

There are several ways to improve the performance of a query:

1. Use appropriate indexes on columns used in search conditions and join clauses.
2. Avoid using SELECT * and only select the necessary columns.
3. Use JOIN instead of subqueries where possible.
4. Avoid using complex expressions or functions in the WHERE clause.
5. Use stored procedures to avoid recompilation of the same query.
6. Use the SET NOCOUNT ON statement to reduce network traffic.
7. Consider using indexed views for frequently used queries.
8. Monitor and optimize query execution plans.
9. Use proper normalization and indexing of tables.
10. Consider partitioning large tables if appropriate.

35. What is a correlated subquery?

A correlated subquery is a subquery that refers to a column from the outer query in its WHERE clause. It executes for each row of the outer query and depends on the values from the outer query.

Example of a correlated subquery:

SELECT FirstName, LastName
FROM Employees AS e
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees AS e2
    WHERE e2.DepartmentID = e.DepartmentID
);

In this example, the inner subquery depends on the DepartmentID from the outer query to calculate the average salary for each department.

36. What is the difference between a DELETE and a DROP statement?

The DELETE statement is used to remove rows from a table, while the DROP statement is used to remove an entire table from the database.

Example of using DELETE:

DELETE FROM Employees WHERE DepartmentID = 5;

Example of using DROP:

DROP TABLE Employees;

37. What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of data in a table, and there can be only one clustered index per table. On the other hand, a non-clustered index creates a separate structure that points to the data and allows multiple non-clustered indexes per table.

Example of creating a clustered index:

CREATE CLUSTERED INDEX IX_Employee_ID ON Employees (EmployeeID);

Example of creating a non-clustered index:

CREATE NONCLUSTERED INDEX IX_Employee_Department ON Employees (DepartmentID);

38. What is a cross join?

A cross join (also known as a Cartesian join) combines each row from the first table with every row from the second table. It results in a Cartesian product of the two tables.

Example of using a cross join:

SELECT *
FROM Employees CROSS JOIN Departments;

This will combine each employee with every department, resulting in a row for each possible combination of an employee and a department.

39. What is a pivot table?

A pivot table is used to transform data from rows into columns, effectively rotating the table to display summarized data.

Example of using a pivot table:

-- Sample data
CREATE TABLE Sales (Product VARCHAR(50), Month VARCHAR(10), Amount DECIMAL(10,2));

INSERT INTO Sales VALUES ('ProductA', 'January', 1000),
                          ('ProductA', 'February', 1500),
                          ('ProductB', 'January', 800),
                          ('ProductB', 'February', 1200);

-- Pivot query
SELECT *
FROM Sales
PIVOT (
    SUM(Amount)
    FOR Month IN ([January], [February])
) AS P;

This will transform the data from rows into columns, summarizing the sales amount for each product in each month.

40. What is the difference between a function and a stored procedure?

A function is a reusable piece of code that returns a single value based on the input parameters, whereas a stored procedure is a reusable piece of code that can perform multiple operations, including selecting, inserting, updating, and deleting data.

Example of a user-defined function:

CREATE FUNCTION GetEmployeeSalary(@EmployeeID INT)
RETURNS DECIMAL(10, 2)
AS
BEGIN
    DECLARE @Salary DECIMAL(10, 2);
    SELECT @Salary = Salary FROM Employees WHERE EmployeeID = @EmployeeID;
    RETURN @Salary;
END;

Example of a stored procedure:

CREATE PROCEDURE UpdateEmployeeSalary

 @EmployeeID INT, @NewSalary DECIMAL(10, 2)
AS
BEGIN
    UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmployeeID;
END;

41. What is a trigger and when would you use one?

A trigger is a special type of stored procedure that automatically executes when an event (like an INSERT, UPDATE, or DELETE operation) occurs on a table. Triggers are used to enforce data integrity, perform auditing, or automate certain actions based on database events.

Example of creating a trigger:

CREATE TRIGGER AuditEmployeeChanges
ON Employees
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    -- Perform auditing or other actions here
    -- For example, log changes to the AuditLog table
    INSERT INTO AuditLog (TableName, Action, ModifiedDate)
    VALUES ('Employees', 'INSERT/UPDATE/DELETE', GETDATE());
END;

In this example, the trigger AuditEmployeeChanges will execute after any INSERT, UPDATE, or DELETE operation on the Employees table and log the changes to the AuditLog table.

42. What is the difference between a view and a stored procedure?

A view is a virtual table that is based on the result of a SELECT query, and it does not store data on its own. A stored procedure, as mentioned earlier, is a reusable piece of code that can perform multiple operations on data.

Example of a view:

CREATE VIEW EmployeeInfo AS
SELECT EmployeeID, FirstName, LastName, DepartmentID, Salary
FROM Employees;

Example of a stored procedure:

CREATE PROCEDURE GetEmployeesByDepartment @DepartmentID INT
AS
BEGIN
    SELECT FirstName, LastName
    FROM Employees
    WHERE DepartmentID = @DepartmentID;
END;

43. What is the difference between a table scan and an index scan?

A table scan refers to the process of reading all rows in a table to find the desired data, without using an index. An index scan, on the other hand, occurs when the database engine uses an index to locate the data.

Example of a table scan:

SELECT * FROM Employees WHERE LastName = 'Smith';

Example of an index scan:

SELECT * FROM Employees WHERE EmployeeID BETWEEN 1000 AND 2000;

In this example, if there is an index on the EmployeeID column, it will be used to perform the index scan, quickly locating the relevant rows.

44. What is a covering index?

A covering index is an index that includes all the columns required to satisfy a query, thereby allowing the database engine to retrieve all necessary data directly from the index without the need to access the underlying table.

Example of creating a covering index:

CREATE NONCLUSTERED INDEX IX_CoveringIndex
ON Employees (DepartmentID, FirstName, LastName, Salary);

In this example, the index IX_CoveringIndex includes all the columns required for the query, which might look like this:

SELECT FirstName, LastName, Salary
FROM Employees
WHERE DepartmentID = 5;

The database engine can retrieve the necessary data directly from the index without accessing the Employees table, making the query more efficient.

45. What is the difference between a left join and a right join?

A left join (or left outer join) includes all rows from the left table and the matching rows from the right table. If there are no matches, the result will contain NULL values for the right table’s columns. A right join (or right outer join) works similarly but includes all rows from the right table and the matching rows from the left table.

Example of a left join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
LEFT JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

Example of a right join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
RIGHT JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

46. What is the difference between a full join and an inner join?

An inner join only includes the rows that have matching values in both the left and right tables. A full join (or full outer join) includes all rows from both tables and puts NULL in the columns where there is no match.

Example of an inner join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
INNER JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

Example of a full join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
FULL JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

47. What is the difference between a cross join and a full outer join?

A cross join produces a Cartesian product of the two tables, combining each row from the first table with every row from the second table. A full outer join, as explained earlier, includes all rows from both tables, with NULL values in the columns where there is no match.

Example of a cross join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
CROSS JOIN Departments AS D;

Example of a full outer join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
FULL JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

48. What is the difference between a natural join and an inner join?

A natural join is a type of inner join that automatically matches the columns with the same name in both tables. It combines rows based on the equality of those common columns.

Example of a natural join:

SELECT E.FirstName, E.LastName, E.DepartmentID, D.DepartmentName
FROM Employees AS E
NATURAL JOIN Departments AS D;

In this example, the natural join will automatically match rows from Employees and Departments based on the equality of the DepartmentID column.

49. What is the difference between a self join and an inner join?

A self join is a type of inner join where a table is joined with itself. It is used when you want to compare rows from the same table based on some conditions.

Example of a self join:

SELECT E1.FirstName, E1.LastName, E2.FirstName AS ManagerFirstName, E2.LastName AS ManagerLastName
FROM Employees AS E1
INNER JOIN Employees AS E2 ON E1.ManagerID = E2.EmployeeID;

In this example, the self join is used to retrieve employees and their corresponding managers by matching the ManagerID with the EmployeeID from the same Employees table.

50. What is the difference between a left outer join and a right outer join?

A left outer join includes all rows from the left table and the matching rows from the right table. If there are no matches, the result will contain NULL values for the right table’s columns. A right outer join, as explained earlier, works similarly but includes all rows from the right table and the matching rows from the left table.

Example of a left outer join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
LEFT JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

Example of a right outer join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
RIGHT JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

51. What is the difference between a full outer join and a left outer join?

A left outer join only includes the rows from the left table and the matching rows from the right table. If there are no matches, the result will contain NULL values for the right table’s columns. A full outer join, as explained earlier, includes all rows from both tables and puts NULL in the columns where there is no match.

Example of a left outer join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
LEFT JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

Example of a full outer join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
FULL JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

52. What is the difference between a right outer join and a full outer join?

A right outer join includes all rows from the right table and the matching rows from the left table. If there are no matches, the result will contain NULL values for the left table’s columns. A full outer join, as explained earlier, includes all rows from both tables and puts NULL in the columns where there is no match.

Example of a right outer join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
RIGHT JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

Example of a full outer join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
FULL JOIN Departments AS D ON E.DepartmentID = D.DepartmentID;

53. What is the difference between a cross join and a natural join?

A cross join produces a Cartesian product of the two tables, combining each row from the first table with every row from the second table. A natural join is a type of inner join that automatically matches the columns with the same name in both tables.

Example of a cross join:

SELECT E.FirstName, E.LastName, D.DepartmentName
FROM Employees AS E
CROSS JOIN Departments AS D;

Example of a natural join:

SELECT E.FirstName, E.LastName, E.DepartmentID, D.DepartmentName
FROM Employees AS E
NATURAL JOIN Departments AS D;

In this example, the natural join will automatically match rows from Employees and Departments based on the equality of the DepartmentID column.

54. What is the difference between a CROSS APPLY and an OUTER APPLY?

CROSS APPLY and OUTER APPLY are used to join a table with a table-valued function. The main difference is that CROSS APPLY returns only the rows where the function returns a non-empty result set, while OUTER APPLY returns all rows from the left table and NULL for the right table where there is no match.

Example of CROSS APPLY:

SELECT E.FirstName, E.LastName, P.ProjectName
FROM Employees AS E
CROSS APPLY GetEmployeeProjects(E.EmployeeID) AS P;

In this example, the function GetEmployeeProjects returns projects for each employee using CROSS APPLY.

Example of OUTER APPLY:

SELECT E.FirstName, E.LastName, P.ProjectName
FROM Employees AS E
OUTER APPLY GetEmployeeProjects(E.EmployeeID) AS P;

In this example, the function GetEmployeeProjects returns projects for each employee using OUTER APPLY, which will include all employees and NULL for those who have no projects.

Advanced Questions

55. What is the difference between a UNION and a UNION ALL?

UNION: The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows from the final result.

UNION ALL: The UNION ALL operator also combines the result sets of two or more SELECT statements into a single result set, but it includes all rows, including duplicates, from the SELECT statements.

Example:
Suppose we have two tables: TableA and TableB, both with a column ID.

-- UNION: Removes duplicates
SELECT ID FROM TableA
UNION
SELECT ID FROM TableB;

-- UNION ALL: Includes duplicates
SELECT ID FROM TableA
UNION ALL
SELECT ID FROM TableB;

56. What is the difference between a UNION and an INTERSECT?

UNION: As explained in the previous answer, UNION combines the results of two or more SELECT statements and removes duplicate rows from the final result.

INTERSECT: The INTERSECT operator is used to combine the results of two or more SELECT statements and returns only the rows that are present in all SELECT statements. It removes duplicate rows from the final result.

Example:
Suppose we have two tables: TableA and TableB, both with a column ID.

-- INTERSECT: Returns common IDs from both tables
SELECT ID FROM TableA
INTERSECT
SELECT ID FROM TableB;

57. What is the difference between a UNION and a JOIN?

UNION: As already described, UNION combines the results of two or more SELECT statements vertically, stacking the result sets on top of each other.

JOIN: A JOIN clause is used to combine rows from two or more tables based on related columns between them.

Example:
Suppose we have two tables: Orders and Customers, both having a common column CustomerID.

-- UNION: Combine two separate queries
SELECT FirstName, LastName FROM Customers
UNION
SELECT ProductName, NULL FROM Orders;

-- JOIN: Combine rows based on matching CustomerID
SELECT FirstName, LastName, ProductName
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

58. What is the difference between a UNION and a CROSS JOIN?

UNION: As mentioned earlier, UNION combines the results of two or more SELECT statements vertically, stacking the result sets on top of each other.

CROSS JOIN: The CROSS JOIN is used to produce a Cartesian product of rows from the involved tables, i.e., all possible combinations of rows.

Example:
Suppose we have two tables: Employees and Departments.

-- UNION: Combine two separate queries
SELECT EmployeeName FROM Employees
UNION
SELECT DepartmentName FROM Departments;

-- CROSS JOIN: Cartesian product of all employee names and department names
SELECT EmployeeName, DepartmentName
FROM Employees
CROSS JOIN Departments;

59. What is the difference between a UNION and a FULL OUTER JOIN?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

FULL OUTER JOIN: The FULL OUTER JOIN combines rows from two tables, including all the rows from both tables and filling in NULLs for missing matches.

Example:
Suppose we have two tables: Orders and Customers, both having a common column CustomerID.

-- UNION: Combine two separate queries and remove duplicates
SELECT FirstName, LastName FROM Customers
UNION
SELECT FirstName, LastName FROM Orders;

-- FULL OUTER JOIN: Combine rows from both tables, including unmatched rows with NULLs
SELECT FirstName, LastName, ProductName
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

60. What is the difference between a UNION and a LEFT OUTER JOIN?

UNION: As explained earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

LEFT OUTER JOIN: The LEFT OUTER JOIN returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the columns from the right table.

Example:
Suppose we have two tables: Orders and Customers, both having a common column CustomerID.

-- UNION: Combine two separate queries and remove duplicates
SELECT FirstName, LastName FROM Customers
UNION
SELECT FirstName, LastName FROM Orders;

-- LEFT OUTER JOIN: Return all customers and their corresponding orders (if any)
SELECT FirstName, LastName, ProductName
FROM Customers
LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

61: What is the difference between a UNION and a RIGHT OUTER JOIN?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

RIGHT OUTER JOIN: The RIGHT OUTER JOIN returns all the rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for the columns from the left table.

Example:
Suppose we have two tables: Orders and Customers, both having a common column CustomerID.

-- UNION: Combine two separate queries and remove duplicates
SELECT FirstName, LastName FROM Customers
UNION
SELECT FirstName, LastName FROM Orders;

-- RIGHT OUTER JOIN: Return all orders and their corresponding customers (if any)
SELECT FirstName, LastName, ProductName
FROM Customers
RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

62. What is the difference between a UNION and a SELF JOIN?

UNION: As explained earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELF JOIN: A SELF JOIN is a regular join, but it joins a table with itself. It is useful when you want to combine rows from the same table based on related columns.

Example:
Suppose we have a table called Employees with columns EmployeeID, FirstName, and ManagerID. The ManagerID column refers to the EmployeeID of the manager.

-- UNION: Combine two separate queries and remove duplicates
SELECT FirstName FROM Employees WHERE ManagerID = 1
UNION
SELECT FirstName FROM Employees WHERE EmployeeID = 1;

-- SELF JOIN: Combine employees with their respective managers
SELECT e1.FirstName AS EmployeeName, e2.FirstName AS ManagerName
FROM Employees e1
JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

63. What is the difference between a UNION and a NATURAL JOIN?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

NATURAL JOIN: A NATURAL JOIN is used to combine rows from two tables based on all columns that have the same name and data type in both tables. It implicitly matches the common columns.

Example:
Suppose we have two tables: Employees and Departments, both having a common column DepartmentID.

-- UNION: Combine two separate queries and remove duplicates
SELECT EmployeeName FROM Employees WHERE DepartmentID = 1
UNION
SELECT DepartmentName FROM Departments WHERE DepartmentID = 1;

-- NATURAL JOIN: Combine employees and departments based on their common DepartmentID
SELECT EmployeeName, DepartmentName
FROM Employees
NATURAL JOIN Departments;

64. What is the difference between a UNION and a CROSS APPLY?

UNION: As explained earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

CROSS APPLY: The CROSS APPLY operator is used to invoke a table-valued function for each row of a table expression. It acts as a correlated subquery for each row in the main query.

Example:
Suppose we have a table called Employees with columns EmployeeID and Skills. The Skills column is a comma-separated list of skills for each employee.

-- UNION: Combine two separate queries and remove duplicates
SELECT Skill FROM EmployeeSkills WHERE EmployeeID = 1
UNION
SELECT Skill FROM JobSkills WHERE JobID = 1;

-- CROSS APPLY: Split the comma-separated skills and return them as rows for each employee
SELECT e.EmployeeID, s.Skill
FROM Employees e
CROSS APPLY STRING_SPLIT(e.Skills, ',') s;

65. What is the difference between a UNION and an OUTER APPLY?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

OUTER APPLY: The OUTER APPLY operator is used to invoke a table-valued function for each row of a table expression, similar to CROSS APPLY. However, it also includes rows from the main query that do not have a match in the table-valued function.

Example:
Suppose we have two tables: Employees and VacationDates. The VacationDates table contains vacation start and end dates for employees.

-- UNION: Combine two separate queries and remove duplicates
SELECT StartDate FROM EmployeeVacations WHERE EmployeeID = 1
UNION
SELECT EndDate FROM EmployeeVacations WHERE EmployeeID = 1;

-- OUTER APPLY: Return the employee's vacation start and end dates, including employees without vacations
SELECT e.EmployeeID, v.StartDate, v.EndDate
FROM Employees e
OUTER APPLY (SELECT StartDate, EndDate FROM VacationDates WHERE EmployeeID = e.EmployeeID) v;

66. What is the difference between a UNION and a PIVOT?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

PIVOT: The PIVOT operator is used to transform rows into columns, creating a cross-tabulation of data.

Example:
Suppose we have a table called Sales with columns Month, Product, and Revenue.

-- UNION: Combine two separate queries and remove duplicates
SELECT Product, Revenue FROM Sales WHERE Month = 'January'
UNION
SELECT Product, Revenue FROM Sales WHERE Month = 'February';

-- PIVOT: Transforming rows into columns to display total revenue for each product in each month
SELECT *
FROM (
    SELECT Month, Product, Revenue FROM Sales
) AS SourceTable
PIVOT (
    SUM(Revenue) FOR Month IN ([January], [February])
) AS PivotTable;

67. What is the difference between a UNION and an UNPIVOT?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

UNPIVOT: The UNPIVOT operator is used to transform columns into rows, essentially the reverse of PIVOT.

Example:
Suppose we have a table called Sales with columns Product, JanuaryRevenue, and FebruaryRevenue.

-- UNION: Combine two separate queries and remove duplicates
SELECT Product, JanuaryRevenue FROM Sales
UNION
SELECT Product, FebruaryRevenue FROM Sales;

-- UNPIVOT: Transforming columns into rows to display revenue for each product in each month
SELECT Product, Month, Revenue
FROM Sales
UNPIVOT (
    Revenue FOR Month IN ([JanuaryRevenue], [FebruaryRevenue])
) AS UnpivotTable;

68. What is the difference between a UNION and a GROUP BY?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

GROUP BY: The GROUP BY clause is used to group rows based on a specified column and allows applying aggregate functions to each group.

Example:
Suppose we have a table called Orders with columns Product, Quantity, and Price.

-- UNION: Combine two separate queries and remove duplicates
SELECT Product, Quantity FROM Orders WHERE Price >= 100
UNION
SELECT Product, Quantity FROM Orders WHERE Price < 100;

-- GROUP BY: Grouping orders by product and calculating total quantity for each product
SELECT Product, SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY Product;

69. What is the difference between a UNION and a HAVING clause?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

HAVING: The HAVING clause is used to filter the results of a GROUP BY query based on aggregate function conditions.

Example:
Suppose we have a table called Orders with columns Product, Quantity, and Price.

-- UNION: Combine two separate queries and remove duplicates
SELECT Product, Quantity FROM Orders WHERE Price >= 100
UNION
SELECT Product, Quantity FROM Orders WHERE Price < 100;

-- HAVING: Grouping orders by product and filtering results to include products with total quantity greater than 50
SELECT Product, SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY Product
HAVING SUM(Quantity) > 50;

70. What is the difference between a UNION and a WHERE clause?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

WHERE: The WHERE clause is used to filter rows based on specified conditions.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- WHERE: Filtering employees with a salary greater than 50,000 and working in the 'IT' department
SELECT Name, Department
FROM Employees
WHERE Salary > 50000 AND Department = 'IT';

71. What is the difference between a UNION and a SELECT INTO?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT INTO: The SELECT INTO statement is used to create a new table and inserts the result set of a SELECT query into that table.

Example:
Suppose we have two tables: HighSalaryEmployees and LowSalaryEmployees, both with columns Name and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Salary FROM HighSalaryEmployees
UNION
SELECT Name, Salary FROM LowSalaryEmployees;

-- SELECT INTO: Create a new table called 'AllEmployees' and insert the result set into it
SELECT Name, Salary INTO AllEmployees FROM HighSalaryEmployees;
INSERT INTO AllEmployees (Name, Salary) SELECT Name, Salary FROM LowSalaryEmployees;

72. What is the difference between a UNION and an INSERT INTO SELECT?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

INSERT INTO SELECT: The INSERT INTO SELECT statement is used to insert the result set of a SELECT query into an existing table.

Example:
Suppose we have two tables: HighSalaryEmployees and LowSalaryEmployees, both with columns Name and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Salary FROM HighSalaryEmployees
UNION
SELECT Name, Salary FROM LowSalaryEmployees;

-- INSERT INTO SELECT: Insert the result set into an existing table called 'AllEmployees'
INSERT INTO AllEmployees (Name, Salary)
SELECT Name, Salary FROM HighSalaryEmployees
UNION
SELECT Name, Salary FROM LowSalaryEmployees;

73. What is the difference between a UNION and a SELECT TOP?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT TOP: The SELECT TOP clause is used to limit the number of rows returned by a SELECT query.

Example:
Suppose we have a table called Products with columns ProductID and ProductName.

-- UNION: Combine two separate queries and remove duplicates
SELECT ProductID FROM Products WHERE ProductName LIKE '%Phone%'
UNION
SELECT ProductID FROM

 Products WHERE ProductName LIKE '%Laptop%';

-- SELECT TOP: Limit the result to the top 10 products containing 'Phone' in their name
SELECT TOP 10 ProductID FROM Products WHERE ProductName LIKE '%Phone%';

74. What is the difference between a UNION and a SELECT DISTINCT?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT DISTINCT: The SELECT DISTINCT clause is used to retrieve unique values from a single SELECT query.

Example:
Suppose we have a table called Products with columns Category and Subcategory.

-- UNION: Combine two separate queries and remove duplicates
SELECT Category FROM Products WHERE Subcategory = 'Phone'
UNION
SELECT Category FROM Products WHERE Subcategory = 'Laptop';

-- SELECT DISTINCT: Retrieve unique category values for products with 'Phone' or 'Laptop' subcategory
SELECT DISTINCT Category FROM Products WHERE Subcategory IN ('Phone', 'Laptop');

75. What is the difference between a UNION and a SELECT ALL?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ALL: The SELECT ALL clause is optional and has the same effect as UNION ALL. It includes all rows, including duplicates, from the SELECT statements.

Example:
Suppose we have a table called Products with columns Category and Subcategory.

-- UNION: Combine two separate queries and remove duplicates
SELECT Category FROM Products WHERE Subcategory = 'Phone'
UNION
SELECT Category FROM Products WHERE Subcategory = 'Laptop';

-- SELECT ALL: Includes all category values for products with 'Phone' or 'Laptop' subcategory
SELECT ALL Category FROM Products WHERE Subcategory IN ('Phone', 'Laptop');

76. What is the difference between a UNION and a SELECT FROM?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT FROM: The SELECT FROM clause is used to specify the table or tables from which you want to retrieve data.

Example:
Suppose we have two tables: Orders and Customers, both with a common column CustomerID.

-- UNION: Combine two separate queries and remove duplicates
SELECT CustomerID FROM Customers
UNION
SELECT CustomerID FROM Orders;

-- SELECT FROM: Retrieve customer IDs from the Customers table
SELECT CustomerID FROM Customers;

77. What is the difference between a UNION and a SELECT INTO OUTFILE?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT INTO OUTFILE: The SELECT INTO OUTFILE statement is used to export the result of a SELECT query to a file on the server.

Example:
Suppose we have a table called Employees with columns EmployeeID and FirstName.

-- UNION: Combine two separate queries and remove duplicates
SELECT EmployeeID FROM Employees WHERE FirstName LIKE 'John%'
UNION
SELECT EmployeeID FROM Employees WHERE FirstName LIKE 'Jane%';

-- SELECT INTO OUTFILE: Export the result set to a file on the server
SELECT EmployeeID FROM Employees WHERE FirstName LIKE 'John%' INTO OUTFILE '/tmp/john_employees.csv';

78. What is the difference between a UNION and a SELECT INTO DUMPFILE?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT INTO DUMPFILE: The SELECT INTO DUMPFILE statement is used to export the result of a SELECT query to a file on the client-side.

Example:
Suppose we have a table called Employees with columns EmployeeID and FirstName.

-- UNION: Combine two separate queries and remove duplicates
SELECT EmployeeID FROM Employees WHERE FirstName LIKE 'John%'
UNION
SELECT EmployeeID FROM Employees WHERE FirstName LIKE 'Jane%';

-- SELECT INTO DUMPFILE: Export the result set to a file on the client-side
SELECT EmployeeID FROM Employees WHERE FirstName LIKE 'John%' INTO DUMPFILE '/tmp/john_employees.csv';

79. What is the difference between a UNION and a SELECT INTO TEMPORARY?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT INTO TEMPORARY: The SELECT INTO TEMPORARY statement is used to create a temporary table and inserts the result set of a SELECT query into that temporary table.

Example:
Suppose we have two tables: HighSalaryEmployees and LowSalaryEmployees, both with columns Name and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Salary FROM HighSalaryEmployees
UNION
SELECT Name, Salary FROM LowSalaryEmployees;

-- SELECT INTO TEMPORARY: Create a temporary table called 'TemporaryEmployees' and insert the result set into it
SELECT Name, Salary INTO TEMPORARY TemporaryEmployees FROM HighSalaryEmployees;
INSERT INTO TemporaryEmployees (Name, Salary) SELECT Name, Salary FROM LowSalaryEmployees;

80. What is the difference between a UNION and a SELECT FOR UPDATE?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT FOR UPDATE: The SELECT FOR UPDATE statement is used in a transaction to lock rows for update to prevent other transactions from modifying them.

Example:
Suppose we have a table called Inventory with columns ProductID and StockQuantity.

-- UNION: Combine two separate queries and remove duplicates
SELECT ProductID FROM Inventory WHERE StockQuantity > 100
UNION
SELECT ProductID FROM Inventory WHERE StockQuantity <= 100;

-- SELECT FOR UPDATE: Lock rows with stock quantity less than or equal to 100 for update
BEGIN TRANSACTION;
SELECT ProductID FROM Inventory WHERE StockQuantity <= 100 FOR UPDATE;
-- Perform updates on the selected rows
COMMIT TRANSACTION;

81. What is the difference between a UNION and a SELECT FOR SHARE?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT FOR SHARE: The SELECT FOR SHARE statement is used in a transaction to lock rows for read access, allowing other transactions to read the data but preventing them from updating it until the transaction is completed.

Example:
Suppose we have a table called Inventory with columns ProductID and StockQuantity.

-- UNION: Combine two separate queries and remove duplicates
SELECT ProductID FROM Inventory WHERE StockQuantity > 100
UNION
SELECT ProductID FROM Inventory WHERE StockQuantity <= 100;

-- SELECT FOR SHARE: Lock rows with stock quantity less than or equal to 100 for read access
BEGIN TRANSACTION;
SELECT ProductID FROM Inventory WHERE StockQuantity <= 100 FOR SHARE;
-- Perform reads on the selected rows
COMMIT TRANSACTION;

82. What is the difference between a UNION and a SELECT LOCK IN SHARE MODE?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT LOCK IN SHARE MODE: The SELECT LOCK IN SHARE MODE statement is used to lock rows for read access, preventing other transactions from updating them, but allowing them to read the data.

Example:
Suppose we have a table called Inventory with columns ProductID and StockQuantity.

-- UNION: Combine two separate queries and remove duplicates
SELECT ProductID FROM Inventory WHERE StockQuantity > 100
UNION
SELECT ProductID FROM Inventory WHERE StockQuantity <= 100;

-- SELECT LOCK IN SHARE MODE: Lock rows with stock quantity greater than 100 for read access
BEGIN TRANSACTION;
SELECT ProductID FROM Inventory WHERE StockQuantity > 100 LOCK IN SHARE MODE;
-- Perform reads on the selected rows
COMMIT TRANSACTION;

83. What is the difference between a UNION and a SELECT … FROM … WHERE …?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM ... WHERE ...: The SELECT ... FROM ... WHERE ... clause is used to retrieve data from a table based on specified conditions.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate

 queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- SELECT ... FROM ... WHERE ...: Retrieve employees from the IT department with a salary greater than 50,000
SELECT Name, Department
FROM Employees
WHERE Salary > 50000 AND Department = 'IT';

84: What is the difference between a UNION and a SELECT … FROM … GROUP BY …?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM ... GROUP BY ...: The SELECT ... FROM ... GROUP BY ... clause is used to group rows based on a specified column and allows applying aggregate functions to each group.

Example:
Suppose we have a table called Orders with columns Product, Quantity, and Price.

-- UNION: Combine two separate queries and remove duplicates
SELECT Product, Quantity FROM Orders WHERE Price >= 100
UNION
SELECT Product, Quantity FROM Orders WHERE Price < 100;

-- SELECT ... FROM ... GROUP BY ...: Grouping orders by product and calculating total quantity for each product
SELECT Product, SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY Product;

85. What is the difference between a UNION and a SELECT … FROM … HAVING …?

UNION: As explained earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM ... HAVING ...: The SELECT ... FROM ... HAVING ... clause is used to filter the results of a GROUP BY query based on aggregate function conditions.

Example:
Suppose we have a table called Orders with columns Product, Quantity, and Price.

-- UNION: Combine two separate queries and remove duplicates
SELECT Product, Quantity FROM Orders WHERE Price >= 100
UNION
SELECT Product, Quantity FROM Orders WHERE Price < 100;

-- SELECT ... FROM ... HAVING ...: Grouping orders by product and filtering results to include products with a total quantity greater than 50
SELECT Product, SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY Product
HAVING SUM(Quantity) > 50;

86. What is the difference between a UNION and a SELECT … FROM … ORDER BY …?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM ... ORDER BY ...: The SELECT ... FROM ... ORDER BY ... clause is used to sort the result set based on one or more columns.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- SELECT ... FROM ... ORDER BY ...: Retrieve employees and order them by salary in descending order
SELECT Name, Department
FROM Employees
ORDER BY Salary DESC;

87. What is the difference between a UNION and a SELECT … FROM … LIMIT …?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM ... LIMIT ...: The SELECT ... FROM ... LIMIT ... clause is used to restrict the number of rows returned by a SELECT query.

Example:
Suppose we have a table called Products with columns ProductID and ProductName.

-- UNION: Combine two separate queries and remove duplicates
SELECT ProductID FROM Products WHERE ProductName LIKE '%Phone%'
UNION
SELECT ProductID FROM Products WHERE ProductName LIKE '%Laptop%';

-- SELECT ... FROM ... LIMIT ...: Retrieve the top 10 products containing 'Phone' in their name
SELECT ProductID FROM Products WHERE ProductName LIKE '%Phone%' LIMIT 10;

88. What is the difference between a UNION and a SELECT … FROM … OFFSET …?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM ... OFFSET ...: The SELECT ... FROM ... OFFSET ... clause is used to skip a specified number of rows before starting to return rows from the SELECT query.

Example:
Suppose we have a table called Products with columns ProductID and ProductName.

-- UNION: Combine two separate queries and remove duplicates
SELECT ProductID FROM Products WHERE ProductName LIKE '%Phone%'
UNION
SELECT ProductID FROM Products WHERE ProductName LIKE '%Laptop%';

-- SELECT ... FROM ... OFFSET ...: Retrieve products containing 'Phone' in their name, starting from the 11th row
SELECT ProductID FROM Products WHERE ProductName LIKE '%Phone%' OFFSET 10;

89. What is the difference between a UNION and a SELECT … FROM … FETCH FIRST … ROWS ONLY?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FETCH FIRST ... ROWS ONLY: The SELECT ... FROM … FETCH FIRST ... ROWS ONLY clause is used to limit the number of rows returned by a SELECT query in a standard SQL way.

Example:
Suppose we have a table called Products with columns ProductID and ProductName.

-- UNION: Combine two separate queries and remove duplicates
SELECT ProductID FROM Products WHERE ProductName LIKE '%Phone%'
UNION
SELECT ProductID FROM Products WHERE ProductName LIKE '%Laptop%';

-- SELECT ... FROM … FETCH FIRST ... ROWS ONLY: Retrieve the first 10 products containing 'Phone' in their name
SELECT ProductID FROM Products WHERE ProductName LIKE '%Phone%' FETCH FIRST 10 ROWS ONLY;

90. What is the difference between a UNION and a SELECT … FROM … FETCH NEXT … ROWS ONLY?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FETCH NEXT ... ROWS ONLY: The SELECT ... FROM … FETCH NEXT ... ROWS ONLY clause is used to limit the number of rows returned by a SELECT query, starting from the current position of the cursor.

Example:
Suppose we have a table called Products with columns ProductID and ProductName.

-- UNION: Combine two separate queries and remove duplicates
SELECT ProductID FROM Products WHERE ProductName LIKE '%Phone%'
UNION
SELECT ProductID FROM Products WHERE ProductName LIKE '%Laptop%';

-- SELECT ... FROM … FETCH NEXT ... ROWS ONLY: Retrieve the next 10 products containing 'Phone' in their name
DECLARE @Offset INT = 10;
SELECT ProductID FROM Products WHERE ProductName LIKE '%Phone%' ORDER BY ProductID OFFSET @Offset ROWS FETCH NEXT 10 ROWS ONLY;

91. What is the difference between a UNION and a SELECT … FROM … FETCH FIRST … ROWS WITH TIES?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FETCH FIRST ... ROWS WITH TIES: The SELECT ... FROM … FETCH FIRST ... ROWS WITH TIES clause is used to return additional rows with the same sort key as the last row when using the ORDER BY clause.

Example:
Suppose we have a table called Scores with columns PlayerName and Score.

-- UNION: Combine two separate queries and remove duplicates
SELECT PlayerName, Score FROM Scores WHERE Score > 100 ORDER BY Score DESC
UNION
SELECT PlayerName, Score FROM Scores WHERE Score <= 100 ORDER BY Score DESC;

-- SELECT ... FROM … FETCH FIRST ... ROWS WITH TIES: Retrieve players with scores greater than 100, including those with the same top score
SELECT PlayerName, Score FROM Scores WHERE Score > 100 ORDER BY Score DESC FETCH FIRST 1 ROWS WITH TIES;

92. What is the difference between a UNION and a SELECT … FROM … FETCH NEXT … ROWS WITH TIES?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FETCH NEXT ... ROWS WITH TIES: The SELECT ... FROM … FETCH NEXT ... ROWS WITH TIES clause is used to return additional rows with the same sort key as the last row when using the ORDER BY clause, starting from the current position of the cursor.

Example:
Suppose we have a table called Scores with columns PlayerName and Score.

-- UNION: Combine two separate queries and remove duplicates
SELECT PlayerName, Score FROM Scores WHERE Score > 100 ORDER BY Score DESC
UNION
SELECT PlayerName, Score FROM Scores WHERE Score <= 100 ORDER BY Score DESC;

-- SELECT ... FROM … FETCH NEXT ... ROWS WITH TIES: Retrieve players with scores greater than 100, including those with the same top score
DECLARE @TopScore INT = 100;
SELECT PlayerName, Score FROM Scores WHERE Score > @TopScore ORDER BY Score DESC FETCH NEXT 1 ROWS WITH TIES;

93. What is the difference between a UNION and a SELECT … FROM … FOR JSON PATH?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FOR JSON PATH: The SELECT ... FROM … FOR JSON PATH clause is used to return query results in JSON format.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- SELECT ... FROM … FOR JSON PATH: Retrieve employees and return the result in JSON format
SELECT Name, Department FROM Employees WHERE Salary > 50000 FOR JSON PATH;

94: What is the difference between a UNION and a SELECT … FROM … FOR JSON AUTO?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FOR JSON AUTO: The SELECT ... FROM … FOR JSON AUTO clause is used to automatically structure the JSON output based on the result set’s table structure.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- SELECT ... FROM … FOR JSON AUTO: Retrieve employees and automatically generate JSON output
SELECT Name, Department FROM Employees WHERE Salary > 50000 FOR JSON AUTO;

95. What is the difference between a UNION and a SELECT … FROM … FOR XML PATH?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FOR XML PATH: The SELECT ... FROM … FOR XML PATH clause is used to return query results in XML format.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- SELECT ... FROM … FOR XML PATH: Retrieve employees and return the result in XML format
SELECT Name, Department FROM Employees WHERE Salary > 50000 FOR XML PATH;

96. What is the difference between a UNION and a SELECT … FROM … FOR XML AUTO?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FOR XML AUTO: The SELECT ... FROM … FOR XML AUTO clause is used to automatically structure the XML output based on the result set’s table structure.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- SELECT ... FROM … FOR XML AUTO: Retrieve employees and automatically generate XML output
SELECT Name, Department FROM Employees WHERE Salary > 50000 FOR XML AUTO;

97. What is the difference between a UNION and a SELECT … FROM … FOR XML RAW?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FOR XML RAW: The SELECT ... FROM … FOR XML RAW clause is used to return query results in XML format, where each row is represented as an element with an attribute for each column.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- SELECT ... FROM … FOR XML RAW: Retrieve employees and return the result in XML format with each row as an element
SELECT Name, Department FROM Employees WHERE Salary > 50000 FOR XML RAW;

98. What is the difference between a UNION and a SELECT … FROM … FOR XML EXPLICIT?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FOR XML EXPLICIT: The SELECT ... FROM … FOR XML EXPLICIT clause is used to specify the XML output explicitly using path expressions.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- SELECT ... FROM … FOR XML EXPLICIT: Retrieve employees and explicitly specify the XML output format
SELECT Name, Department FROM Employees WHERE Salary > 50000 FOR XML EXPLICIT;

99. What is the difference between a UNION and a SELECT … FROM … FOR XML AUTO, ELEMENTS?

UNION: As previously explained, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FOR XML AUTO, ELEMENTS: The SELECT ... FROM … FOR XML AUTO, ELEMENTS clause is used to return query results in XML format, where each row is represented as an element with nested elements for each column.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- SELECT ... FROM … FOR XML AUTO, ELEMENTS: Retrieve employees and return the result in XML format with nested elements
SELECT Name, Department FROM Employees WHERE Salary > 50000 FOR XML AUTO, ELEMENTS;

100. What is the difference between a UNION and a SELECT … FROM … FOR XML PATH, ELEMENTS?

UNION: As described earlier, UNION combines the results of two or more SELECT statements vertically, removing duplicates.

SELECT ... FROM … FOR XML PATH, ELEMENTS: The SELECT ... FROM … FOR XML PATH, ELEMENTS clause is used to return query results in XML format, where each row is represented as an element with nested elements for each column, and the root element is specified.

Example:
Suppose we have a table called Employees with columns Name, Department, and Salary.

-- UNION: Combine two separate queries and remove duplicates
SELECT Name, Department FROM Employees WHERE Salary > 50000
UNION
SELECT Name, Department FROM Employees WHERE Salary <= 50000;

-- SELECT ... FROM … FOR XML PATH, ELEMENTS: Retrieve employees and return the result in XML format with a specified root element
SELECT Name, Department FROM Employees WHERE Salary > 50000 FOR XML PATH('Employee'), ELEMENTS;

MCQ Questions

1. Which keyword is used to retrieve data from a table in T-SQL?

a) SELECT
b) FROM
c) WHERE
d) ORDER BY
Answer: a) SELECT

2. Which keyword is used to filter data in a T-SQL query?

a) SELECT
b) FROM
c) WHERE
d) GROUP BY
Answer: c) WHERE

3. Which keyword is used to specify a table in a T-SQL query?

a) SELECT
b) FROM
c) WHERE
d) TABLE
Answer: b) FROM

4. Which keyword is used to sort data in a T-SQL query?

a) SELECT
b) FROM
c) WHERE
d) ORDER BY
Answer: d) ORDER BY

5. Which keyword is used to insert data into a table in T-SQL?

a) SELECT
b) INSERT
c) VALUES
d) INTO
Answer: b) INSERT

6. Which keyword is used to update data in a table in T-SQL?

a) SELECT
b) UPDATE
c) SET
d) WHERE
Answer: b) UPDATE

7. Which keyword is used to delete data from a table in T-SQL?

a) SELECT
b) DELETE
c) FROM
d) WHERE
Answer: b) DELETE

8. Which keyword is used to join two or more tables in T-SQL?

a) SELECT
b) FROM
c) JOIN
d) WHERE
Answer: c) JOIN

9. Which T-SQL function is used to find the maximum value in a column?

a) MAX
b) MIN
c) AVG
d) SUM
Answer: a) MAX

10. Which T-SQL function is used to find the minimum value in a column?

a) MAX
b) MIN
c) AVG
d) SUM
Answer: b) MIN

11. Which T-SQL function is used to calculate the average value of a column?

a) MAX
b) MIN
c) AVG
d) SUM
Answer: c) AVG

12. Which T-SQL function is used to calculate the sum of values in a column?

a) MAX
b) MIN
c) AVG
d) SUM
Answer: d) SUM

13. Which T-SQL keyword is used to group data in a query?

a) SELECT
b) FROM
c) GROUP BY
d) ORDER BY
Answer: c) GROUP BY

14. Which T-SQL clause is used to filter aggregated data in a query?

a) SELECT
b) FROM
c) WHERE
d) HAVING
Answer: d) HAVING

15. Which T-SQL keyword is used to specify a distinct value in a query?

a) SELECT
b) FROM
c) DISTINCT
d) WHERE
Answer: c) DISTINCT

16. Which T-SQL clause is used to limit the number of rows returned in a query?

a) SELECT
b) FROM
c) WHERE
d) TOP
Answer: d) TOP

17. Which T-SQL function is used to concatenate strings?

a) CONCAT
b) STRING
c) CONCATENATE
d) ADD
Answer: a) CONCAT

18. Which T-SQL function is used to convert a value to uppercase?

a) UPPER
b) LOWER
c) INITCAP
d) CASE
Answer: a) UPPER

19. Which T-SQL function is used to extract a substring from a string?

a) SUBSTRING
b) LEFT
c) RIGHT
d) MID
Answer: a) SUBSTRING

20. Which T-SQL function is used to get the current date and time?

a) GETDATE
b) CURRENT_DATE
c) NOW
d) SYSDATE
Answer: a) GETDATE

21. Which T-SQL function is used to round a numeric value to a specified decimal place?

a) ROUND
b) CEILING
c) FLOOR
d) TRUNCATE
Answer: a) ROUND

22. Which T-SQL function is used to get the length of a string?

a) LENGTH
b) LEN
c) SIZE
d) COUNT
Answer: b) LEN

23. Which T-SQL keyword is used to check for NULL values?

a) NULL
b) EMPTY
c) IS NULL
d) NOT NULL
Answer: c) IS NULL

24. Which T-SQL keyword is used to sort data in descending order?

a) ASC
b) DESC
c) SORT
d) ORDER DESC
Answer: b) DESC

25. Which T-SQL clause is used to specify conditions in a query?

a) SELECT
b) WHERE
c) FROM
d) HAVING
Answer: b) WHERE

26. Which T-SQL statement is used to create a new table?

a) CREATE TABLE
b) INSERT INTO
c) ALTER TABLE
d) UPDATE TABLE
Answer: a) CREATE TABLE

27. Which T-SQL statement is used to modify an existing table?

a) CREATE TABLE
b) INSERT INTO
c) ALTER TABLE
d) UPDATE TABLE
Answer: c) ALTER TABLE

28. Which T-SQL statement is used to add new rows to a table?

a) CREATE TABLE
b) INSERT INTO
c) ALTER TABLE
d) UPDATE TABLE
Answer: b) INSERT INTO

29. Which T-SQL statement is used to update existing rows in a table?

a) CREATE TABLE
b) INSERT INTO
c) ALTER TABLE
d) UPDATE TABLE
Answer: d) UPDATE TABLE

30. Which T-SQL statement is used to delete rows from a table?

a) CREATE TABLE
b) INSERT INTO
c) ALTER TABLE
d) DELETE FROM
Answer: d) DELETE FROM