1. What is SQL?

SQL (Structured Query Language) is a language used to interact with relational databases to manage and manipulate data.

2. What are the types of SQL commands?

SQL commands are categorized as:

• DDL (Data Definition Language) - CREATE, ALTER, DROP
• DML (Data Manipulation Language) - SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language) - GRANT, REVOKE
• TCL (Transaction Control Language) - COMMIT, ROLLBACK, SAVEPOINT

3. What is a primary key?

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

4. What are the types of SQL joins?

SQL joins are:

• INNER JOIN - Returns matching records from both tables.
• LEFT JOIN - Returns all records from the left table and matching ones from the right.
• RIGHT JOIN - Returns all records from the right table and matching ones from the left.
• FULL JOIN - Returns all records when there is a match in either table.

5. Write a SQL query to find the second highest salary from the Employee table.

SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee);

6. What are aggregate functions in SQL?

Aggregate functions perform calculations on a set of values. Examples include COUNT(), SUM(), AVG(), MIN(), MAX().

7. What is an index in SQL?

An index is used to improve the speed of data retrieval operations on a database table.

8. What is a stored procedure?

A stored procedure is a set of SQL statements that can be executed as a single unit. Example:

9. What is a trigger in SQL?

A trigger is a procedural code that automatically executes when an event occurs in a database table.

10. What is normalization?

Normalization is the process of organizing data to reduce redundancy and improve integrity.

11. What are ACID properties in SQL?

ACID properties ensure reliable transactions:

• Atomicity - Ensures transactions are all-or-nothing.
• Consistency - Ensures data remains valid.
• Isolation - Ensures transactions do not interfere.
• Durability - Ensures committed data is permanent.

12. What is the difference between DELETE and TRUNCATE?

DELETE removes specific rows and can be rolled back. TRUNCATE removes all rows and cannot be rolled back.

13. What is a deadlock in SQL?

A deadlock occurs when two transactions hold resources the other needs, causing a block.

14. What is a cursor in SQL?

A cursor is used to iterate over query results one row at a time.

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

UNION removes duplicates, while UNION ALL includes duplicates.

16. What is a self-join?

A self-join is a join where a table is joined with itself.

17. What is a cross join?

A cross join returns the Cartesian product of both tables.

18. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping, HAVING filters groups after aggregation.

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

A clustered index sorts and stores data rows in the table, while a non-clustered index stores a separate structure with pointers.

20. What is a foreign key?

A foreign key is a column that creates a relationship between two tables.

21. What is a composite key?

A composite key consists of two or more columns that uniquely identify a record.

22. What is the difference between IN and EXISTS?

IN checks for values within a list, while EXISTS checks for the existence of rows in a subquery.

23. What is a materialized view?

A materialized view stores query results physically and can be refreshed.

24. What is a temporary table?

A temporary table is a short-lived table that exists for the duration of a session or transaction.

25. What is an SQL injection?

SQL injection is a security attack that manipulates SQL queries by inserting malicious code.

26. How do you prevent SQL injection?

Use prepared statements, parameterized queries, and input validation.

27. What is a correlated subquery?

A correlated subquery depends on an outer query and executes row by row.

28. What is the difference between CHAR and VARCHAR?

CHAR has a fixed length, while VARCHAR has a variable length.

29. What is a common table expression (CTE)?

A CTE is a temporary named result set defined using WITH.

30. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

ROW_NUMBER() assigns unique numbers, RANK() skips ranks for duplicates, DENSE_RANK() does not skip ranks.

31. What is the difference between DELETE and TRUNCATE?

DELETE removes specific rows and can be rolled back. TRUNCATE removes all rows and cannot be rolled back.

32. What is an alias in SQL?

An alias is a temporary name for a table or column using AS.

33. What is a transaction in SQL?

A transaction is a sequence of operations performed as a single logical unit of work.

34. What is a savepoint in SQL?

A savepoint is a point within a transaction to which you can roll back.

35. What is the difference between UNION and JOIN?

UNION combines results of two queries, while JOIN combines rows from multiple tables.

36. What is a subquery?

A subquery is a query inside another query.

37. What is the purpose of GROUP BY?

GROUP BY groups rows with the same values into summary rows.

38. What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN returns matching rows, OUTER JOIN includes unmatched rows.

39. What are SQL constraints?

Constraints enforce rules on data. Examples: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK.

40. What is indexing in SQL?

Indexing speeds up query processing by creating a data structure for quick lookup.

41. What is a surrogate key?

A surrogate key is a system-generated unique identifier, usually an integer.

42. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts all rows, while COUNT(column_name) counts non-null values.

43. What is denormalization?

Denormalization optimizes query performance by adding redundant data.

44. What are window functions in SQL?

Window functions perform calculations across a set of rows related to the current row.

45. What is the difference between DISTINCT and GROUP BY?

DISTINCT removes duplicates, while GROUP BY aggregates data.

46. What is the COALESCE function in SQL?

COALESCE returns the first non-null value in a list.

47. What is the CASE statement in SQL?

CASE is used for conditional expressions in SQL.

48. What is an AUTO_INCREMENT column?

AUTO_INCREMENT generates unique numbers for each row.

49. What is the difference between NVL() and ISNULL()?

NVL() is used in Oracle, ISNULL() in SQL Server, both replace NULL values.

50. What is the difference between logical and physical data independence?

Logical data independence shields schema changes, physical data independence abstracts storage changes.