1. What is SQL?

2. What is a database?

3. What is a table?

4. What is a view?

5. What is a primary key?

6. What is a foreign key?

7. What is a constraint?

8. What is a stored procedure?

9. What is a trigger?

10. What is a join?

11. What is normalization?

12. What is denormalization?

13. What is a transaction?

14. What is indexing?

15. What is a subquery?

16. What is a union?

17. What is a case statement?

18. What is a group by clause?

19. What is a having clause?

20. What is a rank function?

21. Explain lag and lead window functions with examples.

22. What is a temporary table?

23. What is a common table expression?

24. What is a transaction log?

25. What is data warehousing?

26. What is a data mart?

27. What is OLAP?

28. What is ETL?

29. What is a database schema?

30. Can you explain the difference between INSERT, UPDATE, and DELETE statements in SQL?

31. How do you prevent SQL injection attacks when using CRUD operations?

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

33. What is a self-join in SQL, and when would you use it?

34. Can you explain the difference between a left join and a right join?

35. What is a full outer join, and when would you use it?

36. What is a correlated subquery, and when would you use it?
37. How do you use the COUNT function in SQL, and what does it do?

38. How do you use the SUM function in SQL, and what does it do?

39. How do you use the AVG function in SQL, and what does it do?

40. How do you use the MAX function in SQL, and what does it do?

41. How do you use the MIN function in SQL, and what does it do?

42. How do you use the GROUP_CONCAT function in SQL, and what does it do?

43. How do you use the DENSE_RANK function in SQL, and what does it do?

44. How do you use the ON keyword in a JOIN statement in SQL?

45. How do you use the UNION ALL operator in SQL, and what does it do?

46. What is a cross join in SQL, and how is it used?

47. How do you use the NATURAL JOIN keyword in SQL, and what does it do?

48. What is a check constraint in SQL, and how is it used?

49. What is a unique constraint in SQL, and how is it used?

50. What is a default constraint in SQL, and how is it used?

51. What is a null constraint in SQL, and how is it used?

52. How do you add a table constraint to an existing table in SQL?

53. How do you modify or remove a table constraint in SQL?

54. Explain the row_number window function with an example.

55. Can you update or delete data in a view in SQL?

56. What is the difference between a view and a table in SQL?

57. What are the properties of a transaction in SQL?

58. What is a savepoint in SQL, and when would you use it?

60. What are window functions in SQL, and how do they differ from regular aggregate functions?

61. What are some common uses for window functions in SQL?

62. How do you use a window function in SQL?

63. What is a Common Table Expression (CTE) in SQL, and how does it differ from a subquery?

64. What is a recursive CTE in SQL, and when would you use it?

65. What is string transformation in SQL, and how is it used?

66. What is a regular expression (regex) in SQL, and how is it used?

67. What are some common regex patterns used in SQL?

68. How can you use regex to validate data in SQL?

69. What are some potential drawbacks of using regex in SQL?

70. What is data modeling in SQL, and why is it important?

71. What is an entity-relationship (ER) diagram?

72. What are some common types of relationships between entities in SQL data modeling?

73. How do you represent a one-to-many relationship in an ER diagram?

74. How do you represent a many-to-many relationship in an ER diagram?

75. What is a data dictionary in SQL data modeling, and how is it used?

76. Given a large dataset with millions of rows, how would you approach identifying and removing duplicates?

77. How would you perform a time series analysis in SQL?

78. Given a dataset with missing values, how would you approach imputing those missing values?

79. assume or make a tables solve the queries like Fetch all the employees who are not working on any project?

[Link] make a table then Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less
than or equal to 10000.

81. first make a table then Write an SQL query to fetch all the Employee details from the EmployeeDetails table who joined in
the Year 2020.

82. first make a table then Write an SQL query to fetch all employee records from the EmployeeDetails table who have a salary
record in the EmployeeSalary table.

83. first make a table then Write an SQL query to fetch the project-wise count of employees sorted by project’s count in
descending order.

84. first make a table then Write an SQL query to fetch duplicate records from EmployeeDetails (without considering the
primary key– EmpId).

84. first make a table then Write an SQL query to fetch duplicate records from EmployeeDetails

85. first make a table then Write an SQL query to remove duplicates from a table without using a temporary table.

85. first make a table then Write an SQL query to remove duplicates from a table

86. first make a table then Write SQL query to find the 3rd highest salary from a table without using the TOP/limit keyword.

87. first make a table then Write an SQL query to report the movies with an odd-numbered ID and a description that is not
"boring". Return the result table ordered by rating in descending order.

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

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

90. What are the different types of normalization?

91. What is the difference between DELETE and TRUNCATE in SQL?

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

93. What is the difference between the HAVING clause and the WHERE clause?

94. What is ACID in the context of database transactions?

95. What is a deadlock?

96. What is the difference between a database and a schema?

97. What is the difference between a temporary table and a table variable?

98. What is the purpose of the GROUP BY clause?

99. What is the difference between CHAR and VARCHAR data types?

100. What is a stored procedure?

101. What is a view?

102. What is the difference between a cross join and an inner join?

103. What is the purpose of the COMMIT statement?

104. What is the purpose of the ROLLBACK statement?

[Link] is the purpose of the NULL value in SQL?

106. What is the difference between a view and a materialized view?

107. What is a correlated subquery?

108. What is the purpose of the DISTINCT keyword?

109. What is the difference between the CHAR and VARCHAR data types?

110. What is the difference between the IN and EXISTS operators?

111. What is the purpose of the TRIGGER statement?

112. What is the difference between a unique constraint and a unique index?

113. What is the purpose of the TOP or LIMIT clause?

114. What is the difference between the UNION and JOIN operators?

115. What is the difference between a primary key and a candidate key?

116. What is the purpose of the GRANT statement?

117. What is a correlated update?

118. What is the purpose of the CASE statement?

119. What is the purpose of the COALESCE function?

120. What is the purpose of the ROW_NUMBER() function?

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

122. What is the purpose of the CASCADE DELETE constraint?

123. What is the purpose of the ALL keyword in SQL?

124. What is the difference between the EXISTS and NOT EXISTS operators?

125. What is the purpose of the CROSS APPLY operator?

126. What is a self-join?

127. What is an ALIAS command?

128. Why are SQL functions used?

129. What is the difference between “Stored Procedure” and “Function”?

130. What is the difference between DELETE and TRUNCATE and DROP in SQL?

131. What is the difference between a “Local Temporary Table” and “Global Temporary Table”?

132. What are super, primary, candidate and foreign keys?

133. What is the difference between primary key and unique constraints?

134. What is Identity?

135. What is a view in SQL? How to create one

136. What are the uses of view?

137. What are indexes?

138. What is the difference between Trigger and Stored Procedure?

139. What are indexes?

140. What are clustered and non-clustered Indexes?

141. What are the Advantages of SQL?

142. What are properties of a transaction?

143. What is a Database Lock & type of locks ?

144. What is a Composite Key & Composite Primary Key ?

145. What are wild cards used in database for Pattern Matching ?

146. What is a materialized view?

147. What are user defined datatypes and when you should go for them?

148. How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

149. Define candidate key, alternate key, composite key

150. What are defaults? Is there a column to which a default can't be bound?

151. How to restart SQL Server in single user mode? How to start SQL Server in minimal configuration mode?

152. What is Data Mining?

153. Explain the differences between structured data and unstructured data.

154. What is the relationship of ODBC, OLE DB, and ADO?

155. What are the advantages of using stored procedures?

156. What are stored procedures, and how do they differ from triggers?

===================================================================================================

157. SQL Query to find second highest salary of Employee

158. SQL Query to find Max Salary from each department.

159. Write SQL Query to display current date.

160. Write an SQL Query to check whether date passed to Query is date of given format or not.

161. Write a SQL Query to print the name of distinct employee whose DOB is between 01/01/1960 to 31/12/1975.

162. Write an SQL Query find number of employees according to gender whose DOB is between 01/01/1960 to 31/12/1975.

163. Write an SQL Query to find employee whose Salary is equal or greater than 10000.

164. Write an SQL Query to find name of employee whose name Start with ‘M’

165. find all Employee records containing the word "Joe", regardless of whether it was stored as JOE, Joe, or joe.

166. Write a SQL Query to find year from date.

167. To fetch ALTERNATE records from a table. (EVEN NUMBERED)

168. To select ALTERNATE records from a table. (ODD NUMBERED)

169. Find the 3rd MAX salary in the emp table.

170. Find the 3rd MIN salary in the emp table.

171. Select FIRST n records from a table.

172. Select LAST n records from a table.

173. List dept no., Dept name for all the departments in which there are no employees in the department.

174. How to get 3 Max salaries ?

175. How to get 3 Min salaries ?

176. How to get nth max salaries ?

177. Select DISTINCT RECORDS from emp table.

178. How to delete duplicate rows in a table?

179. Count of number of employees in department wise.

180. Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every
employee?

181. Select all record from emp table where deptno =10 or 40.

182. Select all record from emp table where deptno=30 and sal>1500.

183. Select all record from emp where job not in SALESMAN or CLERK.

184. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.

185. Select all records where ename starts with ‘S’ and its lenth is 6 char.

186. Select all records where ename may be any no of character but it should end with ‘R’.

187. Count MGR and their salary in emp table.

188. In emp table add comm+sal as total sal.

189. Select any salary<3000 from emp table.

190. Select all salary<3000 from emp table.191.

192. Select all the employee group by deptno and sal in descending order.

193. How can I create an empty table emp1 with same structure as emp?

194. How to retrive record where sal between 1000 to 2000?

[Link] all records where dept no of both emp and dept table matches.

[Link] there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common
records only once?

197. How to fetch only common records from two tables emp and emp1?

198. How can I retrive all records of emp1 those should not present in emp2?

199. Count the totalsa deptno wise where more than 2 employees exist.

200. Display the names of employees who are working in the company for the past 5 years.

[Link] the list of employees who have joined the company before 30th June 90 or after 31st dec 90.

202. Display the names of employees working in department number 10 or 20 or 40 or employees working as clerks, salesman
or analyst.

[Link] the names of employees whose name starts with alphabet S.

[Link] employee names for employees whose name ends with alphabet.

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

[Link] between SQL and NoSQL databases.

[Link] is an index?

[Link] do you handle NULL values in SQL?

[Link] the differences between the CHARINDEX and PATINDEX functions?

[Link] is a natural join and when would you use it?
Creating a detailed explanation for each of these 209 questions along with 3 examples and syntax for each would
result in a very extensive document. To manage this effectively, let’s start with a subset of questions and their
corresponding explanations, syntax, and examples. Let me know if you'd like the answers to be organized in a
specific way or provided incrementally. For now, I'll tackle the first 10 questions as a sample.

1. What is SQL?

SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational
databases. It allows you to perform operations such as querying, updating, inserting, and deleting data.

Examples:

-- Example 1: Selecting all records from a table

SELECT * FROM Employees;

-- Example 2: Inserting data into a table

INSERT INTO Employees (ID, Name, Position) VALUES (1, 'John Doe', 'Manager');

-- Example 3: Updating data in a table

UPDATE Employees SET Position = 'Senior Manager' WHERE ID = 1;

2. What is a Database?

A database is an organized collection of data stored electronically. It allows efficient data retrieval, management, and
updating.

Examples:

-- Example 1: Creating a database

CREATE DATABASE CompanyDB;

-- Example 2: Using a database

USE CompanyDB;

-- Example 3: Dropping a database

DROP DATABASE CompanyDB;

3. What is a Table?

A table is a structured format to store data in rows and columns. Each column has a name and data type, and each row
represents a record.

Examples:

-- Example 1: Creating a table

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

-- Example 2: Inserting data into a table

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

-- Example 3: Selecting data from a table

SELECT * FROM Employees;

4. What is a View?
A view is a virtual table based on the result set of an SQL query. It does not store data itself but provides a way to
simplify complex queries.

Examples:

-- Example 1: Creating a view

CREATE VIEW EmployeeNames AS
SELECT Name FROM Employees;

-- Example 2: Using a view

SELECT * FROM EmployeeNames;

-- Example 3: Dropping a view

DROP VIEW EmployeeNames;

5. What is a Primary Key?

A primary key uniquely identifies each record in a table. It must contain unique values and cannot be NULL.

Examples:

-- Example 1: Defining a primary key

CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);

-- Example 2: Adding a primary key to an existing table

ALTER TABLE Employees ADD PRIMARY KEY (ID);

-- Example 3: Dropping a primary key

ALTER TABLE Employees DROP PRIMARY KEY;

6. What is a Foreign Key?

A foreign key links two tables by referring to the primary key in another table.

Examples:

-- Example 1: Creating a foreign key

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
EmployeeID INT,
FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);

-- Example 2: Adding a foreign key to an existing table

ALTER TABLE Orders ADD FOREIGN KEY (EmployeeID) REFERENCES Employees(ID);

-- Example 3: Dropping a foreign key

ALTER TABLE Orders DROP FOREIGN KEY FK_Employee;

7. What is a Constraint?

A constraint enforces rules on data in a table, such as PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and
CHECK.

Examples:

-- Example 1: Adding a NOT NULL constraint

CREATE TABLE Products (
 ProductID INT NOT NULL,
ProductName VARCHAR(50) NOT NULL
);

-- Example 2: Adding a CHECK constraint

ALTER TABLE Products ADD CONSTRAINT chk_price CHECK (Price > 0);

-- Example 3: Dropping a constraint

ALTER TABLE Products DROP CONSTRAINT chk_price;

8. What is a Stored Procedure?

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

Examples:

-- Example 1: Creating a stored procedure

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
SELECT * FROM Employees;
END;

-- Example 2: Executing a stored procedure

EXEC GetAllEmployees;

-- Example 3: Dropping a stored procedure

DROP PROCEDURE GetAllEmployees;

9. What is a Trigger?

A trigger is a set of actions automatically executed in response to certain events on a table (e.g., INSERT, UPDATE,
DELETE).

Examples:

-- Example 1: Creating a trigger

CREATE TRIGGER trg_AfterInsert ON Employees
AFTER INSERT
AS
BEGIN
PRINT 'A new record was added to Employees';
END;

-- Example 2: Updating a table to fire the trigger

INSERT INTO Employees (ID, Name, Position) VALUES (2, 'Bob', 'Analyst');

-- Example 3: Dropping a trigger

DROP TRIGGER trg_AfterInsert;

10. What is a Join?

A join combines rows from two or more tables based on a related column.

Examples:

-- Example 1: Inner join

SELECT [Link], [Link]
FROM Employees
INNER JOIN Departments ON [Link] = [Link];

-- Example 2: Left join

SELECT [Link], [Link]
FROM Employees
LEFT JOIN Departments ON [Link] = [Link];

-- Example 3: Right join

SELECT [Link], [Link]
FROM Employees
RIGHT JOIN Departments ON [Link] = [Link];

Would you like me to continue with the next set of questions? Let me know your preferences!

Great! Let’s continue with the next set of questions. Here are explanations and examples for questions 11 through 20.

11. What is Normalization?

Normalization is the process of organizing data to reduce redundancy and dependency by dividing large tables into
smaller ones and using relationships between them.

Examples:

-- Example 1: Creating tables in normalized form

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
DepartmentID INT
);

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);

-- Example 2: Inserting data into normalized tables

INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES (1, 'John Doe', 10);
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES (10, 'HR');

-- Example 3: Selecting data from normalized tables

SELECT [Link], [Link]
FROM Employees
INNER JOIN Departments ON [Link] = [Link];

12. What is Denormalization?

Denormalization is the process of merging tables to reduce the complexity of database queries, often done for
performance reasons, at the expense of additional storage and potential data redundancy.

Examples:

-- Example 1: Denormalized table (combining Employees and Departments)

CREATE TABLE EmployeeDetails (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
DepartmentName VARCHAR(100)
);

-- Example 2: Inserting data into denormalized table

INSERT INTO EmployeeDetails (EmployeeID, Name, DepartmentName) VALUES (1, 'John Doe', 'HR');

-- Example 3: Selecting data from denormalized table

SELECT Name, DepartmentName FROM EmployeeDetails;
13. What is a Transaction?

A transaction is a sequence of operations performed as a single unit of work. Transactions ensure data integrity by
following ACID (Atomicity, Consistency, Isolation, Durability) properties.

Examples:

-- Example 1: Starting a transaction

BEGIN TRANSACTION;

-- Example 2: Committing a transaction

COMMIT;

-- Example 3: Rolling back a transaction

ROLLBACK;

14. What is Indexing?

An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional
space and slower write operations.

Examples:

-- Example 1: Creating an index

CREATE INDEX idx_employee_name ON Employees (Name);

-- Example 2: Dropping an index

DROP INDEX idx_employee_name;

-- Example 3: Using an index to improve query performance

SELECT * FROM Employees WHERE Name = 'John Doe';

15. What is a Subquery?

A subquery is a query nested inside another query, which can be used in SELECT, INSERT, UPDATE, or DELETE
statements to retrieve data.

Examples:

-- Example 1: Subquery in SELECT statement

SELECT Name FROM Employees WHERE EmployeeID = (SELECT EmployeeID FROM Employees WHERE Name =
'John Doe');

-- Example 2: Subquery in WHERE clause

SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Example 3: Subquery in FROM clause

SELECT * FROM (SELECT Name, Salary FROM Employees) AS EmployeeDetails WHERE Salary > 5000;

16. What is a Union?

The UNION operator combines the results of two or more SELECT statements into a single result set, removing
duplicates.

Examples:

-- Example 1: Using UNION to combine two SELECT statements

SELECT Name FROM Employees WHERE DepartmentID = 10
UNION
SELECT Name FROM Employees WHERE DepartmentID = 20;
-- Example 2: Using UNION with different columns
SELECT Name, Salary FROM Employees WHERE DepartmentID = 10
UNION
SELECT Name, Position FROM Employees WHERE DepartmentID = 20;

-- Example 3: Using UNION to combine results from different tables

SELECT Name FROM Employees
UNION
SELECT CustomerName FROM Customers;

17. What is a CASE Statement?

A CASE statement allows you to perform conditional logic in SQL queries. It can be used in SELECT, UPDATE, or
DELETE statements to return different results based on conditions.

Examples:

-- Example 1: CASE in SELECT statement

SELECT Name,
CASE
WHEN Salary > 5000 THEN 'High'
WHEN Salary <= 5000 THEN 'Low'
END AS SalaryCategory
FROM Employees;

-- Example 2: CASE in WHERE clause

SELECT Name FROM Employees
WHERE CASE WHEN Salary > 5000 THEN 1 ELSE 0 END = 1;

-- Example 3: CASE with multiple conditions

SELECT Name, CASE
WHEN Salary > 5000 THEN 'High'
WHEN Salary BETWEEN 3000 AND 5000 THEN 'Medium'
ELSE 'Low'
END AS SalaryCategory
FROM Employees;

18. What is a GROUP BY Clause?

The GROUP BY clause groups rows sharing the same values in specified columns and allows aggregate functions
(COUNT, SUM, AVG, etc.) to be applied to each group.

Examples:

-- Example 1: Using GROUP BY with COUNT function

SELECT DepartmentID, COUNT(*) AS EmployeeCount FROM Employees
GROUP BY DepartmentID;

-- Example 2: Using GROUP BY with SUM function

SELECT DepartmentID, SUM(Salary) AS TotalSalary FROM Employees
GROUP BY DepartmentID;

-- Example 3: Using GROUP BY with HAVING clause

SELECT DepartmentID, AVG(Salary) AS AverageSalary FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 4000;

19. What is a HAVING Clause?

The HAVING clause is used to filter groups created by the GROUP BY clause, allowing you to apply conditions to
aggregate results.
Examples:

-- Example 1: HAVING with COUNT function

SELECT DepartmentID, COUNT(*) AS EmployeeCount FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

-- Example 2: HAVING with SUM function

