SQL Queries

/*Question 1*/

/*(a)*/
SELECT Name, LifeExpectancy, Continent
FROM Country
WHERE LifeExpectancy < 60;

/*(b)*/
SELECT Continent, AVG(LifeExpectancy)
FROM Country
WHERE Continent != 'Antartica'
GROUP BY Continent;

/*(c)*/
SELECT c.Name, ci.District, c.LifeExpectancy, c.Continent
FROM Country AS c, City AS ci
WHERE c.LifeExpectancy < 60
AND c.Code = ci.CountryCode
GROUP BY District;


/*Question 2*/

/*(a)*/
INSERT INTO CountryLanguage (CountryCode, LANGUAGE, IsOfficial, Percentage)
VALUES('IRL', 'Hindi', 'F' , 2.6 );

/*(b)*/
SELECT c.Name, c.HeadOfState, AVG(ci.Population)
FROM Country AS c, City AS ci
WHERE c.Code = ci.CountryCode
AND c.Continent = 'Europe'
AND (ci.Population > 300000 )
GROUP BY c.Name;

/*(c)*/
SELECT c.Name, Capital, AVG(ci.Population), c.SurfaceArea
FROM Country c, City ci, CountryLanguage cl
WHERE c.Continent = 'Oceania'
AND cl.LANGUAGE = 'English'
AND c.Capital = ci.ID
AND c.Code = cl.CountryCode
AND (ci.Population > 10000)
GROUP BY c.Name;