SQL (Structured Query Language) is divided into several sub-languages based on their function. The most common interview question involve the difference between DDL and DML.

• DDL (Data Definition Language): Used to define the database structure (schema).
  • CREATE: Create a new table, database, or index.
  • ALTER: Modify the structure of an existing object.
  • DROP: Delete an object from the database.
  • TRUNCATE: Remove all records from a table (faster than DELETE).
• DML (Data Manipulation Language): Used for managing data within tables.
  • SELECT: Retrieve data.
  • INSERT: Add new rows.
  • UPDATE: Modify existing rows.
  • DELETE: Remove specific rows.

A very frequent interview question:

• DELETE: A DML command; can filter with WHERE; slower because it logs every row deletion; can be rolled back.
• TRUNCATE: A DDL command; cannot filter; faster because it deallocates data pages; cannot be rolled back easily in some DBs.

sql Standard
-- DDL
CREATE TABLE Users (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    CreatedDate TIMESTAMP
);

-- DML
INSERT INTO Users (ID, Name) VALUES (1, 'Alice');
UPDATE Users SET Name = 'Bob' WHERE ID = 1;

• Ignoring NULLs: Forgetting that NULL is not equal to anything (even another NULL). Use IS NULL.
• Primary vs Foreign Keys: Not correctly defining relationships, leading to data inconsistency.
• Reserved Keywords: Using SQL keywords (like Date, User) as table or column names.