Task 35:

Courses and Research Projects per Department

MariaDB

The rector compares teaching and research. Count the courses per department_id in the courses table and the projects per department_id in the research_projects table. Some departments have courses but no projects, others have projects but no courses: show both kinds, with 0 for the missing count.
Output the columns department_id, courses and projects, sorted by department_id.

Expected result Columns: 3 · Rows: 16

department_id	courses	projects

Sample rows open after wrong checks: 0 of 3

Write your query below and click "Check it!". Tables are described in the right pane.

Tutorial

By checking a query, you agree that it is stored without your account details and may be included in anonymized datasets, including for AI training. Learn more

The query result will appear here. Ctrl+Enter - Run query

University Database: table structure and schema overview

University DB is a modern MariaDB 11.7+ sample database for learning SQL - designed as a feature-rich replacement for the classic Sakila database.

It covers all significant MariaDB datatypes including VECTOR(1536), JSON, SET, and FULLTEXT indexes, is fully normalized to 3NF, and ships with enough data for both beginner exercises and complex analytical queries.

The University database contains 16 main tables describing a university's academic structure - departments, faculty, students, courses, enrollments, research projects, and more.

Compact ER diagram of the University database showing table relationships ER diagram of the University database

More about the University database: schema, sample queries and all exercises →

The list of tables

semesters - academic semester table.

semester_idunique record identifier (PK, TINYINT)
termterm type: Fall, Spring, or Summer (ENUM)
academic_yearacademic year (YEAR)
namesemester name (e.g. 'Fall 2024')
start_datefirst day of the semester
end_datelast day of the semester
enroll_deadlinelast date for student enrollment
is_activewhether the semester is currently active (BOOLEAN)

semester_id	term	academic_year	name	start_date	end_date	enroll_deadline	is_active
1	Fall	2024	Fall 2024	2024-09-02	2024-12-20	2024-09-13	1

PRIMARY KEY, btree (semester_id)
UNIQUE KEY (term, academic_year)

rooms - campus classrooms and labs.

room_idunique record identifier (PK, SMALLINT)
buildingbuilding name
room_numberroom number or label
capacitymaximum number of seats (SMALLINT)
room_typeroom type: lecture, seminar, lab, computer_lab, or online (ENUM)
has_projectorwhether the room has a projector (BOOLEAN)
has_videowhether the room has video conferencing equipment (BOOLEAN)

room_id	building	room_number	capacity	room_type	has_projector	has_video
1	Science Hall	101	120	lecture	1	0

PRIMARY KEY, btree (room_id)
UNIQUE KEY (building, room_number)

scholarships - available scholarships and grants.

scholarship_idunique record identifier (PK, SMALLINT)
namescholarship name
amountaward amount (DECIMAL)
frequencyaward frequency: one-time, annual, or per-semester (ENUM)
eligibilityeligibility criteria as JSON - e.g. {"min_gpa": 3.5, "need_based": true}
is_activewhether the scholarship is currently offered (BOOLEAN)

scholarship_id	name	amount	frequency	eligibility	is_active
1	Dean's Excellence Award	5000.00	annual	{"min_gpa": 3.8, "need_based": false, "majors": ["CS","Math"]}	1

PRIMARY KEY, btree (scholarship_id)

departments - three-level department hierarchy (Faculty → Department → Sub-department).

department_idunique record identifier (PK, TINYINT)
parent_idparent department identifier - self-referencing FK (nullable)
codeshort department code (CHAR)
namedepartment name
levelhierarchy level: 1 = Faculty, 2 = Department, 3 = Sub-department (TINYINT)
head_faculty_ididentifier of the department head (FK, nullable)
establishedyear the department was established (YEAR, nullable)

department_id	parent_id	code	name	level	head_faculty_id	established
1	[null]	ENG	Faculty of Engineering	1	1	1965

PRIMARY KEY, btree (department_id)
UNIQUE KEY (code)

FOREIGN KEY (parent_id) REFERENCES departments(department_id)
FOREIGN KEY (head_faculty_id) REFERENCES faculty(faculty_id)

faculty - academic and administrative staff.

faculty_idunique record identifier (PK, SMALLINT)
department_iddepartment identifier (FK)
first_namefaculty member's first name
last_namefaculty member's last name
emailinstitutional email address
phoneoffice phone number (nullable)
rankacademic rank: Instructor, Assistant Professor, Associate Professor, Professor, or Emeritus (ENUM)
hire_datedate of hire
officeoffice room number or location (nullable)
office_hoursweekly office hours as JSON array - e.g. [{"day":"Mon","start":"10:00","end":"12:00"}]
biobiographical text (TEXT, nullable)
is_activewhether the faculty member is currently active (BOOLEAN)

