What keyword is used to specify the tables and views used in a SELECT statement?

SQL KEYWORDS

The list of SQL keywords that are available in SQL.

1. ADD

ADD keyword is used to add a column to the existing table.

2. ADD CONSTRAINT

This keyword is used to create a constraint after a table is already created.

3. ALL

ALL, it returns TRUE, if all the mentioned sub-queries values meet the conditions.

4. ALTER TABLE

It is used to add, modify or delete columns in the table, along with the modification in the table, it can alter the various constraints in the table.

Syntax:

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE CSE_BRANCH
ADD COLLEGE_ID varchar(255);

In the above example, the command is altering the table CSE_BRANCH, and a column COLLEGE_ID with the datatype varchar.

5. ALTER COLUMN

It changes the datatype of the specific column in the table.

Syntax:

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

Example:

ALTER TABLE CSE_BRANCH
ALTER COLUMN YEAR_OF_GRADUATION year;

We are altering the datatype of the YEAR_OF_GRADUATION column of table CSE_BRANCH to year.

6. AND

AND keyword is used with the WHERE clause, and checks if both the conditions given in the WHERE clause are TRUE.

Syntax:

SELECT column_name FROM table_name
WHERE (condition) AND (condition);

Example:

SELECT * FROM CSE_BRANCH
WHERE YEAR_OF_GRADUATION = 2022 AND CGPA >= 8.0;

The above example is showing that we are selecting all the columns from table CSE_BRANCH whose CGPA is greater than 8.0, and their graduation is 2022.

7. ANY

It is used in where clause, and checks if any sub-query meets the condition then return TRUE.

Syntax:

SELECT column_name FROM table_name
WHERE ANY (sub_query);

Example:

SELECT * FROM CSE_BRANCH
WHERE CGPA = ANY (SELECT CGPA FROM CSE_BRANCH WHERE YEAR_OF_GRADUAION=2023);

8. AS

It is used as an alias. It is used to rename a column or a table.

Syntax:

SELECT column_name AS alias_name FROM table_name;

Example:

SELECT YEAR_OF_GRADUATION AS YEAR FROM CSE_BRANCH;

9. ASC

It helps us to sort the result in ascending order.

10. BETWEEN

The Between command is used to select the values within a specified range. The values can be numbers, text, or dates. The beginning and the ending value are inclusive(included) in between commands.

Syntax:

SELECT column_name FROM table_name
WHERE CGPA BETWEEN begin AND end;

Example:

SELECT * FROM CSE_BRANCH
WHERE CGPA BETWEEN 6.5 AND 7.5;

The above code will return a table in which CGPA lies between 6.5 and 7.5.

11. CASE

It is used to display different outputs based on different conditions.

Syntax:

CASE
    WHEN (condition) THEN "STATEMENT"
    ELSE "STATEMENT"

END

Example:

SELECT * FROM CSE_BRANCH
ORDER BY (CASE
            WHEN CGPA < 4.0 THEN ROLL_NUMBER
            ELSE CGPA
         END);

The above SQL query will return a table which is ordered by roll number if CGPA is less than 4, otherwise the table will be ordered by CGPA.

Check out this article on the CASE Statement to learn more about CASE in SQL.

12. CHECK

This constraint limits the value that can be placed in a column.

13. CREATE

This CREATE keyword is used to create a database, table, views, and index.

Syntax:

CREATE TABLE table_name (parameters);

Example:

CREATE TABLE ECE_BRANCH(ROLL_NUMBER INT PRIMARY KEY,
                        NAME VARCHAR(255),
                        BATCH VARCHAR(255),
                        EMAIL VARCHAR(255),
                        PHONE_NUMBER INT)

The above is used to create a table.

14. DEFAULT

Default constraint provides a default value for a column.

Example:

CREATE TABLE ECE_BRANCH(ROLL_NUMBER INT PRIMARY KEY,
                        NAME VARCHAR(255),
                        BATCH VARCHAR(255),
                        EMAIL VARCHAR(255),
                        BRANCH VARCHAR(255) DEFAULT 'ECE'
                        PHONE_NUMBER INT)

The above code is used to create a table in which the default value of the branch field is set to ECEECE.

15. DELETE

The DELETE statement is used to delete the existing records from the table.

Example:

DELETE FROM CSE_BRANCH
WHERE CGPA < 6.5;

The above SQL code will delete all rows in a table CSECSE_BRANCHBRANCH whose CGPA is less than 6.56.5.

16. DESC

DESC command is used to sort the data in descending order.

The following SQL code will sort the required table in descending order of CGPA.

Example:

SELECT * FROM CSE_BRANCH
ORDER BY CGPA DESC;

17. DISTINCT

DISTINCT is used to select only different values from the table.

Example:

SELECT DISTINCT BATCH FROM CSE_BRANCH;

