Here is a list of SQL Interview Questions and Answers based on the Expert Research-

1.What is the difference between DELETE and TRUNCATE statements?

DELETE: Removes specific rows and is a Data Manipulation Language (DML) command; rollback is possible.
TRUNCATE: Removes all rows in a table and is a Data Definition Language (DDL) command; rollback is not possible.

2. What are the subsets of SQL?

DDL (Data Definition Language): Defines database schema (e.g., CREATE, ALTER).
DML (Data Manipulation Language): Manipulates data in tables (e.g., INSERT, UPDATE).
DCL (Data Control Language): Manages permissions (e.g., GRANT, REVOKE).
TCL (Transaction Control Language): Manages transactions (e.g., COMMIT, ROLLBACK).

3. What is a Database Management System (DBMS) and its types?

A DBMS is software for interacting with databases, allowing data storage, modification, and retrieval. Types include:
- Hierarchical: Tree-like structure.
- Relational (RDBMS): Uses tables and relations.
- Network: Supports many-to-many relationships.
- Object-Oriented: Stores data as objects.

4. What is a table and a field in SQL?

Table: Collection of related data organized into rows and columns.
Field: Refers to columns within a table, each with a specific data type.

5. What are joins in SQL?

Joins are used to combine rows from two or more tables based on related columns. Types:
- INNER JOIN: Rows with matching values in both tables.
- LEFT JOIN: All rows from the left table and matching rows from the right.
- RIGHT JOIN: All rows from the right table and matching rows from the left.
- FULL JOIN: All rows from both tables, with matches when available.

6. What is the difference between CHAR and VARCHAR in SQL?

CHAR: Fixed-length strings; suitable for data with consistent length.
VARCHAR: Variable-length strings; allows flexibility in length up to a defined limit.

7. What is a primary key?

A primary key is a unique identifier for each row in a table. It must contain unique values and cannot contain NULL values.

8. What are constraints in SQL?

Constraints are rules applied to table columns, such as:
- NOT NULL: Ensures no NULL values.
- UNIQUE: Ensures all values in a column are unique.
- CHECK: Validates values based on a condition.
- DEFAULT: Sets a default value if no value is provided.
- INDEX: Speeds up data retrieval.

9. What is the difference between SQL and MySQL?

SQL: A standard language for managing databases.
MySQL: A relational database management system that uses SQL to access and manage data.

10. What is the difference between DELETE and TRUNCATE statements?

DELETE: Removes specific rows and allows rollback. It’s a Data Manipulation Language (DML) command.
TRUNCATE: Removes all rows from a table without rollback and is faster than DELETE. It’s a Data Definition Language (DDL) command.

11. What are the different subsets of SQL?

Data Definition Language (DDL): Commands for defining database schema.
Data Manipulation Language (DML): Commands for manipulating data in tables.
Data Control Language (DCL): Commands for permissions and access control.
Transaction Control Language (TCL): Commands for managing database transactions.

12. What is a Database Management System (DBMS), and what are its types?

A DBMS is software for interacting with users and applications to manage and analyze data. Types include hierarchical, relational, network, and object-oriented DBMS.

13. What is the difference between a table and a field in SQL?

Table: Organized collection of data in rows and columns.
Field: Refers to columns in a table.

14. What are JOINS in SQL?

JOINS combine rows from two or more tables based on a related column. Types include INNER JOIN, FULL JOIN, LEFT JOIN, and RIGHT JOIN.

15. Difference between CHAR and VARCHAR data types in SQL?

CHAR: Used for fixed-length strings.
VARCHAR: Used for variable-length strings.

16. What is a Primary Key?

A primary key is a unique identifier for each record in a table. It ensures each record can be uniquely identified.

17. What are constraints in SQL?

Constraints define rules for data types in a table, used to set limits on the data.

18. What is SQL and MySQL?

SQL (Structured Query Language) is the standard language for managing relational databases, while MySQL is an open-source relational database management system that uses SQL as its core.

19. What is a Unique Key?

A unique key identifies a single row in a table, allowing multiple unique values and permitting null values but not duplicates.

20. What is a Foreign Key?

A foreign key enforces referential integrity by linking data between tables, referencing a primary key from another (parent) table, thus preventing actions that would break the link.

21. What is Data Integrity?

Data integrity ensures the accuracy and consistency of data in a database and enforces business rules through integrity constraints.

22. Difference between Clustered and Non-Clustered Index?

