50 SQL Interview Questions & Answers

1. What is DBMS?

A Database Management System (DBMS) is a program that controls creation, maintenance and use of a database. DBMS manages data rather than saving it in file systems.

2. What is RDBMS?

RDBMS stands for Relational Database Management System. It stores data in tables related by common fields. Example: SQL Server.

3. What is SQL?

SQL (Structured Query Language) is used to communicate with a database to retrieve, insert, update, and delete data. Standard SQL commands include SELECT, INSERT, UPDATE, DELETE.

4. What is a Database?

A database is an organized form of data for easy access, storing, retrieval, and management. Example: School Management Database.

5. What are Tables and Fields?

A table organizes data in columns (fields) and rows (records). Example Table: Employee. Fields: Emp ID, Emp Name, Date of Birth. Data: 201456, David, 11/15/1960.

6. What is a Primary Key?

A primary key uniquely identifies a row in a table and cannot be NULL.

7. What is a Unique Key?

A unique key constraint uniquely identifies each record in a table. Many unique keys can exist per table but only one primary key.

8. What is a Foreign Key?

A foreign key in one table refers to the primary key of another table, establishing a relationship between them.

9. What is a Join?

A JOIN keyword retrieves data from multiple tables based on the relationship between their fields.

10. Types of Join

Inner Join, Left Join, Right Join, Full Join. Each retrieves rows based on relationships between tables.

11. What is Normalization?

Normalization organizes fields and tables to minimize redundancy and dependency. It allows easy modification in a single table.

12. What is Denormalization?

Denormalization introduces redundancy to improve query performance by combining data from related tables.

13. Different Normal Forms

1NF, 2NF, 3NF, 4NF, 5NF, 6NF — rules to reduce redundancy and organize tables.

14. What is a View?

A view is a virtual table representing a subset of data from one or more tables.

15. What is an Index?

An index improves data retrieval speed by creating entries for each value in a table.

16. Types of Indexes

Unique, Clustered, Non-Clustered indexes.

17. What is a Cursor?

A cursor allows traversal over rows in a table, useful for retrieval, addition, and deletion of records.

18. What is a Relationship?

Relationships connect tables: One-to-One, One-to-Many, Many-to-One, Self-Referencing.

19. What is a Query?

A query is a code to retrieve information from a database.

20. What is a Subquery?

A subquery is a query inside another query. It runs first, and its result is used by the main query.

21. Types of Subquery

Correlated and Non-Correlated subqueries. Correlated depends on the outer query; Non-Correlated can run independently.

22. What is a Stored Procedure?

A stored procedure is a collection of SQL statements executed together whenever needed.

23. What is a Trigger?

A trigger automatically executes in response to events like INSERT, UPDATE, DELETE to maintain database integrity.

24. Difference between DELETE and TRUNCATE

DELETE removes specific rows with optional WHERE; can rollback. TRUNCATE removes all rows and cannot rollback.

25. Local and Global Variables

Local variables exist within a function; global variables exist throughout the program.

26. What is a Constraint?

Constraints specify limits on table data types. Examples: NOT NULL, CHECK, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY.

27. What is Data Integrity?

Data integrity ensures accuracy and consistency of data. Integrity constraints enforce business rules.

28. What is Auto Increment?

Auto Increment generates unique numbers automatically when inserting new records. In Oracle: AUTO INCREMENT, SQL Server: IDENTITY.

29. Difference between Cluster and Non-Cluster Index

Clustered index sorts physical table rows; one per table. Non-clustered index maintains logical order; multiple allowed per table.

30. What is Datawarehouse?

Datawarehouse consolidates data from multiple sources for analysis and mining. Data marts are subsets of warehouse data.

31. What is Self-Join?

Self-join compares a table to itself using aliases to distinguish multiple instances.

32. What is Cross-Join?

Cross-join produces the Cartesian product of two tables. Rows = table1 rows × table2 rows. WHERE can filter results like INNER JOIN.

33. What are User Defined Functions?

Functions written for reuse to avoid repeating logic. Can return single value or table.

34. Types of User Defined Functions

Scalar Functions, Inline Table-Valued Functions, Multi-Statement Table-Valued Functions. Scalar returns single value; others return tables.

35. What is Collation?

Collation defines rules for sorting and comparing character data (case, accent, kana, width sensitivity).

36. Types of Collation Sensitivity

Case Sensitivity (A ≠ a), Accent Sensitivity, Kana Sensitivity, Width Sensitivity (single vs double byte characters).

37. Advantages & Disadvantages of Stored Procedure

Advantages: modular, reusable, faster execution, reduces network traffic, secure. Disadvantages: uses server memory, limited to database.

38. What is Online Transaction Processing (OLTP)?

OLTP manages transaction-based applications for data entry, retrieval, and processing. Example: Bank daily transactions.

39. What is a Clause?

SQL clause limits result sets. Examples: WHERE, HAVING.

40. What is Recursive Stored Procedure?

A stored procedure that calls itself until a boundary condition is met.

41. Union, Minus, Intersect Commands

UNION: combines results, removes duplicates. MINUS: rows from first query not in second. INTERSECT: rows common to both queries.

42. What is ALIAS?

Alias gives a temporary name to table or column, usable in queries. Example: SELECT st.StudentID, Ex.Result FROM student st, Exam Ex WHERE st.StudentID = Ex.StudentID;

43. Difference between TRUNCATE and DROP

TRUNCATE removes all rows; cannot rollback. DROP removes table entirely; cannot rollback.

44. Aggregate & Scalar Functions

Aggregate functions: perform calculations on columns (MAX(), COUNT()). Scalar functions: return single value (UCASE(), NOW()).

45. Create Empty Table from Existing Table

Example: SELECT * INTO studentcopy FROM student WHERE 1=2; copies structure only.

46. Fetch Common Records from Two Tables

Example: SELECT StudentID FROM student INTERSECT SELECT StudentID FROM Exam;

47. Fetch Alternate Records

Even rows: SELECT studentID FROM (SELECT ROW_NUMBER() OVER(ORDER BY StudentID) AS rowno, StudentID FROM student) WHERE MOD(rowno,2)=0;

Odd rows: same with MOD(rowno,2)=1;

48. Select Unique Records

Use DISTINCT: SELECT DISTINCT StudentID, StudentName FROM Student;

49. Fetch First 5 Characters

SELECT SUBSTRING(StudentName,1,5) AS StudentName; or SELECT LEFT(StudentName,5) AS StudentName;

50. Operator for Pattern Matching

LIKE operator. % = zero or more chars, _ = exactly one char. Example: SELECT * FROM Student WHERE StudentName LIKE 'a%';

Conclusion

This guide of 50 SQL Interview Questions & Answers covers essential concepts for freshers and experienced professionals. Use it to prepare thoroughly and boost your confidence for SQL interviews.

📄 Download PDF