SELECT DepartmentID, SUM(Salary) AS TotalSalary FROM Employees
GROUP BY DepartmentID
HAVING SUM(Salary) > 20000;

-- Example 3: HAVING with AVG function

SELECT DepartmentID, AVG(Salary) AS AverageSalary FROM Employees
GROUP BY DepartmentID
HAVING AVG(Salary) > 3000;

20. What is a Rank Function?

The RANK function is a window function that assigns a rank to each row within a partition of a result set, with gaps
between rank values for ties.

Examples:

-- Example 1: Using RANK() in a query

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

-- Example 2: Using RANK() with PARTITION BY

SELECT DepartmentID, Name, Salary, RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary
DESC) AS DepartmentRank
FROM Employees;

-- Example 3: Ranking employees by salary

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS EmployeeRank
FROM Employees
WHERE DepartmentID = 10;

These are the answers and examples for questions 11 to 20. Would you like me to continue with the next set of
questions? Let me know if you need anything specific!

Let's continue with the next set of questions! Here's the explanation and examples for questions 21 through 30.

21. Explain LAG and LEAD Window Functions with Examples.

The LAG and LEAD functions are window functions used to access data from a previous or next row in the same result
set, respectively.

Examples:

-- Example 1: Using LAG() to get the previous row's salary

SELECT Name, Salary, LAG(Salary, 1) OVER (ORDER BY Salary) AS PreviousSalary
FROM Employees;

-- Example 2: Using LEAD() to get the next row's salary

SELECT Name, Salary, LEAD(Salary, 1) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;

-- Example 3: Combining LAG() and LEAD() in one query

SELECT Name, Salary, LAG(Salary, 1) OVER (ORDER BY Salary) AS PreviousSalary,
LEAD(Salary, 1) OVER (ORDER BY Salary) AS NextSalary
FROM Employees;

22. What is a Temporary Table?

A temporary table is a table that is created and used temporarily within a session. It is dropped automatically when
the session ends or the connection is closed.

Examples:

-- Example 1: Creating a temporary table

CREATE TEMPORARY TABLE TempEmployee (
EmployeeID INT,
Name VARCHAR(100)
);

-- Example 2: Inserting data into a temporary table

INSERT INTO TempEmployee (EmployeeID, Name) VALUES (1, 'John Doe');

-- Example 3: Selecting data from a temporary table

SELECT * FROM TempEmployee;

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

A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a SELECT,
INSERT, UPDATE, or DELETE statement. CTEs are defined using the WITH keyword.

Examples:

-- Example 1: Simple CTE

WITH DepartmentCTE AS (
SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID
)
SELECT * FROM DepartmentCTE;

-- Example 2: CTE with recursive query

WITH RECURSIVE EmployeeHierarchy AS (
SELECT EmployeeID, ManagerID, Name
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT [Link], [Link], [Link]
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON [Link] = [Link]
)
SELECT * FROM EmployeeHierarchy;

-- Example 3: Using CTE in an UPDATE statement

WITH SalaryUpdate AS (
SELECT EmployeeID, Salary
FROM Employees
WHERE DepartmentID = 10
)
UPDATE Employees
SET Salary = Salary + 1000
FROM SalaryUpdate
WHERE [Link] = [Link];

24. What is a Transaction Log?

A transaction log is a record of all changes made to the database, including data modifications, so that the system can
be rolled back to a consistent state in case of failure.

Examples:

 Transaction logs are automatically maintained by the database system (no direct SQL examples).
 To access or query transaction logs, you might use system views like sys.fn_dblog in SQL Server.

25. What is Data Warehousing?

Data warehousing is the process of collecting and managing data from different sources to provide meaningful
business insights, often involving OLAP systems and large-scale data analysis.

Examples:

 Building a data warehouse often involves using ETL (Extract, Transform, Load) processes to populate the data
warehouse with data from operational databases.

-- Example 1: Creating a data warehouse schema

CREATE SCHEMA DataWarehouse;

-- Example 2: Creating a fact table in a data warehouse

CREATE TABLE FactSales (
SalesID INT PRIMARY KEY,
ProductID INT,
QuantitySold INT,
SaleDate DATE
);

-- Example 3: Inserting data into the fact table

INSERT INTO FactSales (SalesID, ProductID, QuantitySold, SaleDate)
VALUES (1, 101, 50, '2024-01-01');

26. What is a Data Mart?

A data mart is a subset of a data warehouse, typically focusing on a specific business area or department. It is used to
make data more accessible and faster to query for a specific group of users.

Examples:

-- Example 1: Creating a data mart schema

CREATE SCHEMA SalesDataMart;

-- Example 2: Creating a sales fact table in the data mart

CREATE TABLE SalesFact (
SaleID INT PRIMARY KEY,
ProductID INT,
QuantitySold INT,
SaleDate DATE
);

-- Example 3: Inserting data into the sales fact table

INSERT INTO SalesFact (SaleID, ProductID, QuantitySold, SaleDate)
VALUES (1, 101, 50, '2024-01-01');

27. What is OLAP (Online Analytical Processing)?

OLAP refers to a category of data processing that enables users to analyze data from multiple dimensions, often used
in data warehousing and business intelligence applications for complex queries.
Examples:

 OLAP systems support operations like slice, dice, pivot, and drill-down.

-- Example 1: Creating an OLAP cube for sales data (typically done through OLAP tools, not SQL
alone)
-- OLAP cubes are designed to support multidimensional queries.

-- Example 2: Example query for OLAP data from a star schema (simplified)
SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID;

-- Example 3: Using ROLLUP for OLAP-like functionality

SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID WITH ROLLUP;

28. What is ETL (Extract, Transform, Load)?

ETL refers to the process of extracting data from different sources, transforming it into a desired format, and loading
it into a destination database or data warehouse.

Examples:

-- Example 1: Extracting data from an operational database

SELECT * FROM Sales;

-- Example 2: Transforming data by converting sales data to monthly aggregation

SELECT MONTH(SaleDate) AS SaleMonth, SUM(QuantitySold) AS TotalSales
FROM Sales
GROUP BY MONTH(SaleDate);

-- Example 3: Loading the transformed data into the data warehouse

INSERT INTO [Link] (SaleMonth, TotalSales)
SELECT MONTH(SaleDate), SUM(QuantitySold)
FROM Sales
GROUP BY MONTH(SaleDate);

29. What is a Database Schema?

A database schema defines the structure of a database, including tables, columns, constraints, and relationships
between tables.

Examples:

-- Example 1: Creating a schema

CREATE SCHEMA HumanResources;

-- Example 2: Creating tables within a schema

CREATE TABLE [Link] (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
DepartmentID INT
);

-- Example 3: Selecting tables from a schema

SELECT * FROM [Link];

30. Difference Between INSERT, UPDATE, and DELETE Statements in SQL

 INSERT: Adds new rows to a table.

  UPDATE: Modifies existing rows in a table.
 DELETE: Removes rows from a table.

Examples:

-- Example 1: INSERT statement

INSERT INTO Employees (EmployeeID, Name, DepartmentID) VALUES (1, 'John Doe', 10);

-- Example 2: UPDATE statement

UPDATE Employees SET Salary = 6000 WHERE EmployeeID = 1;

-- Example 3: DELETE statement

DELETE FROM Employees WHERE EmployeeID = 1;

These are the answers and examples for questions 21 through 30. Would you like to continue with the next set? Let
me know!

Let's continue with the next set of questions (31-40) and provide explanations with examples!

31. How do you prevent SQL Injection attacks when using CRUD operations?

SQL Injection attacks occur when malicious users can inject SQL code into an application’s input fields. To prevent
this:

 Use prepared statements or parameterized queries.

 Escape user input properly.
 Avoid concatenating raw user input into SQL queries.

Examples:

-- Example 1: Using a prepared statement (PHP example)

$stmt = $pdo->prepare('SELECT * FROM Employees WHERE EmployeeID = :id');
$stmt->bindParam(':id', $employeeID);
$stmt->execute();

-- Example 2: Using parameterized queries in SQL Server

SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;

-- Example 3: Escaping input in SQL (avoid this, better to use parameterized queries)
SELECT * FROM Employees WHERE Name = 'O''Reilly'; -- Single quotes must be escaped

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

 INNER JOIN: Returns rows when there is a match in both tables.

 NATURAL JOIN: Automatically joins tables based on columns with the same name and compatible data
types.

Examples:

-- Example 1: Using INNER JOIN

SELECT [Link], [Link]
FROM Employees
INNER JOIN Departments ON [Link] = [Link];

-- Example 2: Using NATURAL JOIN (automatic column match based on same name)
SELECT [Link], [Link]
FROM Employees
NATURAL JOIN Departments;
33. What is a Self-Join in SQL, and When Would You Use It?

A self-join is a query where a table is joined with itself. It’s useful when you want to compare rows within the same
table.

Examples:

-- Example 1: Using self-join to find employees and their managers

SELECT [Link] AS Employee, [Link] AS Manager
FROM Employees e
LEFT JOIN Employees m ON [Link] = [Link];

-- Example 2: Self-join to find pairs of employees with the same department

SELECT [Link] AS Employee1, [Link] AS Employee2
FROM Employees e1
JOIN Employees e2 ON [Link] = [Link]
WHERE [Link] != [Link];

-- Example 3: Self-join to get the closest matches for salary

SELECT [Link] AS Employee1, [Link] AS Employee2
FROM Employees e1
JOIN Employees e2 ON [Link] = [Link]
WHERE [Link] != [Link];

34. Can You Explain the Difference Between a Left Join and a Right Join?

 LEFT JOIN: Returns all rows from the left table and matched rows from the right table. If there’s no match,
NULL values are returned from the right table.
 RIGHT JOIN: Returns all rows from the right table and matched rows from the left table. If there’s no
match, NULL values are returned from the left table.

Examples:

-- Example 1: LEFT JOIN

SELECT [Link], [Link]
FROM Employees
LEFT JOIN Departments ON [Link] = [Link];

-- Example 2: RIGHT JOIN

SELECT [Link], [Link]
FROM Employees
RIGHT JOIN Departments ON [Link] = [Link];

35. What is a Full Outer Join, and When Would You Use It?

A FULL OUTER JOIN returns all rows when there is a match in either the left or right table. If there’s no match,
NULL values are returned for missing rows.

Examples:

-- Example 1: Using FULL OUTER JOIN to combine all rows

SELECT [Link], [Link]
FROM Employees
FULL OUTER JOIN Departments ON [Link] = [Link];

-- Example 2: Returning rows with or without matching data

SELECT [Link], [Link]
FROM Employees e
FULL OUTER JOIN Departments d
ON [Link] = [Link];
36. What is a Correlated Subquery, and When Would You Use It?

A correlated subquery is a subquery that refers to a column from the outer query. The subquery is evaluated for
each row of the outer query.

Examples:

-- Example 1: Correlated subquery to find employees who earn more than the average salary in
their department
SELECT Name, Salary
FROM Employees e1
WHERE Salary > (
SELECT AVG(Salary)
FROM Employees e2
WHERE [Link] = [Link]
);

-- Example 2: Using a correlated subquery in a DELETE statement

DELETE FROM Employees
WHERE DepartmentID = (
SELECT DepartmentID
FROM Departments
WHERE DepartmentName = 'Sales'
);

-- Example 3: Correlated subquery for matching products with a price higher than the
department's average
SELECT ProductName, Price
FROM Products p
WHERE Price > (
SELECT AVG(Price)
FROM Products p2
WHERE [Link] = [Link]
);

37. How Do You Use the COUNT Function in SQL, and What Does It Do?

The COUNT function returns the number of rows that match a specified condition.

Examples:

-- Example 1: COUNT all rows in a table

SELECT COUNT(*) FROM Employees;

-- Example 2: COUNT rows with a condition

SELECT COUNT(*) FROM Employees WHERE DepartmentID = 10;

-- Example 3: COUNT distinct values in a column

SELECT COUNT(DISTINCT DepartmentID) FROM Employees;

38. How Do You Use the SUM Function in SQL, and What Does It Do?

The SUM function adds up the values in a numeric column.

Examples:

-- Example 1: SUM of all salaries

SELECT SUM(Salary) FROM Employees;

-- Example 2: SUM of salaries for a specific department

SELECT SUM(Salary) FROM Employees WHERE DepartmentID = 10;

-- Example 3: SUM of sales from an order table

SELECT SUM(Amount) FROM Sales WHERE OrderDate = '2024-01-01';
39. How Do You Use the AVG Function in SQL, and What Does It Do?

The AVG function calculates the average value of a numeric column.

Examples:

-- Example 1: Average salary of all employees

SELECT AVG(Salary) FROM Employees;

-- Example 2: Average salary in a specific department

SELECT AVG(Salary) FROM Employees WHERE DepartmentID = 10;

-- Example 3: Average sales amount per order

SELECT AVG(Amount) FROM Sales;

40. How Do You Use the MAX Function in SQL, and What Does It Do?

The MAX function returns the maximum value from a specified column.

Examples:

-- Example 1: Maximum salary in the Employees table

SELECT MAX(Salary) FROM Employees;

-- Example 2: Maximum sale amount from the Sales table

SELECT MAX(Amount) FROM Sales;

-- Example 3: Maximum order ID

SELECT MAX(OrderID) FROM Orders;

This concludes the explanation and examples for questions 31-40. Would you like to continue with the next set of
questions?

Let's continue with the next set of questions (41-50) and provide explanations with examples!

41. How Do You Use the MIN Function in SQL, and What Does It Do?

The MIN function returns the minimum value from a specified column.

Examples:

-- Example 1: Minimum salary in the Employees table

SELECT MIN(Salary) FROM Employees;

-- Example 2: Minimum sales amount in the Sales table

SELECT MIN(Amount) FROM Sales;

-- Example 3: Minimum order ID in the Orders table

SELECT MIN(OrderID) FROM Orders;

42. How Do You Use the GROUP_CONCAT Function in SQL, and What Does It Do?

The GROUP_CONCAT function combines values from multiple rows into a single string. It is primarily used in MySQL.

Examples:
-- Example 1: Concatenate employee names from a department
SELECT DepartmentID, GROUP_CONCAT(Name) AS Employees
FROM Employees
GROUP BY DepartmentID;

-- Example 2: Concatenate products in a specific category

SELECT CategoryID, GROUP_CONCAT(ProductName) AS Products
FROM Products
GROUP BY CategoryID;

-- Example 3: Concatenate all order IDs for a customer

SELECT CustomerID, GROUP_CONCAT(OrderID) AS Orders
FROM Orders
GROUP BY CustomerID;

43. How Do You Use the DENSE_RANK Function in SQL, and What Does It Do?

The DENSE_RANK function assigns a rank to each row within a partition of a result set, with no gaps in ranking values.

Examples:

-- Example 1: Rank employees based on their salary

SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

-- Example 2: Rank products by their sales amount

SELECT ProductName, SalesAmount, DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS Rank
FROM Products;

-- Example 3: Rank employees within each department based on salary

SELECT DepartmentID, Name, Salary, DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY
Salary DESC) AS Rank
FROM Employees;

44. How Do You Use the ON Keyword in a JOIN Statement in SQL?

The ON keyword is used to specify the condition for joining two tables. It defines how rows from each table should be
matched.

Examples:

-- Example 1: Inner join using the ON keyword

SELECT [Link], [Link]
FROM Employees
INNER JOIN Departments ON [Link] = [Link];

-- Example 2: Left join using the ON keyword

SELECT [Link], [Link]
FROM Employees
LEFT JOIN Departments ON [Link] = [Link];

-- Example 3: Right join using the ON keyword

SELECT [Link], [Link]
FROM Employees
RIGHT JOIN Departments ON [Link] = [Link];

45. How Do You Use the UNION ALL Operator in SQL, and What Does It Do?

The UNION ALL operator combines the results of two or more SELECT queries, including duplicates. It’s used when
you need all results from both queries.

Examples:
-- Example 1: Combine results from two departments with UNION ALL
SELECT Name FROM Employees WHERE DepartmentID = 10
UNION ALL
SELECT Name FROM Employees WHERE DepartmentID = 20;

-- Example 2: Combine product names and customer names from two tables
SELECT ProductName FROM Products
UNION ALL
SELECT CustomerName FROM Customers;

-- Example 3: Combine employee names from two different locations

SELECT Name FROM Employees WHERE Location = 'New York'
UNION ALL
SELECT Name FROM Employees WHERE Location = 'Chicago';

46. What Is a Cross Join in SQL, and How Is It Used?

A CROSS JOIN returns the Cartesian product of two tables. Each row from the first table is joined with each row
from the second table, resulting in all possible combinations.

Examples:

-- Example 1: Cross join two tables to get all possible combinations

SELECT [Link], [Link]
FROM Employees
CROSS JOIN Departments;

-- Example 2: Cross join products with prices

SELECT ProductName, Price
FROM Products
CROSS JOIN Prices;

-- Example 3: Generate combinations of days and products

SELECT [Link], [Link]
FROM Days
CROSS JOIN Products;

47. How Do You Use the NATURAL JOIN Keyword in SQL, and What Does It Do?

The NATURAL JOIN automatically joins tables based on columns with the same name and compatible data types. It
eliminates the need to specify the ON condition.

Examples:

-- Example 1: Using NATURAL JOIN to combine Employees and Departments

SELECT [Link], [Link]
FROM Employees
NATURAL JOIN Departments;

-- Example 2: Using NATURAL JOIN to combine Orders and Customers

SELECT [Link], [Link]
FROM Orders
NATURAL JOIN Customers;

-- Example 3: Using NATURAL JOIN to combine Employees and Salaries

SELECT [Link], [Link]
FROM Employees
NATURAL JOIN Salaries;

48. What Is a Check Constraint in SQL, and How Is It Used?

A CHECK constraint ensures that values in a column meet a specific condition.

Examples:

-- Example 1: Create a table with a CHECK constraint on the salary column

CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Salary DECIMAL(10, 2),
CHECK (Salary > 0)
);

-- Example 2: Use CHECK to limit the age of employees

CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Age INT,
CHECK (Age BETWEEN 18 AND 65)
);

-- Example 3: Adding a CHECK constraint to an existing table

ALTER TABLE Employees
ADD CONSTRAINT Check_Salary CHECK (Salary >= 1000);

49. What Is a Unique Constraint in SQL, and How Is It Used?

A UNIQUE constraint ensures that all values in a column are distinct, meaning no two rows can have the same
value in that column.

Examples:

-- Example 1: Create a table with a UNIQUE constraint on the email column

CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Email VARCHAR(100) UNIQUE
);

-- Example 2: Adding a UNIQUE constraint to an existing table

ALTER TABLE Employees
ADD CONSTRAINT Unique_Email UNIQUE (Email);

-- Example 3: Using UNIQUE constraint in table creation

CREATE TABLE Users (
UserID INT,
Username VARCHAR(50) UNIQUE,
Password VARCHAR(50)
);

50. What Is a Default Constraint in SQL, and How Is It Used?

A DEFAULT constraint provides a default value for a column when no value is specified during record insertion.

Examples:

-- Example 1: Create a table with a DEFAULT constraint

CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
HireDate DATE DEFAULT GETDATE() -- default current date
);

-- Example 2: Adding a DEFAULT constraint to an existing column

ALTER TABLE Employees
ADD CONSTRAINT Default_HireDate DEFAULT GETDATE() FOR HireDate;

-- Example 3: Using DEFAULT to set default value for a column in an INSERT statement
INSERT INTO Employees (EmployeeID, Name)
VALUES (1, 'John Doe'); -- HireDate will be automatically set to the current date

This concludes the explanation and examples for questions 41-50. Would you like to continue with the next set of
questions?

Let's continue with the next set of questions (51-60) and provide explanations with examples!

51. What Is a Null Constraint in SQL, and How Is It Used?

A NULL constraint specifies whether a column can store NULL values. By default, columns allow NULL values unless
explicitly specified otherwise.

Examples:

