Summary -

In this topic, we described about the Keywords Reference with detailed example.

SQL Keywords are the reserved words which are used to perform special operations on database. SQL is case sensitive, So we can use CREATE or create both have same meaning but to increase readability we use keywords in UPPER CASE letters. SQL has many keywords, The mostly used keywords are listed as follows –


Example -

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

Keyword Description Syntax
ADD It is used to add a new column to the existing table. "ALTER TABLE table_name ADD column_name datatype;"
ADD CONSTRAINT It is used to create constraint on existing table. "ALTER TABLE table_name ADD CONSTRAINT constraint_name "
ALTER It is used to combine with TABLE or COLUMN statement. By using those we can add, delete, modify columns ALTER TABLE table_name ALTER COLUMN datatype;
ALTER COLUMN It is used to modify the columns like with new data types are to increase the range. "ALTER TABLE table_name ALTER COLUMN column_name new_data_type;"
ALTER TABLE It is used to add columns or constraints, delete columns or constraints on existing table. "ALTER TABLE table_name ADD | DROP column_name datatype;"
ALL It is used to get required column values in the result set when the sub query condition is satisfied. SELECT column FROM table_name WHERE column_name = ALL(SELECT column FROM table_name WHERE column_name = value )
AND It is used to get values by using multiple conditions. When all the condition specified are satisfied then only it returns result. SELECT column FROM table_name WHERE condition1 AND condition2;
ANY It is used to get required column values in the result set when the sub query condition is satisfied. SELECT column FROM table_name WHERE column_name = ANY(SELECT column FROM table_name WHERE column_name = value )
AS It is used to rename column or table with alias name. That alias name exists on that query only. SELECT column AS alias_column_name FROM table_name AS alias_table_name;
ASC It is used to sort data in ascending order in result set. SELECT column FROM table_name ORDER BY column ASC;
BACKUP DATABASE It is used to back up the existing database. BACKUP DATABASE database_name TO DISK = 'filepath';
BETWEEN It is used to get the values from table with in specified range. SELECT column FROM table_name WHERE column_name BETWEEN value1 AND value2;
CASE It is used to create different outputs for different conditions. CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN result ELSE result END ;
CHECK It is used to limit the value on table. We can create CHECK with multiple conditions also while creating table or on existing table. CREATE TABLE ( column1, column2, column3, column_name datatype CHECK (condition) | CONSTRAINT constraint_name CHECK (condition) );
COLUMN It is used to change the datatype of column or to delete the column in table. ALTER TABLE table_name ALTER|DROP COLUMN column_name new_data_type;
CONSTRAINT It is used to add or delete constraint on table ALTER TABLE table_name ADD|DROP CONSTRAINT constraint_name;
CREATE It is used to create a table, database, index, view. CREATE TABLE|DATABASE|VIEW|INDEX Specified_name;
CREATE DATABASE It is used to create database. CREATE DATABASE database_name;
CREATE INDEX It is used to create an index on table. CREATE INDEX index_name ON Table_name (column¬_name);
CREATE VIEW It is used to create a view or update existing view CREATE VIEW view_name AS SELECT column1,column2 FROM table_name [ WHERE condition ];
CREATE TABLE It is used to create a new table. CREATE TABLE table_name( Column1 datatype, Column2 datatype,..);
CREATE PROCEDURE It is used to create a stored procedure. CREATE PROCEDURE procedure_name AS sql_statement GO;
CREATE UNIQUE INDEX It is used to create a unique index on table. CREATE UNIQUE INDEX index_name ON table_name (column_name);
DATABASE It is used to create or delete database. CREATE |DROP DATABASE database_name;
DEFAULT It is used to create a default value for specified column on table ALTER TABLE table_name ADD CONSTRAINT DEFAULT Default_Value FOR column_name;
DELETE It is used to delete rows in the table. DELETE FROM table WHERE condition;
DESC It is used to sort data in descending order in result set. SELECT column FROM table_name ORDER BY column DESC;
DISTINCT It returns distinct values in result set from table. SELECT DISTINCT column FROM table;
DROP It is used to delete column, view, database, constraint on existing table. DROP VIEW name;
DROP COLUMN It is used to delete the existing column on table. ALTER TABLE table_name DROP column_name;
DROP CONSTRAINT It is used to delete the existing constraint on table. ALTER TABLE table_name DROP CONSTRAINT constraint_name;
DROP DATABASE It is used to delete existing database. DROP DATABASE database_name;
DROP INDEX It is used to delete existing index on table. DROP INDEX Table_name.index_name;
DROP TABLE It is used to delete existing table. DROP TABLE table_name;
DROP VIEW It is used to delete existing view. DROP VIEW view_name;
EXEC It is used to execute an existing stored procedure EXEC storedprocedure_name;
EXISTS It is used with subquery of SELECT statement. SELECT column1,column2,…. FROM table_name WHERE EXISTS (subquery);
FOREIGN KEY It is used to link two tables together with combined column. ALTER TABLE Child_table ADD [ CONSTRAINT foreignkey_name] FOREIGN KEY (childtable_column) REFERENCES parent_table ( parenttable_column);
FROM It is used to select details from table. SELECT * FROM table_name;
FULL OUTER JOIN It is used to select details from both tables. SELECT column1, column2, table2.column1, table2.column2,…. FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;
GROUP BY It is used to select details from table by using group by statement. SELECT column1, function_name (column2) FROM table_name [ WHERE condition ] GROUP BY column1, column2 [ORDER BY column1, column2 ];
HAVING It is used to get rows in result set by using HAVING clause. SELECT column1, column2, aggregate function(column3), …….. FROM Table_name [ WHERE condition ] GROUP BY column1 HAVING condition [ ORDER BY column1];
IN It is used to get rows from table by using multiple conditions. SELECT column_name1,column_name2,…… FROM table_name WHERE column_name IN (value1,value2,……);
INDEX It is used to create or delete index on table. DROP INDEX table.index;
INNER JOIN It is used to select matched rows from both tables SELECT column1, column2, table2.column1, table2.column2,…. FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
INSERT INTO It is used to insert new rows on existing table. INSERT INTO table_name (column1, column2, column3, ...columnp) VALUES (value1, value2, value3, ...valuep);
INSERT INTO SELECT It is used to copies data from one table to another table. INSERT INTO table1 (column1, column2,..)SELECT column1, column2,.. from table2 [ WHERE condition ];
IS NULL It checks for the NULL values in table. SELECT column1, column2,… FROM table_name WHERE column_name IS NULL;
IS NOT NULL It checks for the not NULL values in the table. SELECT column1, column2,… FROM table_name WHERE column_name IS NOT NULL;
JOINS It is used to select matched rows from two tables. SELECT column1, column2, table2.column1, table2.column2,…. FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name;
LEFT JOIN It is used to select all rows from left side of the join table and matched rows from right side of the join table SELECT column1, column2, table2.column1,table2.column2,…. FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
LIKE It is used to get rows in result set by searching matched pattern in LIKE operator. SELECT column1, column2, ... FROM table_name WHERE required_column LIKE pattern;
LIMIT It is used to limit the rows in result set. SELECT column_name1, column_name2,… FROM table [WHERE conditions] [ORDER BY expression [ ASC | DESC ]] LIMIT number_of_rows [ OFFSET offset_value ];
NOT It is used to get rows in result set by using NOT condition. SELECT column1, column2 FROM table_name WHERE NOT condition;
NOT NULL It is used to specify a column NOT NULL i.e. it does not accept the NULL values. ALTER TABLE table_name ALTER COLUMN column_name datatype NOT NULL;
OR It is used to get rows in result set by satisfying any one condition on OR statement. SELECT column FROM table_name WHERE condition1 OR condition2;
ORDER BY It is used to get rows in result set by sorting in either ascending or descending order. SELECT column FROM table_name ORDER BY column ASC|DESC;
OUTER JOIN It is used to get rows from both tables. SELECT column1, column2, table2.column1,table2.column2,…. FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name;
PRIMARY KEY It is used to create PRIMARY KEY on table to get distinct rows in result set. CREATE TABLE table_name ( column1, column2, column3, [CONSTRAINT primarykey_name] PRIMARYKEY (column_name) );
PROCEDURE It is used to create a stored procedure. CREATE PROCEDURE procedure_name AS sql_statement GO;
RIGHT JOIN It is used to get all rows from right side of the join table and matched rows from left side of the join table. SELECT column1, column2, table2.column1,table2.column2,…. FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name;
ROWNUM It is used to get specified number of rows from table by using ROWNUM. it is used in ORACLE. SELECT column FROM table_name WHERE ROWNUM = value;
SELECT It is used to get specified columns or rows or all columns or rows from rows from table. SELECT column FROM table_name [WHERE condition];
SELECT DISTINCT It is used to get distinct rows from table. SELECT DISTINCT column FROM table_name [WHERE condition];
SELECT TOP It is used to get selected rows in result set from table. SELECT TOP Number| Percent Column1,Column2,.. FROM Table_name [WHERE Condition];
SET It is used to update a value on table. UPDATE table_name SET column= value [WHERE condition];
TABLE It is used to create or delete table. DROP TABLE table_name;
TOP It is used to get selected rows in result set from the table SELECT TOP Number| Percent Column1,Column2,.. FROM Table_name [WHERE Condition];
TRUNCATE TABLE It is used to get delete the rows from the table, but structure of table is there. TRUNCATE TABLE Table_name;
UNION It is used to combine two SELECT statements results in result set. It does not allow duplicate values. SELECT column FROM table_name [WHERE condition]; UNION SELECT column FROM table_name [WHERE condition];
UNION ALL It is used to combine two SELECT statements results in result set. It allows duplicate values. SELECT column FROM table_name [WHERE condition]; UNION ALL SELECT column FROM table_name [WHERE condition];
UNIQUE It is used to create unique columns, indexes on table. CREATE TABLE table_name( Column1 datatype UNIQUE, Column2 datatype,..);
UPDATE It is used to update a value on table. UPDATE table_name SET column= value [WHERE condition];
VALUES It is used to insert values on existing table. INSERT INTO table_name(column1, column2,..) VALUES (value1, value2,..);
VIEW It is used create or delete existing view CREATE | DROP VIEW view_name;
WHERE It is used to select specified rows in result set. SELECT column FROM table_name WHERE condition;

