SQL Notes
SQL Notes

SQL Notes

Tags
SQL
Published
February 16, 2022
This are all basic SQL query, irrespective to database

What is SQL?

DDL Statements

DDL stands for Data Definition Language. It is used define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. DDL is a set of SQL commands used to create, modify, and delete database structures but doesn’t manipulated any data.

For example:

Command
Description
CREATE
Creates a new table, a view of a table, or other object in the database.
ALTER
Modifies an existing database object, such as a table.
DROP
Deletes an entire table, a view of a table or other objects in the database.

DML Statements

DML stands for Data Manipulation Language. It is used to add, change, and delete the content of a database.
Command
Description
SELECT
Retrieves certain records from one or more tables.
INSERT
Creates a record.
UPDATE
Modifies records
DELETE
Deletes records

DCL Statement

DCL stands for Data control language. It is used to access the stored data. It is mainly used for revoke and to grant the user the required access to a database.
Command
Description
GRANT
This command gives users access privileges to the database.
REVOKE
This command withdraws the user’s access privileges given by using the GRANT command.

Data Types

String Data Types

Data Type
Description
CHAR(x)
can store characters of fixed length (max 8000 chars)
VARCHAR(x)
can store characters up to given length (max 8000 chars)
TEXT
can store characters up to 2GB size
IMAGE
can store binary string up to 2 GB size

Integer Data Types

Data Type
Description
BIT
can store single bit (0 or 1) or NULL
TINYINT
can store numbers from 0 to 255
SMALLINT
can store numbers between -32,768 to 32,767
INT
can store numbers between -2,147,483,648 and 2,147,483,647
BIGINT
can store numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807

Date and Time Data Types

Data Type
Description
DATETIME
can store date from January 1, 1753 to December 31, 9999 with time
DATETIME2
can store date from January 1, 0001 to December 31, 9999 with time
DATE
can store only date from January 1, 0001 to December 31, 9999

Creating:

Database

DESCRIBE databaseName; OR DESC databaseName;

Tables

CREATE TABLE tableName ( columnName <DATATYPE> <CONSTRAINTS>, columnName <DATATYPE> <CONSTRAINTS>, columnName <DATATYPE> <CONSTRAINTS>, );

Column Constraints

PRIMARY KEY

A Primary Key is a unique identifier within a table or database that is used to identify each individual record within a table. It is a field or combination of fields that uniquely identify a record within a database table, and it is usually a single field.

CANDIDATE KEY

A Candidate Key is a column or set of columns in a database table that can uniquely identify each row in a table. A Candidate Key can be used as a Primary Key, or it can be used as an Alternate or Secondary Key.

SUPER KEY

A Super Key is a set of one or more columns in a database table that can uniquely identify each row in a table. A Super Key can be a Primary Key, a Candidate Key, an Alternate Key, or a Secondary Key.

ALTERNATE KEY

An Alternate Key is a column or combination of columns in a database table that uniquely identify each row in a table. An Alternate Key can be used as a Primary Key, or it can be used as a Secondary Key.

FOREIGN KEY

A Foreign Key is a column or set of columns in a database table that is used to identify a record in another table. A Foreign Key is used to link two tables together and establish a relationship between them.

SURROGATE KEY

A Surrogate Key is an artificial or synthetic key that is assigned to a record in a database table. A Surrogate Key is usually a numeric or alphanumeric field that is used as a Primary Key or an Alternate Key.

NATURAL KEY

A Natural Key is a column or set of columns in a database table that is used to identify each record within a table. A Natural Key is usually a combination of fields that already exist in the table, such as a combination of a person's first name, last name, and date of birth.

SIMPLE KEY

A Simple Key is a column or set of columns in a database table that is used to identify each record within a table. A Simple Key is usually a single field that is used as a Primary Key or an Alternate Key.

COMPOSITE KEY

A Composite Key is a column or set of columns in a database table that is used to identify each record within a table. A Composite Key is usually a combination of fields that are used as a Primary Key or an Alternate Key.

COMPOUND KEY

A Compound Key is a column or set of columns in a database table that is used to identify each record within a table. A Compound Key is usually a combination of two or more fields that are used as a Primary Key or an Alternate Key.

INTELLIGENT KEY

An Intelligent Key is a column or set of columns in a database table that is used to identify each record within a table. An Intelligent Key is usually a combination of fields that are used as a Primary Key or an Alternate Key, and it is generated by an intelligent algorithm that takes into account the context and other factors to create a unique identifier for each record.

Commit & Rollback

Commit

The COMMIT command is a critical part of the transactional process, and is used to save all the changes that have been executed within a given transaction to the database. It is an essential component of any database transaction, as it ensures that all the changes that were made to the database during the transaction are securely stored and will not be lost. Without the COMMIT command, any changes made during a transaction are not considered permanent and could potentially be lost, leading to a much less secure database system. Therefore, it is important to remember to invoke the COMMIT command whenever changes are made to the database, in order to ensure that they are securely stored.
COMMIT;

Rollback

The ROLLBACK command is the transactional command used to undo any transaction that has not yet been saved to the database. This command is used to reverse any changes made since the last COMMIT or ROLLBACK command was issued and is a convenient way to undo any mistakes or unwanted changes that have been made. This command is extremely important in ensuring the integrity of a database, as it ensures that all transactions that have been made are reversible, and that any changes made are reversible should the need arise.
ROLLBACK;