/*  -----------------------------------------------------
/*
/*  Group 13 Queries SQL Script
/*  Laura Dippold, Lisa Gebreamlak, Huseyin Karaarslan
/*
/*  -----------------------------------------------------

/*  Query 1 = Which experiment had results, and what type?  */

SELECT ER.ExperimentResultID,   E.*, ET.ExperimentTypeValue
FROM 
EXPERIMENT_RESULTS ER
INNER JOIN EXPERIMENT E ON ER.EXPERIMENT_ExperimentID = E.ExperimentID
INNER JOIN experiment_type ET ON E.EXPERIMENT_TYPE_ExperimentTypeID = ET.ExperimentTypeID;

/*  Query 2 = Shows participants from the view table that participated in experiment 6 "Battery power testing in UAVs"  */

SELECT * FROM experiment_participant WHERE EXPERIMENT_ExperimentID=6;

/*  Query 3 = How many results does each experiment do we have? */

SELECT  E.ExperimentID, COUNT(ER.EXPERIMENT_ExperimentID) AS EXPERIMENT_COUNT
FROM 
EXPERIMENT_RESULTS ER
RIGHT JOIN EXPERIMENT E ON ER.EXPERIMENT_ExperimentID = E.ExperimentID

GROUP BY E.ExperimentID
ORDER BY E.ExperimentID;

/*  Query 4 = What is the Participant Data in Campaign 2 "Joint Interagency Field Experimentation (JIFX) 16-4"  */
SELECT P.*
FROM
PARTICIPANTS P 
INNER JOIN EXPERIMENT_has_PARTICIPANTS EP ON P.ParticipantID = EP.PARTICIPANTS_ParticipantID
INNER JOIN EXPERIMENT E ON EP.EXPERIMENT_ExperimentID = E.ExperimentID
INNER JOIN CAMPAIGN C ON E.CAMPAIGN_CampaignID = C.CampaignID
WHERE C.CampaignID = 2;

/*  Query 5 = Find all vehicles using a photo camera in the sensors table  */

SELECT VEHICLE.VehicleName, SENSORS.SensorPurpose, SENSORS.StartDateOfOperation, SENSORS.EndDateOfOperation, SENSOR_TYPE.SensorType
FROM VEHICLE 
INNER JOIN SENSORS ON VEHICLE.VehicleID = SENSORS.VEHICLE_VehicleID
INNER JOIN SENSOR_TYPE ON SENSORS.SENSOR_TYPE_SensorTypeID = SENSOR_TYPE.SensorTypeID
WHERE SensorType = "Photo"
ORDER BY VEHICLE.VehicleName;


/*  Query 6 = Find all vehicles that participated in Experiment 3 "Small swarm testing', 'Mini-swarm 2017"  */

SELECT EXPERIMENT.ExperimentDescription, EXPERIMENT.ExperimentOutcome, VEHICLE.VehicleName
FROM VEHICLE
INNER JOIN EXPERIMENT_RESULTS ON VEHICLE.VehicleID = EXPERIMENT_RESULTS.VEHICLE_VehicleID
INNER JOIN EXPERIMENT ON EXPERIMENT_RESULTS.EXPERIMENT_ExperimentID = EXPERIMENT.ExperimentID
WHERE EXPERIMENT.ExperimentID = 3
ORDER BY VEHICLE.VehicleName;