faculty_id	department_id	first_name	last_name	email	phone	rank	hire_date	office	office_hours	bio	is_active
1	3	Alice	Carter	a.carter@university.edu	+15550100	Professor	2010-08-15	ENG-204	[{"day":"Mon","start":"10:00","end":"12:00"}]	Expert in distributed systems.	1

PRIMARY KEY, btree (faculty_id)
UNIQUE KEY (email)

FOREIGN KEY (department_id) REFERENCES departments(department_id)

students - registered students.

student_idunique record identifier (PK, INT)
department_idhome department identifier (FK)
student_numberunique student ID number (CHAR, e.g. 'S000123')
first_namestudent's first name
last_namestudent's last name
emailstudent's email address
date_of_birthstudent's date of birth
gendergender: M, F, NB, Other, or Prefer not to say (ENUM, nullable)
enrollment_datedate the student was first enrolled
expected_gradexpected graduation year (YEAR, nullable)
statusenrollment status: active, inactive, graduated, suspended, or withdrawn (ENUM)
gpacumulative GPA 0.000–4.000, maintained by trigger (DECIMAL, nullable)
contactsemergency contact and address as JSON - e.g. {"emergency":{"name":"Jane Doe","phone":"+1-555-0100"}}

student_id	department_id	student_number	first_name	last_name	email	date_of_birth	gender	enrollment_date	expected_grad	status	gpa	contacts
1	3	S000123	James	Miller	j.miller@student.edu	2002-04-23	M	2021-09-01	2025	active	3.720	{"emergency":{"name":"Susan Miller","phone":"+1-555-0100"}}

PRIMARY KEY, btree (student_id)
UNIQUE KEY (student_number)
UNIQUE KEY (email)

FOREIGN KEY (department_id) REFERENCES departments(department_id)

courses - course catalog with full-text and vector search support.

course_idunique record identifier (PK, SMALLINT)
department_idowning department identifier (FK)
codecourse code, e.g. 'CS101' (CHAR)
titlecourse title
creditsnumber of credit hours (TINYINT)
levelacademic level: undergraduate, graduate, or doctoral (ENUM)
descriptiondetailed course description (TEXT, FULLTEXT index with title)
is_activewhether the course is currently offered (BOOLEAN)
embedding1536-dimension semantic embedding for vector similarity search (VECTOR(1536), nullable)

course_id	department_id	code	title	credits	level	description	is_active	embedding
1	3	CS301	Database Systems	3	undergraduate	Introduction to relational databases, SQL, and data modeling.	1	[0.023, -0.011, ...]

PRIMARY KEY, btree (course_id)
UNIQUE KEY (code)
FULLTEXT (title, description)

FOREIGN KEY (department_id) REFERENCES departments(department_id)

course_prerequisites - course prerequisite relations (self-referencing many-to-many).

course_ididentifier of the course (FK)
prerequisite_ididentifier of the required prerequisite course (FK)
is_mandatorywhether the prerequisite is mandatory or recommended (BOOLEAN)

course_id	prerequisite_id	is_mandatory
5	1	1

PRIMARY KEY, btree (course_id, prerequisite_id)

FOREIGN KEY (course_id) REFERENCES courses(course_id)
FOREIGN KEY (prerequisite_id) REFERENCES courses(course_id)

sections - one offering of a course in a given semester.

section_idunique record identifier (PK, INT)
course_idcourse identifier (FK)
semester_idsemester identifier (FK)
faculty_idinstructor identifier (FK)
room_idassigned room identifier (FK, nullable - NULL for fully online)
section_numbersection number within the course/semester (TINYINT)
deliverydelivery mode: in-person, online, or hybrid (ENUM)
max_capacitymaximum enrollment (SMALLINT)
statussection status: open, closed, cancelled, or completed (ENUM)
scheduleweekly meeting times as JSON - e.g. [{"day":"Mon","start":"09:00","end":"10:30"}]

section_id	course_id	semester_id	faculty_id	room_id	section_number	delivery	max_capacity	status	schedule
1	1	1	1	1	1	in-person	30	open	[{"day":"Mon","start":"09:00","end":"10:30"},{"day":"Wed","start":"09:00","end":"10:30"}]

PRIMARY KEY, btree (section_id)
UNIQUE KEY (course_id, semester_id, section_number)

FOREIGN KEY (course_id) REFERENCES courses(course_id)
FOREIGN KEY (semester_id) REFERENCES semesters(semester_id)
FOREIGN KEY (faculty_id) REFERENCES faculty(faculty_id)
FOREIGN KEY (room_id) REFERENCES rooms(room_id)

enrollments - student enrollments in course sections.