Keyword example
ADD ALTER TABLE employee_details ADD email VARCHAR(20);

By executing above query, we can add a new column email to employee_details table.
ADD CONSTRAINT ALTER TABLE employee_details ADD CONSTRAINT PK_emp_id PRIMARY KEY (emp_id);

By executing above query, we can create a new constraint pk_emp_id on existing employee_details table.
ALTER ALTER TABLE employee_details DROP COLUMN email;

By executing above query, we can delete existing column on table.
ALTER COLUMN ALTER TABLE employee_details ALTER COLUMN salary FLOAT;

By executing above query, we can change the datatype of salary INT to FLOAT.
ALTER TABLE ALTER TABLE employee_details DROP CONSTRAINT PK_emp_id;

By executing above query, we can delete existing constraint on table.
ALL SELECT emp_id FROM employee_details WHERE dept_id = ALL ( SELECT dept_id FROM department_details WHERE dept_name = 'sales');

By executing above query, we can get corresponding values in result set.
AND SELECT * FROM employee_details WHERE designation = 'manager' AND salary >15000;

By executing above query, we can get details whose designation is manager and salary >15000.
ANY SELECT emp_id FROM employee_details WHERE dept_id = ANY( SELECT dept_id FROM department_details WHERE dept_name = 'sales');

