Exploration base de données - projhistoire/Projet-Histoire GitHub Wiki

Requêtes d'exploration

  • Sélection de la table person et pursuit :
SELECT 
    p.pk_person,
    p.name,
    p.sex,
    p.death_date,
    p.definition AS person_definition,
    p.label AS person_label,
    p.notes AS person_notes,
    p.import_metadata AS person_import_metadata,
    p.original_uri,
    p.fk_appellation,
    purs.pk_pursuit,
    purs.label AS pursuit_label,
    purs.begin_date,
    purs.end_date,
    purs.definition AS pursuit_definition,
    purs.notes AS pursuit_notes,
    purs.import_metadata AS pursuit_import_metadata,
    purs.fk_occupation,
    purs.fk_geo_place
FROM person p
INNER JOIN pursuit purs ON p.pk_person = purs.fk_person

  • Même requête avec la fonction view :
CREATE VIEW person_pursuit_view AS
SELECT 
    p.pk_person,
    p.name,
    p.sex,
    p.death_date,
    p.definition AS person_definition,
    p.label AS person_label,
    p.notes AS person_notes,
    p.import_metadata AS person_import_metadata,
    p.original_uri,
    p.fk_appellation,
    purs.pk_pursuit,
    purs.label AS pursuit_label,
    purs.begin_date,
    purs.end_date,
    purs.definition AS pursuit_definition,
    purs.notes AS pursuit_notes,
    purs.import_metadata AS pursuit_import_metadata,
    purs.fk_occupation,
    purs.fk_geo_place
FROM person p
INNER JOIN pursuit purs ON p.pk_person = purs.fk_person;

  • Sélection de la table occupation avec la table person afin de déterminer qui a occupé quelle occupation :
SELECT 
    o.label AS occupation_label, 
    a.name AS person_name
FROM occupation o
JOIN person a ON o.pk_occupation = a.pk_person
ORDER BY o.name ASC

Mêmes fonctions avec la requête view

CREATE VIEW occupation_person_view AS
SELECT 
    o.label AS occupation_label, 
    a.name AS person_name
FROM occupation o
JOIN person a ON o.pk_occupation = a.pk_person
ORDER BY o.name ASC
  • Cette deuxième requête permet d'associer les noms des organisations à leurs emplacements géographiques respectifs.
SELECT o.pk_organisation,
       o.name AS organisation_name,
       gp.name AS place_name,
       gp.longitude,
       gp.latitude
  FROM organisation o
  JOIN geographical_place gp ON o.fk_geographical_place = gp.pk_geographical_place

Même requête avec la fonction view :

CREATE VIEW v_person_pursuit AS
SELECT o.pk_organisation,
       o.name AS organisation_name,
       gp.name AS place_name,
       gp.longitude,
       gp.latitude
  FROM organisation o
  JOIN geographical_place gp ON o.fk_geographical_place = gp.pk_geographical_place

  • Requête qui lie la table person à la table appellation afin de savoir quelle personne a quelle appellation :
SELECT 
    p.pk_person,
    p.name AS person_name,
    p.sex,
    p.death_date,
    a.name AS appellation_name
FROM person p
LEFT JOIN appellation a ON p.fk_appellation = a.pk_appellation;

  • Même requête avec la fonction view :
CREATE VIEW person_appellation_view AS
SELECT 
    p.pk_person,
    p.name AS person_name,
    p.sex,
    p.death_date,
    a.name AS appellation_name
FROM person p
LEFT JOIN appellation a ON p.fk_appellation = a.pk_appellation;