Tarea 33:

Plazas por edificio y tipo de aula con subtotales

MariaDB

El servicio de instalaciones cuenta las plazas de las aulas físicas de la tabla rooms (omite el tipo online). Muestra las plazas de cada edificio y tipo de aula, un subtotal por edificio y el total general del campus.
Muestra las columnas building, room_type y seats. En el subtotal de un edificio room_type es NULL; en el total general las dos columnas son NULL. El orden de las filas no importa.

Resultado esperado Columnas: 3 · Filas: 21

building	room_type	seats

Las filas de ejemplo se abren tras comprobaciones fallidas: 0 de 2

Escriba su consulta abajo y haga clic en "¡Verificar!". Las tablas se describen en el panel derecho.

Tutorial

Al comprobar una consulta, aceptas que se guarde sin los datos de tu cuenta y pueda formar parte de conjuntos de datos anonimizados, incluso para entrenar IA. Más información

El resultado de la consulta aparecerá aquí. Ctrl+Enter - Ejecutar consulta

Base de Datos Universitaria: estructura de tablas y descripción del esquema

La base de datos universitaria es una moderna MariaDB 11.7+ base de datos de ejemplo para aprender SQL - diseñada como un reemplazo rico en características para la clásica base de datos Sakila.

Cubre todos los tipos de datos significativos de MariaDB, incluyendo VECTOR(1536), JSON, SET, y FULLTEXT índices, está completamente normalizada a 3NF, y se entrega con suficientes datos tanto para ejercicios de principiantes como para consultas analíticas complejas.

La base de datos universitaria contiene 16 tablas principales que describen la estructura académica de una universidad - departamentos, facultades, estudiantes, cursos, inscripciones, proyectos de investigación, y más.

Diagrama ER compacto de la base de datos universitaria mostrando relaciones entre tablas Diagrama ER de la base de datos universitaria

Más sobre la base de datos University: esquema, consultas de ejemplo y todos los ejercicios →

La lista de tablas

semesters - tabla de semestres académicos.

semester_ididentificador único del registro (PK, TINYINT)
termtipo de término: Otoño, Primavera, o Verano (ENUM)
academic_yearaño académico (YEAR)
namenombre del semestre (por ejemplo, 'Otoño 2024')
start_dateprimer día del semestre
end_dateúltimo día del semestre
enroll_deadlineúltima fecha para la inscripción de estudiantes
is_activesi el semestre está actualmente activo (BOOLEAN)

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

CLAVE PRIMARIA, btree (semester_id)
CLAVE ÚNICA (term, academic_year)

rooms - aulas y laboratorios del campus.

room_ididentificador único del registro (PK, SMALLINT)
buildingnombre del edificio
room_numbernúmero o etiqueta de la sala
capacitynúmero máximo de asientos (SMALLINT)
room_typetipo de sala: conferencia, seminario, laboratorio, laboratorio de computación, o en línea (ENUM)
has_projectorsi la sala tiene proyector (BOOLEAN)
has_videosi la sala tiene equipo de videoconferencia (BOOLEAN)

room_id	building	room_number	capacity	room_type	has_projector	has_video
1	Salón de Ciencias	101	120	conferencia	1	0

CLAVE PRIMARIA, btree (room_id)
CLAVE ÚNICA (building, room_number)

scholarships - becas y subvenciones disponibles.

scholarship_ididentificador único del registro (PK, SMALLINT)
namenombre de la beca
amountmonto de la beca (DECIMAL)
frequencyfrecuencia de la beca: única, anual, o por semestre (ENUM)
eligibilitycriterios de elegibilidad como JSON - por ejemplo, {"min_gpa": 3.5, "need_based": true}
is_activesi la beca se ofrece actualmente (BOOLEAN)

scholarship_id	name	amount	frequency	eligibility	is_active
1	Beca de Excelencia del Decano	5000.00	anual	{"min_gpa": 3.8, "need_based": false, "majors": ["CS","Math"]}	1

CLAVE PRIMARIA, btree (scholarship_id)

departments - jerarquía de departamentos de tres niveles (Facultad → Departamento → Subdepartamento).

department_ididentificador único del registro (PK, TINYINT)
parent_ididentificador del departamento padre - FK autorreferencial (nullable)
codecódigo corto del departamento (CHAR)
namenombre del departamento
levelnivel de jerarquía: 1 = Facultad, 2 = Departamento, 3 = Subdepartamento (TINYINT)
head_faculty_ididentificador del jefe del departamento (FK, nullable)
establishedaño en que se estableció el departamento (YEAR, nullable)

