7.

INDEX

Used to speed up searches in a table.

Creating an Index:

CREATE INDEX lastname ON Persons (LastName);

This creates an index on the LastName column in the Persons table.

Creating a Unique Index:

CREATE UNIQUE INDEX index_name ON table_name (column1, column2);

This prevents duplicate values in the indexed column.

Removing an Index:

ALTER TABLE table_name DROP INDEX index_name;

Deletes an index from the table.

When to Avoid Indexes?

• On small tables.

• When frequent, large updates or inserts occur.

• On columns with many NULL values.

• On columns that are frequently modified.

SQL FUNDAMENTALS

Structured Query Language (SQL) is used for storing and managing data in relational database
management systems (RDBMS). Almost all RDBMS (MySQL, Oracle, MS Access, etc.) use SQL. It is case-
insensitive and helps in performing different operations on databases.

SQL commands are divided into the following categories:

• Data Definition Language (DDL)

• Data Manipulation Language (DML)

• Data Control Language (DCL)

• Data Query Language (DQL)

1. Data Definition Language (DDL)

It creates a database or table to store data.

Syntax:
CREATE DATABASE database_name;

CREATE TABLE table_name (

column1 datatype,

column2 datatype,

...

);

Example:

CREATE DATABASE SchoolDB;

CREATE TABLE Student (

student_id INT PRIMARY KEY,

name VARCHAR(100),

age INT,

dept_name VARCHAR(50)

);

2. DROP – Deletes a table or database permanently

It removes an entire database or table, including its structure and data.

Syntax:

DROP TABLE table_name;

DROP DATABASE database_name;

Example:

DROP TABLE Student;

DROP DATABASE SchoolDB;

3. TRUNCATE – Deletes all records from a table but keeps structure

Unlike DROP, it removes only the data, keeping the table intact.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Student;

4. RENAME – Renames an existing table

It changes the name of a table.

Syntax:

RENAME TABLE old_table_name TO new_table_name;

Example:
RENAME TABLE Student TO Student_Info;

5. ALTER – Modifies an existing table structure

It is used to add, delete, or modify columns in a table.

Syntax & Examples:

• Add a new column:

ALTER TABLE Student ADD address VARCHAR(200);

• Rename a column:

ALTER TABLE Student RENAME COLUMN address TO location;

• Modify a column datatype:

ALTER TABLE Student MODIFY name VARCHAR(300);

• Delete a column:

ALTER TABLE Student DROP COLUMN address;

• Add a constraint:

ALTER TABLE Student ADD CONSTRAINT chk_age CHECK (age >= 18);

2. Data Manipulation Language (DML)

It is used to insert new data into a table.

Syntax:

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

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Example:

INSERT INTO Student VALUES (101, 'Adam', 15, 'CSE');

INSERT INTO Student (student_id, name) VALUES (102, 'Alex');

2. UPDATE – Modifies existing records in a table

It updates one or more rows based on a condition.

Syntax:

UPDATE table_name SET column_name = new_value WHERE condition;

Example:

UPDATE Student SET age = 18 WHERE student_id = 102;

UPDATE Student SET name = 'Abhi', age = 17 WHERE student_id = 103;

3. DELETE – Removes records from a table

It deletes specific records from a table based on a condition.

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM Student WHERE student_id = 103;

4. MERGE – Combines rows from two tables based on a condition

It is used to insert or update records in a table based on data from another table.

Syntax:

MERGE INTO target_table

USING source_table

ON (condition)

WHEN MATCHED THEN

UPDATE SET column_name = value

WHEN NOT MATCHED THEN

INSERT (column1, column2) VALUES (value1, value2);

Example:

MERGE INTO Student

USING NewStudents

ON (Student.student_id = NewStudents.student_id)

WHEN MATCHED THEN

UPDATE SET name = NewStudents.name, age = NewStudents.age

WHEN NOT MATCHED THEN

INSERT (student_id, name, age) VALUES (NewStudents.student_id, NewStudents.name,

5. CALL – Invokes a stored procedure

It is used to execute a stored procedure.

Syntax:

CALL procedure_name(parameter1, parameter2, ...);

Example:

CALL UpdateStudentAge(101, 16);

3 .Data Control Language (DCL)

It allows a user to perform specific actions like SELECT, INSERT, DELETE, etc.
Syntax:

GRANT privilege_name ON object_name TO user;

Example:

GRANT SELECT, UPDATE ON Student TO Rahul;

GRANT CREATE TABLE TO user_name;

2. REVOKE – Removes access from users

It withdraws privileges given to a user.

Syntax:

REVOKE privilege_name ON object_name FROM user;

Example:

REVOKE DELETE ON Student FROM Rahul;

3. DENY – Explicitly denies access to users

It prevents a user from performing specific actions.

Syntax:

DENY privilege_name ON object_name TO user;

Example:

DENY INSERT ON Student TO Rahul;

4. SET PASSWORD – Changes the password for a user

It is used to update the password for a database user.

Syntax:

SET PASSWORD FOR user = PASSWORD('new_password');

Example:

SET PASSWORD FOR 'Rahul' = PASSWORD('new_password');

5. CREATE ROLE – Defines a new role

It creates a role that can be assigned to users to manage permissions.

Syntax:

CREATE ROLE role_name;

Example:

CREATE ROLE admin_role;

4. Data Query Language (DQL)

It retrieves data from one or more tables based on conditions.

SELECT column1, column2 FROM table_name;

SELECT * FROM table_name;

SELECT * FROM table_name WHERE condition;

Example:

• Retrieve specific columns:

SELECT student_id, name, age FROM Student;

• Retrieve all records:

SELECT * FROM Student;

• Retrieve records based on a condition:

SELECT * FROM Student WHERE name = 'Abhi';

2. EXPLAIN – Displays the execution plan of a query

It provides information about how a query will be executed.

Syntax:

EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

Example:

EXPLAIN SELECT * FROM Student WHERE age > 18;

3. DESCRIBE – Shows the structure of a table

It provides information about the columns in a table.

Syntax:

DESCRIBE table_name;

Example:

DESCRIBE Student;

4. SHOW – Displays information about databases, tables, or columns

It provides metadata about database objects.

Syntax:

SHOW DATABASES;

SHOW TABLES;

SHOW COLUMNS FROM table_name;

Example:

SHOW DATABASES;

SHOW TABLES;

SHOW COLUMNS FROM Student;

It displays information about SQL syntax and commands.

Syntax:

HELP command_name;

Example:

HELP SELECT;