Sample Queries World Database

1. What languages are spoken in the US, with the percent, sort by percent, high to low.

SELECT Language, Percentage FROM CountryLanguage WHERE CountryCode ="USA" ORDER BY Percentage DESC

2. Do the above, including the numbers of speakers of each language.

SELECT Language, Percentage*Population/100 AS Speakers FROM CountryLanguage , Country WHERE Country.Code = CountryCode AND CountryCode ="USA" ORDER BY Percentage DESC;

3. Which langauges do Brazil, Canada, the USA have in common? Which langauge has the greatest TOTAL number of speakers, and what is that number?

CREATE VIEW BL AS SELECT Language FROM CountryLanguage WHERE CountryCode ="BRA";
CREATE VIEW USL AS SELECT Language FROM CountryLanguage WHERE CountryCode ="USA";
CREATE VIEW CL AS SELECT Language FROM CountryLanguage WHERE CountryCode ="CAN";
SELECT * FROM BL, USL, CL WHERE BL.Language = USL.Language AND USL.Language = CL.Language;

CREATE VIEW BL AS SELECT Language,Population*Percentage/100 AS LangPop FROM CountryLanguage , Country WHERE CountryCode ="BRA" AND Code = CountryCode ;

CREATE VIEW USL AS SELECT Language,Population*Percentage/100 AS LangPop FROM CountryLanguage , Country WHERE CountryCode ="USA" AND Code = CountryCode ;

CREATE VIEW CL AS SELECT Language,Population*Percentage/100 AS LangPop FROM CountryLanguage , Country WHERE CountryCode ="CAN" AND Code = CountryCode ;

SELECT BL.Language,BL.LangPOP+USL.LangPOP+CL.LangPOP AS TotalSpeakers FROM BL, USL, CL WHERE BL.Language = USL.Language AND USL.Language = CL.Language ORDER BY TotalSpeakers DESC LIMIT 1;

4. What are the most common langauge spoken in Europe?

SELECT Language,SUM(Percentage*Population/100) AS Speakers FROM CountryLanguage ,Country WHERE Code=CountryCode AND Continent = "Europe" AND Language IN (SELECT Language FROM CountryLanguage ,Country WHERE Code=CountryCode AND Continent = "Europe") GROUP BY Language ORDER BY Speakers DESC;

SELECT Language,SUM(Percentage*Population/100) AS Speakers FROM CountryLanguage ,Country WHERE Code=CountryCode AND Continent = "Europe" AND Language IN (SELECT Language FROM CountryLanguage ,Country WHERE Code=CountryCode AND Continent = "Europe") GROUP BY Language ORDER BY Speakers DESC LIMIT 1;

5. What country and city is it where that city largest percentage of of the countries population?

6. Which states in the USA have cites in the database?

7. Which states in the USA have for than 5 cities lists?

8. Which states in the USA have NO cities list?

Topic revision: r2 - 2013-10-09 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback