Let’s learn the basics of SQL

Malindu Sasanga

4 min readJun 12, 2021

S Q L : Structured Query Language

There are mainly two types of databases.

Relational DB
Non-Relational DB

Let’s see what are these two before diving into SQL.

Relational DB

Organize data into one or more tables

Each table has columns and rows
A unique key identifies each row

Ex: Microsoft SQL Server, Oracle, MySQL, PostgreSQL

src: https://www.plus2net.com/sql_tutorial/table-records.php

Non-Relational DB

Organize data is anything but a traditional table

Key-value stores
Documents (JSON, XML)
Graphs
Flexible tables

Ex: MongoDB, Apache Cassandra, Apache HBase

src: https://docs.microsoft.com/en-us/azure/architecture/data-guide/big-data/non-relational-data

Different Types of Keys in SQL

Primary key

Use to uniquely identify each record in a table.

2. Foreign key

A field in one table, that refers to the Primary key in another table.

3. Composite key

A combination of two or more columns in a table to uniquely identify each row of the table.

Common Data types in SQL

INT : integers

VARCHAR(size) : variable characters(letters, numbers, and special characters)

BLOB : binary large objects

BOOL : 0 = false, 1 = true

FLOAT(size, d) : floating-point number. The total number of digits specified in size and d specifies the number of digits after the decimal point.

DATE : date format YYYY-MM-DD

Getting started with SQL

Installation

Step 1

Download and install the MySQL community server. rehttps://dev.mysql.com/downloads/mysql/

Then create a database using MySQL Command Line Client.

create database studentdb;

Step 2

Download and Install the modern SQL editor PopSQL

https://popsql.com/

After the installation, it will ask for the database name and provide studentDB as the database.

Now the installation process is finished and let’s dive into code :)

Create Table

We are going to create the below table using SQL.

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

Here the syntax explanation.

We can create the table using CREATE TABLE

We chose student_idas the PRIMARY KEY

name is a string and we define it as a VARCHAR(20) with a length of 20

subject also like the same.

Then we run the code in PopSQL

We have successfully created the table.

Add Data to table

Let’s add data to the newly created table.

INSERT INTO student VALUES(1, 'Sean', 'Maths');

Then click on the code line (line 7) and then you can see a green line in left corner. Then click on the Run button.

click on line

To see the table we created,

SELECT * FROM student;

We can see the student table with the newly added data.

Let’s add some more data to the table.

INSERT INTO student VALUES(2, 'Jack', 'History');
INSERT INTO student VALUES(3, 'Anne', 'Biology');
INSERT INTO student VALUES(4, 'Peter', 'History');
INSERT INTO student VALUES(5, 'Bruno', 'Maths');

Then run SELECT * FROM student; and see the newly added rows in the student table.

Update table

UPDATE student
SET subject =  'Maths'
WHERE student_id = 4

We can update the subject at 4 th student_id using the above script.

WHERE : to specify where need to be updated

SET : assigns the new value

After running the code you can see the table updates.

Let’s see some examples with the update table.

UPDATE student
SET subject =  'Mathematics'
WHERE subject = 'Maths';

Then run SELECT * FROM student; and see the updated rows in the student table.

UPDATE student
SET subject =  'Science'
WHERE subject = 'Mathematics' OR subject = 'Biology';

Delete a row

DELETE FROM studentWHERE student_id = 3;

Updated table:

Delete a table

DROP TABLE student;

If we DROP the table, it will delete from the database.

We discussed some very basics of SQL. Hope you have interested.

Thanks for reading :)