🔹 56. Find Employees Earning More Than Their Managers
Question:
Write a SQL query to list employees who earn more than their managers.
Answer:
sql
SELECT e1.Name AS Employee
FROM Employee e1
JOIN Employee e2 ON e1.ManagerId = e2.Id
WHERE e1.Salary > e2.Salary;
🔹 57. Write SQL Query to Display Max Salary by Department
Question:
How to get the maximum salary for each department?
Answer:
sql
SELECT DeptId, MAX(Salary) AS MaxSalary
FROM Employee
GROUP BY DeptId;
If department names are in a separate Department table:
sql
SELECT d.DeptName, MAX(e.Salary) AS MaxSalary
FROM Employee e
RIGHT JOIN Department d ON e.DeptId = d.DeptId
GROUP BY d.DeptName;
🔹 58. SQL Query to Display Odd ID Movies and Filter Descriptions
Question:
Find movies with odd IDs and descriptions not equal to ‘boring’. Order by rating.
Answer:
sql
SELECT *
FROM Cinema
WHERE MOD(id, 2) = 1 AND description != ‘boring’
ORDER BY rating DESC;
🔹 59. SQL Query to Find Nth Highest Salary
Question:
Write a query to get the nth highest salary. Assume n = 2.
Answer:
sql
SELECT DISTINCT Salary
FROM Employee e1
WHERE 2 = (
SELECT COUNT(DISTINCT Salary)
FROM Employee e2
WHERE e2.Salary >= e1.Salary
);
To handle scenarios where nth salary doesn’t exist:
sql
SELECT
Salary
FROM (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employee
) AS Ranked
WHERE Rank = 2;
🔹 60. What is an Identity Column in SQL?
Question:
Explain what an identity column is in SQL.
Answer:
An Identity Column automatically generates incremental numeric values when a new row is inserted. It is typically used for primary keys.
Example in SQL Server:
sql
CREATE TABLE Employee (
ID INT IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(100)
);
- IDENTITY(1,1) → Starts at 1 and increments by 1.
- Often used in surrogate keys.
- In MySQL, the equivalent is AUTO_INCREMENT.
61. What are indexes in SQL? Why are they important?
Answer: Indexes in SQL are special lookup tables that the database search engine can use to speed up data retrieval. An index is created on columns that are often used in WHERE clauses, JOIN conditions, or ORDER BY operations.
- How it works: Think of an index as similar to an index in a book – it helps the database find data without scanning the whole table.
- Types of Indexes:
- Clustered Index: Sorts and stores the data rows in the table based on key values.
- Non-Clustered Index: Contains pointers to the actual data rows.
- Clustered Index: Sorts and stores the data rows in the table based on key values.
Pros: Fast query performance
Cons: Slows down INSERT, UPDATE, DELETE due to index maintenance and consumes extra space.
62. What is a Composite Primary Key in SQL?
Answer: A Composite Primary Key is a primary key made up of two or more columns used to uniquely identify a row in a table.
Example:
sql
CREATE TABLE Enrollments (
StudentID INT,
CourseID INT,
EnrollDate DATE,
PRIMARY KEY (StudentID, CourseID)
);
- This means a student can enroll in multiple courses, but cannot enroll in the same course twice.
- Useful in many-to-many relationships.
Composite keys ensure data uniqueness across a combination of columns rather than a single column.
63. What are User Defined Data Types (UDT) and when should you use them?
Answer: User Defined Data Types (UDTs) allow developers to define their own data types based on existing SQL Server data types.
Why use them?
- Enforce consistency across multiple tables.
- Improve code readability and maintainability.
Example:
sql
sp_addtype FlightNumType, ‘VARCHAR(8)’, ‘NOT NULL’;
You can then use FlightNumType in table definitions instead of repeating VARCHAR(8) everywhere.
64. Explain different isolation levels in SQL.
Answer: Isolation levels determine how transaction integrity is maintained when multiple users access the database concurrently.
| Isolation Level | Description |
| Read Uncommitted | Allows dirty reads. |
| Read Committed | Default level. Prevents dirty reads. |
| Repeatable Read | Prevents dirty and non-repeatable reads. |
| Serializable | Highest isolation. Prevents all anomalies. |
| Snapshot (SQL Server) | Reads consistent snapshot using row versioning. |
Use:
sql
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
65. Explain Active/Active and Active/Passive cluster configurations.
Answer: SQL Server clustering ensures high availability.
- Active/Active: All nodes are running instances simultaneously and handle separate workloads. If one node fails, its workload is moved to another.
- Active/Passive: One node is active, the other is passive and acts as a standby. The passive node takes over only during failover.
Active/Passive is more stable; Active/Active provides better resource utilization.
66. What is lock escalation in SQL Server?
Answer: Lock escalation is the process of converting many fine-grained locks (like row or page locks) into a coarser lock (like table lock) to reduce overhead.
Why?
- Each lock uses memory.
- Thousands of locks can degrade performance.
- SQL Server dynamically decides when to escalate locks based on thresholds.
Lock escalation helps maintain a balance between concurrency and performance.
67. What is a Heap in SQL Server?
Answer: A heap is a table without a clustered index. Data is stored in no particular order, and retrieval requires a full table scan unless there are non-clustered indexes.
Use Cases:
- Useful for bulk inserts.
- Temporary or staging tables where speed is more important than lookup efficiency.
For fast inserts, drop indexes, load data, and recreate indexes.
68. What are SQL Server Scheduled Jobs or Tasks?
Answer: Scheduled Jobs are automated tasks created using SQL Server Agent.
Example tasks:
- Backup databases
- Rebuild indexes
- Update statistics
Jobs consist of:
- Steps (T-SQL commands)
- Schedules (time and frequency)
- Alerts (notifications on failure/success)
Benefits:
- Reduces manual effort
- Improves reliability of routine operations
69. How to get @@ERROR and @@ROWCOUNT together?
Answer: If you check them separately, one may reset before you access the other. To safely retrieve both values:
sql
DECLARE @Error INT, @RowCount INT;
SELECT @RowCount = @@ROWCOUNT, @Error = @@ERROR;
- @@ERROR: Last T-SQL statement’s error number
- @@ROWCOUNT: Number of rows affected
Use this approach immediately after a DML operation.
70. What is a CHECK Constraint in SQL?
Answer: A CHECK constraint limits the values that can be entered into a column, ensuring domain integrity.
Example:
sql
CREATE TABLE Employees (
Age INT CHECK (Age >= 18)
);
- You can also name the constraint and apply to multiple columns.
- You cannot insert or update rows that violate the condition.
CHECK constraints are enforced at the row level during INSERT or UPDATE.