department_id	parent_id	code	name	level	head_faculty_id	established
1	[null]	ENG	Facultad de Ingeniería	1	1	1965

CLAVE PRIMARIA, btree (department_id)
CLAVE ÚNICA (code)

CLAVE FORÁNEA (parent_id) REFERENCIAS departments(department_id)
CLAVE FORÁNEA (head_faculty_id) REFERENCIAS faculty(faculty_id)

faculty - personal académico y administrativo.

faculty_ididentificador único del registro (PK, SMALLINT)
department_ididentificador del departamento (FK)
first_namenombre del miembro de la facultad
last_nameapellido del miembro de la facultad
emaildirección de correo electrónico institucional
phonenúmero de teléfono de la oficina (nullable)
rankrango académico: Instructor, Profesor Asistente, Profesor Asociado, Profesor, o Emérito (ENUM)
hire_datefecha de contratación
officenúmero o ubicación de la oficina (nullable)
office_hourshorario de oficina semanal como arreglo JSON - por ejemplo, [{"day":"Lun","start":"10:00","end":"12:00"}]
biotexto biográfico (TEXT, nullable)
is_activesi el miembro de la facultad está actualmente activo (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	Profesor	2010-08-15	ENG-204	[{"day":"Lun","start":"10:00","end":"12:00"}]	Experto en sistemas distribuidos.	1

CLAVE PRIMARIA, btree (faculty_id)
CLAVE ÚNICA (email)

CLAVE FORÁNEA (department_id) REFERENCIAS departments(department_id)

students - estudiantes registrados.

student_ididentificador único del registro (PK, INT)
department_ididentificador del departamento principal (FK)
student_numbernúmero de identificación único del estudiante (CHAR, por ejemplo, 'S000123')
first_namenombre del estudiante
last_nameapellido del estudiante
emailcorreo electrónico del estudiante
date_of_birthfecha de nacimiento del estudiante
gendergénero: M, F, NB, Other o Prefer not to say (ENUM, admite NULL)
enrollment_datefecha de la primera matrícula del estudiante
expected_gradaño previsto de graduación (YEAR, admite NULL)
statusestado de matrícula: active, inactive, graduated, suspended o withdrawn (ENUM)
gpaGPA acumulado 0.000–4.000, actualizado por un trigger (DECIMAL, admite NULL)
contactscontacto de emergencia y dirección en JSON, por ejemplo, {"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"}}

CLAVE PRIMARIA, btree (student_id)
CLAVE ÚNICA (student_number)
CLAVE ÚNICA (email)

CLAVE FORÁNEA (department_id) REFERENCIAS departments(department_id)

courses - catálogo de cursos con búsqueda de texto completo y vectorial.

course_ididentificador único del registro (PK, SMALLINT)
department_ididentificador del departamento responsable (FK)
codecódigo del curso, por ejemplo, 'CS101' (CHAR)
titletítulo del curso
creditsnúmero de créditos (TINYINT)
levelnivel académico: undergraduate, graduate o doctoral (ENUM)
descriptiondescripción detallada del curso (TEXT, índice FULLTEXT junto con title)
is_activesi el curso se ofrece actualmente (BOOLEAN)
embeddingembedding semántico de 1536 dimensiones para búsqueda por similitud vectorial (VECTOR(1536), admite 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, ...]

CLAVE PRIMARIA, btree (course_id)
CLAVE ÚNICA (code)
FULLTEXT (title, description)

CLAVE FORÁNEA (department_id) REFERENCIAS departments(department_id)

course_prerequisites - requisitos previos de los cursos (relación muchos a muchos consigo misma).

course_ididentificador del curso (FK)
prerequisite_ididentificador del curso requisito previo (FK)
is_mandatorysi el requisito previo es obligatorio o recomendado (BOOLEAN)

course_id	prerequisite_id	is_mandatory
5	1	1

CLAVE PRIMARIA, btree (course_id, prerequisite_id)

CLAVE FORÁNEA (course_id) REFERENCIAS courses(course_id)
CLAVE FORÁNEA (prerequisite_id) REFERENCIAS courses(course_id)

sections - una edición de un curso en un semestre concreto.

section_ididentificador único del registro (PK, INT)
course_ididentificador del curso (FK)
semester_ididentificador del semestre (FK)
faculty_ididentificador del profesor (FK)
room_ididentificador del aula asignada (FK, admite NULL: NULL para cursos totalmente en línea)
section_numbernúmero de grupo dentro del curso y semestre (TINYINT)
deliverymodalidad: in-person, online o hybrid (ENUM)
max_capacitynúmero máximo de inscritos (SMALLINT)
statusestado del grupo: open, closed, cancelled o completed (ENUM)
schedulehorario semanal en JSON, por ejemplo, [{"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"}]

