none
PROVA SQL MO RRS feed

Tutte le risposte

  • tutto bene
    lunedì 20 luglio 2015 13:10
  • DW 

    /*
    Creare una vista che contenga, per ogni Docente:
    • Il numero medio degli iscritti agli insegnamenti dello stesso Corso di Laurea del Docente
    • Il numero medio degli iscritti agli insegnamenti di Corsi di Laurea diversi da quello del Docente
    Inserire il risultato nella vista:
    VIEW_ES_1.1 (DOCENTE, CORSODILAUREA,  media_iscritti_e, media_iscritti_d )
    */
    create view iscritti_e
    as
    select doc.CF_TITOLARE, d.COD_CDL, SUM(doc.NUM_ISCRITTI)/COUNT(doc.NUM_ISCRITTI) media_iscritti_e
    from DOCENZA doc
    join INSEGNAMENTO i on (doc.COD_INS=i.COD_INS)
    join DOCENTE d on (doc.CF_TITOLARE=d.CF and d.COD_CDL=i.COD_CDL)
    group by doc.CF_TITOLARE, d.COD_CDL

    create view iscritti_d
    as
    select doc.CF_TITOLARE, d.COD_CDL, SUM(doc.NUM_ISCRITTI)/COUNT(doc.NUM_ISCRITTI) media_iscritti_d
    from DOCENZA doc
    join INSEGNAMENTO i on (doc.COD_INS=i.COD_INS)
    join DOCENTE d on (doc.CF_TITOLARE=d.CF and d.COD_CDL<>i.COD_CDL)
    group by doc.CF_TITOLARE, d.COD_CDL

    select e.CF_TITOLARE, e.COD_CDL, e.media_iscritti_e, d.media_iscritti_d 
    from iscritti_e e
    full outer join iscritti_d d on (e.CF_TITOLARE=d.CF_TITOLARE)


    SELECT DO.CF, DO.COD_CDL,
    MEDIA_ISCRITTI_E=AVG(CASE WHEN DO.COD_CDL=I.COD_CDL THEN D.NUM_ISCRITTI END),
    MEDIA_ISCRITTI_D=AVG(CASE WHEN DO.COD_CDL<>I.COD_CDL THEN D.NUM_ISCRITTI END)
    FROM DOCENZA D, INSEGNAMENTO I, DOCENTE DO
    WHERE DO.CF=D.CF_TITOLARE
    AND D.COD_INS=I.COD_INS
    GROUP BY DO.CF, DO.COD_CDL


    /*
    Creare una vista che contenga, per ogni AA, ANNODICORSO e SEMESTRE, la media di
    NUM_ISCRITTI dei top 3 corsi (ordinati per NUM_ISCRITTI) che abbiano come docente titolare un
    docente con almeno due corsi nell’AA.
    Inserire il risultato nella vista:
    VIEW_ES_1.2 (AA, ANNODICORSO, SEMESTRE,  MEDIA_TOP_3Creare una vista che contenga, per ogni AA, ANNODICORSO e SEMESTRE, la media di
    NUM_ISCRITTI dei top 3 corsi (ordinati per NUM_ISCRITTI) che abbiano come docente titolare un
    docente con almeno due corsi nell’AA.
    Inserire il risultato nella vista:
    VIEW_ES_1.2 (AA, ANNODICORSO, SEMESTRE,  MEDIA_TOP_3
    */
    create view docenze_2_corsi
    as
    select doc.AA, i.ANNODICORSO, doc.SEMESTRE, doc.CF_TITOLARE, doc.NUM_ISCRITTI, doc.COD_INS
    from DOCENZA doc
    join DOCENTE d on(d.CF=doc.CF_TITOLARE)
    join INSEGNAMENTO i on (i.COD_INS=doc.COD_INS)
    where doc.CF_TITOLARE in (
    select doc1.CF_TITOLARE
    from DOCENZA doc1
    where doc1.AA=doc.AA
    group by doc1.CF_TITOLARE
    having COUNT(*) > 1
    )


    select doc.AA, doc.ANNODICORSO, doc.SEMESTRE, SUM(doc.NUM_ISCRITTI)/COUNT(doc.NUM_ISCRITTI) media_top_3
    from docenze_2_corsi doc
    where doc.COD_INS in (
    select top 3 doc2.COD_INS
    from docenze_2_corsi doc2
    where doc.AA=doc2.AA
    order by doc2.NUM_ISCRITTI desc
    )
    group by doc.AA, doc.ANNODICORSO, doc.SEMESTRE


    /*
    Dato il database DB_20150210 e considerando il sottoschema indicato, scrivere il trigger (o i trigger) secondo
    la sintassi SQLServer che a seguito di inserimento o modifica su DOCENZA mantengano il database
    consistente con i seguenti vincoli:
    1) In un AA, un DOCENTE deve essere titolare di almeno due insegnamenti per un totale di almeno 9 CFU
    */

    create trigger es_2_1B
    on DOCENZA
    for insert, update
    as 
    begin
    declare @CF varchar(10)
    declare @AA varchar(10)
    declare cursore cursor
    for
    select i.CF_TITOLARE, i.AA
    from inserted i
    open cursore
    fetch next from cursore into @CF, @AA
    while @@FETCH_STATUS=0
    begin
    if (select SUM(doc.CFU)
    from DOCENZA doc
    where doc.AA=@AA and doc.CF_TITOLARE=@CF) < 9
    or
    (
    select COUNT(*)
    from DOCENZA doc
    where doc.AA=@AA and doc.CF_TITOLARE=@CF) < 2
    begin
    raiserror('errore',16,1)
    rollback
    end

    fetch next from cursore into @CF, @AA
    end
    close cursore
    deallocate cursore
    end

    /*
    In un AA, il totale dei CFU di un DOCENTE non può crescere più del 3% rispetto a quello dell’ultimo AA
    in cui il DOCENTE ha tenuto degli insegnamenti
    (ad esempio, nel database DB_20150210, per il docente DOCA: per l’AA 2010-11 il vincolo non si applica in
    quanto è il primo AA in cui ha tenuto dei corsi; per il 2013-14 il controllo deve essere fatto rispetto al 2011-12,
    in quanto nel 2012-13 egli non ha tenuto insegnamenti)
    */


    create trigger es_2_2B
    on DOCENZA
    for insert, update
    as 
    begin
    declare @CF varchar(10)
    declare @AA varchar(10)
    declare @COD_INS varchar(10)
    declare @max_anno varchar(10)
    declare @tot_cfu_old int
    declare @tot_cfu_new int
    declare cursore cursor
    for
    select i.CF_TITOLARE, i.AA
    from inserted i
    open cursore

    fetch next from cursore into @CF, @AA
    while @@FETCH_STATUS=0
    begin

    set @max_anno = isnull((select MAX(doc.aa)
    from DOCENZA doc
    where doc.CF_TITOLARE=@CF
    and doc.AA<@AA
    ),0)

    set @tot_cfu_old = isnull((select SUM(doc.CFU)
    from DOCENZA doc
    where doc.CF_TITOLARE=@CF
    and doc.AA=@max_anno
    ),999)

    set @tot_cfu_new = (select SUM(doc.CFU)
    from DOCENZA doc
    where doc.CF_TITOLARE=@CF
    and doc.AA=@AA)

    if @tot_cfu_new > 1.03*@tot_cfu_old
    begin 
    raiserror('errore',16,1)
    rollback
    end

    fetch next from cursore into @CF, @AA, @COD_INS
    end

    close cursore
    deallocate cursore
    end

    lunedì 20 luglio 2015 16:05
  • FEB

    having > 1 join top desc

    max trigger

    /*
    Creare una vista che contenga, per ogni Docente:
    • Il numero medio degli iscritti agli insegnamenti dello stesso Corso di Laurea del Docente
    • Il numero medio degli iscritti agli insegnamenti di Corsi di Laurea diversi da quello del Docente
    Inserire il risultato nella vista:
    VIEW_ES_1.1 (DOCENTE, CORSODILAUREA,  media_iscritti_e, media_iscritti_d) 
    */ 

    create view media_e
    as
    select doc.CF_TITOLARE, d.COD_CDL, SUM(doc.NUM_ISCRITTI)/COUNT(doc.NUM_ISCRITTI) media_iscritti_e
    from DOCENZA doc
    join DOCENTE d on (doc.CF_TITOLARE=d.CF)
    join INSEGNAMENTO i on (i.COD_INS=doc.COD_INS and i.COD_CDL=d.COD_CDL)
    group by doc.CF_TITOLARE, d.COD_CDL

    create view media_d
    as
    select doc.CF_TITOLARE, d.COD_CDL, SUM(doc.NUM_ISCRITTI)/COUNT(doc.NUM_ISCRITTI) media_iscritti_d
    from DOCENZA doc
    join DOCENTE d on (doc.CF_TITOLARE=d.CF)
    join INSEGNAMENTO i on (i.COD_INS=doc.COD_INS and i.COD_CDL<>d.COD_CDL)
    group by doc.CF_TITOLARE, d.COD_CDL

    select *
    from media_e
    full outer join media_d on (media_e.CF_TITOLARE=media_d.CF_TITOLARE)


    /*
    Creare una vista che contenga, per ogni AA, ANNODICORSO e SEMESTRE, la media di
    NUM_ISCRITTI dei top 3 corsi (3 insegnamenti) (ordinati per NUM_ISCRITTI) che abbiano come docente titolare un
    docente con almeno due corsi nell’AA.
    Inserire il risultato nella vista:
    VIEW_ES_1.2 (AA, ANNODICORSO, SEMESTRE,  MEDIA_TOP_3)
    */

    create view docenze_ok
    as
    select doc1.AA, doc1.CF_TITOLARE, doc1.COD_INS, i.ANNODICORSO, doc1.SEMESTRE, doc1.NUM_ISCRITTI
    from DOCENZA doc1
    join INSEGNAMENTO i on (doc1.COD_INS=i.COD_INS)
    where doc1.CF_TITOLARE in (
    select doc2.CF_TITOLARE
    from DOCENZA doc2
    where doc2.AA=doc1.AA
    group by doc2.CF_TITOLARE
    having COUNT(*) > 1
    )

    select doc1.AA, doc1.ANNODICORSO, doc1.SEMESTRE, SUM(doc1.NUM_ISCRITTI)/COUNT(doc1.NUM_ISCRITTI) media_top_3
    from docenze_ok doc1
    where doc1.COD_INS in (
    select top 3 doc2.COD_INS
    from docenze_ok doc2
    where doc2.AA=doc1.AA
    group by doc2.COD_INS, doc2.NUM_ISCRITTI
    order by doc2.NUM_ISCRITTI desc
    )
    group by doc1.AA, doc1.ANNODICORSO, doc1.SEMESTRE


    /*
    a seguito di inserimento o modifica su DOCENZA mantengano il database
    consistente con i seguenti vincoli:
    1) In un AA, un DOCENTE deve essere titolare di almeno due insegnamenti per un totale di almeno 9 CFU
    */

    create trigger es2_1
    on DOCENZA
    for Insert, Update
    as
    begin
    declare @AA varchar(10)
    declare @CF varchar(10)
    declare cursore cursor
    for
    select doc1.AA, doc1.CF_TITOLARE 
    from inserted doc1
    open cursore
    fetch next from cursore into @AA, @CF
    while @@FETCH_STATUS=0
    begin
    if (select COUNT(*) from DOCENZA doc where doc.AA=@AA and doc.CF_TITOLARE=@CF) < 2
    or (select SUM(doc.CFU) from DOCENZA doc where doc.AA=@AA and doc.CF_TITOLARE=@CF) < 9
    begin 
    raiserror('ERRORE',16,1)
    rollback
    end
    fetch next from cursore into @AA, @CF
    end
    close cursore
    deallocate cursore
    end

    /*
    2) In un AA, il totale dei CFU di un DOCENTE non può crescere più del 3% rispetto a quello dell’ultimo AA
    in cui il DOCENTE ha tenuto degli insegnamenti
    (ad esempio, nel database DB_20150210, per il docente DOCA: per l’AA 2010-11 il vincolo non si applica in
    quanto è il primo AA in cui ha tenuto dei corsi; per il 2013-14 il controllo deve essere fatto rispetto al 2011-12,
    in quanto nel 2012-13 egli non ha tenuto insegnamenti)
    */
    create trigger es2_2
    on DOCENZA
    for Insert, Update
    as
    begin
    declare @AA varchar(10)
    declare @CF varchar(10)
    declare @TOT_CFU_old int
    declare @TOT_CFU_new int
    declare @MAX_ANNO varchar(10)
    declare cursore cursor
    for 
    select doc1.AA, doc1.CF_TITOLARE
    from inserted doc1
    open cursore
    fetch next from cursore into @AA, @CF
    while @@FETCH_STATUS=0
    begin
    set @TOT_CFU_new = (
    select SUM(doc1.CFU)
    from DOCENZA doc1
    where doc1.CF_TITOLARE=@CF
    and doc1.AA=@AA)
    set @MAX_ANNO = isnull((
    select MAX(doc1.AA)
    from DOCENZA doc1
    where doc1.CF_TITOLARE=@CF
    and doc1.AA<@AA
    ), 0)
    set @TOT_CFU_old = isnull((
    select SUM(doc1.CFU)
    from DOCENZA doc1
    where doc1.CF_TITOLARE=@CF
    and doc1.AA=@MAX_ANNO), 999)
    if @TOT_CFU_new > 1.03*@TOT_CFU_old
    begin
    raiserror('ERROR',16,1)
    rollback
    end
    fetch next from cursore into @AA, @CF
    end
    close cursore
    deallocate cursore
    end

    lunedì 20 luglio 2015 16:06
  • GEN 

    top 5 order count 4 SIM

    trigger insert delete bonus

    /*
    Creare una vista che contenga, per ogni UTENTE e ogni sua SIM:
    • il numero di chiamate effettuate verso sim dello stesso operatore e la loro durata media
    • il numero di chiamate effettuate verso sim di operatore diverso e la loro durata media
    Inserire il risultato nella vista:
    VIEW_ES_1.1 (UTENTE, SIM, num_op_e, durata_op_e, num_op_d, durata_op_d)
    */
    create view op_e
    as
    select s1.UTENTE, s1.SIM, COUNT(*) num_op_e, SUM(t.DURATA)/COUNT(t.DURATA) durata_op_e
    from telefonata t
    join SIM s1 on ( t.DA_CHIAMANTE= s1.SIM)
    join SIM s2 on (t.A_CHIAMATA=s2.SIM)
    join TARIFFA tar1 on (s1.TARIFFA=tar1.TARIFFA)
    join TARIFFA tar2 on (s2.TARIFFA=tar2.TARIFFA and tar1.OPERATORE=tar2.OPERATORE)
    group by s1.UTENTE, s1.SIM

    create view op_d
    as
    select s1.UTENTE, s1.SIM, COUNT(*) num_op_d, SUM(t.DURATA)/COUNT(t.DURATA) durata_op_d
    from telefonata t
    join SIM s1 on ( t.DA_CHIAMANTE= s1.SIM)
    join SIM s2 on (t.A_CHIAMATA=s2.SIM)
    join TARIFFA tar1 on (s1.TARIFFA=tar1.TARIFFA)
    join TARIFFA tar2 on (s2.TARIFFA=tar2.TARIFFA and tar1.OPERATORE<>tar2.OPERATORE)
    group by s1.UTENTE, s1.SIM

    select ISNULL(op_e.UTENTE,op_d.UTENTE) UTENTE, ISNULL(op_e.SIM,op_d.SIM) SIM, 
    ISNULL(num_op_e,0) NUM_OP_E, ISNULL(durata_op_e,0) DURATA_OP_E, 
    ISNULL(num_op_d,0) NUM_OP_D, ISNULL(durata_op_d,0) DURATA_OP_D
    from op_e
    full outer join op_d on (op_e.SIM=op_d.SIM)


    select UTENTE, num_op_uguale, durata_op_uguale, num_op_dicerso, durata_op_diverso
    from
    (
    select s1.UTENTE, count(*) num_op_uguale, avg(durata) durata_op_uguale
    from TELEFONATA t
    join SIM s1 on (t.DA_CHIAMANTE = s1.SIM)
    join TARIFFA t1 on (t1.TARIFFA = s1.TARIFFA)
    join sim s2 on (t.A_CHIAMATA = s2.SIM)
    join TARIFFA t2 on (t2.TARIFFA = s2.TARIFFA and t2.OPERATORE = t1.OPERATORE)
    group by s1.UTENTE
    ) a
    full outer join
    (select s1.UTENTE, count(*) num_op_dicerso, avg(durata) durata_op_diverso
    from TELEFONATA t
    join SIM s1 on (t.DA_CHIAMANTE = s1.SIM)
    join TARIFFA t1 on (t1.TARIFFA = s1.TARIFFA)
    join sim s2 on (t.A_CHIAMATA = s2.SIM)
    join TARIFFA t2 on (t2.TARIFFA = s2.TARIFFA and t2.OPERATORE <> t1.OPERATORE)
    group by s1.UTENTE)b
    on (a.UTENTE=b.UTENTE)

    /*
    Creare una vista che contenga, per ogni UTENTE, la durata media delle chiamate effettuate verso i 5
    utenti chiamati di più dall’UTENTE stesso (gli utenti chiamati di più devono essere individuati
    considerando il numero di chiamate effettuate).
    Inserire il risultato nella vista:
    VIEW_ES_1.2 (UTENTE, DURATA_MEDIA_TOP_5)
    */

    select sim1.UTENTE, SUM(t1.DURATA)/COUNT(t1.DURATA) DURATA_MEDIA_TOP_5
    from TELEFONATA t1
    join SIM sim1 on (t1.DA_CHIAMANTE=sim1.SIM)
    join SIM sim2 on (t1.A_CHIAMATA=sim2.SIM)
    where sim2.UTENTE in (
    select top 5 sim2b.UTENTE
    from TELEFONATA t2
    join SIM sim2b on (t2.A_CHIAMATA=sim2b.SIM) 
    join SIM sim1b on (t2.DA_CHIAMANTE=sim1b.SIM and sim1.UTENTE=sim1b.UTENTE)
    group by sim2b.UTENTE
    order by COUNT(t2.DURATA) desc
    )
    group by sim1.UTENTE


    /*
    Dato il database DB_20150113 e considerando il sottoschema indicato, scrivere il trigger (o i trigger) secondo
    la sintassi SQLServer per mantenere aggiornato il dato derivato BONUS in caso d’inserimento, modifica o
    cancellazione su TELEFONATA, secondo la seguente formula:

    BONUS =
    [durata complessiva di chiamate effettuate dalla SIM verso SIM di operatori differenti dal proprio] +
    2 × [durata complessiva di chiamate effettuate dalla SIM verso SIM di operatori uguali al proprio] +
    (1/2) × [durata complessiva di chiamate ricevute dalla SIM]
    Nota: Il BONUS diminuisce in caso di decremento del valore DURATA e in caso di cancellazione.
    */

    create trigger es3_1B 
    on TELEFONATA
    for Insert, Update, Delete
    as
    begin
    declare @sim1 varchar(10)
    declare @sim2 varchar(10)
    declare @durata int
    declare @op1 varchar(10)
    declare @op2 varchar(10)
    declare cursore1 cursor
    for 
    select i.DA_CHIAMANTE, i.A_CHIAMATA, i.DURATA, t1.OPERATORE, t2.OPERATORE
    from inserted i
    join sim sim1 on (i.DA_CHIAMANTE=sim1.SIM)
    join SIM sim2 on (i.A_CHIAMATA=sim2.SIM)
    join TARIFFA t1 on (t1.TARIFFA=sim1.TARIFFA)
    join TARIFFA t2 on (t2.TARIFFA=sim2.TARIFFA)
    open cursore1
    fetch next from cursore1 into @sim1, @sim2, @durata, @op1, @op2
    while @@FETCH_STATUS=0
    begin
    if @op1=@op2
    begin
    update SIM
    set BONUS= BONUS + 2*@durata
    where SIM=@sim1
    end

    if @op1<>@op2
    begin
    update SIM
    set BONUS= BONUS + @durata
    where SIM=@sim1
    end

    update SIM
    set BONUS= BONUS + 0.5*@durata
    where SIM=@sim2
    fetch next from cursore1 into @sim1, @sim2, @durata, @op1, @op2
    end
    close cursore1
    deallocate cursore1

    declare cursore2 cursor
    for 
    select i.DA_CHIAMANTE, i.A_CHIAMATA, i.DURATA, t1.OPERATORE, t2.OPERATORE
    from deleted i
    join sim sim1 on (i.DA_CHIAMANTE=sim1.SIM)
    join SIM sim2 on (i.A_CHIAMATA=sim2.SIM)
    join TARIFFA t1 on (t1.TARIFFA=sim1.TARIFFA)
    join TARIFFA t2 on (t2.TARIFFA=sim2.TARIFFA)
    open cursore2
    fetch next from cursore2 into @sim1, @sim2, @durata, @op1, @op2
    while @@FETCH_STATUS=0
    begin
    if @op1=@op2
    begin
    update SIM
    set BONUS= BONUS - 2*@durata
    where SIM=@sim1
    end

    if @op1<>@op2
    begin
    update SIM
    set BONUS= BONUS - @durata
    where SIM=@sim1
    end

    update SIM
    set BONUS= BONUS - 0.5*@durata
    where SIM=@sim2
    fetch next from cursore2 into @sim1, @sim2, @durata, @op1, @op2
    end
    close cursore2
    deallocate cursore2

    end

    lunedì 20 luglio 2015 16:09
  • Ciao, prima di andare avanti con la discussione  puoi spiegare quale è la domanda? Cosa vuoi ottenere?

    Saluti
    Nino


    ...esistono i motori di ricerca, facci un salto e troverai molte delle risposte che ti darò io.

    lunedì 20 luglio 2015 18:12
    Moderatore