Esercizi Query

JOIN D

impiegato ION = .

.

codice

D

GROUP BY .

Codice

SELECT V V

v nome num

.

. .

, ,

vista

FROM v (

WHERE V =

num

. (num

SELECT MAX

vista

FROM

(

SELECT matricola

.

6 I

I I cognome

nome

. .

. , ,

impiegato

FROM I IN

matricola

WHERE NOT

I . direttore

SELECT

dipartimento

FROM

( IN

matricola

AND NOT

I .

SELECT responsabile

progetto

FROM

( P

SELECT P

7

. codice budget

P nome .

.

. , ,

FROM progetto P responsabile matricola

impiegato P

JOIN I.

I ON =

(

stipendio

WHERE I =

. stipendio

(12

SELECT MAX .

impiegato

FROM I2 matricola responsabile

PC

P2

JOIN I2

progetto ON = .

.

8 SELECT progetto

PP

.

. PP

FROM matricola

impiegato I impiegato

PP

JOIN I.

ON -

progetto

GROUP BY PP

. dipartimento

(DISTINCT

HAVING I 1

COUNT =

.

vista

CREATE

9 AS

VIEW

. stipendio

(I stipendio

dipartimento

SELECT AS

MAX

I .

. ,

FROM impiegato I

dipartimento

GROUP BY I . dipartimento

SELECT matricola I

I I

I cognome

nome ,

. . . .

,

, ,

stipendio

I .

FROM impiegato I dipartimento dipartimento

JOIN vista I AND

ON V

v =

. .

stipendio

stipendio

I V

= .

.

11 CREATE vista

VIEW AS

. 0)

ifnull)COUNT(matricola)

dipartimento

SELECT AS num

,

,

dipartimento D

FROM codice

RIGHT dipartimento

JOIN S

impiegato I ON I

= .

.

dipartimento

BY

GROVP (num

SELECT AVG

vista

FROM vista

10 CREATE VIEW

. AS COUNT)P codice)

matricola I

I

I As

SELECT cognome

nome num

. .

. . ,

,

,

impiegato

FROM I responsabile

matricola P

P ON I

Join progetto =

. .

matricola

GROUP I

BY .

matricola

SELECT cognome num

nome

, ,

,

vista

FROM (

WHERE num = (num

SELECT MAX

FROM vista

S

12 vista

CREATE VIEW AS

. C O

impiegato)

(COUNT

null AS

progetto if

SELECT num

, ,

FROM PP progetto

BY

GROUP (num) (num

(num)

SELECT Min

MAX

AVG ,

,

vista

FROM

Parziale

.

1 VIEW

CREATE vista AS

SUM(bottiglie)

SELECT AS

vino num

,

lotto

FROM BY

GROUP vino

SELECT vino num

,

vista

FROM (

WHERE num = (numb

SELECT MAX

vista

FROM

( annata

SELECT

.

2 V L

nome

. .

,

FROM V

Vino

JOIN Lotto L

L tipo

ON V vino

=

. .

(

annata

WHERE L =

. MINCannata

SELECT lotto

FROM

GROUP tipo

BY V . vind

COUNT)C

SELECT

.

3 VT nome .

. ,

vitigno

FROM VT Codice vitigno

C C

JOIN VT

ON

composizione =

. .

percentuale

WHERE C 2

L 0

. .

codice

GROUP BY VT .

Parziale VIEW

1 CREATE AS

Vista

. SELECT COUNT(ES esame)

S As

. *, num

.

studente

FROM S

JOIN matricola

esamiSostenuti studente

S

ES ES

ON =

. .

matricola

S

GROUP BY .

SELECT V #

.

FROM vista v (

WHERE V =

num

. (mum

SELECT MAX

vista

FROM

I VIEW

2

. CREATE Vista AS

(voto)

SELECT As

AVG

esame num

,

Sostenuti

FROM esami matrico

S

GROUP BY .

SELECT #

FROM vista (

WHERE =

num (numb

SELECT MIN

vista

FROM

( COUNT)esame

studente

SELECT DISTINCT

3

. ,

FROM Sostenuti

esami giornal

(studente

GROUP BY anno mese

, ,

,

(esame)

HAVING COUNT 1

>

Parziale 0

if null I (L

SELECT

I isbn)

scrittore

AL COUNT .

. . , ,

editore

FROM E codice editore

libro LON E L

JOIN =

. .

codice

E

BY

GROUP .

2

. vista

CREATE VIEW AS

(scrittore)

libro AS

SELECT COUNT num

,

autorilibra

FROM libro

GROUP BY

SELECT L isbn titolo

L v num

. . .

,

,

autoritibro L

FROM libro

JOIN isbn

Vista L V

ON

V =

. .

(

WHERE V num =

. (num

SELECT MAX

vista

FROM

( editore

scrittore

.

3 COUNT)L

SELECT AL ,

. .

autolibri

FROM Al

libro libro isbe

JOIN L

L AL

ON = .

.

GROUP scrittore

BY AL . editore)

(L

HAVING COUNT o

>

.

Parziale

.

1 CREATE vista

VIEW AS (progetto)

SELECT impiegato AS

COUNT num

,

partecipazione

FROM

GROUP impiegato

BY

SELECT impiegato num

,

vista

FROM (

WHERE num = (num)

SELECT MAX

vista

FROM

I COUNT(progetto

2 SELECT I

I cognome

nome

. . , . ,

FROM S

supervisione

JOIN impiegato matricola

impiegato

S I.

I ON =

.

.

GROUP matricola

BY I .

P

SELECT

3

. AVG) stipendio

progetto I

. .

,

partecipazione

FROM P

JOIN matricola

impiegato I impiegato I

P.

ON - .

P progetto

GROUP BY .

Parziale 0

(COUNTOS sin)

SELECT ifnull

.

1 description

S ,

, .

.

status

FROM S

OrderStatus status

JOIN

LEFT OS

S

OS ON sin = .

.

S

BY

GROUP sin

. OfFlusse

date

.

2 SELECT OS

V .*, .

FROM U

user

orderStatus

JOIN OS

V

ON

OS win user

=

. . (

date Offlusse

status

WHERE AND

3 OS

OS =

.

. (dateOfFlusse)

MIN) Elapsed

SELECT order Status

FROM status

WHERE OS 3

.

S

.

3 vista AS

CREATE VIEW SUM(product)

SELECT AS

oin num

,

order

FROM

GROUP BY Oin

(num)

SELECT AVG

vista

FROM

Parziale

SELECT

1 posto

descrizione COUNT)pre

S . ·

,

spettacolos

FROM prospettacolo

codice

JOIN ON

programmazione S =

pro .

data

JOIN data

prenotazione ON pre

pre pro =

data "

WHERE " 2006-12-15

=

pro · codice

BY

GROUP S . totale

0)

(SUM

SELECT costol

(C

2 ifnull AS

U .*,

. · ,

utente

FROM v utente

prenotazione P P

JOIN cf

U

ON = .

.

categoriePosti

JoiN posto posto

P CP

CP ON = .

.

Categoria codice

JOIN categoria

CON C

CP = .

.

ef

GROUP BY U .

totale DESC

ORDER BY descrizione postol

.

3 (P

SELECT C AVG

. , .

categoria

FROM C codice

categoria Posti

JOIN categoria

CP

ON

CP C = .

.

JoiN prenotazione posto posto

CP

ON pre

=

pre .

codice

GROUP C

BY .

Parziale code P

P

SELECT null

1

. P if

surname

name ,

.

. .

,

,

(COUNT code) 0)

(F AS num

. ,

police

FROM P

man code police

fine P F

JOIN F ON = man

. .

code

GROUP P

BY .

BY DESC

ORDER num

VIEW

CREATE Vista

.

2 AS 0)

(COUNT(code)

areaCode ifnull

date AS

SELECT num

, ,

,

fine

FROM date

areaCode

GROUP BY , O

(SUM)

(AVG

areaCode

SELECT ifmull ,

,

vista

FROM code

GROUP BY

.

3 VIEW

CREATE Vista AS 0)

ifnull)COUNT(code)

areaCode

SELECT As num

, ,

fine

FROM arealode

GROUP BY

areaCode

SELECT num

,

vista

FROM (

WHERE num = (num)

SELECT MAX

vista

FROM

(

Parziale (P

SELECT visital

1 .

COUNT

. FROM P

prescrizione farmaco

farmaco codice

JOIN P F

FON .

=

. "

nome"

F FO18

WHERE . (DV visita

SELECT

2

. COUNT .

dettaglio

Visita DV

FROM codice

JOIN controllo controllo C

DV

CON = .

.

"CO14"

WHERE C nome

.

3

. CREATE VIEW Vista AS

COUNT pazienti

medico DISTINCT

SELECT AS num

,

visita

FROM medico

BY

GROUP medico

SELECT num

vista

FROM (

WHERE =

num (num)

SELECT MAX

vista

FROM

S

CREATE

4

. VIEW vista AS

COUNT(DISTINCT medico

SELECT paziente AS num

,

visita

FROM paziente

BY

GROUP

SELECT paziente num

,

vista

FROM (

WHERE =

num (num)

SELECT MAX

vista

FROM

S

. VIEW

5 CREATE vista AS farmaco

COUNT(

SELECT visita AS num

,

FROM prescrizione

visita

GROUP BY (num)

SELECT AVG

vista

FROM VIEW vista

CREATE

6

. AS

controllo

visita C

SELECT AS

COUNT num

,

dettaglio Visita

FROM visita

GROUP BY (num)

SELECT AVG

vista

FROM

Parziale Vista

1

. CREATE AS

VIEW code)

(R

food

SELECT AS

COUNT

WFP

WEP Wine num

. .

,

.

,

food

FROM WFP

Wine pairing code

Join R

rating R rating

WFP

ON = .

.

description "PERFECT"

WHERE R =

. food

BY

GROUP WFP

WFP wine

. .

,

food

SELECT Wine num

,

,

Vista

FROM (

WHERE num = (num)

SELECT MAX

vista

FROM

( vieta

CREATE VIEW

2

. AS

COUNT

SELECT variety)

Wine AS

grape num

, variety

FROM wine grape

BY

GROUP wine

COUNT(wine

SELECT

Vista

FROM

WHERE 1

num = a)

SELECT

.

3 code code)

CWFP

(

if

null

W COUNT

W name

. , . .

, ,

FROM Wine W food Code

JOIN

LEFT Wise pairing ON

WFP FP.

=

W Wine

.

code

BY

GROUP W

.

Parziale VIEW

CREATE

1