Description des tables :
CREATE TABLE joueurs (
id INTEGER,
nom VARCHAR(32) UNIQUE NOT NULL,
prénom VARCHAR(32) NOT NULL,
-- Année de naissance.
"année" INTEGER NOT NULL,
PRIMARY KEY (id)
);
-- Lors d'un tournoi chaque participant a au moins une fois
-- les blancs, et au moins une fois les noirs.
CREATE TABLE tournois (
id INTEGER,
-- Souvent le nom de la ville dans laquelle le tournoi a lieu.
nom VARCHAR(32) NOT NULL,
"année" INTEGER NOT NULL,
UNIQUE (nom, "année"),
PRIMARY KEY (id)
);
CREATE TABLE parties (
id_tournoi INTEGER,
-- Un tournoi est divisé en rondes. Pendant une ronde,
-- chaque participant joue au plus une partie.
ronde INTEGER NOT NULL,
-- "blancs" et "noirs" désignent les identifiants des joueurs
-- s'affrontant (respectivement avec les pièces blanches et noires).
blancs INTEGER NOT NULL,
noirs INTEGER NOT NULL,
-- "elo_b" (resp. "elo_n") ci-dessous désigne le classement
-- du joueur des blancs (resp. des noirs).
elo_b INTEGER NOT NULL,
elo_n INTEGER NOT NULL,
-- Le résultat peut prendre trois valeurs :
-- 1-0 = les blancs gagnent,
-- 0-1 = les noirs gagnent,
-- 1/2 = match nul.
"résultat" VARCHAR NOT NULL,
PRIMARY KEY (id_tournoi, ronde, blancs, noirs),
FOREIGN KEY (id_tournoi) REFERENCES tournois(id),
FOREIGN KEY (blancs) REFERENCES joueurs(id),
FOREIGN KEY (noirs) REFERENCES joueurs(id)
);Écrivez une requête qui calcule le nombre de Kasparov.
CREATE TABLE joueurs (
id INTEGER,
nom VARCHAR, --en principe NOT NULL (mais facilite les tests)
club VARCHAR NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE matchs (
id INTEGER,
horaire DATETIME,
type VARCHAR NOT NULL, --"simple" ou "double"
"résultat" VARCHAR,
PRIMARY KEY (id)
);
CREATE TABLE participer (
joueur INTEGER,
match INTEGER,
PRIMARY KEY (joueur, match),
FOREIGN KEY (joueur) REFERENCES joueurs(id),
FOREIGN KEY (match) REFERENCES matchs(id),
UNIQUE (joueur, match)
);Écrire une fonction trigger vérifiant que l'ajout d'un joueur (table "participer") est licite : c'est-à-dire s'il s'agit d'un match simple comportant 0 ou 1 joueur, ou d'un double avec au plus 3 joueurs. Il faut aussi s'assurer que les clubs des joueurs sont cohérents (exactement deux représentés, de manière égale).
CREATE TABLE users (
id INTEGER,
name VARCHAR NOT NULL,
UNIQUE (name),
PRIMARY KEY (id)
);
CREATE TABLE friend_with (
user1 INTEGER,
user2 INTEGER,
PRIMARY KEY (user1, user2)
);(user1, user2) existe ssi.
user1 a envoyé une demande d'amitié à user2. La ligne réciproque
(user2, user1) existe si user2 a accepté cette demande.
Écrire alors une fonction trigger agissant à la suppression d'une ligne, et supprimant la ligne réciproque si elle est trouvée.
On considère les tables suivantes. etudiants contient au plus une ligne par étudiant, identifié par son numéro "id". L'attribut note correspond à sa note finale dans une certaine matière, et info représente diverses informations auxquelles on ne s'intéressera pas ici (date de naissance, nom, adresse ...).
La table statistiques maintient des comptes concernant les notes : moyenne de l'ensemble de la promotion, proportion d'étudiants reçus (par exemple note >= 10), et nombre d'étudiant ayant été notés (donc présents à l'examen).
CREATE TABLE etudiants (
id INTEGER PRIMARY KEY,
note REAL,
info TEXT -- devrait être NOT NULL en réalité
);
CREATE TABLE statistiques (
moyenne_generale REAL,
proportion_recus REAL,
nb_presents INTEGER
);Placez un déclencheur sur la table etudiants dont le rôle sera de mettre à jour la table statistiques dès que la note d'un étudiant est entrée. Question/remarque : pourquoi n'a-t-on pas précisé de clé primaire sur la table statistiques ?
Étendre la modélisation dans le cas – plus réaliste – où un étudiant a des notes dans plusieurs matières. Une troisième table/association contenant les notes sera nécessaire (pourquoi ?)
I have a database with columns looking like:
session | order | atype | amt --------+-------+-------+----- 1 | 0 | ADD | 10 1 | 1 | ADD | 20 1 | 2 | SET | 35 1 | 3 | ADD | 10 2 | 0 | SET | 30 2 | 1 | ADD | 20 2 | 2 | SET | 55
It represents actions happening.
I want a function to return the end value of a session, e.g.
SELECT session_val(1); --returns 45
SELECT session_val(2); --returns 55Is it possible to write such a function/query?
-- Reproduction de l'exemple :
CREATE TABLE command(session INTEGER,"order" INTEGER,atype VARCHAR,amt REAL);
INSERT INTO command VALUES (1,0,'ADD',10),(1,1,'ADD',20),(1,2,'SET',35),
(1,3,'ADD',10),(2,0,'SET',30),(2,1,'ADD',20),(2,2,'SET',55);