Hello.

Maybe you, like me, would like to have a place where you can find all the basic commands with some examples so you don't have to go back and look for the information you need.

So this article is about all the SQL commands that we encounter during the course "Learn Relational Databases by Building a Mario Database" by freeCodeCamp and we will examine the basic SQL commands, which are used to create, modify, and query data within a database. We will learn how to create tables, insert, modify, and delete data, and how query the data for specific information. These commands form the basis for working with a relational database and are essential for anyone who wants to develop applications that use a database.

By the end of this article, you will know all the commands you will find in the course and I will also provide some examples.

Want a preview? Here's a list of the main SQL commands you'll encounter during the "Learn Relational Databases by Building a Mario Database" course:

• CREATE DATABASE - used to create a new database.

• CREATE TABLE - used to create a new table within the selected database.

• SELECT - used to select data from database tables.

• INSERT INTO - used to insert new rows of data into a table.

• UPDATE - used to modify existing data in a table.

• DELETE - used to delete data from a table.

• ALTER TABLE - used to change the structure of an existing table, such as adding or deleting columns.

• DROP TABLE - used to delete a table from the database.

• FOREIGN KEY - used to define a foreign key in a table, which is used to establish a relationship with another table.

• JOIN - used to join data from two or more tables in a single query.

  There are several types of joins, such as INNER JOIN, LEFT JOIN and RIGHT JOIN.

• ORDER BY - used to sort the results of a query by one or more columns.

These are some SQL commands you may encounter during the course, but there are many other commands and functions available in SQL, so it is important to keep learning and practicing to become an expert in using the language.

Honorable Mentions (not found in this course):

• USE - used to select an existing database and make it active.

• GROUP BY - used to group data in a table by one or more columns.

• HAVING - used to filter the results of a query using GROUP BY.

Foreword

This course uses VSCode and an add-on called CodeAlly that allows us to follow tutorials directly in the VSCode interface. If you are a novice, know that nothing needs to be installed!

The entire course runs on our browser and uses multiple different technologies.

The course is still in beta and something could go wrong.

If you run into any problems with the course or want to know tricks to gain efficiency, you can read my previous article for further help but remember that the official freeCodeCamp forum is the best place to get help of any kind!

Theory

Are you a complete beginner and don't even know what I'm talking about? Then let's start with some theory on the basic concepts.

What is SQL?

SQL (Structured Query Language) is a standard language for managing data in a relational database. Many database systems support SQL, such as MySQL, Oracle, and Microsoft SQL Server, making the language very popular and widely used in various fields, such as business, financial analysis, and website development.

What is a database?

A database is a collection of data that is organized in a specific way, making it easy to access, update, and analyze. There are several different types of databases, but they all serve the same basic purpose: to store and manage data.

In a database, data is typically organized into tables, which are similar to spreadsheets.

Databases are particularly useful for storing and managing large amounts of data and for performing complex queries and analyses on that data. Some common examples of databases include relational databases, object-oriented databases, and NoSQL databases.

What is a Relational database?

A relational database is a type of database that stores and organizes data in tables. These tables are connected by relationships, which allows the data to be more easily accessed and updated.

In a relational database, each table contains a set of rows (also known as records) and columns (also known as fields). Each row represents a unique record, and each column represents a specific piece of information about that record. For example, a table in a database might contain information about employees, with columns for the employee's name, job title, salary, and hire date.

One of the key features of a relational database is that it allows us to create relationships between different tables. For example, we might have a table of employees and a table of departments, and we could create a relationship between these two tables based on the department in which each employee works. This makes it easier to retrieve and analyze data from the database, as we can easily find all the employees in a particular department or see how much a department is spending on salaries.

PostgreSQL

This course in particular uses PostgreSQL.

What is it??

PostgreSQL is a powerful, open-source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

https://www.postgresql.org/

I would like to reiterate that to take this course we do not need to install any software, but if instead, we want to create a database on our computer we will need to install Visual Studio Code and PostgreSQL.

Oh yes, I almost forgot: It's all free and there are no hidden fees.

TIPS

How can we save time and increase our effectiveness?

Comfort

How to organize the interface of VSCode?

• Right-click on the title bar of the terminal -> Move Panel Right

• Use F11 to go full screen and get extra space.

  It doesn't work if you have the focus on the terminal!

Below is my ideal configuration.

Speed

• How to scroll the terminal without using the mouse?

  SHIFT + PAGE UP / PAGE DOWN

THE DATABASES

Connect to the PostgreSQL database server

The first thing to do is to connect to the PostgreSQL database server. This course already has a default account, so we can take advantage of it and immediately dive into the amazing world of databases.

To connect with the user "freecodecamp" to a PostgreSQL database server named "postgres" we type the following command:

psql --username=freecodecamp --dbname=postgres

We notice that our terminal has changed.

Show a list of all databases

To show the list of all existing databases, simply write:

\l

Create a database

We can create a new database using the following syntax:

CREATE DATABASE database_name;

For example:

CREATE DATABASE first_database;

PLEASE NOTE the semicolon at the end of the command.

Connect to a database

To connect to a specific database write:

\c database_name

Rename a database

The syntax is as follows:

ALTER DATABASE database_name RENAME TO new_database_name;

For instance:

ALTER DATABASE first_database RENAME TO mario_database;

Delete a database

The syntax is as follows:

DROP DATABASE database_name;

For example:

DROP DATABASE second_database;

THE TABLES

Show tables

To show all the tables in the database to which we are connected write the command

\d

PLEASE NOTE There is no need for the semicolon at the end.

Later in the course, we will learn how to show various details about the structure of a specific table, writing

\d table_name

HINT: You can use TAB to take advantage of auto-completion and not have to type the name of the table

Create a table

To create a table within a database to which we are connected we can write, for example:

CREATE TABLE first_table();

PLEASE NOTE The round brackets and the semicolon.

Later in the course, we will discover that while creating a table we can define and create columns, with the following syntax:

CREATE TABLE table_name (column_name DATATYPE CONSTRAINTS);

E.g.:

CREATE TABLE sounds (sound_id SERIAL PRIMARY KEY);

Delete a table

For example, to delete a table called 'first_table' write:

DROP TABLE first_table;

HINT: You can also write everything in lowercase letters and add a few spaces. The command interpreter won't get mad if you do this. For example:

drop table first_table ;

THE COLUMNS

Create a column

To create a column in a table we use the following syntax:

ALTER TABLE table_name ADD COLUMN column_name DATATYPE;

Where DATATYPE expresses the column format that can represent an integer (INT), for example:

ALTER TABLE second_table ADD COLUMN first_column INT;

DATATYPE also allows a string of characters of a specified maximum length to be represented.

A string of up to 30 characters in length:

ALTER TABLE second_table ADD COLUMN name VARCHAR(30);

But it can also represent a date:

ALTER TABLE more_info ADD COLUMN birthday DATE;

Or a decimal number:

ALTER TABLE more_info ADD COLUMN weight NUMERIC(4, 1);

Later in the course, we will discover the usefulness and necessity of having an integer number that is unique to each row (typically called an ID). This number will automatically increment by one unit and it will be associated with each row when it is created. To do this we will need the SERIAL datatype.

For example, to create a column of type SERIAL called 'character_id':

ALTER TABLE characters ADD COLUMN character_id SERIAL;

The column in this case will still be of type INT, but it will increment automatically and cannot have a NULL value. The NOT NULL constraint ensures that each row will possess a value associated with the id column. This constraint can also be added without using a counter, for example:

ALTER TABLE characters ADD COLUMN name VARCHAR(30) NOT NULL;

Other types of restrictions can be used to reinforce a "1-to-1" relationship, for example:

ALTER TABLE more_info ADD UNIQUE(character_id);

And we can also add restrictions later, after creating the column, for example:

ALTER TABLE more_info ALTER COLUMN character_id SET NOT NULL;

PLEASE NOTE: The SET command

Delete a column

To delete a column in a table we use the following syntax:

ALTER TABLE table_name DROP COLUMN column_name;

E.g.:

ALTER TABLE second_table DROP COLUMN age;

Rename a column

To change the name of a column to a new one we use this syntax:

ALTER TABLE table_name RENAME COLUMN current_column_name TO new_column_name;

E.g.:

ALTER TABLE second_table RENAME COLUMN name TO username;

Thanks to all these commands, we are now able to create a database, connect to it, create a table, and manipulate the columns. The columns don't contain data, though; they serve as the structure of the database. We need one more step to populate our database, which is to insert rows.

THE ROWS

Insert a row

To insert a row into our table, the syntax is as follows:

INSERT INTO table_name (column_name1, column_name2) VALUES (first_value, second_value);

NOTE: You can put a space between the table name and the parenthesis that contains the column names

INSERT INTO second_table (id, username) VALUES(1, 'Samus');

