INTRODUCTION TO SQL

Mukuri Muturi — Wed, 16 Apr 2025 12:22:31 +0000

SQL stands for Standard Query Language. It is a domain-specific language used to manage data especially in a relational database management system and is particularly useful in handling structured data.
The main of SQL is to;

Retrieve data
Store data
Update data
Delete data

Data meant to be manipulated is stored in a Database which can be described as an organized collection of data.
The query language (SQL) is then accessed through a Database Management System. It is basically a software system that allows users to manage and interact with databases.
There are various database management systems used which have different functionalities but for the case of this article, we will be using PostgreSQL which is a Relational database management system (DBMS); meaning it uses tables with rows and columns.
Columns: these contain the field names of the tables created for example last name or age.
Rows: they contain the data entered by the user for example John and 18.

The first thing to understand when dealing with different DBMSs is the datatypes. Most are similar while others aren't.

Datatypes in SQL

1.Numerical

2.Character/String - used to refer to text in SQL

3.Boolean

4.Date and Time

Now that we are familiar with what is SQl and the datatypes used, let us 'get our hands dirty' and look at the basic commands used in SQL

BASIC SQL COMMANDS

For this article, we will go through the various commands by creating and using tables containing information on my favourite Youtube groups.
1.CREATE. This command is used to create schemas (logical containers within databases that organize database objects like tables, views, indexes, and stored procedures).

It is also used to create tables. The fundamental thing to understand when using SQL is the syntax you are supposed to achieve the desired result. In the case of creating a table, the syntax is;
CREATE TABLE table_name ( column_name data type constraint, column_name data type constraint... ) ;
It is important to use the correct syntax to avoid errors
For our example, let us create three tables each with different groups

After creating the table, it remains empty until we input data into the rows, which leads to us to the next command.

2.INSERT INTO. Just as its name suggest, this command is used to add data into a table.
The syntax for this command is;
INSERT INTO table_name( column_name1, column_name2) VALUES (value1, value2)--_for the first column_, (value1, value2);--_for the second column_

When inputting strings/characters, use quotation marks

3.SELECT. The select command is used to retrieve data from the database we have created.
It is possible to retrieve everything from a particular table:
syntax;
SELECT * FROM table_name; The star (*) represents everything

One can also choose to retrieve some of the columns from the table:
syntax;
SELECT column_name1, column_name3 FROM table_name;

4.ALTER. We can also alter the tables we have already created without having to create new ones from scratch.
We can remove a column from the table:
syntax;
ALTER TABLE table_name drop column column_name;

We can add a new column to our table:
syntax;
ALTER TABLE table_name add column column_name data type constraints;

From the image above, we have successfully created a new column in our SIDEMEN table but we need to add the values in the yellow_cards column.

5.UPDATE. we use the update command to input change the values in already existing rows or inputting values in newly created columns. Let us add values in the column we created above:
syntax;
UPDATE table_name SET column_name = CASE reference_column when reference_value1 then new_value1 when reference_value2 then new_value2... end;

6.WHERE. It is used to filter the data we are able to retrieve. let us say for example we want to see which members from the AMP group are from New York, we can use the WHERE statement to retrieve data only from these members.
syntax;
SELECT * FROM table_name WHERE column_name = reference_value;

From the result we can see that only Kai and Fanum, who live in Ney York, have been retrieved.

7.ORDER BY. This command is used to sort our data. For example we can arrange the Betasquad members based on their age.
syntax;
SELECT * FROM table_name ORDER BY column_name asc/desc;
By default, without specyfying, the order is done in ascending order

8.GROUP BY. Used to group and summarize data and makes use of aggregate functions.
syntax (using count() function);
SELECT column_name, COUNT(*) as new_column_name FROM table_name GROUP BY column_name;
for our example let us group the members of the AMP group according to their cities of residence;

These are basic commands, let us now move to Aggregate functions.

AGGREGATE FUNCTIONS

These are helpful in analyzing a whole group of rows at once. we will look at the easy functions and tackle the advanced ones later on.
1.COUNT(). Used to count the number of rows.
syntax;
SELECT COUNT(*) as new_table_name FROM table_name;
for example to find total number of members in the BETASQUAD group;

2.SUM(). Used to add up numeric values.
syntax;
SELECT SUM(column_name) as new_column_name FROM table_name;
for example, let us add up the ages of the networth of the SIDEMEN members;

3.AVG(). Used to get the average of numerical values.
syntax;
SELECT AVG(column_name) as new_column_name FROM table_name;
for example let us calculate the average net worth of the BETASQUAD members;

4.MAX(). Used to find the maximum among values.
syntax;
SELECT MAX(column_name) as new_column_name FROM table_name;
for example let us find the maximum net worth among the SIDEMEN members;

5.MIN(). Used to find the minimum among numerical values.
syntax;
SELECT MIN(column_name) as new_column_name FROM table_name;
for example let us find the minimum age among the AMP members;

In the next article we go through SQL operators, Intermediate SQL functions and many more.

DEV Community: Mukuri Muturi

INTRODUCTION TO SQL

Datatypes in SQL

BASIC SQL COMMANDS

AGGREGATE FUNCTIONS