Sample databases

BroadSQL comes with three small databases to try it on, each ready to open with CONNECT, without any setting. They are made up: every name, title and number in them is invented.

WORLD is presented in Getting started. CINEMA and LIBRARY show that the same commands work on two other database products, and each has a story of its own to query.

Opening a sample

CONNECT CINEMA;
SHOW TABLES;
DESCR SCREENING;

The three connections belong to the Environment LOCAL and to no Database Group. They use the database drivers included in BroadSQL; nothing needs to be installed. Their URLs are relative to the BroadSQL installation folder, where the launchers start BroadSQL.

ifexists=true makes HSQLDB refuse to start rather than create an empty database when the file is not found, and shutdown=true closes the database cleanly when BroadSQL disconnects.

The samples are yours to change: INSERT, UPDATE and DELETE work on them. To start again from the original data, copy the sample files from the BroadSQL download over yours, BroadSQL closed. The scripts that create them are in samples/sql/.

After an upgrade

A new installation has the three connections. When you upgrade by copying your own conf folder into the new installation (see Upgrading BroadSQL), your configuration stays exactly as it was: CINEMA and LIBRARY are not added to it, although their files are in the new samples folder. To add them, type ADD CONNECTION CINEMA; and ADD CONNECTION LIBRARY; (or use CONFIG) with the type, URL and user of the table above, and the Environment LOCAL.

CINEMA: The Starlight

The Starlight shows films on three screens, from 2 to 29 March 2026. Members pay less, according to their tier; a ticket bought by someone who is not a member has no member.

Queries on CINEMA

The programme of one day:

SELECT S.STARTS_AT, F.TITLE, SC.NAME AS SCREEN, S.BASE_PRICE
FROM SCREENING S JOIN FILM F ON F.FILM_ID = S.FILM_ID JOIN SCREEN SC ON SC.SCREEN_ID = S.SCREEN_ID
WHERE CAST(S.STARTS_AT AS DATE) = DATE '2026-03-14'
ORDER BY S.STARTS_AT;

Tickets and takings per film, for the films that sold at least 50 tickets:

SELECT F.TITLE, COUNT(*) AS TICKETS, SUM(T.PRICE_PAID) AS TAKINGS
FROM TICKET T JOIN SCREENING S ON S.SCREENING_ID = T.SCREENING_ID JOIN FILM F ON F.FILM_ID = S.FILM_ID
GROUP BY F.TITLE
HAVING COUNT(*) >= 50
ORDER BY TAKINGS DESC;

Tickets sold in each screen:

SELECT SC.NAME, SC.SEATS, COUNT(DISTINCT S.SCREENING_ID) AS SCREENINGS, COUNT(T.TICKET_ID) AS TICKETS
FROM SCREEN SC JOIN SCREENING S ON S.SCREEN_ID = SC.SCREEN_ID LEFT JOIN TICKET T ON T.SCREENING_ID = S.SCREENING_ID
GROUP BY SC.NAME, SC.SEATS
ORDER BY TICKETS DESC;

Members and other buyers, and what they paid on average:

SELECT CASE WHEN MEMBER_ID IS NULL THEN 'Not a member' ELSE 'Member' END AS BUYER, COUNT(*) AS TICKETS, AVG(PRICE_PAID) AS AVERAGE_PRICE
FROM TICKET
GROUP BY CASE WHEN MEMBER_ID IS NULL THEN 'Not a member' ELSE 'Member' END;

The five most faithful members:

SELECT M.FIRST_NAME, M.LAST_NAME, M.TIER, COUNT(*) AS TICKETS
FROM MEMBER M JOIN TICKET T ON T.MEMBER_ID = M.MEMBER_ID
GROUP BY M.FIRST_NAME, M.LAST_NAME, M.TIER
ORDER BY TICKETS DESC, M.LAST_NAME
LIMIT 5;

Screenings in 3D:

SELECT S.STARTS_AT, F.TITLE
FROM SCREENING S JOIN SCREEN SC ON SC.SCREEN_ID = S.SCREEN_ID JOIN FILM F ON F.FILM_ID = S.FILM_ID
WHERE SC.HAS_3D = TRUE AND S.STARTS_AT < TIMESTAMP '2026-03-04 00:00:00'
ORDER BY S.STARTS_AT;