CLAVE PRIMARIA, btree (section_id)
CLAVE ÚNICA (course_id, semester_id, section_number)

CLAVE FORÁNEA (course_id) REFERENCIAS courses(course_id)
CLAVE FORÁNEA (semester_id) REFERENCIAS semesters(semester_id)
CLAVE FORÁNEA (faculty_id) REFERENCIAS faculty(faculty_id)
CLAVE FORÁNEA (room_id) REFERENCIAS rooms(room_id)

enrollments - inscripciones de estudiantes en grupos de cursos.

enrollment_ididentificador único del registro (PK, INT)
student_ididentificador del estudiante (FK)
section_ididentificador del grupo (FK)
enrolled_atfecha y hora de la inscripción (TIMESTAMP)
statusestado de la inscripción: enrolled, dropped, completed, failed o incomplete (ENUM)
final_gradecalificación final en letras, por ejemplo, 'A', 'B+' (CHAR, admite NULL)
final_scorepuntuación final 0.00–100.00 (DECIMAL, admite 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

CLAVE PRIMARIA, btree (enrollment_id)
CLAVE ÚNICA (student_id, section_id)

CLAVE FORÁNEA (student_id) REFERENCIAS students(student_id)
CLAVE FORÁNEA (section_id) REFERENCIAS sections(section_id)

student_scholarships - becas concedidas a estudiantes.

award_ididentificador único del registro (PK, INT)
student_ididentificador del estudiante (FK)
scholarship_ididentificador de la beca (FK)
awarded_datefecha de concesión de la beca
expires_datefecha de vencimiento de la beca (admite NULL)
amount_awardedimporte concedido (DECIMAL)
notesnotas adicionales sobre la beca (TEXT, admite 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]

CLAVE PRIMARIA, btree (award_id)

CLAVE FORÁNEA (student_id) REFERENCIAS students(student_id)
CLAVE FORÁNEA (scholarship_id) REFERENCIAS scholarships(scholarship_id)

research_projects - proyectos de investigación dirigidos por profesores.

project_ididentificador único del registro (PK, SMALLINT)
department_ididentificador del departamento (FK)
lead_faculty_idinvestigador principal (FK)
titletítulo del proyecto
abstractdescripción del proyecto (TEXT, admite NULL)
start_datefecha de inicio del proyecto
end_datefecha de finalización del proyecto (admite NULL)
statusestado del proyecto: proposed, active, completed o cancelled (ENUM)
fundingfuentes de financiación en JSON, por ejemplo, [{"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"}]

CLAVE PRIMARIA, btree (project_id)

CLAVE FORÁNEA (department_id) REFERENCIAS departments(department_id)
CLAVE FORÁNEA (lead_faculty_id) REFERENCIAS faculty(faculty_id)

publications - artículos de investigación con búsqueda de texto completo.

publication_ididentificador único del registro (PK, INT)
project_idproyecto de investigación asociado (FK, admite NULL)
titletítulo de la publicación
abstractresumen de la publicación (MEDIUMTEXT, índice FULLTEXT junto con title)
pub_yearaño de publicación (YEAR)
venuerevista o congreso (admite NULL)
doiDigital Object Identifier, DOI (admite NULL)
keywordspalabras clave, una o varias de: AI, ML, Data Science, Networking, Security, Algorithms, Databases, HCI, Theory, Bioinformatics, Systems, Mathematics, Physics, Chemistry, Biology (SET)
citation_countnúmero de citas recibidas (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

CLAVE PRIMARIA, btree (publication_id)
CLAVE ÚNICA (doi)
FULLTEXT (title, abstract)

CLAVE FORÁNEA (project_id) REFERENCIAS research_projects(project_id)

project_members - participación de profesores y estudiantes en proyectos de investigación.

member_ididentificador único del registro (PK, INT)
project_ididentificador del proyecto de investigación (FK)
faculty_ididentificador del profesor (FK, admite NULL)
student_ididentificador del estudiante (FK, admite NULL)
rolerol del participante: Principal Investigator, Co-Investigator, Research Assistant, Graduate Student o Undergraduate Student (ENUM)
joined_datefecha de incorporación al proyecto
left_datefecha de salida del proyecto (admite NULL)

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

CLAVE PRIMARIA, btree (member_id)

CLAVE FORÁNEA (project_id) REFERENCIAS research_projects(project_id)
CLAVE FORÁNEA (faculty_id) REFERENCIAS faculty(faculty_id)
CLAVE FORÁNEA (student_id) REFERENCIAS students(student_id)

grade_events - calificaciones individuales por inscripción (~120 000 filas).

event_ididentificador único del registro (PK, BIGINT)
enrollment_ididentificador de la inscripción (FK)
item_namenombre de la actividad evaluada, por ejemplo, 'Assignment 1', 'Midterm Exam'
item_typetipo de actividad: assignment, quiz, midterm, final, project, participation o lab (ENUM)
scorepuntos obtenidos (DECIMAL)
max_scorepuntuación máxima posible, por defecto 100.00 (DECIMAL)
weightpeso en la nota final, por ejemplo, 0.1500 para el 15% (DECIMAL)
graded_atfecha y hora en que se registró la calificación (DATETIME)
grader_idprofesor que calificó la actividad (FK, admite NULL)
feedbackcomentarios del evaluador (TEXT, admite 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	Good analysis, review section 3.

CLAVE PRIMARIA, btree (event_id)

CLAVE FORÁNEA (enrollment_id) REFERENCIAS enrollments(enrollment_id)
CLAVE FORÁNEA (grader_id) REFERENCIAS faculty(faculty_id)

audit_log - historial de cambios por fila generado por triggers (~60 000 filas).

log_ididentificador único del registro (PK, BIGINT)
table_namenombre de la tabla modificada
record_idclave primaria del registro modificado (BIGINT)
actiontipo de cambio: INSERT, UPDATE o DELETE (ENUM)
changed_atfecha y hora del cambio (TIMESTAMP)
changed_byusuario de la base de datos o contexto de la aplicación (admite NULL)
old_valuesvalores anteriores de las columnas en JSON (NULL para INSERT)
new_valuesvalores nuevos de las columnas en 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}

CLAVE PRIMARIA, btree (log_id)

Vistas

v_student_gpa - GPA ponderado de cada estudiante por semestre.

student_ididentificador del estudiante
student_numbernúmero de identificación único del estudiante
first_namenombre del estudiante
last_nameapellido del estudiante
semester_ididentificador del semestre
semester_namenombre del semestre
semester_gpaGPA ponderado del semestre
credits_earnedcréditos obtenidos en el 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 - estudiantes inscritos con datos de contacto por grupo.

section_ididentificador del grupo
course_codecódigo del curso
course_titletítulo del curso
semester_namenombre del semestre
student_ididentificador del estudiante
student_numbernúmero de identificación único del estudiante
first_namenombre del estudiante
last_nameapellido del estudiante
emailcorreo electrónico del estudiante
statusestado de la inscripción

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 - porcentaje histórico de aprobados y suspensos y puntuación media por curso.

course_ididentificador del curso
codecódigo del curso
titletítulo del curso
semester_ididentificador del semestre
semester_namenombre del semestre
total_enrollednúmero total de estudiantes inscritos
passednúmero de estudiantes aprobados
pass_rateporcentaje de aprobados
avg_scorepuntuación final media

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 - grupos impartidos y tasa de ocupación por profesor y semestre.

faculty_ididentificador del profesor
first_namenombre del profesor
last_nameapellido del profesor
semester_ididentificador del semestre
semester_namenombre del semestre
sections_taughtnúmero de grupos impartidos
total_capacitycapacidad total de plazas en todos los grupos
total_enrollednúmero total de estudiantes inscritos
fill_ratetasa de ocupación en porcentaje

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 - estudiantes ordenados por el importe total de becas recibidas.

rank_positionposición según el importe total de becas
student_ididentificador del estudiante
student_numbernúmero de identificación único del estudiante
first_namenombre del estudiante
last_nameapellido del estudiante
total_scholarshipsnúmero de becas recibidas
total_amountsuma de amount_awarded de todas las becas

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 - número de artículos y citas por departamento y año.

department_ididentificador del departamento
department_namenombre del departamento
pub_yearaño de publicación
paper_countnúmero de artículos publicados
total_citationsnúmero total de citas de todos los artículos

department_id	department_name	pub_year	paper_count	total_citations
3	Computer Science	2024	12	87

v_prerequisite_tree - requisitos previos directos de cada curso.

course_ididentificador del curso
course_codecódigo del curso
course_titletítulo del curso
prerequisite_ididentificador del curso requisito previo
prerequisite_codecódigo del curso requisito previo
prerequisite_titletítulo del curso requisito previo
is_mandatorysi el requisito previo es obligatorio o 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