I have been using SQL for years, but I always used it as an excel table.
The real power lies in relational tables with ids that link.
This means having a shared id between tables.
Why do this? It reduces data repetition, and null values.
So for example assume I have a food diary.
I have
User_Table
id
name
Food_Table
id
food
Union_Table
id
name_id
food_id
I might have users that sign up, but never enter a food. If I had the food id as a row in the user table, then it would have a null value.
I can join any names that have foods using the union table.
One other thing I learned is that each table should have the id as a primary key.
Then in the union table, the name_id, and food_id should be noted as foreign keys, as such:
CREATE TABLE Union_Table (
id INT AUTO_INCREMENT PRIMARY KEY,
name_id INT,
food_id INT,
FOREIGN KEY(name_id) REFERENCES User_Table(id),
FOREIGN KEY(food_id) REFERENCES Food_Table(id)
);
Defining a foreign key like this will link the keys for SQL. This will prevent a number being entered in the foreign key that is not also there for the primary key in the reference table.
You can also use functions like Cascade delete to delete all instances of the linked key.
Now, how would I query all these tables to make one result or table?
SELECT
name,
food
FROM Food_Table
INNER JOIN Union_Table
ON Food_Table.id = Union_Table.food_id
INNER JOIN User_Table
ON User_Table.id = Union_Table.name_id
ORDER BY name;
These are my notes so far. I will try come back and add more. I invite you to ask any questions.