Introduction to SQL Keywords
In SQL, the keywords are the reserved words that are used to perform various operations in the database. There are many keywords in SQL and as SQL is case insensitive, it does not matter if we use for example SELECT or select.
List of SQL Keywords
The SQL keywords can be used as explained in the below examples for various operations.
1. CREATE
The CREATE Keyword is used to create a database, table, views, and index. We can create the table CUSTOMER as below.
CREATE TABLE CUSTOMER (CUST_ID INT PRIMARY KEY, NAME VARCHAR(50), STATE VARCHAR(20));
2. PRIMARY KEY
This keyword uniquely identifies each of the records.
A Database in SQL can be created with the usage of CREATE DATABASE statement as below:
CREATE DATABASE DATABASE_NAME;
A View in SQL can be created by using CREATE VIEW as below:
CREATE VIEW VIEW_NAME AS
SELECT COLUMN1, COLUMN2, COLUMN3...
FROM TABLE_NAME WHERE [CONDITION];
3. INSERT
The INSERT Keyword is used to insert the rows of data to a table. We can insert the below rows to the already created CUSTOMER table by using the queries as below.
INSERT INTO CUSTOMER VALUES (121,'Rajesh','Maharashtra');
INSERT INTO CUSTOMER VALUES(256,'Leela','Punjab');
INSERT INTO CUSTOMER VALUES(908,'Priya','Jharkhand');
INSERT INTO CUSTOMER VALUES(787,'Rohit','UP');
The above statements will insert the rows to the table “CUSTOMER”. We can see the result by using a simple SELECT statement below
SELECT * FROM CUSTOMER;
4. SELECT
This keyword is used to select the data from the database or table. The ‘*’ is used in the select statement to select all the columns in a table.
SELECT NAME FROM CUSTOMER;
The result of the above query will display the column NAME from the CUSTOMER table as below
5. FROM
The keyword is used to indicate the table from which the data is selected or deleted.
6. ALTER
The Keyword ALTER is used to modify the columns in tables. The ALTER COLUMN statement modifies the data type of a column and the ALTER TABLE modifies the columns by adding or deleting them.
We can modify the columns of the CUSTOMER table as below by adding a new column “AGE”.
ALTER TABLE CUSTOMER ADD AGE INT;
SELECT * FROM CUSTOMER;
This query above will add the new column “AGE” with values for all the rows as null. Also, the above statement uses another SQL keyword ‘ADD’.
4.5 (4,249 ratings)
View Course
7. ADD
This is used to add a column to the existing table.
8. DISTINCT
The keyword DISTINCT is used to select distinct values. We can use SELECT DISTINCT to select only the distinct values from a table.
Let us add a duplicate value for the state Punjab as below:
INSERT INTO CUSTOMER VALUES(178, 'Pooja', 'Punjab','null');
The customer table has now the below rows
Now we can see the distinct values for the column STATE by using the below query:
SELECT DISTINCT(STATE) FROM CUSTOMER;
9. UPDATE
This keyword is used in an SQL statement to update the existing rows in a table.
UPDATE CUSTOMER SET STATE ='Rajasthan' WHERE CUST_ID= 121;
SELECT * FROM CUSTOMER;
The CUST_ID with value 121 is updated with a new state Rajasthan.
10. SET
This Keyword is used to specify the column or values to be updated.
11. DELETE
This is used to delete the existing rows from a table.
DELETE FROM CUSTOMER WHERE NAME='Rajesh';
The above query will display the below as the row with Name as Rajesh is deleted from the result set.
While using the DELETE keyword, if we do not use the WHERE clause, all the records will be deleted from the table.
DELETE FROM CUSTOMER;
The above query will delete all the records of the CUSTOMER table.
12. TRUNCATE
This is used to delete the data in a table, but it does not delete the structure of the table.
TRUNCATE TABLE CUSTOMER;
The above query only deletes the data but the structure of the table remains. So there is no need to re-create the table.
13. AS
The Keyword AS is used as an alias to rename the column or table.
SELECT CUST_ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME FROM CUSTOMER;
The above statement will create the alias for the columns CUST_ID and NAME as below:
14. ORDER BY
This is used to sort the result in descending or ascending order. This sorts the result by default in ascending order.
15. ASC
This keyword is used for sorting the data returned by the SQL query in ascending order.
SELECT * FROM CUSTOMER ORDER BY NAME ASC;
The above query will select all the columns from the CUSTOMER table and sorts the data by the NAME column in ascending order.
16. DESC
This keyword is to sort the result set in descending order.
SELECT * FROM CUSTOMER ORDER BY CUST_ID DESC;
The above query will sort all the selected fields of the table with the descending order of CUST_ID.
17. BETWEEN
This keyword is used to select values within a given range. The below query uses the BETWEEN keyword to select the CUST_ID and NAME within a given range of values for the CUST_ID.
SELECT CUST_ID, NAME FROM CUSTOMER WHERE CUST_ID BETWEEN 100 AND 500;
The above query will give the below result
18. WHERE
This keyword is used to filter the result set so that only the values satisfying the condition are included.
SELECT * FROM CUSTOMER WHERE STATE ='Punjab';
The above query selects all the values from the table for which the state is Punjab.
19. AND
This keyword is used along with the WHERE clause to select the rows for which both the conditions are true.
SELECT * FROM CUSTOMER WHERE STATE ='Punjab' AND CUST_ID= 256;
The above query will give the result as below
But if one of the conditions is not satisfied, then the query will not return any result as stated in the below query.
SELECT * FROM CUSTOMER WHERE STATE ='Punjab' AND CUST_ID= 121;
20. OR
This is used with the WHERE clause to include the rows in the result set in case of either condition is true.
The below SQL statement will select the fields from the CUSTOMER table if the state is Punjab or UP.
SELECT * FROM CUSTOMER WHERE STATE='Punjab' OR STATE='UP';
In the case of the OR keyword, we can see from the above result that in case of any of the given conditions are true, that gets included in the result set.
21. NOT
The keyword NOT is used with a WHERE clause to include the rows in the result set where a condition is not true.
We can use the NOT keyword in the below query to not include the rows from the state Punjab as below.
SELECT * FROM CUSTOMER WHERE NOT STATE = 'Punjab';
The query will return the rows with the other states excluding Punjab in the result set as below:
22. LIMIT
This keyword retrieves the records from the table in order to limit them based on the limit value.
SELECT * FROM CUSTOMER LIMIT 3;
The above query will select the records from the table CUSTOMER but it will display only the 3 rows of data from the table as below
23. IS NULL
The keyword IS NULL is used to check for NULL values.
The below query will show all the records for which the AGE column has NULL values.
SELECT * FROM CUSTOMER WHERE AGE IS NULL;
IS NOT NULL
This is used to search the NOT NULL values.
SELECT * FROM CUSTOMER WHERE STATE IS NOT NULL;
As the column STATE has no null values, the above query will show the below result.
24. DROP
The DROP keyword can be used to delete a database, table, view, column, index, etc.
25. DROP COLUMN
We can delete an existing column in a table by using a DROP COLUMN along with an ALTER statement. Let us delete the column AGE by using the below query.
ALTER TABLE CUSTOMER DROP COLUMN AGE;
We can see that in the above result, the AGE column is dropped.
26. DROP DATABASE
A database in SQL can be deleted by using the DROP DATABASE statement.
DROP DATABASE DATABASE_NAME;
27. DROP TABLE
A table in SQL can be deleted by using a DROP TABLE statement.
DROP TABLE TABLE_NAME;
We can delete the table CUSTOMER by using the DROP TABLE keyword as below.
But we need to be careful while using DROP TABLE as it will remove the table definition along with all the data and indexes etc.
28. GROUP BY
This 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 CUST_ID according to the various states.
SELECT COUNT(CUST_ID),STATE FROM CUSTOMER GROUP BY STATE;
The result shows the count of different CUST_ID grouped by states.
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(CUST_ID),STATE FROM CUSTOMER GROUP BY STATE HAVING COUNT(CUST_ID)>=2;
The above query will filter the result set by displaying only those values which satisfy the condition given in the HAVING clause.
The above result set shows the values for which the count of the customer ids is more than 2.
30. IN
The IN keyword is used within a WHERE clause to specify more than 1 value or we can say that it can be used instead of the usage of multiple OR keyword in a query.
The below query will select the records for the states Maharashtra, Punjab and UP by the use of the IN keyword.
SELECT * FROM CUSTOMER WHERE STATE IN ('Maharashtra','Punjab','UP');
The above result set shows the usage of IN keyword which selects the records only for the states specified within the IN clause.
31. JOIN
The keyword JOIN is used to combine the rows between two or more tables with related columns among the tables. The JOIN can be INNER, LEFT, RIGHT, OUTER JOIN, etc.
Lets us take another table ‘CUST_ORDER’ as an example.
We can perform an inner join of the CUSTOMER and CUST_ORDER tables as below
SELECT CUSTOMER.NAME, CUSTOMER.STATE, CUST_ORDER.ITEM_DES
FROM CUSTOMER INNER JOIN CUST_ORDER
ON CUSTOMER.CUST_ID =CUST_ORDER.ID;
The above query will join the two tables CUSTOMER and CUST_ORDER on the columns CUST_ID and ID and display only the values which are present in both the tables.
This result shows the matching records for cust_id 121,908 and 178 which are common in both the tables. But the other cust_ids are excluded as they are not present in the CUST_ORDER table. Similarly, the other JOINs can be performed.
32. UNION
The UNION keyword is used to combine the distinct values of two or more select statements.
SELECT CUST_ID FROM CUSTOMER UNION SELECT ID FROM CUST_ORDER;
The above query will show the below result.
33. UNION ALL
This keyword combines two or more select statements but allows duplicate values.
SELECT CUST_ID FROM CUSTOMER UNION ALL SELECT ID FROM CUST_ORDER;
The above result shows that UNION ALL allows the duplicate values which would not be present in the case of UNION.
34. EXISTS
The keyword EXISTS checks if a certain record exists in a sub-query.
SELECT NAME FROM CUSTOMER WHERE EXISTS (SELECT ITEM_DES FROM CUST_ORDER WHERE CUST_ID = ID);
The above query will return true as the sub-query returns the below values.
35. LIKE
This keyword is used to search along with a WHERE clause for a particular pattern. Wildcard % is used to search for a pattern.
In the below query let us search for a pattern ‘ya’ which occurs in the column ‘NAME’.
SELECT NAME FROM CUSTOMER WHERE NAME LIKE '%ya';
36. CASE
This keyword is used to display different output according to different conditions.
SELECT CUST_ID, NAME,
CASE WHEN STATE = 'Punjab' THEN "State is Punjab"
ELSE "State is NOT Punjab"
END AS Output
FROM CUSTOMER;
A few other keywords are DEFAULT used to provide a default value for a column, UNIQUE used to ensure all the values in a column are unique, etc.
Conclusion
The various keywords in SQL provide flexibility in designing a database, tables, etc. They provide the designer with many features which becomes very useful while making any changes after the design is completed.
Recommended Articles
This has been a guide to SQL Keywords. Here we have discuss the introduction and different Keywords in SQL. You may also have a look at the following articles to learn more –