-- Example 1: Create a table with a NOT NULL constraint (prevents NULL values)
CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100) NOT NULL,
Salary DECIMAL(10, 2)
);

-- Example 2: Alter a table to add a NOT NULL constraint

ALTER TABLE Employees
ADD CONSTRAINT Employee_Name_NOT_NULL CHECK (Name IS NOT NULL);

-- Example 3: Default behavior is NULL unless specified NOT NULL

CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(100),
Salary DECIMAL(10, 2)
);

52. How Do You Add a Table Constraint to an Existing Table in SQL?

To add a constraint to an existing table, you can use the ALTER TABLE statement followed by ADD CONSTRAINT.

Examples:

-- Example 1: Add a UNIQUE constraint

ALTER TABLE Employees
ADD CONSTRAINT Unique_Email UNIQUE (Email);

-- Example 2: Add a CHECK constraint

ALTER TABLE Employees
ADD CONSTRAINT Salary_Check CHECK (Salary > 1000);

-- Example 3: Add a FOREIGN KEY constraint

ALTER TABLE Employees
ADD CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);

53. How Do You Modify or Remove a Table Constraint in SQL?

You can modify a constraint using ALTER TABLE and DROP CONSTRAINT to remove it.

Examples:

-- Example 1: Drop a UNIQUE constraint

ALTER TABLE Employees
DROP CONSTRAINT Unique_Email;

-- Example 2: Drop a FOREIGN KEY constraint

ALTER TABLE Employees
DROP CONSTRAINT FK_Department;

-- Example 3: Modify a constraint (e.g., increasing a column's length)

ALTER TABLE Employees
MODIFY COLUMN Name VARCHAR(200);

54. Explain the ROW_NUMBER Window Function with an Example.

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

Examples:

-- Example 1: Assign row numbers based on salary

SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

-- Example 2: Row number within departments

SELECT DepartmentID, Name, ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC)
AS RowNum
FROM Employees;

-- Example 3: Row number within a list of products ordered by price

SELECT ProductName, Price, ROW_NUMBER() OVER (ORDER BY Price DESC) AS RowNum
FROM Products;

55. Can You Update or Delete Data in a View in SQL?

Yes, you can update or delete data in a view, but only if the view is updatable. For a view to be updatable, it must
meet specific criteria (e.g., the view must not have joins, group functions, or distinct clauses).

Examples:

-- Example 1: Update data in a view (if view is updatable)

UPDATE EmployeeView
SET Salary = 5000
WHERE EmployeeID = 1;

-- Example 2: Delete data in a view (if view is updatable)

DELETE FROM EmployeeView
WHERE EmployeeID = 2;

-- Example 3: Non-updatable view (you cannot delete or update data)

CREATE VIEW NonUpdatableView AS
SELECT * FROM Employees
WHERE Salary > 3000;

56. What Is the Difference Between a View and a Table in SQL?

A view is a virtual table that is defined by a query, while a table is a physical structure that stores data.

Examples:

-- Example 1: Create a table

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
 Salary DECIMAL(10, 2)
);

-- Example 2: Create a view

CREATE VIEW HighSalaryEmployees AS
SELECT Name, Salary
FROM Employees
WHERE Salary > 5000;

-- Example 3: Querying a view

SELECT * FROM HighSalaryEmployees;

57. What Are the Properties of a Transaction in SQL?

A transaction has the ACID properties: Atomicity, Consistency, Isolation, and Durability. These ensure reliable
database transactions.

Examples:

-- Example 1: Atomicity: A transaction is atomic

BEGIN TRANSACTION;
UPDATE Employees SET Salary = 7000 WHERE EmployeeID = 1;
UPDATE Employees SET Salary = 8000 WHERE EmployeeID = 2;
COMMIT;

-- Example 2: Consistency: A transaction takes the database from one consistent state to
another
BEGIN TRANSACTION;
UPDATE Employees SET Salary = 7000 WHERE EmployeeID = 1;
-- If there is an error, the transaction ensures no partial updates
ROLLBACK;

-- Example 3: Durability: Changes are permanent once committed

BEGIN TRANSACTION;
UPDATE Employees SET Salary = 9000 WHERE EmployeeID = 1;
COMMIT; -- Changes are saved even if there's a system failure

58. What Is a Savepoint in SQL, and When Would You Use It?

A savepoint allows you to set a point within a transaction to which you can roll back without affecting the entire
transaction.

Examples:

-- Example 1: Set a savepoint in a transaction

BEGIN TRANSACTION;
UPDATE Employees SET Salary = 7000 WHERE EmployeeID = 1;
SAVEPOINT savepoint1;

UPDATE Employees SET Salary = 8000 WHERE EmployeeID = 2;

-- Rollback to savepoint1
ROLLBACK TO SAVEPOINT savepoint1;
COMMIT;

-- Example 2: Rollback after setting multiple savepoints

BEGIN TRANSACTION;
UPDATE Employees SET Salary = 7000 WHERE EmployeeID = 1;
SAVEPOINT savepoint1;
UPDATE Employees SET Salary = 8000 WHERE EmployeeID = 2;
ROLLBACK TO SAVEPOINT savepoint1;
COMMIT;

-- Example 3: Use savepoints to manage large transactions

BEGIN TRANSACTION;
UPDATE Orders SET Status = 'Shipped' WHERE OrderID = 1;
SAVEPOINT orderSavepoint;
UPDATE Orders SET Status = 'Processed' WHERE OrderID = 2;
COMMIT;

59. What Are Window Functions in SQL, and How Do They Differ from Regular Aggregate
Functions?

Window functions perform calculations across a set of table rows related to the current row. Unlike aggregate
functions, window functions do not collapse the result set into a single row.

Examples:

-- Example 1: Window function for ranking employees based on salary

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

-- Example 2: Window function for running total of sales

SELECT OrderID, Amount, SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Sales;

-- Example 3: Window function for calculating moving average

SELECT OrderID, Amount, AVG(Amount) OVER (ORDER BY OrderDate ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW) AS MovingAvg
FROM Sales;

60. What Are Some Common Uses for Window Functions in SQL?

Window functions are commonly used for tasks like ranking, calculating running totals, and calculating moving
averages.

Examples:

-- Example 1: Rank products based on sales amount

SELECT ProductName, SalesAmount, RANK() OVER (ORDER BY SalesAmount DESC) AS Rank
FROM Products;

-- Example 2: Calculate running total of order amounts

SELECT OrderID, Amount, SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

-- Example 3: Calculate a moving average of the last 5 days of sales

SELECT OrderID, SalesAmount, AVG(SalesAmount) OVER (ORDER BY OrderDate ROWS BETWEEN 4
PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Sales;

This concludes the explanation and examples for questions 51-60. Would you like to continue with the next set of
questions?

Let's continue with the next set of questions (61-70) and provide explanations with examples!

61. What Are Window Functions in SQL, and How Do They Differ from Regular Aggregate
Functions?

A window function performs a calculation across a set of rows related to the current row, providing results without
collapsing the rows into a single output. Unlike regular aggregate functions (like SUM, COUNT, etc.), window functions
do not reduce the result set.
Examples:

-- Example 1: RANK() function with salary data (Window function)

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

-- Example 2: SUM() window function to calculate running totals

SELECT OrderID, Amount, SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Orders;

-- Example 3: AVG() window function to calculate moving average

SELECT OrderID, SalesAmount, AVG(SalesAmount) OVER (ORDER BY OrderDate ROWS BETWEEN 3
PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Sales;

62. How Do You Use a Window Function in SQL?

To use a window function, you must specify the function followed by the OVER() clause, which defines the window
(the subset of data).

Examples:

-- Example 1: Using the ROW_NUMBER() window function

SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;

-- Example 2: Using the SUM() window function with partitioning

SELECT DepartmentID, Name, Salary, SUM(Salary) OVER (PARTITION BY DepartmentID ORDER BY
Salary) AS DepartmentSalary
FROM Employees;

-- Example 3: Using the LAG() window function to calculate difference in salary

SELECT Name, Salary, LAG(Salary) OVER (ORDER BY Salary) AS PreviousSalary
FROM Employees;

63. What Is a Common Table Expression (CTE) in SQL, and How Does It Differ from a
Subquery?

A Common Table Expression (CTE) is a temporary result set that you can reference within a SELECT, INSERT,
UPDATE, or DELETE statement. It is defined using the WITH keyword. A subquery is a query nested within another
query.

Examples:

-- Example 1: CTE for employee salary calculation

WITH DepartmentSalaries AS (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
)
SELECT DepartmentID, AvgSalary
FROM DepartmentSalaries;

-- Example 2: CTE for recursive queries

WITH RECURSIVE EmployeeHierarchy AS (
SELECT EmployeeID, ManagerID, Name
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT [Link], [Link], [Link]
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON [Link] = [Link]
)
SELECT * FROM EmployeeHierarchy;
-- Example 3: Subquery to filter employees based on average salary
SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

64. What Is a Recursive CTE in SQL, and When Would You Use It?

A recursive CTE is a CTE that references itself. It is useful for hierarchical or tree-structured data, such as an
organizational chart or bill of materials.

Examples:

-- Example 1: Recursive CTE for finding an employee hierarchy

WITH RECURSIVE EmployeeHierarchy AS (
SELECT EmployeeID, ManagerID, Name
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT [Link], [Link], [Link]
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON [Link] = [Link]
)
SELECT * FROM EmployeeHierarchy;

-- Example 2: Recursive CTE to calculate a factorial

WITH RECURSIVE Factorial AS (
SELECT 1 AS Number, 1 AS FactorialValue
UNION ALL
SELECT Number + 1, FactorialValue * (Number + 1)
FROM Factorial
WHERE Number < 5
)
SELECT * FROM Factorial;

-- Example 3: Recursive CTE to build a tree of categories

WITH RECURSIVE CategoryTree AS (
SELECT CategoryID, ParentCategoryID, CategoryName
FROM Categories
WHERE ParentCategoryID IS NULL
UNION ALL
SELECT [Link], [Link], [Link]
FROM Categories c
INNER JOIN CategoryTree ct ON [Link] = [Link]
)
SELECT * FROM CategoryTree;

65. What Is String Transformation in SQL, and How Is It Used?

String transformation in SQL refers to operations that modify strings, such as changing case, trimming spaces, or
replacing characters. Functions like UPPER(), LOWER(), TRIM(), and REPLACE() are commonly used.

Examples:

-- Example 1: Convert a string to uppercase

SELECT UPPER(Name) FROM Employees;

-- Example 2: Convert a string to lowercase

SELECT LOWER(Name) FROM Employees;

-- Example 3: Trim spaces from the start and end of a string

SELECT TRIM(Name) FROM Employees;
66. What Is a Regular Expression (Regex) in SQL, and How Is It Used?

A regular expression (regex) is a sequence of characters that defines a search pattern. SQL databases like MySQL
or PostgreSQL offer regex functions for pattern matching.

Examples:

-- Example 1: Match names starting with 'A' in PostgreSQL

SELECT Name
FROM Employees
WHERE Name ~ '^A';

-- Example 2: Check if email follows a basic pattern in MySQL

SELECT Email
FROM Users
WHERE Email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

-- Example 3: Match a string containing digits in SQL Server

SELECT Name
FROM Employees
WHERE Name LIKE '%[0-9]%';

67. What Are Some Common Regex Patterns Used in SQL?

Some common regex patterns include matching email addresses, phone numbers, and alphanumeric strings. Here are
a few examples of regex patterns.

Examples:

-- Example 1: Match an email address pattern

SELECT Email
FROM Users
WHERE Email REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

-- Example 2: Match a phone number pattern (e.g., (123) 456-7890)

SELECT PhoneNumber
FROM Contacts
WHERE PhoneNumber REGEXP '^\(\d{3}\)\s\d{3}-\d{4}$';

-- Example 3: Match alphanumeric strings

SELECT Name
FROM Products
WHERE Name REGEXP '^[a-zA-Z0-9]+$';

68. How Can You Use Regex to Validate Data in SQL?

You can use regex in SQL to ensure that data follows a specific format, such as validating phone numbers, email
addresses, or zip codes.

Examples:

-- Example 1: Validate email format

SELECT Email
FROM Users
WHERE Email NOT REGEXP '^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$';

-- Example 2: Validate phone number format

SELECT PhoneNumber
FROM Contacts
WHERE PhoneNumber NOT REGEXP '^\(\d{3}\)\s\d{3}-\d{4}$';

-- Example 3: Validate alphanumeric strings

SELECT ProductCode
FROM Products
WHERE ProductCode NOT REGEXP '^[a-zA-Z0-9]+$';

69. What Are Some Potential Drawbacks of Using Regex in SQL?

Some potential drawbacks of using regex in SQL include:

 Performance overhead: Regex operations can be slower than simple LIKE or = comparisons.
 Complexity: Regex patterns can be difficult to write and understand.
 Database-specific syntax: Different databases have different regex syntax and capabilities.

70. What Is Data Modeling in SQL, and Why Is It Important?

Data modeling is the process of creating a data structure that defines the relationships between different data
elements. It's important for ensuring the efficient organization and retrieval of data, preventing redundancy, and
maintaining data integrity.

Examples:

-- Example 1: Simple table creation for customer data

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100),
Address VARCHAR(200)
);

-- Example 2: Defining relationships between tables (Foreign key)

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Example 3: Creating an entity-relationship model for sales data

CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
Price DECIMAL(10, 2)
);

This concludes the explanation and examples for questions 61-70. Would you like to continue with the next set of
questions?

Let's continue with the next set of questions (71-80) and provide explanations and examples!

71. What Is Normalization in SQL, and Why Is It Important?

Normalization is the process of organizing data in a database to minimize redundancy and dependency by dividing
large tables into smaller ones and establishing relationships between them. The goal is to reduce data anomalies and
improve database efficiency.

Examples:

-- Example 1: A normalized table (2NF)

CREATE TABLE Customers (
 CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100)
);

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Example 2: Normalizing a product table with multiple categories

CREATE TABLE Categories (
CategoryID INT PRIMARY KEY,
CategoryName VARCHAR(50)
);

CREATE TABLE Products (

ProductID INT PRIMARY KEY,
ProductName VARCHAR(100),
CategoryID INT,
FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
);

72. What Are the Different Normal Forms in SQL?

There are several normal forms in SQL, each focusing on different aspects of data organization:

1. 1st Normal Form (1NF): Ensures that all columns contain atomic values (no repeating groups).
2. 2nd Normal Form (2NF): Achieved by eliminating partial dependency (all non-key columns are fully
dependent on the primary key).
3. 3rd Normal Form (3NF): Ensures that all columns are only dependent on the primary key (no transitive
dependency).
4. Boyce-Codd Normal Form (BCNF): A stricter version of 3NF where every determinant is a candidate key.
5. 4th Normal Form (4NF): Deals with multi-valued dependencies.

Examples:

-- Example 1: 1NF - Atomic values

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Skills VARCHAR(255) -- Not atomic (should be split into separate table)
);

-- Example 2: 2NF - Remove partial dependency

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
ProductID INT
);

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100)
);

-- Example 3: 3NF - Remove transitive dependency

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
DepartmentID INT
);

CREATE TABLE Departments (

DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100)
);

73. What Is Denormalization in SQL, and When Would You Use It?

Denormalization is the process of merging tables or adding redundancy to improve read performance, often at the
expense of write performance. It is used when query performance becomes a bottleneck, and data consistency is not a
critical concern.

Examples:

-- Example 1: Denormalized table with redundant data

CREATE TABLE Sales (
SaleID INT PRIMARY KEY,
CustomerID INT,
CustomerName VARCHAR(100),
ProductID INT,
ProductName VARCHAR(100),
SaleAmount DECIMAL(10, 2)
);

-- Example 2: Denormalizing to avoid complex joins

SELECT CustomerName, ProductName, SaleAmount
FROM Sales
WHERE SaleID = 1;

74. What Is a Database Index, and How Does It Improve Query Performance?

A database index is a data structure that improves the speed of data retrieval operations on a database table by
providing a fast way to look up rows. It is like an index in a book, which allows you to quickly locate information
without reading through the entire document.

Examples:

-- Example 1: Creating an index on the "Email" column of the "Users" table

CREATE INDEX idx_email ON Users (Email);

-- Example 2: Creating a composite index on "FirstName" and "LastName"

CREATE INDEX idx_name ON Employees (FirstName, LastName);

-- Example 3: Using an index to speed up a query

SELECT * FROM Employees WHERE LastName = 'Smith';

75. What Are the Types of Indexes in SQL?

There are several types of indexes in SQL:

1. Single-Column Index: An index on a single column.

2. Composite Index: An index on multiple columns.
3. Unique Index: Ensures that the values in the indexed column(s) are unique.
4. Full-Text Index: Used for searching large text fields.
5. Clustered Index: The data is physically organized on the disk in the order of the index.
6. Non-Clustered Index: A separate structure that references the data in the table.

Examples:

-- Example 1: Single-column index

CREATE INDEX idx_email ON Users (Email);

-- Example 2: Composite index

CREATE INDEX idx_name ON Employees (FirstName, LastName);
-- Example 3: Unique index
CREATE UNIQUE INDEX idx_email_unique ON Users (Email);

76. What Is a Primary Key in SQL, and How Is It Different from a Foreign Key?

A primary key uniquely identifies each record in a table. It ensures that no two rows have the same value in the
primary key column(s). A foreign key is a column (or group of columns) that links one table to another, ensuring
referential integrity between the tables.

Examples:

-- Example 1: Primary key definition

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100)
);

-- Example 2: Foreign key definition

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

77. What Is the Difference Between a JOIN and a UNION in SQL?

A JOIN combines rows from two or more tables based on a related column, while a UNION combines the result sets
of two or more queries into a single result set. The key difference is that JOIN works on matching rows between
tables, while UNION merges the result sets vertically.

Examples:

-- Example 1: Using JOIN to combine data from two tables

SELECT [Link], [Link]
FROM Orders
JOIN Customers ON [Link] = [Link];

-- Example 2: Using UNION to combine result sets from two queries

SELECT Name FROM Employees WHERE Department = 'Sales'
UNION
SELECT Name FROM Employees WHERE Department = 'Marketing';

78. What Is an Outer Join in SQL, and How Does It Differ from an Inner Join?

An Outer Join returns all rows from one table and the matched rows from the second table. If no match is found,
NULL values are returned. An Inner Join only returns rows where there is a match between the tables.

Examples:

-- Example 1: Inner Join

SELECT [Link], [Link]
FROM Orders
INNER JOIN Customers ON [Link] = [Link];

-- Example 2: Left Outer Join (returns all rows from the left table)
SELECT [Link], [Link]
FROM Orders
LEFT OUTER JOIN Customers ON [Link] = [Link];

-- Example 3: Right Outer Join (returns all rows from the right table)
SELECT [Link], [Link]
FROM Orders
RIGHT OUTER JOIN Customers ON [Link] = [Link];

79. What Are Subqueries in SQL, and How Are They Used?

A subquery is a query embedded inside another query. It can return a single value, a list of values, or a table result.
Subqueries are often used in WHERE, HAVING, FROM, or SELECT clauses.

Examples:

-- Example 1: Subquery in WHERE clause

SELECT Name
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'Sales');

-- Example 2: Subquery in SELECT clause

SELECT Name,
(SELECT AVG(Salary) FROM Employees) AS AvgSalary
FROM Employees;

-- Example 3: Subquery in FROM clause

SELECT DepartmentID, COUNT(*) AS NumEmployees
FROM (SELECT DepartmentID FROM Employees WHERE Salary > 50000) AS HighSalaryEmployees
GROUP BY DepartmentID;

80. What Are Stored Procedures in SQL, and How Are They Used?

A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. Stored
procedures can accept parameters and return results, making them useful for encapsulating logic and improving
performance.

Examples:

-- Example 1: Creating a stored procedure

CREATE PROCEDURE GetEmployeeDetails (IN empID INT)
BEGIN
SELECT Name, Salary
FROM Employees
WHERE EmployeeID = empID;
END;

