1-SQL-Command

SQL COMMAND


1. DDL (DATA DEFINITION LANGUAGE)
DDL Commands are used to define and manage database structures, such as tables, indexes, and schemas. 

DDL commands include:
- CREATE : used to create a new database, table, index, or view.
- ALTER : used to modify an existing database, table, index, or view.
- DROP : used to delete an existing database, table, index, or view.
- TRUNCATE : used to delete all records from a table, but keep the structure intact.

2. DML (DATA MANIPULATION LANGUAGE)
DML Commands are used to manage and manipulate data stored inside database tables.

DML commands include:
- INSERT : used to add new records into a table.
- UPDATE : used to modify existing records in a table.
- DELETE : used to remove existing records from a table.

3. DQL (DATA QUERY LANGUAGE)
DQL Commands are used to retrieve data from the database.

DQL commands include:
- SELECT : used to fetch data from one or more tables.

4. DCL (DATA CONTROL LANGUAGE)
DCL Commands are used to control access and permissions in the database.

DCL commands include:
- GRANT : used to give user access privileges to the database.
- REVOKE : used to remove user access privileges from the database.

5. TCL (TRANSACTION CONTROL LANGUAGE)
TCL Commands are used to manage transactions in the database.

TCL commands include:
- COMMIT : used to save all changes made in the current transaction.
- ROLLBACK : used to undo changes made in the current transaction.


-- DDL COMMAND  

CREATE SCHEMA student_DB;

USE student_DB;

CREATE TABLE student_1(
StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Age INT,
Course VARCHAR(50),
EnrollmentID DATE
);

ALTER TABLE student_DB
ADD EmailID VARCHAR(100) UNIQUE;

ALTER TABLE student_DB
MODIFY Age INT NOT NULL;

ALTER TABLE student_DB
DROP COLUMN EmailID;

TRUNCATE TABLE student_DB;

DROP TABLE student_DB;

-- DML COMMAND

USE student_DB;

CREATE TABLE student_DB(
CourseID VARCHAR(50) PRIMARY KEY,
CourseName VARCHAR(100),
DurationMonths INT 
);

INSERT INTO student_DB
(CourseID,CourseName,DurationMonths)
VALUES
("c101","Data Science",06),
("c102","UI/UX",07);


CREATE TABLE student(
StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Age INT,
Course VARCHAR(50),
EnrollmentID DATE
);

INSERT INTO student
(StudentID,Name,Age,Course,EnrollmentID)
VALUES
(1,"Alice Johnson",20,"Data Science","2025-01-06"),
(2,"Bob Smith",22,"Web Developmnet","2025-01-05");


DROP TABLE student_db;
 
 UPDATE student_1
 SET Age=21
 WHERE studentID=1;
 
 
 UPDATE student
 SET Course='UI/UX'
 WHERE studentID=2;
 
 DELETE FROM student_1
 WHERE studentID=3;
 
 -- DQL COMMAND

 SELECT * FROM student;

 SELECT * FROM student_db;
  
 SELECT Name , Course FROM student_1;
 
 SELECT * FROM student_1
 WHERE Age > 20;
 
 
-- DCL COMMAND

GRANT SELECT ON student TO USER 'vedant@localhost';
REVOCKE SELECT ON student FROM USER 'vedant@localhost';


-- TCL COMMAND

BEGIN TRANSACTION;
INSERT INTO student
(studentID,Name,Age,Course,EnrollmentID)
VALUES
(3,"Charlie Brown",21,"Data Science","2025-01-07");