In our SQL 101 article, you learned the basics of SQL and how to write a simple query. In this article, we will go a little deeper into the different kinds of SQL queries. After that, we will demonstrate the most commonly used queries and highlight their uses and importance. By the end of this article, you’ll be able to create your own relational database from scratch and populate it with data.
To follow all the steps in the tutorial, use SQLite as your relational database system.
There are primarily two types of SQL queries you should know about: Data Definition Language (DDL) and Data Manipulation Language (DML). Our SQL 101 article covered one type of DML query - the SELECT statement. SELECT is the most common type of query that you will come across. These are the queries that you would be working with on a daily basis if you were regularly pulling data from a relational database. Below, we’ll further define DML and DDL queries. One important thing to note is that in normal conversations, DML and DDL are not specified. They are simply referred to as SQL queries. This article will help you be able to distinguish which queries belong to which category.
Data Definition Language (DDL) is the part of the SQL programming language used to maintain the structure of tables. In the simplest form, DDL queries are primarily used to create, update (alter), and delete tables in a relational database. They can be seen as the building blocks of any relational database. Usually, you don’t have to type them in manually if you’re using an advanced tool (QuintaDB). The reason for this is that DDL queries are long and fragile to use. They can single-handedly delete entire databases if the DROP command is accidentally used. For these two reasons, most modern relational database systems make use of a Graphical User Interface (GUI) that asks for confirmation to smoothly get this done.
There are 6 different keywords that indicate a query is a DDL query: CREATE, DROP, ALTER, RENAME, TRUNCATE, COMMENT. The most important and frequently encountered are CREATE, ALTER, and DROP.
The CREATE clause is how tables come into existence. This is where we can specify how many columns our table will have, what data type they will have, and what will be the primary key of it. If you can’t recall what a primary key is, you should read our [Database 101 article] (Link).
There are many data types that can exist in columns. For a full list, check this link. For simplicity, we’ll stick to INTEGER and TEXT.
The syntax for the CREATE clause is: CREATE TABLE + table name + (column1 name + data type, column2 name + data type, …);
In the above screenshot, ‘table name’ is replaced with ‘tutorial’, ‘column1 name’ is changed with ‘name’, and ‘column2 name’ becomes ‘address’. PRIMARY KEY simply defines the column that will be the key.
Executing this query will result in a table being created.
We can also create tables with select columns from pre-existing tables. This method copies all rows that exist in the previous table to the new one. The new table will also use the same column types.
In the above screenshot, we only copied the ‘name’ column. To add more columns, simply enter their names separated by commas. To practice, make tables with different kinds of columns as explained in the data types link above.
The ALTER clause of a query allows users to change the structure of a table without having to delete it. Minor errors can be a typo in column/table names, data types, extra or fewer than needed columns.
NOTE: If you’ve been following the tutorial using SQLite, you won’t be able to drop columns, change their names, or data types. You will have to create a new table to get by this. SQLite is limited compared to other relational database systems, but it’s good for starting out and getting comfortable.
Syntax for the ALTER clause is: ALTER TABLE + table name + ADD column name + data type;
To add a new column to an existing table, apply the syntax in the screenshot below.
‘Tutorial’ will be replaced by the name of your table. After ADD, type in a column name and its required type. Executing this will add a new column to your table. If you had rows in your table before adding the new column, it will now contain NULL values for all rows if it isn’t given a default value.
To rename a table, execute the command as shown below with replacing ‘tutorial1’ with your existing table and ‘tutorial2’ to what you want to rename it to. You can confirm the change by looking to your left as highlighted in red.
DROP has to be the most infamous of all SQL queries. Its notoriety is well deserved because, in the case of not having a proper backup, you can permanently lose data if you’re not careful. This guide explains what to do if you’ve executed a DROP command.
Any query that contains the clause DROP TABLE should only be executed when you’re completely sure you want to go through with it. Not only does this query remove the data inside the table, but it also deletes the table structure as well.
Syntax: DROP TABLE + table name;
By executing the command in the screenshot, you can see we no longer have the ‘tutorial’ table.
Data Manipulation Language (DML) is exactly what it sounds like. This part of the SQL programming language is used to manipulate data. Each of the following queries enables extracting, adding, changing, and deleting data respectively.
The SELECT query is the most common query you will come across. Our SQL 101 article details the SELECT query in depth. See that article to learn more.
INSERT queries are for populating your tables with data. Without which, there would be no data to pull with the SELECT query. Even if you use a software that lets you insert data by manually entering data in the columns, it uses the INSERT query on the backend to make it happen.
Syntax: INSERT INTO + table name + (column1 name, column2 name, …) VALUES (column1 value, column2 value, ...);
Note: If the value you are inserting is text, it needs to be enclosed in quotes.
One important thing to keep in mind is that the values must match the columns. If the column only accepts numbers, we cannot add text to it. Doing so will result in an error.
The INSERT query can also be written without specifying column names if we intend to add values into every single column. This method also demands that the data match the column’s requirements.
You might be wondering if there is a way to speed the process up if you want to insert multiple rows and they are numbered in hundreds. To insert multiple rows with one INSERT statement, you need to follow this syntax. It’s not much different from the previous statement as values of other rows are simply separated by commas.
This method only allows 1000 rows at a time to be added, although we don’t recommend writing 1000 entries by hand.
This clause is used to update currently existing data with new values. This also needs to be used with a bit of caution. If we fail to specify through the WHERE clause which specific information we want to update, we would change the data for every row and that is going to cause problems.
If we would like to update the information of one specific entry, we can add their primary key to the WHERE clause like in the screenshot below.
Syntax: UPDATE + table name + SET + new column value + WHERE condition
We can also make all entries have the same name by removing the WHERE clause.
The DELETE clause removes rows from the table. It has a simple syntax like the previous queries and has the following syntax: DELETE FROM + table name + WHERE condition.
If we want to delete the row which has id # 4, we need to do the following.
If we omit the WHERE clause like in the case of UPDATE, we will delete all our records of the table, much like the TRUNCATE function (not available in SQLite).
