SQL for Beginners: First Relational Database Project (Free)

Every successful relational database begins with understanding the problem it is intended to solve.

In this article, we examine the business requirements for the Careers platform before any database design or SQL development begins. You will learn what business requirements are, how they are commonly gathered within software projects, and how Gherkin scenarios help translate business requirements into relational database designs.

Attached to this lecture are the following downloadable resources:

Business Requirements – the complete customer requirements captured during the discovery phase for the Careers platform. These requirements form the foundation of the database design.

Gherkin Scenarios – the full behavioural specification for all eight tables in the Careers database. These scenarios reveal the relationships, constraints and business rules that are implemented throughout the remainder of the course.

These resources will be referenced throughout the remainder of the course as we translate business requirements into relational database designs before implementing and populating the completed Careers relational database.

Bridge the gap between business requirements and database implementation by exploring the Careers data model. This article explains what a data model is, how to read one, and the key design decisions that shape a relational database before any SQL is written.

Included with this lecture is the complete Careers data model, covering all eight tables across the Reference, Recruiter and Candidate schemas. The model includes business-friendly column definitions, data types, mandatory and optional fields, business rules, example values and a complete relationship summary.

Most importantly, you will see how business requirements and Gherkin scenarios evolve into a structured data model that provides the blueprint for the detailed table designs and SQL implementation that follow.

This article explains how the Careers data model is transformed into detailed table designs ready for implementation in SQL Server. You will learn how implementation decisions such as data types, primary keys, foreign keys, constraints and relationships are documented before any SQL is written, and why these design decisions are important when building a relational database.

Attached to this lecture are the following downloadable resources:

• Entity Relationship Diagram (ERD) – a visual overview of the complete Careers database, showing all eight tables, relationships, primary keys, foreign keys and constraints.

• Table Design Spreadsheets – one spreadsheet for each table in the Careers database, containing column definitions, SQL Server data types, lengths, nullable settings, default values, keys, constraints and implementation notes.

These resources represent the complete design specification for the Careers database and will be referenced throughout the implementation workshops as the database is built using SQL Server and T-SQL.

Throughout the implementation workshops, SQL Server system catalog views are used to verify that databases, schemas and tables have been created successfully. This article explains the recurring verification queries used throughout the course, the catalog views they reference, and how to interpret the results as you progress through the database implementation.

Begin the implementation phase by creating the Careers database that will be used throughout the remainder of the course. In this workshop, you will learn how the CREATE DATABASE statement establishes the foundation for the project, why meaningful database naming is important, the purpose of the GO batch separator, and why setting the correct database context with the USE statement is essential before creating any database objects.

By the end of this workshop, you will have created the Careers database, verified that it exists using SQL Server system catalog views, and configured it as the active database ready for the schema implementation that follows.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, experiment with the code, and revisit the implementation whenever you need.

Continue building the Careers database by creating the three schemas that organise the relational database: Reference, Recruiter, and Candidate. In this workshop, you will learn why schemas are used to group related database objects, how they improve organisation and maintainability, and why the schema name forms an important part of every database object's identity.

You will also learn why CREATE SCHEMA statements must be executed in separate batches, how the GO batch separator is used by SQL Server Management Studio and other compatible SQL development tools, and how to verify your work using SQL Server's built-in system catalog views.

By the end of this workshop, you will have created the complete schema structure for the Careers database, providing the foundation for implementing the relational database in the workshops that follow.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, experiment with the code, and revisit the implementation whenever you need.

Begin building the Reference schema by creating the Reference.JobType table, the first table in the Careers database. In this workshop, you will learn how to define a table using CREATE TABLE, implement an identity primary key, choose appropriate SQL Server data types, apply default values, and create named primary key and unique constraints.

You will also explore the purpose of lookup tables, why controlled classification data is separated from operational data, and how design decisions such as explicit column lengths, audit columns, and status flags contribute to a well-structured relational database.

By the end of this workshop, you will have created the Reference.JobType table and verified its successful creation using SQL Server's built-in system catalog views, establishing the design pattern that will be reused throughout the remainder of the project.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, experiment with the code, and revisit the implementation whenever you need.

Continue building the Reference schema by creating the Reference.JobSector lookup table. In this workshop, you will learn how lookup tables provide controlled classification data, why consistency in table design is important, and how the same design patterns can be reused across multiple tables within a relational database.

You will also reinforce the use of identity primary keys, Unicode text data types, audit columns, status flags, and named constraints while learning why these design decisions improve maintainability and data integrity. Finally, you will verify the successful creation of the table using SQL Server's built-in system catalog views and a simple JOIN query.

By the end of this workshop, you will have completed the lookup tables required to classify job adverts, establishing the foundation for the business tables that follow.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, experiment with the code, and revisit the implementation whenever you need.

