University database (MariaDB): schema, tables and SQL exercises

University is a sample MariaDB database of a university: departments, faculty, students, courses, class sections, enrollments, grades and research. On SQLtest.online you can query it right in your browser: solve exercises with automatic checking and run your own queries in the playground, with nothing to install.

• 16 tables and 7 views
• 2,000 students
• 24,981 enrollments
• 5 SQL exercises

What is University

University is a modern sample database for MariaDB 11, designed as a richer alternative to the classic Sakila. It is normalized to third normal form and uses many MariaDB data types: JSON, ENUM and SET, FULLTEXT indexes and VECTOR columns for embeddings.

The data is large enough for real analytics: about 25 thousand enrollments, 300 thousand grade events and an audit log of more than half a million rows. At the same time the subject is familiar to anyone who has studied at a university.

ER diagram

The diagram shows the University tables and the foreign keys between them. Click it to open the full-size version.

What's inside

The tables fall into three groups.

The key thing to remember: students enroll in a section, a specific run of a course in a semester, not in the course itself. So the path from a student to a course is enrollments → sections → courses. Seven views, such as v_student_gpa and v_course_pass_rate, contain ready-made reports.

How much data the tables hold:

Table structure

Click a table to see its columns, a sample row and its keys.

Sample queries

These queries show how the data is connected. Copy any of them and run it in the playground.

A student, course and grade: from an enrollment through the section to the course and semester.

SELECT s.first_name, s.last_name, c.code, sem.name AS semester, e.final_grade
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN sections sec ON sec.section_id = e.section_id
JOIN courses c ON c.course_id = sec.course_id
JOIN semesters sem ON sem.semester_id = sec.semester_id
WHERE e.final_grade IS NOT NULL
ORDER BY e.enrollment_id
LIMIT 3;

Office hours from JSON: reading a value from a JSON column with JSON_VALUE.

SELECT first_name, last_name,
       JSON_VALUE(office_hours, '$[0].day') AS day,
       JSON_VALUE(office_hours, '$[0].start') AS starts_at
FROM faculty
ORDER BY faculty_id
LIMIT 3;

SQL exercises by topic

There are 5 exercises on the University database so far, and new ones are being added. Solutions are checked automatically on a real MariaDB server. The number on the right is how many exercises a topic has; the colored dots show its difficulty range.

• Calculations 1

Where to start

The first exercises on the University database:

1. Student Enrollment Age
2. Identify Non-Lab Buildings
3. Oldest Departments
4. Active NASA Funded Projects
5. Publications Query

All University exercises →