Creating tables: data types and constraints

CREATE TABLE Students (
  StudentID   INTEGER PRIMARY KEY,
  AdmNo       TEXT    NOT NULL UNIQUE,
  Name        TEXT    NOT NULL,
  Form        INTEGER CHECK (Form BETWEEN 1 AND 4),
  Phone       TEXT,
  FeesBalance INTEGER DEFAULT 0,
  JoinedOn    TEXT    DEFAULT (DATE('now'))
);

INSERT INTO Students (AdmNo, Name, Form, Phone) VALUES ('ADM001', 'Amina Hassan', 2, '0712345678');
INSERT INTO Students (AdmNo, Name, Form) VALUES ('ADM002', 'Brian Otieno', 3);
SELECT * FROM Students;

INSERT INTO Students (AdmNo, Name, Form) VALUES ('ADM001', 'Copycat', 1);   -- UNIQUE fails
INSERT INTO Students (AdmNo, Form) VALUES ('ADM009', 2);                   -- NOT NULL fails
INSERT INTO Students (AdmNo, Name, Form) VALUES ('ADM010', 'Zed', 7);      -- CHECK fails

PRAGMA foreign_keys = ON;   -- SQLite needs this. MySQL (InnoDB) checks foreign keys automatically.

CREATE TABLE Students (StudentID INTEGER PRIMARY KEY, Name TEXT NOT NULL);
CREATE TABLE Payments (
  PaymentID  INTEGER PRIMARY KEY,
  StudentID  INTEGER NOT NULL REFERENCES Students(StudentID) ON DELETE CASCADE,
  Amount     INTEGER NOT NULL CHECK (Amount > 0),
  MpesaCode  TEXT UNIQUE,
  PaidOn     TEXT NOT NULL
);

INSERT INTO Students (Name) VALUES ('Amina'), ('Brian');
INSERT INTO Payments (StudentID, Amount, MpesaCode, PaidOn) VALUES
  (1, 15000, 'SJK1A2B3C4', '2026-09-01'),
  (1, 5000,  'SJK5D6E7F8', '2026-09-15'),
  (2, 20000, 'SJL9G8H7I6', '2026-09-02');

SELECT s.Name, SUM(p.Amount) AS Paid, COUNT(*) AS Payments
FROM Students s JOIN Payments p ON p.StudentID = s.StudentID
GROUP BY s.Name;

CREATE TABLE Suppliers (SupplierID INTEGER PRIMARY KEY, Name TEXT NOT NULL);
ALTER TABLE Suppliers ADD COLUMN Phone TEXT;
ALTER TABLE Suppliers RENAME TO Vendors;
INSERT INTO Vendors (Name, Phone) VALUES ('Bidco', '0700111222');
SELECT * FROM Vendors;
DROP TABLE Vendors;              -- deletes the table and ALL its data. Careful!

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  adm_no VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  form TINYINT CHECK (form BETWEEN 1 AND 4),
  fees_balance DECIMAL(10,2) DEFAULT 0,
  joined_on DATE DEFAULT (CURRENT_DATE)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Classes (
  ClassID   INTEGER PRIMARY KEY,
  Name      TEXT NOT NULL UNIQUE              -- e.g. 'Form 2 East'
);
CREATE TABLE Students (
  StudentID    INTEGER PRIMARY KEY,
  AdmissionNo  TEXT NOT NULL UNIQUE,
  FullName     TEXT NOT NULL,
  DateOfBirth  TEXT,
  ClassID      INTEGER NOT NULL REFERENCES Classes(ClassID),
  ParentPhone  TEXT CHECK (length(ParentPhone) = 10)
);
CREATE TABLE Subjects (
  SubjectID INTEGER PRIMARY KEY,
  Name      TEXT NOT NULL UNIQUE
);
CREATE TABLE Marks (
  StudentID INTEGER NOT NULL REFERENCES Students(StudentID),
  SubjectID INTEGER NOT NULL REFERENCES Subjects(SubjectID),
  Term      TEXT NOT NULL,
  Score     INTEGER NOT NULL CHECK (Score BETWEEN 0 AND 100),
  PRIMARY KEY (StudentID, SubjectID, Term)     -- one mark per student, subject and term
);

INSERT INTO Classes (Name) VALUES ('Form 2 East'), ('Form 2 West');
INSERT INTO Subjects (Name) VALUES ('Maths'), ('English');
INSERT INTO Students (AdmissionNo, FullName, ClassID, ParentPhone) VALUES
  ('ADM001', 'Baraka Mutua', 1, '0712000101'),
  ('ADM002', 'Neema Chebet', 2, '0712000102');
INSERT INTO Marks VALUES (1, 1, '2026T3', 78), (1, 2, '2026T3', 65), (2, 1, '2026T3', 91);

SELECT s.FullName, c.Name AS Class, sub.Name AS Subject, m.Score
FROM Marks m
JOIN Students s ON s.StudentID = m.StudentID
JOIN Classes c ON c.ClassID = s.ClassID
JOIN Subjects sub ON sub.SubjectID = m.SubjectID
ORDER BY s.FullName, sub.Name;

CREATE TABLE Accounts (
  AccountID INTEGER PRIMARY KEY,
  Phone     TEXT NOT NULL UNIQUE,
  Balance   INTEGER NOT NULL DEFAULT 0 CHECK (Balance >= 0)
);
INSERT INTO Accounts (Phone) VALUES ('0712000001');
INSERT INTO Accounts (Phone, Balance) VALUES ('0712000002', 500);
SELECT * FROM Accounts;
-- Each line below would be rejected. Remove the -- from one line at a time and run:
-- INSERT INTO Accounts (Phone) VALUES ('0712000001')                (UNIQUE fails)
-- INSERT INTO Accounts (Phone, Balance) VALUES ('0712000003', -50)  (CHECK fails)
-- INSERT INTO Accounts (Balance) VALUES (100)                       (NOT NULL fails)

CREATE TABLE Orders (
  OrderID    INTEGER PRIMARY KEY,
  CustomerID INTEGER NOT NULL REFERENCES Customers(CustomerID) ON DELETE RESTRICT,
  ...
);
CREATE TABLE OrderItems (
  OrderID   INTEGER NOT NULL REFERENCES Orders(OrderID) ON DELETE CASCADE,
  ...
);

CREATE TABLE Suppliers (SupplierID INTEGER PRIMARY KEY, Name TEXT NOT NULL);
ALTER TABLE Suppliers ADD COLUMN Phone TEXT;
ALTER TABLE Suppliers ADD COLUMN Active INTEGER NOT NULL DEFAULT 1;
ALTER TABLE Suppliers RENAME COLUMN Name TO CompanyName;
INSERT INTO Suppliers (CompanyName, Phone) VALUES ('Nairobi Electronics Ltd', '0722000000');
SELECT * FROM Suppliers;