Relational database fundamentals

As a beginner working in relational databases, there are some key concepts and fundamentals to understand. These are the things I found most useful when getting started querying databases. Before you get going with writing SQL queries make sure you have a good grasp of theses basics.

The Relational Model

Data is organised in rows and columns stored in tables.

Databases hold a collection of data stored in tables.

Relationship Categories in Databases

one-to-one: one husband and one wife

many-to-one: many students and one school

one-to-many: one customer and many bank accounts

many-to-many: many students and many teachers

Primary Key v Foreign Key

To make the most of the database we need to abide by rules to keep data clean, and organised. If we don’t we may as well be storing expensive spreadsheets,

To help do this we can use a Primary Key. This is a column that best identifies one unique row, and identifies each record as unique, like an ID.

It ensures that there are no duplicates.
There can only be one primary key per table.
It cannot be unknown (NULL).
A foreign key is a column that matches a primary key in another table.

SQL Statements

SQL is a standardised language for querying, manipulating and modifying relational databases. The basic SQL statements used to transform the data into more segmented tables fall into four main groups.

Retrieve data with SELECT

select name, address, city, country -- the columns to return 
from customers.address -- the schema and table to retrieve from 
where age = 21 and name = 'Bob Jones' -- filters for specific rows

select name, address, city, country -- the columns to return

from customers.address -- the schema and table to retrieve from

where age = 21 and name = 'Bob Jones' -- filters for specific rows

Add new data with INSERT

insert into
customers.address (name, address, city, country) -- where the data goes 
values ('Bob Jones', '123 Main St', 'Auckland', 'New Zealand');

insert into

customers.address (name, address, city, country) -- where the data goes

values ('Bob Jones', '123 Main St', 'Auckland', 'New Zealand');

Remove data with DELETE

delete from
customers.address --where the data is to be deleted from
where name = 'Bob Jones' -- the condition that needs to be met

delete from

customers.address --where the data is to be deleted from

where name = 'Bob Jones' -- the condition that needs to be met

Modify data with UPDATE

update
customers.address -- where the data is to be updated 
set country = 'New Zealand' -- the new value 
where city = 'Auckland'; -- the condition that needs to be met

update

customers.address -- where the data is to be updated

set country = 'New Zealand' -- the new value

where city = 'Auckland'; -- the condition that needs to be met

There are plenty of new concepts to get your head around with writing SQL and understanding the structure of a database. These are the relational database fundamentals I feel are important for complete beginners.

Photo by Jeffrey Czum from Pexels