任务 25:

各院系的课程数和科研项目数

MariaDB

校长要对比教学与科研。请按 department_id 统计 courses 表中的课程数和 research_projects 表中的项目数。有的院系有课程但没有项目，有的院系有项目但没有课程：两种都要显示，缺少的数量显示为 0。
输出列 department_id、courses 和 projects，按 department_id 排序。

预期结果列数: 3 · 行数: 16

department_id	courses	projects

错误检查 0 / 3 次后可查看示例行

在下方编写查询并点击 "检查它！"。表的描述在右侧窗格中。

教程

检查查询即表示你同意该查询在不含账户信息的情况下被保存，并可能被纳入匿名数据集，包括用于 AI 训练。了解详情

查询结果将显示在这里。 Ctrl+Enter - 运行查询

大学数据库：表结构和模式概述

大学数据库是一个现代的 MariaDB 11.7+ 示例数据库，用于学习 SQL - 设计为经典 Sakila 数据库的功能丰富的替代品。

它涵盖了所有重要的 MariaDB 数据类型，包括 VECTOR(1536)、JSON、SET 和 FULLTEXT 索引，完全标准化到 3NF，并提供足够的数据供初学者练习和复杂的分析查询。

大学数据库包含 16 个主要表，描述大学的学术结构 - 系、教职员工、学生、课程、注册、研究项目等。

显示大学数据库表关系的紧凑 ER 图大学数据库的 ER 图

了解更多 University 数据库：模式、示例查询和全部练习 →

表列表

semesters - 学术学期表。

semester_id唯一记录标识符 (PK, TINYINT)
term学期类型：秋季、春季或夏季 (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	秋季	2024	2024 秋季	2024-09-02	2024-12-20	2024-09-13	1

主键，btree (semester_id)
唯一键 (term, academic_year)

rooms - 校园教室和实验室。

room_id唯一记录标识符 (PK, SMALLINT)
building建筑名称
room_number房间号码或标签
capacity最大座位数 (SMALLINT)
room_type房间类型：讲座、研讨会、实验室、计算机实验室或在线 (ENUM)
has_projector房间是否有投影仪 (BOOLEAN)
has_video房间是否有视频会议设备 (BOOLEAN)

room_id	building	room_number	capacity	room_type	has_projector	has_video
1	科学大楼	101	120	讲座	1	0

主键，btree (room_id)
唯一键 (building, room_number)

scholarships - 可用奖学金和助学金。

scholarship_id唯一记录标识符 (PK, SMALLINT)
name奖学金名称
amount奖励金额 (DECIMAL)
frequency奖励频率：一次性、年度或每学期 (ENUM)
eligibility资格标准作为 JSON - 例如 {"min_gpa": 3.5, "need_based": true}
is_active奖学金是否当前提供 (BOOLEAN)

scholarship_id	name	amount	frequency	eligibility	is_active
1	院长优秀奖	5000.00	年度	{"min_gpa": 3.8, "need_based": false, "majors": ["CS","Math"]}	1

主键，btree (scholarship_id)

departments - 三层部门层级 (学院 → 部门 → 子部门)。

department_id唯一记录标识符 (PK, TINYINT)
parent_id父部门标识符 - 自引用外键 (可为空)
code短部门代码 (CHAR)
name部门名称
level层级：1 = 学院，2 = 部门，3 = 子部门 (TINYINT)
head_faculty_id部门负责人的标识符 (外键，可为空)
established部门成立年份 (YEAR，可为空)

department_id	parent_id	code	name	level	head_faculty_id	established
1	[null]	ENG	工程学院	1	1	1965

主键，btree (department_id)
唯一键 (code)

外键 (parent_id) 参考 departments(department_id)
外键 (head_faculty_id) 参考 faculty(faculty_id)

faculty - 学术和行政人员。

faculty_id唯一记录标识符 (PK, SMALLINT)
department_id部门标识符 (外键)
first_name教职员工的名字
last_name教职员工的姓氏
email机构电子邮件地址
phone办公室电话号码 (可为空)
rank学术职称：讲师、助理教授、副教授、教授或名誉教授 (ENUM)
hire_date入职日期
office办公室房间号码或位置 (可为空)
office_hours每周办公时间作为 JSON 数组 - 例如 [{"day":"Mon","start":"10:00","end":"12:00"}]
bio个人简介文本 (TEXT, 可为空)
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	教授	2010-08-15	ENG-204	[{"day":"Mon","start":"10:00","end":"12:00"}]	分布式系统专家。	1

主键，btree (faculty_id)
唯一键 (email)

外键 (department_id) 参考 departments(department_id)

students - 注册学生。

student_id唯一记录标识符 (PK, INT)
department_id所属院系标识符 (FK)
student_number唯一学号 (CHAR，例如 'S000123')
first_name学生的名字
last_name学生的姓氏
email学生的电子邮件地址
date_of_birth学生的出生日期
gender性别：M、F、NB、Other 或 Prefer not to say (ENUM，可为空)
enrollment_date学生首次入学日期
expected_grad预计毕业年份 (YEAR，可为空)
status学籍状态：active、inactive、graduated、suspended 或 withdrawn (ENUM)
gpa累计 GPA 0.000–4.000，由触发器维护 (DECIMAL，可为空)
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"}}

主键，btree (student_id)
唯一键 (student_number)
唯一键 (email)

外键 (department_id) 参考 departments(department_id)

courses - 支持全文和向量搜索的课程目录。

course_id唯一记录标识符 (PK, SMALLINT)
department_id开课院系标识符 (FK)
code课程代码，例如 'CS101' (CHAR)
title课程名称
credits学分数 (TINYINT)
level学术层次：undergraduate、graduate 或 doctoral (ENUM)
description详细课程描述 (TEXT，与 title 共同建立 FULLTEXT 索引)
is_active课程当前是否开设 (BOOLEAN)
embedding用于向量相似度搜索的 1536 维语义嵌入 (VECTOR(1536)，可为空)

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, ...]

