DDL Commands
Data Definition Commands is used to do structural changes to a database.
CREATE TABLE
This command is used to create a new table in the database.
CREATE TABLE Students (
SID INT PRIMARY KEY,
FName VARCHAR(50),
LName VARCHAR(50),
BirthDate DATE,
Email VARCHAR(100)
);ALTER TABLE
This command is used to add, delete/drop or modify columns in an existing table.
ALTER TABLE Students
ADD Email VARCHAR(100);DROP TABLE
This command is used to delete a table and all rows in the table.
DROP TABLE Students;DML Commands
DML Commands are used to manipulate data in a database.
INSERT
This command is used to insert new data into a table.
INSERT INTO Students (SID, FName, LName, BirthDate, Email)
VALUES
(1, 'John', 'Doe', '2000-01-01', 'john.doe@example.com'),
(2, 'Jane', 'Smith', '2001-02-02', 'jane.smith@example.com'),
(3, 'Bob', 'Johnson', '2002-03-03', 'bob.johnson@example.com');After inserting data into the Students table, if you run the SELECT statement, you would get:
| SID | FName | LName | BirthDate | |
|---|---|---|---|---|
| 1 | John | Doe | 2000-01-01 | john.doe@example.com |
| 2 | Jane | Smith | 2001-02-02 | jane.smith@example.com |
| 3 | Bob | Johnson | 2002-03-03 | bob.johnson@example.com |
UPDATE
This command is used to modify the existing records in a table.
UPDATE Students
SET Email = 'john.doe@example.com'
WHERE SID = 1;DELETE
This command is used to delete existing records in a table.
DELETE FROM Students
WHERE SID = 1;SELECT
This command is used to select data from a database. The data returned is stored in a result table, called the result-set.
SELECT * FROM Students;INNER JOIN
This command is used to combine rows from two or more tables, based on a related column between them.
We use Students table with Grades table for this SQL statement. The Grades table is as follows:
| StdID | Course | Score |
| 1 | Math | 85 |
| 2 | Math | 90 |
| 3 | Math | 95 |
SELECT Students.FName, Students.LName, Grades.Score
FROM Students
INNER JOIN Grades ON Students.SID = Grades.StdID;Result of the above query:
| FName | LName | Score |
| John | Doe | 85 |
| Jane | Smith | 90 |
| Bob | Johnson | 95 |