As a beginner working with data in relational databases, there are some key concepts to understand before you get going with writing SQL queries. These are the things I found most useful when getting started querying databases.

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: Relationship between one husband and one wife
  • many-to-one: Relationship between many students and one school
  • one-to-many: Relationship between one customer and many bank accounts
  • many-to-many: Relationship between many students and many teachers

Primary Key v Foreign Key

To make the most of the structure of a database, and to avoid storing expensive spreadsheets, we need to abide by rules to keep data clean, organised and in a logical structure.

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. Using this we can join the tables together where there is a match.

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

Add new data with INSERT

Remove data with DELETE

Modify data with UPDATE


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

 

Photo by Medhat Ayad from Pexels

This post first appeared on dev.to