DDL and DML - Mr-JNP/database-in-a-nutshell GitHub Wiki

1. DDL

DDL is short name of Data Definition Language, which deals with database schemas and descriptions, of how the data should reside in the database.

CREATE - to create a database and its objects like (table, index, views, store procedure, function, and triggers)

ALTER - alters the structure of the existing database

DROP - delete objects from the database

1.1. Create a database

-- Syntax
CREATE DATABASE <database_name> [ COLLATE collation_name ]
GO

-- Example
CREATE DATABASE testDB COLLATE THAI_CI_AS
GO

1.2. Delete an existing database

-- Syntax
DROP DATABASE <database_name>
GO

-- Example
DROP DATABASE testDB
GO

1.3. Creating a new table

-- Syntax
CREATE TABLE database_name.schema_name.table_name (
        column_name1 data_type(size),
        column_name2 data_type(size),
        column_name3 data_type(size),
        ...
);

-- Example
CREATE TABLE [dbo].[Students](
	[StudentID] [int] NOT NULL,
	[FirstName] [nchar](20) NULL,
	[LastName] [nchar](20) NULL,
	[NickName] [nchar](20) NULL,
	[Email] [nchar](50) NULL,
	[PhoneNumber] [nchar](10) NULL,
	[Sex] [nchar](10) NULL,
	[BirthDate] [date] NULL
);

1.4. Delete an existing table

-- Syntax
DROP TABLE <table_name>
GO

-- Example
DROP TABLE Staff
GO

1.5. Add new columns

-- Syntax
ALTER TABLE table_name
  ADD column_1 column_definition,
      column_2 column_definition,
      ...
      column_n column_definition;

-- Example
ALTER TABLE employees
  ADD last_name VARCHAR(50),
      first_name VARCHAR(40);

1.6. Modify a column

-- Syntax
ALTER TABLE table_name
  ALTER COLUMN column_name column_type;

-- Example 
ALTER TABLE employees
  ALTER COLUMN last_name VARCHAR(75) NOT NULL;

1.7. Add Primary Key constraint

-- Syntax
ALTER TABLE table_name
ADD PRIMARY KEY (column_name)

-- Example
ALTER TABLE Staff
ADD PRIMARY KEY (StaffID)

1.8. Add Foreign Key constraint

-- Syntax
ALTER TABLE table_name
ADD FOREIGN KEY (column_name_in_the_table)
REFERENCES table_name_containing_PK(column_name_of_PK)

-- Example
ALTER TABLE Staff
ADD FOREIGN KEY (StaffDeptID)
REFERENCES Department(DeptID)

1.9. Primary Key and Foreign Key

-- Syntax
CREATE TABLE table_name
(
     col_name1 datatype1 NOT NULL PRIMARY KEY,
     col_name2 datatype2 NOT NULL,
     col_name3 datatype3 FOREIGN KEY REFERENCES table2(table2_primary_key),
     ...
)

-- Example
CREATE TABLE NewStaff
(
     StaffID int NOT NULL PRIMARY KEY,
     LastName varchar(50) NOT NULL,
     FirstName varchar(50),
     StaffDeptID int FOREIGN KEY REFERENCES Department(DeptID)
)

2. DML

DML is short name of Data Manipulation Language which deals with data manipulation and includes most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to store, modify, retrieve, delete and update data in a database.

SELECT - retrieve data from a database

INSERT - insert data into a table

UPDATE - updates existing data within a table

DELETE - Delete all records from a database table

2.1. Insert a new record into a table

-- Syntax
INSERT INTO table_name VALUES (value1,value2,value3,...);

-- Example
INSERT INTO Staff VALUES (118, 'A', 'B');

2.2. Insert multiple records into a table

-- Syntax
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...),
       (value1,value2,value3,...),
       (value1,value2,value3,...);

INSERT INTO Staff (StaffID, LastName, FirstName) 
VALUES (126, 'X', 'A'),
       (127, 'Y', 'B'),
       (128, 'Z', 'C');

2.3. Update an existing record(s)

-- Syntax
UPDATE table_name
SET column1=value1, column2=value2, ...
WHERE some_column=some_value;

-- Example
UPDATE Staff
SET Address = 'Bangkok'
WHERE StaffID = 16

2.4. Delete an existing record(s)

-- Syntax
DELETE FROM table_name
WHERE some_column = some_value;

-- Example
DELETE FROM Staff WHERE StaffID = 124
⚠️ **GitHub.com Fallback** ⚠️