Tâche 33:

Places par bâtiment et type de salle avec sous-totaux

MariaDB

Les services généraux comptent les places des salles physiques de la table rooms (ignorez le type online). Affichez les places pour chaque bâtiment et type de salle, un sous-total par bâtiment et le total général du campus.
Affichez les colonnes building, room_type et seats. Dans le sous-total d'un bâtiment, room_type vaut NULL ; dans le total général, les deux colonnes valent NULL. L'ordre des lignes n'a pas d'importance.

Résultat attendu Colonnes: 3 · Lignes: 21

building	room_type	seats

Les lignes d'exemple s'ouvrent après des vérifications ratées : 0 sur 2

Écrivez votre requête ci-dessous et cliquez sur "Vérifier !". Les tables sont décrites dans le panneau de droite.

Tutoriel

En vérifiant une requête, vous acceptez qu'elle soit enregistrée sans les données de votre compte et puisse faire partie de jeux de données anonymisés, y compris pour l'entraînement d'IA. En savoir plus

Le résultat de la requête s'affichera ici. Ctrl+Enter - Exécuter la requête

Base de données University : structure et description des tables

University DB est une base de données d'exemple moderne pour MariaDB 11.7+ conçue pour l'apprentissage du SQL - pensée comme un remplacement riche en fonctionnalités de la base de données classique Sakila.

Elle couvre tous les types de données significatifs de MariaDB, notamment VECTOR(1536), JSON, SET et les index FULLTEXT, est entièrement normalisée en 3FN et contient suffisamment de données pour les exercices débutants comme pour les requêtes analytiques complexes.

La base de données University contient 16 tables principales décrivant la structure académique d'une université : départements, corps enseignant, étudiants, cours, inscriptions, projets de recherche, et bien plus encore.

Schéma ER compact de la base de données University avec les relations entre les tables Schéma ER de la base de données University

En savoir plus sur la base University : schéma, exemples de requêtes et tous les exercices →

Liste des tables

semesters - table des semestres académiques.