Clustered indexes store data in a sorted order and are faster but limited to one per table, while non-clustered indexes create a separate data structure pointing back to the original data, allowing multiple non-clustered indexes per table.

23. SQL Query to Display Current Date

Use the GETDATE() function in SQL Server to display the current date and time. Example: SELECT GETDATE();

24. Types of Joins in SQL

Inner Join: Returns records with matching values in both tables.
Full Join: Returns records with matches in either the left or right table.
Left Join: Returns all records from the left table and matching records from the right table.
Right Join: Returns all records from the right table and matching records from the left table.

25. What is Denormalization?

Denormalization involves combining data from multiple tables to reduce complexity and improve database performance by adding redundant data.

27. Entities and Relationships

Entities represent objects or things with distinct attributes, while relationships define how these entities interact within a database.

28. What is an Entity in SQL?

Entities represent real-world objects like a person, place, or thing, about which data is stored in a database. A table holds data for one type of entity, and each attribute of an entity corresponds to a column.

29. What is a Relationship in SQL?

A relationship defines how different entities (tables) are connected, such as linking customer details in different tables to retrieve complete information.

30. What is an Index, and why is it used?

An index is a performance tuning method for faster data retrieval. It creates an entry for each value, enhancing query speed by reducing search time within a table.

31. What are the types of Indexes in SQL?

The three main types of indexes are:
- Unique Index: Prevents duplicate values in a column.
- Clustered Index: Alters the physical order of data based on key values; each table has one clustered index.
- Non-clustered Index: Maintains a logical order without changing physical order; tables can have multiple non-clustered indexes.

32. What is Normalization, and what are its advantages?

Normalization organizes data to reduce redundancy. Advantages include efficient data access, better organization, easier security implementation, reduced data duplication, and more consistent data.

33. Difference between DROP and TRUNCATE commands?

DROP: Removes a table entirely from the database and cannot be rolled back.
TRUNCATE: Deletes all rows in a table but does not remove the table structure; it also cannot be rolled back.

34. What are the types of Normalization forms?

Common forms include:
- 1NF: Ensures each table cell contains a single value and all entries are unique.
- 2NF, 3NF, and BCNF: Each adds constraints to further reduce redundancy and improve data structure.

35. What are normalization forms in SQL?

1NF: Ensures no repeating groups within rows.
2NF: Every non-key column is fully dependent on the whole primary key.
3NF: No transitive functional dependency; every non-key column depends only on the primary key.
BCNF: If multiple candidate keys exist, each must be a superkey to avoid redundancy.

36. Explain the ACID properties in databases.

Atomicity: Transactions are fully completed or fully rolled back.
Consistency: Ensures data integrity by maintaining all validation rules.
Isolation: Manages concurrent transactions to avoid conflicts.
Durability: Committed transactions are saved, even in case of system failures.

37. What is a trigger in SQL?

A trigger is a stored procedure executed automatically in response to specific events (INSERT, UPDATE, DELETE) on a table. Types include BEFORE and AFTER triggers for each action.

38. What are the different types of SQL operators?

Arithmetic: +, -, *, /.
Bitwise: &, |, ~.
Comparison: =, <, >, etc.
Compound: +=, -=, *=, etc.
Logical: AND, OR, NOT.

39. Are NULL values the same as zero or a blank space?

No, NULL represents missing or unknown data. Zero is a numeric value, and a blank space is a character.

40. What is the difference between a CROSS JOIN and a NATURAL JOIN?

CROSS JOIN: Produces the Cartesian product of two tables.
NATURAL JOIN: Joins tables based on columns with the same name and data type.

41. What is a subquery in SQL?

A subquery is a query nested within another query, often used to retrieve data needed for the main query. Types include correlated (references outer query) and non-correlated (independent).

42. How can you count the number of records in a table?

Common methods:
- SELECT COUNT(*) FROM table;
- Using system indexes.

43. Write a SQL query to find employee names that begin with ‘A’.

SELECT * FROM employees WHERE employee_name LIKE 'A%';

44.How do you find employee names that end with ‘a’?

Use a LIKE clause with a wildcard: SELECT * FROM table_name WHERE employee_name LIKE '%a';

45. Write a SQL query to get the third-highest salary.

Use a subquery to rank salaries: SELECT TOP 1 salary FROM (SELECT TOP 3 salary FROM employee ORDER BY salary DESC) AS temp ORDER BY salary ASC;

46. What is the purpose of group functions in SQL?

Group functions (like AVG, COUNT, MAX, MIN, SUM) operate on a set of rows, returning one result per group.

