3. Data Types in SQL | DDL | DML

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

   //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
   

   // 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:

   ALTER TABLE courses
   ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
   

(ii) Rename the coloumn

ALTER TABLE courses
RENAME duration TO course_duration;

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

DROP TABLE courses

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

TRUNCATE TABLE students; // now studentds table is empty

PRACTICE PROBLEMS

//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

   //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

   //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)

   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

   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.

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.

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.

//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

//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 ?

//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

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.

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.