SQL Programming I am lost and any help would be great SQL queries that join mult
ID: 3873740 • Letter: S
Question
SQL Programming
I am lost and any help would be great
SQL queries that join multiple tables.
1. Display complete country name and language spoken in that country. (Use the country and countrylanguage tables) Display them in order of the country name and language name within each country.
2. Use the above query as the starting point for query # 2. Display country name and the total number of languages spoken in that country.
-16 country 15202600-1 . city ID int(11) Name: char(35) CountryCode: char(3) 6 countrylanguage a CountryCode: char(3) a CountryCode char(3) Name:char(52) Continent:char(15) Language:char(30) somci al : enum(TF) Region:char(26) n SurfaceArea:oat(10,2) # Indeprear: smallint(6) n Population: int(11) # Life Expectancy : noat(31) n GNP float 10,2) #GNPOld float(10,2) District: char(20) # Percentage : float(4,1) Population : int11) LocalName: char(45) GovernmentForm: char(45) HeadofState: char(60) # capital : int(11) Code2 : char(2)Explanation / Answer
The following queries work on MySQL
Answer 1:
SELECT C.Name, L.Language
FROM countrylanguage L
INNER JOIN country C ON L.CountryCode = C.countryCode
ORDER BY C.Name, L.Language
In the above query, since we want to list all languages along with their country, we select language column from the countrylanguage using alias L, and performing inner join with country table to fetch the corrresponding country name. The tables are joined on the common field CountryCode . Also "order by" clause is used to order the output first on country name and then on language.
Answer 2
SELECT C.Name, count(L.Language)
FROM countrylanguage L
INNER JOIN country C ON L.CountryCode = C.countryCode
GROUP BY C.Name
We use the answer 1 as start point and modify it. So the tables are joined in similar fashion. But now instead of names being displayed, we need the count () of the languages, so we use the aggregate function count(). When ever we use the aggregate functions like count(), sum(), avg () etc , we use the GROUP BY clause to specify which records are need to be grouped and then the function applied,. So now we group based on the country name. So all records with the same country name are grouped together and the count() function is applied on that group i.e it iwll count the number of records in that group.
Hope the answer helped. If it did, please do rate it . Thank you.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.