47. Define relationships in databases and list types.

Relationships define connections between tables: types include One-to-One, One-to-Many, Many-to-One, and Self-referencing relationships.

48. How can you insert `NULL` values into a column?

Insert NULL implicitly by omitting the column in INSERT or explicitly using NULL in VALUES.

49. What is the difference between `BETWEEN` and `IN` operators?

BETWEEN filters rows within a range; IN checks if values match a specified set.

50. Why are SQL functions used?

SQL functions perform calculations, format data, modify items, and convert data types.

51. What is the purpose of the `MERGE` statement?

MERGE allows conditional updates or inserts, updating rows if they exist or inserting if they don’t.

52. What is a recursive stored procedure?

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

53. What is a clause in SQL, and name examples.

Clauses filter result sets (e.g., WHERE and HAVING).

54. What is the difference between `HAVING` and `WHERE` clauses?

HAVING applies to groups and is often used with GROUP BY, whereas WHERE filters individual rows before grouping.

55. List methods to execute Dynamic SQL.

Methods include using parameters, EXEC, and sp_executesql.

56. What are the levels of constraints in SQL?

Constraints have two levels: column-level and table-level.

57. How can you fetch common records from two tables?

Use the INTERSECT statement to retrieve common records between two tables.

58. List case manipulation functions in SQL.

Functions include LOWER (converts to lowercase), UPPER (converts to uppercase), and INITCAP (capitalizes the first letter).

59. What are the set operators in SQL?

SQL has three main set operators: UNION, INTERSECT, and MINUS.
- UNION combines rows from two queries, removing duplicates.
- INTERSECT returns only rows present in both queries.
- MINUS provides rows from the left query that are not in the right query.

60. What is the alias command in SQL?

The alias command assigns a temporary name to a table or column, simplifying queries and referencing tables in complex joins.

61. What are aggregate and scalar functions?

Aggregate functions perform calculations across table columns and return a single result (e.g., COUNT, MAX).
Scalar functions return a single value based on an input value (e.g., UCASE, NOW).

63. How do you fetch alternate records from a table?

Alternate records (odd/even rows) can be fetched using ROW_NUMBER() with modulus logic, e.g., ROW_NUMBER() % 2 = 0 for even rows.

64. Which operator is used for pattern matching in SQL?

The LIKE operator is used for pattern matching. It can use % for multiple characters and _ for a single character.

65. How can you select unique records from a table?

Use the DISTINCT keyword to return unique records, e.g., SELECT DISTINCT student_id FROM students.

66. How can you fetch the first five characters of a string?

Use the SUBSTRING or LEFT function, e.g., SELECT SUBSTRING(column_name, 1, 5) FROM table_name.

67. What is the difference between SQL and PL/SQL?

SQL is a query language for performing single queries. PL/SQL is a procedural language extension by Oracle, allowing full programs with loops and control structures.

68. What is a view in SQL?

A view is a virtual table that contains a subset of data from one or more tables. It doesn’t store data, saving space.

69. What are views used for?

Views simplify complex queries, restrict access to data, enforce data independence, and provide different representations of data.

70. What is a stored procedure?

A stored procedure is a collection of SQL statements saved to be reused, reducing redundancy and improving efficiency.

71. List some advantages and disadvantages of stored procedures.

Advantages: Modular programming, faster execution, reduced network traffic, enhanced security.
Disadvantages: Executes only in the database and may consume more memory.

72. What are the types of user-defined functions?

The three main types are scalar functions, inline table-valued functions, and multi-statement table-valued functions.

73. What is collation in SQL?

Collation defines rules for data sorting and comparison, such as case sensitivity and accent sensitivity.

74. What are the types of collation sensitivity?

The types include case sensitivity, kana sensitivity, width sensitivity, and accent sensitivity.

75. What are local and global variables?

Local variables are accessible only within a function. Global variables are accessible throughout the program.

76. What is auto increment in SQL?

AUTO INCREMENT generates a unique value for new records. In SQL Server, the equivalent is IDENTITY.

77. What is a data warehouse?

A data warehouse is a central repository that stores data from multiple sources for analysis and reporting.

78. What are the authentication modes in SQL Server, and how can they be changed?

SQL Server offers Windows Authentication and Mixed Mode. Modes can be changed in SQL Server Management Studio under Security settings.

79. What are the STUFF and REPLACE functions in SQL?

STUFF replaces characters in a string at a specified position. REPLACE substitutes all instances of a substring with a new substring.

SQL Interview Questions Answers