SQL commands SQL commands are instructions, coded into SQL statements, which are used to communicate with the database to perform specific task, work, functions and queries with data.
SQL commands can be used not only for searching the database but also to perform various other functions like for example we can create tables, add data to tables or modify data, drop the table, set permissions for users.
SQL commands are grouped into four major categories depending on their functionality:-
Data definition language (DDL)- DDL or data definition language actually consists of the SQL commands that can be used to define the database schema(logical structure). These SQL commands are used for creating, modifying and dropping the structure of database objects(table, index, functions, views etc.)
Examples of DDL commands:
- CREATE- it is used to create the database or its objects like table, index, functions, view, stored procedures and triggers
- DROP- it is used to delete object from the database
- ALTER- it is used to alter the structure of the database
- TRUNCATE- it is used to remove all records from a table, including all spaces allocated for the records.
- COMMENT- it is used to add comments to the data dictionary.
- RENAME- it is used to rename a object existing in the database.
Data manipulation language (DML)- the SQL commands that deals with the manipulation of data present in database belongs to DML or data manipulation language. These SQL commands are used for storing and retrieving, modifying and deleting data.
Examples of DML commands:
- SELECT- it is used to retrieve data from the database
- INSERT- it is used to insert data into a table
- UPDATE- it is used to update existing data within a table
- DELETE- it is used to delete records from a database table
Transaction control language (TCL)- these SQL commands are used for managing changes affecting the data. TCL commands deals with the transaction within the database.
Examples of TCL commands:
- COMMIT- commits a transaction
- ROLLBACK- rollbacks a transaction in case of any error occurs
- SAVEPOINT- sets a savepoint within a transaction
- SET TRANSACTION- specifies characteristics for the transaction
Data control language (DCL)- these SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE which mainly deals with the rights permissions and other controls of the database system.
Examples of DCL commands:
- GRANT- gives users access privileges to database
- REVOKE- revoke (withdraw) uses access privileges given by using the GRANT command
DDL commands
- CREATE- there are two Create statements available in SQL.
- Create Database
- Create Table
Create Database- a database is defined as a structured set of data. So very first step to store the data in a well structured manner is to create a database.
We can use CREATE DATABASE statement to create a new database in SQL.
Syntax:
CREATE DATABASE database_name;
E.g.
CREATE DATABASE school;
This query will create an new database in SQL and name the database as school.
After creating database we can use USE statement to use and make it the current database
Syntax:
USE database_name;
E.g.
USE school;
Create Table- We can use CREATE TABLE statement to create a new table in SQL.
Syntax:
CREATE TABLE table_name;
(
Column1 data_type(size),
Column2 data_type(size),
...
);
E.g.
CREATE TABLE student
(
roll_no int(3),
name varchar(30),
address varchar(50),
mob_no bigint(12),
age int(2)
);
This query will create a new table in SQL and name the table as student.
- DROP- drop is used to delete a whole database or just a table. The SQL drop command is used to remove an object from the database. If we drop a table, all the rows in the table is deleted and the table structure is removed from the database. Once a table is dropped we cannot get it back so be careful while using drop command. When a table is dropped all the references to the table will not be valid.
Deleting Table
Syntax:
DROP TABLE table_name;
E.g.
DROP TABLE student;
This query will drop/delete table student in SQL.
Deleting Database
Syntax:
DROP DATABASE database_name;
E.g.
DROP DATABASE school;
This query will drop/delete database school in SQL.
- TRUNCATE- truncate statement is a data definition language(DDL) operation that is used to mark the extents(contiguous area of storage) of a table for deallocation(empty for reuse). The SQL TRUNCATE command is used to delete all the rows from the table and free the space containing the table.
Syntax:
TRUNCATE TABLE table_name;
E.g. TRUNCATE TABLE student;
This query will truncate/empty table student in SQL.
DML commands
- INSERT- using INSERT command we can insert data into a table. We can use INSERT INTO statement by using two ways.
1. Only values- using this way we can specify only the value of data to be inserted without the column names.
Syntax:
INSERT INTO table_name
values(value1,value2, value3, .....valueN);
E.g.
INSERT INTO student
values(1,'Rohan', 'Delhi',9858584584, 15);
This query will create a new row in a table student.
2. Column names and values- using this way we can specify column name which we want to fill and their corresponding values.
Syntax:
INSERT INTO table_name (col1,col2,col3, ..colN)
values(value1,value2, value3, .....valueN);
E.g.
INSERT INTO student (roll_no, name)
values(2,'Mohan');
This query will create a new row in a table student.
- SELECT- using SELECT command we can retrieve or fetch data from a database. We can fetch either the entire table or specified columns according to our specification. We can use SELECT command using following syntax:
Syntax:
SELECT col1, col2, ...colN FROM table_name;
E.g.
SELECT roll_no, name FROM student;
This query will fetch/retrieve data of roll_no and name columns of student table.
If we want to fetch/retrieve data of entire table, then we can use * symbol instead of columns name.
Syntax:
SELECT * FROM table_name;
E.g.
SELECT * FROM student;
This query will fetch/retrieve data of entire table i.e. student.
WHERE Clause
WHERE clause is used with SELECT statement to specify the condition based on which rows will be extracted from the table. WHERE keyword is used for fetching filtered data in a result set.
- It is used to fetch data according to a particular criteria
- WHERE keyword can also be used to filter data by matching patterns.
Syntax:
SELECT col1, col2, ...colN
FROM table_name
WHERE col_name operator value;
E.g.
SELECT roll_no, name
FROM student
WEHRE roll_no = 1;
This query will extract a row/rows from the table student.
List of operators that can be used with WHERE clause
| Operator | Description |
|---|---|
| > | Greater than |
| >= | Greater than or Equal to |
| < | Less than |
| <= | Less than or Equal to |
| = | Equal to |
| <> | Not equal to |
| BETWEEN | In an inclusive range |
| LIKE | Search for a pattern |
| IN | To specify multiple possible values for a column |
Example queries with operators
> Operator
SELECT *
FROM student
WEHRE roll_no > 1;
>= Operator
SELECT *
FROM student
WEHRE roll_no >= 1;
< Operator
SELECT *
FROM student
WEHRE roll_no < 2;
<= Operator
SELECT *
FROM student
WEHRE roll_no <= 2;
= Operator
SELECT *
FROM student
WEHRE roll_no = 2;
<> Operator
SELECT *
FROM student
WEHRE roll_no <> 2;
There are two wildcards often used in conjunction with the LIKE operator:
BETWEEN Operator- the between operator is used to specify a range of values. The range we specify contains a lower and upper range. It is used to fetch filtered data in a given range.
Syntax:
SELECT col1, col2, ...colN
FROM table_name
WHERE col_name BETWEEN Val1 AND Val2;
E.g.
SELECT *
FROM student
WEHRE roll_no BETWEEN 1 AND 3;
LIKE Operator- in certain cases we may not always know the exact value to search for. We can select rows that match the correct pattern by using LIKE operator which is used to fetch filtered data by searching for a particular pattern in WHERE clause. LIKE operator is used with CHAR data type.
There are two wildcards often used in conjunction with the LIKE operator:
- % - The percent sign represents zero, one, or multiple characters
- _ - The underscore represents a single character
Syntax:
SELECT col1, col2, ...colN
FROM table_name
WHERE col_name LIKE pattern;
E.g. % wildcard
SELECT *
FROM student
WEHRE name LIKE 'r%';
E.g. _ wildcard
SELECT *
FROM student
WEHRE name LIKE 's____';
IN Operator- IN operator is used along with WHERE clause for providing multiple values as part of the WHERE clause.
Syntax:
SELECT col1, col2, ...colN
FROM table_name
WHERE col_name IN (Val1, Val2, ValN);
E.g. 1
SELECT *
FROM student
WEHRE roll_no IN (1, 3);
E.g. 2
SELECT *
FROM student
WEHRE name IN ('rohan','sujit');
AND, OR, NOT Operators
AND operator- this operator displays only those records where both/all the conditions Condition1 and Condition2 and ConditionN evaluates to true.
Syntax:
SELECT col1, col2, ...colN
FROM table_name
WHERE Condition1 AND Condition2 AND ConditionN;
E.g.
SELECT *
FROM student
WEHRE roll_no = 3 AND name = 'sujit';
OR operator- this operator displays only those records where either one of the conditions Condition1 and Condition2 and ConditionN evaluates to true.
Syntax:
SELECT col1, col2, ...colN
FROM table_name
WHERE Condition1 OR Condition2 OR ConditionN;
E.g.
SELECT *
FROM student
WEHRE roll_no = 3 OR name = 'rohan';
NOT operator- this operator displays only those records where condition evaluates to false.
Syntax:
SELECT col1, col2, ...colN
FROM table_name
WHERE NOT Condition;
E.g.
SELECT *
FROM student
WEHRE NOT roll_no = 2;
ORDER BY Clause
ORDER BY clause is used with SELECT statement to sort the fetched data in either ascending or descending order according to one or more columns.
- By default ORDER BY clause sorts the data in ascending order.
- We can use DESC keyword to sort the data in descending order and ASC keyword to sort the data in ascending order.
Sort according to one column
Syntax:
SELECT col1, col2, ...colN/*
FROM table_name
ORDER BY col_name ASC/DESC;
E.g.
SELECT *
FROM student
ORDER BY roll_no DESC;
Sort according to multiple columns
Syntax:
SELECT col1, col2, ...colN/*
FROM table_name
ORDER BY Column1 ASC/DESC, Column2 ASC/DESC, ....ColumnN ASC/DESC;
E.g.
SELECT *
FROM student
ORDER BY roll_no ASC, age DESC;
ALTER (ADD, DROP, MODIFY)
Tables can be altered in one of the 3 ways
- by adding a column to existing table
- by changing a column's definition
- by dropping a column
ALTER TABLE command is used to add, delete/drop or modify columns in the existing table. It is also used to add, drop various constraints on the existing table.
ALTER TABLE- ADD- add is used to add columns into the existing table. Sometimes we may require to add additional information, in that case we do not require to create the whole database again, ADD comes to our rescue.
Syntax:
ALTER TABLE table_name
ADD (col1 datatype, Col2 datatype ...ColN datatype);
E.g.
ALTER TABLE student
ADD (class varchar(10), sec char(1));
ALTER TABLE- DROP- we can use DROP COLUMN clause to drop/delete column from the existing table. Sometimes we may require to drop/delete additional information, in that case we can use DROP COLUMN clause with ALTER TABLE statement using following syntax:
Syntax:
ALTER TABLE table_name
DROP COLUMN Column_name;
E.g.
ALTER TABLE student
DROP COLUMN sec;
ALTER TABLE- MODIFY- we can modify a column definition by using the ALTER TABLE statement with the MODIFY clause which is used to modify the existing columns in a table. Column modification can include changes to a column's datatype, size and default value.
Syntax:
ALTER TABLE table_name
MODIFY Column_name Column_type;
E.g.
ALTER TABLE student
MODIFY name varchar(50);
After running above query the maximum size of name column is increased to 50 from 30
UPDATE- the UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple columns using UPDATE statement as per our requirement
Syntax:
UPDATE table_name SET Column1 = value1, Column2 = value2 ... ColumnN = valueN
WHERE Condition;
E.g.
UPDATE student SET name ='John', age = 18
WHERE roll_no = 1;
DISTINCT- the DISTINCT clause is used when there is need of avoiding the duplicate values present in any specific columns/table. When we use DISTINCT keyword only the unique values are fetched or retrieve. We can use DISTINCT clause with SELECT statement.
Syntax:
SELECT DISTINCT Column1, Column2, ....ColumnN FROM table_name;
E.g.
SELECT DISTINCT name FROM student;
Arithmetic operators
We can use arithmetic operators on the data stored in the tables.
| Operators | Descriptions |
|---|---|
| + | It adds both operand's values |
| - | It subtracts right hand operand from left hand operand |
| * | It multiply both operand's values |
| / | It divides left hand operand by right hand operand |
| % | It divides left hand operand by right hand operand and returns remainder |
E.g. + operator
SELECT roll_no, name, age + 1 FROM student;
COLUMN ALIAS- aliases are the temporary names given to table or column for the purpose of a particular SQL query. It is used when name of column or table is used other than their original names, but the modified name is only temporary.
Syntax:
SELECT Column1 as alias_name, Column2 as alias_name, ....ColumnN as alias_name FROM table_name;
E.g.
- Aliases are created to make table or column names more readable
- The renaming is just a temporary change and table name does not change in the original database
- Aliases are useful when table or column names are big or not very readable
- These are preferred when there are more than one table involved in a query
Syntax:
SELECT Column1 as alias_name, Column2 as alias_name, ....ColumnN as alias_name FROM table_name;
E.g.
SELECT roll_no as s_code, name as s_name FROM student;
Transactions
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 successfully complete. If any of the task fail, the transaction fails. Therefore, the transaction has only two results: success or failure
Incomplete steps results in the failure of the transaction. A database transaction, by definition, must be atomic, consistent, isolated and durable. These are properly known as ACID (atomicity, consistency, isolation and durability) properties.
How to implement transaction using SQL
Following commands are used to control transactions. It is important to note that these statements cannot be used while creating tables and are only used with the DML commands such as- INSERT, UPDATE AND DELETE.
SET TRANSACTION- SET TRANSACTION command is used to establish the current transaction as read write or read-only and its isolation level.
Syntax:-
SET TRANSACTION [READ WRITE | READ ONLY]
COMMIT- commit is the SQL command that is used for storing changes performed by a transaction. When a COMMIT command is issued it saves all the changes since last commit or rollback.
Syntax:-
COMMIT;
Following is an example which would delete those records from the table which have age = 13 and then COMMIT the changes in the database.
E.g.
DELETE FROM student WHERE age = 13;
COMMIT;
Thus, two rows from the table would be deleted.
ROLLBACK- ROLLBACK is the SQL command that is used for reverting changes performed by a transaction. When a ROLLBACK command is issued it reverts all the changes since last COMMIT or ROLLBACK.
Syntax:-
ROLLBACK;
Following is an example which would delete those records from the table which have age = 18 and then ROLLBACK the changes in the database.
* Note: By default, MySQL automatically commits the changes permanently to the database. To force MySQL not to commit changes automatically, we need to use the following statement:
SET autocommit = 0; Or SET autocommit = off;
To enable
SET autocommit = 1; Or SET autocommit = on;
E.g.
SET autocommit = 0;
DELETE FROM student WHERE age = 18;
ROLLBACK;
here is SAVEPOINT SP1, created before deletion statement.
SAVEPOINT- SAVEPOINT is a point in a transaction in which we can roll the transaction back to a certain point without rolling back the entire transaction.
We can create a SAVEPOINT using following syntax:
Syntax:-
SAVEPOINT SAVEPOINT_NAME;
Syntax for rolling back to SAVEPOINT Command:
Syntax:-
ROLLBACK TO SAVEPOINT_NAME;
Following is an example which would delete those records from the table which have age = 18 and then ROLLBACK the changes in the database by keeping Savepoints.
E.g.
SET autocommit = 0;
SAVEPOINT SP1;
DELETE FROM student WHERE age = 18;
Let us assume that we have changed our mind and decided to ROLLBACK to the SAVEPOINT that we identified as SP1 which is before deletion.
So we can issue following statement to revert/undo deletion of records
ROLLBACK TO SP1;
RELEASE SAVEPOINT- this command is used to remove a SAVEPOINT that we have created.
We can release/remove a SAVEPOINT using following syntax:
Syntax:-
RELEASE SAVEPOINT SAVEPOINT_NAME;
E.g.
RELEASE SAVEPOINT SP1;
CREATING USER ACCOUNT IN SQL- the DBA(Database Administrator) creates the user by executing the CREATE USER statement. The user does not have any privileges at this point. The DBA can then grant number of privileges to that user. These privileges determine what the user can do at database level.
Syntax:
CREATE USER USER_NAME
IDENTIFIED BY PASSWORD;
E.g.
CREATE USER rohan
IDENTIFIED BY 'admin';
SQL GRANT and REVOKE COMMANDS
DCL(Data Control Language) commands are used to enforce database security in a multiple user database environment. Two types of DCL commands are GRANT and REVOKE. Only database administrator or owner of the database object can provide/remove privileges on a database object.
GRANT- GRANT is a command used to provide access or privileges on the database object to the users.
Syntax:
GRANT PRIVILEGE_NAME
ON OBJECT_NAME
TO {USER_NAME | PUBLIC | ROLE_NAME};
E.g.
GRANT ALL
ON student
TO rohan;
REVOKE- REVOKE is a command used to revoke some or all of the privileges which have been granted to a user in the past.
Syntax:
REVOKE PRIVILEGE_NAME
ON OBJECT_NAME
TO {USER_NAME | PUBLIC | ROLE_NAME};
E.g.
REVOKE ALL
ON student
TO rohan;
DELETING USER ACCOUNT- to delete a user account along with associated rights and privileges we can use DROP USER statement
Syntax:
DROP USER USER_NAME;
E.g.
DROP USER rohan;
Functions
For doing operations on data SQL has many built in functions they are categorized into 2 main categories and further subdivide in different 7 functions under each category.
The categories are:
Aggregate functions- these functions are used to do operations from the values of the column and a single value is returned.
- AVG()
- COUNT()
- FIRST()
- LAST()
- MAX()
- MIN()
- SUM()
Scalar functions- these functions are based on user input, these too returns single value.
- UCASE()
- LCASE()
- MID()
- LENGTH()
- ROUND()
- NOW()
Aggregate Functions:
AVG()- It returns average value after calculating from values in a numeric column.
Syntax:
SELECT AVG(COL_NAME) FROM table_name;
E.g.
SELECT AVG(age) FROM student;
COUNT()- it is used to count the number of rows returned in a SELECT statement
Syntax:
SELECT COUNT(COL_NAME|*) FROM table_name;
E.g.
SELECT COUNT(*) FROM student;
FIRST()- It returns the first value of the selected column.
Syntax:
SELECT FIRST(COL_NAME) FROM table_name;
E.g.
SELECT FIRST(age) FROM student;
* Note- FIRST() and LAST() aggregate functions are not supported by MySQL
LAST()- It returns the last value of the selected column.
Syntax:
SELECT LAST(COL_NAME) FROM table_name;
E.g.
SELECT LAST(age) FROM student;
* Note- FIRST() and LAST() aggregate functions are not supported by MySQL
MAX()- It returns the maximum value of the selected column.
Syntax:
SELECT MAX(COL_NAME) FROM table_name;
E.g.
SELECT MAX(age) FROM student;
MIN()- It returns the minimum value of the selected column.
Syntax:
SELECT MIN(COL_NAME) FROM table_name;
E.g.
SELECT MIN(age) FROM student;
SUM()- It returns the sum of all the values of the selected column.
Syntax:
SELECT SUM(COL_NAME) FROM table_name;
E.g.
SELECT SUM(age) FROM student;
Scalar Functions:
UCASE()- It converts the value of a field to uppercase.
Syntax:
SELECT UCASE(COL_NAME) FROM table_name;
E.g.
SELECT UCASE(name) FROM student;
LCASE()- It converts the value of a field to lowercase.
Syntax:
SELECT LCASE(COL_NAME) FROM table_name;
E.g.
SELECT LCASE(name) FROM student;
MID()- It extracts texts from the text field.
Syntax:
SELECT MID(COL_NAME, START, LENGTH) FROM table_name;
E.g.
SELECT MID(name, 1, 3) FROM student;
LENGTH()- It returns the length of the value in a text field.
Syntax:
SELECT LENGTH(COL_NAME) FROM table_name;
E.g.
SELECT LENGTH(name) FROM student;
ROUND()- It round a numeric field to the number of decimals specified.
Syntax:
SELECT ROUND(COL_NAME, DECIMALS) FROM table_name;
E.g.
SELECT ROUND(age, 2) FROM student;
NOW()- It returns the current system date and time.
Syntax:
SELECT NOW() FROM table_name;
E.g.
SELECT name, NOW() FROM student;