Book Image

The SQL Workshop

By : Frank Solomon, Prashanth Jayaram, Awni Al Saqqa
Book Image

The SQL Workshop

By: Frank Solomon, Prashanth Jayaram, Awni Al Saqqa

Overview of this book

Many software applications are backed by powerful relational database systems, meaning that the skills to be able to maintain a SQL database and reliably retrieve data are in high demand. With its simple syntax and effective data manipulation capabilities, SQL enables you to manage relational databases with ease. The SQL Workshop will help you progress from basic to advanced-level SQL queries in order to create and manage databases successfully. This Workshop begins with an introduction to basic CRUD commands and gives you an overview of the different data types in SQL. You'll use commands for narrowing down the search results within a database and learn about data retrieval from single and multiple tables in a single query. As you advance, you'll use aggregate functions to perform calculations on a set of values, and implement process automation using stored procedures, functions, and triggers. Finally, you'll secure your database against potential threats and use access control to keep your data safe. Throughout this Workshop, you'll use your skills on a realistic database for an online shop, preparing you for solving data problems in the real world. By the end of this book, you'll have built the knowledge, skills and confidence to creatively solve real-world data problems with SQL.
Table of Contents (13 chapters)

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

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

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

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

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

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

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

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.