Task 1:

Publications Search

Construct a query to retrieve the pub_year, title, venue, and citation_count from the publications table. Your results should include only those entries where the title and abstract contain the terms "neural" and "network". The output must be ordered by citation_count in descending order.

Use MariaDB 11.8 syntax to write your answer. Descriptions of tables are provided in the right pane.

Write your request in the field below and click the "Check it!" button.

Get hint

Copy code Clear editor

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.

ER diagram of the University database

The list of tables

semesters - academic semester table.

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

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

PRIMARY KEY, btree (semester_id)

rooms - campus classrooms and labs.

room_idunique record identifier (PK)
buildingbuilding name
room_numberroom number or label
capacitymaximum number of seats (SMALLINT)
typeroom type: Lecture, Lab, Seminar, or Conference (ENUM)
has_projectorwhether the room has a projector (BOOLEAN)

room_id	building	room_number	capacity	type	has_projector
1	Science Hall	101	120	Lecture	1

PRIMARY KEY, btree (room_id)

scholarships - available scholarships.

scholarship_idunique record identifier (PK)
namescholarship name
amountaward amount (DECIMAL)
typescholarship type: Merit, Need-Based, Athletic, or Research (ENUM)
eligibilityeligibility criteria as JSON — e.g. {"min_gpa": 3.5, "need_based": true}

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

PRIMARY KEY, btree (scholarship_id)

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

department_idunique record identifier (PK)
namedepartment name
codeshort department code
parent_idparent department identifier — self-referencing FK (nullable)
levelhierarchy level: 1 = Faculty, 2 = Department, 3 = Sub-department (TINYINT)

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

PRIMARY KEY, btree (department_id)

FOREIGN KEY (parent_id) REFERENCES departments(department_id)

faculty - university faculty members.

faculty_idunique record identifier (PK)
first_namefaculty member's first name
last_namefaculty member's last name
emailinstitutional email address
department_iddepartment identifier (FK)
titleacademic rank: Professor, Associate Professor, Assistant Professor, Lecturer, or Instructor (ENUM)
hire_datedate of hire
office_hoursweekly office hours as JSON array — e.g. [{"day":"Mon","start":"10:00","end":"12:00"}]
biobiographical text (TEXT)

faculty_id	first_name	last_name	email	department_id	title	hire_date	office_hours	bio
1	Alice	Carter	a.carter@university.edu	3	Professor	2010-08-15	[{"day":"Mon","start":"10:00","end":"12:00"},{"day":"Wed","start":"14:00","end":"16:00"}]	Expert in distributed systems and cloud computing.

PRIMARY KEY, btree (faculty_id)

FOREIGN KEY (department_id) REFERENCES departments(department_id)

students - enrolled students.

student_idunique record identifier (PK)
student_numberunique student ID number (CHAR)
first_namestudent's first name
last_namestudent's last name
emailstudent's email address
date_of_birthstudent's date of birth
enrollment_datedate the student was first enrolled
statusenrollment status: Active, Inactive, Graduated, or Suspended (ENUM)
gpacurrent cumulative GPA (DECIMAL)
contactsemergency contact and address as JSON

student_id	student_number	first_name	last_name	email	date_of_birth	enrollment_date	status	gpa	contacts
1	S2021001	James	Miller	j.miller@student.edu	2002-04-23	2021-09-01	Active	3.72	[{"emergency":{"name":"Susan Miller","phone":"+15551234567"}}]

PRIMARY KEY, btree (student_id)

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

course_idunique record identifier (PK)
codecourse code (e.g. CS301)
titlecourse title
descriptiondetailed course description (TEXT, FULLTEXT index)
creditsnumber of credit hours (TINYINT)
department_iddepartment identifier (FK)
is_activewhether the course is currently offered (BOOLEAN)
embedding1536-dimension semantic embedding for vector similarity search (VECTOR(1536))

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

