SQL: RDBMS, creating and dropping tables

Relational database management system

A system used to maintain relational databases is a relational database management system (RDBMS). There are numerous RDBMS, the most widely used systems are:

1. Oracle Database
2. MySQL
3. Microsoft SQL Server
4. PostgreSQL (free software)
5. IBM Db2
6. Microsoft Access
7. SQLite (free software)
8. MariaDB (free software)
9. Snowflake
10. Microsoft Azure SQL Database
11. Apache Hive (free software)
12. Teradata Vantage

For this series of posts we will use MySQL. MySQL is a well-known, free and open-source database application. Its high performance, ease of use and data security makes it a popular database solution.

But the focus of the course is more on SQL itself than on particular RDBMS.

Creating table in MySQL

The general syntax for creating a table in MySQL is:

𝙲𝚁𝙴𝙰𝚃𝙴 [𝚃𝙴𝙼𝙿𝙾𝚁𝙰𝚁𝚈] 𝚃𝙰𝙱𝙻𝙴 [𝙸𝙵 𝙽𝙾𝚃 𝙴𝚇𝙸𝚂𝚃𝚂] 𝚝𝚊𝚋𝚕𝚎_𝚗𝚊𝚖𝚎(  

   𝚌𝚘𝚕𝚞𝚖𝚗_𝚗𝚊𝚖𝚎𝟷 𝚍𝚊𝚝𝚊_𝚝𝚢𝚙𝚎 𝚊𝚍𝚍𝚒𝚝𝚒𝚘𝚗𝚊𝚕_𝚙𝚊𝚛𝚊𝚖𝚎𝚝𝚎𝚛𝚜,

   𝚌𝚘𝚕𝚞𝚖𝚗_𝚗𝚊𝚖𝚎𝟸 𝚍𝚊𝚝𝚊_𝚝𝚢𝚙𝚎 𝚊𝚍𝚍𝚒𝚝𝚒𝚘𝚗𝚊𝚕_𝚙𝚊𝚛𝚊𝚖𝚎𝚝𝚎𝚛𝚜,

   ...,

   𝚌𝚘𝚕𝚞𝚖𝚗_𝚗𝚊𝚖𝚎𝟹 𝚍𝚊𝚝𝚊_𝚝𝚢𝚙𝚎 𝚊𝚍𝚍𝚒𝚝𝚒𝚘𝚗𝚊𝚕_𝚙𝚊𝚛𝚊𝚖𝚎𝚝𝚎𝚛𝚜

);

A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed.

[IF NOT EXISTS] verifies if there is an identical table in the database. The query will not be executed if an identical table already exists.

Let's create employee table:

"NOT NULL" specifies that the value cannot be missing. We will talk about keys in a separate post.

You can learn about different data types from the picture below:

Dropping table in MySQL

To remove a table in MySQL, use the DROP TABLE statement. The basic syntax of the command is as follows:

DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] [RESTRICT | CASCADE];

Let’s break down the syntax:

• The DROP TABLE statement deletes a table and its rows permanently.
• The [TEMPORARY] option ensures you remove temporary tables only.
• The [IF EXISTS] option drops a table only if it exists.
• The [RESTRICT] option will be available in future iterations of MySQL. It ensures that a parent row is not deleted if a child row is referencing a value in said parent row.
• The [CASCADE] option ensures that when you delete a row, all rows in related tables that reference that row are deleted as well. This option will be available in future iterations of MySQL.

RESTRICT and CASCADE options will become more clear in the next SQL post where we introduce keys.