Lab 2- Solutions at end PDF

Preview

Summary

Lab solutions (at end) and questions ...

Description

CITS1402 Lab Two Gordon Royle 2020 Semester Two

Learning Aims This lab is concerned with combining the SELECT and JOIN commands to perform more advanced queries, along with additional practice on ORDER BY and LIMIT. We start with a worked example from the world database. Worked Example Suppose that you must list each city and the country in which it lies. The names of the cities are in the table City while the names of the countries are in the table Country. These tables are related by the three-letter code for the country, which is in the countryCode column in the table City, but the code column in the table Country. SELECT City.name, Country.name FROM City, Country WHERE City.countryCode = Country.code;

There is a lot going on here: • SELECT City.name, Country.name This commands says that each row of the output should consist of the fields City.name and Country.name where we need to use the “full name” of the two columns. • FROM City, Country This indicates that the selection should start with the Cartesian product of the tables City and Country. • WHERE City.countryCode = Country.code This indicates that each row of the Cartesian product should be tested, and only the rows satisfying the condition should be further processed. This ensures that the “two halves” of the each row are referring to the same country.

1

Lab Questions This lab should be completed by the end of Teaching Week 3. You should submit three files called Q1.sql, Q2.sql and Q3.sql to cssubmit at https://secure.csse.uwa.edu. au/run/cssubmit. As usual, you should submit plain text files only with the correct names, and each file should contain just the SQL query — no dot commands, no output and no terminal prompts from SQLite or the shell (Terminal/Powershell). Your commands will be tested against a database with the same schema as world but not necessarily the same data.

Question 1. Write a SQL query that will output the full details (i.e., just use SELECT *) for each city with population strictly greater than 5000000. SELECT * FROM City WHERE pop > 5000000;

Question 2. Write a SQL query that will output the city and country name for each European city. The first few rows of the output table should be: Amsterdam|Netherlands Rotterdam|Netherlands Haag|Netherlands

SELECT City.name, Country.name FROM City, Country WHERE City.countryCode = Country.code AND Country.continent = "Europe";

Question 3. Alter the worked-example query at the top of this lab sheet to print out all the Australian cities. (Use AND to add a further condition to the WHERE clause.) The first few rows of the output table should be: Sydney|Australia Melbourne|Australia Brisbane|Australia

2

SELECT City.name, Country.name FROM City, Country WHERE City.countryCode = Country.code AND Country.name = "Australia";

Question 4. cssubmit Q1.sql List the 3-letter country codes of all of the countries where English is spoken. You may assume that English is spoken in a country if it appears in one of the rows of CountryLanguage for that country. (Hint: You only need to use one table for this question, so no joins are needed.) The first few rows of the output table should be: ABW AIA ANT SELECT countryCode FROM CountryLanguage WHERE language = "English";

Question 5. Write a SQL query to list the rows of the table CountryLanguage relating to Afghanistan, which has country code AFG. According to this, how many languages are spoken in Afghanistan and how many of them are official languages? SELECT * FROM CountryLanguage WHERE countryCode = ’AFG’;

Question 6. Write a single query to construct a 2-column table where each row contains a country name and a language spoken in that country. If there are multiple languages spoken in a country, then the output should contain multiple rows, one for each language. If your query is correct, then the first few rows of the output table should be: Aruba|Dutch Aruba|English Aruba|Papiamento Aruba|Spanish

3

Afghanistan|Balochi Afghanistan|Dari SELECT Country.name, CountryLanguage.language FROM Country, CountryLanguage WHERE Country.code = CountryLanguage.countryCode;

Question 7. cssubmit Q2.sql Write a single query to construct a 2-column table where each row contains a country name and an official language spoken in that country. If there are multiple official languages for a country, then the output should contain multiple rows, one for each official language. Note: The isOfficial column uses the text values "T" and "F" to indicate true and false respectively. If your query is correct, the first few rows of the output table should be: Aruba|Dutch Afghanistan|Dari Afghanistan|Pashto Anguilla|English Albania|Albaniana SELECT Country.name, CountryLanguage.language FROM Country, CountryLanguage WHERE Country.code = CountryLanguage.countryCode AND isOfficial = ’T’;

Question 8. Write a SQL query to list just the names of all the countries where French is an official language If your query is correct, the first few rows of the output table should be: Burundi Belgium Canada SELECT Country.name FROM Country, CountryLanguage WHERE Country.code = CountryLanguage.countryCode AND CountryLanguage.language = "French" AND CountryLanguage.isOfficial = ’T’;

4

Question 9. Write a SQL query to list just the names of all the countries where French is an official language, even though it is spoken by fewer than 25% of the inhabitants of that country. If your query is correct, then it should return 13 countries from Burundi to Vanuatu. SELECT Country.name FROM Country, CountryLanguage WHERE Country.code = CountryLanguage.countrycode AND CountryLanguage.language = "French" AND CountryLanguage.isOfficial = ’T’ AND CountryLanguage.percentage < 25;

Question 10. Write a SQL query to list the ID numbers of each capital city. (Your list may have some blank lines in it, can you figure out why?) SELECT capital FROM Country;

Question 11. cssubmit Q3.sql Write a single SQL query to list the name of each country along with the name of its capital city. (You may ignore “countries” with no capital city.) If your query is correct, the first few rows of the output table should be: Aruba|Oranjestad Afghanistan|Kabul Angola|Luanda SELECT Country.name, City.name FROM Country, City WHERE Country.code = City.countryCode AND Country.capital = City.ID;

Question 12. CHALLENGE List the country codes of all countries that have both English and French as an official language. (There are only three such countries.) Hint: You only need to use the table CountryLanguage but because SQL is a “rowprocessing machine”, you cannot directly refer to two different rows of the same table. If only you could work with two copies of the same table at the same time . . . 5

SELECT L1.countrycode FROM Countrylanguage L1, Countrylanguage L2 WHERE L1.countrycode = L2.countrycode AND L1.language = ’English’ AND L2.language = ’French’ AND L1.isOfficial = ’T’ AND L2.isOfficial = ’T’;

6...