SQL Short Notes

                                                       SQL  Short  Notes

What is SQL?

• sql is stand for structured query language.

• This database language is mainly designed for maintaining the data in relational database management systems.

• sql is standard language for accessing and manipulating database.

Types of SQL Commands:

DDL COMMANDS:

• DTM (Data Defined Languages ) used to change the structure of the table Like creating the table, altering the table & Deleting the table.

• All the commands in the DDL are auto Committed that means it permanently save all the changes in the database.

1. CREATE :

This command is used to create a new database or table.

Syntax:

CREATE TABLE table_name( column1 datatype, column2 datatype, column3 datatype,

Example:

CREATE TABLE Employee

EmployeelD int;

 FirstName varchar(255),

 LastName varchar(255),

 AddressLine varchar(255),

 City varchar(255)

);

 

2. UPDATE :

The UPDATE statement is used to modify the existing records in a table.

Synta:

UPDATE table„name

SET colomn1 = value1, colomn2 = value2, .....

WHARE CustomerlD = 101;

Example:

UPDATE Customers

SET Cant¿tctName = ’iamrupnath', City = ’Kolkata'

 WHERE CustomerlD - 1O1;

3. DELETE :

The DELETE statement is used to delete the existing records in a table. Syntax:

DELETE FROM table_name[WHERE condition];

Example:

DELETE Custome rs wHERE Cunt omerName = ”iamrupnath";

4. TRUNCATE:

A truncate SQL statement is used to remove all rows (complete data) from a table. It is simi\ar to the DELETE statement with no WHERE clause.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Employee;

DML COMONDS:

1. INSERT:

SQL INSERT statement is a SQL query. It is used to insert a single or a multiple records in a table.

Syntax:

INSERT INTO table name

VALUES (value1, value› 1 value3 );

Example:

INSERT INTO STUDENTS (ROLL_NO, NAM E, AGE, CITY)

VALUES (1, iamrupnath, 21, Kolkata)

3. ALTER:

The ALTER TABLE statement in Structured Query Language allows you to add, modify, and delete columns of an existing table.

syntax:

ALTER table name

ADD colum n name datatype;

Example:

ALTER TABLE EMPLOYEE

ADD Email varchar (255);

4. DROP:

The DROP TABLE statement is used to drop an existing table in a database. This command deletes both the structure & Records Stored in the table.

syntax:

DROP TABLE tab\e_name;

Exampe:

DROP TABLE Employee

TCL COMMANDS:

1. COMMIT :

Commits a Transaction. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.

syntax:

COMMIT;

Exampte:

DELETE FROM Student WHERE AGE = 21; COMMIT:

2. ROLLBACK :

If any error occurs with any of the SQL-grouped statements, all changes need to be aborted. The process of reversing changes is called rDllback

Syntax:

ROLLBACK;

Exampte:

DELETE FROM Student WHERE AGE = 21; 

ROLLBACK;

 

DCL COMONDS:

1. GRANT :

It is used to give user access privileges to a database.

Syntax:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, AN0THER_USER;

2. RWOKE :

GRANT SELECT, UPDATE ON MY_TABLE TO SO ME_USER« ANOTH ER_USER;

Syntax:

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER!, USER2;