Open in app

Sign in

Write

Sign in

SQL for beginners in 10 minutes

Sylvain Tiset
9 min readFeb 21, 2024

--

This article is designed for people who don’t know computer science and SQL. It will cover the main SQL types of queries. Of course, don’t expect to become an SQL expert with this article, it’s just an overview of what can SQL do.

SQL for beginners (Generated by Microsoft Bing AI)

Definitions and context

Relational databases

In this article we are only covering the relational databases, as it is an SQL subject. SQL means Structured Query Language. SQL is a programming language for storing and processing information in a relational database.

  • But what’s a database?

A database is an organized collection of structured information, or data, typically stored electronically in a computer system. A database is usually controlled by a database management system (DBMS). — Oracle

  • But what’s a relational database?

A relational database is a type of database that stores and provides access to data points that are related to one another. Relational databases are based on the relational model, an intuitive, straightforward way of representing data in tables. In a relational database, each row in the table is a record with a unique ID called the key. The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish the relationships among data points. — Oracle

Relational databases (RDBMS) have the following properties which are necessary to understand if wanted to understand SQL as well:

  • Atomicity : the entire transaction takes place at once or doesn’t happen at all
  • Consistency : the database must be consistent before and after the transaction
  • Isolation : several transactions occur independantly without interference
  • Durability : the changes of a successful transaction occurs even if a system failure occurs

All togather these properties are called ACID.

How a relational database is structured?

RDBMS is working with Tables. In DBMS, the table is known as relation and row as a tuple.

A Table has a name, and can store records. The table has columns that have datatypes, and a special column which is called primary key, which is and must be unique and not null (must have a value) for every row in the table.

Tables can also be linked together through special columns call foreign key. Here is a schema representing a database storing products with customers.

Schema of relational database by QuestDB

In this example we 3 tables:

  • Product
  • Customer
  • Transaction

The primary keys are respectively:

  • ProductId
  • CustomerId
  • TransactionId

An example of record for the Product table is (789, Apple, 5).

The transaction table has 2 foreign keys:

  • ProductId: refers to ProductId primary key from Product table
  • CustomerId: refers to CustomerId primary key from Customer table

SQL rules

  • Structure query language is case unsensitive.
  • Statements of SQL are dependent on text lines, it’s possible to use a single SQL querry on one or multiple text line.
  • SQL depends on tuple relational calculus and relational algebra.

SQL datatypes

Binary

  • Binary: it has a maximum length of 8000 bytes. It contains fixed-length binary data.
  • Varbinary: it has a maximum length of 8000 bytes. It contains variable-length binary data.
  • Image: it has a maximum length of 2,147,483,647 bytes. It contains variable-length binary data.

Numeric

  • Int: it is used to specify an integer value.
  • Smallint: it is used to specify small integer value.
  • Bit: it has the number of bits to store.
  • Decimal: it specifies a numeric value that can have a decimal number.
  • Numeric: it is used to specify a numeric value.
  • Float: it is used to specify a floating-point value e.g. 6.2, 2.9 etc. from -1.79E + 308 to 1.79E + 308
  • Real: it specifies a single precision floating point number from -3.40e + 38 to 3.40E + 38

String

  • Char: it has a maximum length of 8000 characters. It contains Fixed-length non-unicode characters.
  • Varchar: it has a maximum length of 8000 characters. It contains variable-length non-unicode characters.
  • Text: it has a maximum length of 2,147,483,647 characters. It contains variable-length non-unicode characters.

Date

  • Date: it is used to store the year, month, and days value.
  • Time: it is used to store the hour, minute, and second values.
  • Timestamp: it stores the year, month, day, hour, minute, and the second value.

Note that every DBMS also have their own datatypes.

Type of SQL queries

  • DDL : Data Definition Language
  • DML : Data Manipulation Language
  • DCL : Data Control Language
  • TCL : Transaction Control Language
  • DQL : Data Query Language
SQL types of queries

DDL

DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc. All DDL commands are auto-commited, meaning that the changes are permanently saved in the database.

Here are the most common commands that come under DDL:

  • CREATE: used to create a table in the database
  • DROP: used to delete the table structure and all the table records in the database
  • ALTER: used to alter the database structure, like modifying a characteristic of an attribute or creating a new attribute
  • TRUNCATE: used to delete all the rows from the table and free the space containing the table
CREATE TABLE table_name(column_name datatypes[,....]);

DROP TABLE table_name;

ALTER TABLE table_name ADD column_name column_definition;
ALTER TABLE table_name MODIFY(column_definitions....);

TRUNCATE TABLE table_name;

DML

DML commands are used to modify the database records. DML commands are not auto-commited, meaning that the changes can be rollback.

Here are the main DML commands:

  • INSERT: used to insert some records to a table
  • UPDATE: used to modify some records in a table
  • DELETE: used to delete some records in the table
INSERT INTO table_name
(col1, col2, col3,.... col N)  
VALUES (value1, value2, value3, .... valueN);

-- the where clause is in [] because it is optional
UPDATE table_name
SET [column_name1= value1,...column_nameN = valueN]
[WHERE CONDITION];

-- the where clause is in [] because it is optional
DELETE FROM table_name [WHERE condition];

DCL

DCL commands are used to grant and take back authority from any database user.

Here are the two commands:

  • GRANT: used to give user access privileges to a database
  • REVOKE: used to take back user access privileges to a database
-- Granting reading and updating permission for user1 and user3 on my_table
GRANT SELECT, UPDATE ON MY_TABLE TO USER1, USER3;

-- Revoking reading and updating permission for user1 and user2 on my_table
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