By executing above query, we can get corresponding values in result set.
AS SELECT SUM(salary) AS 'Total salary' FROM employee_details;

By executing above query, we can rename the column in result set.
ASC SELECT * FROM employee_details ORDER BY emp_id ASC;

It sort data in result set by ascending order of emp_id.
BACKUP DATABASE BACKUP DATABASE demo TO 'D:\backups\demo.bak ';

By executing above query, we can back up the existing database.
BETWEEN SELECT * FROM employee_details WHERE salary BETWEEN 15000 AND 25000;

By executing above query, we can get details of employees by using BETWEEN.
CASE SELECT emp_name, salary, CASE WHEN salary > 25000 THEN 'SALARY IS GREATER THAN 25000' WHEN salary = 25000 THEN 'SALARY IS EQUAL TO 25000' ELSE 'SALARY IS UNDER 25000' END AS salarytext FROM employee_details ;

By executing above query we can get different outputs on salarytext column.
CHECK CREATE TABLE employee_details ( emp_name varchar (20) not null, emp_id int not null, dept_id int CHECK (dept_id = 1000 OR dept_id = 2000) );

By executing above query, we can create CHECK condition while creating table.
COLUMN ALTER TABLE employee_details DROP COLUMN designation;

It delete the designation column in table.
CONSTRAINT ALTER TABLE employee_details DROP CONSTRAINT PK_emp_id;

