SQL

What is SQL?

SQL or structured query language is a declarative language used for relational databases.
It is important to note that everything that is done in SQL could be done in other languages.
SQL has been designed to manipulate data more easily.
One SQL line can replace many lines of python.
Here also, we will not spend time on the syntax but rather on some interesting concepts that we covered in the course.

CRUD

The main actions that we make on a database are :
C: Create / INSERT
R: Read / SELECT
U: Update / ALTER
D: Delete / DROP

Primary key, foreign key and join tables

A Primary Key is a unique identifier for a row in a table. It ensures that each record in the table can be uniquely identified.
A Foreign Key is a field in one table that refers to the Primary Key of another table. It establishes a relationship between the two tables.
Let's consider two tables: Company and Employee.

Here the Company ID serves as the Primary Key, ensuring each company has a unique identifier.

Company ID (PK)	Company Name	Product	Location
1	Tech Corp	Software	USA
2	InnovateX	AI Tools	Germany

And here the Company ID is a Foreign Key, linking each employee to a company from the Company Table.

Employee Name	Employee Position	Company ID (FK)
Alice	Developer	1
Bob	Manager	2

In a many-to-many relationship, a single record in one table can be associated with multiple records in another table, and vice versa. Since relational databases do not support direct many-to-many relationships, we use a join table (also called a junction table) to bridge the two tables. Consider the following tables:

Student: Stores information about students (e.g., name, major, and ID as the primary key).
Courses: Stores information about courses (e.g., subject, duration, and ID as the primary key).

To track which students are enrolled in which courses, we create a join table that contains foreign keys referencing both tables.

      
      CREATE TABLE student_course (
          student_id INT,
          course_id INT,
          PRIMARY KEY (student_id, course_id),
          FOREIGN KEY (student_id) REFERENCES Student(id) ON DELETE CASCADE,
          FOREIGN KEY (course_id) REFERENCES Courses(id) ON DELETE CASCADE
      );

To retrieve the names of students who have taken a specific course (e.g., Computer Science), use the following query:

      
      SELECT Student.name 
      FROM Student 
      JOIN student_course ON Student.id = student_course.student_id 
      JOIN Courses ON Courses.id = student_course.course_id 
      WHERE Courses.subject = 'Computer Science';

Table Summary

Relationship	Example	Schema	Foreign key placement
One to One (1:1)	Person → Passport	passports.person_id UNIQUE	Foreign key in one table, set as unique.
One-to-Many (1:M)	Company → Employees	employees.company_id	Foreign key in the child table (employees).
Many-to-Many (M:M)	Students ↔ Courses	enrollments(student_id, course_id)	Join table with two foreign keys.

Indexes, Race conditions and SQL injections attacks

Indexes
Indexes create a B-tree structure (not binary tree structure) that allows us to access information faster.
They improve performance a lot when we want to read data from the database but might slow down the insertion and deletion ( as we saw with tree like structure that need to stay balanced). So they are great for read heavy sites.

Race conditions
Imagine a picture goes viral and everyone in the world wants to like it at the same time.
How can we make sure we can hanlde the database update in a way that data is not lost?
So there are many techniques to handle this problem but we will focus on one in particular which is transcations and pessimistic locking.
In our example, the transaction will group the reading and the updating of the likes in one operation and the locking will make sure that one transaction is executed at a time.
For more on the topic look ACID properties in database

SQL Injection Attacks
SQL Injection Attacks are malicious inputs that could give access to unauthorized information.
If your API is using f format and not placeholders, a user could enter as username : name'--
The ' would close the first string and the -- would ignore everything after this command.
So basically I user could access the account of someone by only entering the username and not the password.
So use the placeholder instead of the f string.
rows = db.execute (f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'") // Bad
rows = db.execute ("SELECT * FROM users WHERE username = ? AND password = ? ", username , password) // Better