Begin implementing the Recruiter schema by creating the Recruiter.Account table, which represents recruiting companies registered on the Careers platform. In this workshop, you will learn how to define a business entity using CREATE TABLE, implement identity primary keys, store company and login information, and apply database constraints that enforce data integrity.

You will also explore important design decisions, including why company addresses are stored as free text, why account status is represented using separate IsLocked and IsActive flags, how audit columns are implemented using DATETIME2 and SYSUTCDATETIME(), and why email uniqueness is enforced at the database level using a named UNIQUE constraint.

By the end of this workshop, you will have created the Recruiter.Account table, verified its successful creation using SQL Server's built-in system catalog views, and established the parent table that supports the recruiter side of the Careers platform.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, experiment with the code, and revisit the implementation whenever you need.

Continue building the Recruiter schema by creating the Recruiter.Profile table, which represents the individual recruiters who work for each recruiting company. In this workshop, you will create the first table in the project to use a foreign key, introducing the concept of referential integrity and demonstrating how relationships are enforced at the database level.

You will also explore important design decisions, including why telephone numbers are stored as text, how the IsAccountHolder flag differs from application-managed business logic, and how foreign key constraints ensure that every recruiter profile belongs to a valid recruiter account. Throughout the workshop, you'll continue applying the design patterns introduced earlier, including identity primary keys, audit columns, status flags, and named constraints.

By the end of this workshop, you will have created the Recruiter.Profile table, verified its successful creation using SQL Server's built-in system catalog views, and established the first parent-child relationship within the Careers database.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, experiment with the code, and revisit the implementation whenever you need.

Begin building the Candidate side of the Careers database by creating the Candidate.Account table, which represents job seekers registered on the platform. In this workshop, you will see how the same account design pattern used for recruiters can be adapted for a different business entity while keeping the schema simpler and focused on candidate login information.

You will also reinforce the use of identity primary keys, unique email constraints, status flags, audit columns and default values. The workshop explains why Candidate.Account and Recruiter.Account can share the same table name, how schemas prevent naming conflicts, and why uniqueness constraints apply independently within each table.

By the end of this workshop, you will have created the Candidate.Account table, confirmed its position within the Candidate schema, and established the parent table required for the Candidate.Profile relationship that follows.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, experiment with the code, and revisit the implementation whenever you need.

Continue building the Candidate schema by creating the Candidate.Profile table, which stores the personal and job-seeking details associated with each candidate account. In this workshop, you will introduce a new relational design pattern by applying both a foreign key and a unique constraint to the same column, enforcing a one-to-one relationship between Candidate.Account and Candidate.Profile.

You will also explore why some profile data is mandatory while other values are optional, why telephone numbers and CV links are stored as text, how availability is represented using a BIT flag, and why CandidateType remains a free-text value rather than referencing the existing JobType lookup table.

By the end of this workshop, you will have created the Candidate.Profile table, enforced the one-account-to-one-profile relationship at the database level, and completed the core Candidate schema ready for the job advert and application tables that follow.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, experiment with the code, and revisit the implementation whenever you need.

Create the Recruiter.JobAdvert table, the central table within the Careers database where recruiters publish job opportunities. In this workshop, you will bring together the lookup, recruiter and candidate foundations created in earlier workshops by implementing the most connected table in the project, introducing multiple foreign key relationships across different schemas.

You will also learn how to work with the DECIMAL data type for salary ranges, implement CHECK constraints to enforce business rules, create meaningful business reference numbers using a UNIQUE constraint, and understand why application status and record status are represented as separate business concepts. Throughout the workshop, you'll see how SQL Server enforces data integrity before invalid data can be written to the database.

By the end of this workshop, you will have created the Recruiter.JobAdvert table, implemented its relationships to recruiter profiles, job types and job sectors, and completed the core table that links together the major entities within the Careers database.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, experiment with the code, and revisit the implementation whenever you need.

Complete the Careers database by creating the Recruiter.JobApplication table, which connects candidates with job adverts and represents the final relationship within the project. In this workshop, you will implement the most dependent table in the database and bring together the recruiter and candidate sides of the schema through foreign key relationships.

You will also learn how to create a composite UNIQUE constraint, enforcing a business rule across multiple columns to ensure that a candidate can apply for the same job advert only once. In addition, you will explore optional data, business reference numbers, CHECK constraints, audit columns, and how SQL Server enforces both referential integrity and business rules directly within the database.

By the end of this workshop, you will have created the Recruiter.JobApplication table, completed the implementation of all eight tables within the Careers database, and established a fully relational database ready to be populated with data in the next section.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, experiment with the code, and revisit the implementation whenever you need.

Bring the Careers database to life by populating all eight tables with realistic, related data. In this workshop, you will see why data must be inserted in dependency order and how foreign key relationships determine which parent tables must be populated before their child tables.