Now it delete the existing constraint on table.
CREATE CREATE TABLE demo_table( Id INT, fullname VARHAR(20) );

It creates demo_table.
CREATE DATABASE CREATE DATABASE demo;

It creates a demo database.
CREATE INDEX CREATE INDEX idx_manager_id ON employee_details (manager_id);

It creates an index on table.
CREATE VIEW CREATE VIEW employee_info AS SELECT emp_id, emp_name, designation FROM employee_details ;

It create a view.
CREATE TABLE CREATE TABLE demo_table( Id INT, fullname VARHAR(20) );

It creates demo_table.
CREATE PROCEDURE CREATE PROCEDURE selectallemployees AS SELECT * FROM employee_details GO;

It creates a stored procedure.
CREATE UNIQUE INDEX CREATE UNIQUE INDEX idx_employee ON employee_details (emp_id);

It creates a unique index.
DATABASE DROP DATABASE DEMO;

It deletes the database.
DEFAULT ALTER TABLE employee_details ADD CONSTRAINT dk_salary DEFAULT 20000 for salary;

It creates default salary for salary column on table.
DELETE DELETE FROM employee_details WHERE emp_id = 001;

It delete the emp_id is 001 details from table.
DESC SELECT * FROM employee_details ORDER BY emp_id DESC;

It sort data in result set by descending order of emp_id.
DISTINCT SELECT DISTINCT salary FROM employee_details;

It returns the distinct salary from table in result set.
DROP DROP VIEW employee;

It delete the existing view.
DROP COLUMN ALTER TABLE employee_details DROP COLUMN emp_name;

By executing above query, we can delete existing column on table.
DROP CONSTRAINT ALTER TABLE employee_details DROP CONSTRAINT PK_emp_id;

By executing above query, we can delete existing constraint on table.
DROP DATABASE DROP DATABASE DEMO;

It deletes the database.
DROP DEFAULT ALTER TABLE employee_details ALTER COLUMN salary DROP DEFAULT;

It delete the existing default value on column in table.
DROP INDEX DROP INDEX employee_details.idx_employee;

It delete the existing index on table.
DROP TABLE DROP TABLE employee_details;

It delete the existing table.
DROP VIEW DROP VIEW employee;

It delete the existing view.
EXEC EXEC selectallemployees;

It is used to calling the existing view.
EXISTS SELECT * FROM employee_details WHERE EXISTS (SELECT dept_id FROM department_details WHERE department_details.dept_id = employee_details.dept_id AND salary > 35000 );
FOREIGN KEY ALTER TABLE department ADD FOREIGN KEY ( dept_id) REFERENCES employee_details ( dept_id);

It creates foreign key on existing table.
FROM SELECT * FROM employee_details;

