An Overview of Basic SQL Commands

SQL (often pronounced "sequel") stands for Structured Query Language. A query in SQL is constructed using different commands. These commands are classified into what are called sublanguages of SQL. Even if you think you know them already, give this a read to see if these seem more relatable to you. There are five sublanguages in SQL, as follows:

• Data Definition Language (DDL): As the name suggests, the commands that fall under this category work with defining either a table, a database, or anything within. Any command that talks about creating something in SQL is part of DDL. Some examples of such commands are CREATE, ALTER, and DROP.

  The following table shows the DDL commands:

  

Figure 1.2: DDL commands

• Data Manipulation Language (DML): In DML, you do not deal with the containers of data but the data itself. When you must update the data itself, or perform calculations or operations on it, you use the DML. The commands that form part of this language (or sublanguage) include INSERT, UPDATE, MERGE, and DELETE.

  DML allows you to work on the data without modifying the container or stored procedures. A copy of the data is created and the operations are performed on this copy of the data. These operations are performed using the DML. The following table shows the DML commands:

  

Figure 1.3: DML commands

• Data Control Language (DCL): When we sit back and think about what the word control means in the context of data, we think of allowing and disallowing actions on the data. In SQL terms, or in terms of data, this is about authorization. Therefore, the commands that fall in this category are GRANT and REVOKE. They control access to the data. The following table explains them:

Figure 1.4: DCL commands

• Transaction Control Language (TCL): Anything that makes a change to the data is called a transaction. When you perform a data manipulation operation, the manipulation happens to data in a temporary location and not the table/database itself. The result is shown after the operation. In order to write or remove something from the database, you need to use a command to ask the database to update itself with the new content. Applying these changes to the database is called a transaction and is done using the TCL. The commands associated with this language are COMMIT and ROLLBACK. The following table explains these commands in detail:

Figure 1.5: TCL commands

• Data Query Language (DQL): The final part of this section regarding the classification of commands is the DQL. This is used to fetch data from the database with the SELECT command. It's explained in detail in the following table:

Figure 1.6: DQL command

We'll look at these queries in detail in later chapters.

Creating Databases

An interesting point to note is that the create database command is not part of the regular SQL standard. However, it is supported by almost all database products today. The create database statement is straightforward. You just need to issue a database name along with the command, followed by a semicolon.

Let's start by creating a simple example database. We'll call it studentdemo. To create the studentdemo database with the default configuration, use the following command:

create database studentdemo;

To run this statement, click the Execute button (shaped like a lightning bolt):

Figure 1.7: Creating the studentdemo database

In the Action Output pane, the successful completion of a command will appear. You will also be able to see the newly created database in the Schemas tab of the Navigator pane.

Note

SQL is not case sensitive. This implies CREATE TABLE studentdemo; is the same as create table studentdemo;.

We cannot have multiple databases with the same name. If you try to run the query again, you'll get the following error:

Figure 1.8: Error message displayed in the case of a database with the same name as another database

The Use of Semicolons

As you may have noticed, there's a semicolon, ;, at the end of the statement as an indication that that's the end of that statement. It depends on the database system you are using; some of them require a semicolon at the end of each statement and some don't, but you can still add it without worrying about the results.

Note

In general, it's good practice to use a semicolon at the end of a statement as it could play a significant role when we have multiple SQL statements or while writing a function or a trigger. This will be explained in more detail in the upcoming chapters. Throughout this book, we will use semicolons at the end of each statement.