Java · Advanced level

Advanced level · Module 3: Databases with JDBC · Lesson 1 of 4

Relational Data and SQL for Java Developers

Design tables with primary and foreign keys and write the SQL queries an application needs.

In this lesson you will

  • Design tables with primary keys, foreign keys and constraints
  • Write SELECT queries with WHERE, JOIN and GROUP BY

Key concepts

primary keyforeign keyJOINGROUP BY

The lesson

A relational database stores data in tables of rows and columns. A primary key identifies each row; a foreign key links a row to another table, such as appointment.patient_id referring to patient.id. Constraints such as NOT NULL and UNIQUE make the database reject invalid data.

SELECT reads data, WHERE filters rows, ORDER BY sorts them, JOIN combines related tables and GROUP BY with COUNT or SUM summarises them. INSERT, UPDATE and DELETE change data.

Design the schema from the domain: one table per entity, foreign keys for relationships, and the right types for each column. Write and test queries in a database tool before putting them into Java code.

Step by step

  1. Create patient and appointment tables with keys and constraints.
  2. Insert sample rows.
  3. Write a JOIN listing appointments with patient names.
  4. Count appointments per day with GROUP BY.

Practice activity

Design the schema for the clinic capstone and write the five queries the service will need.

Expected result: A schema script that runs without errors and five tested queries with their expected results.

Check your understanding

  1. What does a foreign key do?

    Show answer

    It links a row to a row in another table and prevents references to rows that do not exist.

  2. Which clause summarises rows into groups?

    Show answer

    GROUP BY, usually with an aggregate such as COUNT or SUM.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.