TCL

TCL commands are used to manage transactions cycles for DML queries.

Here are the main commands:

  • BEGIN: explicitely starts a transaction (can also be START transaction depending on the DBMS used)
  • COMMIT: used to save all the transactions to the database
  • ROLLBACK: used to undo transactions that have not already been saved to the database
  • SAVEPOINT: used to roll the transaction back to a certain point without rolling back the entire transaction
COMMIT;
ROLLBACK;
SAVEPOINT SAVEPOINT_NAME;

-- Examples
BEGIN
DELETE FROM CUSTOMERS  
WHERE AGE = 25;  
COMMIT;

BEGIN
DELETE FROM CUSTOMERS  
WHERE AGE = 25;  
ROLLBACK;

-- will insert values 1 and 3 but not 2
BEGIN;
INSERT INTO table1 VALUES (1);
SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (2);
ROLLBACK TO SAVEPOINT my_savepoint;
INSERT INTO table1 VALUES (3);
COMMIT;

DQL

DQL is used to fetch the data from the database.

It uses only one command:

  • SELECT: used to fetch data from some tables
-- basic select syntax
SELECT expressions    
FROM TABLES    
WHERE conditions;

The following picture is showing the execution order of SQL select statement. Let’s review each point in the next session.

SQL execution order by BytebyteGo

Relational algebra

If we want to go further than a basic select query, we should understand how we can fetch specific data. That’s where the relational algebra comes in.

Let’s work with this table as example base:

Example of Person table

The select attribute

First, the select attribute allows to retrieve specific columns of a table. Let’s look at the examples below:

-- will retrieve only 3 columns of all Person rows
select personId, name, height from Person;

-- * means every columns: will retrieve every columns of Person table
select * from Person;

The where clause

The where clause allows to retrieve specific rows on a table based on some conditions.

-- will retrieve rows 1, 2, and 5
select * from Person
where dateofbirth > '01/01/1990';

-- will retrieve row 5
select * from Person
where dateofbirth > '01/01/1990' and height > 180;

-- will retrieve row 1
-- like operator will check if the data is matching the pattern
-- % is a joker for like operator meaning every character repeated from 0 to infinite times
select * from Person
where dateofbirth > '01/01/1990' and email like 'john%';

-- will retrieve no rows
select * from Person where height <= 150 or height > 200;

-- will retrieve all 5 rows
select * from Person where height between 150 and 200;

The group by clause

Let’s say way now want to group people by a pattern, here the email that have the same extension, and calculate the average height of those people.

-- will count rows for each different name in the table
-- will retrieve the following result:
-- DOE, 3
-- EDISON, 1
-- MEAL, 1
select name, count(*)
from Person
group by name;

-- will retrieve the following result:
-- DOE, avg(180, 160, 190) = 176.66
-- EDISON, avg(168) = 168
-- MEAL, avg(175) = 175
select name, avg(height)
from Person
group by name;

-- Note that the group by attribute must be in the select statement
-- The other selected attributes must be an aggregated function

Having

The having clause must be after a group by clause. It permits to filter rows among the group by selection.

-- will retrieve only DOE, 3
select name, count(*)
from Person
group by name
having count(*) > 1;

Join

To understand joins commands, we have to introduce new tables to our example.

Person, Product and Purchase example tables

Join commands are used to retrieve data among several tables based on conditions.

There are 4 major types of join:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

In the following schema, we see what we retrieve (colored in blue) if we JOIN table A with table B.

Joins from a Medium article

Join operations are often based on condition with foreign keys. Let’s see now how we write it in SQL with our 3 tables examples.

-- will retrieve person 1,2 and 3 with their name
select purchaseId, productId, personId, name
from Person
inner join Purchase
on Person.PersonId = Purchase.PersonId;

-- will retrieve every 5 persons but with null value
-- for person 4 and 5 for purchaseId and productId columns
select purchaseId, productId, personId, name
from Person
left outer join Purchase
on Person.PersonId = Purchase.PersonId;

-- will retrieve person 1,2 and 3 with their name
select purchaseId, productId, personId, name
from Person
right outer join Purchase
on Person.PersonId = Purchase.PersonId;

-- will retrieve every rows from both table PRODUCT and PURCHASE
select purchaseId, productId, personId, price
from Product
full outer join Purchase
on Product.ProductId = Purchase.ProductId;

Set operators

Set operators are used to group 2 queries between them to make only one result. Here are the four main set operators:

  • UNION
  • UNION ALL
  • INTERSECT
  • EXCEPT

In the following schema we can see what the set operators do.

Set operators by CsharpCorner

Let’s see now with our table examples what it looks like in SQL:

-- Note that the selected columns must be the same
-- The difference between union and union all
-- is union all keeps all records (including duplicates)

-- will retrieve person 1, 3, 4 and 5
select personId from Person where name = 'Doe'
UNION
select personId from Person where height = '175';

-- will retrieve person 1, 3, 4, 5, 1 and 5
select personId from Person where name = 'Doe'
UNION ALL
select personId from Person where height >= '175';

-- will retrieve person 1 and 5
select personId from Person where name = 'Doe'
INTERSECT
select personId from Person where height >= '175';

-- will retrieve person 3
select personId from Person where name = 'Doe'
EXCEPT
select personId from Person where height >= '175';

Thanks for having read this article until the end. If you want to know more, you can read JavaTpoint tutorial as this article was inspired from it.

Sql
Database
Relational Databases

--

--

Sylvain Tiset

Written by Sylvain Tiset

12 Followers

Tech Lead developer

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams