Basics

Commands

Data Definition Language (DDL)

• a set of statements used to define database objects

Data Control Language (DCL)

• a set of statements used to manage security permissions for users and objects

Data Manipulation Language (DML)

• a set of statements that are used to query or modify data

DDL	DCL	DML
CREATE	GRANT	SELECT
ALTER	REVOKE	INSERT
DROP	DENY	UPDATE
RENAME (T-SQL)		DELETE

Constraints

a set of rules to maintain accuracy, integrity and reliability of a database

Constraints	Description
NOT NULL	Ensures that a column cannot have a NULL value
UNIQUE	Ensures that all values in a column are different
PRIMARY KEY	A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY	Prevents actions that would destroy links between tables. A foreign key is a column in one table that is a primary key in a different table.
CHECK	Ensures that the values in a column satisfies a specific condition
DEFAULT	Sets a default value for a column if no value is specified
CREATE INDEX	Used to create and retrieve data from the database very quickly

CREATE TABLE Person (
    ID int NOT NULL UNIQUE,
    Name varchar(255) NOT NULL,
    Age int,
    Country varchar(255) DEFAULT 'India',

    CHECK (Age >= 60),
    PRIMARY KEY (ID),
);
CREATE TABLE Order (
    O-ID int NOT NULL,
    P-ID int,
    
    PRIMARY KEY (O-ID),
    FOREIGN KEY (P-ID) REFERENCES Person(P-ID)
);

CREATE INDEX P-Index ON Person (ID);

A table with a Composite Key does not have a unique key for each row in the table. Instead a combination of two or more columns serves as a unique identifier for each row.