The BEC section of the CPA exam will test whether a candidate understands the basic commands used in SQL. These SQL commands must use the correct syntax. Syntax is the way the command must be structured by itself or with other commands to properly retrieve the data. There are four categories of SQL commands:

1) Data definition language (“DDL”) – Includes commands that are used to define the rows and columns in the relational database.

2) Data manipulation language (“DML”) – Commands used to change or manipulate data in the relational database

3) Data control language (“DCL”) – Includes commands that relate to user access or permissions in the relational database

4) Transaction control language (“TCL”) – Includes commands that relate to the transactions in the relational database

Specific types of DDL commands

As described above, DDL commands are used to manage the columns and rows in an SQL database. Below are some specific commands that you should be familiar with.

CREATE – This command is used to create a table or database

DROP – This command is used to drop or delete a table

TRUNCATE – This command is used to delete information in a table but it will NOT delete the table itself

ALTER – This command is used to add, delete, or modify columns in a table

BACKUP – This command is used to duplicate or backup an existing database so that information will not be lost if the database is hacked, stolen, or destroyed.

Specific types of DML commands

As described above, DML commands are used to change or manipulate the data itself that is stored in an SQL database.

USE – This command is used to select the database that you want to modify. This assumes that there are multiple databased in the SQL.

UPDATE – This command updates existing records in the table. The update command can be used on all rows or just specific rows.

DELETE – This command deletes an existing record in the table. Specific records can be deleted or records based on a specific condition can be deleted.

SELECT – This command is used to select specific records in the table.

Specific types of DCL commands

As described above, DCL commands relate to user access and permissions. Therefore, the commands will relate to changing or modifying access and permission for users to the database.

GRANT – This command will grant or add access to the database for a specific user.

REVOKE – This command will revoke or remove access to the database for a specific user.

Specific types of TCL commands

As described above, TCL commands relate to managing transactions within the relational database.

SAVEPOINT – This command will temporarily save a transaction. Note that this does not mean the transaction is finalized in the database. This command is useful because the user doesn’t have to go back to the last COMMIT point when rolling back a transaction.

COMMIT – This command is used to fully save a transaction in the database.

ROLLBACK – This command is used to restore the database back to the last time the “COMMIT” command was used. This basically just means we are restoring the database back to the last save point.