Summary SQL & Databases - Complete Study Guide

SQL & Databases - Complete Study
Guide
SELECT, JOINs, aggregation, subqueries, normalisation and transactions - with
runnable examples




Complete Study Guide | Key Concepts | Examples | Practice Questions

, 1. Relational Model, Keys and Schema Design

A relational database stores data in tables (relations). Each row is a record, each column an attribute. Keys
enforce identity and relationships.

Key Meaning

Primary key Uniquely identifies each row; never NULL

Foreign key References a primary key in another table

Composite key Two+ columns together form the primary key

Candidate key Any column set that could be a primary key



CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
enrolled DATE DEFAULT CURRENT_DATE
);


CREATE TABLE enrollments (
student_id INT,
course_id INT,
grade CHAR(2),
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id)
);



- NOT NULL, UNIQUE, CHECK and DEFAULT are column constraints.
- One-to-many is the most common relationship (one student, many enrollments).
- Many-to-many needs a junction table - like enrollments above.




Page 1