Trending September 2023 # Learn Top 36 Keywords In Sql With Examples # Suggested October 2023 # Top 14 Popular

You are reading the article Learn Top 36 Keywords In Sql With Examples updated in September 2023 on the website Nhahang12h.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Learn Top 36 Keywords In Sql With Examples

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.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

List of SQL Keywords

The examples below explain that SQL keywords can be used 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 into a table. We can insert the rows below to the already created CUSTOMER table using the queries 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 below.

5. FROM

The keyword indicates 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.

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’.

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 now has 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 in 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 conditions are true.

SELECT * FROM CUSTOMER WHERE STATE ='Punjab' AND CUST_ID= 256;

The above query will give the result as mentioned 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 if any of the given conditions are true, that gets included in the result set.

21. NOT

The keyword NOT uses 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 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 using a DROP COLUMN and 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 must be careful while using the DROP TABLE as it will remove the table definition, all the data, 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.

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 query below will select the records for the states Maharashtra, Punjab, and UP by using the IN keyword.

SELECT * FROM CUSTOMER WHERE STATE IN ('Maharashtra','Punjab','UP');

The above result shows the usage of IN keyword, which selects the records only for the states specified within the IN clause.

31. JOIN

The keyword JOIN combines 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 tables.

This result shows the matching records for cust_id 121,908 and 178, which are common in both 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 combines 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 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 outputs 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 that become very useful while making changes after the design is completed.