Per le prime cinque domande

Considerare una base di dati sullo schema i cui dettagli sono stati illustrati a lezione e pubblicati sul sito.
Per comodità si ricorda che contiene le relazioni
- Elezioni (ID, Anno, TipoElezione) con  chiave primaria ID
- Partiti (Cod, Nome) con chiave primaria Cod
- Risultati (Elezione, Partito, Seggi) con chiave primaria formata da Elezione e Partito 

Domanda 1
Algebra relazionale, vedere immagini (nel file pdf) e formulare interrogazioni equivalenti in SQL
parziale20211126domanda1testo.pdf

Soluzioni

1A
SELECT Nome, Anno, TipoElezione, Seggi
FROM Risultati JOIN Partiti ON Partito=Cod 
   JOIN Elezioni ON Elezione=ID
WHERE Anno > 2006
ORDER BY Anno, Nome, TipoElezione

1B
SELECT Nome, Anno, TipoElezione, Seggi
FROM Risultati JOIN Partiti ON Partito=Cod 
   JOIN Elezioni ON Elezione=ID
WHERE TipoElezione ='Politiche'
ORDER BY Anno, Nome, TipoElezione

1C
SELECT Nome, Anno, TipoElezione, Seggi
FROM Risultati JOIN Partiti ON Partito=Cod 
   JOIN Elezioni ON Elezione=ID
WHERE Nome ='Partito A'
ORDER BY Anno, TipoElezione

1D
SELECT Nome, Anno, TipoElezione, Seggi
FROM Risultati JOIN Partiti ON Partito=Cod 
   JOIN Elezioni ON Elezione=ID
WHERE Seggi > 60
ORDER BY Anno, Nome, TipoElezione

Domanda 2A
Con riferimento a tale schema, scrivere l'interrogazione che mostra i risultati delle varie elezioni 'Politiche', con una relazione che contiene ennuple con anno dell'elezione, nome del partito e seggi, ordinata per anno dell'elezione e numero decrescente di seggi

SELECT Anno, nome as Partito, Seggi
FROM Risultati JOIN Elezioni ON elezione=id
               JOIN Partiti ON partito=cod
WHERE TipoElezione ='Politiche'
ORDER BY Anno, seggi DESC

Domanda 2B
Con riferimento a tale schema, scrivere l'interrogazione che mostra il numero di seggi ottenuti dal 'Partito B' nelle varie elezioni, con una relazione che contiene ennuple con tipo dell'elezione, anno e seggi, ordinata per tipo dell'elezione e anno

SELECT Anno, tipoelezione, Seggi
FROM Risultati JOIN Elezioni ON elezione=id
               JOIN Partiti ON partito=cod
WHERE Nome ='Partito B'
ORDER BY TipoElezione, Anno

Domanda 2C come 2A ma 'Europee'
Domanda 2D come 2B ma 'Partito C'

Domanda 3 (uguale per tutti)
Con riferimento a tale schema, scrivere l'interrogazione che mostra i partiti che hanno conseguito almeno una volta almeno 5 seggi. Mostrare i nomi dei partiti, in ordine alfabetico
 
SELECT DISTINCT nome as partito
FROM Risultati JOIN Partiti ON partito=cod
WHERE Seggi >= 5
ORDER BY nome

Domanda 4A e 4C

Con riferimento a tale schema, scrivere l'interrogazione che calcola, per ciascun partito il numero medio di seggi ottenuto nelle elezioni 'Europee'. L'interrogazione deve mostrare codice e nome del partito e numero medio di seggi e il risultato deve essere ordinato per nome del partito.

SELECT Cod as CodicePartito, Nome,  avg(Seggi) as mediaSeggi
FROM (Partiti JOIN Risultati ON Cod=Partito) 
              JOIN Elezioni ON Elezione=ID
WHERE TipoElezione LIKE 'Europee%'
GROUP BY Cod, Nome 
ORDER BY Nome 


Domanda 4B e 4D come 4A  ma 'Politiche'

Domanda 5A e 5C
Con riferimento a tale schema, scrivere l'interrogazione che trova, per ciascun partito, le elezioni 'Europee' in cui ha ottenuto un numero di seggi maggiore o uguale a quelli ottenuti in media dallo stesso partito nelle elezioni dello stesso tipo. L'interrogazione deve mostrare nome del partito, anno, numero di seggi nell'elezione e numero medio di seggi e il risultato deve essere ordinato per nome del partito e anno. Si suggerisce di usare una vista basata sull'interrogazione precedente.


CREATE VIEW MediaSeggiPerPartito AS 
  SELECT Cod, Nome, avg(Seggi) as MediaSeggi
  FROM (Partiti JOIN Risultati ON Cod=Partito) 
              JOIN Elezioni ON Elezione=ID
  WHERE TipoElezione = 'Europee'
  GROUP BY Cod, Nome;

SELECT P.Nome, Anno, Seggi, MediaSeggi
FROM Risultati JOIN Partiti P ON Partito=P.Cod
             JOIN Elezioni E ON Elezione=E.ID
             JOIN MediaSeggiPerPartito M ON (M.Cod=P.Cod)
WHERE Seggi > MediaSeggi AND TipoElezione='Europee' 
ORDER BY P.Nome, Anno

Domanda 5B e 5D come 5A ma 'Politiche'

Domanda 6 
Vedi parziale20211126domanda6testo.pdf
Soluzioni
NO SI NO NO
SI SI NO NO
NO SI SI SI