SQL Study Guide: ER Modeling, SELECT, Constraints & Transactions

FoundationsER Modeling & Database Design

Before writing any SQL, most courses start with entity-relationship (E-R) modeling — the process of mapping real-world objects (entities) and their attributes into a database structure. An entity is a distinct object or concept, like a Patient or Appointment in a medical office database. An attribute is a property of that entity, like Patient ID or Date of Birth.

Here's the mental shift that makes this click: stop thinking of a database as "a spreadsheet" and start thinking of it as "a filing system for facts about distinct things." Every table is a category of thing (Patients, Doctors, Appointments); every column is a fact about that thing; every row is one specific instance of it.

💡 Why this matters later Every SQL mistake a beginner makes — duplicate data, confusing joins, slow queries — traces back to a database that wasn't modeled carefully at this stage. Get the entities right first, and the SQL gets dramatically easier.

Quiz: ER Modeling & Database Design →

QueryingSQL SELECT Basics

Every SQL query starts with SELECT and FROM — the only two mandatory clauses in a basic query. SELECT specifies which columns to return, and FROM specifies the table.

SELECT first_name, last_name
FROM patients
WHERE date_of_birth > '2000-01-01';
-- returns two columns, only for patients born after Jan 1, 2000

Using an asterisk (SELECT *) returns every column, while listing specific column names returns just those columns, in the order you list them. Read a query right-to-left in your head first — start from the table (FROM), then the filter (WHERE), then finally what you're choosing to display (SELECT) — it mirrors how the database itself actually processes it.

Try it yourself: what does this query return?

SELECT *
FROM students
WHERE gpa >= 3.5;

Answer: every column, for every student whose GPA is 3.5 or higher.

Quiz: SQL SELECT Basics →

StructureCreating Tables & Data Types

Tables are created with CREATE TABLE, and every column needs a name, a data type, and a width where required. Column names must be unique within a table.

CREATE TABLE students (
    student_id    NUMBER(6),
    first_name    VARCHAR2(20),
    last_name     VARCHAR2(20),
    gpa           NUMBER(3,2)
);

⚠️ Common mistake Picking the wrong data type early is one of the most expensive mistakes to fix later — imagine storing a phone number as a NUMBER and losing a leading zero, or trying to do math on a date stored as plain text. Choose types based on what the data actually is, not just what looks convenient today.

Quiz: Creating Tables & Data Types →

Data integrityConstraints

A constraint is a rule applied to data being added to a table — if new data violates the rule, it isn't added. Constraints can be defined when a table is created, or added later with ALTER TABLE.

ALTER TABLE students
ADD CONSTRAINT gpa_check CHECK (gpa >= 0 AND gpa <= 4.0);

Think of constraints as the database's own immune system — they reject bad data automatically, before it ever has a chance to corrupt a report or break an application further downstream. A database with no constraints will happily store a GPA of -400 or a birthdate in the year 3000.

Quiz: Constraints →

Data controlTransactions & Commits

A transaction groups one or more changes together so they either all succeed or all fail. A commit is issued implicitly in a few situations — for example, when a DDL command runs, or when you exit SQL Developer or SQL*Plus normally.

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- both updates succeed together, or neither does

💡 Why this matters Imagine transferring money between two bank accounts. If the first UPDATE succeeds but the system crashes before the second one runs, you've just deleted $100 from existence. Transactions exist specifically to prevent that — either both halves happen, or the whole thing rolls back like it never started.

Quiz: Transactions & Commits →

Advanced objectsSequences, Views & Indexes

A sequence generates a series of integers that can be stored in a database — commonly used for auto-incrementing primary keys. A view is a saved query that acts like a virtual table. An index speeds up how quickly records can be located.

CREATE SEQUENCE student_id_seq START WITH 1000 INCREMENT BY 1;
CREATE VIEW honor_roll AS
  SELECT first_name, last_name FROM students WHERE gpa >= 3.5;

A helpful analogy: a sequence is like a "take a number" dispenser at a deli counter, a view is a saved shortcut to a specific slice of your data, and an index is the equivalent of a book's index — instead of reading every page to find a topic, you jump straight to it.

Quiz: Sequences, Views & Indexes →

Cumulative reviewMidterm & Final Review

Once you've covered the individual topics above, the Midterm Review and Final Review quizzes mix questions across everything learned so far — good for simulating exam conditions before test day.

Quiz: Midterm Review → · Quiz: Final Review →