Задание 65:

Курсы и исследовательские проекты по кафедрам

MariaDB

Ректор сравнивает учебную и научную работу. Посчитайте курсы по department_id в таблице courses и проекты по department_id в таблице research_projects. У одних кафедр есть курсы, но нет проектов, у других есть проекты, но нет курсов: покажите и те, и другие, с 0 вместо отсутствующего числа.
Выведите столбцы department_id, courses и projects, отсортировав по department_id.

Ожидаемый результат Столбцов: 3 · Строк: 16

department_id	courses	projects

Примеры строк откроются после неудачных проверок: 0 из 3

Напишите запрос ниже и нажмите "Проверить!". Описания таблиц - в правой панели.

Учебник

Проверяя запрос, вы соглашаетесь, что он сохраняется без данных аккаунта и может войти в обезличенные наборы данных, в том числе для обучения ИИ. Подробнее

Здесь появится результат запроса. Ctrl+Enter - Выполнить запрос

База данных University: описание таблиц и структуры

University DB - это современная учебная база данных MariaDB 11.7+ для изучения SQL, разработанная как многофункциональная замена классической базы данных Sakila.

Она охватывает все значимые типы данных MariaDB, включая VECTOR(1536), JSON, SET и индексы FULLTEXT, полностью нормализована до 3НФ и содержит достаточно данных как для начальных упражнений, так и для сложных аналитических запросов.

База данных University содержит 16 основных таблиц, описывающих академическую структуру университета - кафедры, преподавателей, студентов, курсы, записи на курсы, научные проекты и многое другое.

Компактная ER-диаграмма базы данных University со связями между таблицами ER диаграмма базы данных University

Подробнее о базе University: схема, примеры запросов и все задачи →

Список таблиц

semesters - таблица учебных семестров.

semester_idуникальный идентификатор записи (ПК, TINYINT)
termтип периода: Fall, Spring или Summer (ENUM)
academic_yearучебный год (тип YEAR)
nameназвание семестра (например, 'Fall 2024')
start_dateпервый день семестра
end_dateпоследний день семестра
enroll_deadlineпоследний день записи студентов на курсы
is_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 - аудитории и лаборатории кампуса.

