Sources & Credits:
https://www.geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands/
https://www.learncomputerscienceonline.com/database-keys/
What is Structured Query Language, SQL?
We can perform certain operations on the existing database and to create a database.
The Four Categories
1. DDL - Data Definition Language
Consists of commands that can be used to define the database schema.
It is used to create and modify the structure.
It is normally not used by a general user.
CREATE, DROP, ALTER, TRUNCATE, COMMENT, RENAME
2. DML - Data Manipulation Language
It deals with the manipulation of data present in the database.
It controls access to data and to the database.
SELECT, INSERT, UPDATE, DELETE, LOCK
The SELECT command also belongs to DQL.
3. DCL - Data Control Language
It mainly deal with the rights, permissions and other controls of the database system.
GRANT, REVOKE
4. TCL - Transaction Control Language
It used to manage transactions in the database.
It is used to manage the changes made by DML-statement.
It allows statements to be grouped together into logical transactions.
COMMIT, ROLLBACK. SAVEPOINT. SET TRANSACTION
Data Types
1. Binary Data Types
binary : Fixed 8000 bytes max
varbinary : Variable 8000 byte max
image : Variable 2,147,483,647 bytes max
2. Exact Numeric Data Types
bigint : -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
int : -2,147,483,648 to 2,147,483,647
smallint : -32,768 to 32,767
bit : 0 to 1
decimal : -10³⁸+1 to 10³⁸+1
numeric : -10³⁸+1 to 10³⁸+1
3. Approximate Numeric Data Types
float : -1.79E+308 to 1.79E+308
real : -3.40E+38 to 3.40E+38
4. Character String Data Types
char : Fixed 8000 characters max
varchar : Variable 8000 characters max
5. Unicode Character String Data Types
nchar : Fixed 4000 character max
Nvchar : Variable 4000 characters max
6. Data and Time Data Types
datetime : From January 01, 1753 to December 31, 9999
date : Stores a date
time : Stores a time of day
Key
It's an attribute or a group of attribute that can uniquely identify each record in a table.
The keys are important feature of a relational table.
The DBMS access the data field in the table with reference to the table row and column.
Therefore, each record in a table is uniquely identified with the help of a key.
1. Primary Key
It is defined as a database key that is selected by the database administrator or designer as a primary key.
A table can have only one primary key.
Its unique value for each row is used to access each data field in a table with reference to the respective primary key attribute and column name.
2. Foreign Key
When the primary key of one table(Parent) is included as a non-unique attribute into another table(Child) then usch database key is referred as foreign key.
The main purpose is to create and define the relationship between the two tables.
The foreign constrain is an important feature of the relational database design to enforce a referential integrity.
Referential Integrity
- It ensures that for every foreign key field in a child table there must be a corresponding table with same attribute as a primary key.
- It plays an important role in the relational database to ensure the accuracy and the consistency of the data.