-- Example 2: Calling a stored procedure

CALL GetEmployeeDetails(1);

-- Example 3: Stored procedure with parameterized query

CREATE PROCEDURE GetEmployeesByDepartment (IN deptID INT)
BEGIN
SELECT Name, Department
FROM Employees
WHERE DepartmentID = deptID;
END;

This concludes the explanation and examples for questions 71-80. Would you like to continue with the next set of
questions?
Great! Let's continue with questions 81-90.

81. What Is a Trigger in SQL, and How Does It Work?

A trigger is a stored procedure in SQL that is automatically executed in response to certain events on a particular
table or view, such as INSERT, UPDATE, or DELETE. Triggers are used for enforcing business rules, validating input, or
automatically updating other tables.

Examples:

-- Example 1: Creating a trigger that fires after an INSERT

CREATE TRIGGER AfterEmployeeInsert
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
INSERT INTO EmployeeAudit (EmployeeID, Action)
VALUES ([Link], 'INSERT');
END;

-- Example 2: Creating a trigger for UPDATE event

CREATE TRIGGER BeforeEmployeeUpdate
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
IF [Link] <> [Link] THEN
INSERT INTO SalaryChanges (EmployeeID, OldSalary, NewSalary)
VALUES ([Link], [Link], [Link]);
END IF;
END;

82. What Is a View in SQL, and How Is It Different from a Table?

A view is a virtual table in SQL that is based on the result of a SELECT query. Unlike a table, a view does not store
data itself; instead, it dynamically retrieves data from one or more tables when queried. Views can simplify complex
queries and provide security by restricting access to certain columns or rows.

Examples:

-- Example 1: Creating a simple view

CREATE VIEW EmployeeView AS
SELECT EmployeeID, Name, Department
FROM Employees
WHERE Department = 'Sales';

-- Example 2: Querying a view

SELECT * FROM EmployeeView;

83. What Are the Advantages and Disadvantages of Using Views?

Advantages of Views:

1. Simplify complex queries by encapsulating them.

2. Provide security by restricting access to sensitive data.
3. Make maintenance easier by allowing updates to be made in a single place.

Disadvantages of Views:
 1. Performance can be slower, as the view’s underlying query needs to be executed each time.
2. Views cannot always be indexed.
3. Views may not be updatable if the underlying query is too complex.

84. What Is a Cursor in SQL, and When Would You Use It?

A cursor in SQL is a database object that allows you to retrieve, manipulate, and traverse rows of data one at a time.
Cursors are useful for operations that require row-by-row processing, but they are generally slower than set-based
operations.

Examples:

-- Example 1: Declaring and opening a cursor

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

OPEN EmployeeCursor;
FETCH NEXT FROM EmployeeCursor INTO @EmployeeID;

-- Example 2: Using a cursor to process each row

WHILE @@FETCH_STATUS = 0
BEGIN
-- Process each employee ID
PRINT @EmployeeID;

FETCH NEXT FROM EmployeeCursor INTO @EmployeeID;

END;

-- Example 3: Closing and deallocating the cursor

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

85. What Is Referential Integrity in SQL, and How Is It Enforced?

Referential integrity ensures that relationships between tables remain consistent. It is enforced using foreign keys
and ensures that for every value in a foreign key column, there is a matching value in the referenced primary key
column.

Examples:

-- Example 1: Enforcing referential integrity using foreign keys

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Example 2: Attempting to insert a value that violates referential integrity

INSERT INTO Orders (OrderID, CustomerID)
VALUES (1, 999); -- This will fail if there is no CustomerID 999 in the Customers table.

86. What Is a Self Join in SQL, and When Is It Used?

A self join is a join where a table is joined with itself. It is useful when you need to compare rows within the same
table, for example, finding pairs of employees who work in the same department.

Examples:
-- Example 1: Self join to find employees working in the same department
SELECT [Link], [Link]
FROM Employees A, Employees B
WHERE [Link] = [Link] AND [Link] <> [Link];

-- Example 2: Self join with aliases for clarity

SELECT [Link] AS Employee1, [Link] AS Employee2
FROM Employees E1
JOIN Employees E2 ON [Link] = [Link]
WHERE [Link] <> [Link];

87. What Is a Composite Key in SQL?

A composite key is a combination of two or more columns in a table used to uniquely identify a record. It is used
when a single column is not sufficient to uniquely identify each row.

Examples:

-- Example 1: Creating a composite primary key

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

-- Example 2: Composite foreign key

CREATE TABLE ProductOrders (
OrderID INT,
ProductID INT,
PRIMARY KEY (OrderID, ProductID),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

88. What Is a Temporary Table in SQL?

A temporary table is a table that is created for the duration of a session or query. It is useful for storing intermediate
results and automatically dropped when the session ends or the connection is closed.

Examples:

-- Example 1: Creating a temporary table

CREATE TEMPORARY TABLE TempEmployees (
EmployeeID INT,
EmployeeName VARCHAR(100)
);

-- Example 2: Using a temporary table to store intermediate results

INSERT INTO TempEmployees
SELECT EmployeeID, EmployeeName
FROM Employees WHERE Department = 'Sales';

SELECT * FROM TempEmployees;

-- Example 3: Temporary table is automatically dropped at the end of the session

89. What Is an Aggregate Function in SQL, and What Are Some Examples?

An aggregate function performs a calculation on a set of values and returns a single result. Common aggregate
functions include COUNT(), SUM(), AVG(), MAX(), and MIN().
Examples:

-- Example 1: Using COUNT to get the number of employees

SELECT COUNT(*) FROM Employees;

-- Example 2: Using SUM to calculate the total salary of employees

SELECT SUM(Salary) FROM Employees;

-- Example 3: Using AVG to find the average salary

SELECT AVG(Salary) FROM Employees;

-- Example 4: Using MAX to get the highest salary

SELECT MAX(Salary) FROM Employees;

-- Example 5: Using MIN to get the lowest salary

SELECT MIN(Salary) FROM Employees;

90. What Is a Group By Clause in SQL, and How Is It Used?

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into summary
rows, like finding the total sales for each product or department. It is often used with aggregate functions.

Examples:

-- Example 1: Using GROUP BY with COUNT

SELECT DepartmentID, COUNT(*) AS NumEmployees
FROM Employees
GROUP BY DepartmentID;

-- Example 2: Using GROUP BY with SUM

SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID;

-- Example 3: Using GROUP BY with multiple columns

SELECT DepartmentID, JobTitle, COUNT(*) AS NumEmployees
FROM Employees
GROUP BY DepartmentID, JobTitle;

That concludes questions 81-90. Would you like to continue with the next set of questions?

Great! Let's continue with questions 91-100.

91. What Is the Difference Between DELETE and TRUNCATE in SQL?

Both DELETE and TRUNCATE are used to remove records from a table, but they behave differently:

 DELETE: Removes rows one at a time and logs each deletion, which can be slower. It can be rolled back and
is used with conditions (e.g., WHERE clause).
 TRUNCATE: Removes all rows in a table and does not log individual row deletions. It is faster but cannot be
rolled back unless within a transaction.

Examples:

-- Example 1: DELETE statement with condition

DELETE FROM Employees WHERE Department = 'Sales';

-- Example 2: TRUNCATE statement to remove all rows

TRUNCATE TABLE Employees;
92. What Is the Difference Between UNION and UNION ALL in SQL?

 UNION: Combines results from two or more queries and removes duplicates.
 UNION ALL: Combines results from two or more queries without removing duplicates.

Examples:

-- Example 1: Using UNION (removes duplicates)

SELECT EmployeeID FROM Employees WHERE Department = 'Sales'
UNION
SELECT EmployeeID FROM Employees WHERE Department = 'HR';

-- Example 2: Using UNION ALL (includes duplicates)

SELECT EmployeeID FROM Employees WHERE Department = 'Sales'
UNION ALL
SELECT EmployeeID FROM Employees WHERE Department = 'HR';

93. What Is the Difference Between the HAVING Clause and the WHERE Clause in SQL?

 WHERE: Filters rows before grouping and aggregating (used with individual rows).
 HAVING: Filters groups after they have been aggregated (used with GROUP BY).

Examples:

-- Example 1: Using WHERE clause to filter before grouping

SELECT DepartmentID, COUNT(*) AS NumEmployees
FROM Employees
WHERE Salary > 5000
GROUP BY DepartmentID;

-- Example 2: Using HAVING clause to filter after grouping

SELECT DepartmentID, COUNT(*) AS NumEmployees
FROM Employees
GROUP BY DepartmentID
HAVING COUNT(*) > 5;

94. What Is ACID in the Context of Database Transactions?

ACID stands for:

 Atomicity: All operations within a transaction are completed or none are.

 Consistency: The database remains in a consistent state after the transaction.
 Isolation: Transactions are isolated from each other until they are completed.
 Durability: Once a transaction is committed, it cannot be undone, even if the system crashes.

Example:

-- Example 1: Starting a transaction

BEGIN TRANSACTION;

-- Example 2: Committing a transaction

COMMIT;

-- Example 3: Rolling back a transaction if an error occurs

ROLLBACK;

95. What Is a Deadlock in SQL?

A deadlock occurs when two or more transactions are waiting for each other to release locks, causing the system to
become stuck. SQL Server automatically detects deadlocks and chooses one transaction to terminate.
Example:

-- Example 1: Deadlock scenario

BEGIN TRANSACTION;
UPDATE Employees SET Salary = 10000 WHERE EmployeeID = 1;

BEGIN TRANSACTION;
UPDATE Employees SET Salary = 12000 WHERE EmployeeID = 2;

-- Both transactions will wait for each other, causing a deadlock.

96. What Is the Difference Between a Database and a Schema?

 Database: A collection of data and the objects (tables, views, procedures, etc.) that define it.
 Schema: A logical grouping of database objects, usually belonging to a particular user or application, within a
database.

Examples:

-- Example 1: Creating a schema

CREATE SCHEMA Sales;

-- Example 2: Creating a table within a schema

CREATE TABLE [Link] (
OrderID INT PRIMARY KEY,
OrderDate DATE
);

97. What Is the Difference Between a Temporary Table and a Table Variable?

 Temporary Table: Created using CREATE TEMPORARY TABLE and can be used within a session. It can have
indexes and constraints.
 Table Variable: Declared using DECLARE and is used in a more limited scope (within a single batch or
function).

Examples:

-- Example 1: Creating a temporary table

CREATE TEMPORARY TABLE TempEmployees (
EmployeeID INT,
EmployeeName VARCHAR(100)
);

-- Example 2: Declaring a table variable

DECLARE @EmployeeTable TABLE (
EmployeeID INT,
EmployeeName VARCHAR(100)
);

98. What Is the Purpose of the GROUP BY Clause in SQL?

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows,
like calculating the total sales per product or department. It is often used with aggregate functions.

Examples:

-- Example 1: Grouping by DepartmentID

SELECT DepartmentID, COUNT(*) AS NumEmployees
FROM Employees
GROUP BY DepartmentID;
-- Example 2: Grouping by DepartmentID and calculating the total salary
SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DepartmentID;

99. What Is the Difference Between CHAR and VARCHAR Data Types in SQL?

 CHAR: Fixed-length string. It pads with spaces if the string is shorter than the defined length.
 VARCHAR: Variable-length string. It uses only as much space as required for the actual string.

Examples:

-- Example 1: Using CHAR

CREATE TABLE Employees (
EmployeeID INT,
Name CHAR(50)
);

-- Example 2: Using VARCHAR

CREATE TABLE Employees (
EmployeeID INT,
Name VARCHAR(50)
);

100. What Is a Stored Procedure?

A stored procedure is a precompiled collection of SQL statements that can be executed as a single unit. It can accept
parameters, execute queries, and return results. Stored procedures help in simplifying complex operations and
improving performance by reducing the need to repeatedly send the same SQL commands.

Examples:

-- Example 1: Creating a stored procedure

CREATE PROCEDURE GetEmployeeDetails
@EmployeeID INT
AS
BEGIN
SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

-- Example 2: Executing a stored procedure

EXEC GetEmployeeDetails @EmployeeID = 1;

-- Example 3: Creating a stored procedure with multiple queries

CREATE PROCEDURE AddEmployee
@EmployeeName VARCHAR(100),
@Salary DECIMAL(10, 2)
AS
BEGIN
INSERT INTO Employees (EmployeeName, Salary)
VALUES (@EmployeeName, @Salary);
END;

That concludes questions 91-100. Would you like to continue with the next set of questions?

Let's continue with questions 101-110:

101. What Is a View?

A view is a virtual table that provides a way to look at data from one or more tables. It is a stored query that you can
reference like a table, but it doesn’t store data itself; it only displays the data as per the underlying query.

Examples:

-- Example 1: Creating a simple view

CREATE VIEW EmployeeView AS
SELECT EmployeeID, EmployeeName, Department FROM Employees;

-- Example 2: Using the view to query data

SELECT * FROM EmployeeView;

-- Example 3: View with a join

CREATE VIEW DepartmentEmployee AS
SELECT [Link], [Link], [Link]
FROM Employees e
JOIN Departments d ON [Link] = [Link];

102. What Is the Difference Between a Cross Join and an Inner Join?

 Cross Join: Returns the Cartesian product of two tables, meaning every row from the first table is combined
with every row from the second table.
 Inner Join: Returns rows where there is a match in both tables based on a specified condition (usually with
ON).

Examples:

-- Example 1: Cross Join

SELECT * FROM Employees CROSS JOIN Departments;

-- Example 2: Inner Join

SELECT [Link], [Link]
FROM Employees e
INNER JOIN Departments d ON [Link] = [Link];

103. What Is the Purpose of the COMMIT Statement?

The COMMIT statement is used to save all changes made in a transaction to the database permanently. Once
committed, the transaction cannot be rolled back.

Examples:

-- Example 1: Committing a transaction

BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary + 500 WHERE Department = 'Sales';
COMMIT;

-- Example 2: Committing changes after multiple operations

BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary + 1000 WHERE EmployeeID = 1;
UPDATE Employees SET Salary = Salary + 1000 WHERE EmployeeID = 2;
COMMIT;

104. What Is the Purpose of the ROLLBACK Statement?

The ROLLBACK statement is used to undo changes made in the current transaction. It is useful in case of errors or
when you want to cancel the transaction.

Examples:
-- Example 1: Rolling back a transaction
BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary + 1000 WHERE EmployeeID = 1;
ROLLBACK;

-- Example 2: Rolling back after an error

BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary + 1000 WHERE EmployeeID = 1;
-- An error occurs here
ROLLBACK;

105. What Is the Purpose of the NULL Value in SQL?

The NULL value represents the absence of data or an unknown value in a column. It is not the same as an empty
string or zero.

Examples:

-- Example 1: Inserting a NULL value

INSERT INTO Employees (EmployeeName, DepartmentID, Salary)
VALUES ('John Doe', NULL, 5000);

-- Example 2: Checking for NULL

SELECT * FROM Employees WHERE DepartmentID IS NULL;

-- Example 3: Using COALESCE to replace NULL

SELECT COALESCE(Salary, 0) FROM Employees;

106. What Is the Difference Between a View and a Materialized View?

 View: A virtual table that does not store data and is computed each time it is queried.
 Materialized View: A physical object that stores the query result. It can be refreshed periodically, making it
faster for repeated queries.

Examples:

-- Example 1: Creating a simple view

CREATE VIEW EmployeeView AS
SELECT EmployeeID, EmployeeName FROM Employees;

-- Example 2: Creating a materialized view (syntax may vary by RDBMS)

CREATE MATERIALIZED VIEW EmployeeSalarySummary AS
SELECT Department, SUM(Salary) AS TotalSalary FROM Employees GROUP BY Department;

107. What Is a Correlated Subquery?

A correlated subquery is a subquery that references columns from the outer query. It cannot be executed
independently, as it depends on values from the outer query.

Examples:

-- Example 1: Correlated subquery

SELECT EmployeeName, Salary
FROM Employees e
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE Department = [Link]);

-- Example 2: Correlated subquery for employee with higher salary than the department average
SELECT [Link]
FROM Employees e
WHERE [Link] > (SELECT AVG([Link]) FROM Employees e1 WHERE [Link] = [Link]);
108. What Is the Purpose of the DISTINCT Keyword?

The DISTINCT keyword is used to remove duplicate rows from the result set and return only unique values.

Examples:

-- Example 1: Using DISTINCT to get unique department names

SELECT DISTINCT Department FROM Employees;

-- Example 2: Using DISTINCT to avoid duplicate records

SELECT DISTINCT EmployeeName FROM Employees;

-- Example 3: Using DISTINCT on multiple columns

SELECT DISTINCT Department, JobTitle FROM Employees;

109. What Is the Difference Between the CHAR and VARCHAR Data Types?

This was already explained in question 99. Both CHAR and VARCHAR store string data, but:

 CHAR: Fixed length (pads with spaces if string is shorter).

 VARCHAR: Variable length (uses only necessary space).

110. What Is the Difference Between the IN and EXISTS Operators?

 IN: Used to check if a value is in a list or subquery result. It compares a column’s value against a list of
values.
 EXISTS: Checks if a subquery returns any rows. It is used for subqueries that check for the existence of rows
rather than specific values.

Examples:

-- Example 1: Using IN operator

SELECT EmployeeID FROM Employees WHERE Department IN ('Sales', 'HR');

-- Example 2: Using EXISTS operator

SELECT EmployeeID FROM Employees e
WHERE EXISTS (SELECT 1 FROM Departments d WHERE [Link] = [Link]);

That concludes questions 101-110. Would you like to continue with the next set of questions?

Here are answers and examples for questions 111-120:

111. What Is the Purpose of the TRIGGER Statement?

A trigger is a stored procedure that automatically executes when an event such as an INSERT, UPDATE, or DELETE
occurs on a table or view. It is used to enforce business rules, data integrity, or logging.

Examples:

-- Example 1: Creating a trigger for inserting a record

CREATE TRIGGER AfterInsertEmployee
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
INSERT INTO AuditLog (Action, EmployeeID)
 VALUES ('Inserted', [Link]);
END;

-- Example 2: Trigger for updating salary

CREATE TRIGGER BeforeUpdateSalary
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
IF [Link] < [Link] THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be decreased';
END IF;
END;

112. What Is the Difference Between a Unique Constraint and a Unique Index?

 Unique Constraint: A database constraint that ensures that all values in a column (or a combination of
columns) are unique.
 Unique Index: A type of index that prevents duplicate values in a column and can improve query
performance.

Examples:

-- Example 1: Unique constraint

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100),
Email VARCHAR(100) UNIQUE
);

-- Example 2: Unique index

CREATE UNIQUE INDEX idx_email ON Employees (Email);

113. What Is the Purpose of the TOP or LIMIT Clause?

The TOP (in SQL Server) or LIMIT (in MySQL, PostgreSQL) clause is used to limit the number of rows returned
by a query.

Examples:

-- Example 1: Using LIMIT in MySQL

SELECT * FROM Employees LIMIT 5;

-- Example 2: Using TOP in SQL Server

SELECT TOP 5 * FROM Employees;

114. What Is the Difference Between the UNION and JOIN Operators?

 UNION: Combines the result sets of two or more queries into a single result set, eliminating duplicates.
 JOIN: Combines rows from two or more tables based on a related column.

Examples:

-- Example 1: Using UNION

SELECT EmployeeName FROM Employees WHERE Department = 'HR'
UNION
SELECT EmployeeName FROM Employees WHERE Department = 'IT';

-- Example 2: Using JOIN

SELECT [Link], [Link]
FROM Employees e
JOIN Departments d ON [Link] = [Link];
115. What Is the Difference Between a Primary Key and a Candidate Key?

 Primary Key: A column or a set of columns that uniquely identify a record in a table. It must be unique and
not NULL.
 Candidate Key: A set of columns that could potentially be used as a primary key. A table can have multiple