enrollment_idunique record identifier (PK, INT)
student_idstudent identifier (FK)
section_idsection identifier (FK)
enrolled_atdate and time of enrollment (TIMESTAMP)
statusenrollment status: enrolled, dropped, completed, failed, or incomplete (ENUM)
final_gradefinal letter grade, e.g. 'A', 'B+' (CHAR, nullable)
final_scorefinal numeric score 0.00–100.00 (DECIMAL, nullable)

enrollment_id	student_id	section_id	enrolled_at	status	final_grade	final_score
1	1	1	2024-08-25 10:34:02	completed	A	93.50

PRIMARY KEY, btree (enrollment_id)
UNIQUE KEY (student_id, section_id)

FOREIGN KEY (student_id) REFERENCES students(student_id)
FOREIGN KEY (section_id) REFERENCES sections(section_id)

student_scholarships - scholarship awards granted to students.

award_idunique record identifier (PK, INT)
student_idstudent identifier (FK)
scholarship_idscholarship identifier (FK)
awarded_datedate the scholarship was awarded
expires_datedate the award expires (nullable)
amount_awardedactual awarded amount (DECIMAL)
notesadditional notes about the award (TEXT, nullable)

award_id	student_id	scholarship_id	awarded_date	expires_date	amount_awarded	notes
1	1	1	2024-09-01	2025-08-31	5000.00	[null]

PRIMARY KEY, btree (award_id)

FOREIGN KEY (student_id) REFERENCES students(student_id)
FOREIGN KEY (scholarship_id) REFERENCES scholarships(scholarship_id)

research_projects - faculty-led research initiatives.

project_idunique record identifier (PK, SMALLINT)
department_iddepartment identifier (FK)
lead_faculty_idprincipal investigator (FK)
titleproject title
abstractproject description (TEXT, nullable)
start_dateproject start date
end_dateproject end date (nullable)
statusproject status: proposed, active, completed, or cancelled (ENUM)
fundingfunding sources as JSON - e.g. [{"source":"NSF","amount":150000,"grant_id":"NSF-2024-001"}]

project_id	department_id	lead_faculty_id	title	abstract	start_date	end_date	status	funding
1	5	1	AI-Assisted Drug Discovery	Using machine learning to identify candidate molecules.	2023-01-15	[null]	active	[{"source":"NSF","amount":150000,"grant_id":"NSF-2023-042"}]

PRIMARY KEY, btree (project_id)

FOREIGN KEY (department_id) REFERENCES departments(department_id)
FOREIGN KEY (lead_faculty_id) REFERENCES faculty(faculty_id)

publications - research papers with full-text search support.

publication_idunique record identifier (PK, INT)
project_idassociated research project (FK, nullable)
titlepublication title
abstractpublication abstract (MEDIUMTEXT, FULLTEXT index with title)
pub_yearyear of publication (YEAR)
venuejournal name or conference (nullable)
doiDigital Object Identifier (nullable)
keywordskeyword tags - one or more of: AI, ML, Data Science, Networking, Security, Algorithms, Databases, HCI, Theory, Bioinformatics, Systems, Mathematics, Physics, Chemistry, Biology (SET)
citation_countnumber of citations received (INT)

publication_id	project_id	title	abstract	pub_year	venue	doi	keywords	citation_count
1	1	Deep Learning for Molecular Screening	We present a transformer-based architecture for virtual screening...	2024	Nature Machine Intelligence	10.1038/s42256-024-00001-1	AI,ML,Bioinformatics	12

PRIMARY KEY, btree (publication_id)
UNIQUE KEY (doi)
FULLTEXT (title, abstract)

FOREIGN KEY (project_id) REFERENCES research_projects(project_id)

project_members - faculty and student participation in research projects.

member_idunique record identifier (PK, INT)
project_idresearch project identifier (FK)
faculty_idfaculty member identifier (FK, nullable)
student_idstudent identifier (FK, nullable)
rolemember role: Principal Investigator, Co-Investigator, Research Assistant, Graduate Student, or Undergraduate Student (ENUM)
joined_datedate the member joined the project
left_datedate the member left the project (nullable)

member_id	project_id	faculty_id	student_id	role	joined_date	left_date
1	1	1	[null]	Principal Investigator	2023-01-15	[null]

PRIMARY KEY, btree (member_id)

FOREIGN KEY (project_id) REFERENCES research_projects(project_id)
FOREIGN KEY (faculty_id) REFERENCES faculty(faculty_id)
FOREIGN KEY (student_id) REFERENCES students(student_id)

grade_events - individual scored items per enrollment (~120 000 rows).

event_idunique record identifier (PK, BIGINT)
enrollment_idenrollment identifier (FK)
item_namename of the graded item, e.g. 'Assignment 1', 'Midterm Exam'
item_typeitem type: assignment, quiz, midterm, final, project, participation, or lab (ENUM)
scoreachieved points (DECIMAL)
max_scoremaximum possible points, default 100.00 (DECIMAL)
weightfraction of the final grade, e.g. 0.1500 for 15% (DECIMAL)
graded_atdate and time the grade was recorded (DATETIME)
grader_idfaculty member who graded the item (FK, nullable)
feedbackgrader's feedback text (TEXT, nullable)

