Tarefa 65:

Cursos e projetos de pesquisa por departamento

MariaDB

O reitor compara ensino e pesquisa. Conte os cursos por department_id na tabela courses e os projetos por department_id na tabela research_projects. Alguns departamentos têm cursos mas não projetos, outros têm projetos mas não cursos: mostre os dois tipos, com 0 no número que falta.
Mostre as colunas department_id, courses e projects, ordenadas por department_id.

Resultado esperado Colunas: 3 · Linhas: 16

department_id	courses	projects

As linhas de exemplo abrem após verificações erradas: 0 de 3

Escreva sua consulta abaixo e clique em "Verificar!". As tabelas estão descritas no painel direito.

Tutorial

Ao verificar uma consulta, você concorda que ela seja armazenada sem os dados da sua conta e possa fazer parte de conjuntos de dados anonimizados, inclusive para treinamento de IA. Saiba mais

O resultado da consulta aparecerá aqui. Ctrl+Enter - Executar consulta

Banco de Dados University: estrutura e descrição das tabelas

University DB é um banco de dados de exemplo moderno para MariaDB 11.7+ desenvolvido para aprendizado de SQL - criado como um substituto rico em recursos do clássico banco de dados Sakila.

Ele abrange todos os principais tipos de dados do MariaDB, incluindo VECTOR(1536), JSON, SET e índices FULLTEXT, está totalmente normalizado na 3FN e contém dados suficientes para exercícios de iniciantes e consultas analíticas complexas.

O banco de dados University contém 16 tabelas principais descrevendo a estrutura acadêmica de uma universidade - departamentos, corpo docente, estudantes, disciplinas, matrículas, projetos de pesquisa e muito mais.

Diagrama ER compacto do banco de dados University com relacionamentos entre tabelas Diagrama ER do banco de dados University

Mais sobre o banco de dados University: esquema, exemplos de consultas e todos os exercícios →

Lista de Tabelas

semesters - tabela de semestres acadêmicos.

