SELECT - extracts data from a database
UPDATE - updates data in a database
DELETE - deletes data from a database
INSERT INTO - inserts new data into a database
CREATE DATABASE - creates a new database
ALTER DATABASE - modifies a database
CREATE TABLE - creates a new table
ALTER TABLE - modifies a table
DROP TABLE - deletes a table
CREATE INDEX - creates an index (search key)
DROP INDEX - deletes an index
SQL commands are fundamental building blocks used to perform given operations
on database. The operations include queries of data. creating a table, adding data to
tables, dropping the table, modifying the table and set permission for users.
SQL Commands are mainly categorized into five categories:
Command Description Syntax
Create database or its CREATE TABLE
objects (table, index, table_name (column1
CREATE
function, views, store data_type, column2
procedure and triggers) data_type, ...);
Delete objects from the
DROP DROP TABLE table_name;
database
ALTER TABLE table_name
Alter the structure of the
ALTER ADD COLUMN
database
column_name data_type;
Remove all records from a
table, including all spaces TRUNCATE TABLE
TRUNCATE
allocated for the records are table_name;
removed
COMMENT ON TABLE
Add comments to the data
COMMENT table_name IS
dictionary
'comment_text';
RENAME TABLE
Rename an object existing in
RENAME old_table_name TO
the database
new_table_name;
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
hire_date DATE
);
2. DQL - Data Query Language
DQL is used to fetch data from the database. The main command is SELECT, which
retrieves records based on the query. The output is returned as a result set (a
temporary table) that can be viewed or used in applications.
Note: DQL has only one command, SELECT. Other terms like
FROM, WHERE, GROUP BY, HAVING, ORDER BY, DISTINCT and
LIMIT are clauses of SELECT, not separate commands.
Example:
SELECT first_name, last_name, hire_date
FROM employees
WHERE department = 'Sales'
ORDER BY hire_date DESC;
This query retrieves employees first and last names, along with their hire dates,
from the employees table, specifically for those in the 'Sales' department, sorted by
hire date.
3. DML - Data Manipulation Language
DML commands are used to manipulate the data stored in database tables. With
DML, you can insert new records, update existing ones, delete unwanted data or
retrieve information.
Command Description Syntax
INSERT INTO table_name (column1,
INSERT Insert data into a table
column2, ...) VALUES (value1, value2, ...);
UPDATE table_name SET column1 =
Update existing data
UPDATE value1, column2 = value2 WHERE
within a table
condition;
Delete records from a DELETE FROM table_name WHERE
DELETE
database table condition;
Example:
INSERT INTO employees (first_name, last_name, department)
VALUES ('Jane', 'Smith', 'HR');
This query inserts a new record into employees table with first name 'Jane', last
name 'Smith' and department 'HR'.
4. DCL - Data Control Language
DCL (Data Control Language) includes commands such as GRANT and REVOKE
which mainly deal with the rights, permissions and other controls of the database
system. These commands are used to control access to data in the database by
granting or revoking permissions.
Command Description Syntax
Assigns new privileges to a GRANT privilege_type
user account, allowing [(column_list)] ON
GRANT access to specific database [object_type] object_name TO
objects, actions or user [WITH GRANT
functions. OPTION];
Removes previously REVOKE [GRANT OPTION
granted privileges from a FOR] privilege_type
REVOKE user account, taking away [(column_list)] ON
their access to certain [object_type] object_name
database objects or actions. FROM user [CASCADE];
Example:
GRANT SELECT, UPDATE ON employees TO user_name;
This command grants the user user_name the permissions to select and update
records in the employees table.
5. TCL - Transaction Control Language
Transactions group a set of tasks into a single execution unit. Each transaction
begins with a specific task and ends when all the tasks in the group are successfully
completed. If any of the tasks fail, transaction fails. Therefore, a transaction has
only two results: success or failure.
Command Description Syntax
BEGIN BEGIN TRANSACTION
Starts a new transaction
TRANSACTION [transaction_name];
Command Description Syntax
Saves all changes made
COMMIT COMMIT;
during the transaction
Undoes all changes made
ROLLBACK ROLLBACK;
during the transaction
Creates a savepoint within SAVEPOINT
SAVEPOINT
the current transaction savepoint_name;
Example:
BEGIN TRANSACTION;
UPDATE employees SET department = 'Marketing' WHERE
department = 'Sales';
SAVEPOINT before_update;
UPDATE employees SET department = 'IT' WHERE department =
'HR';
ROLLBACK TO SAVEPOINT before_update;
COMMIT;
In this example, a transaction is started, changes are made and a savepoint is set. If
needed, the transaction can be rolled back to the savepoint before being committed.