SQL Interview Questions

Jun 26, 2024

SQL Interview Questions Lecture Notes

Introduction

  • Presenter: Sahiti from Edureka
  • Focus: Top 65 SQL interview questions
  • **Importance of SQL: **
    • Crucial for data handling and decision-making in business
    • Core of relational database management systems
    • Essential for becoming a database administrator

Key SQL Interview Questions and Concepts

1. Difference between DELETE and TRUNCATE

  • **DELETE: **

    • Deletes a row in a table
    • Can roll back data
    • Data Manipulation Command

  • **TRUNCATE: **

    • Deletes all rows from a table
    • Cannot roll back data
    • Data Definition Language Command
    • Faster than DELETE

2. Subsets of SQL

  • **Data Definition Language (DDL): ** Defines the database schema
  • **Data Manipulation Language (DML): ** Deals with manipulation of data
  • **Data Control Language (DCL): ** Deals with rights and permissions
  • **Transaction Control Language (TCL): ** Deals with database transactions

3. Database Management Systems (DBMS)

  • **Definition: ** Software application that interacts with user applications and databases
  • **Types of DBMS: **
    • Hierarchical
    • Relational
    • Network
    • Object-Oriented

4. Table and Field

  • **Table: ** Collection of data in organized rows and columns
  • **Field: ** Number of columns in a table

5. Joins in SQL

  • Combine rows from two or more tables based on related columns
  • **Types: **
    • Inner Join
    • Full Join
    • Left Join
    • Right Join

6. Difference between CHAR and VARCHAR

  • CHAR: Fixed-length character strings
  • VARCHAR: Variable-length character strings

7. Primary Key

  • **Definition: ** Uniquely identifies each tuple in a table
  • **Example: ** Employee ID in an employee table

8. Constraints

  • Specifies the limit on data type of a table
  • **Types: **
    • Not Null
    • Unique
    • Check
    • Default
    • Index

9. Difference between SQL and MySQL

  • **SQL: ** Standard language for managing databases
  • **MySQL: ** Relational database management system

10. Unique Key

  • Identifies a single row in a table
  • Allows multiple values and null values but no duplicate values

11. Foreign Key

  • Maintains referential integrity by linking data between tables
  • Refers to the primary key in the parent table

12. Data Integrity

  • **Definition: ** Accuracy and consistency of data in the database

13. Clustered vs Non-Clustered Index

  • **Clustered Index: ** Alters the physical order of the table, faster, only one per table
  • **Non-Clustered Index: ** Does not alter physical order, slower, many per table

14. SQL Query to Display Current Date

  • Function: GETDATE()
  • Query Example: SELECT GETDATE();

15. Types of Joins

  • Inner Join: Returns records with matching values in both tables
  • Full Join: Returns records with matching values or no matches in both tables
  • 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

16. Denormalization

  • **Definition: ** Technique to access data from higher to lower forms of database, introduces redundancy

17. Entities and Relationships

  • Entities: Objects or things in real-world represented in database
  • Relationships: Links between entities

18. Index in SQL

  • **Definition: ** Performance tuning method for faster data retrieval
  • Types of Indexes: Unique, Clustered, Non-clustered

19. Normalization and Its Advantages

  • **Definition: ** Organizing data to avoid duplication and redundancy
  • **Advantages: ** Better organization, efficient data access, easy modification, etc.

20. Difference between DROP and TRUNCATE

  • DROP: Removes entire table, cannot roll back
  • **TRUNCATE: ** Removes all rows from a table, cannot roll back

21. Types of Normalization

  • 1NF: No repeating groups
  • 2NF: Single column primary key
  • 3NF: No transitive functional dependency
  • BCNF: Only one candidate key

22. ACID Property in Database

  • Atomicity, Consistency, Isolation, Durability: Ensures reliable transactions

23. Trigger in SQL

  • Definition: Special type of procedure that executes automatically on data modification
  • Types: Before Insert, After Insert, Before Update, After Update, Before Delete, After Delete

24. SQL Operators

  • Types: Arithmetic, Bitwise, Comparison, Compound, Logical

25. NULL Values vs Zero/Blank Space

  • NULL Value: Unavailable, unknown, or not applicable
  • Zero: A number
  • Blank Space: A character

26. Cross Join vs Natural Join

  • Cross Join: Produces Cartesian product of two tables
  • Natural Join: Based on columns with same name and data types in both tables

27. Subquery

  • Definition: Query inside another query
  • Types: Correlated, Non-Correlated

28. Fetching Records Count in a Table

  • Queries:
    • SELECT COUNT(*) FROM table_name;
    • SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2;

29. Select Names of Employees Starting with A

  • Query: SELECT * FROM employees WHERE name LIKE 'A%';

