SQL Keywords, Operators, & Statements: 34 Definitions + Examples

Suppose you're looking to build your knowledge of SQL and improve your company's overall data strategy. In that case, it helps to understand SQL keywords, operators, and statements. These components of the SQL language are essential to creating efficient and powerful queries that extract valuable insights from your data.

In this post, we'll break down each component and how they work together in SQL. Then we'll provide you with a comprehensive list of SQL keywords, statements, and operators that you can use as a reference point going forward. We've even included an example for each one to help you better understand the component.

From comparing values and manipulating data to managing security and transactions, this guide covers all the essential elements of SQL that will help you achieve better results when working with databases.

What are SQL Keywords?

SQL keywords are a crucial component of the SQL language and are used to perform specific tasks such as retrieving, filtering, and managing data in a database. These reserved words have special meanings and are used to define data structures, perform operations, and specify conditions.

Remember that SQL keywords are not case-sensitive, but using uppercase letters for readability and consistency is recommended. By familiarizing yourself with SQL keywords, you can write efficient and effective SQL queries that help you analyze and extract insights from your database.

What are SQL operators?

Operators are elements of the language that enable you to perform logical or mathematical functions on data. SQL operators are broken down into categories such as comparison, logical, arithmetic, and bitwise. Each allows you to execute complex queries in a structured format.

What are SQL statements?

SQL statements manipulate, manage and control data stored within a relational database. These statements provide great flexibility to the user and can perform several tasks like defining database objects, manipulating data, controlling security rights, and even rolling back transactions.

Now that you know what these elements do, let's review a list of the most common ones used in SQL.

34 SQL Keywords, Operators, & Statements

The following list of definitions and examples uses this database titled "Baseball Team."

1. SELECT

The SELECT statement retrieves data from one or more tables. 

SELECT Statement Example: 

SELECTNAME,
rbis
FROMbaseball_team
WHERErbis>20;

Output:

This would select the names and RBIs of all players with an RBI count greater than 20.

2. DELETE

The DELETE statement deletes one or more rows from a table.

DELETE Statement Example: 

DELETEFROMbaseball_team
WHERENAME='Betts';

Output:

This would delete all rows from the baseball team table where the player's name is "Betts."

3. AS

The AS keyword assigns an alias to a table or column.

AS Keyword Example: 

SELECTbatting_averageASAVG
FROMbaseball_team;

Output:

This would select the batting average column and give it the alias “AVG.”

4. CREATE

The CREATE keyword creates a new database object, such as a table, index, or view.

Create Keyword Example:

CREATETABLEplayers
(
nameVARCHAR(50),
jersey_numberINT
);

Output:

This would create a new table called “players.”

5. FROM 

The FROM keyword specifies a SELECT statement's source table(s).

FROM Keyword Example: 

SELECT*
FROMbaseball_team;

Output:

This would retrieve all data from the table.

6. JOIN

The JOIN clause combines rows from two or more tables based on a related column between them.

JOIN Clause Example: 

SELECTplayer_stats.hits,
player_stats.runs,
team_info.team_name,
team_info.city
FROMplayer_stats
JOINbaseball_team
ONplayer_stats.player_id=team_info.player_id;

Output:

This would join a second table called “player_stats” to our original table.

7. WHERE

The WHERE clause filters rows based on a specified condition.

WHERE Clause Example

SELECT*
FROMbaseball_team
WHEREbatting_average>0.320;

Output:

This would show you all players with a batting average greater than 0.320.

8. AND

AND is an operator that combines multiple conditions in a WHERE clause.

AND Operator Example:

SELECT*
FROMbaseball_team
WHERErbis>=50
ANDslugging_percentage>0.450;

Output:

This identifies all players with at least 50 RBIs and a slugging percentage greater than 0.450.

9. IN

The keyword IN specifies multiple possible values for a column in a WHERE clause.

IN Keyword Example:

SELECT*
FROMbaseball_team
WHEREbatting_averageIN(0.301,0.195);

Output:

This finds all players with a batting average that is either greater than 0.300 or less than 0.200.

10. LIKE

The LIKE keyword compares a value to a pattern.

LIKE Keyword Example: 

