In this tutorial we will create a simple SQL table. I will be demonstrating this in MySQL5.7. If you would like information on how to install and setup a MYSQL Command Line Client, I would advise you to visit this link.

To create a table, we could directly type our commands into the command line but, in case of any errors that occur we will use a MySQL script file so that it is easier to correct mistakes in typing. This script file will have commands that are a part of the Data Manipulation Language (DML), which is a sublanguage of SQL that allows for the modification and retrieval of information from database objects. The reason for this, from painstaking mistakes on my part, is to make it as easy as possible to execute commands. Let's take a look at the code below.

                                
CREATE DATABASE test_db;

USE test_db;

CREATE TABLE students(
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_fname VARCHAR(40),
    student_lname VARCHAR(40),
    address VARCHAR(100),
    gpa FLOAT(3, 2)
);
                                
                                Code copied
                            

Use any text editor you like, I happened to use Notepad++. Also make sure to save the file as .sql. The first line will use the DML command CREATE DATABASE, which will create our database, I happened to name it test_db but feel free to name it what you want. The next command allows us to USE this database. The CREATE TABLE command will create our student table which holds three different data types. The first is an integer, which is used for the student ID, a necessary item in the case of students who may share the same name. This id is a primary key which means that it is a unique identifier. There is also an added command of auto increment which automatically increments the primary key by one at each addition to the table. The next data type is a VARCHAR, a character string data type that takes in an argument of an integer that specifies the maximum number of characters allowed in the string. Lastly, we have a FLOAT data type which contains the students gpa. This float takes in two arguments, the first is the number of digits we want to specify and the second argument is for the number of digits after the decimal point. Another important point to note is that each command is ended with a semicolon. This helps with the compiler to indicate when a command ends and a new command is specified. If you'd like to run the script, we use the SOURCE command. This command will be followed by the path to your .sql file. If you would like to see what you've done, in the command line you can type DESC students, which will display the data types and column names of your table.

In conclusion, the importance of using a script allows the programmer to make edits in the case of errors, which is incredibly likely. It is unlikely that you will be able to edit a previous statement and will have to retype the entire command. Now that we know how to create a database and a table, our next step would be to insert data into the table in another blog post.