Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* 1.
- Using a UNION query, create a report that lists the name and the district of the
- cities in Canada (code CAN) and the Netherlands (code NLD). Note that this query
- can be written without using a UNION. The purpose of this question is to use UNION.
- (city table)*/
- SELECT name, district, CountryCode
- FROM city
- WHERE CountryCode = "CAN"
- UNION
- SELECT name, district, CountryCode
- FROM city
- WHERE CountryCode = "NLD"
- ORDER BY CountryCode, district;
- /* 2.
- Using an INNER JOIN..ON, create a report that list the name of cities, the country
- and the indepyear of all countries whose indepYear field is not null. (city and
- country tables).*/
- SELECT cy.name, c.name, c.indepyear
- FROM city cy INNER JOIN country c
- ON (cy.countrycode = c.Code)
- WHERE c.indepyear IS NOT NULL;
- /* 3.
- Rewrite the previous query using WHERE/FROM.*/
- SELECT cy.name, c.name, c.indepyear
- FROM city cy, country c
- WHERE cy.countrycode = c.Code
- AND c.indepyear IS NOT NULL;
- /* 4.
- Using a subquery, list the name of the city, the countrycode, the population and the
- average city population of all cities with a population less than the average population.
- (city table)*/
- SELECT name, countrycode, population, (SELECT AVG(population) FROM city) as "Average Pop"
- FROM City
- GROUP BY population
- HAVING population<(SELECT AVG(population) FROM city);
- /* 5.
- Using INNER JOIN ..ON, write a query that displays, for the city of Toronto, the name
- of the city and the head of state of its country. (city and country tables)*/
- SELECT cy.name, c.headofstate
- FROM city cy INNER JOIN country c
- ON (cy.countrycode = c.code)
- WHERE cy.name = "Toronto";
- /* 6.
- Using INNER JOIN ..ON the three tables, write a query that displays the name of the
- city, the city continent, the city head of state, the year of independance and the
- percentage of people who speaks English in the city of Toronto. (city, countrylanguage
- and country tables)*/
- SELECT cy.name, c.continent, c.headofstate, c.indepyear, cl.percentage, cl.language
- FROM city cy INNER JOIN countrylanguage cl
- ON (cy.countrycode = cl.countrycode)
- INNER JOIN country c
- ON (c.code = cl.countrycode)
- WHERE cy.name = "Toronto" AND cl.language = "English";
- /* 7.
- Rewrite the previous query using WHERE/FROM.*/
- SELECT cy.name, c.continent, c.headofstate, c.indepyear, cl.percentage, cl.language
- FROM city cy, countrylanguage cl, country c
- WHERE (cy.countrycode = cl.countrycode) AND (c.code = cl.countrycode)
- AND cy.name = "Toronto" AND cl.language = "English";
- /*Querying table city and country.*/
- /* 8.
- Using a subquery, list the name of the city, the countrycode and the population of the
- city with the largest population. (city table)*/
- SELECT name, countrycode, MAX(population)
- FROM city;
- /* 9.
- Using a subquery, list the name of the city, the countrycode and the population of all
- the cities whose population is larger than the average population. (city table)*/
- SELECT name, countrycode, population
- FROM city
- HAVING population>(SELECT AVG(population) FROM city);
- /* 10.
- What is wrong with the following subquery?
- SELECT name,countrycode,population,avg(population)
- FROM city
- WHERE population > (SELECT avg(population) FROM city)*/
- /*Selecting avg(population) instead of (SELECT avg(population) FROM city) will not return the desired results.
- It will do the average of each field.
- It also does not have a closing ;*/
- /* 11.
- Replace the first avg(population) with (SELECT AVG(population) from city)
- What happens? Why?*/
- SELECT name,countrycode,population,(SELECT avg(population) FROM city)
- FROM city
- WHERE population > (SELECT avg(population) FROM city);
- /*The query now works because it is fetching the average of all city populations*/
- /* 12.
- Using a subquery, list the name of the country, the continent and the life expectancy
- of all countries whose life expectancy is less than the average life expectancy.
- (country table)*/
- SELECT name, continent, lifeexpectancy
- FROM country
- HAVING lifeexpectancy < (SELECT avg(lifeexpectancy) FROM country)
- ORDER BY lifeexpectancy;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement