A Comprehensive Guide to SQL for Beginners: Creating a Database

Published in

Towards Dev

14 min readNov 24, 2023

So, you’re in a bind and need to learn everything you can about SQL and fast… Maybe you have an interview coming up where you may have exaggerated your skillset a smidge or you went to one too many parties to study for that SQL final that’s going to be 50% of your grade. Well have no fear… your SQL guru (yes, me) is here to help. I am going to take you from zero to hero in SQL and you’ll be showing off those coding chops in time for that test!

Outline

In Part One of this series, we are going to be going over the very basics of creating an SQL database. We need to know all of SQL’s functionalities before we can move on to more advanced topics. Here’s what you’ll know after finishing this article:

RDBMS
SQL Commands
Data Types
Primary & Foreign Keys
Constraints
Creating, Modifying, & Deleting Tables
Creating, Modifying, and Deleting Records

This article will give you an introduction into all of these simple topics in creating an SQL database so that you can later learn how to script in SQL like a pro!

RDBMS

If you’ve ever been in the world of tech, you’ll here this acronym thrown around constantly. Usually when it comes to random acronyms, I just try to smile and nod along, but RDBSM is something that you’ll want to know like the back of your hand in any SQL interview or test. RDBMS stands for Relational Database Management System. Now you’re thinking “what is that? That sounds complicated and confusing.” It’s actually quite simple. A Regional Database Management System is basically any system that holds data in the form of tables with rows and columns rather than any other form. There are several other forms of DBMS (Database Management Systems) because there are several other ways that data can be stored and managed, but all you have to remember is that RDBMS hold tables to organize its data. That’s it. Some good examples of RDBMS are MySQL, PostegreSQL, and Oracle.

Now you’re asking “well, what does this have to do with SQL? Is SQL a RDBMS?” Well… not exactly. SQL is essentially the only language that a RDBMS can understand. If we need to retrieve something from a table in our RDBMS, we need to ask for it. How can we ask? Using SQL. We want to be able to communicate with any RDBMS so that we can create, retrieve, update, and delete any of the data within the RDBMS with ease. This is the purpose of SQL and learning it will change the way that you see data forever.

SQL Commands

This is a very broad topic so I’m going to teach you the most fundamental commands and build on it later. First, what is an SQL command? An SQL command is just an action that you want your RDBMS to perform in order to create, retrieve, update, or delete some data in your database (table or set of tables). Here are some of the most important commands in SQL:

SELECT — A SELECT command is to retrieve some specific data from the database
CREATE TABLE— The CREATE TABLE command creates a new table in our database
ALTER TABLE — The ALTER TABLE command is to modify or update a table in our database
DROP TABLE — The DROP TABLE command deletes a table from our database
INSERT INTO — The INSERT INTO command inserts data into one our our tables
UPDATE — The UPDATE command is used to modify or update data in one of our tables
DELETE FROM — The DELETE FROM command deletes data from one of our tables

These commands are the building blocks of SQL, you will use these again and again in any SQL code that you create, so make sure to get really familiar with these commands and what they do.

Data Types

Each column in any of our tables in our database has something called a data type. This is exacly what it sounds like: the type of data that we want our column to hold. Data types are used to categorize the data in our database so that we can keep our database organized. The most common data types in SQL are:

INT — INT is short for integer and refers to any integer value
DECIMAL(M,N) — DECIMAL is a number with a decimal point where M is the maximum total number of digits and N is the maximum total number of digits after the decimal
VARCHAR(N) — This is a string of text that must always be in quotes, where N is the maximum number of characters
BLOB — This is a Binary Large OBject, it basically refers to large amounts of data i.e images and files
DATE — A date formatted as ‘YYYY-MM-DD’
TIMESTAMP — A date & time formatted as ‘YYYY-MM-DD HH:MM:SS’

When creating a table or a column in a table in the database, we must indicate what kind of data we are inserting into our table. This way all of our data is formatted, clean, and the RDBMS can recognize the funtions that each column can and cannot perform.

Creating a Table

Now that we know the command to create a table and we know that we need to indicate which data types are going into our table, we can finally attempt to create a table in our database. Let’s create a table called “student”. Here’s how:

CREATE TABLE student(
  name VARCHAR(20),
  age INT
);

Okay, let’s break this down. Here we used the command CREATE TABLE to create a table in our database, we gave it a name which is “student”, and we added two columns. The first column is called “name” and it can hold data in the form of a VARCHAR element (string of text) that can have up to 20 characters. The second column is called “age” and it can hold data in the form of an INT (any integer value).

Let’s create another table called “professor” that has 2 columns: a “name” column and a “startdate” column:

CREATE TABLE professor(
  name VARCHAR(20),
  startdate DATE
);

Here we used the command CREATE TABLE again to create a table called “professor”. We added a “name” column which can hold VARCHAR elements up to 20 characters and a “startdate” column which can hold a date in the form of ‘YYYY-MM-DD’.

Great! We’ve created the first tables in our database, the “student” table and the “professor” table.

Primary Keys

So, we’ve created our student and professor tables and are ready to add data into them, right? Not just yet. There is still something important that we need to consider first. What if we have two students with the same name and age? Certainly, stranger things have happened. How do we tell them apart in our database? That’s a great question and the answer is simple, with the PRIMARY KEY!

A PRIMARY KEY refers to a column that uniquely identifies each record (row) in our table. This can be a trivial value, or it can be something significant that represents the record in the real world, like a SSN. Really it is just a unique identifier of each row, so that even if all other values are the same, the database can still set each record apart. To indicate that a column will hold the PRIMARY KEY, you must put the PRIMARY KEY keyword after declaring the column name and type.

So, let’s delete our previous tables and recreate them with a primary key.

DROP TABLE student;
DROP TABLE professor;

CREATE TABLE student(
  student_id INT PRIMARY KEY,
  name VARCHAR(20),
  age INT
);

CREATE TABLE professor(
  professor_id INT PRIMARY KEY,
  name VARCHAR(20),
  startdate DATE
);

First in our code, we used the DROP TABLE command to delete the student and professor tables that we created before. Then we used the CREATE TABLE command again to recreate them with the columns that we want.

We’ve created 2 tables called student and professor where the student table has a PRIMARY KEY called “student_id” which is an integer value, and the professor table has a PRIMARY KEY called “professor_id” which is also an integer value.

Note: You can also declare your PRIMARY KEY after all of the columns are set. For example:

CREATE TABLE student(
  student_id INT,
  age INT,
  PRIMARY KEY(student_id)
)

Constraints

A constraint is something that you add to your table when you want one or more columns to adhere to specific rules. For example, PRIMARY KEY is a constraint. If a column is made to be a primary key, it must be unique and it cannot be empty. This is because it is the unique identifier of the record. If you try to add a record where the primary key does not adhere to these rules, an error will be indicated in your code. Constraints help the DBMS identify the rules and boundaries that each column must adhere to. Here is a list of important constraints:

NOT NULL — allows us to define that a particular column in the table cannot be empty aka NULL
UNIQUE — allows us to define that a particular column in the table cannot have duplicate entries
DEFAULT — allows us to add a default value if nothing is specified
AUTO_INCREMENT- automatically increments a value with every additional table entry

If we wanted to create a table to demonstrate how constraints work, we could create this table called “courses”:

CREATE TABLE courses(
  id INT PRIMARY KEY,
  course_name VARCHAR(20) UNIQUE NOT NULL,
  textbook VARCHAR(40) DEFAULT 'Basic'
);

Let’s break this down. Here we created a table called courses. The first column is called “id” and holds the primary key. The second column, “course_name” is a VARCHAR and has two constraints. “course_name” is both unique and not NULL. The third column is called “textbook”, if no value is added in that field, the value will be filled in as “Basic”.

Constraints are extremely useful to specify what rules we want our data to follow in our database!

Inserting Data into a Table

Now that we have completed our tables, we can add data to them in the form of rows or records. Let’s add a record to our “student” table.

INSERT INTO student VALUES(1, 'Harry', 11);

Here, using the INSERT INTO command, we have inserted the values 1 as the “student_id”, Harry as the “name”, and 11 as the “age”.

If we want to insert several records into our table, we can do so by adding more groups of values separated by a comma, like this:

INSERT INTO student VALUES
  (2, 'Ron', 11),
  (3, 'Hermione',11),
  (4, 'Percy', 16);

We’ve now added 4 different records into our student table. When we retrieve it using our SELECT command (more on this later), it should look like this:

SELECT *
FROM student;

Here we used the SELECT command to retrieve our data. We won’t be going over the SELECT statement in this part of our series, but just know that the * indicates that all data will be retrieved FROM the table specified. From now on, any time I show a table, I’m using the SELECT command to retrieve the data.

We can also signify that we only want to add values into specific columns. Let’s see how using our “courses” table:

INSERT INTO courses(id,course_name) VALUES (203, 'Potions');

If you recall from the last section, our “courses” table has a column called “texbook” which has a DEFAULT constraint. If we don’t specify a value for “textbook”, SQL will add the default value, which is written as “Basic”. In the code above, we used the INSERT INTO command to only add values to the “id” and “course_name” fields. Our “courses” table should look like this after running the code above:

Deleting Data from our Table

Let’s say, we only want first years in our student table. We know that all first years are 11 years old. That means we can delete any records from the student table where the age is not equal to 11. Note that in SQL, the symbol for “is not equal to” is “<>”. We can do this by using the DELETE FROM command, and a keyword we haven’t seen yet called WHERE.

DELETE FROM student WHERE age <> 11;

As you can see above, the WHERE keyword specifies a condition. We use the WHERE keyword when we want to tell our DBMS to do our command only to the records from the table that follow a specific condition. When we run this code, it will delete all of the records from student table WHERE the age is not equal to (<>) 11.

Altering our Table

Deleting Columns

There are two very important ways to alter a table: to add columns and to delete columns. Let’s take a look at deleting a column first. We previously deleted all of the records in our “student” table where the age was not equal to 11. Now that we have a table with only first year students, and we know that each of the students is 11 years old, the age column seems a bit redundant. Let’s get rid of it:

ALTER TABLE student DROP COLUMN age;

