SQL Keywords are the reserved words that are used to perform various operations in the database. These Keywords are case insensitive. For example, SELECT and select has the same meaning in SQL.
List of SQL Keywords
The available SQL Keywords can be shown in the following examples.
#1. CREATE
The CREATE keyword is used to create database, table, view, stored procedure, trigger, index, etc. Following is the example of creating a table employee using CREATE keyword.
CREATE TABLE employee ( EmpId integer PRIMARY KEY, EmpName varchar(50), Address varchar(50), Age integer, Salary decimal(10,2) );
The CREATE DATABASE is used to create a database in SQL as below.
CREATE DATABASE database_name;
A View in SQL can be created using CREATE keyword as follows.
CREATE VIEW VIEW_NAME AS SELECT COLUMN1, COLUMN2, COLUMN3... FROM table_name WHERE [CONDITION];
#2. INSERT
The INSERT keyword is used to insert data into the table in SQL. We can insert few records into the already created employee table as below.
INSERT INTO employee values (101,'Sagar Sharma','Kolkata',30,80000); INSERT INTO employee values (102,'Ramesh Patil','Mumbai',35,65000); INSERT INTO employee values (103,'Piyush Roy','Siliguri',36,40000); INSERT INTO employee values (104,'Sneha Sharma','Delhi',28,70000); INSERT INTO employee values (105,'Shankar Kumar','Delhi',32,48000);
The above statements insert records into the employee table. Now, we can see the records of employee tables using a simple SELECT statement as below.
SELECT * FROM employee;
| EmpId | EmpName | Address | Age | Salary |
| 101 | Sagar Sharma | Kolkata | 30 | 80000 |
| 102 | Ramesh Patil | Mumbai | 35 | 65000 |
| 103 | Piyush Roy | Siliguri | 36 | 40000 |
| 104 | Sneha Sharma | Delhi | 28 | 70000 |
| 105 | Shankar Kumar | Delhi | 32 | 48000 |
#3. SELECT
The SELECT keyword is used to display the records from table or view. The '*' is used in the SELECT statement to select all the columns of the table or view.
SELECT EmpName FROM employee;
The above query returns only the EmpName column from the employee table.
| EmpName |
| Sagar Sharma |
| Ramesh Patil |
| Piyush Roy |
| Sneha Sharma |
| Shankar Kumar |
#4. FROM
The FROM keyword indicates the table from which the data to be selected, deleted or updated.
#5. ALTER
The ALTER keyword is used to modify the column in a table. The ALTER TABLE modifies the table columns by adding new column or by deleting existing columns and ALTER COLUMN modifies the datatype of the column.
We can modify the columns of employee table by adding a new column such as hobbies.
ALTER TABLE employee ADD Hobbies varchar(20); SELECT * FROM employee;
The above ALTER TABLE statement adds a new column in the employee table 'Hobbies' with all the value as NULL. Also here we have used another SQL keyword ADD.
| EmpId | EmpName | Address | Age | Salary | Hobbies |
| 101 | Sagar Sharma | Kolkata | 30 | 80000 | Null |
| 102 | Ramesh Patil | Mumbai | 35 | 65000 | Null |
| 103 | Piyush Roy | Siliguri | 36 | 40000 | Null |
| 104 | Sneha Sharma | Delhi | 28 | 70000 | Null |
| 105 | Shankar Kumar | Delhi | 32 | 48000 | Null |
#6. ADD
The ADD keyword is used to add a new column in the existing table.
#7. DISTINCT
The DISTINCT keyword is used to select distinct values from any column. SELECT DISTINCT can be used to select only the distinct values from any table.
If you notice, you will find duplicate Address value for Sneha Sharma and Shankar Kumar as Delhi. Now if you want to select only the distinct addresses from the employee table, you can use SELECT DISTINCT as follows.
SELECT DISTINCT Address FROM employee;
The output will be as below.
| Address |
| Kolkata |
| Mumbai |
| Siliguri |
| Delhi |
#8. UPDATE
The UPDATE keyword is used to update an existing value in any SQL table.
For example, if you want to update the address of Piyush Roy to Chennai, you can use UPDATE keyword as below.
UPDATE employee SET Address='Chennai' WHERE EmpId=103; SELECT * FROM employee;
| EmpId | EmpName | Address | Age | Salary | Hobbies |
| 101 | Sagar Sharma | Kolkata | 30 | 80000 | Null |
| 102 | Ramesh Patil | Mumbai | 35 | 65000 | Null |
| 103 | Piyush Roy | Chennai | 36 | 40000 | Null |
| 104 | Sneha Sharma | Delhi | 28 | 70000 | Null |
| 105 | Shankar Kumar | Delhi | 32 | 48000 | Null |
#9. SET
This keyword is used to specify the columns which need to be updated.
#10. DELETE
The DELETE keyword deletes records from the existing table.
Suppose you want to remove the record of "Shankar Kumar" from the employee table. You can use DELETE keyword as below.
DELETE FROM employee WHERE EmpId=105; SELECT * FROM employee;
| EmpId | EmpName | Address | Age | Salary | Hobbies |
| 101 | Sagar Sharma | Kolkata | 30 | 80000 | Null |
| 102 | Ramesh Patil | Mumbai | 35 | 65000 | Null |
| 103 | Piyush Roy | Siliguri | 36 | 40000 | Null |
| 104 | Sneha Sharma | Delhi | 28 | 70000 | Null |
On the other hand, if you want delete all the records from the employee table, you can use DELETE keyword without using the WHERE caluse.
DELETE FROM employee;
#11. TRUNCATE
The TRUNCATE keyword removes all the records from an existing table, however the structure of the table will be preserved.
TRUNCATE TABLE employee;
The above query deletes all the rows from the employee table but the table structure will not be deleted. As a result, there is no need to create the structure of the table again.
#12. AS
The AS keyword used as a alias to rename column name in the result set or rename a table while query processing. Table name aliasing is useful while joining multiple table or self join. On the other hand column aliasing is useful to give meaning name for a table column in the result set.
SELECT EmpId AS "EMPLOYEE ID", EmpName AS "Employee Name" from employee;
| Employee ID | Employee Name |
| 101 | Sagar Sharma |
| 102 | Ramesh Patil |
| 103 | Piyush Roy |
| 104 | Sneha Sharma |
| 105 | Shankar Kumar |
#13. ORDER BY
The ORDER BY Keywords is used to sort the result set in ascending or descending order. By default, the result-set is sorted in ascending order if ASC or DESC is not supplied.
SELECT EmpId, EmpName, Salary from employee order by Salary;
| EmpId | EmpName | Salary |
| 103 | Piyush Roy | 40000 |
| 105 | Shankar Kumar | 48000 |
| 102 | Ramesh Patil | 65000 |
| 104 | Sneha Sharma | 70000 |
| 101 | Sagar Sharma | 80000 |
#14. ASC
This Keyword is used to sort the result set in ascending order. Note that this the default behavior of the ORDER BY clause.
SELECT EmpId, EmpName, Salary from employee order by Salary ASC;
| EmpId | EmpName | Salary |
| 103 | Piyush Roy | 40000 |
| 105 | Shankar Kumar | 48000 |
| 102 | Ramesh Patil | 65000 |
| 104 | Sneha Sharma | 70000 |
| 101 | Sagar Sharma | 80000 |
#15. DESC
This keyword is used to sort the result set in descending order.
Suppose you want to sort the employee by their salary in descending order. In this case you have to use DESC keyword along with the ORDER BY clause.
SELECT EmpId, EmpName, Salary from employee order by Salary DESC;
| EmpId | EmpName | Salary |
| 101 | Sagar Sharma | 80000 |
| 104 | Sneha Sharma | 70000 |
| 102 | Ramesh Patil | 65000 |
| 105 | Shankar Kumar | 48000 |
| 103 | Piyush Roy | 40000 |
#16. BETWEEN
The BETWEEN keyword is used to select the records from a table or view in a given range.
For example, if you want select the name of the employee wh0se salary is greater than equal to 50000 and less than equal to 70000, you can use BTWEEN keyword to filter out the data as below.
SELECT EmpName from employee where Salary BETWEEN 50000 AND 70000;
| EmpId | EmpName | Salary |
| 102 | Ramesh Patil | 65000 |
| 104 | Sneha Sharma | 70000 |
#17. WHERE
The WHERE is the one of most popular keyword in SQL which is used to filter out the data from the result set based on the condition specified in the WHERE clause. Only the records satisfying the condition mentioned in the WHERE clause, include in the result-set.
Suppose, you want to check the employee details whose ID is 105, then you can use the following query.
SELECT * FROM employee WHERE EmpId=105;
| EmpId | EmpName | Address | Age | Salary | Hobbies |
| 105 | Shankar Kumar | Delhi | 32 | 48000 | Null |
#18. AND
This keyword is used along with the WHERE clause to provide additional condition for selecting data from the table. Both the conditions need to be true for inclusion of the data into the result set.
SELECT * FROM employee WHERE Location='Delhi' AND Salary > 50000;
The above query gives two conditions, first one the location of the employee is Delhi and the Salary should be more than 50000. If both the condition is satisfied by any records from the employee table will be select.
Output of the above query is as below.
| EmpId | EmpName | Address | Age | Salary | Hobbies |
| 104 | Sneha Sharma | Delhi | 28 | 70000 | Null |
#19. OR
This keyword is used along with the WHERE clause to provide additional condition and either of the condition can be true for selecting the records in the result-set.
SELECT * FROM employee WHERE Location='Delhi' OR Salary > 50000;
The above query returns the records which satisfy any one of the condition given in the WHERE clause. Below if the output.
| EmpId | EmpName | Address | Age | Salary | Hobbies |
| 104 | Sneha Sharma | Delhi | 28 | 70000 | Null |
| 105 | Shankar Kumar | Delhi | 32 | 48000 | Null |
#20. NOT
The NOT keyword used in the WHERE clause to include those records which are not satisfied the condition.
The below query will select all the records from the employee table except “Delhi”.
SELECT * FROM employee WHERE NOT Address='Delhi';
| EmpId | EmpName | Address | Age | Salary | Hobbies |
| 101 | Sagar Sharma | Kolkata | 30 | 80000 | Null |
| 102 | Ramesh Patil | Mumbai | 35 | 65000 | Null |
| 103 | Piyush Roy | Siliguri | 36 | 40000 | Null |
#21. LIMIT
The LIMIT keyword limits the number of records in the result set based on the value provided.
SELECT * FROM employee LIMIT 3;
The above query limits the rows into 3 from the employee table as we have used LIMIT 3 option in the select query.
| EmpId | EmpName | Address | Age | Salary | Hobbies |
| 101 | Sagar Sharma | Kolkata | 30 | 80000 | Null |
| 102 | Ramesh Patil | Mumbai | 35 | 65000 | Null |
| 103 | Piyush Roy | Siliguri | 36 | 40000 | Null |
#22. IS NULL
This IS NULL keyword checks the null values in the column.
The below query will returns all the records for which Hobbies has NULL values.
SELECT * FROM employee WHERE Hobbies IS NULL;
#23. IS NOT NULL
The IS NOT NULL keyword checks the not null values in the column. This is just opposite to the IS NULL keyword.
The below query will returns all the records for which Address has NOT NULL values.
SELECT * FROM employee WHERE Address IS NOT NULL;
| EmpId | EmpName | Address | Age | Salary | Hobbies |
| 101 | Sagar Sharma | Kolkata | 30 | 80000 | Null |
| 102 | Ramesh Patil | Mumbai | 35 | 65000 | Null |
| 103 | Piyush Roy | Siliguri | 36 | 40000 | Null |
| 104 | Sneha Sharma | Delhi | 28 | 70000 | Null |
| 105 | Shankar Kumar | Delhi | 32 | 48000 | Null |
#24. DROP
The DROP keyword is used to drop database, table, view, index, column, etc.
#25 DROP DATABASE
The DROP DATABASE keywords drop a database from the system.
DROP DATEBASE database_name;
#26. DROP TABLE
This keyword is used to delete an existing table from the database.
DROP TABLE TABLE_NAME;
We can drop the employee table using DROP TABLE keyword as follows.
DROP TABLE employee;
Note that we need to very careful before issuing any DROP TABLE statement as this will remove all the data along with the definition of the table.
#27. DROP COLUMN
You can drop an existing column from a table using DROP COLUMN keyword along with the ALTER TABLE statement in SQL.
We can remove Hobbies column from the employee table as below.
ALTER TABLE employee DROP COLUMN Hobbies; SELECT * FROM employee;
| EmpId | EmpName | Address | Age | Salary |
| 101 | Sagar Sharma | Kolkata | 30 | 80000 |
| 102 | Ramesh Patil | Mumbai | 35 | 65000 |
| 103 | Piyush Roy | Siliguri | 36 | 40000 |
| 104 | Sneha Sharma | Delhi | 28 | 70000 |
| 105 | Shankar Kumar | Delhi | 32 | 48000 |
We can see from the above result that Hobbies column has been deleted.
#28. GROUP BY
This keyword is used along with the aggregate functions like COUNT, MAX, MIN, AVG, SUM, etc. and groups the result set.
The below query will group the Address according to the various states.
SELECT COUNT(Address), Address FROM employee GROUP BY Address;
| Count(Address) | Address |
| 1 | Kolkata |
| 1 | Mumbai |
| 1 | Siliguri |
| 2 | Delhi |
#29. HAVING
This keyword is used with aggregate functions and GROUP BY instead of the WHERE clause to filter the values of a result set.
SELECT COUNT(*), Address FROM employee GROUP BY Address HAVING COUNT(Address)>=2;
| Count(*) | Address |
| 2 | Delhi |
The above query filters the result set and shows only those values which satisfy the condition given in the HAVING clause.
In our case, the above result set shows the values for which the count of the Address is greater than equal to 2.
#30. IN
The IN keyword is used along with the WHERE clause to specify more than one value or we can say it is used instead of multiple OR clause in a SQL statement.
The below query will select the employee details from the Address Kolkata, Mumbai, Siliguri.
SELECT * FROM employee where Address IN ('Kolkata','Mumbai','Siliguri');
| EmpId | EmpName | Address | Age | Salary | Hobbies |
| 101 | Sagar Sharma | Kolkata | 30 | 80000 | Null |
| 102 | Ramesh Patil | Mumbai | 35 | 65000 | Null |
| 103 | Piyush Roy | Siliguri | 36 | 40000 | Null |
#31. JOIN
The JOIN keyword is used to combine the rows from two or more tables. Join works on the related columns among different tables. The JOIN can be different types such as INNER, LEFT, OUTER, FULL, etc.
Consider the below employee and department table.
| EmpId | EmpName | Address | Age | Salary | DeptId |
| 101 | Sagar Sharma | Kolkata | 30 | 80000 | 1 |
| 102 | Ramesh Patil | Mumbai | 35 | 65000 | 2 |
| 103 | Piyush Roy | Siliguri | 36 | 40000 | 1 |
| 104 | Sneha Sharma | Delhi | 28 | 70000 | 3 |
| 105 | Shankar Kumar | Delhi | 32 | 48000 | 4 |
| DeptId | DeptName |
| 1 | Admin |
| 2 | Sales |
| 3 | IT |
| 4 | Marketing |
| 5 | HR |
The following query perform a INNER JOIN between employee and department table.
SELECT e.EmpId, e.EmpName, d.DeptName FROM employee e INNER JOIN department d on e.DeptId = d.DeptId;
The above query joins employee and department table on the common column DeptId and displays only those result which are presents in both the tables.
| EmpId | EmpName | DeptName |
| 101 | Sagar Sharma | Admin |
| 102 | Ramesh Patil | Sales |
| 103 | Piyush Roy | Admin |
| 104 | Sneha Sharma | IT |
| 105 | Shankar Kumar | Marketing |
The above result set shows that all the matching rows from both the table selected i.e. all the employee from the left table selected from the employee table however all department not selected from the table as no employee present to that department.
#32. UNION
The UNION keyword used to combine distinct values of two or more select statement.
SELECT DeptId FROM employee UNION SELECT DeptId FROM department;
The above query will produce the below result.
| DeptId |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
#33. UNION ALL
The UNION ALL keyword combines two or more select statements but allows duplicate values.
SELECT DeptId FROM employee UNION ALL SELECT DeptId FROM department;
The above query select rows from both the select query and retain the duplicate values.
| DeptId |
| 1 |
| 2 |
| 1 |
| 3 |
| 4 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
#34. EXISTS
The EXISTS keyword checks if a particular record present exist in the sub-query.
SELECT EmpName FROM employee WHERE EXISTS (SELECT * FROM department where employee.DeptId = department.DeptId);
The above query gives the below result.
| EmpName |
| Sagar Sharma |
| Ramesh Patil |
| Piyush Roy |
| Sneha Sharma |
| Shankar Kumar |
#35. LIKE
The LIKE keyword is used along with the WHERE clause to search a particular pattern. Wildcard % is used to search for a pattern.
SELECT EmpName from employee where EmpName like 'S%';
The above shows the below result once executed.
| EmpName |
| Sneha Sharma |
| Shankar Kumar |
#36. CASE
The CASE keyword is used to display different output according to different conditions.
SELECT EmpId, EmpName, Address CASE WHEN Address = 'Kolkata' THEN "Address is Kolkata" CASE WHEN Address = 'Mumbai' THEN "Address is Mumbai" CASE WHEN Address = 'Siliguri' THEN "Address is Siliguri" ELSE "Address is Delhi" AS "Employee Address" FROM employee;
| EmpId | EmpName | Address |
| 101 | Sagar Sharma | Address is Kolkata |
| 102 | Ramesh Patil | Address is Mumbai |
| 103 | Piyush Roy | Address is Siliguri |
| 104 | Sneha Sharma | Address is Delhi |
| 105 | Shankar Kumar | Address is Delhi |
Summary: In this tutorial, you have learned about SQL Keywords and examples of the most used SQL keyword. Apart from the above discussed keywords, other keywords such as DEFAULT used for default value for a column, UNIQUE used for ensuring unique value for a column.