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.
