SQL Interview Questions and Answers

This section covers frequently asked SQL interview questions for both beginners and experienced professionals.

1. What is SQL?

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It's used to create, update, and retrieve data from databases.

2. When Did SQL Appear?

SQL's origins date back to the 1970s, with standardization occurring in the 1980s (ANSI in 1986 and ISO in 1987).

3. Usages of SQL.

• Querying data (SELECT)
• Inserting data (INSERT)
• Updating data (UPDATE)
• Deleting data (DELETE)
• Creating databases and tables (CREATE)
• Creating views (CREATE VIEW)
• Managing database structure (ALTER TABLE, etc.)

4. Does SQL Support Programming Language Features?

SQL is primarily a declarative language, not a procedural one. It lacks constructs like loops or conditional statements found in most programming languages. While procedural extensions exist (PL/SQL, T-SQL), core SQL focuses on data manipulation.

5. Subsets of SQL.

• DDL (Data Definition Language): Defines database structure (CREATE, ALTER, DROP).
• DML (Data Manipulation Language): Manipulates data (SELECT, INSERT, UPDATE, DELETE).
• DCL (Data Control Language): Manages access permissions (GRANT, REVOKE).
• TCL (Transaction Control Language): Manages database transactions (COMMIT, ROLLBACK).

6. Purpose of DDL.

DDL (Data Definition Language) commands define the structure of a database and its objects (tables, indexes, views, etc.).

7. Purpose of DML.

DML (Data Manipulation Language) commands allow you to retrieve, insert, update, and delete data within a database.

8. Purpose of DCL.

DCL (Data Control Language) commands control access to data, managing user permissions and roles.

9. Tables and Fields in a Database.

A table is an organized collection of data. Fields (or columns) are the individual attributes within a table.

10. Primary Keys.

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

11. Foreign Keys.

Foreign keys create relationships between tables. A foreign key in one table references the primary key of another table.

12. Unique Keys.

A unique key ensures that all values in a column (or set of columns) are unique. It can contain one NULL value.

13. Primary Key vs. Unique Key.

14. What is a Database?

A database is an organized collection of structured information, typically stored electronically. It's designed for efficient storage, retrieval, and management of data.

15. What is DBMS (Database Management System)?

A DBMS is software that manages and provides access to a database. It provides tools for creating, updating, and querying data.

16. Types of Database Management Systems (DBMS).

• Hierarchical databases
• Network databases
• Relational databases (RDBMS)
• Object-oriented databases
• Document databases
• Graph databases
• NoSQL databases

17. What is RDBMS?

RDBMS (Relational Database Management System) is a type of DBMS that stores data in tables with relationships between them. Data is accessed and manipulated using SQL.

18. Database Normalization.

Normalization is a process of organizing data to reduce redundancy and improve data integrity. Normal forms (1NF, 2NF, 3NF, BCNF) define levels of normalization.

19. Primary Use of Normalization.

Normalization reduces data redundancy and improves data integrity by minimizing data anomalies (insertion, update, deletion anomalies).

20. Disadvantages of Not Normalizing.

• Data redundancy (wastes storage space).
• Data inconsistency (update anomalies).

21. Inconsistent Dependency.

An inconsistent dependency means that data is not properly linked, causing problems when trying to retrieve related information.

22. Denormalization.

Denormalization is a technique of adding redundancy to a database to improve query performance. It's often done after normalization to optimize specific queries.

23. Types of SQL Operators.

SQL uses various operators:

• Arithmetic: +, -, *, /, %
• Logical: AND, OR, NOT, BETWEEN, IN, LIKE, EXISTS
• Comparison: =, !=, <, >, <=, >=
• Bitwise: &, |, ^, ~
• Compound: +=, -=, *=, /=
• String: || (concatenation), LIKE (pattern matching)

24. Views in SQL.

A view is a virtual table based on the result-set of an SQL statement. It doesn't store data itself but provides a customized way to access data from underlying tables.