PRIMARY KEY, btree (course_id)
FULLTEXT (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 prerequisite course (FK)

course_id	prerequisite_id
5	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 - course sections (a specific offering of a course in a semester).

section_idunique record identifier (PK)
course_idcourse identifier (FK)
semester_idsemester identifier (FK)
faculty_idinstructor identifier (FK)
room_idassigned room identifier (FK)
capacitymaximum enrollment (SMALLINT)
enrolled_countcurrent number of enrolled students (SMALLINT)
statussection status: Open, Closed, or Cancelled (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	capacity	enrolled_count	status	schedule
1	1	1	1	1	30	28	Open	[{"day":"Mon","start":"09:00","end":"10:30"},{"day":"Wed","start":"09:00","end":"10:30"}]

PRIMARY KEY, btree (section_id)

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)
student_idstudent identifier (FK)
section_idsection identifier (FK)
enrolled_atdate and time of enrollment (TIMESTAMP)
statusenrollment status: Enrolled, Dropped, Completed, or Withdrawn (ENUM)
gradefinal letter grade (CHAR)
final_scorecomputed numeric score (DECIMAL)

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

PRIMARY KEY, btree (enrollment_id)

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

student_scholarships - scholarships awarded to students.

student_idstudent identifier (FK)
scholarship_idscholarship identifier (FK)
awarded_datedate the scholarship was awarded
amountactual awarded amount, may differ from the base scholarship amount (DECIMAL)

student_id	scholarship_id	awarded_date	amount
1	1	2024-09-01	5000.00

PRIMARY KEY, btree (student_id, scholarship_id)

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

research_projects - department research projects.

project_idunique record identifier (PK)
titleproject title
descriptionproject description (TEXT)
department_iddepartment identifier (FK)
statusproject status: Proposed, Active, Completed, or Cancelled (ENUM)
start_dateproject start date
end_dateproject end date (nullable)
fundingfunding sources as JSON — e.g. [{"source":"NSF","amount":150000}]

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

PRIMARY KEY, btree (project_id)

FOREIGN KEY (department_id) REFERENCES departments(department_id)

publications - research publications with full-text search support.

publication_idunique record identifier (PK)
titlepublication title
abstractpublication abstract (MEDIUMTEXT, FULLTEXT index)
journaljournal or conference name
publication_yearyear of publication (YEAR)
doiDigital Object Identifier
contribution_typetype of contribution — one or more of: Theoretical, Experimental, Review, Applied (SET)
project_idassociated research project identifier (FK, nullable)

publication_id	title	abstract	journal	publication_year	doi	contribution_type	project_id
1	Deep Learning for Molecular Screening	We present a transformer-based architecture for high-throughput virtual screening...	Nature Machine Intelligence	2024	10.1038/s42256-024-00001-1	Theoretical,Experimental	1

PRIMARY KEY, btree (publication_id)
FULLTEXT (abstract)

FOREIGN KEY (project_id) REFERENCES research_projects(project_id)

project_members - faculty and student participation in research projects.

project_idresearch project identifier (FK)
faculty_idfaculty member identifier (FK, nullable)
student_idstudent identifier (FK, nullable)
rolemember role: PI, Co-PI, Researcher, or Assistant (ENUM)
join_datedate the member joined the project

project_id	faculty_id	student_id	role	join_date
1	1	[null]	PI	2023-01-15

PRIMARY KEY, btree (project_id, faculty_id, student_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 graded items per enrollment (~120 000 rows).

event_idunique record identifier (BIGINT PK)
enrollment_idenrollment identifier (FK)
item_namename of the graded item (e.g. 'Midterm Exam')
item_typeitem type: Homework, Quiz, Midterm, Final, Project, or Lab (ENUM)
scorescore received (DECIMAL)
max_scoremaximum possible score (DECIMAL)
weightpercentage weight toward final grade (DECIMAL)
grader_idfaculty member who graded the item (FK)
created_atdate and time the grade was recorded (DATETIME)

event_id	enrollment_id	item_name	item_type	score	max_score	weight	grader_id	created_at
1	1	Midterm Exam	Midterm	87.00	100.00	30.00	1	2024-10-18 14:22:00

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 change history (~60 000 rows).

log_idunique record identifier (BIGINT PK)
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)
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	old_values	new_values
1	enrollments	1	UPDATE	2024-12-21 09:05:33	[{"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	S2021001	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	S2021001	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 scholarships awarded
total_amounttotal scholarship funding received

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

v_publication_stats - paper count per department per year.

department_iddepartment identifier
department_namedepartment name
publication_yearyear of publication
paper_countnumber of papers published
journal_countnumber of distinct journals

department_id	department_name	publication_year	paper_count	journal_count
3	Computer Science	2024	12	8

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

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

Learning SQL: Generate, Manipulate, and Retrieve Data by Alan Beaulieu

As data floods into your company, you need to put it to work right away―and SQL is the best tool for the job. With the latest edition of this introductory guide, author Alan Beaulieu helps developers get up to speed with SQL fundamentals for writing database applications, performing administrative tasks, and generating reports. You’ll find new chapters on SQL and big data, analytic functions, and working with very large databases.

Practice

Lessons

Playground

Test

Publications Search

Login

Register

Publications Search