query.txt

query examples:

-- Selection: select attribute in 1st field, type in operators(>,<,=, <=) in 2nd field, and input value in the 3rd field.
examples:
SELECT * FROM MilitaryUnit WHERE Capacity > 10; 
SELECT * FROM MilitaryUnit WHERE Death < 1;

-- Projection: project on Combatant
examples:
SELECT * FROM Combatant;   			// select nothing, click Enter.
SELECT Combatant_name, CID, MUID FROM Combatant;// select checkbox: Combatant_name, CID, MUID, etc.


-- Join: select two tables to join(case 1)
examples:
SELECT * FROM Combatant natural join Commander;
SELECT * FROM Combatant natural join Soldier_enrolls;

-- Aggregation: total num of Combatant older than N where N is an integer input from user
ex:
SELECT Count(*) FROM Combatant WHERE Age > 30;


-- Nested agg + groupby:  find the muid with the lowest avg age (youngest unit)
SELECT MIN(AvgAge) AS LowestAvgAge FROM (SELECT MUID, AVG(Age) AS AvgAge FROM Combatant GROUP BY MUID);



-- Division: Find all MilitaryUnit that have been to every Area in missions.  
SELECT * 
FROM MilitaryUnit m 
WHERE NOT EXISTS 
((SELECT a.AreaID from Area a) 
MINUS 
(select t.AreaID from Mission_takePlace_assign3 t where t.MUID = m.MUID))