25. Indexes in SQL.

Indexes are data structures that speed up data retrieval. They work similarly to an index in a book. They improve query performance by reducing the amount of data that needs to be scanned.

26. Types of SQL Indexes.

• Unique index
• Clustered index
• Non-clustered index
• Bitmap index
• Composite index
• B-tree index
• Function-based index

27. Unique Indexes.

A unique index ensures that all values in a column (or a set of columns) are unique. A unique index is created automatically when a primary key is defined.

28. Clustered Indexes.

A clustered index determines the physical order of data rows in a table. A table can have only one clustered index.

29. Non-Clustered Indexes.

Non-clustered indexes are separate from the data; they point to the data rows. A table can have multiple non-clustered indexes.

30. SQL, MySQL, and SQL Server: Key Differences.

16. Types of Database Management Systems.

• Hierarchical databases
• Network databases
• Relational databases (RDBMS)
• Object-oriented databases
• Document databases (NoSQL)
• Graph databases (NoSQL)
• Key-value stores (NoSQL)

17. What is RDBMS?

RDBMS (Relational Database Management System) stores data in tables with relationships between them. Data is accessed and managed using SQL.

18. Database Normalization.

Normalization organizes database tables to reduce redundancy and improve data integrity. Normal forms (1NF, 2NF, 3NF, BCNF) guide this process.

19. Primary Use of Normalization.

Normalization minimizes data redundancy and anomalies (insertion, update, deletion anomalies) by organizing data into well-structured tables.

20. Disadvantages of Not Normalizing.

• Wasted storage space due to redundancy.
• Data inconsistencies due to update anomalies.
• Increased maintenance challenges.

21. Inconsistent Dependency.

An inconsistent dependency occurs when data relationships are poorly defined, making it difficult to retrieve accurate information.

22. Denormalization.

Denormalization adds redundancy to a database (after normalization) to improve query performance, often by reducing the need for complex joins.

23. Types of SQL Operators.

SQL supports various operators:

• Arithmetic: +, -, *, /, % (modulo)
• Logical: AND, OR, NOT, BETWEEN, IN, LIKE, EXISTS, IS NULL
• Comparison: =, !=, <, >, <=, >=
• Bitwise: &, |, ^, ~
• Compound Assignment: +=, -=, *=, /=, %=
• String: + (concatenation), LIKE (pattern matching)

24. Views in SQL.

A view is a virtual table based on an SQL query. It doesn't store data but provides a customized way to access data from one or more tables, often for security or simplifying queries.

25. Indexes in SQL.

Indexes are data structures that significantly speed up data retrieval from a table. They work by creating a separate lookup structure containing keys and pointers to the actual data rows, making it faster to find specific records.

26. Types of Indexes.

• Unique index
• Clustered index
• Non-clustered index
• Bitmap index
• Composite index
• Full-text index

27. Unique Index.

A unique index ensures that all values in a column are unique. It's automatically created for primary keys.

28. Clustered Index.

A clustered index determines the physical order of data rows in a table. A table can have only one clustered index.

29. Non-Clustered Index.

A non-clustered index is a separate structure that points to data rows in a table. A table can have many non-clustered indexes.

30. SQL, MySQL, and SQL Server: Differences.

31. SQL vs. PL/SQL.

32. Sorting with Column Aliases.

Yes, you can use column aliases in the ORDER BY clause.

33. Clustered vs. Non-Clustered Indexes.

34. Displaying the Current Date in SQL.

(This section would provide the specific syntax for getting the current date/time, which varies depending on the database system. Example: `GETDATE()` in SQL Server, `NOW()` in MySQL, `SYSDATE` in Oracle.)

35. Commonly Used SQL Joins.

• INNER JOIN
• LEFT (OUTER) JOIN
• RIGHT (OUTER) JOIN

36. Types of SQL Joins.

(This section would list the various types of joins, including inner, left outer, right outer, full outer, self, and cross joins.)

37. INNER JOIN.

