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.
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.