candidate keys.

Examples:

-- Example 1: Primary Key

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(100)
);

-- Example 2: Candidate Key

CREATE TABLE Employees (
EmployeeID INT,
Email VARCHAR(100),
PRIMARY KEY (EmployeeID)
-- Email can also be a candidate key if it’s unique
);

116. What Is the Purpose of the GRANT Statement?

The GRANT statement is used to give users access privileges (such as SELECT, INSERT, UPDATE, DELETE) on
database objects like tables, views, or stored procedures.

Examples:

-- Example 1: Granting SELECT permission

GRANT SELECT ON Employees TO user1;

-- Example 2: Granting multiple privileges

GRANT SELECT, INSERT, UPDATE ON Employees TO user2;

-- Example 3: Granting all privileges

GRANT ALL PRIVILEGES ON Employees TO user3;

117. What Is a Correlated Update?

A correlated update involves updating a record based on a value that depends on the data in the same row or from a
related table, typically used in subqueries.

Examples:

-- Example 1: Correlated update

UPDATE Employees e
SET [Link] = [Link] + 1000
WHERE EXISTS (
SELECT 1 FROM Departments d WHERE [Link] = [Link] AND [Link] = 'Sales'
);

-- Example 2: Updating based on another table's data

UPDATE Employees e
SET [Link] = (SELECT MAX(Salary) FROM Employees WHERE DepartmentID = [Link])
WHERE [Link] = 1;
118. What Is the Purpose of the CASE Statement?

The CASE statement is used for conditional logic in SQL. It allows you to execute expressions based on specific
conditions.

Examples:

-- Example 1: Using CASE for conditional display

SELECT EmployeeName,
CASE
WHEN Salary > 5000 THEN 'High Salary'
WHEN Salary <= 5000 THEN 'Low Salary'
ELSE 'Unknown'
END AS SalaryCategory
FROM Employees;

-- Example 2: Using CASE in UPDATE statement

UPDATE Employees
SET Salary = CASE
WHEN Department = 'Sales' THEN Salary + 1000
ELSE Salary
END;

119. What Is the Purpose of the COALESCE Function?

The COALESCE function returns the first non-NULL value in a list of expressions. It is useful for replacing NULL
values with a default value.

Examples:

-- Example 1: Replacing NULL with a default value

SELECT COALESCE(Salary, 0) AS Salary FROM Employees;

-- Example 2: Using COALESCE with multiple columns

SELECT EmployeeID, COALESCE(Phone, Email, 'No Contact Info') AS ContactInfo
FROM Employees;

120. What Is the Purpose of the ROW_NUMBER() Function?

The ROW_NUMBER() function assigns a unique sequential number to rows within a partition of a result set. It’s
commonly used for pagination or ranking results.

Examples:

-- Example 1: Using ROW_NUMBER for ranking

SELECT EmployeeID, EmployeeName, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

-- Example 2: Using ROW_NUMBER with partition

SELECT EmployeeID, EmployeeName, Salary,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DepartmentRank
FROM Employees;

That concludes questions 111-120. Would you like to continue with the next set?

Here are answers and examples for questions 121-130:

121. What Is the Difference Between a Natural Join and an Inner Join?

 Natural Join: Automatically joins tables based on columns with the same name and data type. It eliminates
duplicate columns in the result.
 Inner Join: Joins tables based on a specified condition and returns rows that satisfy the condition.

Examples:

-- Example 1: Using NATURAL JOIN

SELECT *
FROM Employees
NATURAL JOIN Departments;

-- Example 2: Using INNER JOIN

SELECT [Link], [Link]
FROM Employees e
INNER JOIN Departments d ON [Link] = [Link];

122. What Is the Purpose of the CASCADE DELETE Constraint?

The CASCADE DELETE constraint automatically deletes rows in child tables when the corresponding row in the
parent table is deleted. It maintains referential integrity.

Examples:

-- Example 1: Creating a CASCADE DELETE constraint

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
CONSTRAINT fk_Customer
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
ON DELETE CASCADE
);

-- Example 2: Deleting from the parent table

DELETE FROM Customers WHERE CustomerID = 1; -- Orders related to this customer will be deleted

123. What Is the Purpose of the ALL Keyword in SQL?

The ALL keyword is used in comparisons to check if all values in a subquery meet a condition (e.g., ALL values are
greater than or equal to a specified value).

Examples:

-- Example 1: Using ALL to compare salary

SELECT EmployeeName
FROM Employees
WHERE Salary >= ALL (SELECT Salary FROM Employees WHERE Department = 'HR');

-- Example 2: Using ALL for subquery comparison

SELECT ProductName
FROM Products
WHERE Price > ALL (SELECT Price FROM Products WHERE Category = 'Electronics');

124. What Is the Difference Between the EXISTS and NOT EXISTS Operators?

 EXISTS: Returns TRUE if the subquery returns one or more rows, otherwise FALSE.
 NOT EXISTS: Returns TRUE if the subquery returns no rows, otherwise FALSE.

Examples:
-- Example 1: Using EXISTS
SELECT EmployeeName
FROM Employees e
WHERE EXISTS (SELECT 1 FROM Orders o WHERE [Link] = [Link]);

-- Example 2: Using NOT EXISTS

SELECT EmployeeName
FROM Employees e
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE [Link] = [Link]);

125. What Is the Purpose of the CROSS APPLY Operator?

The CROSS APPLY operator allows you to join a table with a table-valued function. It works like an INNER JOIN
but is used specifically with table-valued functions.

Examples:

-- Example 1: Using CROSS APPLY with a table-valued function

SELECT [Link], [Link], [Link]
FROM Employees e
CROSS APPLY GetEmployeeProjects([Link]) AS proj;

-- Example 2: Using CROSS APPLY with a subquery

SELECT [Link], [Link], [Link]
FROM Employees e
CROSS APPLY (SELECT TOP 1 OrderDate FROM Orders WHERE EmployeeID = [Link] ORDER BY
OrderDate DESC) o;

126. What Is a Self-Join?

A self-join is a join operation where a table is joined with itself. It is useful when you need to compare rows within
the same table.

Examples:

-- Example 1: Self-join to find employees and their managers

SELECT [Link], [Link] AS Manager
FROM Employees e
LEFT JOIN Employees m ON [Link] = [Link];

-- Example 2: Self-join to find employees with same salary

SELECT [Link], [Link]
FROM Employees e1
JOIN Employees e2 ON [Link] = [Link]
WHERE [Link] != [Link];

127. What Is an ALIAS Command?

The ALIAS command is used to create a temporary name for a table or column. It simplifies complex queries and
makes the output easier to understand.

Examples:

-- Example 1: Alias for column

SELECT EmployeeName AS "Name", Salary AS "Income"
FROM Employees;

-- Example 2: Alias for table

SELECT [Link], [Link]
FROM Employees e
JOIN Departments d ON [Link] = [Link];
128. Why Are SQL Functions Used?

SQL functions are used to perform operations on data and return a result. They are used for calculations,
transformations, string manipulation, and more.

Examples:

-- Example 1: Using string function

SELECT UPPER(EmployeeName) FROM Employees;

-- Example 2: Using aggregate function

SELECT AVG(Salary) FROM Employees;

-- Example 3: Using date function

SELECT YEAR(HireDate) FROM Employees;

129. What Is the Difference Between “Stored Procedure” and “Function”?

 Stored Procedure: A set of SQL statements that can be executed to perform a task. It may or may not return
a value.
 Function: A set of SQL statements that return a value and can be used in queries like a regular expression or
calculation.

Examples:

-- Example 1: Stored procedure

CREATE PROCEDURE GetEmployeeSalary(IN empID INT)
BEGIN
SELECT Salary FROM Employees WHERE EmployeeID = empID;
END;

-- Example 2: Function
CREATE FUNCTION GetEmployeeSalary(empID INT)
RETURNS DECIMAL(10,2)
BEGIN
RETURN (SELECT Salary FROM Employees WHERE EmployeeID = empID);
END;

130. What Is the Difference Between DELETE, TRUNCATE, and DROP in SQL?

 DELETE: Removes rows from a table based on a condition, can be rolled back, and does not affect table
structure.
 TRUNCATE: Removes all rows from a table, cannot be rolled back in some systems, and resets identity
columns.
 DROP: Deletes the entire table, including its structure and data.

Examples:

-- Example 1: DELETE
DELETE FROM Employees WHERE EmployeeID = 5;

-- Example 2: TRUNCATE
TRUNCATE TABLE Employees;

-- Example 3: DROP
DROP TABLE Employees;

This concludes questions 121-130. Would you like to continue with more?

Here are answers and examples for questions 131-140:

131. What Is the Difference Between a Local Temporary Table and a Global Temporary Table?

 Local Temporary Table: Created using # and only available within the session in which it was created. It is
dropped when the session ends.
 Global Temporary Table: Created using ## and available to all sessions. It is dropped when the last session
referencing it ends.

Examples:

-- Example 1: Local Temporary Table

CREATE TABLE #TempEmployees (
EmployeeID INT,
EmployeeName VARCHAR(50)
);

-- Example 2: Global Temporary Table

CREATE TABLE ##GlobalTempEmployees (
EmployeeID INT,
EmployeeName VARCHAR(50)
);

132. What Are Super, Primary, Candidate, and Foreign Keys?

 Super Key: A set of one or more columns that can uniquely identify a row in a table.
 Primary Key: A superkey that uniquely identifies each record in a table and cannot have null values.
 Candidate Key: A set of columns that can uniquely identify records but may not be the primary key.
 Foreign Key: A column (or set of columns) in one table that links to the primary key in another table.

Examples:

-- Example 1: Primary Key

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50)
);

-- Example 2: Foreign Key

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
EmployeeID INT,
FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

133. What Is the Difference Between Primary Key and Unique Constraints?

 Primary Key: Uniquely identifies each record in a table and does not allow null values.
 Unique Constraint: Ensures that all values in a column are unique but allows null values.

Examples:

-- Example 1: Primary Key

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50)
);

-- Example 2: Unique Constraint

CREATE TABLE Employees (
EmployeeID INT,
EmployeeName VARCHAR(50) UNIQUE
);

134. What Is Identity?

 Identity: A property used to create an auto-incrementing value for a column, typically for primary keys.

Examples:

-- Example 1: Using Identity

CREATE TABLE Employees (
EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
EmployeeName VARCHAR(50)
);

135. What Is a View in SQL? How to Create One?

 A view is a virtual table created by a query that selects data from one or more tables. Views simplify complex
queries and abstract the data layer.

Examples:

-- Example 1: Creating a View

CREATE VIEW EmployeeView AS
SELECT EmployeeID, EmployeeName FROM Employees;

-- Example 2: Using the View

SELECT * FROM EmployeeView;

136. What Are the Uses of View?

 Data abstraction: Hides the complexity of data.

 Security: Restricts access to sensitive data.
 Simplifies complex queries: Encapsulates complex joins and aggregations.

Examples:

-- Example 1: Using View for Security

CREATE VIEW EmployeeView AS
SELECT EmployeeID, EmployeeName FROM Employees WHERE Salary > 5000;
-- Users can access the view but not the raw data.

-- Example 2: Using View for Simplification

SELECT * FROM EmployeeView WHERE EmployeeName LIKE 'A%';

137. What Are Indexes?

 Index: A database object that improves the speed of data retrieval operations on a table by providing quick
access to rows.

Examples:

-- Example 1: Creating an Index

CREATE INDEX idx_EmployeeName ON Employees (EmployeeName);

-- Example 2: Using Index in Query

SELECT * FROM Employees WHERE EmployeeName = 'John';

-- Example 3: Dropping an Index

DROP INDEX idx_EmployeeName ON Employees;
138. What Is the Difference Between Trigger and Stored Procedure?

 Trigger: Automatically executed in response to specific events like insert, update, or delete.
 Stored Procedure: A set of SQL statements executed manually or by other SQL commands, not triggered
automatically.

Examples:

-- Example 1: Trigger
CREATE TRIGGER BeforeEmployeeInsert
ON Employees
FOR INSERT
AS
BEGIN
PRINT 'An employee is being added';
END;

-- Example 2: Stored Procedure

CREATE PROCEDURE GetEmployeeDetails
AS
BEGIN
SELECT * FROM Employees;
END;

139. What Are Indexes?

Indexes (already covered in 137) are database objects that speed up data retrieval. They act like a table of contents
for a database.

140. What Are Clustered and Non-Clustered Indexes?

 Clustered Index: The data is physically stored in the order of the index. There can only be one clustered
index per table.
 Non-Clustered Index: A separate structure from the data that contains pointers to the data. You can have
multiple non-clustered indexes on a table.

Examples:

-- Example 1: Creating a Clustered Index

CREATE CLUSTERED INDEX idx_EmployeeID ON Employees (EmployeeID);

-- Example 2: Creating a Non-Clustered Index

CREATE NONCLUSTERED INDEX idx_EmployeeName ON Employees (EmployeeName);

This concludes questions 131-140. Would you like to continue with more?

Let's continue with questions 141-150:

141. What Are the Advantages of SQL?

 Data Manipulation: SQL allows for efficient querying, updating, and managing data.
 Standardization: SQL is a standardized language supported by most RDBMS.
 Scalability: SQL databases handle a wide range of data, from small to very large datasets.
  Flexibility: SQL provides the ability to define complex relationships between tables and perform
aggregations.

142. What Are Properties of a Transaction?

 Atomicity: All operations in a transaction are completed successfully, or none are.

 Consistency: The database transitions from one valid state to another.
 Isolation: Transactions are performed independently, without interference from others.
 Durability: Once a transaction is committed, it remains so even in the case of a system failure.

143. What Is a Database Lock & Types of Locks?

 Database Lock: A mechanism to control concurrent access to database resources (tables, rows, etc.).
 Types of Locks:
o Shared Lock: Read access is allowed, but no writes.
o Exclusive Lock: Exclusive control over a resource, no other operations allowed.
o Intent Lock: Indicates intention to lock rows or tables.

Examples:

-- Example 1: Shared Lock

SELECT * FROM Employees WITH (HOLDLOCK);

-- Example 2: Exclusive Lock

UPDATE Employees SET Salary = 5000 WHERE EmployeeID = 1 WITH (ROWLOCK, UPDLOCK);

144. What Is a Composite Key & Composite Primary Key?

 Composite Key: A primary key made up of multiple columns.

 Composite Primary Key: A primary key using two or more columns to uniquely identify a record.

Examples:

-- Example 1: Composite Key

CREATE TABLE Orders (
OrderID INT,
ProductID INT,
PRIMARY KEY (OrderID, ProductID)
);

-- Example 2: Composite Primary Key

CREATE TABLE Employees (
DepartmentID INT,
EmployeeID INT,
PRIMARY KEY (DepartmentID, EmployeeID)
);

145. What Are Wildcards Used in Database for Pattern Matching?

 %: Matches zero or more characters.

 _: Matches exactly one character.

Examples:

-- Example 1: Using % wildcard

SELECT * FROM Employees WHERE EmployeeName LIKE 'J%';

-- Example 2: Using _ wildcard

SELECT * FROM Employees WHERE EmployeeName LIKE 'Jo_';

146. What Is a Materialized View?

 Materialized View: A database object that stores the result of a query physically, unlike a regular view,
which computes results at runtime.

Examples:

-- Example 1: Creating a Materialized View

CREATE MATERIALIZED VIEW EmployeeSalaries AS
SELECT EmployeeID, SUM(Salary) FROM Employees GROUP BY EmployeeID;

-- Example 2: Refreshing Materialized View

REFRESH MATERIALIZED VIEW EmployeeSalaries;

147. What Are User Defined Datatypes and When Should You Use Them?

 User Defined Datatypes: Custom data types created using SQL to meet specific business requirements.
 Use Case: When standard data types are not sufficient, e.g., Location as a datatype combining latitude and
longitude.

Examples:

-- Example 1: Creating a User Defined Data Type

CREATE TYPE Location AS
(
Latitude FLOAT,
Longitude FLOAT
);

-- Example 2: Using User Defined Type

CREATE TABLE Stores (
StoreID INT PRIMARY KEY,
StoreLocation Location
);

148. How Do You Implement One-to-One, One-to-Many, and Many-to-Many Relationships While
Designing Tables?

 One-to-One: Two tables share a one-to-one relationship using a primary key and foreign key.
 CREATE TABLE Profiles (
 ProfileID INT PRIMARY KEY,
 EmployeeID INT,
 FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
 );
 One-to-Many: One record in the primary table relates to many records in another table.
 CREATE TABLE Employees (
 EmployeeID INT PRIMARY KEY,
 DepartmentID INT,
 FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
 );
 Many-to-Many: Two tables are linked through a junction table.
 CREATE TABLE EmployeeProjects (
 EmployeeID INT,
 ProjectID INT,
  PRIMARY KEY(EmployeeID, ProjectID),
 FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID),
 FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID)
 );

149. Define Candidate Key, Alternate Key, Composite Key:

 Candidate Key: A minimal set of columns that uniquely identifies rows (e.g., EmployeeID).
 Alternate Key: Other candidate keys not selected as the primary key (e.g., EmployeeEmail).
 Composite Key: Made up of two or more columns (e.g., DepartmentID + EmployeeID).

150. What Are Defaults? Is There a Column to Which a Default Can't be Bound?

 Default: A predefined value automatically assigned to a column when no value is provided.

 **No default can be bound to columns that cannot accept null values (e.g., columns with NOT NULL
constraint).

Examples:

-- Example 1: Setting Default Value

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
DepartmentID INT DEFAULT 1 -- Default department
);

Let me know if you'd like to proceed with more questions!

Great! Let's continue with questions 141-150:

141. What Are the Advantages of SQL?

 Standardized: SQL is a standard language for relational databases.

 Flexibility: Supports complex queries and multiple data types.
 Scalability: Suitable for large and small datasets.
 Portability: SQL databases can be used across platforms.
 Performance: Efficient for handling large volumes of data.

142. What Are Properties of a Transaction?

A transaction must have the following properties (ACID):

 Atomicity: All operations are completed successfully or not at all.

 Consistency: Data remains valid before and after a transaction.
 Isolation: Transactions are independent.
 Durability: Changes are permanent after commit.

143. What Is a Database Lock & Type of Locks?

  A database lock is a mechanism to ensure data integrity and prevent multiple users from accessing and
modifying the same data simultaneously.
 Types of locks:
o Shared Lock: Allows multiple users to read data.
o Exclusive Lock: Allows only one user to modify data.
o Row Lock: Locks specific rows.
o Table Lock: Locks entire tables.

144. What Is a Composite Key & Composite Primary Key?

 Composite Key: A key consisting of two or more columns to uniquely identify a record.
 Composite Primary Key: A combination of multiple columns that together form a primary key.

Examples:

-- Example 1: Composite Key

CREATE TABLE Orders (
OrderID INT,
CustomerID INT,
PRIMARY KEY (OrderID, CustomerID)
);

145. What Are Wild Cards Used in Database for Pattern Matching?

 % (percent) matches zero or more characters.

 _ (underscore) matches exactly one character.

Examples:

-- Example 1: Using % (matches any number of characters)

SELECT * FROM Employees WHERE EmployeeName LIKE 'A%';

-- Example 2: Using _ (matches exactly one character)

SELECT * FROM Employees WHERE EmployeeName LIKE 'J_';

146. What Is a Materialized View?

 A materialized view is a database object that stores the results of a query physically. It is used to speed up
read access and reduces the need to run complex queries multiple times.

Examples:

-- Example 1: Creating a Materialized View

CREATE MATERIALIZED VIEW SalesSummary AS
SELECT ProductID, SUM(SalesAmount) AS TotalSales
FROM Sales
GROUP BY ProductID;

147. What Are User Defined Data Types and When You Should Go for Them?

 User-defined data types allow you to create custom data types that conform to your specific needs.
 Useful when:
o You want to ensure consistent data structure.
o You need to define specific business rules.
o You wish to simplify complex queries and improve readability.
Examples:

