OPALTOGEL SITUS GAME ONLINE — SQL Keywords

April 12, 2021

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.