ftp.disi.unige.it/2015-02-11.ftp.disi.unige.it.tar/ftp.disi.unige.it/pub/.person/MesitiM/teach/sol5.txt

Text File | 2003-04-14 | 4KB | 123 lines

INSERT INTO PROFESSORI VALUES (38, 'PRINI', 'GIANFRANCO', 50000)

INSERT INTO PROFESSORI VALUES (39, 'BANDINI', 'STEFANIA', DEFAULT)
/* stipendio ammatte anche valori nulli
INSERT INTO PROFESSORI VALUES (39, 'BANDINI', 'STEFANIA', NULL)
*/
INSERT INTO PROFESSORI VALUES (40, 'ROSTI', NULL, DEFAULT)
/** non va a buon fine perchΘ il nome non pu≤ essere null **/
--2
INSERT INTO LAUREATI
SELECT MATRICOLA, LAUREA, PROFESSORI.COGNOME, CORSIDILAUREA.DENOMINAZIONE
FROM STUDENTI JOIN PROFESSORI ON RELATORE=PROFESSORI.ID JOIN CORSIDILAUREA ON
CORSIDILAUREA.ID=CORSODILAUREA WHERE LAUREA IS NOT NULL
--3
DELETE FROM PIANIDISTUDIO
WHERE STUDENTE IN (SELECT MATRICOLAS FROM LAUREATI)
--4
UPDATE CORSI
SET DENOMINAZIONE='Basi di Dati 1 - Nuova Laurea'
WHERE DENOMINAZIONE = 'Basi di Dati 1' AND CORSODILAUREA= (SELECT ID FROM CORSIDILAUREA
WHERE DENOMINAZIONE = 'INFORMATICA')
--5
UPDATE PROFESSORI
SET STIPENDIO = STIPENDIO + 5000
WHERE STIPENDIO < 15000


/** VIEW **/

--1
CREATE VIEW STUDENTINONINTESI AS
SELECT MATRICOLA,COGNOME,NOME,RESIDENZA,DATANASCITA,LUOGONASCITA,CORSODILAUREA,ISCRIZIONE
FROM STUDENTI WHERE RELATORE IS NULL
--2
INSERT INTO STUDENTINONINTESI
VALUES ('EC503905', 'CAMOSSI', 'ELENA', 'GENOVA', '04/29/1975', 'BRENO', '9', '1994/1995')
--3
INSERT INTO STUDENTINONINTESI
SELECT 'FA2002165B', COGNOME, NOME, RESIDENZA, DATANASCITA, LUOGONASCITA,'9','2001/2002'
FROM STUDENTI JOIN CORSIDILAUREA ON CORSODILAUREA = ID
 WHERE COGNOME = 'FANIZZI' AND NOME = 'ANGELA' AND DENOMINAZIONE = 'MATEMATICA'

--4
CREATE VIEW STUDENTIMATE as
SELECT * FROM STUDENTI JOIN CORSIDILAUREA ON CORSODILAUREA=CORSIDILAUREA.ID
WHERE  DENOMINAZIONE = 'MATEMATICA' AND ISCRIZIONE > '1999/2000'

--5
CREATE VIEW PROFRICCHI AS
SELECT * FROM PROFESSORI
WHERE STIPENDIO > 35000

insert into ProfRicchi values (80, 'Ancona', 'Massimo', 40000)

insert into ProfRicchi values (81, 'Catania', 'Barbara', 20000)

-- Vengono permessi entrambi gli inserimenti. Per impedire il secondo occorre utilizzare la CHECK OPTION

CREATE VIEW PROFRICCHI2 AS
SELECT * FROM PROFESSORI
WHERE STIPENDIO > 35000
WITH CHECK OPTION


--6
UPDATE STUDENTINONINTESI
SET CORSODILAUREA = 9, ISCRIZIONE = '2001/2002'
WHERE COGNOME = 'GELLINI' AND NOME = 'MARCO'
AND ISCRIZIONE = '1999/2000'

/*-- alternativa: select nella set
UPDATE STUDENTINONINTESI
SET CORSODILAUREA = = (SELECT ID FROM CORSIDILAUREA
            WHERE DENOMINAZIONE='INFORMATICA'), ISCRIZIONE = '2001/2002'
WHERE COGNOME = 'GELLINI' AND NOME = 'MARCO' AND ISCRIZIONE = '1999/2000'
*/
--7
UPDATE STUDENTINONINTESI
SET CORSODILAUREA = (SELECT ID FROM CORSIDILAUREA
            WHERE DENOMINAZIONE = 'FISICA'),
ISCRIZIONE = '2001/2002'
WHERE CORSODILAUREA=(SELECT ID FROM CORSIDILAUREA
WHERE DENOMINAZIONE = 'MATEMATICA' AND ISCRIZIONE = '2000/2001')
--8
DELETE FROM STUDENTINONINTESI
WHERE ISCRIZIONE < '1990/1991'


/* Operazioni insiemistiche */

-- In SQL-server e' presente solo l'operatore UNION. Gli altri operatori
-- non sono supportati e le interrogazioni vanno risolte utilizzando
-- sotto interrogazioni

-- 1
SELECT cognome, nome
FROM professori
UNION
SELECT cognome, nome
FROM studenti


-- 2
SELECT cognome, nome, 'professore'
FROM professori
UNION
SELECT cognome, nome, 'studente'
FROM studenti

-- La cardinalita' degli insiemi restituiti non e' uguale perche' nel primo
-- caso vengono eliminati i duplicati. Per impedirlo usare la clausola UNION ALL

-- 3
SELECT X.cognome, X.nome
FROM professori X
where EXISTS (SELECT * FROM studenti where cognome = X.cognome and nome = X.nome)


-- 4
SELECT X.cognome, X.nome
FROM professori X
where NOT EXISTS (SELECT * FROM studenti where cognome = X.cognome and nome = X.nome)