SQL Commands
SQL Commands – Definition, Syntax and Examples
SQL (Structured Query Language) is a standard
programming language used to create, manage, manipulate, and retrieve data from
relational databases.
SQL
commands are divided into five main categories:
1. DDL – Data Definition
Language
2. DML – Data Manipulation
Language
3. DQL – Data Query Language
4. DCL – Data Control
Language
5. TCL – Transaction Control
Language
1.
DDL – Data Definition Language
DDL
commands are used to create, modify, and delete database structures such as
tables and databases.
1.
CREATE
Definition: CREATE command is used to create a new
database or table.
Syntax:
CREATE TABLE table_name
(
column1 datatype,
column2 datatype
);
Example:
CREATE TABLE Student
(
RollNo INT,
Name VARCHAR(50),
Age INT,
City VARCHAR(30)
);
2.
ALTER
Definition: ALTER command is used to modify the
structure of an existing table.
Syntax:
ALTER TABLE
table_name
ADD column_name datatype;
Example:
ALTER TABLE
Student
ADD Marks INT;
3.
DROP
Definition: DROP command is used to permanently delete
a table or database.
Syntax:
DROP TABLE
table_name;
Example:
DROP TABLE
Student;
4.
TRUNCATE
Definition: TRUNCATE command is used to delete all
records from a table without deleting its structure.
Syntax:
TRUNCATE TABLE
table_name;
Example:
TRUNCATE TABLE
Student;
5.
RENAME
Definition: RENAME command is used to change the name
of an existing table.
Syntax:
RENAME TABLE
old_name
TO
new_name;
Example:
RENAME TABLE
Student
TO
Students;
Note: RENAME syntax varies among database systems.
2.
DML – Data Manipulation Language
DML
commands are used to insert, update, and delete records in a table.
6.
INSERT
Definition: INSERT command is used to add new records
to a table.
Syntax:
INSERT INTO table_name
VALUES (value1,
value2,
value3);
Example:
INSERT INTO Student
VALUES (1,
'Rahul',
15,
'Delhi');
7.
UPDATE
Definition: UPDATE command is used to modify existing
records in a table.
Syntax:
UPDATE table_name
SET column_name
=
value
WHERE condition;
Example:
UPDATE Student
SET Age
=
16
WHERE RollNo
=
1;
8.
DELETE
Definition: DELETE command is used to remove records
from a table.
Syntax:
DELETE FROM table_name
WHERE condition;
Example:
DELETE FROM Student
WHERE RollNo
=
1;
3.
DQL – Data Query Language
DQL
commands are used to retrieve data from database tables.
9.
SELECT
Definition: SELECT command is used to display or
retrieve records from a table.
Syntax:
SELECT column_name
FROM table_name;
Example:
SELECT * FROM Student;
This
displays all records and columns from the Student table.
10.
DISTINCT
Definition: DISTINCT keyword is used with SELECT to
display only unique values.
Syntax:
SELECT DISTINCT column_name
FROM table_name;
Example:
SELECT DISTINCT City
FROM Student;
11.
WHERE
Definition: WHERE clause is used to retrieve records
that satisfy a specified condition.
Syntax:
SELECT * FROM table_name
WHERE condition;
Example:
SELECT * FROM Student
WHERE Age
>
15;
12.
ORDER BY
Definition: ORDER BY clause is used to arrange records
in ascending or descending order.
Syntax:
SELECT * FROM table_name
ORDER BY column_name
ASC;
Example:
SELECT * FROM Student
ORDER BY Age
DESC;
13.
GROUP BY
Definition: GROUP BY clause is used to group rows
having the same values in a particular column.
Syntax:
SELECT column_name,
COUNT(*)
FROM table_name
GROUP BY column_name;
Example:
SELECT City,
COUNT(*)
FROM Student
GROUP BY City;
14.
HAVING
Definition: HAVING clause is used to apply conditions
to groups created by GROUP BY.
Syntax:
SELECT column_name,
COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*)
>
1;
Example:
SELECT City,
COUNT(*)
FROM Student
GROUP BY City
HAVING COUNT(*)
>
1;
15.
LIKE
Definition: LIKE operator is used to search for a
specified pattern in a column.
Syntax:
SELECT * FROM table_name
WHERE column_name
LIKE
'pattern';
Example:
SELECT * FROM Student
WHERE Name
LIKE
'R%';
This
displays students whose names start with R.
4.
DCL – Data Control Language
DCL
commands are used to control access and permissions in a database.
16.
GRANT
Definition: GRANT command is used to provide
privileges to database users.
Syntax:
GRANT privilege_name
ON table_name
TO user_name;
Example:
GRANT SELECT
ON Student
TO user1;
17.
REVOKE
Definition: REVOKE command is used to withdraw
previously granted permissions from users.
Syntax:
REVOKE privilege_name
ON table_name
FROM user_name;
Example:
REVOKE SELECT
ON Student
FROM user1;
5.
TCL – Transaction Control Language
TCL
commands are used to manage transactions in a database.
18.
COMMIT
Definition: COMMIT command is used to permanently save
the changes made during a transaction.
Syntax:
COMMIT;
Example:
UPDATE Student
SET Age
=
17
WHERE RollNo
=
2;
COMMIT;
19.
ROLLBACK
Definition: ROLLBACK command is used to undo changes
made during a transaction that have not been committed.
Syntax:
ROLLBACK;
Example:
DELETE FROM Student
WHERE RollNo
=
2;
ROLLBACK;
20.
SAVEPOINT
Definition: SAVEPOINT command is used to create a
temporary point within a transaction to which we can roll back.
Syntax:
SAVEPOINT savepoint_name;
Example:
SAVEPOINT S1;
UPDATE Student
SET Age
=
18
WHERE RollNo
=
3;
ROLLBACK TO
SAVEPOINT
S1;
Summary
of SQL Commands
|
Category |
Command |
Purpose |
|
DDL |
CREATE |
Creates database
objects |
|
DDL |
ALTER |
Modifies table
structure |
|
DDL |
DROP |
Deletes database
objects |
|
DDL |
TRUNCATE |
Deletes all table
records |
|
DDL |
RENAME |
Renames a table |
|
DML |
INSERT |
Adds new records |
|
DML |
UPDATE |
Modifies existing
records |
|
DML |
DELETE |
Deletes records |
|
DQL |
SELECT |
Retrieves records |
|
DQL |
DISTINCT |
Displays unique values |
|
DQL |
WHERE |
Filters records |
|
DQL |
ORDER BY |
Sorts records |
|
DQL |
GROUP BY |
Groups records |
|
DQL |
HAVING |
Filters groups |
|
DCL |
GRANT |
Gives permissions |
|
DCL |
REVOKE |
Removes permissions |
|
TCL |
COMMIT |
Saves changes |
|
TCL |
ROLLBACK |
Undoes changes |
|
TCL |
SAVEPOINT |
Creates a rollback
point |
Important
Difference Between SQL Commands
|
Command |
Effect |
|
DELETE |
Deletes selected or all
rows |
|
TRUNCATE |
Deletes all rows but
keeps table structure |
|
DROP |
Deletes the entire
table |
|
UPDATE |
Modifies existing
records |
|
ALTER |
Modifies table
structure |
Important: Always use the WHERE clause carefully with
UPDATE and DELETE. Without WHERE, these commands may affect every record in the
table.
This guide covers 20 commonly used SQL commands and clauses, with definitions, syntax, and practical examples. It can be used as study material for computer students.
No comments:
Post a Comment