1 / 49

Basi di Dati e Sistemi Informativi

Basi di Dati e Sistemi Informativi. Esercitazione : Il Linguaggio SQL Home page del corso : http:// www.cs.unibo.it /~ difelice / dbsi /. Esercizio 1. Scrivere il codice SQL dello schema. ARTICOLI. Autore deve essere sempre definito .

evangeline-marks
Download Presentation

Basi di Dati e Sistemi Informativi

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Basi di Dati e SistemiInformativi Esercitazione: Il Linguaggio SQL Home page del corso: http://www.cs.unibo.it/~difelice/dbsi/

  2. Esercizio1 • Scrivereilcodice SQL dello schema ARTICOLI • Autoredeveesseresempredefinito. • Conferenza e’ unastringa di lunghezza max 8. • Formato e’ unastringa di lunghezza 3. • Il numero di paginedeveesseresempremaggiore di 0, nelcaso di omissionesiassume pari ad 1.

  3. Esercizio 1 CREATE TABLE ARTICOLI ( CODICE SMALLINT PRIMARY KEY, AUTORE VARCHAR(20) NOT NULL, CONFERENZA CHAR(8), FORMATO CHAR(3), ANNO DATE, PAGINE SMALLINT DEFAULT 1 CHECK (PAGINE>1) );

  4. Esercizio1 • Scrivereilcodice SQL dello schema CONFERENZE • Vincolo di integrita’: ARTICOLO.{Conferenza, Data}  CONFERENZA.{Nome, Data} • Rimuovendounaconferenza da CONFERENZE, vengonorimossianchegliarticolicorrispondenti

  5. Esercizio 1 CREATE TABLE CONFERENZE ( NOME VARCHAR(8), DATA DATE, ORGANIZZATORE VARCHAR(20), NUMPARTECIPANTI INTEGER CHECK (NUMPARTECIPANTI>0), PRIMARY KEY(NOME,DATA) );

  6. Esercizio 1 CREATE TABLE ARTICOLI ( CODICE SMALLINT PRIMARY KEY, AUTORE VARCHAR(20) NOT NULL, CONFERENZA CHAR(8), FORMATO CHAR(3), DATA DATE, PAGINE SMALLINT DEFAULT 1 CHECK (PAGINE>1) FOREIGN KEY (CONFERENZA,DATA) REFERENCES CONFERENZE(NOME, DATA) ON DELETE CASCADE );

  7. Esercizio1 • Scrivereilcodice SQL dello schema PARTECIPANTE • Vincolo di integrita’: PARTECIPANTE.{NomeConf, DataConf}  CONFERENZA.{Nome, Data} • Ogni aggiornamento di {Nome, Data} nellarelazione CONFERENZA vienepropagatoancheallarelazione PARTECIPANTE.

  8. Esercizio 1 CREATE TABLE PARTECIPANTE ( NOMECONF CHAR(8), DATACONF DATE, NOME VARCHAR(20), COGNOME VARCHAR(30), RUOLO CHARACTER(3), PRIMARY KEY (NOMECONF,DATACONF, NOME, COGNOME) FOREIGN KEY (NOMECONF,DATACONF) REFERENCES CONFERENZE(NOME, DATA) ON UPDATE CASCADE );

  9. Esercizio 2 Datoilseguente schema: Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Gara(CodiceGara, Disciplina, Data, CodiceVincitore) Partecipazione(CodiceGara, CodiceAtleta) Societa’(Nome, Sede, Anno, NumeroTrofei) Scrivere la query SQLchedetermina: 1. Le righedellatabella ATLETA chesiriferiscono ad atletiil cui nomeinizia per “M” oppure non e’ specificato.

  10. Esercizio 2 Datoilseguente schema: Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Gara(CodiceGara, Disciplina, Data, CodiceVincitore) Partecipazione(CodiceGara, CodiceAtleta) Societa’(Nome, Sede, Anno, NumeroTrofei) Scrivere la query SQLchedetermina: 2. Tuttiicodicidellegare di Atletica cui partecipanoatletidellasocieta’ “Borgorosso”.

  11. Esercizio 2 Datoilseguente schema: Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Gara(CodiceGara, Disciplina, Data, CodiceVincitore) Partecipazione(CodiceGara, CodiceAtleta) Societa’(Nome, Sede, Anno, NumeroTrofei) Scrivere la query SQLchedetermina: 3. Tuttiinomi/cognomidegliatletichepartecipano ad almeno due gare.

  12. Esercizio 2 Datoilseguente schema: Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Gara(CodiceGara, Disciplina, Data, CodiceVincitore) Partecipazione(CodiceGara, CodiceAtleta) Societa’(Nome, Sede, Anno, NumeroTrofei) Scrivere la query SQLchedetermina: 4. Il numerototale di Discipline cui ha partecipatol’atleta 234 nellaGaraidentificata dal codice 12.

  13. Esercizio 2 Datoilseguente schema: Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Gara(CodiceGara, Disciplina, Data, CodiceVincitore) Partecipazione(CodiceGara, CodiceAtleta) Societa’(Nome, Sede, Anno, NumeroTrofei) Scrivere la query SQLchedetermina: 5. Per ognisocieta’, ilnumerototale di garevinte.

  14. Esercizio 2 Datoilseguente schema: Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Gara(CodiceGara, Disciplina, Data, CodiceVincitore) Partecipazione(CodiceGara, CodiceAtleta) Societa’(Nome, Sede, Anno, NumeroTrofei, Presidente) Scrivere la query SQLchedetermina: 6. Il nomedeipresidenti di societa’ chehannovintopiu’ di 4 garesvolte in data 10/11/2012.

  15. Esercizio 3 Datoilseguente schema: Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Allenatore(Codice, Nome, Cognome, Profilo, Societa’) Preparazione(CodiceAtleta, CodiceAllenatore) Societa’(Nome, Sede, Anno, NumeroTrofei, Presidente) Sportivi(Nome, Cognome, Societa’) Scrivere la query SQLche: 1. Popola la tabellaSportivi(Nome, Cognome, Societa’) come insieme di tuttigliatletiedallenatoripresentinello schema.

  16. Esercizio 3 Datoilseguente schema: Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Allenatore(Codice, Nome, Cognome, Profilo, Societa’) Preparazione(CodiceAtleta, CodiceAllenatore) Societa’(Nome, Sede, Anno, NumeroTrofei, Presidente) Scrivere la query SQLche: 2. AggiornaidatidegliAtletinati in data 10/11/2012, settandoilnuovovaloredellasocieta’ a “Polisportiva Rossi”.

  17. Esercizio 3 Datoilseguente schema: Atleta(Codice, Nome, Cognome, DataNascita, Societa’) Allenatore(Codice, Nome, Cognome, Profilo, Societa’) Preparazione(CodiceAtleta, CodiceAllenatore) Societa’(Nome, Sede, Anno, NumeroTrofei, Presidente) Scrivere la query SQLche: 3. Rimuovetuttigliallenatori la cui societa’ di appartenenza non e’ specificata.

  18. Esercizio 4 Datoilseguente schema: Persona(Nome, DataNascita, CittaNascita, CodiceFiscale) Discendenza(Figlio, Padre) Matrimonio (Marito, Moglie, Citta’, Data) Scrivere la query SQLchedetermina: 1. Il numero di personenate a Bologna nell’anno 1985.

  19. Esercizio 4 Datoilseguente schema: Persona(Nome, AnnoNascita, CittaNascita, CodiceFiscale) Discendenza(Figlio, Padre) Matrimonio (Marito, Moglie, Citta’, Anno) Scrivere la query SQLchedetermina: 2. Il totale di matrimonisvoltiogni anno a Bologna.

  20. Esercizio 4 Datoilseguente schema: Persona(Nome, AnnoNascita, CittaNascita, CodiceFiscale) Discendenza(Figlio, Padre) Matrimonio (Marito, Moglie, Citta’, Anno) Scrivere la query SQLchedetermina: 3. Il numero di personechesichiamano Michele e non hannofigli.

  21. Esercizio5 Datoilseguente schema: FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere) PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso, DataProiezione) SALE (CodSala, Posti, Nome, Città) Scrivere la query SQLchedetermina: 1. Per ognicitta’, ilnumero di sale con piu’ di 60 posti.

  22. Esercizio5 Datoilseguente schema: FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere) PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso, DataProiezione) SALE (CodSala, Posti, Nome, Città) Scrivere la query SQLchedetermina: 2. Il numerototaledeipostidei cinema di Bologna.

  23. Esercizio5 Datoilseguente schema: FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere) PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso, DataProiezione) SALE (CodSala, Posti, Nome, Città) Scrivere la query SQLchedetermina: 3. Per ogniregista, la sommatotaledegliincassitotalizzatidaisuoi film.

  24. Esercizio5 Datoilseguente schema: FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere) PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso, DataProiezione) SALE (CodSala, Posti, Nome, Città) Scrivere la query SQLchedetermina: 4. Per ognicitta’, gliincassi di film Italianiproietattati in data 10/11/2012.

  25. Esercizio5 Datoilseguente schema: AEROPORTO(Citta’, Nazione, NumPiste) VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo, OraPartenza, OraArrivo) AEREO(TipoAereo, NumPasseggeri, QtaMerci) Scrivere la query SQLchedetermina: 1. Le citta’ con un aeroporto di cui non e’ notoilnumero di piste.

  26. Esercizio 5 Datoilseguente schema: AEROPORTO(Citta’, Nazione, NumPiste) VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo, OraPartenza, OraArrivo) AEREO(TipoAereo, NumPasseggeri, QtaMerci) Scrivere la query SQLchedetermina: 2. Le nazioni da cui parte edarrivailvolo con codice AZ1343.

  27. Esercizio5 Datoilseguente schema: AEROPORTO(Citta’, Nazione, NumPiste) VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo, OraPartenza, OraArrivo) AEREO(TipoAereo, NumPasseggeri, QtaMerci) Scrivere la query SQLchedetermina: 3. I tipi di aereousatineivolichepartono da Torino.

  28. Esercizio5 Datoilseguente schema: AEROPORTO(Citta’, Nazione, NumPiste) VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo, OraPartenza, OraArrivo) AEREO(TipoAereo, NumPasseggeri, QtaMerci) Scrivere la query SQLchedetermina: 4. Il numero di voliinternazionalichepartono da Napoli.

  29. Esercizio5 Datoilseguente schema: AEROPORTO(Citta’, Nazione, NumPiste) VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo, OraPartenza, OraArrivo) AEREO(TipoAereo, NumPasseggeri, QtaMerci) Scrivere la query SQLchedetermina: 5. La quantita’ totale di merci trasportata da aereichepartonoognigiovedi’ da Napoli e diretti in aeroportiitaliani.

  30. Esercizio5 Datoilseguente schema: AEROPORTO(Citta’, Nazione, NumPiste) VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo, OraPartenza, OraArrivo) AEREO(TipoAereo, NumPasseggeri, QtaMerci) Scrivere la query SQLchedetermina: 6. Le citta’ francesi da cui partonopiu’ di ventivoliallasettimanadiretti per l’Italia.

  31. Esercizio5 Datoilseguente schema: AEROPORTO(Citta’, Nazione, NumPiste) VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo, OraPartenza, OraArrivo) AEREO(TipoAereo, NumPasseggeri, QtaMerci) Scrivere la query SQLchedetermina: 6. Le citta’ servitedall’aereo con numerominimo di passeggeri.

  32. Esercizio5 Datoilseguente schema: AEROPORTO(Citta’, Nazione, NumPiste) VOLO(IdVolo, GiornoSett, CittaPart, CittaArr, TipoAereo, OraPartenza, OraArrivo) AEREO(TipoAereo, NumPasseggeri, QtaMerci) Scrivere la query SQLchedetermina: 7. Il massimonumero di passeggerichepossonoarrivaredallaFrancia in Italia ilgiovedi’.

  33. Esercizio6 Datoilseguente schema: NEGOZI(IdNegozio, Nome, Citta) PRODOTTI(CodProdotto, NomeProdotto, Marca) LISTINO(Negozio, CodProdotto, Prezzo) FORNITORE(Codice, Nome, Cognome, Sede) RIFORNIMENTO(CodiceFornitore,Negozio) Scrivere la query SQLchedetermina: 1. L’iddeinegozichevendonoilprodotto con codice 123 ad un prezzosuperiorealla media (calcolata sui listini di tuttiinegozi).

  34. Esercizio6 Datoilseguente schema: NEGOZI(IdNegozio, Nome, Citta) PRODOTTI(CodProdotto, NomeProdotto, Marca) LISTINO(Negozio, CodProdotto, Prezzo) FORNITORE(Codice, Nome, Cognome, Sede) RIFORNIMENTO(CodiceFornitore,Negozio) Scrivere la query SQLchedetermina: 2. Il codicedeiprodottivenduti in una sola citta’.

  35. Esercizio6 Datoilseguente schema: NEGOZI(IdNegozio, Nome, Citta) PRODOTTI(CodProdotto, NomeProdotto, Marca) LISTINO(Negozio, CodProdotto, Prezzo) FORNITORE(Codice, Nome, Cognome, Sede) RIFORNIMENTO(CodiceFornitore,Negozio) Scrivere la query SQLchedetermina: 3. La citta’ in cui vienevenduto al prezzo + basso ilprodotto con codice 123.

  36. Esercizio6 Datoilseguente schema: NEGOZI(IdNegozio, Nome, Citta) PRODOTTI(CodProdotto, NomeProdotto, Marca) LISTINO(Negozio, CodProdotto, Prezzo) FORNITORE(Codice, Nome, Cognome, Sede) RIFORNIMENTO(CodiceFornitore,Negozio) Scrivere la query SQLchedetermina: 4. Nome/Cognomedeifornitoriche NON rifornisconoalcunnegozio di Bologna.

  37. Esercizio 7 Datoilseguente schema: STADIO(Nome,Citta, Capienza) INCONTRO(NomeStadio, Data, Ora, Squadra1, Squadra2) NAZIONALE(Nazione, Continente, Livello) Scrivere la query SQLchedetermina: 1. La citta/le citta in cui sitrova lo stadio in cui la squadrainglesegiocailmaggiornumero di partite.

  38. Esercizio 7 Datoilseguente schema: STADIO(Nome,Citta, Capienza) INCONTRO(NomeStadio, Data, Ora, Squadra1, Squadra2) NAZIONALE(Nazione, Continente, Livello) Scrivere la query SQLchedetermina: 2. I nomideglistadi in cui non giocanessunasquadraeuropea.

  39. Esercizio 7 Datoilseguente schema: STADIO(Nome,Citta, Capienza) INCONTRO(NomeStadio, Data, Ora, Squadra1, Squadra2) NAZIONALE(Nazione, Continente, Livello) Scrivere la query SQLchedetermina: 3. La capienzacomplessivadeglistadi in cui giocaunanazioneeuropea.

  40. Esercizio 7 Datoilseguente schema: STADIO(Nome,Citta, Capienza) INCONTRO(NomeStadio, Data, Ora, Squadra1, Squadra2) NAZIONALE(Nazione, Continente, Livello) Scrivere la query SQLchedetermina: 4. I nomidellesquadrecheincontrano solo squadredellostessolivello.

  41. Esercizio 8 • Datoilseguente schema: • PROFESSORE(Codice, Nome, Cognome, Dipartimento, Qualifica, Stipendio) • CORSO(CodiceCorso, CodiceProf, OreCorso) • 0. Costruireilcodice SQL dello schema, definendoivincoli di chiave di ciascunatabella e glieventualivincoli di integrita’ inter-relazionaliesistenti. • Definire un dominio per CodiceCorso come: stringa, lunghezza 6 caratteri, la stringadeveiniziare per “C” e terminare per “00”.

  42. Esercizio 8 • Datoilseguente schema: • PROFESSORE(Codice, Nome, Cognome, Dipartimento, Qualifica, Stipendio) • CORSO(CodiceCorso, CodiceProf, OreCorso) • 0. Costruireilcodice SQL dello schema, definendoivincoli di chiave di ciascunatabella e glieventualivincoli di integrita’ inter-relazionaliesistenti. • Ogni aggiornamento del CodiceCorsodellatabella CORSO deveesserepropagatoancheall’attributoCodicedellatabella PROFESSORE.

  43. Esercizio 8 Datoilseguente schema: PROFESSORE(Codice, Nome, Cognome, Dipartimento, Qualifica, Stipendio) CORSO(CodiceCorso, CodiceProf, OreCorso) Definireilseguentevincolosullo schema: 1. Ognidipartimentodeveaverealmeno 30 professori, e lo stipendiomedio in ognidipartimentodeveessereinferiore a 25000 euro.

  44. Esercizio 8 Datoilseguente schema: PROFESSORE(Codice, Nome, Cognome, Dipartimento, Qualifica, Stipendio) CORSO(CodiceCorso, CodiceProf, OreCorso) Definireilseguentevincolosullo schema: 2. I professoriordinaridovrebberoinsegnare per almeno 120 ore, mentreiricercatori non dovrebberosuperare le 60 ore di lezione.

  45. Esercizio 8 Datoilseguente schema: PROFESSORE(Codice, Nome, Cognome, Dipartimento, Qualifica, Stipendio) CORSO(CodiceCorso, CodiceProf, OreCorso) Definireilseguentevincolosullo schema: 3. Ognivoltachesirimuoveunarigadallatabella PROFESSORI, se iltotaledeiProfessoridiventaminore di 20 per ildipartimento di Informatica,devonoessererimossituttii CORSI tenuti da professori di Informatica.

  46. Esercizio 8 Datoilseguente schema: PROFESSORE(Codice, Nome, Cognome, Dipartimento, Qualifica, Stipendio) CORSO(CodiceCorso, CodiceProf, OreCorso) Definireilseguentevincolosullo schema: 4. Definireuna vista DIPARTIMENTIARISCHIO(Dipartimento, MonteOre), definita come l’insiemedeidipartimenti in cui iltotaledelle ore di lezionesvolte da docenti di queldipartimentosiainferiore a 400.

  47. Esercizio 8 Datoilseguente schema: ATLETA(Nome, Nazione, Eta’) PARTECIPAZIONE(NomeAtleta, NomeGara, Piazzamento) GARA(Nome, Nazione) Scrivere la query SQLchedetermina: 1. Il nomedegliatletichehannogareggiato solo nellaproprianazione.

  48. Esercizio 8 Datoilseguente schema: ATLETA(Nome, Nazione, Eta’) PARTECIPAZIONE(NomeAtleta, NomeGara, Piazzamento) GARA(Nome, Nazione) Scrivere la query SQLchedetermina: 2. Il nomedegliatleticheprovengono da unanazione in cui non sisvolgealcunagara.

  49. Esercizio 8 Datoilseguente schema: ATLETA(Nome, Nazione, Eta’) PARTECIPAZIONE(NomeAtleta, NomeGara, Piazzamento) GARA(Nome, Nazione) Scrivere la query SQLchedetermina: 3. Il nomedegliatletichehannopreso parte a tutte le garesvolte in Francia.

More Related