# 3. Data Types in SQL | DDL | DML

![](https://cdn.hashnode.com/uploads/covers/65c53497c49d93a3d3eb4afe/853fba8a-38d0-4569-ae56-8c88d0e0d402.png align="center")

![](https://cdn.hashnode.com/uploads/covers/65c53497c49d93a3d3eb4afe/648ccbe3-88c2-4ac6-ad7e-a9b456076a15.png align="center")

Boolean -> `BOOLEAN/BOOL` -> True/False values  
Binary -> BINARY(n) , VARBINARY(n) , BLOB (for files )  
Speacil -> `ENUM`\-> fixed length of list

**DDL ( DATA define language)**  
A category of SQL commands used to **define and modify the structure of database objects** such as tables, schemas, indexes, etc.  
Common DDL commands -> CREATE , ALTER , DROP , TRUNCATE etc

1.  CREATE TABLE
    
    ```javascript
    //syntax
    CREATE TABLE  table_name(
    col1 datatype constrain
    col2 datatype constrain
    ...
    )
    
    //contarinst -> RULES or some checks
    1. PRIMARY KEY
    2. NOT NULL
    3. CHECK : age must be >= 5; age INT CHECK(age >=5)
    4. DEFAULT
    5. UNIQUE
    6. FOREIGN KEY
    ```
    
    ```javascript
    // Example IN mYsql
    CREATE TABLE courses(
      course_id INT AUTO_INCREMENT PRIMARY KEY,
      course_name VARCHAR(100) NOT NULL UNIQUE,
      description TEXT,
      duration INT CHECK(duration > 0),
      start_date DATE NOT NULL,
      end_date DATE,
      CONSTRAINT chk_dates CHECK(end_date IS NULL OR end_date > start_date), -- custom contrain 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
    
    
    //Example in postgres
    CREATE TABLE courses(
      course_id SERIAL PRIMARY KEY, // just add serial
      course_name VARCHAR(100) NOT NULL UNIQUE,
      description TEXT,
      duration INT CHECK(duration > 0),
      start_date DATE NOT NULL,
      end_date DATE,
      CONSTRAINT chk_dates CHECK(end_date IS NULL OR end_date > start_date) -- custom contrain ,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) 
    ```
    
2.  `ALTER` TABLE  
    Used to modify an exsting table - add/remove/modify column  
    (i) Add a coloumn:
    
    ```javascript
    ALTER TABLE courses
    ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
    ```
    

(ii) Rename the coloumn

```javascript
ALTER TABLE courses
RENAME duration TO course_duration;
```

3\. `DROP` TABLE  
Delete the entire table and its data without recovery(no backups)

```javascript
DROP TABLE courses
```

4\. `TRUNCATE` TABLE  
`TRUNCATE` is used to **remove all rows from a table** very quickly.

```javascript
TRUNCATE TABLE students; // now studentds table is empty
```

**PRACTICE PROBLEMS**

```javascript
//Create Table with constarians
CREATE TABLE Employee (
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
department VARCHAR(50),
salary FLOAT
joint_date DATE
is_active BOOLEAN DEFAULT TRUE
);

// aDD eMAIL COL TO THE Employye table 
ALTER TABLE Employee
ADD COLUMN email VARCHAR(50)

// create Departement table and reference it from Employee
CREATE TABLE Department(
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

ALTER TABLE Employee
ADD dept_id INT;
ALTER TABLE Employee
ADD FOREIGN KEY(dept_id) REFERENCES Department(dept_id);
```

**DML (Data Manipulation language)**  
**DML (Data Manipulation Language)** consists of SQL commands used to **insert, update, delete, and retrieve data** from tables.

1.  `INSERT INTO` - used to add new rows
    
    ```javascript
    //SINGLE row syntax
    INSERT INTO table_name(userid , email , col3...)
    VALUES (1, @@@@ , Value3)
    
    //MULTIPLE ROW SYNTAX
    INSERT INTO table_name(userid , email , col3...)VALUES
    (1, @@@@ , Value3),
    (2, @@@@ , Value3),
    (3, @@@@ , Value3);
      
    ```
    
2.  `UPDATE` - Modify Existing Record
    
    ```javascript
    //single value of col update in row 
    UPDATE students
    SET city = 'Delhi'
    WHERE id = 5;
    
    //multiple col value update in row 
    UPDATE students
    SET city = 'Mumbai' name='Mukesh'
    WHERE id =6;
    ```
    
3.  `DELETE` `FROM` \- Used to delete the record(row)
    
    ```javascript
    DELETE FROM students WHERE id = 2;
    // if you cant use WHERE clause here then it delete all the rows
    ```
    
4.  `TRUNCATE` - Delete all rows from table , More efficient than delete cuz it auto increase primary key value  
    
5.  `REPLACE INTO` - Insert or Update in One shot (not available in postgres use UPDATE instead)  
    if that particular id not exist then it created new one otherwise it only updates
    
    ```javascript
    REPLACE INTO students (id , name, city)VALUES //-- must write all coloumns here otherwise they can came as default value only 
    (3, 'Vanshi' ,'Kanpur');
    ```
    

**INTERVIEW QUESTIONS**

**Q: Difference between DELETE, TRUNCATE and DROP?**  
DELETE - Delete single row , Where allowed  
TRUNCATE - DELETE alll rows , Where not allowed  
DROP - DELETE whole table  
  
Q:**Create a table where salary must be greater than 10000.**

```javascript
CREATE TABLE Employee (
id INT PRIMARY KEY , 
name VARCHAR(50) NOT NULL,
salary FLOAT CHECK(salary > 10000) // -> IMP
);
```

**Q:Create a table where course end date must be after start date.**

```javascript
CREATE TABLE Courses(
    start_date DATE,
    end_date DATE,
    CONSTRAINT chk_date CHECK(end_date > start_date)
);
```

  
Q:**What happens if WHERE is missing in UPDATE...set?**  
MySQL goes through **every row** and changes that col value.  

```javascript
//BEFORE

| course_id | course_name | description            | start_date |
| --------- | ----------- | ---------------------- | ---------- |
| 1         | Java        | Core Java Course       | 2026-01-10 |
| 2         | Python      | Python Basics          | 2026-02-15 |
| 3         | DBMS        | Database Management    | 2026-03-20 |
| 4         | MERN        | Full Stack Development | 2026-04-25 |

//AFter 
UPDATE courses
SET course_name = 'CSE';

//OUTPUR
| course_id | course_name | description            | start_date |
| --------- | ----------- | ---------------------- | ---------- |
| 1         | CSE         | Core Java Course       | 2026-01-10 |
| 2         | CSE         | Python Basics          | 2026-02-15 |
| 3         | CSE         | Database Management    | 2026-03-20 |
| 4         | CSE         | Full Stack Development | 2026-04-25 |
```

**Q:TABLE GIVEN ; Cols -> id , name , email , age , salary , city , active**  

```sql
//1. Find employees from Delhi, Mumbai, Jaipur
//age between 25 and 35
//salary > 40000

SELECT * 
FROM Employess
WHERE city IN ('Delhi' , 'Mumbai' , 'Jaipur') // if one city ask then write city = 'Delhi'
AND age BETWEEN 25 AND 35
AND salary > 40000

//2.Find employees: email not provided ,active users only
//highest salary first
SELECT * 
FROM Employess 
WHERE email IS NULL // dont email = NULL
AND active = TRUE
ORDER BY salary DESC
LIMIT 1;

//3. Find second highest salary employee.
SELECT *
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1; // OFFSET 1 means we SKIP 1 row
```

**Q: Whats wrong ?**

```javascript
//1.
SELECT *
FROM Employee
WHERE email = NULL; // <--- this should be email IS NULL
```

**Q: Write SQL query for :**  
**Given Employee table, find:**

*   Active employees
    
*   Age between 25 and 35
    
*   Salary between 50000 and 100000
    
*   City not Delhi and Mumbai
    
*   Email must exist
    
*   Name starts with A
    
*   Sort by salary descending
    
*   Show top 5
    

```sql
SELECT * 
FROM Employee
WHERE active = True
AND age BETWEEN 25 AND 35
AND salary BETWEEN 500000 AND 100000
AND city NOT IN ('Delhi', 'Mumbai')
AND email IS NOT NULL
AND name LIKE "A%"
ORDER BY salary DESC
LIMIT 5;
```

**Q: FROM above Employee Table :  
Deactivate all employees whose salary is less than 60000.**

```sql
UPDATE Employee
SET active = FALSE
WHERE salary < 60000;
```

Q IMP: Create Employee and Department tables. Add proper constraints. Insert sample data. Add a phone column later. Increase salary of Engineering employees by 15%. Delete inactive employees. Finally return the top 3 active employees whose name starts with A, age is between 25 and 35, salary is above 70000, email exists, not from Delhi/Mumbai, and order them by highest salary.
