Analytics Vidya SQL Lecture Notes

Introduction to SQL

• Structured Query Language (SQL) is a popular database language.
• Used for creating, managing, updating, and retrieving data from databases.
• Essential skill for IT roles such as data analysis, business intelligence, and web development.

Top 10 Conceptual SQL Interview Questions

1. What is DBMS?

• DBMS: Database Management System.
  • Software for storing, creating, updating, and managing databases.
  • Acts as a digital filing cabinet.
  • Interface between developers and end-users.
• **Types of DBMS:
  • Relational DBMS (RDBMS): Data organized in 2D tables (rows and columns).
  • Object-oriented DBMS: Data represented as objects (used in OOP).
  • Hierarchical DBMS: Data elements have a one-to-many relationship.
  • Network DBMS: Data elements maintain one-to-many or many-to-many relationships.

2. Difference between DBMS and RDBMS

• DBMS:
  • Stores data as files.
  • Handles small amounts of data.
  • Individual access to data elements.
  • Does not support multiple users and distributed databases.
  • Normalization is achievable.
• RDBMS:
  • Stores data in rows and columns (tables).
  • Handles large amounts of data.
  • Multiple data elements accessible together.
  • Supports multiple users and distributed databases.
  • Normalization is not achievable.

3. Explain Joins in SQL

• Joins: Combine rows from one or more tables based on common columns.
• **Types of Joins:
  • Inner Join: Returns rows when the condition is satisfied.
  • Left Join: Returns all rows from the left table and matched rows from the right table.
  • Right Join: Returns all rows from the right table and matched rows from the left table.
  • Full Join: Returns all rows when there is a match in any table.

4. Explain ETL in SQL

• ETL: Extract, Transform, and Load.
  • Extract: Gather data from various sources.
  • Transform: Sort, clean, and prepare data.
  • Load: Load data into the final destination (database).
  • Includes processes like filtering, sorting, joining, aggregating, and analyzing data.

5. What are Constraints in SQL?

• Constraints: Rules to ensure accuracy and reliability of data in a table.
  • If violated, the action is aborted.
• Types of Constraints:
  • Not Null: Ensures a column cannot have a null value.
  • Unique: Guarantees unique values in a column (no duplicates).
  • Primary Key: Combination of Not Null and Unique.
  • Foreign Key: Ensures referential integrity between tables.
  • Check: Ensures that the value in a column meets a specific condition.
  • Default: Sets a default value for a column if no value is specified.

6. What is Denormalization?

• Normalization: Organizes data to minimize redundancy.
• Denormalization: Adds redundant data to improve read performance.

7. What are the Subsets of SQL?

• DDL (Data Definition Language): Structure of data (create, modify, delete tables).
• DML (Data Manipulation Language): Manipulate and manage data (retrieve, insert, update data).
• DCL (Data Control Language): Manage access to data (grant, revoke permissions).
• TCL (Transaction Control Language): Manage transactions (commit, rollback).

8. What is a View and Difference between Views and Tables?

• Table: Collection of data organized as rows and columns.
• View: Virtual table representing a subset of the data from one or more tables.

9. Uses of SQL Functions

• Perform operations on data.
• Calculate, convert data types, format dates/numbers.
• Manipulate text data (strings).

10. ACID Properties in SQL

• Atomicity: All or nothing execution of transactions.
• Consistency: Transitions the database from one consistent state to another.
• Isolation: Ensures transactions do not interfere with each other.
• Durability: Once a transaction is committed, it is permanent. Guarantees data integrity during failures.

Conclusion

• SQL is essential for many IT and data-related roles.
• Understanding core concepts, such as DBMS, RDBMS, Joins, Constraints, ETL, and ACID properties, is critical for interviews.
• Stay updated and practice SQL regularly.