Returns rows only when there's a match in both tables.

38. RIGHT JOIN.

Returns all rows from the right table and matching rows from the left table.

39. LEFT JOIN.

Returns all rows from the left table and matching rows from the right table.

40. FULL JOIN.

Returns all rows from both tables.

41. Triggers in SQL.

Triggers are stored procedures automatically executed in response to events (INSERT, UPDATE, DELETE) on a table.

42. Self-Joins.

A self-join joins a table to itself, often used to compare rows within the same table.

43. Set Operators in SQL (Continued).

Set operators combine result sets from multiple queries:

• UNION: Combines results, removing duplicates.
• UNION ALL: Combines results, retaining duplicates.
• INTERSECT: Returns common rows from multiple result sets.
• MINUS (or EXCEPT): Returns rows from the first result set that are not present in the second.

44. IN vs. BETWEEN Operators.

45. Constraints in SQL.

Constraints are rules ensuring data integrity. They enforce restrictions on what data can be stored in a table. Constraints can be defined at the column level or table level.

46. SQL Query: Students' Names Starting with "A".

SELECT * FROM students WHERE name LIKE 'A%';

47. Finding the Third Highest Salary.

Several methods exist; one approach uses the OFFSET and LIMIT clauses (in MySQL and some other systems):

SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

(Other approaches using subqueries and window functions would be listed here.)

48. DELETE vs. TRUNCATE.

49. ACID Properties.

ACID properties ensure database transaction reliability:

• Atomicity: All operations succeed or none do.
• Consistency: Data integrity is maintained.
• Isolation: Concurrent transactions are isolated from each other.
• Durability: Committed transactions persist permanently.

50. NULL vs. Blank Space/Zero.

NULL represents the absence of a value; it's not the same as zero or a blank space. Comparison with NULL generally requires special handling (e.g., using IS NULL or IS NOT NULL).

51. Functions in SQL.

SQL functions perform specific tasks on data. They can be user-defined or system-defined (built-in).

52. Case Manipulation Functions.

Case manipulation functions change the case of characters (e.g., converting to upper or lower case):

• LOWER()
• UPPER()
• INITCAP()

53. Character Manipulation Functions.

Character manipulation functions modify or extract parts of strings.

• CONCAT(): Combines strings
• SUBSTR(): Extracts substrings
• LENGTH(): Gets string length
• TRIM(): Removes leading/trailing spaces

43. Set Operators in SQL (Continued).

Set operators combine results from multiple SELECT statements. They work on rows, not columns (unlike joins):

• UNION: Combines results, removing duplicates.
• UNION ALL: Combines results, keeping duplicates.
• INTERSECT: Returns only rows that appear in all input result sets.
• MINUS (or EXCEPT): Returns rows from the first result set that are not in the second.

44. IN vs. BETWEEN.

45. Constraints in SQL.

Constraints are rules ensuring data integrity. Types include:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

Constraints can be defined at the column level or table level.

46. SQL Query: Names Starting with "A".

SELECT * FROM students WHERE name LIKE 'A%';

47. Finding the Third Highest Salary.

(Multiple approaches would be detailed here, perhaps including examples using OFFSET and LIMIT, subqueries, window functions (like RANK()), or other techniques depending on the specific database system.)

48. DELETE vs. TRUNCATE.

49. ACID Properties.

ACID properties (Atomicity, Consistency, Isolation, Durability) ensure reliable database transactions.

50. NULL vs. Blank/Zero.

NULL represents the absence of a value; it's different from zero or a blank. Comparisons with NULL use IS NULL or IS NOT NULL.

51. Functions in SQL.

Functions perform operations on data, returning a single value. They can be user-defined or built-in (system-defined).

52. Case Manipulation Functions.

Functions to convert case (uppercase, lowercase):

• LOWER()
• UPPER()
• INITCAP()

53. Character Manipulation Functions.

Functions for manipulating strings (concatenation, substring extraction, etc.):

