Scribd
Upload
8 views6 pages

SQL Interview Notes: Key Concepts Explained

Uploaded by

ANTONY NAVIN RAJ I
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
8 views6 pages

SQL Interview Notes: Key Concepts Explained

Uploaded by

ANTONY NAVIN RAJ I
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

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 [Link], [Link]

FROM Students s

INNER JOIN Enrollments e ON [Link] = [Link];

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;

Common questions

Powered by AI

Normalization is a process that restructures a database to reduce redundancy and dependency by organizing fields and table relationships . It involves organizing data according to constraints like atomicity (1NF), removing partial dependences (2NF), and removing transitive dependencies (3NF), ensuring each piece of data is stored only once and dependencies are logically sound . This improves data integrity and optimizes storage efficiency, although it may increase the complexity of database queries .

Foreign keys play a crucial role in maintaining data integrity across tables by establishing a link between related tables . They ensure that relationships between tables are valid and enforce referential integrity by restricting actions that would destroy these links, such as preventing the deletion of a referenced record in a parent table . This helps maintain consistent and accurate data across related tables in a database system .

JOIN operations enhance querying capabilities by allowing retrieval of related data from multiple tables . INNER JOIN returns rows with matching values from both tables, useful for finding intersecting data . LEFT JOIN returns all rows from the left table and matched rows from the right, returning null for unmatched rows . RIGHT JOIN is similar but returns all rows from the right table . FULL JOIN retrieves all rows when there is a match in either table, useful for comprehensive datasets .

DROP command completely removes a table, including its data and structure, from the database . TRUNCATE deletes all rows in a table while keeping its structure intact, which allows for quick deletion and resetting of the data . DELETE removes specific rows from a table based on a specified condition and retains both the table structure and the rest of its data .

ACID compliance ensures reliability in database transactions by enforcing four properties: Atomicity ensures that a transaction is all or nothing, meaning it either fully completes or does not occur at all . Consistency maintains a valid state across the database before and after a transaction . Isolation guarantees that concurrent transactions do not affect each other's execution, maintaining transaction order . Durability assures that once a transaction is committed, its changes are permanent, even in case of system failure .

Views are preferable in scenarios where complex database queries need to be executed multiple times or by different users because they simplify query management and improve security by exposing only relevant data . They provide an abstraction layer that can protect underlying table structures while enhancing data consistency and simplifying access to regularly used data .

Subqueries are distinct from JOINs in that they are nested queries used to provide input data for the outer query, often allowing more readable and easily maintained queries . Subqueries are advantageous when querying aggregated data or performing checks before executing the main query, making them useful for filtering using conditions that are dependent on aggregated data from another table . They can simplify complex queries where multiple filtering conditions or derived tables are involved .

UNION is preferred over UNION ALL when the goal is to produce a result set with only unique records, removing duplicates from the combined outputs . This operation can be more performance-intensive due to the need to eliminate duplicates, which may involve additional sorting and comparison . However, it ensures precision in scenarios where duplication of results is undesirable . Conversely, UNION ALL is more performant as it does not require duplicate removal, making it suitable when duplicate records are permissible or beneficial .

The WHERE clause in SQL is used to filter records before any groupings are made, primarily dealing with raw data before aggregation . It is applicable for conditions on individual rows without aggregation functions . On the other hand, the HAVING clause is used to filter data after aggregation, applicable only when GROUP BY is used, and operates on aggregate results, such as filtering out groups after counting or summing values .

Indexes in SQL databases are critical for optimizing query performance by providing efficient data retrieval, reducing the time taken for search operations . Clustered indexes modify the data storage order based on the indexed column, improving search speed for ordered queries . Non-clustered indexes do not alter the data storage order but offer pointers to relevant rows, speeding up query execution . However, indexes require additional storage space and can increase maintenance overhead during data modifications such as inserts, updates, and deletes .

You might also like