30. Getting the Third Highest Salary

  • Query: 
    SELECT TOP 1 salary
FROM (
    SELECT TOP 3 salary 
    FROM employee_table
    ORDER BY salary DESC
) AS emp
ORDER BY salary ASC;

31. Group Functions in SQL

  • Purpose: Work on a set of rows and return a single result
  • Examples: AVG, COUNT, MAX, MIN, SUM

32. Types of Relationships

  • One-to-One
  • One-to-Many
  • Many-to-One
  • Self-Referencing

33. Inserting NULL Values

  • Methods: Implicitly by omitting columns or explicitly by specifying NULL keyword

34. Difference between BETWEEN and IN Operators

  • BETWEEN: Used to display rows in a range of values
  • IN: Used to check for values in a specific set

35. SQL Functions Usage

  • Purpose: Perform calculations, modify data, format dates/numbers, convert data types

36. Need of MERGE Statement

  • Allows conditional update or insertion

37. Recursive Stored Procedure

  • Definition: Procedure that calls itself until a boundary condition is met

38. SQL Clause

  • Purpose: Limits result set by providing condition to the query (e.g., WHERE, HAVING)

39. Difference between HAVING and WHERE Clause

  • HAVING: Used with SELECT statement, mainly with GROUP BY
  • WHERE: Applied to each row before GROUP BY function

40. Dynamic SQL Execution

  • Methods: Writing query with parameters, using EXEC, using sp_executesql

41. Levels of Constraints

  • Types: Column-level, Table-level

42. Fetching Common Records

  • Method: Using INTERSECT statement

43. Case Manipulation Functions

  • Types: LOWER, UPPER, INITCAP

44. Set Operators in SQL

  • Types: UNION, INTERSECT, MINUS

45. Alias Command

  • Purpose: Temporary name given to a table or column

46. Aggregate vs Scalar Functions

  • Aggregate: Evaluate mathematical calculations on data and return a single value (e.g., MAX, COUNT)
  • Scalar: Return a single value based on input (e.g., UCASE, NOW)

47. Fetch Alternate Records

  • Method:
    • Even Rows: SELECT student_id FROM (SELECT ROW_NUMBER() OVER (ORDER BY roll_no) AS row_num, student_id FROM students) WHERE row_num % 2 = 0;
    • Odd Rows: SELECT student_id FROM (SELECT ROW_NUMBER() OVER (ORDER BY roll_no) AS row_num, student_id FROM students) WHERE row_num % 2 = 1;

48. Pattern Matching Operator

  • Operator: LIKE
    • Usage: SELECT * FROM students WHERE name LIKE 'A%';
    • Symbols Used: Percentage sign (%), Underscore (_)

49. Select Unique Records

  • Keyword: DISTINCT

50. Fetching First Five Characters of a String

  • Methods: SUBSTRING, RIGHT
    • Example: SELECT SUBSTRING(student_name, 1, 5) FROM students;

51. SQL vs PL/SQL

  • SQL: Language for issuing single queries
  • PL/SQL: Oracle's procedural language allowing multi-operation functions

52. View

  • Definition: Virtual table consisting of a subset of data

53. Use of Views

  • Restrict data access, simplify complex queries, ensure data independence, provide different perspectives of the same data

54. Stored Procedure

  • Definition: Function that includes many SQL statements for accessing databases
  • Advantages: Modular programming, faster execution, reduced network traffic, better security
  • Disadvantages: Can only be executed in the database, uses more memory

55. Types of User-Defined Functions

  • Scalar Functions
  • Inline Table-Valued Functions
  • Multi-Statement Table-Valued Functions

56. Collation

  • Definition: Set of rules for data sorting and comparison

57. Types of Collation Sensitivity

  • Types: Case Sensitivity, Kana Sensitivity, Width Sensitivity, Accent Sensitivity

58. Local vs Global Variables

  • Local Variables: Only used within a function
  • Global Variables: Accessed throughout the program

59. Auto Increment in SQL

  • Used for generating unique numbers for new records
  • Keyword: AUTO_INCREMENT in Oracle, IDENTITY in SQL Server

60. Data Warehouse

  • Definition: Central repository of data from multiple sources for analytics

61. Authentication Modes in SQL Server

  • Types: Windows Mode, Mixed Mode
  • Changing Modes: Use SQL Server Enterprise Manager

62. STUFF and REPLACE Functions

  • STUFF: Overrides characters in a string starting at a specified position
  • REPLACE: Replaces existing characters in a string with new characters

Conclusion

  • Understanding Key Questions: Crucial for SQL interviews
  • Practice: Through queries and concepts to ensure readiness
  • Acknowledgement: Thanks for attending and good luck with interviews!