SELECT*
FROMbaseball_team
WHEREbatting_averageLIKE'3%';

Output:

This would return the rows for all players whose batting average starts with 3. 

11. DROP

The drop keyword deletes an existing database object. It is traditionally used in transactional SQL. 

DROP Keyword Example

DROPTABLEbaseball_team;

Output:

This removes the entire table from the database.

12. GROUP BY 

GROUP BY is a clause that groups rows based on the values in one or more columns.

GROUP BY Clause Example

SELECTplayer_position,
Count(*)
FROMbaseball_team
GROUPBYplayer_position;

Output:

This groups the players in the table by their position and returns a count of how many players are in each position.

13. ALTER

The ALTER keyword modifies the structure of an existing database object.

ALTER Keyword Example:

ALTERVIEWtop_playersASSELECTplayer_name,hits,runs,rbisFROM
baseball_tableWHEREhits>100ANDrbis>50ORDERBYrbisDESC;

Output: 

This modifies the top_players view only to include players with more than 100 hits and more than 50 RBIs, sorted by their RBI count in descending order.

14. ALTER TABLE

ALTER TABLE is a statement that modifies the structure of an existing table.

ALTER TABLE Statement Example

ALTERTABLEbaseball_team
ADDCOLUMNbatting_avgDECIMAL(4,3);

Output:

This adds a new column called batting_avg to the table with a data type of DECIMAL and a precision of 4, and a scale of 3

15. UNION

The UNION keyword combines the results of two or more SELECT statements into a single result set.

UNION Keyword Example

SELECTplayer_name,
hits,
runs,
rbis
FROMbaseball_team
WHEREhits>100
UNION
SELECTplayer_name,
hits,
runs,
rbis
FROMbaseball_team
WHERErbis>50
ORDERBYrbisDESC;

Output:

This query returns a list of players with more than 100 hits or more than 50 RBIs, sorted by their RBI count in descending order.

16. ORDER BY

ORDER BY is a clause that sorts the results of a SELECT statement by one or more columns.

ORDER BY Clause Example

SELECTplayer_name,
hits,
runs,
rbis
FROMbaseball_team
ORDERBYhitsDESC,
rbisDESC;

Output:

This would order the table by hits and RBIs in descending order.

17. ASC

ASC sorts the results of a SELECT statement in ascending order.

ASC Keyword Example

SELECTplayer_name,
batting_average
FROMbaseball_team
ORDERBYbatting_averageASC;

Output:

This would order the table by batting average, starting with the lowest value. 

18. DESC 

DESC sorts the results of a SELECT statement in descending order.

DESC Keyword Example

SELECTplayer_name,
batting_average
FROMbaseball_team
ORDERBYbatting_averageDESC;

Output:

This would order the table by batting average, starting with the highest value. 

19. SET

SET is a keyword that assigns a value to a variable or modifies the value of a database property.

SET Keyword Example:

UPDATEbaseball_team
SETbatting_average=.320
WHEREplayer_name='Trout';

Output:

This would set Trout’s batting average to .320. 

20. ADD

ADD adds a column to an existing table.

ADD Keyword Example

ALTERTABLEbaseball_team
ADDCOLUMNstrikeoutsINT;

Output:

This would add the column “strikeouts” to the table.

21. DISTINCT

The DISTINCT keyword specifies that the result set of a SELECT statement should only include distinct (unique) values.

DISTINCT Keyword Example

SELECTDISTINCTbatting_average
FROMbaseball_team;

Output:

This would return a list of all unique batting averages from the table.

22. INSERT INTO

INSERT INTO inserts new data into a table.

INSERT INTO Statement Example

INSERTINTObaseball_team
(
hits,
runs,
rbis,
batting_average,
steals,
slugging_percentage
)
VALUES
(
100,
45,
60,
0.320,
20,
0.550
);

Output:

This would add a new player to the database with specific stats.

23. UPDATE

The UPDATE keyword modifies existing data in a table.

UPDATE Keyword Example

UPDATEbaseball_team
SETbatting_average=0.280
WHEREid=3;

Output:

This would update Jack’s batting average to .300.

24. BETWEEN

The BETWEEN keyword specifies a range to search for a value in a condition.