LIBRARY: Willowbrook Public Library

The library lends books from three branches. A book has one or more copies, each in one branch; a loan is one copy lent to one member for 21 days. A loan not yet returned has no RETURNED_ON. The loans run from September 2025 to the end of March 2026: the examples count a loan as late when it is not returned and was due before 31 March 2026.

SQLite keeps dates as text (2026-03-31), which compares and sorts correctly in that form.

Queries on LIBRARY

Late loans, oldest first:

SELECT M.NAME AS MEMBER, B.TITLE, L.LOANED_ON, L.DUE_ON
FROM LOAN L JOIN MEMBER M ON M.MEMBER_ID = L.MEMBER_ID JOIN BOOK_COPY C ON C.COPY_ID = L.COPY_ID JOIN BOOK B ON B.BOOK_ID = C.BOOK_ID
WHERE L.RETURNED_ON IS NULL AND L.DUE_ON < '2026-03-31'
ORDER BY L.DUE_ON;

The ten most borrowed books:

SELECT B.TITLE, A.NAME AS AUTHOR, COUNT(*) AS LOANS
FROM LOAN L JOIN BOOK_COPY C ON C.COPY_ID = L.COPY_ID JOIN BOOK B ON B.BOOK_ID = C.BOOK_ID JOIN AUTHOR A ON A.AUTHOR_ID = B.AUTHOR_ID
GROUP BY B.TITLE, A.NAME
ORDER BY LOANS DESC, B.TITLE
LIMIT 10;

Loans per branch and month:

SELECT BR.NAME AS BRANCH, SUBSTR(L.LOANED_ON, 1, 7) AS MONTH, COUNT(*) AS LOANS
FROM LOAN L JOIN BOOK_COPY C ON C.COPY_ID = L.COPY_ID JOIN BRANCH BR ON BR.BRANCH_ID = C.BRANCH_ID
GROUP BY BR.NAME, SUBSTR(L.LOANED_ON, 1, 7)
ORDER BY BR.NAME, MONTH;

Members who never borrowed a book:

SELECT M.NAME, M.JOINED_ON
FROM MEMBER M LEFT JOIN LOAN L ON L.MEMBER_ID = M.MEMBER_ID
WHERE L.LOAN_ID IS NULL
ORDER BY M.NAME;

Genres with their number of books and average length:

SELECT G.NAME AS GENRE, COUNT(*) AS BOOKS, ROUND(AVG(B.PAGES)) AS AVERAGE_PAGES
FROM BOOK B JOIN GENRE G ON G.GENRE_ID = B.GENRE_ID
GROUP BY G.NAME
HAVING COUNT(*) >= 5
ORDER BY BOOKS DESC;

Authors per country:

SELECT COUNTRY_CODE, COUNT(*) AS AUTHORS
FROM AUTHOR
GROUP BY COUNTRY_CODE
ORDER BY AUTHORS DESC, COUNTRY_CODE;

Using two samples together

A list read on one connection can be used on another. The authors' country codes of LIBRARY are those of WORLD:

CONNECT LIBRARY;
LIST author_countries = SELECT DISTINCT COUNTRY_CODE FROM AUTHOR;
CONNECT WORLD;
SELECT CODE, NAME, CONTINENT FROM COUNTRY WHERE CODE IN ${author_countries} ORDER BY NAME;

The last statement runs on WORLD as WHERE CODE IN ('AUS', 'BRA', ...). See LIST.

Exporting a result works the same on every sample:

CONNECT CINEMA;
DUMP (SELECT F.TITLE, S.STARTS_AT FROM SCREENING S JOIN FILM F ON F.FILM_ID = S.FILM_ID ORDER BY S.STARTS_AT) TO starlight_programme AS XLSX;

Related

• Getting started, with the WORLD sample
• Connect to SQLite and other databases
• Exploring a database
• LIST

Found something wrong or unclear on this page? Tell us, with the page address.