semester_ididentificador único do registro (PK, TINYINT)
termtipo de período: Fall, Spring ou Summer (ENUM)
academic_yearano letivo (tipo YEAR)
namenome do semestre (ex.: 'Fall 2024')
start_dateprimeiro dia do semestre
end_dateúltimo dia do semestre
enroll_deadlineúltimo dia para matrícula de estudantes
is_activeindica se o semestre está ativo no momento (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 - salas de aula e laboratórios do campus.

room_ididentificador único do registro (PK, SMALLINT)
buildingnome do edifício
room_numbernúmero ou identificação da sala
capacitynúmero máximo de lugares (SMALLINT)
room_typetipo de sala: lecture, seminar, lab, computer_lab ou online (ENUM)
has_projectorindica se a sala possui projetor (BOOLEAN)
has_videoindica se a sala possui equipamento de videoconferência (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 - bolsas de estudo disponíveis.

scholarship_ididentificador único do registro (PK, SMALLINT)
namenome da bolsa de estudo
amountvalor da bolsa (DECIMAL)
frequencyfrequência de concessão: one-time, annual ou per-semester (ENUM)
eligibilitycritérios de elegibilidade em JSON - ex.: {"min_gpa": 3.5, "need_based": true}
is_activeindica se a bolsa está sendo oferecida no momento (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 - hierarquia de três níveis de departamentos (Faculdade → Departamento → Subdepartamento).

department_ididentificador único do registro (PK, TINYINT)
parent_ididentificador do departamento pai - FK autorreferenciada (nulável)
codecódigo abreviado do departamento (CHAR)
namenome do departamento
levelnível hierárquico: 1 = Faculdade, 2 = Departamento, 3 = Subdepartamento (TINYINT)
head_faculty_ididentificador do chefe do departamento (FK, nulável)
establishedano de fundação do departamento (YEAR, nulável)

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 - membros do corpo docente da universidade.

faculty_ididentificador único do registro (PK, SMALLINT)
department_ididentificador do departamento (FK)
first_nameprimeiro nome do docente
last_namesobrenome do docente
emailendereço de e-mail institucional
phonenúmero de telefone do escritório (nulável)
rankcargo acadêmico: Instructor, Assistant Professor, Associate Professor, Professor ou Emeritus (ENUM)
hire_datedata de contratação
officenúmero ou localização da sala do docente (nulável)
office_hourshorários de atendimento semanais em JSON - ex.: [{"day":"Mon","start":"10:00","end":"12:00"}]
biotexto biográfico (TEXT, nulável)
is_activeindica se o docente está ativo no momento (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 - estudantes matriculados.

student_ididentificador único do registro (PK, INT)
department_ididentificador do departamento de origem (FK)
student_numbernúmero de matrícula único do estudante (CHAR, ex.: 'S000123')
first_nameprimeiro nome do estudante
last_namesobrenome do estudante
emailendereço de e-mail do estudante
date_of_birthdata de nascimento do estudante
gendergênero: M, F, NB, Other ou Prefer not to say (ENUM, nulável)
enrollment_datedata da primeira matrícula do estudante
expected_gradano previsto de formatura (YEAR, nulável)
statussituação acadêmica: active, inactive, graduated, suspended ou withdrawn (ENUM)
gpacoeficiente de rendimento acumulado 0.000–4.000, mantido por trigger (DECIMAL, nulável)
contactscontato de emergência e endereço em JSON - ex.: {"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 - catálogo de disciplinas com suporte a busca por texto completo e vetorial.

course_ididentificador único do registro (PK, SMALLINT)
department_ididentificador do departamento responsável (FK)
codecódigo da disciplina (ex.: 'CS101') (CHAR)
titletítulo da disciplina
creditsnúmero de créditos (TINYINT)
levelnível acadêmico: undergraduate, graduate ou doctoral (ENUM)
descriptiondescrição detalhada da disciplina (TEXT, índice FULLTEXT com title)
is_activeindica se a disciplina está sendo oferecida no momento (BOOLEAN)
embeddingembedding semântico de 1536 dimensões para busca por similaridade vetorial (VECTOR(1536), nulável)

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 - relações de pré-requisitos entre disciplinas (muitos-para-muitos autorreferenciada).

course_ididentificador da disciplina (FK)
prerequisite_ididentificador da disciplina pré-requisito (FK)
is_mandatoryindica se o pré-requisito é obrigatório ou recomendado (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 - turmas de disciplinas (uma oferta específica de uma disciplina em um semestre).

section_ididentificador único do registro (PK, INT)
course_ididentificador da disciplina (FK)
semester_ididentificador do semestre (FK)
faculty_ididentificador do professor responsável (FK)
room_ididentificador da sala alocada (FK, nulável - NULL para turmas totalmente online)
section_numbernúmero da turma dentro da disciplina/semestre (TINYINT)
deliverymodalidade de ensino: in-person, online ou hybrid (ENUM)
max_capacitynúmero máximo de vagas (SMALLINT)
statussituação da turma: open, closed, cancelled ou completed (ENUM)
schedulehorários semanais das aulas em JSON - ex.: [{"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 - matrículas de estudantes em turmas de disciplinas.

enrollment_ididentificador único do registro (PK, INT)
student_ididentificador do estudante (FK)
section_ididentificador da turma (FK)
enrolled_atdata e hora da matrícula (TIMESTAMP)
statussituação da matrícula: enrolled, dropped, completed, failed ou incomplete (ENUM)
final_gradeconceito final em letra, ex.: 'A', 'B+' (CHAR, nulável)
final_scorepontuação numérica final 0.00–100.00 (DECIMAL, nulável)

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 - bolsas de estudo concedidas a estudantes.

award_ididentificador único do registro (PK, INT)
student_ididentificador do estudante (FK)
scholarship_ididentificador da bolsa de estudo (FK)
awarded_datedata em que a bolsa foi concedida
expires_datedata de expiração do prêmio (nulável)
amount_awardedvalor efetivamente concedido (DECIMAL)
notesobservações adicionais sobre o prêmio (TEXT, nulável)

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 - projetos de pesquisa dos departamentos.

project_ididentificador único do registro (PK, SMALLINT)
department_ididentificador do departamento (FK)
lead_faculty_idinvestigador principal do projeto (FK)
titletítulo do projeto
abstractdescrição do projeto (TEXT, nulável)
start_datedata de início do projeto
end_datedata de encerramento do projeto (nulável)
statussituação do projeto: proposed, active, completed ou cancelled (ENUM)
fundingfontes de financiamento em JSON - ex.: [{"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 - publicações científicas com suporte a busca por texto completo.

publication_ididentificador único do registro (PK, INT)
project_idprojeto de pesquisa associado (FK, nulável)
titletítulo da publicação
abstractresumo da publicação (MEDIUMTEXT, índice FULLTEXT com title)
pub_yearano de publicação (YEAR)
venuenome do periódico ou conferência (nulável)
doiIdentificador de Objeto Digital (DOI, nulável)
keywordspalavras-chave - uma ou mais entre: AI, ML, Data Science, Networking, Security, Algorithms, Databases, HCI, Theory, Bioinformatics, Systems, Mathematics, Physics, Chemistry, Biology (SET)
citation_countnúmero de citações recebidas (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 - participação de docentes e estudantes em projetos de pesquisa.

member_ididentificador único do registro (PK, INT)
project_ididentificador do projeto de pesquisa (FK)
faculty_ididentificador do docente (FK, nulável)
student_ididentificador do estudante (FK, nulável)
rolepapel do membro: Principal Investigator, Co-Investigator, Research Assistant, Graduate Student ou Undergraduate Student (ENUM)
joined_datedata de ingresso no projeto
left_datedata de saída do projeto (nulável)

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 - itens de avaliação individuais por matrícula (~120 000 linhas).

event_ididentificador único do registro (PK, BIGINT)
enrollment_ididentificador da matrícula (FK)
item_namenome do item avaliado (ex.: 'Assignment 1', 'Midterm Exam')
item_typetipo de item: assignment, quiz, midterm, final, project, participation ou lab (ENUM)
scorepontuação obtida (DECIMAL)
max_scorepontuação máxima possível, padrão 100.00 (DECIMAL)
weightfração da nota final, ex.: 0.1500 para 15% (DECIMAL)
graded_atdata e hora do registro da nota (DATETIME)
grader_iddocente responsável pela correção (FK, nulável)
feedbacktexto de feedback do avaliador (TEXT, nulável)

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	Boa análise, revisar a seção 3.

PRIMARY KEY, btree (event_id)

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

audit_log - histórico de alterações gerado por triggers (~60 000 linhas).

log_ididentificador único do registro (PK, BIGINT)
table_namenome da tabela modificada
record_idchave primária do registro modificado (BIGINT)
actiontipo de alteração: INSERT, UPDATE ou DELETE (ENUM)
changed_atdata e hora da alteração (TIMESTAMP)
changed_byusuário do banco de dados ou contexto da aplicação (nulável)
old_valuesvalores anteriores das colunas em JSON (null para INSERT)
new_valuesnovos valores das colunas em JSON (null para 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)

Visões

v_student_gpa - GPA ponderado por estudante por semestre.

student_ididentificador do estudante
student_numbernúmero de matrícula único do estudante
first_nameprimeiro nome do estudante
last_namesobrenome do estudante
semester_ididentificador do semestre
semester_namenome do semestre
semester_gpaGPA ponderado do semestre
credits_earnedcréditos obtidos no semestre

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 - estudantes matriculados com informações de contato por turma.

section_ididentificador da turma
course_codecódigo da disciplina
course_titletítulo da disciplina
semester_namenome do semestre
student_ididentificador do estudante
student_numbernúmero de matrícula único do estudante
first_nameprimeiro nome do estudante
last_namesobrenome do estudante
emailendereço de e-mail do estudante
statussituação da matrícula

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 - percentual histórico de aprovação/reprovação e nota média por disciplina.

course_ididentificador da disciplina
codecódigo da disciplina
titletítulo da disciplina
semester_ididentificador do semestre
semester_namenome do semestre
total_enrolledtotal de estudantes matriculados
passednúmero de estudantes aprovados
pass_ratetaxa de aprovação em percentual
avg_scorenota final média

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 - turmas ministradas e taxa de ocupação por docente por semestre.

faculty_ididentificador do docente
first_nameprimeiro nome do docente
last_namesobrenome do docente
semester_ididentificador do semestre
semester_namenome do semestre
sections_taughtnúmero de turmas ministradas
total_capacitycapacidade total de vagas em todas as turmas
total_enrolledtotal de estudantes matriculados
fill_ratetaxa de ocupação em percentual

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 - estudantes classificados pelo total de bolsas recebidas.

rank_positionposição no ranking por valor total de bolsas
student_ididentificador do estudante
student_numbernúmero de matrícula único do estudante
first_nameprimeiro nome do estudante
last_namesobrenome do estudante
total_scholarshipsnúmero de bolsas concedidas
total_amountvalor total de amount_awarded em todas as bolsas

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 - contagem de artigos e citações por departamento por ano.

department_ididentificador do departamento
department_namenome do departamento
pub_yearano de publicação
paper_countnúmero de artigos publicados
total_citationstotal de citações de todos os artigos

department_id	department_name	pub_year	paper_count	total_citations
3	Computer Science	2024	12	87

v_prerequisite_tree - pré-requisitos diretos de cada disciplina.

course_ididentificador da disciplina
course_codecódigo da disciplina
course_titletítulo da disciplina
prerequisite_ididentificador da disciplina pré-requisito
prerequisite_codecódigo da disciplina pré-requisito
prerequisite_titletítulo da disciplina pré-requisito
is_mandatoryindica se o pré-requisito é obrigatório ou recomendado

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