BETWEEN Keyword Example

SELECT*
FROMbaseball_team
WHEREbatting_averageBETWEEN0.250AND0.300;

Output:

This would select all players with a batting average between 0.250 and 0.300.

25. ELSE

The ELSE keyword is used in conjunction with the CASE statement in SQL. It specifies the result that should be returned if none of the preceding conditions are true.

ELSE Keyword Example

SELECTplayer_name,
casewhenbatting_average>=.300THEN
'All-Star'else'Not an All-Star'endASall_star_statusFROMbaseball_team;

Output:

This would assign the value “not an All-star” if the batter does not have a batting average that is higher than .300. 

26. NOT NULL

The NOT NULL statement specifies that a value is not null (exists in the database).

NOT NULL Statement Example

SELECT*
FROMbaseball_team
WHERErbisISNOTNULL;

Output:

This would select all players with non-null values for RBIs. 

27. HAVING

The HAVING keyword specifies a condition for filtering data in a GROUP BY clause.

HAVING Keyword Example

SELECTAvg(batting_average)
FROMbaseball_team
GROUPBYhits
HAVINGhits>=100;

Output:

This would select all players with at least 100 hits. 

28. EXISTS

The EXISTS keyword tests for the existence of any record in a subquery.

EXISTS Keyword Example

SELECT*
FROMbaseball_team
WHEREEXISTS(SELECT*
FROMteams
WHEREbaseball_team.id=teams.id);

Output:

This would select all players from the table that have a corresponding entry in the team’s table.

29. LIMIT

The LIMIT keyword limits the number of rows returned in a SELECT statement.

LIMIT Keyword Example

SELECT*
FROMbaseball_team
ORDERBYrbisDESC
LIMIT5;

Output:

This selects the five players with the highest number of RBIs.

30. OFFSET

The OFFSET keyword specifies the starting point for a result set in a SELECT statement.

OFFSET Keyword Example

SELECT*
FROMbaseball_team
ORDERBYrbisDESC
LIMIT5offset5;

Output:

This would select the top 5 players with the highest number of RBIs, starting from the 6th result.

31. TRUNCATE

The TRUNCATE keyword Deletes all data from a table but keeps the table structure intact.

TRUNCATE Keyword Example

TRUNCATETABLEbaseball_team;

Output:

This would delete all data from the table.

32. VIEW

The VIEW operator creates a virtual table from a SELECT statement that can be used like a regular table.

VIEW Operator Example

CREATEVIEWplayer_stats
AS
SELECThits,
runs,
rbis,
batting_avg,
steals,
slugging_pct
FROMbaseball_team;

Output:

This creates a view that shows each player's batting average and slugging percentage.

33. WHEN

WHEN is used in a CASE expression to specify a condition.

WHEN Keyword Example

ALTERTABLEbaseball_teamADDCOLUMNhitter_typevarchar(20);UPDATEbaseball_team
SEThitter_type=casewhenslugging_pct<0.4WHEN
'contact hitter'else'power hitter'end;

Output:

This would create a new column in the table that categorizes each player as either a "contact hitter" or a "power hitter" based on their slugging percentage.

34. THEN

THEN is used in a CASE expression to specify the value to return if the condition is true.

THEN Keyword Example

ALTERTABLEbaseball_teamADDCOLUMNhitter_typevarchar(20);UPDATEbaseball_team
SEThitter_type=casewhenslugging_pct<0.4THEN
'contact hitter'else'power hitter'end;

Output:

In this example, we're using the WHEN/THEN statement within a CASE statement to assign a value to the new "hitter_type" column based on the value of the "slugging_pct" column. If the slugging percentage is less than 0.4, we classify the player as a "contact hitter". Otherwise, we classify them as a "power hitter".

Using SQL Keywords, Operators, & Statements

Understanding SQL keywords, operators, and statements is essential for effectively mastering the art of querying databases.  By familiarizing yourself with these components, you can confidently navigate and manipulate your database to retrieve, filter, and transform data to meet your specific needs. Whether you're a data analyst, database administrator, or developer, this knowledge will empower you to write efficient and powerful SQL queries.

Author's Note: This post was written/edited by a human with the assistance of generative AI.