It is used to select details from table.
FULL OUTER JOIN SELECT emp_id, emp_name, designation, department_details.dept_name FROM employee_details FULL JOIN department_details ON employee_details.dept_id = department_details.dept_id;
GROUP BY SELECT COUNT( Emp_id ) AS 'total employees', dept_id FROM employee_details Group By dept_id;
HAVING SELECT dept_id, COUNT (emp_id) AS 'Total Employees' FROM employee_details GROUP BY dept_id HAVING COUNT (emp_id) > 2;
IN SELECT emp_id, emp_name, designation FROM employee_details WHERE Emp_id IN (003, 004);
INDEX DROP INDEX employee_details.idx_employee;

It delete the existing index on table.
INNER JOIN SELECT emp_id, emp_name, designation, department_details.dept_name FROM employee_details INNER JOIN department_details employee_details.dept_id = department_details.dept_id;
INSERT INTO INSERT INTO employee_details (emp_id, emp_name, designation, date_of_hire, salary, dept_id ) VALUES ( 001, 'Employee1',' Director', '2019-11-07', 45000.00, 1000);
INSERT INTO SELECT INSERT INTO employee_info SELECT emp_id, emp_name, designation, manager_id, date_of_hire, salary, dept_id FROM employee_details;
IS NULL SELECT * FROM employee_details WHERE manager_id IS NULL;
IS NOT NULL SELECT * FROM employee_details WHERE manager_id IS NOT NULL;
JOINS SELECT emp_id, emp_name, designation, department_details.dept_name FROM employee_details INNER JOIN department_details employee_details.dept_id = department_details.dept_id;
LEFT JOIN SELECT emp_id, emp_name, designation, salary, department_details.dept_name FROM employee_details LEFT JOIN department_details ON employee_details.dept_id = department_details.dept_id;
LIKE SELECT * FROM employee_details WHERE salary LIKE '15%';
LIMIT SELECT * FROM employee_details LIMIT 3;
NOT SELECT * FROM employee_details WHERE NOT dept_id = 1000;
NOT NULL ALTER TABLE employee_details ALTER COLUMN dept_id NOT NULL;
OR SELECT * FROM employee_details WHERE designation = 'manager' OR salary >15000;
ORDER BY SELECT * FROM employee_details ORDER BY emp_id DESC;
OUTER JOIN SELECT emp_id, emp_name, designation, department_details.dept_name FROM employee_details FULL JOIN department_details ON employee_details.dept_id = department_details.dept_id;
PRIMARY KEY CREATE TABLE employee_details ( emp_name varchar (20) not null, dept_id int, emp_id int not null, PRIMARY KEY (emp_id) );
PROCEDURE CREATE PROCEDURE selectallemployees AS SELECT * FROM employee_details GO;

It creates a stored procedure.
RIGHT JOIN SELECT emp_id, emp_name, designation, salary, department_details.dept_name FROM employee_details RIGHT JOIN department_details ON employee_details.dept_id = department_details.dept_id;
ROWNUM SELECT * FROM employee_details WHERE ROWNUM = 3;
SELECT SELECT * FROM employee_details;
SELECT DISTINCT SELECT DISTINCT salary FROM employee_details;
SELECT INTO SELECT emp_name, designation INTO employee FROM employee_details;
SELECT TOP SELECT TOP 4 * FROM employee_details ;
SET UPDATE employee_details SET salary = 20000 WHERE emp_id = 001;
TABLE DROP TABLE employee;
TOP SELECT TOP 4 * FROM employee_details ;
TRUNCATE TABLE TRUNCATE TABLE employee_details ;
UNION SELECT salary FROM employee_details UNION SELECT emp_name FROM employee_details;
UNION ALL SELECT salary FROM employee_details UNION ALL SELECT emp_name FROM employee_details;
UNIQUE CREATE TABLE demo_table( Id INT UNIQUE, fullname VARHAR(20) );
UPDATE UPDATE employee_details SET salary = 20000 WHERE emp_id = 001;
VALUES INSERT INTO employee_details (emp_id, emp_name, designation, date_of_hire, salary, dept_id ) VALUES ( 001, 'Employee1',' Director', '2019-11-07', 45000.00, 1000);
VIEW DROP VIEW employee;

It delete the existing view.
WHERE SELECT * FROM employee_details WHERE dept_id = 1000;