STRUCTURED QUERY LANGUAGE | DBMS | DQL,DDL,DML,DCL

SQL, which is an abbreviation for Structured Query Language, is a language used to request data from a database, to add, update, or remove data within a database, or to manipulate the metadata of the database.

SQL is generally pronounced as the three letters in the name, e.g. ess-cue-ell, or in some people's usage, as the word sequel.

SQL is a declarative language in which the expected result or operation is given without the specific details about how to accomplish the task. The steps required to execute SQL statements are handled transparently by the SQL database. Sometimes SQL is characterized as non-procedural because procedural languages generally require the details of the operations to be specified, such as opening and closing tables, loading and searching indexes, or flushing buffers and writing data to file systems. Therefore, SQL is considered to be designed at a higher conceptual level of operation than procedural languages because the lower level logical and physical operations aren't specified and are determined by the SQL engine or server process that executes it.

Instructions are given in the form of statements, consisting of a specific SQL statement and additional parameters and operands that apply to that statement. SQL statements and their modifiers are based upon official SQL standards and certain extensions to that each database provider implements. Commonly used statements are grouped into the following categories:

Data Definition Language (DDL)

• CREATE
• ALTER
• DROP
• TRUNCATE

CREATE :
Used to create a new table, a view of a table, or other object in database.

Example : create table test (id int null, name varchar(20));

Constraints:

Constraints are the rules enforced on data columns on table. These 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 database.

Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.

Domain integrity constraint :Domain integrity Constraints are the rules enforced on a particular row of a table.
Not Null Constraint : Ensures that a column cannot have NULL value.
Default Constraint : Provides a default value for a column when none is specified.
Check Constraint : The CHECK constraint ensures that all values in a column satisfy certain conditions.
Entity integrity constraint :Constraints are the rules enforced on data columns on table

• Unique Constraint : Ensures that all values in a column are different. 
• Primary Key : Uniquely identified each rows/records in a database table 

Referential integrity constraint : It relates two or more tables
Foreign Key: Uniquely identified a rows/records in any another database table.

ALTER :

ALTER - Used to modify an existing database object, such as a table.
Example : Alter table test add d_id int;

DROP :
DROP - Used to delete an entire table, a view of a table or other object in the database.the structure remains 
Example : Drop table test;

TRUNCATE :
TRUNCATE - Used to delete an entire table, a view of a table or other object in the database with structure 
Example : truncate table test;

Data Manipulation Language (DML)

• INSERT
• UPDATE
• DELETE

INSERT :
INSERT - Used to create a record. 
Example :

• To insert values for every columns 

Insert into test values(1,’cse’,1)

• To insert values for selected columns 

Insert into test(id,name) values(2,’it’); 

UPDATE :
UPDATE - Used to change certain records.
Example :

• To Update with particular condition 

update test set id = 3 where id =2;

• To update every rows of the table without condition 

update test set d_id = d_id * 1 

DELETE :
DELETE - Used to delete certain records
Example :

• To delete a particular row 

Delete from test where d_id =1;

• To delete every data of a table 

Delete from test

Data Control Language (DCL)

GRANT - Used to give a privilege to someone. 

REVOKE - Used to take back privileges granted to someone.

Data Query Language (DQL)

SELECT - Used to retrieve certain records from one or more tables.