Introduction
Structured Query Language (SQL) is a language for querying and manipulating data. It is a standard language for relational database management systems (RDBMS). SQL is based on relational algebra and tuple relational calculus. The language is consists of many sublanguages, such as data definition language (DDL), data manipulation language (DML), data control language (DCL), and transaction control language (TCL).
The language SQL falls into the category of declarative languages. It is a non-procedural language, which means that it does not describe how to perform a task, but rather what the task is.
Types of SQL Commands
SQL commands are used to perform different operations on the database. The operations range from creating a database to creating a table, inserting data into a table, updating data in a table, granting permissions to users, and so on. The following are the types of SQL commands:
- Data Definition Language (DDL)
- Data Manipulation Language (DML)
- Data Control Language (DCL)
- Data Query Language (DQL)
- Transaction Control Language (TCL)
Data Definition Language (DDL)
DDL is used to define the database structure or schema. This includes creating and modifying objects in the database. The following are the DDL commands:
CREATE
The CREATE
command is used to create the database or its objects (like table, index, function etc). The following SQL command creates a table named "books" in the "library" database.
CREATE TABLE library.books (
book_id INT NOT NULL,
book_name VARCHAR(100) NOT NULL,
author VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (book_id)
);
ALTER
The ALTER
command is used to alter the structure of the database. It is also used to add and drop various constraints on the table. The following SQL command adds a new column named "publisher" to the "books" table.
ALTER TABLE library.books ADD publisher VARCHAR(100) NOT NULL;
DROP
The DROP
command is used to delete objects from the database. The following SQL command deletes the "books" table from the "library" database.
DROP TABLE library.books;
TRUNCATE
The TRUNCATE
command is used to remove all records from a table, including all spaces allocated for the records are removed. The following SQL command deletes all the records from the "books" table.
TRUNCATE TABLE library.books;
COMMENT
The COMMENT
command is used to add comments to the table or database objects. The following SQL command adds a comment to the "books" table.
COMMENT ON TABLE library.books IS 'This table contains the list of books in the library.';
RENAME
The RENAME
command is used to rename an object existing in the database. The following SQL command renames the "books" table to "book_list".
RENAME TABLE library.books TO library.book_list;
Data Manipulation Language (DML)
DML is used to manipulate the data present in the database. It includes inserting, deleting, and modifying data in the database. Most of the DML commands are used to manipulate data in a single table. The following are the DML commands:
INSERT
The INSERT
command is used to insert data into a table. The following SQL command inserts a new record in the "books" table.
INSERT INTO library.books (book_id, book_name, author, price, publisher)
VALUES (1, 'The Alchemist', 'Paulo Coelho', 200.00, 'HarperCollins');
The previous SQL command can also be written as:
INSERT INTO library.books
VALUES (1, 'The Alchemist', 'Paulo Coelho', 200.00, 'HarperCollins');
UPDATE
The UPDATE
command is used to modify the existing records in a table. The following SQL command updates the price of the book whose id is 1.
UPDATE library.books
SET price = 250.00
WHERE book_id = 1;
DELETE
The DELETE
command is used to delete records from a table. The following SQL command deletes the book whose id is 1.
DELETE FROM library.books
WHERE book_id = 1;
LOCK
The LOCK
command is used to lock a table against any changes. The following SQL command locks the "books" table.
LOCK TABLE library.books IN EXCLUSIVE MODE;
Data Control Language (DCL)
DCL is used to define the security of the database. It includes granting and revoking permissions to the users. The following are the DCL commands:
GRANT
The GRANT
command is used to grant permissions to the users. The following SQL command grants the SELECT
permission on the "books" table to the user "user_1".
GRANT SELECT ON library.books TO user_1;
REVOKE
The REVOKE
command is used to revoke permissions from the users. The following SQL command revokes the SELECT
permission on the "books" table from the user "user_1".
REVOKE SELECT ON library.books FROM user_1;
Data Query Language (DQL)
DQL is used to query data from the database. It is used to fetch data from a single table or multiple tables. The following are the DQL commands:
SELECT
The SELECT
command is used to fetch data from a database. The following SQL command fetches all the records from the "books" table.
SELECT * FROM library.books;
Transaction Control Language (TCL)
TCL is used to control the transactions in the database. When a transaction is started, it is in temporary state. TCL commands are used to make the changes permanent or to cancel the transaction. The following are the TCL commands:
COMMIT
The COMMIT
command is used to commit the changes made by the transaction. The following SQL command commits the changes made by the transaction.
COMMIT;
ROLLBACK
The ROLLBACK
command is used to rollback the changes made by the transaction. The following SQL command rolls back the changes made by the transaction.
ROLLBACK;
SAVEPOINT
The SAVEPOINT
command is used to create a savepoint within a transaction. The following SQL command creates a savepoint named "savepoint_1".
SAVEPOINT savepoint_1;
SQL Operators
SQL provides various operators to perform different operations on data. The following table lists the most commonly used operators.
Arithmetic Operators
Arithmetic operators are used to perform arithmetic operations on numeric data.
Operator | Description | Example |
---|---|---|
+ | adds two operands | SELECT 10 + 5 AS sum; |
- | subtracts two operands | SELECT 10 - 5 AS difference; |
* | multiplies two operands | SELECT 10 * 5 AS product; |
/ | divides two operands | SELECT 10 / 5 AS quotient; |
% | returns the remainder | SELECT 10 % 5 AS remainder; |
Comparison Operators
Comparison operators are used to compare two values. The result of a comparison is always a boolean value.
Operator | Description | Example |
---|---|---|
= | is equal | SELECT 10 = 5 AS result; |
<> | is not equal | SELECT 10 <> 5 AS result; |
> | is greater than | SELECT 10 > 5 AS result; |
< | is less than | SELECT 10 < 5 AS result; |
>= | is greater than or equal to | SELECT 10 >= 5 AS result; |
<= | is less than or equal to | SELECT 10 <= 5 AS result; |
Logical Operators
Logical operators are used to combine two or more conditions. The result of a logical operation is always a boolean value. The following table lists the logical operators.
Operator | Description | Example |
---|---|---|
AND | and | SELECT 10 > 5 AND 10 < 15; |
OR | or | SELECT 10 > 5 OR 10 < 5; |
NOT | not | SELECT NOT 10 > 5; |
BETWEEN | between | SELECT 10 BETWEEN 5 AND 15; |
IN | in | SELECT 10 IN (5, 10, 15); |
LIKE | like | SELECT 'abc' LIKE 'a%'; |
EXISTS | exists | SELECT EXISTS (SELECT * FROM library.books); |
ALL | all | SELECT 10 > ALL (SELECT price FROM library.books); |
ANY | any | SELECT 10 > ANY (SELECT price FROM library.books); |
SOME | some | SELECT 10 > SOME (SELECT price FROM library.books); |
Bitwise Operators
Bitwise operators are used to perform bitwise operations on integer data.
Operator | Description | Example |
---|---|---|
& | and | SELECT 10 & 5 AS result; |
^ | xor | SELECT 10 ^ 5 AS result; |
~ | not | SELECT ~10 AS result; |
Basic SQL Commands
The following are the basic SQL commands:
Create
The CREATE
command is used to create the database or its objects (like table, index, function etc).
The following SQL command creates a database named "library".
CREATE DATABASE library;
The following SQL command creates a table named "books" in the "library" database.
CREATE TABLE library.books (
book_id INT NOT NULL,
book_name VARCHAR(100) NOT NULL,
author VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (book_id)
);
Insert
The INSERT
command is used to insert data into a table. The following SQL command inserts a new record in the "books" table.
INSERT INTO library.books (book_id, book_name, author, price, publisher)
VALUES (1, 'The Alchemist', 'Paulo Coelho', 200.00, 'HarperCollins');
When inserting data into a table, you can specify the column names or omit them. If you omit the column names, the values will be inserted in the same order in which the columns are defined in the table.
Select
The SELECT
command is used to fetch data from a database. The following SQL command fetches all the records from the "books" table.
SELECT * FROM library.books;
To fetch only specific columns, you can specify the column names separated by commas.
SELECT book_id, book_name, author FROM library.books;
To fetch only distinct values, you can use the DISTINCT
keyword. This keyword is used to return only distinct (different) values.
SELECT DISTINCT author FROM library.books;
Where
To filter the records, you can use the WHERE
clause. The WHERE
clause is used to specify a condition while fetching the data. The following SQL command fetches the books written by "Paulo Coelho".
SELECT * FROM library.books
WHERE author = 'Paulo Coelho';
The WHERE
clause can be used with comparison operators like =
, >
, <
, >=
, <=
, <>
(not equal to) etc.
SELECT * FROM library.books
WHERE price >= 200.00;
Order By
The ORDER BY
clause is used to sort the result-set in ascending or descending order. By default, the ORDER BY
clause sorts the result-set in ascending order. The following SQL command fetches all the books sorted by their price in descending order.
SELECT * FROM library.books
ORDER BY price DESC;
You can sort the result-set by multiple columns. The following SQL command fetches all the books sorted by their author and then by their price in descending order.
SELECT * FROM library.books
ORDER BY author, price DESC;
Limit
The LIMIT
clause is used to specify the maximum number of records to be fetched from the database. The following SQL command fetches only the first 5 books from the "books" table.
SELECT * FROM library.books
LIMIT 5;
To fetch a certain number of records starting from a particular offset, you can use the OFFSET
clause. The following SQL command fetches 5 books starting from the 3th book.
SELECT * FROM library.books
LIMIT 5 OFFSET 2;
This is equivalent to the following SQL command.
SELECT * FROM library.books
LIMIT 2, 5;
Alias
Alias is used to give a table, or a column in a table, a temporary name. The AS
keyword is used to create an alias. The following SQL command fetches all the books and gives the "books" table an alias name "b".
SELECT * FROM library.books AS b;
The following SQL command fetches the book name and price of all the books and gives the "book_name" and "price" columns an alias name "name" and "cost" respectively.
SELECT book_name AS name, price AS cost FROM library.books;
Group By
The GROUP BY
clause is used to group the result-set by one or more columns. The following SQL command fetches the number of books written by each author.
SELECT author, COUNT(*) AS num_books
FROM library.books
GROUP BY author;
Having
The HAVING
clause is used with the GROUP BY
clause to filter the grouped records. The following SQL command fetches the number of books written by each author having more than 1 book.
SELECT author, COUNT(*) AS num_books
FROM library.books
GROUP BY author
HAVING COUNT(*) > 1;
SQL Commands for Modifying Data
The following are the SQL commands for modifying data:
Update
The UPDATE
command is used to modify the existing records in a table. The following SQL command updates the price of all the books written by "Paulo Coelho".
UPDATE library.books
SET price = price + 10.00
WHERE author = 'Paulo Coelho';
Delete
The DELETE
command is used to delete existing records from a table. The following SQL command deletes all the books written by "Paulo Coelho".
DELETE FROM library.books
WHERE author = 'Paulo Coelho';
Alter
The ALTER
command is used to alter the structure of the database. The command can be used to add, delete or modify columns in a table. The following SQL command adds a new column named "publisher" in the "books" table.
ALTER TABLE library.books
ADD publisher VARCHAR(100) NOT NULL;
SQL Commands for Database Administration
The following are the SQL commands for database administration:
Drop
The DROP
command is used to delete objects from the database. The command can be used to drop a database, table, view, index etc. The following SQL command drops the "books" table.
DROP TABLE library.books;
Truncate
The TRUNCATE
command is used to delete all the records from a table. The following SQL command deletes all the records from the "books" table.
TRUNCATE TABLE library.books;
References
- Chamberlin, D. D. (n.d.). SQL. Wikipedia. From https://en.wikipedia.org/wiki/SQL
- SQL Commands: DDL, DML, DCL, TCL, DQL (With Examples). (2022, January 21). InterviewBit. From https://www.interviewbit.com/blog/sql-commands/
- SQL | DDL, DQL, DML, DCL and TCL Commands. (2021, September 30). GeeksforGeeks. From https://www.geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands/
- SQL Operators. (n.d.). W3Schools. From https://www.w3schools.com/sql/sql_operators.asp