主键，btree (course_id)
唯一键 (code)
FULLTEXT (title, description)

外键 (department_id) 参考 departments(department_id)

course_prerequisites - 课程先修关系（自引用多对多）。

course_id课程标识符 (FK)
prerequisite_id先修课程标识符 (FK)
is_mandatory先修课程是必修还是推荐 (BOOLEAN)

course_id	prerequisite_id	is_mandatory
5	1	1

主键，btree (course_id, prerequisite_id)

外键 (course_id) 参考 courses(course_id)
外键 (prerequisite_id) 参考 courses(course_id)

sections - 某学期开设的一个课程班。

section_id唯一记录标识符 (PK, INT)
course_id课程标识符 (FK)
semester_id学期标识符 (FK)
faculty_id授课教师标识符 (FK)
room_id分配的教室标识符 (FK，可为空-完全在线时为 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"}]

主键，btree (section_id)
唯一键 (course_id, semester_id, section_number)

外键 (course_id) 参考 courses(course_id)
外键 (semester_id) 参考 semesters(semester_id)
外键 (faculty_id) 参考 faculty(faculty_id)
外键 (room_id) 参考 rooms(room_id)

enrollments - 学生选课记录。

enrollment_id唯一记录标识符 (PK, INT)
student_id学生标识符 (FK)
section_id课程班标识符 (FK)
enrolled_at选课日期和时间 (TIMESTAMP)
status选课状态：enrolled、dropped、completed、failed 或 incomplete (ENUM)
final_grade最终字母成绩，例如 'A'、'B+' (CHAR，可为空)
final_score最终分数 0.00–100.00 (DECIMAL，可为空)

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

主键，btree (enrollment_id)
唯一键 (student_id, section_id)

外键 (student_id) 参考 students(student_id)
外键 (section_id) 参考 sections(section_id)

student_scholarships - 授予学生的奖学金。

award_id唯一记录标识符 (PK, INT)
student_id学生标识符 (FK)
scholarship_id奖学金标识符 (FK)
awarded_date奖学金授予日期
expires_date奖学金到期日期（可为空）
amount_awarded实际授予金额 (DECIMAL)
notes关于奖学金的附加说明 (TEXT，可为空)

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]

主键，btree (award_id)

外键 (student_id) 参考 students(student_id)
外键 (scholarship_id) 参考 scholarships(scholarship_id)

research_projects - 教师主导的科研项目。

project_id唯一记录标识符 (PK, SMALLINT)
department_id院系标识符 (FK)
lead_faculty_id项目负责人 (FK)
title项目名称
abstract项目描述 (TEXT，可为空)
start_date项目开始日期
end_date项目结束日期（可为空）
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"}]

主键，btree (project_id)

外键 (department_id) 参考 departments(department_id)
外键 (lead_faculty_id) 参考 faculty(faculty_id)

publications - 支持全文搜索的科研论文。

publication_id唯一记录标识符 (PK, INT)
project_id关联的科研项目 (FK，可为空)
title论文标题
abstract论文摘要 (MEDIUMTEXT，与 title 共同建立 FULLTEXT 索引)
pub_year发表年份 (YEAR)
venue期刊或会议名称（可为空）
doi数字对象标识符 DOI（可为空）
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

主键，btree (publication_id)
唯一键 (doi)
FULLTEXT (title, abstract)

外键 (project_id) 参考 research_projects(project_id)

project_members - 教师和学生参与科研项目的记录。

member_id唯一记录标识符 (PK, INT)
project_id科研项目标识符 (FK)
faculty_id教师标识符 (FK，可为空)
student_id学生标识符 (FK，可为空)
role成员角色：Principal Investigator、Co-Investigator、Research Assistant、Graduate Student 或 Undergraduate Student (ENUM)
joined_date成员加入项目的日期
left_date成员离开项目的日期（可为空）

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

主键，btree (member_id)

外键 (project_id) 参考 research_projects(project_id)
外键 (faculty_id) 参考 faculty(faculty_id)
外键 (student_id) 参考 students(student_id)

grade_events - 每条选课记录的单项评分（约 120 000 行）。

event_id唯一记录标识符 (PK, BIGINT)
enrollment_id选课记录标识符 (FK)
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评分教师 (FK，可为空)
feedback评分教师的反馈 (TEXT，可为空)

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.

主键，btree (event_id)

外键 (enrollment_id) 参考 enrollments(enrollment_id)
外键 (grader_id) 参考 faculty(faculty_id)

audit_log - 由触发器生成的行级变更历史（约 60 000 行）。

log_id唯一记录标识符 (PK, BIGINT)
table_name被修改的表名
record_id被修改记录的主键 (BIGINT)
action变更类型：INSERT、UPDATE 或 DELETE (ENUM)
changed_at变更日期和时间 (TIMESTAMP)
changed_by数据库用户或应用上下文（可为空）
old_values变更前的列值（JSON，INSERT 时为 NULL）
new_values变更后的列值（JSON，DELETE 时为 NULL）

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}

主键，btree (log_id)

视图

v_student_gpa - 每名学生每学期的加权 GPA。

student_id学生标识符
student_number唯一学号
first_name学生的名字
last_name学生的姓氏
semester_id学期标识符
semester_name学期名称
semester_gpa本学期加权 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_code先修课程代码
prerequisite_title先修课程名称
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