Skip to content

Interview_Question_1.md

Latest commit

 

History

History
327 lines (269 loc) · 8.76 KB

Interview_Question_1.md

File metadata and controls

327 lines (269 loc) · 8.76 KB

SQL & Database Interview Questions

1. Difference between DBMS and RDBMS

DBMS (Database Management System)

  • Definition: Software that manages databases
  • Data Storage: Files or hierarchical structure
  • Relationships: No relationship between data
  • ACID Properties: Not supported
  • Normalization: Not supported
  • Examples: File systems, XML, Microsoft Access

RDBMS (Relational Database Management System)

  • Definition: Advanced version of DBMS that follows relational model
  • Data Storage: Tables with rows and columns
  • Relationships: Supports relationships between tables
  • ACID Properties: Fully supported (Atomicity, Consistency, Isolation, Durability)
  • Normalization: Supports normalization rules
  • Examples: MySQL, PostgreSQL, Oracle, SQL Server

Key Differences Table:

Feature DBMS RDBMS
Data Model Hierarchical/Network Relational (Tables)
Data Integrity Limited High
Security Basic Advanced
Multi-user Support Limited Full support
Data Redundancy High Low (due to normalization)
Complexity Simple Complex

2. What is Primary Key and Foreign Key?

Primary Key

  • Definition: A unique identifier for each record in a table
  • Characteristics:
    • Must be unique for each row
    • Cannot contain NULL values
    • Only one primary key per table
    • Automatically creates a unique index

Example:

CREATE TABLE students (
    student_id INT PRIMARY KEY,  -- Primary Key
    name VARCHAR(50),
    email VARCHAR(100)
);

Foreign Key

  • Definition: A field that links two tables together by referencing the primary key of another table
  • Characteristics:
    • Can contain duplicate values
    • Can contain NULL values
    • Multiple foreign keys allowed per table
    • Maintains referential integrity

Example:

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,  -- Foreign Key
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Key Differences:

Aspect Primary Key Foreign Key
Purpose Unique identifier Links tables
Uniqueness Must be unique Can be duplicate
NULL Values Not allowed Allowed
Quantity One per table Multiple per table

3. What are Constraints and Their Types?

Definition:

Constraints are rules applied to table columns to ensure data accuracy, integrity, and reliability.

Types of Constraints:

1. NOT NULL

  • Ensures a column cannot have empty values
CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(50) NOT NULL
);

2. UNIQUE

  • Ensures all values in a column are different
CREATE TABLE users (
    id INT UNIQUE,
    email VARCHAR(100) UNIQUE
);

3. PRIMARY KEY

  • Combination of NOT NULL and UNIQUE
  • Uniquely identifies each record
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

4. FOREIGN KEY

  • Links two tables and maintains referential integrity
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

5. CHECK

  • Ensures values meet specific conditions
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2) CHECK (price > 0),
    age INT CHECK (age >= 18 AND age <= 65)
);

6. DEFAULT

  • Sets a default value for a column
