codecore

day15.md

#SQL

One to Many

one student <<<<<<<<<<<<<<<< many projects

One to One

one student <<<<<<<<<<<<<<<< single profile

Many to Many

Student <<<<<<<<<<<<<<<<<<<< Courses

Association

Foreign Key reference to the original data

Joins

INNER JOIN and LEFT JOIN

SELECT * FROM students INNER JOIN projects ON projects.student_id = students.id ORDER BY students.id;

MULTI TABEL JOIN

SELECT * FROM students INNER JOIN enrolments ON students.id = enrolments.studnet_id INNER JOIN courses ON courses.id = enrolments.course_id WHERE courses LIMIT 100;

AS

SELECT st.id, st.first_name, st.last_name FROM students AS st INNER JOIN enrolments ON st.id = enrolments.student_id INNER JOIN courses ON courses.id = enrolments.course_id LIMIT 100;

SELECT courses.id, courses.title, AVG(score) FROM courses INNER JOIN enrolments ON courses.id = enrolments.course_id GROUP BY courses.id LIMIT 100;

Constraints

NOT NULL

CREATE TABLE users(email VARCHAR(255) NOT NULL)

UNIQUE

syntax may vary

PRIMARY KEY

UNIQUE and NOT NULL is a default

FOREIGN KEY

if you try to add non existing record it will through an error

1.manually delete all the related data

2.using cascade to automatically delete all the related data

Database Normalization

go throught your database that can be calculated by an query or redundant fields

Trade offs

Database Indexing

linear search O^n sequential search binary search O^log(n)