room_idуникальный идентификатор записи (ПК, SMALLINT)
buildingназвание корпуса
room_numberномер или обозначение аудитории
capacityмаксимальное количество мест (SMALLINT)
room_typeтип аудитории: lecture, seminar, lab, computer_lab или online (ENUM)
has_projectorналичие проектора в аудитории (BOOLEAN)
has_videoналичие оборудования для видеоконференций (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 - доступные стипендии.

scholarship_idуникальный идентификатор записи (ПК, SMALLINT)
nameназвание стипендии
amountразмер выплаты (DECIMAL)
frequencyпериодичность выплаты: one-time, annual или per-semester (ENUM)
eligibilityкритерии допуска в формате JSON - например, {"min_gpa": 3.5, "need_based": true}
is_activeпредоставляется ли стипендия в настоящее время (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 - трёхуровневая иерархия подразделений (Факультет → Кафедра → Подразделение).

department_idуникальный идентификатор записи (ПК, TINYINT)
parent_idидентификатор родительского подразделения - самоссылающийся ВК (допускает NULL)
codeкраткий код подразделения (CHAR)
nameназвание подразделения
levelуровень иерархии: 1 = Факультет, 2 = Кафедра, 3 = Подразделение (TINYINT)
head_faculty_idидентификатор заведующего кафедрой (ВК, допускает NULL)
establishedгод основания подразделения (YEAR, допускает NULL)

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 - преподаватели университета.

faculty_idуникальный идентификатор записи (ПК, SMALLINT)
department_idидентификатор кафедры (ВК)
first_nameимя преподавателя
last_nameфамилия преподавателя
emailинституциональный адрес электронной почты
phoneрабочий номер телефона (допускает NULL)
rankучёное звание: Instructor, Assistant Professor, Associate Professor, Professor или Emeritus (ENUM)
hire_dateдата приёма на работу
officeномер или местоположение кабинета (допускает NULL)
office_hoursеженедельные часы приёма в формате JSON-массива - например, [{"day":"Mon","start":"10:00","end":"12:00"}]
bioбиографический текст (TEXT, допускает NULL)
is_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 - зачисленные студенты.

student_idуникальный идентификатор записи (ПК, INT)
department_idидентификатор основной кафедры студента (ВК)
student_numberуникальный номер студенческого билета (CHAR, например, 'S000123')
first_nameимя студента
last_nameфамилия студента
emailадрес электронной почты студента
date_of_birthдата рождения студента
genderпол: M, F, NB, Other или Prefer not to say (ENUM, допускает NULL)
enrollment_dateдата первичного зачисления студента
expected_gradожидаемый год окончания обучения (YEAR, допускает NULL)
statusстатус зачисления: active, inactive, graduated, suspended или withdrawn (ENUM)
gpaтекущий накопленный средний балл 0.000–4.000, поддерживается триггером (DECIMAL, допускает NULL)
contactsконтакт для экстренной связи и адрес в формате JSON - например, {"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_idуникальный идентификатор записи (ПК, SMALLINT)
department_idидентификатор кафедры (ВК)
codeкод курса, например, 'CS101' (CHAR)
titleназвание курса
creditsколичество кредитных часов (TINYINT)
levelакадемический уровень: undergraduate, graduate или doctoral (ENUM)
descriptionподробное описание курса (TEXT, индекс FULLTEXT вместе с title)
is_activeпреподаётся ли курс в настоящее время (BOOLEAN)
embedding1536-мерное семантическое эмбеддинг-представление для векторного поиска по сходству (VECTOR(1536), допускает NULL)

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_idидентификатор курса (ВК)
prerequisite_idидентификатор курса-prerequisite (ВК)
is_mandatoryявляется ли предварительный курс обязательным или рекомендуемым (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 - секции курсов (конкретное проведение курса в рамках семестра).

section_idуникальный идентификатор записи (ПК, INT)
course_idидентификатор курса (ВК)
semester_idидентификатор семестра (ВК)
faculty_idидентификатор преподавателя (ВК)
room_idидентификатор назначенной аудитории (ВК, допускает NULL - для полностью онлайн-секций)
section_numberномер секции в рамках курса и семестра (TINYINT)
deliveryформат проведения: in-person, online или hybrid (ENUM)
max_capacityмаксимальное количество записавшихся студентов (SMALLINT)
statusстатус секции: open, closed, cancelled или completed (ENUM)
scheduleеженедельное расписание занятий в формате JSON - например, [{"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 - записи студентов на секции курсов.

enrollment_idуникальный идентификатор записи (ПК, INT)
student_idидентификатор студента (ВК)
section_idидентификатор секции (ВК)
enrolled_atдата и время зачисления (TIMESTAMP)
statusстатус зачисления: enrolled, dropped, completed, failed или incomplete (ENUM)
final_gradeитоговая буквенная оценка, например, 'A', 'B+' (CHAR, допускает NULL)
final_scoreитоговый числовой балл 0.00–100.00 (DECIMAL, допускает NULL)

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 - стипендии, назначенные студентам.

award_idуникальный идентификатор записи (ПК, INT)
student_idидентификатор студента (ВК)
scholarship_idидентификатор стипендии (ВК)
awarded_dateдата назначения стипендии
expires_dateдата истечения срока действия награды (допускает NULL)
amount_awardedфактически выплаченная сумма (DECIMAL)
notesдополнительные примечания к награде (TEXT, допускает NULL)

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 - научно-исследовательские проекты кафедр.

project_idуникальный идентификатор записи (ПК, SMALLINT)
department_idидентификатор кафедры (ВК)
lead_faculty_idглавный исследователь проекта (ВК)
titleназвание проекта
abstractаннотация проекта (TEXT, допускает NULL)
start_dateдата начала проекта
end_dateдата окончания проекта (допускает NULL)
statusстатус проекта: proposed, active, completed или cancelled (ENUM)
fundingисточники финансирования в формате JSON - например, [{"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 - научные публикации с поддержкой полнотекстового поиска.

publication_idуникальный идентификатор записи (ПК, INT)
project_idидентификатор связанного научного проекта (ВК, допускает NULL)
titleназвание публикации
abstractаннотация публикации (MEDIUMTEXT, индекс FULLTEXT вместе с title)
pub_yearгод публикации (YEAR)
venueназвание журнала или конференции (допускает NULL)
doiцифровой идентификатор объекта (DOI, допускает NULL)
keywordsключевые теги - одно или несколько значений: AI, ML, Data Science, Networking, Security, Algorithms, Databases, HCI, Theory, Bioinformatics, Systems, Mathematics, Physics, Chemistry, Biology (SET)
citation_countколичество полученных цитирований (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 - участие преподавателей и студентов в научных проектах.

member_idуникальный идентификатор записи (ПК, INT)
project_idидентификатор научного проекта (ВК)
faculty_idидентификатор преподавателя (ВК, допускает NULL)
student_idидентификатор студента (ВК, допускает NULL)
roleроль участника: Principal Investigator, Co-Investigator, Research Assistant, Graduate Student или Undergraduate Student (ENUM)
joined_dateдата вступления участника в проект
left_dateдата выхода участника из проекта (допускает NULL)

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 - отдельные оцениваемые элементы по каждому зачислению (~120 000 строк).

event_idуникальный идентификатор записи (ПК, BIGINT)
enrollment_idидентификатор записи о зачислении (ВК)
item_nameназвание оцениваемого элемента (например, 'Assignment 1', 'Midterm Exam')
item_typeтип элемента: assignment, quiz, midterm, final, project, participation или lab (ENUM)
scoreполученный балл (DECIMAL)
max_scoreмаксимально возможный балл, по умолчанию 100.00 (DECIMAL)
weightдоля итоговой оценки, например, 0.1500 означает 15% (DECIMAL)
graded_atдата и время фиксации оценки (DATETIME)
grader_idпреподаватель, выставивший оценку (ВК, допускает NULL)
feedbackтекст обратной связи от проверяющего (TEXT, допускает NULL)

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	Хороший анализ, повторите раздел 3.

PRIMARY KEY, btree (event_id)

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

audit_log - история изменений, генерируемая триггерами (~60 000 строк).

log_idуникальный идентификатор записи (ПК, BIGINT)
table_nameназвание изменённой таблицы
record_idпервичный ключ изменённой записи (BIGINT)
actionтип изменения: INSERT, UPDATE или DELETE (ENUM)
changed_atдата и время изменения (TIMESTAMP)
changed_byпользователь базы данных или контекст приложения (допускает NULL)
old_valuesпредыдущие значения столбцов в формате JSON (null для INSERT)
new_valuesновые значения столбцов в формате JSON (null для 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)

Представления

v_student_gpa - взвешенный средний балл студента по семестрам.

student_idидентификатор студента
student_numberуникальный номер студенческого билета
first_nameимя студента
last_nameфамилия студента
semester_idидентификатор семестра
semester_nameназвание семестра
semester_gpaвзвешенный средний балл за семестр
credits_earnedкредитные часы, заработанные в семестре

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 - список зачисленных студентов с контактными данными по секции.

section_idидентификатор секции
course_codeкод курса
course_titleназвание курса
semester_nameназвание семестра
student_idидентификатор студента
student_numberуникальный номер студенческого билета
first_nameимя студента
last_nameфамилия студента
emailадрес электронной почты студента
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 - исторический процент успеваемости и средний балл по курсам.

course_idидентификатор курса
codeкод курса
titleназвание курса
semester_idидентификатор семестра
semester_nameназвание семестра
total_enrolledобщее количество записавшихся студентов
passedколичество студентов, успешно сдавших курс
pass_rateпроцент успеваемости
avg_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 - количество проведённых секций и процент заполненности по преподавателям за семестр.

faculty_idидентификатор преподавателя
first_nameимя преподавателя
last_nameфамилия преподавателя
semester_idидентификатор семестра
semester_nameназвание семестра
sections_taughtколичество проведённых секций
total_capacityсуммарная вместимость по всем секциям
total_enrolledобщее количество записавшихся студентов
fill_rateпроцент заполненности

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 - студенты, ранжированные по общей сумме полученных стипендий.

rank_positionместо в рейтинге по суммарной сумме стипендий
student_idидентификатор студента
student_numberуникальный номер студенческого билета
first_nameимя студента
last_nameфамилия студента
total_scholarshipsколичество назначенных стипендий
total_amountсуммарная сумма по полю amount_awarded по всем стипендиям

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 - количество публикаций и цитирований по кафедрам и годам.

department_idидентификатор кафедры
department_nameназвание кафедры
pub_yearгод публикации
paper_countколичество опубликованных статей
total_citationsсуммарное количество цитирований по всем статьям

department_id	department_name	pub_year	paper_count	total_citations
3	Computer Science	2024	12	87

v_prerequisite_tree - непосредственные предварительные требования для каждого курса.

course_idидентификатор курса
course_codeкод курса
course_titleназвание курса
prerequisite_idидентификатор курса-prerequisite
prerequisite_codeкод курса-prerequisite
prerequisite_titleназвание курса-prerequisite
is_mandatoryявляется ли предварительный курс обязательным или рекомендуемым

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