CREATE TABLE orders (
    id INT PRIMARY KEY,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Summary of Constraints:

Constraint Purpose Example
NOT NULL Prevents empty values name VARCHAR(50) NOT NULL
UNIQUE Ensures uniqueness email VARCHAR(100) UNIQUE
PRIMARY KEY Unique identifier id INT PRIMARY KEY
FOREIGN KEY Links tables FOREIGN KEY (user_id) REFERENCES users(id)
CHECK Validates conditions CHECK (age >= 18)
DEFAULT Sets default value status VARCHAR(20) DEFAULT 'active'

4. What are DDL and DML Commands?

DDL (Data Definition Language)

  • Purpose: Define and modify database structure
  • Characteristics:
    • Auto-commit (changes are permanent)
    • Cannot be rolled back
    • Affects database schema

DDL Commands:

  • CREATE: Creates database objects
  • ALTER: Modifies existing objects
  • DROP: Deletes objects
  • TRUNCATE: Removes all data from table

Examples:

-- CREATE
CREATE TABLE employees (id INT, name VARCHAR(50));

-- ALTER
ALTER TABLE employees ADD COLUMN salary DECIMAL(10,2);

-- DROP
DROP TABLE employees;

-- TRUNCATE
TRUNCATE TABLE employees;

DML (Data Manipulation Language)

  • Purpose: Manipulate data within tables
  • Characteristics:
    • Can be rolled back
    • Does not affect database structure
    • Works with data only

DML Commands:

  • INSERT: Adds new records
  • UPDATE: Modifies existing records
  • DELETE: Removes records
  • SELECT: Retrieves data

Examples:

-- INSERT
INSERT INTO employees (id, name, salary) VALUES (1, 'John', 50000);

-- UPDATE
UPDATE employees SET salary = 55000 WHERE id = 1;

-- DELETE
DELETE FROM employees WHERE id = 1;

-- SELECT
SELECT * FROM employees;

Comparison Table:

Aspect DDL DML
Purpose Structure definition Data manipulation
Auto-commit Yes No
Rollback Cannot rollback Can rollback
Examples CREATE, ALTER, DROP INSERT, UPDATE, DELETE, SELECT

5. Difference between DELETE, DROP, and TRUNCATE

DELETE

  • Type: DML command
  • Purpose: Removes specific rows from a table
  • Structure: Table structure remains intact
  • WHERE clause: Supported
  • Rollback: Can be rolled back
  • Speed: Slower (row-by-row deletion)
  • Auto-increment: Continues from last value

Syntax:

DELETE FROM table_name WHERE condition;

-- Examples:
DELETE FROM students WHERE marks < 50;  -- Conditional
DELETE FROM students;                   -- All records

DROP

  • Type: DDL command
  • Purpose: Removes entire table/database from system
  • Structure: Completely destroys table structure
  • WHERE clause: Not supported
  • Rollback: Cannot be rolled back
  • Speed: Fastest
  • Recovery: Cannot recover data

Syntax:

DROP TABLE table_name;
DROP DATABASE database_name;

-- Examples:
DROP TABLE students;    -- Removes entire table
DROP DATABASE school;   -- Removes entire database

TRUNCATE

  • Type: DDL command
  • Purpose: Removes all rows from table
  • Structure: Table structure remains intact
  • WHERE clause: Not supported
  • Rollback: Cannot be rolled back
  • Speed: Fast
  • Auto-increment: Resets to starting value

Syntax:

TRUNCATE TABLE table_name;

-- Example:
TRUNCATE TABLE students;  -- Removes all data, keeps structure

Detailed Comparison:

Feature DELETE DROP TRUNCATE
Command Type DML DDL DDL
Removes Specific/All rows Entire table All rows
Table Structure Preserved Destroyed Preserved
WHERE Clause ✅ Supported ❌ Not supported ❌ Not supported
Rollback ✅ Possible ❌ Not possible ❌ Not possible
Speed 🐌 Slow ⚡ Fastest 🚀 Fast
Auto-increment Continues N/A Resets
Triggers Fired N/A Not fired
Memory Uses more Uses least Uses less

When to Use:

  • DELETE: When you need to remove specific records or want rollback option
  • DROP: When you want to completely remove table/database
  • TRUNCATE: When you want to quickly remove all data but keep table structure

Example Scenario:

-- Sample table
CREATE TABLE test_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO test_table (name) VALUES ('A'), ('B'), ('C');

-- DELETE: Remove specific record
DELETE FROM test_table WHERE name = 'B';  -- Only removes 'B'

-- TRUNCATE: Remove all records, keep table
TRUNCATE TABLE test_table;  -- Table exists, but empty, id resets

-- DROP: Remove entire table
DROP TABLE test_table;  -- Table no longer exists

🎯 Quick Summary for Interviews:

  1. DBMS vs RDBMS: RDBMS supports relationships, ACID properties, and normalization
  2. Keys: Primary Key = unique identifier, Foreign Key = links tables
  3. Constraints: Rules to ensure data integrity (NOT NULL, UNIQUE, etc.)
  4. DDL vs DML: DDL defines structure (CREATE, DROP), DML manipulates data (INSERT, SELECT)
  5. DELETE/DROP/TRUNCATE: DELETE removes rows (rollback possible), DROP removes table (permanent), TRUNCATE removes all rows (fast, permanent)