86. What is a Primary Key in SQL?
Answer:
A Primary Key is a constraint that uniquely identifies each record in a table. Features:
- Must contain unique and non-null values.
- Can be a single column or a composite key (multiple columns).
- Only one primary key is allowed per table.
Example:
sql
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100)
);
87. What is a Unique Key in SQL?
Answer:
A Unique Key ensures that all values in a column or set of columns are unique across rows. Unlike primary keys, it can accept a single NULL value.
Example:
sql
CREATE TABLE Users (
Email VARCHAR(100) UNIQUE,
Phone VARCHAR(15)
);
88. Difference Between Primary Key and Unique Key?
| Feature | Primary Key | Unique Key |
| Uniqueness | Yes | Yes |
| NULLs Allowed | No | Yes (only one) |
| Number per Table | One | Multiple allowed |
89. What is a Foreign Key in SQL?
Answer:
A Foreign Key is a constraint that establishes a relationship between two tables. It links a column in one table to the Primary Key in another.
Example:
sql
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
This enforces referential integrity by ensuring that CustomerID exists in the Customers table.
90. What are Entities and Relationships in SQL?
Answer:
- Entities: Things or objects that can be identified and stored in a database (e.g., Student, Product).
- Relationships: Logical connections between entities (e.g., A student enrolls in a course).
These are foundational concepts in ER Modeling for relational databases.
91. What is ACID Property in Databases?
Answer: ACID stands for:
- Atomicity: All operations in a transaction are completed or none are.
- Consistency: Database remains in a valid state before and after transactions.
- Isolation: Transactions execute independently without interference.
- Durability: Committed transactions persist even after crashes.
These properties ensure reliable, safe transactions in SQL databases.
92. What is the STUFF() Function in SQL?
Answer:
The STUFF() function inserts a string into another string, deleting a specified length of characters first.
Syntax:
sql
STUFF(original_string, start, length_to_delete, insert_string)
Example:
sql
SELECT STUFF(‘abcdef’, 2, 3, ‘XYZ’); — Output: aXYZef
93. What is a Stored Procedure? Give an Example.
Answer:
A stored procedure encapsulates SQL logic that can be executed repeatedly with parameters.
Example:
sql
CREATE PROCEDURE UpdateSalary
@EmpID INT, @NewSalary DECIMAL(10,2)
AS
BEGIN
UPDATE Employees SET Salary = @NewSalary WHERE EmployeeID = @EmpID;
END;
Execution:
sql
EXEC UpdateSalary 1001, 75000.00;
94. Query to Return Fraction of Retained Users After Joining (Day-Wise Activity)
Answer:
To compute the retention rate:
sql
SELECT
DayOffset,
COUNT(DISTINCT UserID) * 1.0 / (SELECT COUNT(*) FROM Users) AS RetentionRate
FROM (
SELECT UserID, DATEDIFF(DAY, JoinDate, ActivityDate) AS DayOffset
FROM UserActivity
WHERE DATEDIFF(DAY, JoinDate, ActivityDate) >= 0
) AS DailyActivity
GROUP BY DayOffset;
95. Write a SQL Query to Return Cancellation Rate (LeetCode Style)
Answer:
sql
SELECT
ROUND(SUM(CASE WHEN t.status LIKE ‘cancelled%’ THEN 1 ELSE 0 END) * 1.0 / COUNT(*), 2) AS CancellationRate
FROM Trips t
JOIN Users r ON t.rider_id = r.user_id
JOIN Users d ON t.driver_id = d.user_id
WHERE t.request_date BETWEEN ‘2020-10-01’ AND ‘2020-10-02’
AND r.banned = ‘no’
AND d.banned = ‘no’;
96. Difference Between OLTP and OLAP Systems
| Feature | OLTP | OLAP |
| Full Form | Online Transaction Processing | Online Analytical Processing |
| Purpose | Day-to-day transactions | Data analysis and reporting |
| Data Type | Real-time transactional data | Historical, aggregated data |
| Query Type | Simple and fast | Complex with aggregations |
| Example | Banking systems | Business intelligence dashboards |
97. What is a Self Join in SQL?
Answer:
A Self Join joins a table with itself using an alias.
Example:
sql
SELECT A.EmployeeID, A.Name, B.ManagerID
FROM Employees A
JOIN Employees B ON A.EmployeeID = B.ManagerID;
Used for hierarchy traversal, such as employees reporting to managers.
98. Difference Between UNION and UNION ALL
| Operator | Removes Duplicates | Performance | Use Case |
| UNION | Yes | Slower | When unique rows are required |
| UNION ALL | No | Faster | When duplicates are acceptable |
99. What is a Cursor in SQL? How to Use It?
Answer: A Cursor is used to iterate over rows returned by a query.
Steps to use Cursor:
- DECLARE cursor with SELECT statement.
- OPEN cursor.
- FETCH data row-by-row.
- CLOSE cursor.
- DEALLOCATE cursor.
Example:
sql
DECLARE salary_cursor CURSOR FOR
SELECT EmployeeID FROM Employees;
OPEN salary_cursor;
FETCH NEXT FROM salary_cursor;
— Process each row…
CLOSE salary_cursor;
DEALLOCATE salary_cursor;
100. What is the INTERSECT Operator in SQL?
Answer:
The INTERSECT operator returns common rows from two SELECT queries.
Example:
sql
SELECT Name FROM Students
INTERSECT
SELECT Name FROM TopPerformers;
Only students who exist in both tables will be shown. Duplicate rows are automatically removed.
