Demystifying SQL 102: DDL Basic Commands

SQL DDL: Basic Commands

SQL most used commands can be separated in five categories (source: GeeksforGeeks), but we will only cover the main three categories: Data Definition Language (DDL), Data Querying Language (DQL) and Data Manipulation Language (DML). We will talk about these in the next sections, but first, let me introduce you to the tool to use to put into practice your SQL skills.

DB Fiddle

DB Fiddle is an online tool that you can access for free. Here, you are able to select the SQL dialect and version of your preference (MySQL, PostgreSQL, or SQLite), and you can create your own databases and tables, as well as develop SQL queries to test and share with your colleagues. 

In DB Fiddle, you don't need to create an account unless you want to upgrade your account to a PRO version (but that is not needed for the purpose of this blog).

The first thing to notice in DB Fiddle is that there are different sections:

• SQL Dialect Selector: here you can select between MySQL, PostgreSQL or SQLite dialects, and for each you can select distinct versions of them. 

NOTE: We will use MySQL latest version available for the next explanations, so make sure to select MySQL to follow the next steps.
The reason behind this is because MySQL is one of the most popular open-source DBMS systems. 
MySQL has a large and active community of users and developers, which means you'll find a wealth of resources, tutorials, and forums 
to help you with any questions or issues you encounter. MySQL is also used widely in the industry, so you will find yourself learning
a dialect used in real-world scenarios and work places.

DB Fiddle: SQL Dialect Selector

• SQL DDL and DML section: on the left you have a panel called Schema SQL. This is where you will place all the DDL and DML SQL commands (to create databases, create tables, define data types for the tables, insert new records, etc.). In the video below we are creating a table called "test" this table  has two columns ("column1" and "column2") and we are specifying the data type of the columns. After that, we are inserting some rows to the table, specifying the columns to populate and the values of these columns. In the next section we will talk about the DDL SQL commands in more detail. 

DB Fiddle: Schema SQL (DDL) panel

• SQL DQL section: with the databases and tables created on the left panel (Schema SQL panel), you will be able to query them on the right panel called Query SQL. This is where the DQL SQL commands will be placed (to select data, aggregate data, join tables, etc.) Here you can also add DML commands to update a table based on the query results, for example. You can see in the next video how we are querying the previously created table "test"and we are selecting all the rows that have a "column2" value of "true" (in other words, we are filtering out those rows that doesn't meet this condition). 

DB Fiddle: SQL Query (DQL) panel

Great, we have successfully identified where to place the schema (structure and organisation of the database, tables and data it will contain), and  where we will write the code to query the data. But where do we see the results? Well, after you specify the schema and developed your query, you can now run the code by clicking the Play button on the top of the DB Fiddle interface (right next to where you select the SQL dialect). After running the code, you will be able to see the results on the bottom of the page (see next video as reference).

DB Fiddle: Running a SQL query

SQL DDL Commands

Data Definition Language (DDL) are SQL commands used to design a database structure; they are specific commands to describe data and its relationship in a database. These commands allow you to create, modify, and manage database objects (such as tables).

The DDL commands define the database schema. A database schema provides the overall structure of a database, including the definitions of tables. The schema within a table defines the structure and organization of that specific table (columns, data types, and relationships with other tables.

We will explain some of the most common DDL commands. For a quick overview of other useful DDL commands that may not have been covered in this blog, you can read the following blog post "SQL DDL: Getting started with SQL DDL commands in SQL Server" by Ben Richardson.

CREATE

The CREATE command is used to create databases and tables within a Database Management System (DBMS). This command requires you to specify the type of object to create. Creating a database is the first step in setting up your data environment:

CREATE DATABASE sqldojo_db;

/* To create a table in a specific database you need to either add the 
database name as a prefix (e.g., CREATE TABLE [db name].[table name]), 
or you can also call the database to use before creating the table:
USE [db name]; CREATE TABLE [table name]. */


USE sqldojo_db;
CREATE TABLE sqldojo_pets_table (name VARCHAR(55), owner VARCHAR(55), species VARCHAR(55), sex CHAR(1), birth DATE);

As you can see in the code above, each command we write must be finished with a semicolon ';'. And you can also notice the greyed-out sentence; this is a comment in SQL! A comment in the world of programming are lines that will be ignored, i. e., it will not be executed by the program. You may be wondering, "why do we need these comments?", well, programmers use comments to provide some explanation of their codes, to explain what the code is doing or explain some variables or functions in the code directly. With comments, one can go into the code and understand easier what is the purpose of a line of code. This way you will be able to explain your code better, to share it with others so they can easily read it without going back to you asking the purpose of each part of the code, and also to document the code. You may write a code and don't touch it in several weeks or even months, and when you need to review the code once again, you may find yourself trying to understand it all over again;, but with comments you will be able to understand it faster. Below you will see a block of code showing the different ways to add a comment in SQL. There are two different ways to add comments in SQL:

• Single-line comments: these are comments that just occupy one line in the code editor
• Multi-line or block comments: these are comments that occupy multiple consecutive lines. It is equivalent as using multiple single line comments followed one after another.

1 2 3 4 5 6 7 8 9 10 11

-- This is a single line SQL comment CREATE DATABASE sqldojo_db; /* This is a multi-line/block SQL comment. It utilises more than one line. This is useful to comment multiple lines of code. */ CREATE DATABASE sqldojo_db2;

ALTER TABLE

The ALTER TABLE command is used to modify an existing table's structure. You can add, modify, or drop columns, as well as add constraints.

In the next video you will be able to see the structure of the table "sqldojo_pets_table" from the database "sqldojo_db". To obtain the schema we will make use of the command "DESCRIBE", which tells us about the structure of a table. DESCRIBE is not a DDL command as it is not a standard SQL command, rather it was develop by specific DBMS like MySQL. 

ALTER Table command in MySQL

DROP TABLE

The DROP TABLE command deletes an entire table along with all its data. This is irreversible, using this command will permanently delete the table and its data, so use this command carefully. You can see in the example below that we created a database "sqldojo_db" that contains two tables "sqldojo_pets_table" and "sqldojo_owners_table". We are then specifying to DELETE the "sqldojo_owners_table". You may be wondering why we are specifying these commands in the right panel of DB Fiddle, well, if you place these commands on the left, you won't be able to see the results below, so you won't have a visual aid showing how the table changed.

Drop Table in MySQL Dialect

Conclusion

In conclusion, SQL DDL commands are fundamental tools for anyone venturing into the world of databases and data management. We've explored the power of DDL commands, focusing on CREATE, ALTER TABLE, and DROP TABLE. With these commands, you can design, modify, and manage the structure of your database with precision and control.

Moreover, we introduced you to DB Fiddle, which offers an excellent environment to practice SQL DDL commands in various dialects for free.

While we've covered some of the most common DDL commands here, there's a wealth of SQL techniques and statements waiting to be explored. 

The SQL journey doesn't end here. With your new understanding of SQL DDL, you're well-equipped to take the next steps in mastering the art of database management and data manipulation. In the next blog post, we will walk you through the SQL Data Querying Language and Data Manipulation Language, so stay tuned.

Happy querying and designing!