-- Example 1: Creating a User Defined Data Type

CREATE TYPE PhoneNumber AS VARCHAR(15);

148. How Do You Implement One-to-One, One-to-Many, and Many-to-Many Relationships While
Designing Tables?

 One-to-One: Link tables using a common unique key.

 One-to-Many: A table references another table's primary key.
 Many-to-Many: Use a junction table linking two tables.

Examples:

-- Example 1: One-to-One
CREATE TABLE Users (
UserID INT PRIMARY KEY,
UserName VARCHAR(50)
);

CREATE TABLE Profile (

ProfileID INT PRIMARY KEY,
UserID INT,
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

-- Example 2: One-to-Many
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(50)
);

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

-- Example 3: Many-to-Many
CREATE TABLE EmployeeProjects (
EmployeeID INT,
ProjectID INT,
PRIMARY KEY (EmployeeID, ProjectID)
);

149. Define Candidate Key, Alternate Key, Composite Key:

 Candidate Key: A set of attributes that uniquely identifies each row.

 Alternate Key: A candidate key that is not selected as the primary key.
 Composite Key: A key composed of two or more attributes to uniquely identify rows.

150. What Are Defaults? Is There a Column to Which a Default Can't Be Bound?

 Defaults: Automatically applied values to a column if no value is provided.

 A column to which a default can't be bound: Columns with NULL as a default value cannot have a constraint
like NOT NULL.

Examples:

-- Example 1: Creating a Default

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
Department VARCHAR(50) DEFAULT 'General'
);

Let me know if you'd like to continue with more!

Let's continue with questions 151-160:

151. How to Restart SQL Server in Single-User Mode? How to Start SQL Server in Minimal
Configuration Mode?

 Single-User Mode: Used to troubleshoot and fix issues when only one user is allowed to connect.

Steps to restart in Single-User Mode:

o Stop SQL Server.

o Use the command:
o [Link] -m
o Start SQL Server in single-user mode.
 Minimal Configuration Mode: Starts SQL Server with the minimal configuration (ignores certain settings)
to help troubleshoot.

Steps to start in Minimal Configuration Mode:

o Use the command:

o [Link] -f

152. What Is Data Mining?

 Data Mining is the process of discovering patterns, correlations, trends, or useful information from large
datasets using algorithms and statistical methods.
 Common techniques include classification, regression, clustering, and association rule mining.

153. Explain the Differences Between Structured Data and Unstructured Data.

 Structured Data: Organized in rows and columns, typically stored in databases (e.g., relational data).
o Example: Data in tables like customers and orders.
 Unstructured Data: No specific format or structure, such as text, images, and audio files.
o Example: Emails, social media posts, video files.

154. What Is the Relationship of ODBC, OLE DB, and ADO?

 ODBC (Open Database Connectivity): A standard API for accessing database management systems
(DBMS).
 OLE DB (Object Linking and Embedding Database): A set of COM-based interfaces for accessing
different data sources.
 ADO (ActiveX Data Objects): A set of COM objects for accessing data, particularly useful in VB and web
applications.
Relationship: ODBC, OLE DB, and ADO are all related technologies used to connect and interact with databases,
but they differ in how they interact with the data and types of supported data sources.

155. What Are the Advantages of Using Stored Procedures?

 Improved Performance: Stored procedures are precompiled, reducing runtime overhead.

 Security: You can grant access to stored procedures instead of direct table access.
 Maintainability: Easier to manage code in stored procedures, which are stored in the database.
 Reusability: Stored procedures can be reused by multiple applications or users.

156. What Are Stored Procedures, and How Do They Differ from Triggers?

 Stored Procedures: A precompiled collection of one or more SQL statements that are executed by an
application or user.
 Triggers: Automatic, event-driven actions that are executed when certain events (like insert, update, delete)
happen in a database.

Difference:

 Stored procedures are invoked manually, while triggers are executed automatically in response to certain
events.

Example of Stored Procedure:

CREATE PROCEDURE GetEmployeeDetails

AS
SELECT * FROM Employees;

Example of Trigger:

CREATE TRIGGER AfterEmployeeInsert

ON Employees
FOR INSERT
AS
BEGIN
PRINT 'New employee added';
END;

157. SQL Query to Find Second Highest Salary of Employee

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

158. SQL Query to Find Max Salary from Each Department

SELECT DeptID, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DeptID;

159. Write SQL Query to Display Current Date

SELECT GETDATE(); -- SQL Server
SELECT CURRENT_DATE; -- MySQL/PostgreSQL
160. Write an SQL Query to Check Whether Date Passed to Query Is Date of Given Format or
Not
-- SQL Server
SELECT CASE
WHEN ISDATE('2025-01-17') = 1 THEN 'Valid Date'
ELSE 'Invalid Date'
END AS DateValidation;

Let me know if you'd like to continue further!

Let's continue with questions 161-170:

161. Write an SQL Query to Print the Name of Distinct Employees Whose DOB Is Between
01/01/1960 to 31/12/1975.
SELECT DISTINCT EmployeeName
FROM Employees
WHERE DOB BETWEEN '1960-01-01' AND '1975-12-31';

162. Write an SQL Query to Find the Number of Employees According to Gender Whose DOB Is
Between 01/01/1960 to 31/12/1975.
SELECT Gender, COUNT(*) AS EmployeeCount
FROM Employees
WHERE DOB BETWEEN '1960-01-01' AND '1975-12-31'
GROUP BY Gender;

163. Write an SQL Query to Find Employee Whose Salary Is Equal or Greater Than 10000.
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary >= 10000;

164. Write an SQL Query to Find the Name of Employee Whose Name Starts With 'M'.
SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE 'M%';

165. Find All Employee Records Containing the Word "Joe", Regardless of Whether It Was
Stored as JOE, Joe, or joe.
SELECT *
FROM Employees
WHERE EmployeeName LIKE '%joe%' COLLATE SQL_Latin1_General_CP1_CI_AS;

 The COLLATE clause allows case-insensitive comparison.

166. Write an SQL Query to Find Year From Date.

SELECT YEAR(DOB) AS BirthYear
FROM Employees;

167. To Fetch Alternate Records from a Table (Even Numbered).

SELECT *
FROM Employees
WHERE MOD(EmployeeID, 2) = 0;

 This assumes EmployeeID is numeric and used for sorting.

168. To Select Alternate Records from a Table (Odd Numbered).

SELECT *
FROM Employees
WHERE MOD(EmployeeID, 2) = 1;

169. Find the 3rd MAX Salary in the Emp Table.

SELECT MAX(Salary) AS ThirdMaxSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM
Employees));

170. Find the 3rd MIN Salary in the Emp Table.

SELECT MIN(Salary) AS ThirdMinSalary
FROM Employees
WHERE Salary > (SELECT MIN(Salary) FROM Employees WHERE Salary > (SELECT MIN(Salary) FROM
Employees));

Let me know if you'd like to continue further!

Let's continue with questions 171-180:

171. Select First n Records from a Table.

-- SQL Server
SELECT TOP 5 * FROM Employees;

-- MySQL/PostgreSQL
SELECT * FROM Employees LIMIT 5;

172. Select Last n Records from a Table.

-- SQL Server
SELECT TOP 5 * FROM Employees ORDER BY EmployeeID DESC;

-- MySQL/PostgreSQL
SELECT * FROM Employees ORDER BY EmployeeID DESC LIMIT 5;
173. List Dept No., Dept Name for All the Departments in Which There Are No Employees in the
Department.
SELECT DeptID, DeptName
FROM Departments
WHERE DeptID NOT IN (SELECT DISTINCT DeptID FROM Employees);

174. How to Get 3 Max Salaries?

SELECT DISTINCT TOP 3 Salary
FROM Employees
ORDER BY Salary DESC;

175. How to Get 3 Min Salaries?

SELECT DISTINCT TOP 3 Salary
FROM Employees
ORDER BY Salary ASC;

176. How to Get nth Max Salaries?

-- To get the 3rd maximum salary
SELECT Salary
FROM (SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees) AS RankedSalaries
WHERE RowNum = 3;

177. Select DISTINCT Records from Emp Table.

SELECT DISTINCT * FROM Employees;

178. How to Delete Duplicate Rows in a Table?

WITH CTE AS (
SELECT EmployeeID, ROW_NUMBER() OVER (PARTITION BY EmployeeName, Salary ORDER BY
EmployeeID) AS RowNum
FROM Employees
)
DELETE FROM CTE WHERE RowNum > 1;

179. Count of Number of Employees in Department Wise.

SELECT DeptID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DeptID;

180. Suppose There Is Annual Salary Information Provided by Emp Table. How to Fetch Monthly
Salary of Each and Every Employee?
SELECT EmployeeName, (Salary / 12) AS MonthlySalary
FROM Employees;

Let me know if you'd like to continue!

Let's continue with questions 181-190:

181. Select All Records from Emp Table Where DeptNo = 10 or 40.
SELECT *
FROM Employees
WHERE DeptID IN (10, 40);

182. Select All Records from Emp Table Where DeptNo = 30 and Salary > 1500.
SELECT *
FROM Employees
WHERE DeptID = 30 AND Salary > 1500;

183. Select All Records from Emp Table Where Job Not In 'SALESMAN' or 'CLERK'.
SELECT *
FROM Employees
WHERE Job NOT IN ('SALESMAN', 'CLERK');

184. Select All Records from Emp Table Where Ename In 'BLAKE', 'SCOTT', 'KING' and
'FORD'.
SELECT *
FROM Employees
WHERE EmployeeName IN ('BLAKE', 'SCOTT', 'KING', 'FORD');

185. Select All Records Where Ename Starts with ‘S’ and Its Length is 6 Characters.
SELECT *
FROM Employees
WHERE EmployeeName LIKE 'S%' AND LEN(EmployeeName) = 6;

186. Select All Records Where Ename May Be Any Number of Characters But It Should End with
‘R’.
SELECT *
FROM Employees
WHERE EmployeeName LIKE '%R';

187. Count MGR and Their Salary in Emp Table.

SELECT MGR, COUNT(*) AS EmployeeCount, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY MGR;

188. In Emp Table Add Comm + Salary as Total Salary.

SELECT EmployeeName, Salary, Comm, (Salary + Comm) AS TotalSalary
FROM Employees;
189. Select Any Salary < 3000 from Emp Table.
SELECT *
FROM Employees
WHERE Salary < 3000;

190. Select All Salary < 3000 from Emp Table.

SELECT *
FROM Employees
WHERE Salary < 3000;

Let me know if you'd like to proceed with the next set of questions!

Let's continue with questions 191-200:

191. Select All the Employee Grouped by DeptNo and Sal in Descending Order.
SELECT DeptID, Salary, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DeptID, Salary
ORDER BY Salary DESC;

192. How Can I Create an Empty Table Emp1 with the Same Structure as Emp?
-- SQL Server
SELECT * INTO Emp1 FROM Emp WHERE 1 = 0;

-- MySQL/PostgreSQL
CREATE TABLE Emp1 AS SELECT * FROM Emp WHERE 1 = 0;

193. How to Retrieve Record Where Salary Between 1000 to 2000?

SELECT *
FROM Employees
WHERE Salary BETWEEN 1000 AND 2000;

194. Select All Records Where Dept No of Both Emp and Dept Table Matches.
SELECT [Link], [Link]
FROM Employees E
JOIN Departments D ON [Link] = [Link];

195. If There Are Two Tables Emp1 and Emp2, and Both Have Common Record. How Can I
Fetch All the Records but Common Records Only Once?
SELECT *
FROM Emp1
UNION
SELECT *
FROM Emp2;
196. How to Fetch Only Common Records from Two Tables Emp and Emp1?
SELECT *
FROM Emp
INTERSECT
SELECT *
FROM Emp1;

197. How Can I Retrieve All Records of Emp1 That Should Not Be Present in Emp2?
SELECT *
FROM Emp1
WHERE NOT EXISTS (SELECT 1 FROM Emp2 WHERE [Link] = [Link]);

198. Count the Totals DeptNo Wise Where More Than 2 Employees Exist.
SELECT DeptID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DeptID
HAVING COUNT(*) > 2;

199. Display the Names of Employees Who Are Working in the Company for the Past 5 Years.
SELECT EmployeeName
FROM Employees
WHERE DATEDIFF(YEAR, JoinDate, GETDATE()) >= 5;

200. Display the List of Employees Who Have Joined the Company Before 30th June 1990 or
After 31st Dec 1990.
SELECT EmployeeName
FROM Employees
WHERE JoinDate < '1990-06-30' OR JoinDate > '1990-12-31';

These are the next set of SQL queries. Let me know if you'd like to continue further!

Let's continue with questions 201-210:

201. Display the Names of Employees Working in Department Number 10 or 20 or 40 or

Employees Working as Clerks, Salesman, or Analyst.
SELECT EmployeeName
FROM Employees
WHERE DeptID IN (10, 20, 40)
OR Job IN ('CLERK', 'SALESMAN', 'ANALYST');

202. Display the Names of Employees Whose Name Starts with Alphabet S.
SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE 'S%';
203. Display Employee Names for Employees Whose Name Ends with Alphabet 'R'.
SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE '%R';

204. Select Distinct Records from Emp Table.

SELECT DISTINCT *
FROM Employees;

205. Differentiate Between SQL and NoSQL Databases.

SQL Databases:

 Relational databases (RDBMS) that store data in tables.

 Structured Query Language (SQL) is used to manage data.
 Data is stored in a fixed schema (tables, rows, columns).
 Examples: MySQL, PostgreSQL, SQL Server.

NoSQL Databases:

 Non-relational databases that store data in various formats (documents, key-value pairs, wide-column stores).
 Does not require a fixed schema.
 Better for unstructured data and scalability.
 Examples: MongoDB, Cassandra, Redis.

206. What Is an Index?

An index is a database object used to speed up the retrieval of rows from a table. It improves the performance of
queries by reducing the number of disk accesses required.

-- Creating an index on a table

CREATE INDEX idx_employee_name ON Employees(EmployeeName);

207. How Do You Handle NULL Values in SQL?

NULL represents the absence of a value in SQL. To handle NULL values:

 Use IS NULL or IS NOT NULL to filter out NULL values.

 Use COALESCE() or IFNULL() to replace NULL values.

SELECT EmployeeName, COALESCE(Salary, 0) AS Salary FROM Employees;

208. Explain the Differences Between the CHARINDEX and PATINDEX Functions.

 CHARINDEX: Finds the position of a substring in a string.

 PATINDEX: Similar to CHARINDEX, but supports wildcard characters and pattern matching.

-- Using CHARINDEX
SELECT CHARINDEX('John', 'Hello John Doe'); -- Returns 7

-- Using PATINDEX
SELECT PATINDEX('%J%n%', 'Hello John Doe'); -- Returns 7
209. What Is a Natural Join and When Would You Use It?

A Natural Join automatically joins two tables based on columns with the same name and data type.

SELECT *
FROM Employees
NATURAL JOIN Departments;

You would use it when you want to join tables based on columns that share the same name and structure without
explicitly defining the ON clause.

210. What Is the Purpose of the DISTINCT Keyword?

The DISTINCT keyword removes duplicate values from the result set, returning only unique records.

SELECT DISTINCT Department FROM Employees;

Let me know if you'd like to continue with the next set of questions!

Let's continue with questions 211-220:

211. How to Fetch the First N Records from a Table?

-- For SQL Server
SELECT TOP 5 *
FROM Employees;

-- For MySQL/PostgreSQL
SELECT *
FROM Employees
LIMIT 5;

212. How to Fetch the Last N Records from a Table?

-- For SQL Server
SELECT TOP 5 *
FROM Employees
ORDER BY EmployeeID DESC;

-- For MySQL/PostgreSQL
SELECT *
FROM Employees
ORDER BY EmployeeID DESC
LIMIT 5;

213. List Department Number, Department Name for All Departments in Which There Are No
Employees in the Department.
SELECT DeptID, DeptName
FROM Departments
WHERE DeptID NOT IN (SELECT DISTINCT DeptID FROM Employees);
214. How to Get the Top 3 Maximum Salaries?
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 3;

215. How to Get the Top 3 Minimum Salaries?

SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary ASC
LIMIT 3;

216. How to Get the Nth Maximum Salary?

SELECT MAX(Salary) AS NthMaxSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM
Employees)));

217. How to Get the Nth Minimum Salary?

SELECT MIN(Salary) AS NthMinSalary
FROM Employees
WHERE Salary > (SELECT MIN(Salary) FROM Employees WHERE Salary > (SELECT MIN(Salary) FROM
Employees)));

218. Select Distinct Records from Emp Table.

SELECT DISTINCT *
FROM Employees;

219. How to Delete Duplicate Rows in a Table?

-- Using a CTE in SQL Server
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY EmployeeID) AS RowNum
FROM Employees
)
DELETE FROM CTE WHERE RowNum > 1;

220. Count the Number of Employees in Each Department.

SELECT DeptID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DeptID;

These are the next set of queries. Let me know if you'd like to continue further!

Let's continue with questions 221-230:

221. How to Select All Records Where Salary is Between 1000 and 2000?
SELECT *
FROM Employees
WHERE Salary BETWEEN 1000 AND 2000;

222. How to Select All Records Where DeptNo of Both Emp and Dept Tables Matches?
SELECT e.*
FROM Employees e
INNER JOIN Departments d ON [Link] = [Link];

223. How to Fetch All Records but Common Records Only Once from Two Tables (Emp1 and
Emp2)?
SELECT *
FROM Emp1
INTERSECT
SELECT *
FROM Emp2;

224. How to Fetch Only Common Records from Two Tables (Emp and Emp1)?
SELECT *
FROM Emp
INNER JOIN Emp1 ON [Link] = [Link];

225. How to Retrieve All Records from Emp1 that Are Not Present in Emp2?
SELECT *
FROM Emp1
WHERE EmployeeID NOT IN (SELECT EmployeeID FROM Emp2);

226. Count the Total Number of Employees in Each Department Where More Than 2 Employees
Exist.
SELECT DeptID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DeptID
HAVING COUNT(*) > 2;

227. Display the Names of Employees Who Have Worked for the Company for the Past 5 Years.
SELECT EmployeeName
FROM Employees
WHERE HireDate <= DATEADD(YEAR, -5, GETDATE());

228. Display the List of Employees Who Have Joined the Company Before 30th June 1990 or
After 31st December 1990.
SELECT EmployeeName
FROM Employees
WHERE HireDate < '1990-06-30' OR HireDate > '1990-12-31';
229. Display the Names of Employees Working in Department Numbers 10, 20, or 40, or
Employees Working as Clerks, Salesmen, or Analysts.
SELECT EmployeeName
FROM Employees
WHERE DeptID IN (10, 20, 40)
OR Job IN ('CLERK', 'SALESMAN', 'ANALYST');

230. Display the Names of Employees Whose Name Starts with the Alphabet 'S'.
SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE 'S%';

That concludes the next set of queries. Let me know if you would like to continue or need any further clarification!

Let's continue with questions 231-240:

231. Display Employee Names for Employees Whose Name Ends with the Alphabet 'R'.
SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE '%R';

232. How to Find All Employees with Even Numbered IDs?

SELECT *
FROM Employees
WHERE MOD(EmployeeID, 2) = 0;

233. How to Find All Employees with Odd Numbered IDs?

SELECT *
FROM Employees
WHERE MOD(EmployeeID, 2) = 1;

234. How to Find the 3rd Highest Salary in the Employees Table?
SELECT MIN(Salary) AS ThirdHighestSalary
FROM Employees
WHERE Salary NOT IN (
SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC LIMIT 2
);

235. How to Find the 3rd Lowest Salary in the Employees Table?
SELECT MAX(Salary) AS ThirdLowestSalary
FROM Employees
WHERE Salary NOT IN (
SELECT DISTINCT Salary FROM Employees ORDER BY Salary ASC LIMIT 2
);
236. How to Get the First 5 Records from the Employees Table?
-- For SQL Server
SELECT TOP 5 *
FROM Employees;

