What is SQL and how does it work?

YatinSeptember 9th, 2021Last Updated: January 10th, 2022

0 28 4 minutes read

Hello. In this tutorial, we will learn and understand what SQL is and how does it work.

1. Introduction

SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –

Parsing – Process to check the SQL query syntax
Binding – Process to check the SQL query semantics
Optimization – Process to generate the SQL query execution plan

1.1 Usage of SQL

Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too:

Execute queries against the database
Retrieve data from the database
Insert new records into the database
Update existing records into the database
Created stored procedures, functions, and materialized views in the database
Create users and grant permissions
Set permissions on tables, stored procedures, functions, and materialized views

1.2 Exploring some basic SQL commands

Let us explore some basic SQL commands that one needs to know and learn. Here are some commands that I am a fan of and will be exploring as well.

CREATE – Creates a new database, table, materialized view, or another object in the database
INSERT – Adds a new record in the table
UPDATE – Update an existing record in the table
DELETE – Delete an existing record from the table based on the given filter or all records from the table
SELECT – Fetch one, multiple, or all records from the table
ALTER – Modify the table structure such as adding a new column, renaming an existing column, adding a default value to a column, etc
DROP – Delete an entire table, materialized view, or in fact the complete database itself
GRANT – Give privileges to the users
REVOKE – Take back granted privileges from the users

Let us move ahead and understand some of the practical SQL queries which you can practice on any relational database of your choice. I am going to use PostgreSQL on Docker for this tutorial as my preferred choice.

1.3. Setting up PostgreSQL on Docker

Usually, set up the database is a tedious step but with the technological advancements, this process has become simple with the help of Docker. Readers can watch the video available at this link to understand the Docker installation on Windows OS. Open the terminal and trigger the following commands to get the PostgreSQL up and running on the local machine.

-- command to run postgres on docker
docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres

-- command to stop the Postgres docker container
docker stop postgres

-- command to remove the Postgres docker container
docker rm postgres

Remember to enter the password of your choice. If everything goes well the PostgreSQL database server would be up and running on a port number – 5433.

what is sql - postgre on docker — Fig. 1: PostgreSQL running on Docker

1.4 Executing basic SQL commands

You can use the following SQL commands for practice purposes. The commands extensively cover the SQL basics.

SQL Script

-- create database command --
create database if not exists test;

-- create table command --
create table student (
	id serial primary key,
	first_name VARCHAR(50) not null,
	last_name VARCHAR(50) null,
	email VARCHAR(50) not null,
	gender VARCHAR(50) 
);

-- changing a column's default value --
alter table student alter column gender set default 'male';

-- inserting mock values in table --
insert into student (first_name, last_name, email, gender) values ('Ky', 'Treher', 'ktreher0@nba.com', 'Female');
insert into student (first_name, last_name, email, gender) values ('Margie', 'Netley', 'mnetley1@psu.edu', 'Male');
insert into student (first_name, last_name, email, gender) values ('Aile', 'Strettle', 'astrettle2@virginia.edu', 'Non-binary');
insert into student (first_name, last_name, email, gender) values ('Trevor', 'Tebbutt', 'ttebbutt3@unblog.fr', 'Female');
insert into student (first_name, last_name, email, gender) values ('Birgit', 'Skilling', 'bskilling4@webeden.co.uk', 'Non-binary');

-- get all values from the table --
select * from student;

-- filtering data where clause --
select * from student where gender = 'Female';

-- sorting data order by --
select * from student order by first_name desc;

-- count number of records --
select count(*) as total_students from student;

-- count number of filtered records --
select count(*) total_female_students from student where gender = 'Female';

-- update record(s) based on a condition --
update student set last_name = 'Doe' where gender = 'Female';

-- delete record(s) --
delete from student;
-- or 
delete from student where gender = 'Female';

-- drop table --
drop table student;

-- drop database --
drop database if not exists test;

1.5 How SQL works when joining tables?

A join is a SQL that combines the columns from one or more tables in a relational database based on a common field to create a dataset that can be further used. SQL provides 4 types of joins to prepare the common dataset from two or more tables based on a common field –

INNER JOIN – Represents the dataset that has the matching values in both tables. Represented by the query syntax – SELECT col1, col2, col3 FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name
LEFT JOIN – Represents the complete dataset from the left table (table1) and matching values from the right table (table2). Represented by the query syntax – SELECT col1, col2, col3 FROM table1 LEFT JOIN table2 ON table1.colum_name = table2.column_name
RIGHT JOIN – Represents the complete dataset from the right table (table2) and matching values from the left table (table1). Represented by the query syntax – SELECT col1, col2, col3 FROM table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name
FULL JOIN – Represents the complete dataset from both tables. If no matches are found in the table, it places NULL. Represented by the query syntax – SELECT col1, col2, col3 FROM table1 FULL JOIN table2 ON table1.column_name = table2.column_name
SELF JOIN – Represents creating a table by joining itself as if there were two tables. Represented by the query syntax – SELECT col1, col2, col3 FROM table1 t1, table2 t2 WHERE condition. The t1 and t2 represents the different aliases for the same table

2. Summary

In this tutorial, we learned the basics of SQL, JOINS, and some basic queries implementation. You can download the sql scripts from the Downloads section.

3. Download the Scripts

This was a tutorial on learning SQL basics and understand the basic queries implementation.

Download
You can download the full source code of this example here: What is SQL and how does it work?

Last updated on Jan. 10th, 2022

What is SQL and how does it work?