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: 

SELECT NAME,
       rbis
FROM   baseball_team
WHERE  rbis > 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: 

DELETE FROM baseball_team
WHERE  NAME = '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: 

SELECT batting_average AS AVG
FROM   baseball_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:

CREATE TABLE players
  (
     name          VARCHAR(50),
     jersey_number INT
  ); 

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 *
FROM   baseball_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: 

SELECT player_stats.hits,
       player_stats.runs,
       team_info.team_name,
       team_info.city
FROM   player_stats
       JOIN baseball_team
         ON player_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 *
FROM   baseball_team
WHERE  batting_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 *
FROM   baseball_team
WHERE  rbis >= 50
       AND slugging_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 *
FROM   baseball_team
WHERE  batting_average IN ( 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 *
FROM   baseball_team
WHERE  batting_average LIKE '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

DROP TABLE baseball_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

SELECT player_position,
       Count(*)
FROM   baseball_team
GROUP  BY player_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:

ALTER VIEW top_players AS SELECT player_name, hits, runs, rbis FROM
baseball_table WHERE hits > 100 AND rbis > 50 ORDER BY rbis DESC; 

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

ALTER TABLE baseball_team
  ADD COLUMN batting_avg DECIMAL(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

SELECT player_name,
       hits,
       runs,
       rbis
FROM   baseball_team
WHERE  hits > 100
UNION
SELECT player_name,
       hits,
       runs,
       rbis
FROM   baseball_team
WHERE  rbis > 50
ORDER  BY rbis DESC; 

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

SELECT player_name,
       hits,
       runs,
       rbis
FROM   baseball_team
ORDER  BY hits DESC,
          rbis DESC; 

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

SELECT player_name,
       batting_average
FROM   baseball_team
ORDER  BY batting_average ASC; 

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

SELECT player_name,
       batting_average
FROM   baseball_team
ORDER  BY batting_average DESC; 

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:

UPDATE baseball_team
SET    batting_average = .320
WHERE  player_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

ALTER TABLE baseball_team
  ADD COLUMN strikeouts INT; 

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

SELECT DISTINCT batting_average
FROM   baseball_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

INSERT INTO baseball_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

UPDATE baseball_team
SET    batting_average = 0.280
WHERE  id = 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 *
FROM   baseball_team
WHERE  batting_average BETWEEN 0.250 AND 0.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

SELECT player_name,
        case when batting_average >= .300 THEN
'All-Star' else 'Not an All-Star' end AS all_star_status FROM baseball_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 *
FROM   baseball_team
WHERE  rbis IS NOT NULL; 

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

SELECT Avg(batting_average)
FROM   baseball_team
GROUP  BY hits
HAVING hits >= 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 *
FROM   baseball_team
WHERE  EXISTS (SELECT *
               FROM   teams
               WHERE  baseball_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 *
FROM   baseball_team
ORDER  BY rbis DESC
LIMIT  5; 

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 *
FROM   baseball_team
ORDER  BY rbis DESC
LIMIT  5 offset 5; 

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

TRUNCATE TABLE baseball_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

CREATE VIEW player_stats
AS
  SELECT hits,
         runs,
         rbis,
         batting_avg,
         steals,
         slugging_pct
  FROM   baseball_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

ALTER TABLE baseball_team ADD COLUMN hitter_type varchar(20);UPDATE baseball_team
SET    hitter_type = case when slugging_pct < 0.4 WHEN
'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

ALTER TABLE baseball_team ADD COLUMN hitter_type varchar(20);UPDATE baseball_team
SET    hitter_type = case when slugging_pct < 0.4 THEN
'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.