-- For MySQL/PostgreSQL
SELECT *
FROM Employees
LIMIT 5;

237. How to Get the Last 5 Records from the Employees Table?
-- For SQL Server
SELECT TOP 5 *
FROM Employees
ORDER BY EmployeeID DESC;

-- For MySQL/PostgreSQL
SELECT *
FROM Employees
ORDER BY EmployeeID DESC
LIMIT 5;

238. How to List All Department Numbers and Names Where There Are No Employees?
SELECT DeptID, DeptName
FROM Departments
WHERE DeptID NOT IN (SELECT DISTINCT DeptID FROM Employees);

239. How to Get the Top 3 Salaries from the Employees Table?
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 3;

240. How to Get the Top 3 Lowest Salaries from the Employees Table?
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary ASC
LIMIT 3;

That concludes the next set of queries! Let me know if you'd like to continue or need any more explanations!

Let's continue with questions 241-250:

241. How to Find the Nth Highest Salary in a Table?

SELECT Salary
FROM (SELECT DISTINCT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees) AS Temp
WHERE RowNum = N; -- Replace N with the desired Nth highest value
242. How to Find the Nth Lowest Salary in a Table?
SELECT Salary
FROM (SELECT DISTINCT Salary, ROW_NUMBER() OVER (ORDER BY Salary ASC) AS RowNum
FROM Employees) AS Temp
WHERE RowNum = N; -- Replace N with the desired Nth lowest value

243. How to Find the Total Number of Employees in Each Department and Their Average Salary?
SELECT DeptID, COUNT(*) AS EmployeeCount, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DeptID;

244. How to List the Employees Who Have the Same Salary?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary IN (SELECT Salary FROM Employees GROUP BY Salary HAVING COUNT(*) > 1);

245. How to Find the Employee with the Maximum Salary in Each Department?
SELECT DeptID, EmployeeName, Salary
FROM Employees
WHERE (DeptID, Salary) IN (SELECT DeptID, MAX(Salary) FROM Employees GROUP BY DeptID);

246. How to Get All Employees Who Joined Before a Specific Date?
SELECT *
FROM Employees
WHERE HireDate < '2020-01-01'; -- Change the date as per requirement

247. How to Get the Names of Employees Whose Salary is Greater than the Average Salary?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

248. How to Find Employees Who Have Been in the Company for More than 10 Years?
SELECT EmployeeName
FROM Employees
WHERE HireDate <= DATEADD(YEAR, -10, GETDATE());

249. How to List Employees Whose Name Starts with a Specific Letter (e.g., 'A')?
SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE 'A%';

250. How to Find the Average Salary of Employees Working in Each Department?
SELECT DeptID, AVG(Salary) AS AverageSalary
FROM Employees
GROUP BY DeptID;
That concludes the next set of queries! Let me know if you'd like to continue further or need more information!

Let's continue with questions 251-260:

251. How to Retrieve Employees Whose Names Contain a Specific Word (e.g., "John")?
SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE '%John%';

252. How to Retrieve Employees Working in a Specific Department and Having a Salary Greater
Than a Value?
SELECT EmployeeName, Salary
FROM Employees
WHERE DeptID = 10 AND Salary > 5000;

253. How to Find the Employees Who Have the Highest Salary in the Company?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

254. How to Find the Employees Who Have a Salary Between Two Values (e.g., Between 3000 and
6000)?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary BETWEEN 3000 AND 6000;

255. How to Find the Employees Who Do Not Have a Manager?

SELECT EmployeeName
FROM Employees
WHERE ManagerID IS NULL;

256. How to Find the Employee with the Minimum Salary?

SELECT EmployeeName, Salary
FROM Employees
WHERE Salary = (SELECT MIN(Salary) FROM Employees);

257. How to Get the Employees Who Joined the Company After a Certain Date?
SELECT EmployeeName, HireDate
FROM Employees
WHERE HireDate > '2020-01-01'; -- Change the date as per requirement

258. How to Find the Employees Whose Name Is Exactly a Specific Value (e.g., 'John Doe')?
SELECT EmployeeName
FROM Employees
WHERE EmployeeName = 'John Doe';

259. How to List Employees Who Earn More Than the Average Salary of All Employees?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

260. How to Find the Total Salary Paid to Employees in Each Department?
SELECT DeptID, SUM(Salary) AS TotalSalary
FROM Employees
GROUP BY DeptID;

That's the next set of queries! Let me know if you'd like to continue or need any further clarifications.

Let's continue with questions 261-270:

261. How to List Employees Who Are in a Specific Department and Have a Salary Greater Than a
Given Value?
SELECT EmployeeName, Salary
FROM Employees
WHERE DeptID = 20 AND Salary > 5000;

262. How to List the Employees Who Have a Salary Equal to or Greater Than the Average Salary
of Their Department?
SELECT EmployeeName, DeptID, Salary
FROM Employees E
WHERE Salary >= (SELECT AVG(Salary) FROM Employees WHERE DeptID = [Link]);

263. How to Retrieve Employees Who Are Either Managers or Have a Specific Job Title?
SELECT EmployeeName
FROM Employees
WHERE JobTitle IN ('Manager', 'Analyst');

264. How to Find Employees Who Have Worked for More Than a Certain Number of Years?
SELECT EmployeeName, DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsWorked
FROM Employees
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5;

265. How to Find Employees Who Have Been in the Company for Less Than a Certain Number of
Years?
SELECT EmployeeName
FROM Employees
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) < 3;
266. How to Retrieve Employees Whose Name Starts with the Alphabet 'S'?
SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE 'S%';

267. How to Retrieve Employees Whose Name Ends with the Alphabet 'R'?
SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE '%R';

268. How to Retrieve Employees Whose Salary Is in the Top 10 Highest Salaries?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary IN (SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC LIMIT 10);

269. How to Get Employees Who Were Hired in the Last Year?
SELECT EmployeeName, HireDate
FROM Employees
WHERE HireDate >= '2023-01-01' AND HireDate < '2024-01-01';

270. How to Retrieve All Employees in Departments That Have More Than 5 Employees?
SELECT [Link], [Link]
FROM Employees E
WHERE [Link] IN (
SELECT DeptID
FROM Employees
GROUP BY DeptID
HAVING COUNT(*) > 5
);

That concludes this set of queries! Let me know if you'd like to continue further or need any clarification!

Let's continue with questions 271-280:

271. How to Find Employees Whose Salary Is Between Two Values, and Sort by Salary in
Descending Order?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary BETWEEN 3000 AND 6000
ORDER BY Salary DESC;

272. How to Find the Total Number of Employees in Each Department?

SELECT DeptID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DeptID;
273. How to Retrieve Employees Who Have Worked in Multiple Departments?
SELECT EmployeeName
FROM Employees
GROUP BY EmployeeName
HAVING COUNT(DISTINCT DeptID) > 1;

274. How to Retrieve Employees Whose Salary Is the Same as Their Manager's Salary?
SELECT [Link]
FROM Employees E
JOIN Employees M ON [Link] = [Link]
WHERE [Link] = [Link];

275. How to Retrieve Employees Whose Salary Is Greater Than the Average Salary of All
Employees in Their Department?
SELECT [Link]
FROM Employees E
JOIN (SELECT DeptID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DeptID) AS D ON [Link] = [Link]
WHERE [Link] > [Link];

276. How to Retrieve Employees Who Are Either in Department 10 or Department 20?
SELECT EmployeeName
FROM Employees
WHERE DeptID IN (10, 20);

277. How to Find Employees Who Have a Salary Above the Average Salary Across All
Departments?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

278. How to Retrieve Employees Who Have Worked in the Company for More Than 10 Years?
SELECT EmployeeName
FROM Employees
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 10;

279. How to Retrieve Employees Who Have Been Promoted in the Last Year?
SELECT EmployeeName, PromotionDate
FROM Employees
WHERE PromotionDate >= '2023-01-01' AND PromotionDate < '2024-01-01';

280. How to Find the Employees Who Have the Lowest Salary in Each Department?
SELECT [Link], [Link], [Link]
FROM Employees E
JOIN (SELECT DeptID, MIN(Salary) AS MinSalary
FROM Employees
GROUP BY DeptID) AS D ON [Link] = [Link]
WHERE [Link] = [Link];

Let me know if you'd like to continue with more queries or need any further explanations!

Let's continue with questions 281-290:

281. How to Retrieve Employees Who Have a Specific Job Title and Belong to a Specific
Department?
SELECT EmployeeName, JobTitle, DeptID
FROM Employees
WHERE JobTitle = 'Manager' AND DeptID = 10;

282. How to Find the Average Salary of Employees in Each Department?

SELECT DeptID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DeptID;

283. How to Retrieve Employees Whose Salary is Greater than 5000 and Less than 10000?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary BETWEEN 5000 AND 10000;

284. How to Find the Employee with the Highest Salary in a Specific Department?
SELECT EmployeeName, Salary
FROM Employees
WHERE DeptID = 20
ORDER BY Salary DESC
LIMIT 1;

285. How to Retrieve the Names of Employees Whose Name Contains the Word 'John'?
SELECT EmployeeName
FROM Employees
WHERE EmployeeName LIKE '%John%';

286. How to Retrieve Employees with the Same Job Title and Same Department?
SELECT [Link], [Link]
FROM Employees E1
JOIN Employees E2 ON [Link] = [Link] AND [Link] = [Link]
WHERE [Link] != [Link];

287. How to Retrieve Employees Who Have a Null Value for a Specific Column (e.g., Bonus)?
SELECT EmployeeName
FROM Employees
WHERE Bonus IS NULL;

288. How to Retrieve Employees Who Have a Specific Combination of Job Title and Salary
Range?
SELECT EmployeeName, JobTitle, Salary
FROM Employees
WHERE JobTitle = 'Analyst' AND Salary BETWEEN 4000 AND 6000;

289. How to Retrieve Employees Who Have Joined After a Specific Date and Belong to a Certain
Department?
SELECT EmployeeName, HireDate
FROM Employees
WHERE HireDate > '2022-01-01' AND DeptID = 30;

290. How to Retrieve the Department with the Maximum Number of Employees?
SELECT DeptID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DeptID
ORDER BY EmployeeCount DESC
LIMIT 1;

That completes this set of queries! Let me know if you'd like to continue further or need clarification!

Let's continue with questions 291-300:

291. How to Retrieve Employees Who Do Not Have a Manager?

SELECT EmployeeName
FROM Employees
WHERE ManagerID IS NULL;

292. How to Retrieve Employees Who Have Worked for More Than 5 Years in the Company?
SELECT EmployeeName, HireDate
FROM Employees
WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5;

293. How to Find Employees Who Have the Same Salary as the Maximum Salary in Their
Department?
SELECT [Link], [Link]
FROM Employees E
JOIN (SELECT DeptID, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DeptID) AS D ON [Link] = [Link]
WHERE [Link] = [Link];

294. How to Retrieve the Number of Employees in Each Department, Sorted by Department
Name?
SELECT DeptID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DeptID
ORDER BY DeptID;

295. How to Find Employees with the Highest and Lowest Salaries in the Company?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees)
OR Salary = (SELECT MIN(Salary) FROM Employees);

296. How to Retrieve Employees Who Were Hired in the Last 6 Months?
SELECT EmployeeName, HireDate
FROM Employees
WHERE HireDate > DATEADD(MONTH, -6, GETDATE());

297. How to Find Employees Whose Salary Is Greater Than the Average Salary of All Employees?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

298. How to Retrieve Employees Who Have Been Assigned to More Than One Project?
SELECT EmployeeName
FROM EmployeeProjects
GROUP BY EmployeeID
HAVING COUNT(DISTINCT ProjectID) > 1;

299. How to Find Employees Who Have Not Been Assigned to Any Project?
SELECT EmployeeName
FROM Employees
WHERE EmployeeID NOT IN (SELECT DISTINCT EmployeeID FROM EmployeeProjects);

300. How to Retrieve Employees Who Have Worked for a Specific Manager?
SELECT EmployeeName
FROM Employees
WHERE ManagerID = (SELECT EmployeeID FROM Employees WHERE EmployeeName = 'John Doe');

That wraps up questions 291-300! Let me know if you'd like to continue or need further explanations!

Let's continue with questions 301-310:

301. How to Retrieve Employees Who Are Currently Not Assigned to Any Department?
SELECT EmployeeName
FROM Employees
WHERE DeptID IS NULL;
302. How to Find the Department with the Lowest Average Salary?
SELECT DeptID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DeptID
ORDER BY AvgSalary ASC
LIMIT 1;

303. How to Retrieve Employees with a Salary Above the Department's Average Salary?
SELECT [Link], [Link]
FROM Employees E
JOIN (SELECT DeptID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DeptID) AS D ON [Link] = [Link]
WHERE [Link] > [Link];

304. How to Retrieve Employees with the Top 5 Highest Salaries?

SELECT EmployeeName, Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 5;

305. How to Find the Department with the Maximum and Minimum Salary?
SELECT DeptID, MAX(Salary) AS MaxSalary, MIN(Salary) AS MinSalary
FROM Employees
GROUP BY DeptID;

306. How to Retrieve Employees Who Have a Bonus Greater Than the Average Bonus?
SELECT EmployeeName, Bonus
FROM Employees
WHERE Bonus > (SELECT AVG(Bonus) FROM Employees);

307. How to Retrieve Employees Who Have a Job Title of 'Manager' and Earn More Than 7000?
SELECT EmployeeName, Salary
FROM Employees
WHERE JobTitle = 'Manager' AND Salary > 7000;

308. How to Find the Second Highest Salary in the Company?

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

309. How to Retrieve Employees Who Have a Salary Equal to or Greater Than the Median
Salary?
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary >= (SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) FROM Employees);
310. How to Retrieve Employees Who Are Older Than 40 and Have Been Employed for More
Than 10 Years?
SELECT EmployeeName, Age, HireDate
FROM Employees
WHERE Age > 40 AND DATEDIFF(YEAR, HireDate, GETDATE()) > 10;

That completes questions 301-310! Let me know if you'd like to continue or need further explanations.
Let's walk through each of your queries with examples. We will start by creating an "Employees" table and use it for
all of the queries. Then, I'll provide explanations, examples, and the corresponding SQL syntax for each of your
requests.

Step 1: Create the Employees Table

CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(100),
DeptID INT,
Salary DECIMAL(10, 2),
JobTitle VARCHAR(50),
HireDate DATE,
Gender CHAR(1),
DOB DATE,
Bonus DECIMAL(10, 2)
);

157. SQL Query to Find Second Highest Salary of Employee

Explanation:

To find the second highest salary, we can use a subquery to exclude the highest salary and find the maximum from
the remaining salaries.

Example:
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
Syntax:
SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

158. SQL Query to Find Max Salary from Each Department

Explanation:

To find the maximum salary for each department, we group the employees by DeptID and apply the MAX() function.

Example:
SELECT DeptID, MAX(Salary) AS MaxSalary
FROM Employees
GROUP BY DeptID;
Syntax:
SELECT DeptID, MAX(Salary)
FROM Employees
GROUP BY DeptID;

159. Write SQL Query to Display Current Date

Explanation:

To display the current date, use the CURRENT_DATE function (or GETDATE() in SQL Server).
Example:
SELECT CURRENT_DATE AS Today;
Syntax:
SELECT CURRENT_DATE;

160. Write SQL Query to Check Whether Date Passed to Query is Date of Given Format or Not

Explanation:

You can use TRY_CONVERT() or ISDATE() in SQL Server to check if a given string can be converted into a date.

Example:
SELECT CASE
WHEN ISDATE('2023-12-31') = 1 THEN 'Valid Date'
ELSE 'Invalid Date'
END;
Syntax:
SELECT CASE WHEN ISDATE('2023-12-31') = 1 THEN 'Valid Date' ELSE 'Invalid Date' END;

161. Write SQL Query to Print the Name of Distinct Employee Whose DOB is Between 01/01/1960
and 31/12/1975

Explanation:

To filter employees based on their date of birth (DOB), we can use the BETWEEN operator along with the DISTINCT
keyword to ensure that the names are unique.

Example:
SELECT DISTINCT EmpName
FROM Employees
WHERE DOB BETWEEN '1960-01-01' AND '1975-12-31';
Syntax:
SELECT DISTINCT EmpName
FROM Employees
WHERE DOB BETWEEN '1960-01-01' AND '1975-12-31';

162. Write SQL Query to Find Number of Employees According to Gender Whose DOB is
Between 01/01/1960 and 31/12/1975

Explanation:

To count the number of employees based on gender and DOB, we can group the data by Gender and use the COUNT()
function.

Example:
SELECT Gender, COUNT(EmpID) AS EmployeeCount
FROM Employees
WHERE DOB BETWEEN '1960-01-01' AND '1975-12-31'
GROUP BY Gender;
Syntax:
SELECT Gender, COUNT(EmpID) AS EmployeeCount
FROM Employees
WHERE DOB BETWEEN '1960-01-01' AND '1975-12-31'
GROUP BY Gender;

163. Write SQL Query to Find Employee Whose Salary is Equal or Greater Than 10000
Explanation:

We can filter employees whose salary is greater than or equal to 10000 using the >= operator.

Example:
SELECT EmpName, Salary
FROM Employees
WHERE Salary >= 10000;
Syntax:
SELECT EmpName, Salary
FROM Employees
WHERE Salary >= 10000;

164. Write SQL Query to Find Name of Employee Whose Name Starts with ‘M’

Explanation:

To filter employees whose name starts with 'M', use the LIKE operator with a pattern M%.

Example:
SELECT EmpName
FROM Employees
WHERE EmpName LIKE 'M%';
Syntax:
SELECT EmpName
FROM Employees
WHERE EmpName LIKE 'M%';

165. Find All Employee Records Containing the Word "Joe", Regardless of Whether It Was
Stored as JOE, Joe, or joe

Explanation:

To search for records with a case-insensitive match, we can use LIKE with the LOWER() function to standardize the
comparison.

Example:
SELECT *
FROM Employees
WHERE LOWER(EmpName) LIKE '%joe%';
Syntax:
SELECT *
FROM Employees
WHERE LOWER(EmpName) LIKE '%joe%';

166. Write SQL Query to Find Year from Date

Explanation:

To extract the year from a date, use the YEAR() function.

Example:
SELECT YEAR(HireDate) AS HireYear
FROM Employees;
Syntax:
SELECT YEAR(HireDate) AS HireYear
FROM Employees;
167. To Fetch Alternate Records from a Table (Even Numbered)

Explanation:

To get alternate records, use the ROW_NUMBER() window function and filter for even rows.

Example:
WITH RowNum AS (
SELECT EmpID, EmpName, ROW_NUMBER() OVER (ORDER BY EmpID) AS RowNum
FROM Employees
)
SELECT EmpID, EmpName
FROM RowNum
WHERE RowNum % 2 = 0;
Syntax:
WITH RowNum AS (
SELECT EmpID, EmpName, ROW_NUMBER() OVER (ORDER BY EmpID) AS RowNum
FROM Employees
)
SELECT EmpID, EmpName
FROM RowNum
WHERE RowNum % 2 = 0;

168. To Select Alternate Records from a Table (Odd Numbered)

Explanation:

To fetch odd-numbered records, filter the result of ROW_NUMBER() for rows where the row number is odd.

Example:
WITH RowNum AS (
SELECT EmpID, EmpName, ROW_NUMBER() OVER (ORDER BY EmpID) AS RowNum
FROM Employees
)
SELECT EmpID, EmpName
FROM RowNum
WHERE RowNum % 2 != 0;
Syntax:
WITH RowNum AS (
SELECT EmpID, EmpName, ROW_NUMBER() OVER (ORDER BY EmpID) AS RowNum
FROM Employees
)
SELECT EmpID, EmpName
FROM RowNum
WHERE RowNum % 2 != 0;

