3 - SQL Starting(Introduction, Data-Types,Constraints and Command-Groups)

 

What is SQL?

In simple words,SQL(Structured Query Language) is a programming language specifically defined for working with data-base to 

--> CREATE

--> MANIPULATE

--> SHARE/ACCESS

or

SQL stands for Structured Query Language is used for storing and managing data in relational database management system (RDMS).

 It is a standard language for Relational Database System. It enables a user to create, read, update and delete relational databases and tables.

SQL Data Types

Data types are used to represent the nature of the data that can be stored in the database table.

For example, in a particular column of a table, if we want to store a string type of data then-->

 we will have to declare a string data type of this column.

Data types mainly classified into three categories for every database.

--> String Data types

--> Numeric Data types

--> Date and time Data types

1- String               - char , varchar , text

2- Numeric          - bit , tinyint , Smallint , Int , bigint , float ,  real , money

3-Date/Time        - Date ,Time ,DateTime ,TimeStamp ,Year

4-Miscellanious  - JSON, XML

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

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

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

SQL | DDL, DQL, DML, DCL and TCL Commands

Structured Query Language(SQL) as we all know is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database.

These SQL commands are mainly categorized into four categories as: 

--> DDL – Data Definition Language

--> DQl – Data Query Language

--> DML – Data Manipulation Language

--> DCL – Data Control Language

DDL (Data Definition Language): 

DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema.

List of DDL commands: 

CREATE:   This command is used to create the database or its   objects(like table, index, function, views, store  procedure,and triggers).

DROP:     This command is used to delete objects from the             database.

ALTER:    This is used to alter the structure of the database.

TRUNCATE:    This is used to remove all records from a table, including all spaces allocated for the records are   removed.

COMMENT:  This is used to add comments to the data dictionary.

RENAME:   This is used to rename an object existing in the      database.

DML(Data Manipulation Language): 

The SQL commands that deals with the manipulation of data present in the database belong to DML or Data Manipulation Language 

and this includes most of the SQL statements. 

List of DML commands: 

INSERT : It is used to insert data into a table.

UPDATE : It is used to update existing data within a table.

DELETE : It is used to delete records from a database table.

LOCK   : Table control concurrency.

CALL   : Call a PL/SQL or JAVA subprogram.

EXPLAIN PLAN: It describes the access path to data.

DCL (Data Control Language): 

DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system. 

List of  DCL commands: 

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.

TCL(Transaction Control Language):

TCL commands deal with the transaction within the database. 

List of TCL commands: 

 COMMIT:     Commits a Transaction.

ROLLBACK:  Rollbacks a transaction in case of any error occurs.

SAVEPOINT:  Sets a savepoint within a transaction.

SET TRANSACTION:   Specify characteristics for the transaction.