PC02-DB-UTEC, PostgreSQL

Language: Layout:

--PostgreSQL 9.6
--'\\' is a delimiter

CREATE TABLE Programa (
  Pid integer PRIMARY KEY, 
  Actividad  varchar(50) default NULL,
  Carrera varchar(50) default NULL,
  Lugar varchar(255) default NULL,
  Horario varchar(255) default NULL
);

CREATE TABLE Interesado (
  DNI varchar(50) PRIMARY KEY, 
  Nombre  varchar(50) default NULL,
  Email varchar(50) default NULL,
  Edad integer default NULL,
  Sexo varchar(1) default NULL,
  Colegio varchar(255) default NULL              
);

CREATE TABLE Asistencia (
  Pid integer NOT NULL, 
  DNI  varchar(50) NOT NULL,
  Hora  timestamp  default NULL, 
  PRIMARY KEY (Pid,DNI),
  FOREIGN KEY (Pid) REFERENCES Programa (Pid),
  FOREIGN KEY (DNI) REFERENCES Interesado (DNI)
);

--Preg (2a) 
ALTER TABLE Interesado ADD CONSTRAINT email_check UNIQUE (Email); 
ALTER TABLE Interesado ALTER COLUMN Nombre SET NOT NULL;
ALTER TABLE Interesado ALTER COLUMN Colegio SET NOT NULL;
ALTER TABLE Interesado ADD CONSTRAINT Edad UNIQUE (Email); 
ALTER TABLE Interesado ADD CONSTRAINT edad_check CHECK (Edad >=12 AND Edad <=85);

INSERT INTO Programa (Pid,Actividad,Carrera,Lugar,Horario) VALUES 
(1,'RoboRally', 'Ciencia Computacion','Piso 5','10:00 a 11:00'),
(2,'RoboRally', 'Ciencia Computacion','Piso 5','13:00 a 14:00'),
(3,'Robots Biomédicos', 'Bioingeniería','Piso 1','12:00 a 14:00'),
(4,'Reality Space', 'Ciencia Computacion','Piso 5','10:40 a 11:25'),
(5,'Pensamiento Computacional', 'Ciencia Computacion','Piso 8','14:40 a 15:25');

INSERT INTO Interesado (DNI,Nombre,Email,Edad,Sexo,Colegio) VALUES 
('92436099','Madaline Henson','et.euismod.et@cursus.edu',18,'M','Phasellus In Consulting'),
('52527899','Ignatius Walsh','sem.molestie@Duisatlacus.net',19,'M','Risus Quisque Inc.'),
('120571899','Alyssa Massey','erat@aliquetmolestie.org',18,'F','Euismod Urna Nullam Corporation'),
('152666099','Uriel Callahan','Aliquam.vulputate@Nullam.ca',19,'M','Tincidunt Dui Institute'),
('151856699','Jeanette Prince','ullamcorper.eu.euismod@accumsaninterdum.edu',19,'F','Pede LLC'),
('103092199','Ashton Dudley','in.hendrerit@ullamcorperviverraMaecenas.org',19,'F','Arcu Imperdiet Limited'),
('152075299','Colt Turner','vestibulum@Donec.net',16,'F','Congue Incorporated'),
('121824399','Amanda Stout','Curabitur@nonjustoProin.edu',19,'F','Ac Feugiat Non Associates'),
('181055499','Brian Potter','sed.dui@mauris.ca',17,'M','Mauris LLC'),
('192928799','Dolan Mullen','adipiscing@tellus.net',18,'M','Odio Tristique Pharetra Limited');

INSERT INTO Asistencia (Pid,DNI,Hora) VALUES
(1,'181055499','1/10/2019 08:00:00'),
(2,'181055499','1/10/2019 08:00:00'),
(3,'152666099','1/10/2019 08:10:00'),
(4,'181055499','1/10/2019 08:12:00'),
(1,'192928799','1/10/2019 08:15:00'),
(2,'103092199','1/10/2019 08:30:00'),
(2,'121824399','1/10/2019 08:33:00'),
(3,'181055499','1/10/2019 08:35:00'),
(5,'181055499','1/10/2019 08:45:00'),
(4,'192928799','1/10/2019 08:47:00');
--select * from programa;
--select * from interesado;
--select * from Asistencia;
 
--Preg (2d) 
Select A.Pid,P.Actividad, COUNT(*) AS NUMASIST 
from Asistencia A, Programa P
WHERE  A.Pid= P.Pid
GROUP BY A.Pid , P.Actividad
HAVING P.Actividad ='RoboRally';

--Preg (2e) 
SELECT I.DNI,I.Nombre 
 FROM  Interesado I WHERE I.DNI
    NOT IN (SELECT A.DNI
            FROM Asistencia A
            WHERE A.Pid IN (SELECT P.Pid
                            FROM Programa P
                            Where p.Carrera='Ciencia Computacion'));
                            
 --Preg (2h)                            
SELECT I.DNI,I.Nombre 
FROM  Interesado I WHERE I.DNI IN (
        SELECT A.DNI from Programa P, Asistencia A 
              WHERE P.PID = A.PID AND P.Actividad='RoboRally' 
             INTERSECT
        SELECT  A.DNI  from Programa P , Asistencia A 
             WHERE P.PID = A.PID AND P.Actividad='Pensamiento Computacional') ;

--Preg (2i) Opcion 1
 SELECT A.DNI   FROM Asistencia A
  JOIN Programa P on A.Pid = P.Pid AND P.carrera = 'Ciencia Computacion'
  GROUP BY A.DNI  
 HAVING COUNT(DISTINCT A.Pid) = 
  (SELECT count(*) FROM Programa WHERE carrera = 'Ciencia Computacion')  ;
    
 --Preg (2i) Opcion 2
SELECT I.DNI, I.Nombre
FROM Interesado I
WHERE NOT EXISTS (( 
                    SELECT P.Pid
                        FROM Programa P WHERE  carrera = 'Ciencia Computacion' )
                       EXCEPT
                (SELECT A.Pid
                FROM Asistencia A
                WHERE A.DNI = I.DNI ));
--Preg (2i) Opcion 3                
SELECT I.DNI, I.Nombre
FROM Interesado I
WHERE NOT EXISTS ( SELECT P.Pid
                   FROM Programa P
                   WHERE  carrera = 'Ciencia Computacion' 
                          AND NOT EXISTS (SELECT A.Pid
                                        FROM Asistencia A
                                        WHERE A.Pid = P.Pid
                                        AND A.DNI = I.DNI))


absolute service time: 0,48 sec