It also works if we leave a space after the VALUE command:

INSERT INTO characters (name, homeland, favorite_color) VALUES ('Mario', 'Mushroom Kingdom', 'Red');

Later in the course, we will find out how to specify that a field contains no data, for example:

INSERT INTO more_info (birthday, height, weight, character_id) VALUES ('1989-07-31', NULL, NULL, 6);

And that we can also add multiple rows at the same time, for example:

INSERT INTO sounds(filename, character_id) VALUES('mm-hmm.wav', 3), ('yahoo.wav', 1);

Display information

To display the information contained in our table, we can use the query string:

SELECT column_names FROM table_name;

For example:

SELECT * FROM second_table;

Using the wildcard we select all the columns of the table called "second_table" and if we want we can also show the records of our table following a particular order, for example, ID number:

SELECT * FROM characters ORDER BY character_id;

Later in the course, we will discover that instead of showing all rows we can filter the result with the WHERE condition, e.g.:

SELECT character_id, name FROM characters WHERE name = 'Toad';

Finally, we will discover how to show results by joining multiple tables via their primary key/foreign key.

SELECT columns FROM table_1 FULL JOIN table_2 ON table_1.primary_key_column = table_2.foreign_key_column;

For example:

SELECT * FROM characters FULL JOIN more_info ON characters.character_id = more_info.character_id;

At the end of the course, we will learn how to show the value of three tables using one command:

SELECT columns FROM junction_table FULL JOIN table_1 ON junction_table.foreign_key_column = table_1.primary_key_column FULL JOIN table_2 ON junction_table.foreign_key_column = table_2.primary_key_column;

Delete a row

To delete a record (a row) we use the following syntax:

DELETE FROM table_name WHERE condition;

For instance:

DELETE FROM second_table WHERE username = 'Luigi';

NOTE: We use single quotes here, not double quotes. Spaces are optional and writing username='Luigi' is equivalent.

Edit a record

UPDATE table_name SET column_name = new_ value WHERE condition;

For exemple:

UPDATE characters SET favorite_color = 'Orange' WHERE name = 'Daisy';

THE KEYS

Add a primary key

They are used to express the uniqueness of the value in the column. It serves as the table index, and there can only be one primary key per table. Having said that, we can see why one should always be created for each table. The syntax is as follows:

ALTER TABLE table_name ADD PRIMARY KEY(column_name);

E.g.:

ALTER TABLE characters ADD PRIMARY KEY(name);

Or a better example:

ALTER TABLE characters ADD PRIMARY KEY(character_id);

Toward the end of the course, we will discover that we can designate TWO columns as the primary key, creating in this case a compound primary key. This is the syntax:

ALTER TABLE table_name ADD PRIMARY KEY(column1, column2);

Remove a restriction

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

We can see the restrictions by writing "\d table_name".

Some of them are under "indexes" and we can find out the name of the restriction this way to remove it later with the DROP command. For example:

ALTER TABLE characters DROP CONSTRAINT characters_pkey ;

Adding a foreign key

There are cases where a column refers to another column found in a different table. In these cases, a different type of restriction called a Foreign Key must be added. Useful for expressing 1-to-1 and 1-to-many relationships. Also, using join tables (two 1-to-many) many-to-many relationships can be expressed. For example:

ALTER TABLE more_info ADD COLUMN character_id INT REFERENCES characters(character_id);

Here is the complete syntax:

ALTER TABLE table_name ADD COLUMN column_name DATATYPE CONSTRAINT REFERENCES referenced_table_name(referenced_column_name);

Another example, but this one includes a CONSTRAINT :

ALTER TABLE sounds ADD COLUMN character_id INT NOT NULL REFERENCES characters (character_id);

But if our column already exists, how can we add a foreign key? Like this:

ALTER TABLE table_name ADD FOREIGN KEY(column_name) REFERENCES referenced_table(referenced_column);

CONCLUSION

These basic SQL commands are fundamental to managing data in a relational database. We have seen how to create tables, insert, modify, and delete data, and how run queries to get specific information from the database. With these commands, we are now ready to start working with a relational database and developing applications that make use of it. However, the SQL language offers many other advanced commands and features, so we should not hesitate to deepen our knowledge of the language to exploit the full potential of our databases.

This is my second article on medium for the 2022 Become-a-Dev New Year's Resolution Challenge Step #4: "Publish 3 tutorials on your Hashnode blog" I hope you enjoyed it.