You will also reinforce the use of INSERT statements, identity columns, default values, Unicode string literals and explicit foreign key values. The workshop demonstrates how reference data, recruiter accounts and profiles, candidate accounts and profiles, job adverts and job applications are inserted in a controlled sequence that preserves referential integrity.

By the end of this workshop, the Careers database will contain a complete set of connected sample data, including active and inactive records, optional salary values, closed adverts and valid job application relationships. The completed database will be ready for use throughout the final course in the SQL for Beginners series.

The complete SQL script demonstrated in this workshop is included as a downloadable resource, allowing you to follow along, execute the inserts yourself and revisit the completed dataset whenever needed.

Learn why the order in which data is inserted into a relational database is determined by foreign key relationships rather than personal preference. This article explains the concept of referential integrity, demonstrates why parent tables must always be populated before their child tables, and shows what happens when that rule is violated.

Included with this lecture is a SQL script containing two deliberate failure examples. By executing the script yourself, you will see how SQL Server rejects invalid INSERT statements, protects the database from inconsistent data, and leaves existing data unchanged when a constraint violation occurs.

Understanding population order is an essential relational database concept and will help you develop a stronger understanding of foreign keys, data integrity, and the dependency chain that exists within the Careers database.

Verify that the Careers database has been populated correctly by reviewing the data across all eight tables. This article demonstrates a series of practical SQL queries that confirm the database contains the expected records, including active and inactive data, optional salary information, and different advert states created during the population process.

Included with this lecture is the complete SQL script containing every query discussed in the article. Running these queries yourself will reinforce important SQL concepts such as COUNT, UNION ALL, WHERE, IS NULL, AND, and OR, while helping you become familiar with the dataset that will be used throughout the querying workshops that follow.

By completing this review, you will have confidence that your Careers database has been built and populated correctly, providing a solid foundation for the SQL query exercises in the next section.

Wondering where the SQL queries and data retrieval are? This article explains why database implementation and SQL querying have been separated into different courses, what you will learn in the final course of the free SQL for Beginners series, and how the Careers database you have built becomes the foundation for retrieving and querying relational data.

Test your understanding of the Careers database by applying the concepts covered throughout the design and implementation sections of this course. Start by reading the accompanying article, which explains each challenge, outlines the expected outcomes, and provides guidance before you begin the exercises.

The following resources are included with this lecture:

• Read this first: Database Structure Challenges (Article)

• Download: Database Structure Challenges (SQL Challenge Questions)

• Download: Database Structure Challenge Solutions (SQL Solutions)

Complete each challenge using your completed Careers database before opening the solutions. These exercises reinforce your understanding of schemas, foreign key relationships, dependency order, constraints, and relational database design, helping you consolidate everything you have learned throughout the project.

Test your understanding of data population by applying the concepts covered throughout the database implementation section of this course. Start by reading the accompanying article, which explains each challenge, outlines the expected outcomes, and provides guidance before you begin the exercises.

The following resources are included with this lecture:

• Read this first: Data Population Challenges (Article)

• Download: Data Population Challenges (SQL Challenge Questions)

• Download: Data Population Challenge Solutions (SQL Solutions)

Complete each challenge using your completed Careers database before opening the solutions. These exercises reinforce your understanding of INSERT statements, population order, foreign key relationships, constraint enforcement, debugging failed inserts, and verifying data using SELECT statements, helping you build confidence in populating relational databases correctly.

Take the final step in the Careers database project by exploring how a relational database evolves as new business requirements emerge. Start by reading the accompanying article, which introduces a series of open-ended design challenges that encourage you to think like a database developer rather than simply follow worked examples.

The following resources are included with this lecture:

• Read this first: Project Extension & Schema Evolution (Article)

• Download: Project Extension & Schema Evolution (SQL Challenge Questions)

• Download: Project Extension & Schema Evolution Solutions (SQL Solutions)

These exercises challenge you to extend the Careers database with a new feature, evaluate existing design decisions, develop your own SQL queries, and reflect on the trade-offs involved in relational database design. The final challenge also introduces several SQL concepts beyond those covered in the workshops, providing a glimpse of what comes next in your SQL learning journey.

There is no expectation that you already know every technique required -these optional extension exercises are designed to encourage experimentation, independent problem-solving, and continued learning.

Congratulations on completing both the Careers database project and the free SQL for Beginners series. In this final lecture, we review everything you have accomplished, reflect on the skills you have developed, and discuss how to continue building your SQL and relational database knowledge beyond this course.

Explore recommended next steps for continuing your SQL and T-SQL learning journey. Reflect on the foundations established throughout the SQL for Beginners series and discover how to continue developing your database design, development and querying skills through practice, projects and further study.

Learn more about the instructor, professional background and teaching philosophy behind the SQL for Beginners course series and future learning pathways.

Review the terms governing course materials, downloads and workshop content. This article outlines permitted usage and important information relating to course resources.