database

Vocabulary:

• The visual representation of a database is called a schema
• A database is composed of different tables: each model will have it's own table. We can have a table name "cities" for example
• Each table has a set of columns: they represent the attributes of a table. For example the table "cities" can have the columns "name", "size", "population" etc...
• When inserting data in a table you create a new record, which is actually just a new row on the table

It really works like an Excel file...

Unique ID: every record on a table of a database will have a unique ID. The first record created will have the ID 1, the second one 2 etc... And ID 1 will ALWAYS be assigned to this first record. IDs are unique and can never be assigned to another record. Even if you delete the first record, ID 1 will disapear but never be re-assigned to a new record. This unique ID is also called primary key. Databases (like PostgreSQL) will always create and manage primary keys automatically for you.

1 to Many relation (1:n): for example a "City" / "Inhabitant" relation is a 1 to many relation:

• "1 city can have many inhabitants"
• "City" is the parent and "Inhabitant" is the children
• It is always the children that carries the relation. We say that "inhabitants belongs to their city"
• Inhabitants will carry the unique ID of their parent to know what city they belongs to
• We say that inhabitants carry the foreign key (of their city)

May to Many relation (n:n): for example a "Doctor" / "Patient" relation is a many to many relation:

• "1 doctor can have many patients, and 1 patient, can have many doctors
• In that case we need the create a join table between doctors table and patients table, which unique purpose will be to carry the relations between doctors and patients
• This join table will have a doctor_id column and a patient_id column
• To materialize a relation between doctor with id 10 and patient with ID 543, we will create a new record in the join table with doctor_id = 10 and patient_id = 543

SQL = Structured Query Language. It is the language of databases.

Give me all patient first names and last names

SELECT first_name, last_name FROM patients

Give me all patient attributes (will select the whole table)

SELECT * FROM patients

Give me all patients of age 21

SELECT * FROM patients WHERE age = 21

Give me all surgery doctors

SELECT * FROM doctors WHERE specialty LIKE '%surgery'

• '%surgery' means the string can start with anything but needs to end with "surgery". (ex: "specialist in surgery")
• 'surgery%' means the string needs to start with "surgery" but can end with anything. (ex: "surgery specialist")
• '%surgery%' means the string needs to contain "surgery". (ex: "This doctor is a surgery specialist")

Give me all cardiac surgery doctors named Steve

SELECT * FROM doctors
WHERE specialty = 'Cardiac Surgery'
AND first_name = 'Steve'

Give me all cardiologists or dentists

DELETE * FROM doctors
WHERE specialty = 'Cardiologist'
OR specialty = 'Dentist'

Give me all patients ordered by age

SELECT * FROM patients ORDER BY age ASC

SELECT * FROM patients ORDER BY age DESC

How many doctors do I have?

SELECT COUNT(*) FROM doctors

Count all doctors per specialty

SELECT COUNT(*), specialty
FROM doctors
GROUP BY specialty

Join: give me all the inhabitants from Paris

SELECT * FROM inhabitants
JOIN cities ON cities.id = inhabitants.city_id
WHERE cities.name = 'Paris'

To query data from more than one table I need to create a join between my tables and specify the nature of this join. Here I'm saying line by line:

• get all attributes from Inhabitants table
• join Inhabitants table and Cities table, selecting inhabitants whose city_id is equal to the id of the cities I will select
• only select cities with the name Paris

Once you joined 2 tables, in all the following SQL queries you will need to specify what table you are working on. For example we could add to the previous query: AND inhabitants.age >= 18

When you have a n:n relation, you need to join both tables to the join table

CRUD with SQL:

First we will need to create a table:

CREATE TABLE `doctors` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT,
  `name` TEXT,
  `age` INTEGER,
  `specialty` TEXT
);

CREATE

INSERT INTO doctors (name, age, specialty)
VALUES ('Dr. Crazy', 45, 'Dentist')

READ

SELECT * FROM doctors

UPDATE

UPDATE doctors SET age = 40, name = 'Dr. Waaay too Crazy' WHERE id = 3

DELETE

DELETE FROM doctors WHERE id = 32