SQL

🧠 Day 5: SQL Commands, Data Types, and Table Operations (DDL, DML, TCL, DQL)

Photo of Mathimaa Mathimaa3 weeks ago
0 0 2 minutes read

🧠 Day 5: SQL Commands, Data Types, and Table Operations (DDL, DML, TCL, DQL)

🏗️ SQL Command Categories

SQL commands are grouped based on their purpose:

Type Full Form Purpose
DDL Data Definition Language Defines and manages database structure
DML Data Manipulation Language Handles data inside tables
TCL Transaction Control Language Manages transactions and data integrity
DQL Data Query Language Retrieves data from tables

⚙️ 1️⃣ DDL – Data Definition Language

Used to define or change table structure.

Command Description
CREATE Creates new tables or databases
ALTER Modifies existing table structure
RENAME Changes table name
TRUNCATE Removes all records but keeps table structure
DROP Deletes table along with structure

🔹 Syntax:

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
...
);
ALTER TABLE table_name ADD column_name datatype;
RENAME old_table_name TO new_table_name;
TRUNCATE TABLE table_name;
DROP TABLE table_name;

✏️ 2️⃣ DML – Data Manipulation Language

Used to add, modify, or remove records.

Command Description
INSERT Adds new records
UPDATE Modifies existing records
DELETE Removes specific records

🔹 Syntax:

INSERT INTO table_name VALUES (value1, value2, ...);
UPDATE table_name SET column1 = value1 WHERE condition;
DELETE FROM table_name WHERE condition;

🔹 Multi-row Insert:

INSERT ALL
INTO class VALUES (100, 'John', 10000)
INTO class VALUES (200, 'James', 20000)
INTO class VALUES (300, 'Sundar', 30000)
SELECT * FROM dual;

🔒 3️⃣ TCL – Transaction Control Language

Used to manage database transactions.

Command Description
COMMIT Saves changes permanently
ROLLBACK Reverts to the last commit or savepoint
SAVEPOINT Creates a checkpoint within a transaction

🔹 Syntax:

COMMIT;
ROLLBACK;
SAVEPOINT sp_day1;

Example Workflow:

INSERT INTO class VALUES (400, 'Ravi', 25000);
SAVEPOINT sp_day1;

UPDATE class SET fee = 30000 WHERE student_id = 400;
SAVEPOINT sp_day2;

DELETE FROM class WHERE student_id = 300;
ROLLBACK TO sp_day1;

🔍 4️⃣ DQL – Data Query Language

Only one key command — SELECT — used to query data.

SELECT * FROM employees;
SELECT first_name, salary FROM employees WHERE salary > 10000;

🧩 SQL Data Types

Data Type Description
NUMBER (INT, FLOAT, DECIMAL) Numeric values
CHAR(n) Fixed-length string (static memory)
VARCHAR(n) Variable-length string (dynamic memory)
LONG Up to 35,000 characters
DATE Stores date and time
BLOB Binary Large Object (e.g., images, files)
CLOB Character Large Object (text data)

🧮 Table Example

CREATE TABLE Class (
Student_ID NUMBER,
SName VARCHAR(30),
Fee NUMBER
);

INSERT INTO Class VALUES (100, 'John', 10000);
INSERT INTO Class VALUES (200, 'James', 20000);
INSERT INTO Class VALUES (300, 'Sundar', 30000);

SELECT * FROM Class;
DESC Class;

🔁 Difference Between DELETE, TRUNCATE, and DROP

Feature DELETE TRUNCATE DROP
Command Type DML DDL DDL
Removes Specific rows All rows (keeps structure) Table + structure
WHERE Clause Allowed Not allowed Not applicable
Rollback ✅ Possible ❌ Not possible ❌ Not possible
Speed Slower Faster Fastest (permanent)

🔹 Syntax:

DELETE FROM table_name WHERE condition;
TRUNCATE TABLE table_name;
DROP TABLE table_name;

✅ Quick Recap

  • DDL: Create and modify database objects

  • DML: Insert, update, delete data

  • TCL: Manage transactions (Commit, Rollback, Savepoint)

  • DQL: Query and retrieve data

  • Data Types: Number, Char, Varchar, Long, Date, Blob, Clob

Photo of Mathimaa Mathimaa3 weeks ago
0 0 2 minutes read

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button