select blt.ble_test_id, bls.ble_scan_id, ble_scan_object_id, name, bls.phone_battery_level, blt.NBR_SCAN, blt.SCAN_DURATION, blt.SCAN_PERIODICITY
from ble_test blt, ble_scan bls, ble_scan_object blso
where blt.ble_test_id = bls.ble_test_id
and bls.ble_scan_id = blso.ble_scan_id
and blt.ble_test_id = 100393;

--Number of scans for each test
select blt.ble_test_id, count(bls.ble_scan_id) as Number_Of_Scans
from ble_test blt, ble_scan bls
where blt.ble_test_id = bls.ble_test_id
group by blt.ble_test_id;

--Number of devices found for each scan
select blt.ble_test_id, bls.ble_scan_id, count(blso.ble_scan_object_id) as Number_Of_BLE_Devices
from ble_test blt, ble_scan bls, ble_scan_object blso
where blt.ble_test_id = bls.ble_test_id
and bls.ble_scan_id = blso.ble_scan_id
group by bls.ble_scan_id, blt.ble_test_id
order by bls.ble_scan_id desc;

--show devices with RSSI between -80 and -90
select blt.ble_test_id, bls.ble_scan_id, blso.ble_scan_object_id, blso.rssi
from ble_test blt, ble_scan bls, ble_scan_object blso
where blt.ble_test_id = bls.ble_test_id
and bls.ble_scan_id = blso.ble_scan_id
and blso.rssi between -90 and -80
order by blt.ble_test_id, bls.ble_scan_id, blso.ble_scan_object_id, blso.rssi;

--show tests with more than 5 scans
select blt.ble_test_id, name, blt.NBR_SCAN ,count(bls.ble_scan_id) as number_of_scans
from ble_test blt, ble_scan bls
where blt.ble_test_id = bls.ble_test_id
group by blt.ble_test_id, name, blt.NBR_SCAN
having count(bls.ble_scan_id) > 5
order by blt.ble_test_id;

select * from ble_scan where ble_test_id = 100184;

select * from ble_test where ble_test_id = 100184;

--show all tests that have different number of real scans than the number of scan set in the test
select blt.ble_test_id, name, blt.NBR_SCAN, count(bls.ble_scan_id) as number_of_scans
from ble_test blt, ble_scan bls
where blt.ble_test_id = bls.ble_test_id
group by blt.ble_test_id, name, blt.NBR_SCAN
having count(bls.ble_scan_id) != blt.NBR_SCAN
order by blt.ble_test_id, name, blt.NBR_SCAN;