Ingeniero

1) CREATE TABLE Persona ( idPersona serial PRIMARY KEY, nom varchar(100) UNIQUE, edat int NOT NULL, sexe varchar(4) CHECK (sexe IN ('Home', 'Dona')), email varchar(50), telefon varchar(15) NOT NULL,ciutat varchar(50) DEFAULT 'Lleida', adresa varchar(100), cp varchar(10) );

2) CREATE VIEW Vista1 AS ( SELECT o.* FROM Ofertes o, Solicitud s, Persona p WHERE o.idOferta = s.idOferta ANDs.idPersona = p.idPersona AND p.ciutat = 'Barcelona' );

3) CREATE TABLE Primerencs AS ( SELECT p.nom, p.edat FROM Persona p, Estudis e, Titols t, Ofertes o, Solicitud s WHERE p.idPersona = t.idPersona ANDe.idEstudi = t.idEstudi AND e.nivell = 10 AND s.idPersona = p.idPersona AND t.idPersona = s.idPersona AND s.idOferta = o.idOferta AND o.any_alta = t.any_final );

4) ALTER TABLE Persona ADD COLUMNalsada int NOT NULL;

5) INSERT INTO Estudis VALUES (1, ‘Grau Superior, 'Un grau UdL', 3); INSERT INTO Persona VALUES (1,‘Pere’,35,’Home’,'pere@mail.com',’973123456’,’Lleida’,’Major,3’,’25001’);

6)UPDATE Persona SET ciutat = 'ILERDA', CP = ‘25000’ WHERE ciutat = 'Lleida';

7) UPDATE Titols SET any_inici = any_inici -1 FROM Persona p, Estudis e WHERE p.sexe = 'Dona' AND p.ciutat = 'Girona'AND p.edat > 25 AND e.nivel = 1 AND titols.idEstudi = e.idEstudi AND titols.idPersona = p.idPersona );

8) DELETE FROM Titols WHERE any_final – any_inici > 5;

9) DELETE FROM Ofertes WHERE idOfertaNOT IN(SELECT idOferta FROM Solicitud s);

10) SELECT o.idOferta, o.descripció FROM Ofertes o INNER JOIN Solicitud s ON (o.idOferta = s.idOferta AND o.salari_minim > 30.000) INNER JOIN Persona p ON(p.idPersona = s.idPersona INNER JOIN Titols t ON (t.idPersona = p.idPersona) INNER JOIN Estudis e ON (e.idEstudi = t.idEstudi AND e.nom LIKE '%Superior%' AND o.any_alta – e.any_final = 5) ORDER BYo.idOferta DESC; AND p.sexe = 'Home')

11) SELECT MIN (any_alta) AS “MINANY”, MAX(any_alta) AS “MAXANY”, AVG(salari_minim) AS “MITMIN”, AVG(salari_maxim) AS “MITMAX” FROM Ofertes;

12) SELECT...