Complete SQL Interview Notes
1. Primary Key
A primary key uniquely identifies each record in a table. It cannot contain NULL values and must be unique.
Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT
);
2. Foreign Key
A foreign key establishes a link between two tables using a column that refers to the primary key in another
table.
Example:
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseName VARCHAR(100),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);
3. WHERE Clause
Filters rows before grouping or aggregation.
Example:
SELECT * FROM Students WHERE Age > 18;
4. HAVING Clause
Used with GROUP BY to filter aggregated results.
Example:
SELECT Age, COUNT(*) AS Total
FROM Students
GROUP BY Age
HAVING COUNT(*) > 2;
5. Joins
INNER JOIN: Returns matching rows in both tables.
LEFT JOIN: All rows from the left table and matched rows from the right.
RIGHT JOIN: All rows from the right table and matched rows from the left.
FULL JOIN: All rows when there is a match in one of the tables.
Example INNER JOIN:
SELECT s.Name, e.CourseName
FROM Students s
INNER JOIN Enrollments e ON s.StudentID = e.StudentID;
6. DROP vs TRUNCATE vs DELETE
DROP: Deletes entire table.
TRUNCATE: Deletes all data but keeps structure.
DELETE: Deletes specific rows.
DROP TABLE Enrollments;
TRUNCATE TABLE Students;
DELETE FROM Students WHERE Age < 18;
7. Normalization
Process of organizing data to reduce redundancy.
1NF: Atomic values.
2NF: Remove partial dependency.
3NF: Remove transitive dependency.
Normalized Example:
Orders(OrderID, Customer)
OrderDetails(OrderID, ProductName)
8. Subquery
A query inside another query.
Example:
SELECT Name FROM Students
WHERE StudentID IN (
SELECT StudentID FROM Enrollments WHERE CourseName = 'SQL'
);
9. UNION vs UNION ALL
UNION: Removes duplicates.
UNION ALL: Includes duplicates.
Example:
SELECT Name FROM Students
UNION
SELECT CourseName FROM Enrollments;
10. Indexes
Indexes speed up SELECT queries.
Clustered: Affects row order.
Non-Clustered: Does not affect row order.
Example:
CREATE INDEX idx_name ON Students(Name);
11. ACID Properties
Atomicity: All or nothing.
Consistency: Valid state maintained.
Isolation: Concurrent transactions.
Durability: Permanent changes.
Example:
BEGIN TRANSACTION;
UPDATE Students SET Age = 21 WHERE StudentID = 5;
COMMIT;
12. GROUP BY
Groups rows and used with aggregate functions.
Example:
SELECT Age, COUNT(*) FROM Students GROUP BY Age;
13. View
A virtual table based on a SELECT statement.
Example:
CREATE VIEW YoungStudents AS
SELECT Name, Age FROM Students WHERE Age < 21;
14. Stored Procedure
A stored group of SQL statements.
Example:
CREATE PROCEDURE GetCourses @StudentID INT AS BEGIN
SELECT CourseName FROM Enrollments WHERE StudentID = @StudentID;
END;
EXEC GetCourses 1;
15. Trigger
Automatically fires when an event occurs.
Example:
CREATE TRIGGER trg_after_insert ON Students AFTER INSERT AS BEGIN
PRINT 'New student added.' END;
16. Cursor
Processes result set row-by-row.
Example:
DECLARE student_cursor CURSOR FOR SELECT Name FROM Students;
...
17. Transaction
Groups multiple SQL statements into a single unit.
Example:
BEGIN TRANSACTION;
UPDATE Students SET Age = Age + 1 WHERE StudentID = 2;
COMMIT;
