This is part of “An intro to SQL” series of posts, with content from the Enki app. If you stumbled upon this, you can start from the beginning.
Previously, we have looked at how you can extract information from a database using queries, but there may be a time when you will want to create a new database, add a new table or simply edit the ones that already exist.
To create an SQL database, we need to enter the following command:
This command creates a new database called my_first_db. Inside this database you can now start creating tables or insert data in said tables. Keep in mind that each database must hold a unique name. Having done this we can now follow up with creating tables or altering already existing tables.
Note that if the name is not unique, an error is usually thrown. To suppress the error and skip creating a duplicate database, you can use the IF NOT EXISTS:
On the rare occasion where you might want to delete your database you can use the DROP DATABASE command followed by the name of the database you want deleted.
Keep in mind that all the data within the database (including tables and data inside them) will be deleted.
Here is how you would delete the database that was created earlier:
When creating a new table there are some essential rules to follow to ensure good performance:
On top of the rules mentioned above, you must also decide what data you want to include in your table, what name do you assign to the data (column) and what type it is going to be¹.
The process of creating a new table contains two steps. First, you tell the database that you want to create a new table using the CREATE TABLE statement followed by the table name. Next, inside the CREATE TABLE statement, you start defining your columns together with their types, and every subsequent pair of column-type must be preceded by a comma.
To gain a better understanding of this concept let’s look at an example. We want to create a simple table called employees which takes two columns:
Note that if the name is not unique, an error is usually thrown. To suppress the error and skip creating a duplicate table, you can utilize the IF NOT EXISTS clause which is used as such:
To delete a table from a database, you can use the DROP TABLE command followed by the name of the table you want deleted.
Keep in mind that the table and all the information inside it will be deleted. Here is how you would delete the employees table:
[1] Each object in a table holds an attribute (*data type*) that specifies the type of data that it can hold: numbers, text, date & time, monetary data and others.
[2] This type is used for columns which will store a string of characters (can be letters, digits and so on).
[3] An integer represents a whole number or a number which is not a fraction.
Andrei Calabangiu
Content Manager