Here we have the ALTER TABLE command paired with a (new) DROP COLUMN command. When we run this, the “age” column is deleted. Now we have a table with just the remaining columns, “student_id” and “name”:

Adding Columns

Now let’s take a look at adding a column to our table. Say we want to indicate who our student’s homeroom professors are. We could add a “professor_name” and “professor_startdate” column to the student table to show who each student’s professor is. But that’s a bit unnecessary, isn’t it? If you recall, we already have a professor table with that information in it. If we want to find out specific data about a professor, we should only be refering to the professor table. How then, can we succinctly identify which professor is for which student? By using the “professor_id” of course! This is the unique identifier of each professor in our database; we can use that to refer to the correct professor and look up more information in the professor table later if we need to. Let’s add a column for “professor_id” in our student table:

ALTER TABLE student ADD professor_id INT;

Once we run this, we get:

Notice that the value is NULL for “professor_id” in each student record. This means that there is no value listed in that column for a particular record. Our “professor_id” was created after the student table already had 3 records, so we have no values in the “professor_id” column yet. Before we can populate that column in our student table, we must create some professor records.

Foreign Keys

A foreign key refers to a column which references another table’s primary key. This is exactly the situation that we’ve just created. In the table above, the student table has a “professor_id” column which we would like to reference the “professor_id” column in our professor table. Before we can add any values to “professor_id”, we’ll need to create some professor records, so that we can know which professors our student table is referencing. Let’s add 3 professors:

INSERT INTO professor VALUES
  (101, 'Snape', '1981-09-01'),
  (102, 'Hagrid', '1993-07-15'),
  (103, 'McGonagall', '1956-12-01');

Here’s our professor table now:

Now that we’ve added professors to our professor table, we can populate our “professor_id” column in our student table. Before we do that though, we want to indicate to our DBMS that our student “professor_id” is refering to the professor “professor_id”. To do this, we need to add a foreign key to our student table. Take a look:

ALTER TABLE student 
ADD FOREIGN KEY (professor_id) 
REFERENCES professor(professor_id)
ON DELETE SET NULL;

Let’s break this down:

Line 1 — We are using the ALTER TABLE command to let the DBMS know that we are altering the student table.
Line 2 — We are using the ADD FOREIGN KEY command to indicate that the “professor_id” in the student table is a foreign key.
Line 3 — We are using the REFERENCES keyword to say that the foreign key is referencing the “professor_id” column in the professor table.
Line 4 — ON DELETE SET NULL tells the DBMS that if the reference record is deleted from the professor table, it should set the corresponding student records to NULL in the “professor_id” column.

After letting the DBMS know that the student table’s “professor_id” column is a foreign key, we can then populate it.

Updating Records

We want to update our “professor_id” column in our student table for each record. Let’s say Harry and Ron have Snape as a professor. We know that Snape’s id is 101, so lets add that value to our “professor_id” field for Harry and Ron:

UPDATE student
SET professor_id = 101
WHERE student_id < 3;

Let’s analyze this code together:

Line 1 — We use the UPDATE keyword when we want to modify a record or records in a table. In this case we are updating our student table.
Line 2 — The SET keyword lets the DBMS know what value you want to change in your table. In our case, we want the “professor_id” to be equal to 101.
Line 3 — Here’s that WHERE keyword again. The WHERE keyword specifies what conditions the records need to meet for the previous command to apply. This WHERE statement is letting us know that any record with a “student_id” that is less than 3 (i.e. 1 and 2 for Harry and Ron) will set their “professor_id” to 101.

Here’s what we get after running this code.

Let’s add McGonagall as Hermione’s professor.

UPDATE student
SET professor_id = 103
WHERE student_id = 3;

SELECT *
FROM student;

Conclusion

We have succesfully created a database! We went from knowing no SQL to now knowing what a relational database management system is, how SQL fits in, what goes into creating a database, and how to create, modify, and delete tables and records within our database! We created 3 tables in our database, added constraints and primary keys, and connected some of them through the use of foreign keys. I encourage you to play around with these concepts! You can add more records to each table, add another foreign key to the student table referencing the courses table, add another table which is a combination of two existing tables. The possibilities are endless!

Now I know that you’re dying to know how to retrieve all of the data that I showed you in this tutorial in a dynamic way, you’re asking yourself what is SELECT * and why did she have me use that every time I wanted to look at my tables. Fear not! I will explain everything in the next install of this SQL series called “A Comprehensive Guide to SQL for Beginners: Queries.” Master creating a database first, and stay tuned for the next install of this series! Happy coding!

Acknowledgements

I want to thank two majorly important helpers in my journey creating this article. First, I need to thank TechTFQ on YouTube for giving me all of the guidelines for what needs to be learned from beginning to end in SQL. His video was an incredible help to me in creating the outline for this article and the articles to come and I highly recommend watching it to get a sense of what you need to learn at which stage while learning SQL. Secondly, I’d like to thank freeCodeCamp.org. I took quite a bit of inspiration for the material in this article from them and I would be remiss not to include their information in this article. Find the videos that helped me along the way below, and be sure to check them out if you’re a more hands-on learner!