DDL and DML commands

DDL (Data Definition Language) Commands

DDL commands are used to define, modify, and manage the structure of a database and its objects (such as tables, schemas, etc.).

Command	Description	Example
CREATE	Creates a new database object (table, view, index, etc.).	`CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(50), age INT);`
ALTER	Modifies the structure of an existing database object (add, modify, or drop columns).	`ALTER TABLE students ADD COLUMN email VARCHAR(100);`
DROP	Deletes a database object permanently.	`DROP TABLE students;`
TRUNCATE	Removes all rows from a table but retains the structure for future use.	`TRUNCATE TABLE students;`
RENAME	Renames a database object (like a table).	`RENAME TABLE students TO learners;`

DML (Data Manipulation Language) Commands

DML commands are used to manipulate the data stored in database tables.

Command	Description	Example
INSERT	Adds new rows of data into a table.	`INSERT INTO students (id, name, age) VALUES (1, 'John Doe', 20);`
UPDATE	Modifies existing data in a table.	`UPDATE students SET age = 21 WHERE id = 1;`
DELETE	Deletes specific rows from a table.	`DELETE FROM students WHERE id = 1;`
SELECT	Retrieves data from one or more tables (technically a DQL command, but often considered part of DML).	`SELECT * FROM students;`

Example

-- DDL Example: Creating a table
CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    salary DECIMAL(10, 2)
);

-- DML Example: Inserting data into the table
INSERT INTO employees (emp_id, emp_name, salary)
VALUES (1, 'Alice', 50000.00);

-- DML Example: Updating data
UPDATE employees
SET salary = 55000.00
WHERE emp_id = 1;

-- DML Example: Retrieving data
SELECT * FROM employees;

-- DDL Example: Altering the table to add a new column
ALTER TABLE employees ADD COLUMN department VARCHAR(50);

-- DDL Example: Dropping the table
DROP TABLE employees

DDL Commands with Interview Questions and Answers

1. CREATE

Question: What is the difference between the CREATE and ALTER commands?

Answer:
- CREATE is used to make new database objects like tables, views, or databases.
- ALTER is used to change the structure of existing database objects, such as adding or removing columns.
  - Example ofCREATE:
```
  sqlCopy codeCREATE TABLE employees (
      emp_id INT PRIMARY KEY,
      emp_name VARCHAR(100)
  );
```
  - Example of⁣ALTER:
```
  sqlCopy codeALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);
```

2. ALTER

Question: How do you add a foreign key using the ALTER command?
- Answer:
  - Use the ADD CONSTRAINT clause in the ALTER command to define the foreign key.
  - Example:
```
  sqlCopy codeALTER TABLE orders
  ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES 
  customers(customer_id);
```

Question: How can you drop multiple columns using ALTER?
- Answer:
  - Some database systems (like MySQL 8.0+) support dropping multiple columns at once.
```
  sqlCopy codeALTER TABLE employees DROP COLUMN salary, 
  DROP COLUMN department;
```
  - For older versions or systems that don't support this syntax, you must drop each column separately.
  - Answer:
    - Some database systems (like MySQL 8.0+) support dropping multiple columns at once.
```
  sqlCopy codeALTER TABLE employees DROP COLUMN salary, DROP COLUMN department;
```
    - For older versions or systems that don't support this syntax, you must drop each column separately.

Got it! Here's a revised version of the DDL and DML interview questions section, now including detailed answers for each question. This will help you prepare for interviews and serve as excellent content for your blog.

DDL Commands with Interview Questions and Answers

1. CREATE

Question: What is the difference between CREATE and ALTER commands?
- Answer:
  - CREATE is used to create new database objects like tables, views, or databases.
  - ALTER is used to modify the structure of existing database objects, such as adding or deleting columns.
  - Example of CREATE:
```
  sqlCopy codeCREATE TABLE employees (
      emp_id INT PRIMARY KEY,
      emp_name VARCHAR(100)
  );
```
  - Example of ALTER:
```
  sqlCopy codeALTER TABLE employees ADD COLUMN salary DECIMAL(10, 2);
```

2. ALTER

Question: How do you add a foreign key using the ALTER command?
- Answer:
  - Use the ADD CONSTRAINT clause in the ALTER command to define the foreign key.
  - Example:
```
  sqlCopy codeALTER TABLE orders
  ADD CONSTRAINT fk_customer
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
```
Question: How can you drop multiple columns using ALTER?
- Answer:
  - Some database systems (like MySQL 8.0+) support dropping multiple columns at once:
