SQL (structured query language) is the set of instructions used to interact with a relational database System. It is used for storing, manipulating and retrieving data stored in relational database. SQL is the standard language for Relation Database System.
SQL has three major components.
1) Data Definition Language (DDL)
Data Definition Language used to create and modify the structure of database objects in a database. These database objects include views, schemas, tables etc.
Commonly used Data Definition Languages are:
CREATE: This is used to create a new table, It has its own predefined syntax
e.g CREATE customer (customer_id int NOT NULL AUTO_INCREMENT, first_name varchar(255), email varchar(255),address varchar(255), PRIMARY KEY (customer_id) );
ALTER : This command is used to modify existing database table structure
e.g ALTER TABLE customer AUTO_INCREMENT=100
DROP : A drop command deletes a table, index etc
e.g DROP TABLE customer
TRUNCATE : This command is used to empty a table
e.g TRUNCATE TABLE customer
RENAME : This command is used to rename a table
e.g RENAME customer TO customers_info
COMMENT : This statement is used to add a comment about a table or table column .
e.g COMMENT ON COLUMN customer.address IS ‘only customer city name’;
2) Data Manipulation Language (DML)
As the name specify data manipulation language is permitting users to manipulate data in a database .Common Manipulation operation are insert records into table, or need to change some record, or delete some record or perform any other actions on records in the database.
Commonly used Data Manipulation Languages are:
INSERT: INSERT is used to insert data in to database
e.g INSERT TO CUSTOMER(first_name, email, address) VALUES (‘sachin’,’[email protected]’,’Mubai’)
SELECT : SELECT is used to retrive data from database
e.g SELECT * FROM customer
UPDATE : UPDATE is used to updates existing data within a table
e.g UPDATE CUSTOMER SET first_name=’sachin ramesh’ WHERE email=’[email protected]’
DELETE : DELETE is used to DELETE existing data within a table
e.g DELETE FROM CUSTOMER WHERE email=’[email protected]
CALL: call a PL/SQL or Java subprogram
CALL my_procedure(arg1 => 3, arg2 => 4)
LOCK TABLE : concurrency Control
e.g LOCK TABLES customer READ;
3) Data Control Language (DCL)
Data Control Language command is used to control access to data stored in a database. It is used to decide who can view this table and what kind of access like read, write, delete should be given to the other users.
Commonly used Data Control Languages are:
GRANT : GRANT provides the privileges to the users on the database.
GRANT INSERT ON customer TO vijay;
REVOKE : Revoke is used to cancel previously granted or denied permissions
e.g REVOKE INSERT ON customer FROM vijay;