Please write the query statements for the questions below, Thank You! Table: cit
ID: 3904187 • Letter: P
Question
Please write the query statements for the questions below, Thank You!
Table: city
column name data type size notes about the data
ID Number 11 City Id Code (primary key)
name Text 35 Name of City
country Code Text 3 Country Code foreign Key to Country(Code)
District Text 20
Population Number 11
Table: country
column name data type size notes about the data
Code Text 3 Country Code (primary key)
Name Text 52 street address
Continent Text 13 Asia, Europe, North America, Africa, Oceania, Antarctica, South America
Region Text 26
SurfaceArea number (10,2)
IndepYear Number 6 Year of independence
Population Number 11
LifeExpectancy Number (3,1)
GNP Number (10,2)
GNPOld Number (10,2)
LocalName Text 45
GovermentForm Text 45
HeadOfState Text 60
Capital Number 11 foreign Key to City (ID)
Code2 Text 2
Table: countrylanguage
column name data type size notes about the data
CountryCode Text 3 Country Code (primary key) foreign Key to Country(Code)
Language Text 30 Language (primary key)
IsOfficial Text 1 Official Language (T or F)
Percentage Number (4,1)
Write the join queries for the SQL database described above:
Please use descriptive column names were needed.
1 The Life Expectancy, Country Population, Country Name, Head of State and Official Language of countries with more than 100 million people. Sort the results by Life Expectancy, Population.
2 The Country Name and Capital City of countries with surface area greater than 2 million.
3. The Country Name, Independence Year, Capital City Name, Language, Language Percentage, with Independence Year > 1991. Sort the results by country name ascending and language percentage Descending
Explanation / Answer
If you have any doubts, please give me comment...
-- 1)
SELECT LifeExpectancy, Population, Name, HeadOfState, Language
FROM country C, countrylanguage CL
WHERE C.Code = CL.CountryCode AND IsOfficial = 'T' AND POPULATION>100000000
ORDER BY LifeExpectancy, Population;
-- 2)
SELECT C.Name, CT.name
FROM country C, city CT
WHERE C.Code = CT.countryCode AND SurfaceArea < 2000000;
-- 3)
SELECT C.Name, IndepYear, CT.Name, Language, Percentage
FROM country C, city CT, countrylanguage CL
WHERE C.Code = CL.CountryCode AND C.Code = CT.countryCode AND IndepYear>1991
ORDER BY C.Name ASC, Percentage DESC;