```
  sqlCopy codeALTER TABLE employees DROP COLUMN salary, DROP COLUMN department;
```
  - For older versions or systems that don't support this syntax, you must drop each column separately.

3. DROP

Question: What’s the difference between DROP and TRUNCATE?
- Answer:
  - DROP: Removes the table and all its data, structure, and constraints permanently.
  - TRUNCATE: Deletes all rows but retains the table structure for future use.
  - Example of DROP:
```
  sqlCopy codeDROP TABLE employees;
```
  - Example of TRUNCATE:
```
  sqlCopy codeTRUNCATE TABLE employees;
```

4. TRUNCATE

Question: Why is TRUNCATE faster than DELETE?
- Answer:
  - TRUNCATE is faster because it bypasses the transaction log for each row and instead deallocates the entire data pages directly.
  - Example:
```
  sqlCopy codeTRUNCATE TABLE employees;
```

5. RENAME

Question: Can RENAME be used with columns? If not, how do you rename a column?
- Answer:
  - RENAME is typically used for renaming tables, not columns.
  - To rename a column, use the ALTER command:
```
  sqlCopy codeALTER TABLE employees 
  RENAME COLUMN emp_name TO employee_name;
```
    (Supported in PostgreSQL and MySQL 8.0+. In SQL Server, use GUI tools or create a new column and copy data.)

DML Commands with Interview Questions and Answers

1. INSERT

Question: How can you insert data from one table into another?

Answer:

Use the INSERT INTO ... SELECT syntax to copy data:

  sqlCopy codeINSERT INTO employees_backup 
  (emp_id, emp_name, salary)
  SELECT emp_id, emp_name, salary FROM employees;

Question: How do you handle duplicate records during insertion?

Answer:

Use ON DUPLICATE KEY UPDATE (MySQL) or MERGE (SQL Server).

Example in MySQL:

  sqlCopy codeINSERT INTO employees 
  (emp_id, emp_name, salary)
  VALUES (1, 'Alice', 50000.00)
  ON DUPLICATE KEY UPDATE salary = 50000.00;

2. UPDATE

Question: How can you update multiple columns in one query?
- Answer:
  - Use the SET clause with multiple column assignments:
```
  sqlCopy codeUPDATE employees
  SET emp_name = 'Bob', salary = 70000.00
  WHERE emp_id = 1;
```
Question: How do you ensure updates don’t violate constraints?
- Answer:
  - Use WHERE clauses and check existing data before updating.
  - Example:
```
  sqlCopy codeUPDATE employees
  SET salary = salary * 1.1
  WHERE department = 'IT' AND salary < 80000.00;
```

3. DELETE

Question: Can DELETE be used without a WHERE clause?
- Answer:
  - Yes, but it will delete all rows from the table. Use caution:
```
  sqlCopy codeDELETE FROM employees;
```

4. SELECT

Question: How do you write a query to get the top N highest salaries?
- Answer:
  - Use ORDER BY with LIMIT or FETCH FIRST:
```
  sqlCopy codeSELECT emp_name, salary
  FROM employees
  ORDER BY salary DESC
  FETCH FIRST 3 ROWS ONLY;
```

Question: How do you combine results from two tables?

Answer:

Use UNION or UNION ALL:

  sqlCopy codeSELECT emp_id, emp_name 
  FROM employees UNION SELECT customer_id,
  customer_name FROM customers;

Advanced Scenarios

Scenario 1: Handling Constraints

Question: How do you add a UNIQUE constraint after table creation?

Answer:

Use the ALTER command:

  sqlCopy codeALTER TABLE employees 
  ADD CONSTRAINT unique_emp_name UNIQUE (emp_name);

Scenario 2: Preventing Duplicate Rows

Question: How do you prevent inserting duplicate rows into a table?
- Answer:
  - Use a UNIQUE constraint on relevant columns.
  - Example:
```
  sqlCopy codeCREATE TABLE employees (
      emp_id INT PRIMARY KEY,
      emp_name VARCHAR(100) UNIQUE
  );
```

Scenario 3: Joining Data

Question: How do you write a query to fetch employees who haven't placed orders?

Answer:

Use a LEFT JOIN with IS NULL:

  sqlCopy codeSELECT e.emp_id, e.emp_name
  FROM employees e
  LEFT JOIN orders o ON e.emp_id = o.emp_id
  WHERE o.emp_id IS NULL;