Basic Database Design and What’s Next

Primary and foreign keys

A primary key uniquely identifies a row within its table (students.id) — no two rows can share the same primary key value, and most databases auto-generate it for you. A foreign key in one table points to a primary key in another (enrollments.student_id → students.id) — this is exactly what makes JOINs possible, and it’s what a database uses to enforce that you can’t create an enrollment for a student who doesn’t exist.

Creating a table

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  course TEXT,
  score INTEGER
);

NOT NULL means that column can never be left empty — attempting to insert a student with no name would be rejected by the database itself, rather than relying on your application code to catch the mistake.

Inserting and updating data

INSERT INTO students (name, course, score) VALUES ('Yash', 'SQL', 95);
UPDATE students SET score = 98 WHERE name = 'Yash';
DELETE FROM students WHERE score IS NULL;

Always include a WHERE clause with UPDATE and DELETE — omitting it applies the change to every single row in the table, which is one of the most common and most damaging mistakes made against a real production database. Many teams require running the equivalent SELECT with the same WHERE clause first, to confirm exactly which rows would be affected, before running the actual UPDATE or DELETE.

Normalization, briefly

Splitting data across related tables instead of one giant flat table is called normalization. It avoids storing the same information redundantly (a course’s title shouldn’t be copied into every single enrollment row) and keeps updates consistent — change a course’s title once, in one place, rather than hunting down every duplicate copy of it.

Onward to the advanced lessons

You now know how to query, filter, join, aggregate, and structure relational data. The next three lessons take you to a professional level: window functions, query performance, and the transactions that keep data correct even under heavy concurrent use.