169. Find the 3rd MAX Salary in the Emp Table

Explanation:

To find the third highest salary, we use a subquery to exclude the top two salaries and then select the maximum of the
remaining.

Example:
SELECT MAX(Salary) AS ThirdMaxSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM
Employees));
Syntax:
SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM
Employees));

170. Find the 3rd MIN Salary in the Emp Table

Explanation:

To find the third lowest salary, use a similar approach as the third highest salary.

Example:
SELECT MIN(Salary) AS ThirdMinSalary
FROM Employees
WHERE Salary > (SELECT MIN(Salary) FROM Employees WHERE Salary > (SELECT MIN(Salary) FROM
Employees));
Syntax:
SELECT MIN(Salary)
FROM Employees
WHERE Salary > (SELECT MIN(Salary) FROM Employees WHERE Salary > (SELECT MIN(Salary) FROM
Employees));

This covers the first set of queries (157-170). Let me know if you'd like me to continue for the rest or need further
explanations!

Let's continue with the next set of queries.

171. Select FIRST N Records from a Table

Explanation:

To select the first N records from a table, we use the LIMIT clause in MySQL or TOP in SQL Server.

Example (MySQL):
SELECT *
FROM Employees
LIMIT 5;
Example (SQL Server):
SELECT TOP 5 *
FROM Employees;
Syntax:

 MySQL:

SELECT *
FROM Employees
LIMIT N;

 SQL Server:

SELECT TOP N *
FROM Employees;

172. Select LAST N Records from a Table

Explanation:

To select the last N records, you can use ORDER BY and LIMIT in MySQL or a ROW_NUMBER() in SQL Server.

Example (MySQL):
SELECT *
FROM Employees
ORDER BY EmpID DESC
LIMIT 5;
Example (SQL Server):
WITH RowNum AS (
SELECT EmpID, EmpName, ROW_NUMBER() OVER (ORDER BY EmpID DESC) AS RowNum
FROM Employees
)
SELECT EmpID, EmpName
FROM RowNum
WHERE RowNum <= 5;
Syntax:

 MySQL:

SELECT *
FROM Employees
ORDER BY EmpID DESC
LIMIT N;

 SQL Server:

WITH RowNum AS (
SELECT EmpID, EmpName, ROW_NUMBER() OVER (ORDER BY EmpID DESC) AS RowNum
FROM Employees
)
SELECT EmpID, EmpName
FROM RowNum
WHERE RowNum <= N;

173. List Dept No., Dept Name for All the Departments in Which There Are No Employees

Explanation:

To find departments with no employees, use a LEFT JOIN with the Employees table and filter for NULL values in the
employee column.

Example:
SELECT [Link], [Link]
FROM Department Dept
LEFT JOIN Employees Emp ON [Link] = [Link]
WHERE [Link] IS NULL;
Syntax:
SELECT [Link], [Link]
FROM Department Dept
LEFT JOIN Employees Emp ON [Link] = [Link]
WHERE [Link] IS NULL;

174. How to Get 3 Max Salaries?

Explanation:

To get the top 3 maximum salaries, use the LIMIT clause in MySQL or TOP in SQL Server.
Example (MySQL):
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary DESC
LIMIT 3;
Example (SQL Server):
SELECT TOP 3 DISTINCT Salary
FROM Employees
ORDER BY Salary DESC;
Syntax:

 MySQL:

SELECT DISTINCT Salary

FROM Employees
ORDER BY Salary DESC
LIMIT 3;

 SQL Server:

SELECT TOP 3 DISTINCT Salary

FROM Employees
ORDER BY Salary DESC;

175. How to Get 3 Min Salaries?

Explanation:

To get the top 3 minimum salaries, use the LIMIT clause in MySQL or TOP in SQL Server.

Example (MySQL):
SELECT DISTINCT Salary
FROM Employees
ORDER BY Salary ASC
LIMIT 3;
Example (SQL Server):
SELECT TOP 3 DISTINCT Salary
FROM Employees
ORDER BY Salary ASC;
Syntax:

 MySQL:

SELECT DISTINCT Salary

FROM Employees
ORDER BY Salary ASC
LIMIT 3;

 SQL Server:

SELECT TOP 3 DISTINCT Salary

FROM Employees
ORDER BY Salary ASC;

176. How to Get nth Max Salaries?

Explanation:

To get the nth maximum salary, use a subquery and a condition to exclude the top n-1 salaries.
Example:
SELECT MAX(Salary) AS nthMaxSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees)));
Syntax:
SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary)
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees)));

177. Select DISTINCT Records from Emp Table

Explanation:

To select distinct records, use the DISTINCT keyword.

Example:
SELECT DISTINCT EmpName, Salary
FROM Employees;
Syntax:
SELECT DISTINCT EmpName, Salary
FROM Employees;

178. How to Delete Duplicate Rows in a Table?

Explanation:

You can delete duplicates by identifying the rows with a ROW_NUMBER() or RANK() and removing those that are
duplicates.

Example:
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY EmpName, Salary ORDER BY EmpID) AS RowNum
FROM Employees
)
DELETE FROM CTE WHERE RowNum > 1;
Syntax:
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY EmpName, Salary ORDER BY EmpID) AS RowNum
FROM Employees
)
DELETE FROM CTE WHERE RowNum > 1;

179. Count of Number of Employees in Department Wise

Explanation:

To count the number of employees in each department, use the COUNT() function and group by DeptID.

Example:
SELECT DeptID, COUNT(EmpID) AS EmployeeCount
FROM Employees
GROUP BY DeptID;
Syntax:
SELECT DeptID, COUNT(EmpID)
FROM Employees
GROUP BY DeptID;

180. Suppose There Is Annual Salary Information Provided by Emp Table. How to Fetch Monthly
Salary of Each and Every Employee?

Explanation:

To get the monthly salary, divide the annual salary by 12.

Example:
SELECT EmpName, Salary / 12 AS MonthlySalary
FROM Employees;
Syntax:
SELECT EmpName, Salary / 12 AS MonthlySalary
FROM Employees;

181. Select All Records from Emp Table Where DeptNo = 10 or 40

Explanation:

You can use the IN operator to filter records by multiple department numbers.

Example:
SELECT *
FROM Employees
WHERE DeptID IN (10, 40);
Syntax:
SELECT *
FROM Employees
WHERE DeptID IN (10, 40);

182. Select All Records from Emp Table Where DeptNo = 30 and Salary > 1500

Explanation:

You can filter records using the AND operator to satisfy both conditions.

Example:
SELECT *
FROM Employees
WHERE DeptID = 30 AND Salary > 1500;
Syntax:
SELECT *
FROM Employees
WHERE DeptID = 30 AND Salary > 1500;

This completes the second set of queries (171-182). Would you like to continue with the rest, or is there something
specific you'd like to explore?

Let's continue with the next set of queries.

183. Select All Records from Emp Table Where Job Not in SALESMAN or CLERK

Explanation:

To exclude specific job titles, use the NOT IN operator.

Example:
SELECT *
FROM Employees
WHERE Job NOT IN ('SALESMAN', 'CLERK');
Syntax:
SELECT *
FROM Employees
WHERE Job NOT IN ('SALESMAN', 'CLERK');

184. Select All Records from Emp Table Where Ename in ('BLAKE', 'SCOTT', 'KING', 'FORD')

Explanation:

You can filter records by specifying a list of values in the IN clause.

Example:
SELECT *
FROM Employees
WHERE Ename IN ('BLAKE', 'SCOTT', 'KING', 'FORD');
Syntax:
SELECT *
FROM Employees
WHERE Ename IN ('BLAKE', 'SCOTT', 'KING', 'FORD');

185. Select All Records Where Ename Starts with ‘S’ and Its Length Is 6 Characters

Explanation:

You can use LIKE to filter records where the name starts with 'S', and use LENGTH() to check the length of the name.

Example:
SELECT *
FROM Employees
WHERE Ename LIKE 'S%'
AND LENGTH(Ename) = 6;
Syntax:
SELECT *
FROM Employees
WHERE Ename LIKE 'S%'
AND LENGTH(Ename) = 6;

186. Select All Records Where Ename May Be Any Number of Characters but It Should End with
‘R’

Explanation:

To match records where the name ends with 'R', use the LIKE clause.

Example:
SELECT *
FROM Employees
WHERE Ename LIKE '%R';
Syntax:
SELECT *
FROM Employees
WHERE Ename LIKE '%R';

187. Count MGR and Their Salary in Emp Table

Explanation:

To count the number of managers (MGR) and their salary, use the COUNT() function along with a WHERE clause for
filtering managers.

Example:
SELECT COUNT(EmpID) AS ManagerCount, AVG(Salary) AS AverageSalary
FROM Employees
WHERE Job = 'MGR';
Syntax:
SELECT COUNT(EmpID) AS ManagerCount, AVG(Salary) AS AverageSalary
FROM Employees
WHERE Job = 'MGR';

188. In Emp Table, Add Comm+Sal as Total Salary

Explanation:

To add the comm (commission) and sal (salary) fields and display as TotalSalary, use an expression in the SELECT
clause.

Example:
SELECT EmpID, Ename, Salary + Comm AS TotalSalary
FROM Employees;
Syntax:
SELECT EmpID, Ename, Salary + Comm AS TotalSalary
FROM Employees;

189. Select Any Salary < 3000 from Emp Table

Explanation:

To find salaries less than 3000, use a WHERE clause with a condition.

Example:
SELECT *
FROM Employees
WHERE Salary < 3000
LIMIT 1;
Syntax:
SELECT *
FROM Employees
WHERE Salary < 3000
LIMIT 1;

190. Select All Salaries < 3000 from Emp Table

Explanation:

To get all records with salaries less than 3000, use the WHERE clause.

Example:
SELECT *
FROM Employees
WHERE Salary < 3000;
Syntax:
SELECT *
FROM Employees
WHERE Salary < 3000;

191. Select All the Employees Group By DeptNo and Salary in Descending Order

Explanation:

To group employees by department and salary, use GROUP BY along with ORDER BY to sort the results.

Example:
SELECT DeptID, Salary, COUNT(EmpID) AS EmployeeCount
FROM Employees
GROUP BY DeptID, Salary
ORDER BY Salary DESC;
Syntax:
SELECT DeptID, Salary, COUNT(EmpID)
FROM Employees
GROUP BY DeptID, Salary
ORDER BY Salary DESC;

192. How Can I Create an Empty Table Emp1 with the Same Structure as Emp?

Explanation:

To create a new table with the same structure as an existing table without copying any data, use the CREATE TABLE
statement with a LIKE clause.

Example:
CREATE TABLE Emp1 LIKE Employees;
Syntax:
CREATE TABLE Emp1 LIKE Employees;

193. How to Retrieve Record Where Salary is Between 1000 to 2000?

Explanation:

To retrieve records where the salary is within a specified range, use the BETWEEN operator.

Example:
SELECT *
FROM Employees
WHERE Salary BETWEEN 1000 AND 2000;
Syntax:
SELECT *
FROM Employees
WHERE Salary BETWEEN 1000 AND 2000;
194. Select All Records Where DeptNo of Both Emp and Dept Table Matches

Explanation:

To select records where the department number matches between two tables, use a JOIN operation.

Example:
SELECT [Link], [Link], [Link]
FROM Employees Emp
JOIN Department Dept ON [Link] = [Link];
Syntax:
SELECT [Link], [Link], [Link]
FROM Employees Emp
JOIN Department Dept ON [Link] = [Link];

195. If There Are Two Tables Emp1 and Emp2, and Both Have Common Record. How Can I
Fetch All the Records but Common Records Only Once?

Explanation:

To fetch all records from two tables but only one instance of the common records, use a UNION operation, as UNION
automatically eliminates duplicates.

Example:
SELECT *
FROM Emp1
UNION
SELECT *
FROM Emp2;
Syntax:
SELECT *
FROM Emp1
UNION
SELECT *
FROM Emp2;

This completes queries from 183 to 195. Would you like to continue with the rest, or is there anything else you'd like
to dive deeper into?

Let's continue with the next set of queries.

196. How to Fetch Only Common Records from Two Tables Emp and Emp1?

Explanation:

To fetch only the common records between two tables, use the INTERSECT operator. It returns only the rows that
appear in both tables.

Example:
SELECT *
FROM Emp
INTERSECT
SELECT *
FROM Emp1;
Syntax:
SELECT *
FROM Emp
INTERSECT
SELECT *
FROM Emp1;

197. How Can I Retrieve All Records of Emp1 Those Should Not Be Present in Emp2?

Explanation:

To retrieve records from Emp1 that are not in Emp2, use the NOT EXISTS or LEFT JOIN with a NULL check.

Example:
SELECT *
FROM Emp1
WHERE NOT EXISTS (
SELECT 1
FROM Emp2
WHERE [Link] = [Link]
);
Syntax:
SELECT *
FROM Emp1
WHERE NOT EXISTS (
SELECT 1
FROM Emp2
WHERE [Link] = [Link]
);

198. Count the Totals DeptNo Wise Where More Than 2 Employees Exist

Explanation:

To count the total number of employees in each department where the number of employees is greater than 2, use
GROUP BY and HAVING.

Example:
SELECT DeptID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DeptID
HAVING COUNT(*) > 2;
Syntax:
SELECT DeptID, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DeptID
HAVING COUNT(*) > 2;

199. Display the Names of Employees Who Are Working in the Company for the Past 5 Years

Explanation:

To display employees who have been with the company for more than 5 years, use a date comparison with
CURRENT_DATE and the employee's hire date.

Example:
SELECT Ename
FROM Employees
WHERE HireDate <= DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);
Syntax:
SELECT Ename
FROM Employees
WHERE HireDate <= DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);

200. Display the List of Employees Who Have Joined the Company Before 30th June 90 or After
31st Dec 90

Explanation:

To fetch employees who joined before a certain date or after another, use the OR condition with DATE.

Example:
SELECT Ename
FROM Employees
WHERE HireDate < '1990-06-30' OR HireDate > '1990-12-31';
Syntax:
SELECT Ename
FROM Employees
WHERE HireDate < '1990-06-30' OR HireDate > '1990-12-31';

201. Display the Names of Employees Working in Department Number 10 or 20 or 40 or

Employees Working as Clerks, Salesman or Analysts

Explanation:

To fetch employees working in specific departments or having specific job titles, use the IN operator for both
conditions.

Example:
SELECT Ename
FROM Employees
WHERE DeptID IN (10, 20, 40)
OR Job IN ('CLERK', 'SALESMAN', 'ANALYST');
Syntax:
SELECT Ename
FROM Employees
WHERE DeptID IN (10, 20, 40)
OR Job IN ('CLERK', 'SALESMAN', 'ANALYST');

202. Display the Names of Employees Whose Name Starts with Alphabet 'S'

Explanation:

To fetch employees whose name starts with the letter 'S', use the LIKE operator with a wildcard %.

Example:
SELECT Ename
FROM Employees
WHERE Ename LIKE 'S%';
Syntax:
SELECT Ename
FROM Employees
WHERE Ename LIKE 'S%';

203. Display Employee Names for Employees Whose Name Ends with Alphabet
Explanation:

To fetch employees whose name ends with a specific letter, use the LIKE operator with the % wildcard at the
beginning.

Example:
SELECT Ename
FROM Employees
WHERE Ename LIKE '%R';
Syntax:
SELECT Ename
FROM Employees
WHERE Ename LIKE '%R';

204. Display Employee Names for Employees Whose Name Ends with Alphabet 'R'

Explanation:

Similar to query 202, but explicitly asking for names ending with the letter 'R'.

Example:
SELECT Ename
FROM Employees
WHERE Ename LIKE '%R';
Syntax:
SELECT Ename
FROM Employees
WHERE Ename LIKE '%R';

This completes the rest of the queries from 196 to 204. Let me know if you'd like more details or have any other
questions!

Great! Let's continue with the explanation of additional SQL queries.

205. Select Employees Who Are Not Managers

Explanation:

To select employees who are not managers, you can check the Mgr column (or similar) where the manager ID is NULL
or does not match the employee’s EmpID.

Example:
SELECT Ename
FROM Employees
WHERE Mgr IS NULL;
Syntax:
SELECT Ename
FROM Employees
WHERE Mgr IS NULL;

206. Find the Employee Who Has the Highest Salary

Explanation:

To find the employee with the highest salary, you can use the MAX function or a subquery to fetch the maximum
salary and then filter by that value.

Example:
SELECT Ename
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);
Syntax:
SELECT Ename
FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

207. Find All Employees with Salary Greater Than Average Salary

Explanation:

To find employees whose salary is greater than the average salary, you can use the AVG function.

Example:
SELECT Ename, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
Syntax:
SELECT Ename, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

208. Find Departments with More Than 3 Employees

Explanation:

To find departments with more than 3 employees, use GROUP BY and HAVING.

Example:
SELECT DeptID
FROM Employees
GROUP BY DeptID
HAVING COUNT(EmpID) > 3;
Syntax:
SELECT DeptID
FROM Employees
GROUP BY DeptID
HAVING COUNT(EmpID) > 3;

209. List the Employees Who Have No Manager

Explanation:

To select employees who do not have a manager, check for Mgr column being NULL.

Example:
SELECT Ename
FROM Employees
WHERE Mgr IS NULL;
Syntax:
SELECT Ename
FROM Employees
WHERE Mgr IS NULL;

210. Select Employee Name and Department for Employees Earning More Than a Given Amount

Explanation:

To fetch the employee name and department for employees whose salary exceeds a certain value, you can use a
WHERE clause.

Example:
SELECT Ename, DeptID
FROM Employees
WHERE Salary > 5000;
Syntax:
SELECT Ename, DeptID
FROM Employees
WHERE Salary > 5000;

211. Find the Second Highest Salary

Explanation:

To find the second-highest salary, use a subquery or LIMIT (in some DBMS) along with ORDER BY and OFFSET.

Example:
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
Syntax:
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

212. Fetch Employees Who Were Hired in the Last 5 Years

Explanation:

To select employees hired in the last 5 years, use the DATE functions to subtract from the current date.

Example:
SELECT Ename, HireDate
FROM Employees
WHERE HireDate >= DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);
Syntax:
SELECT Ename, HireDate
FROM Employees
WHERE HireDate >= DATE_SUB(CURRENT_DATE, INTERVAL 5 YEAR);

213. Find All Employees Who Are Older Than a Specific Age

Explanation:

To find employees older than a certain age, calculate the difference between the DOB and current date using DATEDIFF
or similar functions.
Example:
SELECT Ename, DOB
FROM Employees
WHERE DATEDIFF(CURRENT_DATE, DOB) / 365 > 30;
Syntax:
SELECT Ename, DOB
FROM Employees
WHERE DATEDIFF(CURRENT_DATE, DOB) / 365 > 30;

214. List Employees Who Have a Salary Between Two Values

Explanation:

To find employees whose salary falls within a range, use the BETWEEN operator.

Example:
SELECT Ename, Salary
FROM Employees
WHERE Salary BETWEEN 3000 AND 5000;
Syntax:
SELECT Ename, Salary
FROM Employees
WHERE Salary BETWEEN 3000 AND 5000;

215. Find All Employees Who Work in More Than One Department

Explanation:

To find employees who work in more than one department, use a GROUP BY with HAVING COUNT(DISTINCT DeptID)
> 1.

Example:
SELECT EmpID, COUNT(DISTINCT DeptID) AS DeptCount
FROM Employees
GROUP BY EmpID
HAVING COUNT(DISTINCT DeptID) > 1;
Syntax:
SELECT EmpID, COUNT(DISTINCT DeptID) AS DeptCount
FROM Employees
GROUP BY EmpID
HAVING COUNT(DISTINCT DeptID) > 1;

These queries are commonly used in database management and data analysis tasks. They provide useful ways to
filter, aggregate, and group data to extract valuable insights.

Let me know if you'd like more explanations or further examples!