The above code selects all the different values from the BATCH column in the CSE_BRANCH table.

18. DROP

The DROP statement is used to delete a column in the mentioned table.

Syntax:

The following SQL code states that we are dropping a column BATCH from the table CSE_BRANCH.

Example:

ALTER TABLE CSE_BRANCH;
DROP COLUMN BATCH;

19. EXEC

EXEC command is used to execute a stored procedure.

Syntax:

The following SQL code tells us we are executing a stored procedure, i.e., SelectPlacedStudents.

Example:

EXEC SelectPlacedStudents;

20. EXISTS

The EXISTS commands test whether there exists any record in the sub-query. If the records are present in the sub-query, then, EXISTS will return True. Otherwise, it returns False.

Syntax:

SELECT column_name FROM table_name
WHERE EXISTS (sub_query);

Example:

select * from cse_branch
where exists (select batch from ece_branch where cgpa >= 7.0);

In the above SQL code, if our sub_query returns True, then only our main query will work, else it will return an empty table.

21. FOREIGN KEY

Foreign Key constraint is a key that is used to link two tables together. It is the field(s) in one table that refers to the PRIMARY KEY in another table. We can declare Foreign Key at the time of the creation of the table.

Syntax:

CREATE TABLE table_name(constraints);

Example:

CREATE TABLE IT_BRANCH(ROLL_NUMBER INT PRIMARY KEY,
                       NAME varchar(255),
                       EMAIL varchar(255) FOREIGN KEY
                       )

In the above SQL code, a table named ITIT_BRANCHBRANCH has been created, with roll number as the primary key and email as its foreign key.

22. FROM

FROM command is used to specify the table on which we need to operate.

Example:

SELECT * FROM CSE_BRANCH;

The above command selects all the available records in the table CSECSE_BRANCHBRANCH.

23. GROUP BY

The GROUP BY keyword is used to group the result set, and it is used with the aggregate functions like COUNT, MAX, MIN, SUM, AVG etc.

Syntax:

SELECT column_names FROM table_name
GROUP BY column_name;

Example:

SELECT count(ROLL_NUMBER), BATCH FROM cse_branch
GROUP BY BATCH;

24. HAVING

HAVING statement is similar to WHERE statement. The difference between HAVING and WHERE is that in the HAVING clause, we can use aggregate functions, whereas in the WHERE clause, we can't use aggregate functions.

Syntax:

SELECT column_name(s) FROM table_name
GROUP BY column_name
HAVING aggregate_function;

Example:

SELECT count(roll_number),batch FROM cse_branch
GROUP BY roll_number
HAVING count(roll_number) = 30;

In the above code, we select two columns from the CSECSE_BRANCHBRANCH table and group them on the roll_numner to see how many batches have a total number of students = 30.

25. IN

The IN constraint allows us to specify multiple values in the WHERE clause.

Syntax:

select column_name(s) from table_name
where condition;

Example:

select * from cse_branch
where batch in ('A1', 'A2', 'A3', 'A4');

The above code will select all the records whose batch is A1, A2, A3, or A4.

26. INDEX

INDEX is used to create or delete the indexes in the table. Indexes help us to retrieve data from the database very fast. It helps us to speed up the performance of our queries/searching.

Syntax:

CREATE INDEX index_name
ON table_name(column_name(s));

The following SQL code creates an index indexCGPAindexCGPA on the column CGPACGPA of table ECEBRANCHECE_BRANCH.

Example:

CREATE INDEX indexCGPA
ON ECE_BRANCH(CGPA);

27. INSERT

INSERT command is used to insert the data into the table.

• INSERT INTO (with hardcoded values): INSERT INTO command is used to insert rows in a table.

Syntax:

INSERT INTO table_name(column_name(s))
VALUES (data);

Example:

INSERT INTO CSE_BRANCH(ROLL_NUMBER, NAME, EMAIL, PHONENUMBER, BATCH, CGPA)
VALUES (1678643, 'BCD', '', 1676771718, A7, 9);

In the above example, we insert values in the table CSECSE_BRANCHBRANCH.

• INSERT INTO (with SELECT clause): This command is used to insert the data of one table to another.

Syntax:

INSERT INTO table_name(column_name(s))
SELECT column_name(s) FROM table_name;

Example:

INSERT INTO ALL_BATCHES(ROLL_NUMBER, NAME, EMAIL, PHONENUMBER, BATCH, CGPA)
SELECT ROLL_NUMBER, NAME, EMAIL, PHONENUMBER, BATCH, CGPA FROM CSE_BRANCH;

In the above code, we are just copying all the data of CSECSEBRANCHBRANCH data to the table ALLALLBRANCHESBRANCHES table.

28. JOIN

JOIN command is used to JOIN TABLES. There are different JOINS available in SQL:

