Databases and persistence

The Relational Model

SQL Basics, DDL, and DML

Querying Data

Advanced Concepts

Sqlite

DBeaver

SQL Scripts

Connecting with Javascript

Appendix: Glossary of Database Terms

Resources

Practice

Assignment

• Teachers

Core program

What is a SQL Script?

A SQL script is a plain text file (usually ending in .sql) that contains one or more SQL statements. Instead of typing commands one by one in the terminal, you write them all in a file and run the whole thing in one go.

This is extremely useful for:

• Setting up a database schema (creating tables, indexes, constraints)
• Populating a database with sample or real data
• Sharing a database setup with other people — they just run your script

The books library database

Throughout this course we will use a sample database: a library of 100 books by 24 authors — ranging from J.K. Rowling and Tolkien to Agatha Christie, Toni Morrison, and Kazuo Ishiguro. Some authors have a single book, others have a whole series.

The database has two tables:

authors (id, first_name, last_name, nationality, birth_year)
books   (id, title, published_year, genre, author_id)

The script books_library.sql creates both tables, then inserts all the data. You can download it (and the ready-made .db file) from the course materials.

Method 1 — SQLite CLI

Step 1: Create an empty database

In SQLite, a database is just a file. You create one simply by opening it:

sqlite3 books_library.db

If the file does not exist yet, SQLite creates it automatically. You will see the sqlite> prompt. Type .quit to exit — the (empty) file now exists on disk.

Step 2: Load a SQL script

There are two ways to run a .sql file.

Option A — redirect the file on the command line (recommended)

Run this from your terminal (not from inside sqlite3):

sqlite3 books_library.db < books_library.sql

SQLite reads every statement in the file and executes them all. If the database file does not exist it is created first.

Option B — use .read inside the SQLite shell

If you are already inside the sqlite3 shell, use the .read dot command:

sqlite3 books_library.db

.read books_library.sql

Step 3: Verify it worked

Still inside the shell, run a quick check:

.tables
SELECT COUNT(*) FROM authors;
SELECT COUNT(*) FROM books;

You should see 24 authors and 100 books. To see a sample:

.headers on
.mode column
SELECT b.title, a.first_name || ' ' || a.last_name AS author
FROM books b
JOIN authors a ON a.id = b.author_id
LIMIT 10;

<aside> 💡

Add a YouTube video here demonstrating loading a SQL script with the SQLite CLI. This video he downloads a sql script from the internet, in keeping with our authors books theme you can get the sql script right here:

https://www.youtube.com/watch?v=V40VYF1hHlM

</aside>

Method 2 — DBeaver

DBeaver can run a SQL script just as easily. This is often more comfortable because you can see results in a table and spot errors more clearly.

Step 1: Create or open a database file

If you do not have a .db file yet, you need to create one first.

1. Open DBeaver.
2. Go to Database → New Database Connection (or click the plug icon).
3. Select SQLite and click Next.
4. In the Database field, type a path to a new file, e.g.:
   • Windows: C:\\Users\\yourname\\projects\\books_library.db
   • Mac: /Users/yourname/projects/books_library.db
5. Click Test Connection (download the driver if prompted), then Finish.

The empty database file is now created and connected.

Step 2: Open the SQL editor

1. In the Database Navigator panel on the left, right-click your new connection.
2. Choose SQL Editor → Open SQL Script.

A blank SQL editor tab will open.

Step 3: Load and run the script

Option A — Open the script file directly

1. In the SQL Editor toolbar, click the Open SQL Script folder icon (or use File → Open File).
2. Navigate to books_library.sql and open it.
3. All the SQL will appear in the editor.
4. Click the Execute Script button (the double play ▶▶ icon) — this runs all statements in the file.

Option B — Paste the script

1. Open books_library.sql in any text editor and copy all the content.
2. Paste it into the DBeaver SQL Editor.
3. Click Execute Script (▶▶).

Important: Use Execute Script (▶▶), not Execute Statement (▶). "Execute Statement" only runs the single statement under your cursor; "Execute Script" runs everything.

Step 4: Verify it worked

In the Database Navigator, expand your connection → Tables. You should see authors and books. Double-click either table to browse the data, or run a query:

SELECT b.title, b.published_year, a.first_name || ' ' || a.last_name AS author
FROM books b
JOIN authors a ON a.id = b.author_id
ORDER BY b.published_year DESC
LIMIT 10;

<aside> 💡

Add a YouTube video here demonstrating running a SQL script in DBeaver.

</aside>

Common gotchas

• Wrong directory: When using .read in the CLI, make sure the path to the .sql file is correct relative to where you launched sqlite3.
• File already has data: The books_library.sql script uses DROP TABLE IF EXISTS at the top, so it is safe to run more than once — it will reset the database cleanly.
• Execute Script vs Execute Statement: In DBeaver, always use the ▶▶ button (Execute Script) for multi-statement files. The single ▶ button only runs one statement.
• Locked file: If DBeaver is connected to the .db file, the CLI may refuse to open it at the same time (and vice versa). Close one before using the other.

The HackYourFuture curriculum is licensed under CC BY-NC-SA 4.0 *https://hackyourfuture.net/*

Built with ❤️ by the HackYourFuture community · Thank you, contributors

Found a mistake or have a suggestion? Let us know in the feedback form.