MBA - MIS Concentration Student Page

This page provides answers for the SQL Problem sets.

SQL Exercise Set #6

1. Display the name and size of all nations that have at least three bordering nations.

First, create two queries to join countries and borders.
Second, create a union query to list all results from the countries/borders joins.
SELECT nations.country as CountryA, nations_1.country as CountryB from qryCountriesA
UNION
select nations.country as CountryA, nations_1.country as CountryB from qryCountriesB;
Third, create a query to count borders and list only those with three or more bordering countries.
SELECT A.CountryA, Count(A.CountryB) AS Borders
FROM qryCountriesUnion AS A
GROUP BY A.CountryA
HAVING (((Count(A.CountryB))>2));

2. Display all countries which border on other countries, and for each country list its bordering nations by name.

This query used a function, created in module1:

Function strReturnBorderingCountryNames(strCountry)
	Dim dbs As Database, rst As Recordset, str As String
	str = ""
	Set rst = CurrentDb.OpenRecordset("select CountryB " _
		& "from qryCountriesUnion where CountryA='" _
		& strCountry & "'", dbOpenDynaset)
	Do While Not rst.EOF
		str = str & rst.Fields(0) & ","
	rst.MoveNext
	Loop
	str = Left(str, Len(str) - 1)
	strReturnBorderingCountryNames = str
End Function

Then, the following query was created. Note that we used the union query created in the previous question.

SELECT country, strReturnBorderingCountryNames([country]) as borders
FROM nations
WHERE country in (select CountryA from qryCountriesUnion);

The results are:

SQL Problem Sets