• INNER JOIN
• FULL OUTER JOIN
• SELF JOIN
• CROSS JOIN
• LEFT JOIN
• RIGHT JOIN

29. LIKE

It is used with the WHERE clause to search for a specified pattern in that mentioned column.

% - It is used to represent multiple characters. _ - It is used to represent a single character.

Syntax:

SELECT column_name(s) FROM table_name
WHERE condition;

The following SQL code will return a table where the roll number starts from 19.

Example:

SELECT * FROM cse_branch
WHERE ROLL_NUMBER LIKE '19%';

30. LIMIT

LIMIT is used to specify the number of returned records.

Syntax:

Example:

SELECT * FROM CSE_BRANCH
LIMIT 100;

The above code will show only 100 records due to the limit constraint that we have applied.

31. NOT

This command is used with the WHERE clause to only include those that record where a condition is not true.

Syntax:

SELECT column_name FROM table_name
WHERE condition;

Example:

SELECT * FROM CSE_BRANCH
WHERE NOT BATCH = 'A1';

The above code will return a table with no A1 batch students record.

32. OR

This OR command is used with the WHERE clause, which results in whether to include record(s) on a given condition.

Syntax:

SELECT column_name FROM table_name
WHERE condition;

Example:

SELECT * FROM CSE_BRANCH
WHERE BATCH = 'A1' OR BATCH = 'A2';

In the above example, the query will return the table that contains only batch = A1 and A2.

33. ORDER BY

ORDER BY command is used to sort the result by ascending or descending order. But by default ORDER BY sorts the result in ascending order.

Example:

SELECT * FROM CSE_BRANCH
ORDER BY CGPA;

34. PRIMARY KEY

The PRIMARY KEY constraint uniquely identifies each record in a table.

A table can have only one primary key.

Syntax:

CREATE TABLE table_name(contraints);

Example:

CREATE TABLE IT_BRANCH(ROLL_NUMBER INT PRIMARY KEY,
                       NAME varchar(255),
                       EMAIL varchar(255)
                       )

The above SQL code has created a table named ITIT_BRANCHBRANCH , with roll number as the primary key.

35. PROCEDURE

PROCEDURE is used to create a store procedure. A STORED PROCEDURE is a SQL code that can be reused again.

Syntax:

CREATE PROCEDURE procedure_name
AS
...
GO;

Example:

CREATE PROCEDURE SelectAllData
AS
SELECT * FROM CSE_BRANCH
GO;

-- Execute the procedure
EXEC SelectAllData;

36. SELECT

SELECT statement is used to select the data from a database, and that data is returned is stored in a table.

Syntax:

SELECT column_name(s) FROM table_name;

Example:

SELECT * FROM CSE_BRANCH;

37. TOP

TOP command is used with SELECT. It will select the mentioned number of records from the table.

Syntax:

SELECT TOP number column_name(s) FROM table_name;

Example:

SELECT TOP 10 * FROM CSE_BRANCH;

It will return a table in which it will have the first 10 records.

38. TRUNCATE

Previously we have studied the DROP command in which we have deleted our table, but this time we will see the TRUNCATE statement which will not delete the table, it will delete the data inside the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE ECE_BRANCH;

39. UNION

This UNION statement will combine the result of two or more queries. It will combine only distinct records.

Syntax:

Example:

SELECT * FROM CSE_BRANCH
UNION
SELECT * FROM ECE_BRANCH;

40. UNION ALL

The UNION statement allows only distinct values, whereas the UNION ALL also allows duplicate values.

Syntax:

Example:

SELECT * FROM CSE_BRANCH
UNION ALL
SELECT * FROM ECE_BRANCH;

Check out this article to learn more about UNION and UNION ALL.

41. UNIQUE

The UNIQUE constraint ensures that all the values in a column are unique.

Example:

CREATE TABLE IT_BRANCH(ROLL_NUMBER INT PRIMARY KEY,
                       NAME varchar(255),
                       EMAIL varchar(255)
                       UNIQUE(ROLL_NUMBER)  
                      )

42. WHERE

The WHERE clause filters the table, which includes records that fulfill a specific condition.

Syntax:

select column_name(s) from table_name
where condition;

The following SQL code will return a table where the roll number will start from 20.

Example:

select * from CSE_BRANCH
where ROLL_NUMBER LIKE '20%';

43. UPDATE

UPDATE command is used to update the existing records in a table.

Example:

UPDATE CSE_BRANCH
SET BRANCH = 'CSE';

The following code will update the table CSECSE_BRANCHBRANCH and set the BRANCHBRANCH as CSECSE.

44. IS NULL

This command is used to check whether any record is empty or not.

Example:

select * from cse_branch
where roll_number IS NULL;

It will return a table in which all the roll numbers are NULL.