When working with data in relational databases there are some key concepts to understand. These are some of the things I found useful when getting started querying databases.
The Relational Model
- Data is organised and 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
A primary key 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
- It cannot be unknown (NULL)
- There can only be one primary key per table
- A foreign key is a column that matches a primary key in another table so we can join the data in each together.
SQL is a standardised language for querying, manipulating and modifying relational databases.
SQL statements are used to transform the data into more segmented tables. The basics fall into four main groups.
select name, address, city, country /* the columns to return */ from customers.address /* the schema and table to retrieve data from */ where age = 21 and name = 'Bob Jones' /* filters for specific rows */
Add new data
insert into customers.address (name, address, city, country) /* where the data is to go */ values ('Bob Jones', '123 Main St', 'Auckland', 'New Zealand'); /* the data that is to go into the columns above */
delete from customers.address /* where the data is to be deleted from */ where name = 'Bob Jones' /* the condition that needs to be met */
update customers.address /* where the data is to be updated */ set country = 'New Zealand' /* the new value or update */ where city = 'Auckland'; /* the condition that needs to be met */
Let me know what other key concepts you think would be useful for complete beginners and junior data analysts.