1.

Create Table

The CREATE TABLE command is used to create a new table in the database.

sql

CREATE TABLE table_name (

column1 datatype [constraint],
column2 datatype [constraint],
...
);

Example:

sql

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
HireDate DATE
);

2. Alter Table

The ALTER TABLE command is used to modify an existing table, like adding, deleting, or
modifying columns.

sql

ALTER TABLE table_name

ADD column_name datatype;

Example (add a column):

sql

ALTER TABLE Employees

ADD Email VARCHAR(100);

To modify a column:

sql

ALTER TABLE Employees

MODIFY COLUMN LastName VARCHAR(100);

To drop a column:

sql
ALTER TABLE Employees
DROP COLUMN Email;

3. Drop Table

The DROP TABLE command is used to delete an existing table and all of its data.

sql

DROP TABLE table_name;

Example:

sql

DROP TABLE Employees;

4. Create Index

The CREATE INDEX command is used to create an index on one or more columns of a table to
improve query performance.

sql

CREATE INDEX index_name

ON table_name (column1, column2, ...);

Example:

sql

CREATE INDEX idx_lastname

ON Employees (LastName);

5. Drop Index

The DROP INDEX command is used to delete an existing index.

sql

DROP INDEX index_name;

Example:

sql

DROP INDEX idx_lastname;

6. Create Database
The CREATE DATABASE command is used to create a new database.

sql

CREATE DATABASE database_name;

Example:

sql

CREATE DATABASE CompanyDB;

7. Alter Database

The ALTER DATABASE command is used to modify a database's properties (like changing the
name or altering its settings).

sql

ALTER DATABASE database_name

MODIFY property;

Example (changing the database collation):

sql

ALTER DATABASE CompanyDB

COLLATE Latin1_General_CI_AS;

8. Drop Database

The DROP DATABASE command is used to delete an existing database and all of its contents.

sql

DROP DATABASE database_name;

Example:

sql

DROP DATABASE CompanyDB;

9. To rename table.
• Syntax: ALTER TABLE table_name RENAME TO new_table_name;
• Columns can also be given a new name with the use of ALTER TABLE.

• ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Here’s a breakdown of the difference between a database, a table, and an index in SQL:

1. Database

A database is a collection of related data stored in an organized manner. It is the highest-level

structure in a relational database management system (RDBMS) and serves as a container for
storing tables, views, indexes, and other objects.

 Purpose: It stores data and allows for management, querying, and security of data.
 Components: It can contain multiple tables, views, indexes, stored procedures,
triggers, etc.
 Example: CompanyDB, CustomerDB, etc.

Example of Creating a Database:

sql

CREATE DATABASE CompanyDB;

2. Table

A table is a collection of rows and columns used to store data in a structured format. A table is a
fundamental unit of storage within a database.

 Purpose: It holds the actual data for an application, organized into rows and columns.
Each table corresponds to an entity (like "employees" or "orders") in the system.
 Structure: Tables have columns (fields) that define the type of data each row (record)
can store.
 Example: A table called Employees might have columns like EmployeeID, FirstName,
LastName, and HireDate.

Example of Creating a Table:

sql

CREATE TABLE Employees (

EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
HireDate DATE
);

3. Index

An index is a database object that improves the speed of data retrieval operations on a table. It is
used to quickly locate rows based on the values in one or more columns of a table.
 Purpose: Indexes speed up query processing by allowing the database to quickly locate
data without having to scan the entire table. They work like an index in a book—helping
you find specific data more efficiently.
 Structure: It is built on one or more columns of a table and works by creating a sorted
data structure (typically a B-tree) that allows fast searching.
 Example: An index on the LastName column of the Employees table could speed up
queries filtering by last name.

Example of Creating an Index:

sql

CREATE INDEX idx_lastname

ON Employees (LastName);

Key Differences:

Aspect Database Table Index

Stores and organizes all Stores the actual data in rows Speeds up data retrieval
Purpose
data and columns queries
Organized into rows and Organized in a sorted data
Structure Contains tables, views, etc.
columns structure (e.g., B-tree)
Does not store actual data; Does not store data; just a
Data Stores actual data records
just holds tables and objects pointer for faster access
Stores data of a specific
High-level container for Optimizes performance for
Usage entity (e.g., employees,
data read operations
orders)

In short:

 Database = A container for data.

 Table = The actual structure where data is stored.
 Index = A tool to speed up searching data in a table.

1. SELECT

The SELECT statement is used to retrieve data from one or more tables in a database. It is the
most commonly used query in SQL to fetch records.

 Purpose: To query and retrieve data.

 Syntax:

sql

SELECT column1, column2, ...

FROM table_name
WHERE condition;
Example:

sql

SELECT FirstName, LastName FROM Employees WHERE HireDate > '2020-01-01';

2. INSERT INTO

The INSERT INTO statement is used to add new records (rows) into a table.

 Purpose: To insert new rows into a table.

 Syntax:

sql

INSERT INTO table_name (column1, column2, ...)

VALUES (value1, value2, ...);

Example:

sql

INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)

VALUES (101, 'John', 'Doe', '2024-03-15');

3. UPDATE

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

 Purpose: To update existing rows in a table with new values.

 Syntax:

sql

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

Example:

sql

UPDATE Employees
SET LastName = 'Smith'
WHERE EmployeeID = 101;

4. DELETE FROM

The DELETE FROM statement is used to remove records from a table.

 Purpose: To delete one or more rows from a table.
 Syntax:

sql

DELETE FROM table_name

WHERE condition;

Example:

sql

DELETE FROM Employees

WHERE EmployeeID = 101;

5. MERGE INTO

The MERGE INTO statement is used for merging two tables by updating, inserting, or deleting data
based on conditions. It’s often called an "upsert" operation (update or insert).

 Purpose: To synchronize two tables, performing insert, update, or delete operations in

one step.
 Syntax:

sql

MERGE INTO target_table AS T

USING source_table AS S
ON (T.column = S.column)
WHEN MATCHED THEN
UPDATE SET T.column1 = S.column1
WHEN NOT MATCHED THEN
INSERT (column1, column2) VALUES (S.column1, S.column2);

Example:

sql

MERGE INTO Employees AS E

USING TempEmployees AS T
ON E.EmployeeID = T.EmployeeID
WHEN MATCHED THEN
UPDATE SET E.FirstName = T.FirstName
WHEN NOT MATCHED THEN
INSERT (EmployeeID, FirstName, LastName)
VALUES (T.EmployeeID, T.FirstName, T.LastName);

6. TRUNCATE TABLE
The TRUNCATE TABLE statement is used to remove all rows from a table quickly and efficiently.
Unlike DELETE, TRUNCATE does not log individual row deletions and cannot be rolled back (in
most cases).

 Purpose: To delete all rows from a table without affecting the structure of the table.
 Syntax:

sql

TRUNCATE TABLE table_name;

Example:

sql

TRUNCATE TABLE Employees;

Summary of the Commands:

Command Purpose Example Usage

SELECT * FROM Employees WHERE
SELECT Retrieves data from tables HireDate > '2020-01-01';
INSERT INTO Employees (EmployeeID,
INSERT INTO Adds new rows to a table FirstName) VALUES (101, 'John');
UPDATE Employees SET LastName =
UPDATE Modifies existing records 'Smith' WHERE EmployeeID = 101;
DELETE DELETE FROM Employees WHERE
Deletes records from a table EmployeeID = 101;
FROM
MERGE INTO Employees USING
Synchronizes two tables (insert, TempEmployees ON E.EmployeeID =
MERGE INTO
update, delete) T.EmployeeID;
TRUNCATE Removes all rows from a table TRUNCATE TABLE Employees;
TABLE (no table structure change)

GRANT Command:

The GRANT command is used to give specific privileges (permissions) to users or roles for
database objects.

Syntax:

sql

GRANT privilege_type ON object TO user;

 privilege_type is the type of permission (e.g., SELECT, INSERT, UPDATE, DELETE).

 object is the database object (e.g., a table, view, etc.).
 user is the database user or role to which the privileges are granted.

Example:

sql

GRANT SELECT, INSERT ON employees TO john;

This gives the user john permission to select and insert data into the employees table.

REVOKE Command:

The REVOKE command is used to remove specific privileges that have been previously granted to
a user or role.

Syntax:

sql

REVOKE privilege_type ON object FROM user;

 privilege_type is the type of permission (e.g., SELECT, INSERT, UPDATE, DELETE).

 object is the database object (e.g., a table, view, etc.).
 user is the database user or role whose privileges are being revoked.

Example:

sql

REVOKE INSERT ON employees FROM john;

This removes the INSERT privilege for the user john on the employees table.

COMMIT:

The COMMIT command is used to save all the changes made during the current transaction to the
database. Once a transaction is committed, all the operations within it (e.g., inserts, updates,
deletes) are permanently stored in the database, and the transaction is complete.

Syntax:

sql

COMMIT;

Example:
sql

BEGIN TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE department = 'HR';
COMMIT;

In this example, the UPDATE statement is applied to the employees table, and the changes are
committed, making them permanent in the database.

2. ROLLBACK:

The ROLLBACK command is used to undo all changes made during the current transaction. If a
transaction fails or you want to discard all the changes made in the transaction, you can use
ROLLBACK. This restores the database to the state it was in before the transaction began.

Syntax:

sql

ROLLBACK;

Example:

sql

BEGIN TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE department = 'HR';
ROLLBACK;

In this example, the UPDATE statement changes the employees table, but the ROLLBACK command
undoes these changes, so no permanent modification occurs.

3. SAVEPOINT:

A SAVEPOINT allows you to set a point within a transaction to which you can later ROLLBACK. It’s
useful when you want to partially undo a transaction. You can roll back to a specific savepoint
instead of rolling back the entire transaction.

Syntax:

sql

SAVEPOINT savepoint_name;

To rollback to a specific savepoint:

sql

ROLLBACK TO SAVEPOINT savepoint_name;

Example:

sql

BEGIN TRANSACTION;
UPDATE employees SET salary = salary + 1000 WHERE department = 'HR';
SAVEPOINT before_update;
UPDATE employees SET salary = salary - 500 WHERE department = 'HR';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;

In this example:

1. The first update is made to the employees table.

2. A savepoint is created after that update (before_update).
3. A second update is made to undo some of the salary changes.
4. The ROLLBACK TO SAVEPOINT undoes the second update (reverting back to the state after
the first update).
5. The transaction is then committed, making the first update permanent.