SQL Notes

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.

Joins

`INNER JOIN`

`LEFT JOIN`

`RIGHT JOIN`

`FULL JOIN`

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;

🔗

Practicals