event_id	enrollment_id	item_name	item_type	score	max_score	weight	graded_at	grader_id	feedback
1	1	Midterm Exam	midterm	87.00	100.00	0.3000	2024-10-18 14:22:00	1	Good analysis, review section 3.

PRIMARY KEY, btree (event_id)

FOREIGN KEY (enrollment_id) REFERENCES enrollments(enrollment_id)
FOREIGN KEY (grader_id) REFERENCES faculty(faculty_id)

audit_log - trigger-generated row-level change history (~60 000 rows).

log_idunique record identifier (PK, BIGINT)
table_namename of the modified table
record_idprimary key of the modified record (BIGINT)
actiontype of change: INSERT, UPDATE, or DELETE (ENUM)
changed_atdate and time of the change (TIMESTAMP)
changed_bydatabase user or application context (nullable)
old_valuesprevious column values as JSON (null for INSERT)
new_valuesnew column values as JSON (null for DELETE)

log_id	table_name	record_id	action	changed_at	changed_by	old_values	new_values
1	enrollments	1	UPDATE	2024-12-21 09:05:33	app_user	{"status":"enrolled","final_score":null}	{"status":"completed","final_score":93.50}

PRIMARY KEY, btree (log_id)

Views

v_student_gpa - weighted GPA per student per semester.

student_idstudent identifier
student_numberunique student ID number
first_namestudent's first name
last_namestudent's last name
semester_idsemester identifier
semester_namesemester name
semester_gpaweighted GPA for the semester
credits_earnedcredit hours earned in the semester

student_id	student_number	first_name	last_name	semester_id	semester_name	semester_gpa	credits_earned
1	S000123	James	Miller	1	Fall 2024	3.72	15

v_section_roster - enrolled students with contact info per section.

section_idsection identifier
course_codecourse code
course_titlecourse title
semester_namesemester name
student_idstudent identifier
student_numberunique student ID number
first_namestudent's first name
last_namestudent's last name
emailstudent's email address
statusenrollment status

section_id	course_code	course_title	semester_name	student_id	student_number	first_name	last_name	email	status
1	CS301	Database Systems	Fall 2024	1	S000123	James	Miller	j.miller@student.edu	enrolled

v_course_pass_rate - historical pass/fail percentage and average score per course.

course_idcourse identifier
codecourse code
titlecourse title
semester_idsemester identifier
semester_namesemester name
total_enrolledtotal number of students enrolled
passednumber of students who passed
pass_ratepass rate as a percentage
avg_scoreaverage final score

course_id	code	title	semester_id	semester_name	total_enrolled	passed	pass_rate	avg_score
1	CS301	Database Systems	1	Fall 2024	28	25	89.29	81.40

v_faculty_workload - sections taught and fill rate per faculty member per semester.

faculty_idfaculty member identifier
first_namefaculty member's first name
last_namefaculty member's last name
semester_idsemester identifier
semester_namesemester name
sections_taughtnumber of sections taught
total_capacitytotal seat capacity across all sections
total_enrolledtotal number of enrolled students
fill_ratefill rate as a percentage

faculty_id	first_name	last_name	semester_id	semester_name	sections_taught	total_capacity	total_enrolled	fill_rate
1	Alice	Carter	1	Fall 2024	3	90	82	91.11

v_top_scholars - students ranked by total scholarship funding received.

rank_positionrank by total scholarship amount
student_idstudent identifier
student_numberunique student ID number
first_namestudent's first name
last_namestudent's last name
total_scholarshipsnumber of scholarship awards
total_amounttotal amount_awarded across all scholarships

rank_position	student_id	student_number	first_name	last_name	total_scholarships	total_amount
1	1	S000123	James	Miller	2	8500.00

v_publication_stats - paper count and citations per department per year.

department_iddepartment identifier
department_namedepartment name
pub_yearyear of publication
paper_countnumber of papers published
total_citationstotal citation count across all papers

department_id	department_name	pub_year	paper_count	total_citations
3	Computer Science	2024	12	87

v_prerequisite_tree - direct prerequisites for every course.

course_idcourse identifier
course_codecourse code
course_titlecourse title
prerequisite_idprerequisite course identifier
prerequisite_codeprerequisite course code
prerequisite_titleprerequisite course title
is_mandatorywhether the prerequisite is mandatory or recommended

course_id	course_code	course_title	prerequisite_id	prerequisite_code	prerequisite_title	is_mandatory
5	CS401	Advanced Database Systems	1	CS301	Database Systems	1