• CONCAT()
• SUBSTR()
• LENGTH()
• INSTR()
• LPAD()
• RPAD()
• TRIM()
• REPLACE()

54. NVL() Function.

NVL() (in Oracle) replaces NULL values with a specified value. MySQL uses IFNULL(), and SQL Server uses ISNULL().

55. MOD() Function.

MOD() returns the remainder of a division operation.

56. COALESCE() Function.

COALESCE() returns the first non-NULL value from a list of expressions.

57. The DISTINCT Keyword.

DISTINCT in a SELECT statement returns only unique rows.

58. Default Ordering with ORDER BY.

ORDER BY sorts data in ascending order by default. Use DESC for descending order.

59. WHERE Clause with Aggregate Functions.

You cannot use a WHERE clause to filter groups; use HAVING instead.

60. WHERE vs. HAVING Clauses.

61. Aggregate Functions in SQL.

Aggregate functions perform calculations on multiple rows and return a single value:

• AVG()
• COUNT()
• MAX()
• MIN()
• SUM()
• FIRST()
• LAST()

62. SQL Injection.

SQL injection is a security vulnerability allowing attackers to manipulate database queries, potentially accessing or modifying sensitive data.

63. RANK() vs. DENSE_RANK().

Both assign ranks within ordered partitions. RANK() allows gaps in rankings for ties; DENSE_RANK() assigns consecutive ranks without gaps.

64. Implicitly Inserting into Identity Columns.

You can usually insert values into identity columns implicitly (the database automatically generates the next value). However, some systems allow explicit insertion into identity columns using the correct syntax (like `SET IDENTITY_INSERT` in SQL Server).

65. SQL Comments.

Comments improve code readability. Single-line comments start with --; multi-line comments are enclosed in /* ... */.

Advanced SQL Multiple Choice Questions:

1. Outer join is needed to include rows with non-matching values.
2. CASE statements handle conditional logic in SQL.
3. LINT is not a standard SQL data type.

Advanced SQL Multiple Choice Questions

This section presents multiple-choice questions focusing on more advanced SQL concepts.

1. Choosing the Right Join Type.

An outer join is necessary when you need to include rows from one or both tables even if there's no match in the other table.

2. SQL CASE Statement.

The CASE statement provides conditional logic in SQL, similar to if-then-else statements in programming languages.

3. Illegal SQL Data Type.

From the given options, LINT is not a standard SQL data type.

4. Materialized Views.

Materialized views are pre-computed tables storing the results of queries. They're updated periodically, providing faster access to frequently used data.

5. SQL Constructs.

The Persistent Storage Module (PSM) extends SQL by allowing the creation of user-defined functions, procedures, and types.

6. Correlated Subqueries.

A correlated subquery is executed repeatedly for each row processed by the outer query. It uses values from the outer query in its WHERE clause.

7. Statements Executed on Database Modification.

Triggers are automatically executed when data in a table is modified (INSERT, UPDATE, DELETE).

8. When Does a Transaction Begin?

A transaction begins implicitly when the first data-modifying operation is performed or explicitly with commands like `BEGIN TRANSACTION` (or its equivalent in different database systems).

9. Statements Not Allowed in a Function.

The WAITFOR statement (in some database systems) is typically not allowed within a user-defined function.

10. TIME(p) Data Type.

In the TIME(p) data type, p specifies the precision (number of fractional digits) for seconds.

11. SQL Privileges.

(This section would list various SQL privileges like `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `CREATE`, `ALTER`, `DROP`, etc.)

12. Referential Integrity Constraints.

Referential integrity constraints are also known as subset dependencies.

13. Triggers: Called or Executed Automatically.

Triggers are executed automatically in response to database events.

14. Outer Join vs. Equijoin.

Outer joins include rows even when there's no match in the other table. Equijoins only include matching rows.

15. Database Transactions.

A transaction is a sequence of operations treated as a single unit of work. It follows the ACID properties (Atomicity, Consistency, Isolation, Durability).