Scarica Esercizi di SQL svolti (ideale per ripasso) e più Esercizi in PDF di Database Relazionali solo su Docsity!
DATABASE E SQL - esempi teorici
Progettazione logica 2 Esercizi SQL per iniziare 8 GROUP BY HAVING 12 SVOLGIMENTO SQL 18 CREA TABELLE 24 INSERISCO VALORI NELLE TABELLE 26 Tracciato record studente nome: stringa cognome: stringa email: stringa corsoFrequentato Mario Rossi [email protected] Fondamenti di basi di dati Laura Bianchi [email protected] Fondamenti di programmazione Paolo Verdi [email protected] Fondamenti di programmazione corso nomeCorso: stringa ore: intero codice Fondamenti di basi di dati 80 10 Fondamenti di programmazione
Uno studente quale UF frequenta? Per rispondere devo correlare i dati di studente e UF. Nei database relazionali se uno studente frequenta solo un corso, possiamo correlare i dati aggiungendo a studente un’ulteriore informazione sul corso frequentato. E’ una correlazione basata si valori. DBMS (Database Management System) in SQL SELECT * FROM studente, corso
WHERE studente.corsoFrequentato = corso.nomeCorso Associazioni uno a uno ( 1 : 1 ) Associazioni uno a molti ( 1 :N) Associazioni molti a molti (N:M) Si guardano le cardinalità massime!!!!! Attenzione alle associazioni molti a molti noleggio idCliente idAutomobile dataInizio 10 100 10 / 11 / 2021 10 100 11 / 11 / 2021 noleggio 2 id idCliente idAutomobile dataInizio 1 10 100 10 / 11 / 2021 2 10 100 11 / 11 / 2021 3 10 150 11 / 11 / 2021
Progettazione logica
Rappresentazione dei dati nel modello logico relazionale Esempio entità PersonaggioSecondario ha due rappresentazioni, una appartenente alla teoria del modello e una più legata ai DBMS Relazione PersonaggioSecondario(nome) l’id univoco dell’ER diventa PRIMARY KEY Tabella PersonaggioSecondario
Automobile(id, targa, marca, modello, annoImm, disponibile) ClasseTariffaria(id, quotaFissa, quotaKm, quotaAss) Cliente(id, codFisc, nome) Telefono(numero) Automobile(id, targa, marca, modello, annoImm, disponibile) ClasseTariffaria(id, quotaFissa, quotaKm, quotaAss) Traduciamo le associazioni- Primo caso (da non replicare) Cliente(id, codFisc, nome) Telefono(numero) Automobile(id, targa, marca, modello, annoImm, disponibile) ClasseTariffaria(id, quotaFissa, quotaKm, quotaAss)
Possiede(idCliente, numeroTel) Noleggia(idCliente, idAuto, datainizio, anticipo) Appartiene(idAuto, ClasseTarif) L’asterisco indica FOREIGN KEY Traduzione ottimizzata Cliente(id, codFisc, nome) Telefono(numero, idCliente) Automobile(id, targa, marca, modello, annoImm, disponibile, classeTarif) ClasseTariffaria(id, quotaFissa, quotaKm, quotaAss) Noleggia(idCliente, idAuto, datainizio, anticipo)
Utilizza (idPrenotazione, idPianta) Specie (id, nome, descrizione, coltivazione, esposizione, collocazione, idDipendente) Dipendente (id, cognome, nome, tipo, qualifica, annoAssunzione) Attivita (id, nome, costoOrario) Svolge (idDipendente, idAttivita*)
Esercizi SQL per iniziare
Romanzo(id, idAutore*, titolo, annoPubblicazione, prezzo, scheda)
Autore(id, cognome, nome, nazionalità, dataNascita, luogoNascita, dataMorte,
luogoMorte)
Personaggio(id, nome, cognome, descrizione)
PersonaggioRomanzo(idPersonaggio, idRomanzo, descrizione)
Scrivere le interrogazioni SQL che soddisfano le seguenti richieste:
1. Romanzi pubblicati nel 2009
select * from Romanzo where annoPubblicazione= 2009
2. Romanzi pubblicati a partire dal 1990
SELECT *
FROM Romanzo
WHERE annoPubblicazione >= 1990
4. Autore (cioè Nome e Cognome) e Titolo di tutti i romanzi
SELECT Nome,Cognome,Titolo
FROM Romanzo, Autore
WHERE Romanzo.idAutore = Autore.id
Oppure (cambia solo la forma)
SELECT A.Nome, A.Cognome, R.Titolo
FROM Romanzo R, Autore A
WHERE R.idAutore = A.id
5. Autore (cioè Nome e Cognome) e Titolo di tutti i romanzi, ordinati per
cognome e nome dell’autore
SELECT nome,cognome,titolo
FROM Romanzo, Autore
WHERE Romanzo.idAutore = Autore.id
ORDER BY cognome, nome
6. Autore (cioè Nome e Cognome) e Titolo dei romanzi di autori russi, ordinati
per cognome e nome dell’autore
SELECT Nome, Cognome, Titolo
FROM Romanzo, Autore
WHERE Romanzo.idAutore=Autore.id AND luogoNascita="Russia"
ORDER BY Nome, Cognome
7. Titolo dei romanzi di autori nati nel ventesimo secolo, ordinati per cognome
e nome dell’autore e – a parità di autore – per data di pubblicazione
SELECT Titolo, Cognome, Nome
FROM Romanzo, Autore
WHERE R.idAutore = A.id AND A.luogoNascita=’Roma’
1 1.Romanzi con titolo che inizia per Tokyo
SELECT *
FROM Romanzo
WHERE titolo LIKE “Tokio%”
12.Romanzi con la parola Sposi nel titolo
SELECT *
FROM romanzo WHERE titolo LIKE ‘%Sposi%’
13.Romanzi con penultima lettera i nel titolo
SELECT *
FROM Romanzo
WHERE Titolo LIKE "%i_"
14.Romanzi il cui titolo termina per blues
SELECT *
FROM Romanzo
WHERE Titolo LIKE "%blues”
17.Romanzi di Alessandro Manzoni
SELECT *
FROM Romanzo, Autore
WHERE nome = “Alessandro” AND cognome = “Manzoni” AND Romanzo.idAutore =
Autore.id
18.Titoli dei romanzi scritti da Don DeLillo, escludendo eventuali duplicati
select DISTINCT titolo
From romanzo, autore
where romanzo.idAutore = autore.id AND autore.cognome = “DeLillo” AND
autore.nome = “Don”
19.Romanzi con il personaggio di Benjamin Malaussène
SELECT *
FROM Romanzo R, PersonaggioRomanzo PR, Personaggio P
WHERE R.id=PR.idRomanzo AND P.id=PR.idPersonaggio AND P.nome='Benjamin'
AND P.cognome='Malaussène'
20.Romanzi il cui autore non è presente nel database
SELECT *
FROM Romanzo R
WHERE R.idAutore IS NULL
GROUP BY HAVING
Trovare il numero di Autori
SELECT COUNT(*) AS 'Numero autori'
FROM Autore
Trovare il numero di Autori di nazionalità italiana
SELECT COUNT(*) AS 'Numero autori'
FROM Autore
WHERE nazionalità = 'italiana'
Per ogni autore il numero di romanzi scritti
SELECT idAutore, COUNT(*) AS 'numero di romanzi'
FROM Romanzo
GROUP BY idAutore, cognome, nome
HAVING 'numero di romanzi' > 1
Per ogni autore di nazionalità italiana, il cognome e nome dell’autore e il
numero di romanzi scritti, ma solo se l’autore ha scritto almeno 2 romanzi, con
ordinamento in base al numero di romanzi
SELECT cognome, nome, COUNT(*) AS 'numero di romanzi'
FROM Romanzo, Autore
WHERE Romanzo.idAutore = Autore.id AND nazionalità = 'italiana'
GROUP BY idAutore, cognome, nome
HAVING 'numero di romanzi' > 1
ORDER BY COUNT(*)
oppure
SELECT cognome, nome, COUNT(*) AS 'numero di romanzi'
FROM Romanzo, Autore
WHERE Romanzo.idAutore = Autore.id AND nazionalità = 'italiana'
GROUP BY idAutore, cognome, nome
HAVING 'numero di romanzi' > 1
ORDER BY 'numero di romanzi'
Dato lo schema relazionale attori (codAttore, nome, annoNascita, nazionalita); recita (codAttore, codFilm) film (codFilm, titolo, annoProduzione, nazionalità, regista, genere) proiezioni (codProiezione, codFilm, codSala, incasso, durata, dataProiezione) sale (codSala, posti, nome, citta) Scrivere le interrogazioni SQL che restituiscono le seguenti informazioni Per ogni città, il numero di sale
SELECT citta, COUNT() AS ‘numero sale’ FROM sale GROUP BY citta resultset sale .citta numero sale Per ogni città, il numero di sale con più di 60 posti SELECT citta, COUNT() AS ‘numero sale’ FROM sale WHERE posti > 60 GROUP BY citta Per ogni regista, il numero di film diretti dopo il 1990 SELECT regista, COUNT(*) AS ‘numero film’ FROM film WHERE annoProduzione > 1990 GROUP BY regista resultset film.regista numero film Per ogni regista, l’incasso totale di tutte le proiezioni dei suoi film SELECT regista, SUM(incasso) AS ‘IncassoTotale' FROM film, proiezioni WHERE film.codFilm=proiezioni.codFilm GROUP BY regista resultset film.regista IncassoTotale
Per ogni regista e per ogni attore, il numero di film del regista con l’attore SELECT F.regista, A.nome, COUNT() AS NumFilm FROM film F, attori A, recita R WHERE R.codAttore = A.codAttore AND R.codFilm = F.codFilm GROUP BY F.regista, A.nome resultset film.regista attori.nome numFilm Per ogni film prodotto dopo il 2000 , il codice, il titolo e l’incasso totale di tutte le sue proiezioni SELECT F.codFilm, F.titolo, SUM(P.incasso) AS ‘IncassoTotale’ FROM film F, proiezioni P WHERE F.codFilm=P.codFilm AND F.annoProduzione> 2000 GROUP BY F.codFilm, F.titolo resultset film.codFilm film.titolo Incasso totale Il numero di attori dei film in cui appaiono solo attori nati prima del 1970 SELECT film.titolo, COUNT() FROM film,attori,recita WHERE film.codFilm=recita.codFilm AND recita.codAttore=attori.codAttore AND annoNascita< 1970 GROUP BY film.titolo resultset film.titolo Numero attori Per ogni film di fantascienza, il titolo e l’incasso totale di tutte le sue proiezioni
SELECT film.titolo, SUM(proiezioni.incasso) AS ‘IncassoTotale’ FROM film,proiezioni WHERE film.codFilm=proiezioni.codFilm AND film.genere=’fantascienza’ GROUP BY film.titolo resultset film.titolo Incasso totale Per ogni film di fantascienza il titolo e l’incasso totale di tutte le sue proiezioni successive al 1 / 1 / 01 Per ogni film di fantascienza che non è mai stato proiettato prima del 1 / 1 / 01 il titolo e l’incasso totale di tutte le sue proiezioni Per ogni sala di Pisa, che nel mese di gennaio 2005 ha incassato più di 20000 €, il nome della sala e l’incasso totale (sempre del mese di gennaio 2005 )
SVOLGIMENTO SQL
Romanzo(id, idAutore*, titolo, annoPubblicazione, prezzo, scheda)
Autore(id, cognome, nome, nazionalità, dataNascita, luogoNascita, dataMorte,
luogoMorte)
Personaggio(id, nome, cognome, descrizione)
PersonaggioRomanzo(idPersonaggio, idRomanzo, descrizione)
storia.sql
-- phpMyAdmin SQL Dump -- version 5.1. -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0. -- Creato il: Dic 08, 2021 alle 17: -- Versione del server: 10.4.22-MariaDB -- Versione PHP: 7.3.
(2, 'Lara', 'Sole', 'Inglese', '1950-10-03', 'New York', '0000-00-00', ''), (3, 'Jova', 'Pino', 'Inglese', '1930-12-05', 'Londra', '2003-12-02', 'Boston'), (4, 'Valdo', 'Lori', 'Russo', '1971-12-08', 'Russia', NULL, NULL), (5, 'Ponyo', 'Lon', 'Russo', '1991-08-08', 'Russia', NULL, NULL), (6, 'Connie', 'Sand', 'Italiana', '1991-12-06', 'Roma', NULL, NULL), (7, 'Alessandro', 'Manzoni', 'italiana', '1785-03-07', 'Milano', '1873-05-22', 'Milano'), (8, 'Don', 'DeLillo', 'inglese', '1936-11-20', 'New York', NULL, NULL);
-- -- Struttura della tabella personaggio -- CREATE TABLE personaggio ( id int(1) NOT NULL, nome varchar(150) NOT NULL, cognome varchar(150) NOT NULL, descrizione varchar(150) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dump dei dati per la tabella personaggio -- INSERT INTO personaggio (id, nome, cognome, descrizione) VALUES (1, 'Chris', 'Columbus', 'coraggioso'), (2, 'Pina', 'Margarina', 'timida'), (3, 'Pino', 'Lolito', 'coraggioso'), (4, 'Jonathan', 'Natalino', 'spavaldo'), (5, 'Luana', 'Sera', 'timida'), (6, 'Alessandro', 'Nanco', 'comico'), (7, 'Benjamin', 'Malaussène', 'comico');
-- Struttura della tabella personaggioromanzo
CREATE TABLE personaggioromanzo ( idPersonaggio int(1) NOT NULL, idRomanzo int(1) NOT NULL, descrizione varchar(150) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dump dei dati per la tabella personaggioromanzo -- INSERT INTO personaggioromanzo (idPersonaggio, idRomanzo, descrizione) VALUES (2, 1, 'magico'), (3, 2, 'pauroso'), (1, 3, 'magico'), (7, 19, 'dramma');
-- -- Struttura della tabella romanzo -- CREATE TABLE romanzo ( id int(1) NOT NULL, idAutore int(1) NOT NULL, titolo varchar(150) NOT NULL, annoPubblicazione date NOT NULL, prezzo int(3) NOT NULL, scheda varchar(150) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dump dei dati per la tabella romanzo