semester_ididentifiant unique de l'enregistrement (PK, TINYINT)
termtype de période : Fall, Spring ou Summer (ENUM)
academic_yearannée académique (type YEAR)
namenom du semestre (ex. 'Fall 2024')
start_datepremier jour du semestre
end_datedernier jour du semestre
enroll_deadlinedernière date pour l'inscription des étudiants
is_activeindique si le semestre est actuellement en cours (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 - salles de cours et laboratoires du campus.

room_ididentifiant unique de l'enregistrement (PK, SMALLINT)
buildingnom du bâtiment
room_numbernuméro ou étiquette de la salle
capacitynombre maximum de places (SMALLINT)
room_typetype de salle : lecture, seminar, lab, computer_lab ou online (ENUM)
has_projectorindique si la salle est équipée d'un projecteur (BOOLEAN)
has_videoindique si la salle est équipée d'un système de vidéoconférence (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 - bourses disponibles.

scholarship_ididentifiant unique de l'enregistrement (PK, SMALLINT)
namenom de la bourse
amountmontant de la récompense (DECIMAL)
frequencyfréquence d'attribution : one-time, annual ou per-semester (ENUM)
eligibilitycritères d'éligibilité au format JSON - ex. {"min_gpa": 3.5, "need_based": true}
is_activeindique si la bourse est actuellement proposée (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 - hiérarchie de départements à trois niveaux (Faculté → Département → Sous-département).

department_ididentifiant unique de l'enregistrement (PK, TINYINT)
parent_ididentifiant du département parent - FK auto-référençante (nullable)
codecode court du département (CHAR)
namenom du département
levelniveau hiérarchique : 1 = Faculté, 2 = Département, 3 = Sous-département (TINYINT)
head_faculty_ididentifiant du responsable du département (FK, nullable)
establishedannée de création du département (YEAR, nullable)

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 - membres du corps enseignant de l'université.

faculty_ididentifiant unique de l'enregistrement (PK, SMALLINT)
department_ididentifiant du département (FK)
first_nameprénom du membre du corps enseignant
last_namenom de famille du membre du corps enseignant
emailadresse e-mail institutionnelle
phonenuméro de téléphone du bureau (nullable)
rankrang académique : Instructor, Assistant Professor, Associate Professor, Professor ou Emeritus (ENUM)
hire_datedate d'embauche
officenuméro de bureau ou emplacement (nullable)
office_hoursheures de permanence hebdomadaires au format tableau JSON - ex. [{"day":"Mon","start":"10:00","end":"12:00"}]
biotexte biographique (TEXT, nullable)
is_activeindique si le membre du corps enseignant est actuellement en activité (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 - étudiants inscrits.

student_ididentifiant unique de l'enregistrement (PK, INT)
department_ididentifiant du département d'appartenance (FK)
student_numbernuméro d'étudiant unique (CHAR, ex. 'S000123')
first_nameprénom de l'étudiant
last_namenom de famille de l'étudiant
emailadresse e-mail de l'étudiant
date_of_birthdate de naissance de l'étudiant
gendergenre : M, F, NB, Other ou Prefer not to say (ENUM, nullable)
enrollment_datedate de la première inscription de l'étudiant
expected_gradannée de diplôme prévue (YEAR, nullable)
statusstatut d'inscription : active, inactive, graduated, suspended ou withdrawn (ENUM)
gpamoyenne générale cumulative 0,000–4,000, maintenue par déclencheur (DECIMAL, nullable)
contactscontact d'urgence et adresse au format 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 - catalogue des cours avec prise en charge de la recherche plein texte et vectorielle.

course_ididentifiant unique de l'enregistrement (PK, SMALLINT)
department_ididentifiant du département propriétaire (FK)
codecode du cours, ex. 'CS101' (CHAR)
titleintitulé du cours
creditsnombre de crédits (TINYINT)
levelniveau académique : undergraduate, graduate ou doctoral (ENUM)
descriptiondescription détaillée du cours (TEXT, index FULLTEXT avec title)
is_activeindique si le cours est actuellement proposé (BOOLEAN)
embeddingembedding sémantique de 1536 dimensions pour la recherche par similarité vectorielle (VECTOR(1536), nullable)

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 - relations de prérequis entre cours (many-to-many auto-référençante).

course_ididentifiant du cours (FK)
prerequisite_ididentifiant du cours prérequis obligatoire (FK)
is_mandatoryindique si le prérequis est obligatoire ou recommandé (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 - groupes de cours (une offre spécifique d'un cours au cours d'un semestre).

section_ididentifiant unique de l'enregistrement (PK, INT)
course_ididentifiant du cours (FK)
semester_ididentifiant du semestre (FK)
faculty_ididentifiant de l'enseignant (FK)
room_ididentifiant de la salle attribuée (FK, nullable - NULL pour les cours entièrement en ligne)
section_numbernuméro du groupe au sein du cours et du semestre (TINYINT)
deliverymode d'enseignement : in-person, online ou hybrid (ENUM)
max_capacitynombre maximum d'inscriptions (SMALLINT)
statusstatut du groupe : open, closed, cancelled ou completed (ENUM)
schedulehoraires hebdomadaires au format 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 - inscriptions des étudiants aux groupes de cours.

enrollment_ididentifiant unique de l'enregistrement (PK, INT)
student_ididentifiant de l'étudiant (FK)
section_ididentifiant du groupe (FK)
enrolled_atdate et heure de l'inscription (TIMESTAMP)
statusstatut d'inscription : enrolled, dropped, completed, failed ou incomplete (ENUM)
final_gradenote finale en lettre, ex. 'A', 'B+' (CHAR, nullable)
final_scorescore numérique final 0,00–100,00 (DECIMAL, nullable)

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 - bourses attribuées aux étudiants.

award_ididentifiant unique de l'enregistrement (PK, INT)
student_ididentifiant de l'étudiant (FK)
scholarship_ididentifiant de la bourse (FK)
awarded_datedate d'attribution de la bourse
expires_datedate d'expiration de la récompense (nullable)
amount_awardedmontant réellement attribué (DECIMAL)
notesnotes supplémentaires sur la récompense (TEXT, nullable)

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 - projets de recherche des départements.

project_ididentifiant unique de l'enregistrement (PK, SMALLINT)
department_ididentifiant du département (FK)
lead_faculty_idinvestigateur principal (FK)
titletitre du projet
abstractdescription du projet (TEXT, nullable)
start_datedate de début du projet
end_datedate de fin du projet (nullable)
statusstatut du projet : proposed, active, completed ou cancelled (ENUM)
fundingsources de financement au format 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 - publications de recherche avec prise en charge de la recherche plein texte.

publication_ididentifiant unique de l'enregistrement (PK, INT)
project_idprojet de recherche associé (FK, nullable)
titletitre de la publication
abstractrésumé de la publication (MEDIUMTEXT, index FULLTEXT avec title)
pub_yearannée de publication (YEAR)
venuenom du journal ou de la conférence (nullable)
doiidentifiant numérique d'objet DOI (nullable)
keywordsmots-clés - une ou plusieurs valeurs parmi : AI, ML, Data Science, Networking, Security, Algorithms, Databases, HCI, Theory, Bioinformatics, Systems, Mathematics, Physics, Chemistry, Biology (SET)
citation_countnombre de citations reçues (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 - participation des enseignants et des étudiants aux projets de recherche.

member_ididentifiant unique de l'enregistrement (PK, INT)
project_ididentifiant du projet de recherche (FK)
faculty_ididentifiant du membre du corps enseignant (FK, nullable)
student_ididentifiant de l'étudiant (FK, nullable)
rolerôle du membre : Principal Investigator, Co-Investigator, Research Assistant, Graduate Student ou Undergraduate Student (ENUM)
joined_datedate d'intégration du membre au projet
left_datedate de départ du membre du projet (nullable)

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 - éléments d'évaluation individuels par inscription (~120 000 lignes).

event_ididentifiant unique de l'enregistrement (PK, BIGINT)
enrollment_ididentifiant de l'inscription (FK)
item_namenom de l'élément évalué, ex. 'Assignment 1', 'Midterm Exam'
item_typetype d'élément : assignment, quiz, midterm, final, project, participation ou lab (ENUM)
scorenote obtenue (DECIMAL)
max_scorenote maximale possible, défaut 100,00 (DECIMAL)
weightfraction de la note finale, ex. 0,1500 pour 15% (DECIMAL)
graded_atdate et heure d'enregistrement de la note (DATETIME)
grader_idmembre du corps enseignant ayant noté l'élément (FK, nullable)
feedbacktexte de retour de l'évaluateur (TEXT, nullable)

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	Bonne analyse, revoir la section 3.

PRIMARY KEY, btree (event_id)

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

audit_log - historique des modifications généré par déclencheurs (~60 000 lignes).

log_ididentifiant unique de l'enregistrement (PK, BIGINT)
table_namenom de la table modifiée
record_idclé primaire de l'enregistrement modifié (BIGINT)
actiontype de modification : INSERT, UPDATE ou DELETE (ENUM)
changed_atdate et heure de la modification (TIMESTAMP)
changed_byutilisateur de la base de données ou contexte applicatif (nullable)
old_valuesvaleurs précédentes des colonnes au format JSON (null pour INSERT)
new_valuesnouvelles valeurs des colonnes au format JSON (null pour 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)

Vues

v_student_gpa - moyenne pondérée par étudiant et par semestre.

student_ididentifiant de l'étudiant
student_numbernuméro d'étudiant unique
first_nameprénom de l'étudiant
last_namenom de famille de l'étudiant
semester_ididentifiant du semestre
semester_namenom du semestre
semester_gpamoyenne pondérée du semestre
credits_earnedcrédits obtenus au cours du 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 - étudiants inscrits avec coordonnées par groupe de cours.

section_ididentifiant du groupe
course_codecode du cours
course_titleintitulé du cours
semester_namenom du semestre
student_ididentifiant de l'étudiant
student_numbernuméro d'étudiant unique
first_nameprénom de l'étudiant
last_namenom de famille de l'étudiant
emailadresse e-mail de l'étudiant
statusstatut d'inscription

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 - taux de réussite/échec historique et score moyen par cours.

course_ididentifiant du cours
codecode du cours
titleintitulé du cours
semester_ididentifiant du semestre
semester_namenom du semestre
total_enrollednombre total d'étudiants inscrits
passednombre d'étudiants ayant réussi
pass_ratetaux de réussite en pourcentage
avg_scorescore final moyen

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 - groupes enseignés et taux de remplissage par enseignant et par semestre.

faculty_ididentifiant du membre du corps enseignant
first_nameprénom du membre du corps enseignant
last_namenom de famille du membre du corps enseignant
semester_ididentifiant du semestre
semester_namenom du semestre
sections_taughtnombre de groupes enseignés
total_capacitycapacité totale en places sur tous les groupes
total_enrollednombre total d'étudiants inscrits
fill_ratetaux de remplissage en pourcentage

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 - étudiants classés par montant total de bourses reçues.

rank_positionclassement par montant total de bourses
student_ididentifiant de l'étudiant
student_numbernuméro d'étudiant unique
first_nameprénom de l'étudiant
last_namenom de famille de l'étudiant
total_scholarshipsnombre de bourses attribuées
total_amountmontant total de amount_awarded sur toutes les bourses

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 - nombre d'articles et citations par département et par année.

department_ididentifiant du département
department_namenom du département
pub_yearannée de publication
paper_countnombre d'articles publiés
total_citationsnombre total de citations sur tous les articles

department_id	department_name	pub_year	paper_count	total_citations
3	Computer Science	2024	12	87

v_prerequisite_tree - prérequis directs pour chaque cours.

course_ididentifiant du cours
course_codecode du cours
course_titleintitulé du cours
prerequisite_ididentifiant du cours prérequis
prerequisite_codecode du cours prérequis
prerequisite_titleintitulé du cours prérequis
is_mandatoryindique si le prérequis est obligatoire ou recommandé

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