SQL Short Notes PDF FREE Download

What is SQL?

• SQL stands for Structured Query Language.

• This database language is mainly designed for managing and manipulating the data in relational database management systems(RDBMS).

• SQL is a standardized programming language for accessing and manipulating databases.

• SQL allows users to retrieve specific data from a database using queries.

Types of SQL Commands

• Data Definition Language(DDL)

• CREATE
• ALTER
• DROP
• TRUNCATE

• Data Manipulation Language (DML)

• INSERT
• UPDATE
• DELETE

• Data Control Language (DCL)

• GRANT
• REVOKE

• Transaction Control Language (TCL)

• COMMIT
• ROLLBACK
• SAVEPOINT

1. DDL Commands

• DDL (Data Definition Language) is used to change the structure of the table like creating a table, modifying the table, and managing the structure of a database and its objects.

• All the commands in the DDL are auto committed that means it permanently save all the changes in the database (DB).

• The main purpose of DDL commands is to CREATE and modify the schema of a database.

a). CREATE: The CREATE command is used to create a new database or table.

Syntax

CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype, column4 datatype,..............);

Example

CREATE TABLE students (id int, firstName varchar(20), lastName varchar(20), addressLine varchar(50), city varchar(20));

b). ALTER: The ALTER statement is used to modify an existing database table like adding or deleting columns from an existing table.

Syntax

ALTER TABLE students;

ALTER TABLE table_name ADD column_name datatype;

Example

ALTER TABLE students;

ALTER TABLE students ADD studentEmail varchar(30);

c). DROP: The DROP statement is used to drop an existing table in a database. This command is used to delete both the structure and the records stored in a table.

Syntax

DROP TABLE table_name;

Example

DROP TABLE students;

d). TRUNCATE: A TRUNCATE SQL statement is used to remove all the rows from a table. TRUNCATE is similar to te DELETE statement but with no WHERE clause.

Syntax

TRUNCATE TABLE table_name;

Example

TRUNCATE TABLE students;

2. DML Commands

• DML stands for Data Manipulation Langauge.

• DML commands are used to interact with and manipulate data stored in the database.

• DML focuses on inserting, updating, retrieving, and deleting data within database tables.

a). INSERT: The SQL INSERT statement is used to insert a single or multiple records in a table.

Syntax

INSERT INTO table_name VALUES (value1, value2, value3,.....);

Example

INSERT INTO students (1, 'Reena', 'Kaushik', 'Rohtak', 'Rohtak');

b). UPDATE: The UPDATE table statement is used to modify the existing records (rows) in a table.

Syntax

UPDATE table_name SET column1 = value1, column2 = value2,... WHERE condition;

Example

UPDATE students SET addressLine = 'Bohar' WHERE name = 'Reena';

c). DELETE: The DELETE statement is used to delete existing records in a table without altering the table structure.

Syntax

DELETE FROM table_name [WHERE condition];

Example

DELETE FROM students WHERE name = 'Reena';

3. DCL Commands

• DCL stands for Data Control Language.

• DCL commands are used to control access to data in a database.

• These commands allow database administrators to grant or revoke permissions for uses to perform specific actions on the database.

a). GRANT: This statement is used to give specific permission to users or access privileges to a database. And allowing them to perform some actions like SELECT, INSERT, UPDATE.

Syntax

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

Example

GRANT SELECT, UPDATE ON employees TO user1, user2;

b). REVOKE: It removes previously granted permissions from users or roles preventing them from performing specific actions.

Syntax

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

Example

REVOKE SELECT, UPDATE ON employees FROM user1, user2;

4. TCL Commands

• TCL stands for Transaction Control Language.

• The TCL commands are used to manage and control transactions within a database.

• A transaction is a sequence of one or more SQL operations that are executed as a single unit of work.

a). COMMIT: The COMMIT command permanently saves all the transactions.

Syntax

COMMIT;

Example

DELETE FROM students WHERE age = 20;

COMMIT;

b). 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 rollback.

Syntax

ROLLBACK;

Example

DELETE FROM students WHERE age = 20;

ROLLBACK;

c). SAVEPOINT: SAVEPOINT sets a savepoint within a transaction, and allows partial rollback to a specific point instead of the entire transaction.

Example

SAVEPOINT savepoint1;

SQL Short Notes PDF FREE Download: Download Now