Here are the eight most populous countries in the world:
Id | Name | Area | Population | Source |
---|---|---|---|---|
18 | India | 3,287,240 | 1,348,834,400 | Based on 2011 census |
27 | Bangladesh | 143,998 | 166,774,136 | — |
34 | Pakistan | 803,940 | 205,095,217 | Official population clock |
42 | Nigeria | 923,768 | 200,962,000 | UN projection |
46 | United States | 9,833,517 | 329,424,894 | Official population clock |
54 | Brazil | 8,515,767 | 210,076,263 | — |
59 | China | 9,640,821 | 1,397,906,480 | Official estimate |
60 | Indonesia | 1,904,569 | 268,074,600 | Official annual projection |
-
Using your favorite DB client, design and create a database table called
countries
that would store the information presented above (create a database first if you don’t have any existing ones to play with). Don’t bother with creating any keys or indices for now, just create the five columns. Copy and paste the SQL query generated by the client below (it should start withcreate table
or something similar; if it is difficult to find the query generated by your client, ask for assistance):create table country ( id int PRIMARY KEY, Name text, Area int, Population int, Source text );
-
Manually create a query or a series of queries that would fill the table with the information above. Put the query/queries below:
INSERT INTO country (id, name, area, population, source) VALUES (18, 'India', 3287240, 1348834400, 'Based on 2011 census'), (27, 'Bangladesh', 143998, 166774136, null), (34, 'Pakistan', 803940, 205095217, 'Official population clock'), (42, 'Nigeria', 923768, 200962000, 'UN projection'), (46, 'United States', 9833517, 329424894, 'Official population clock'), (54, 'Brazil', 8515767, 210076263, null), (59, 'China', 9640821, 1397906480, 'Official estimate'), (60, 'Indonesia', 1904569, 268074600, 'Official annual projection')
-
Create a query that would return everything from the table:
SELECT * FROM country
-
Create a query that would return a single row: the country with the ID of 46.
SELECT name FROM country WHERE id = 46
-
Create a query that would return the four countries with the following IDs: 18, 34, 54, 59.
SELECT name FROM country WHERE id IN (18, 34, 54, 59)
-
Create a query that would return all the countries except the country with the ID of 27 (
Bangladesh
).SELECT name FROM country WHERE NOT id = 27
-
Create a query that would select the names and sources for the countries whose area is over 1,000,000 km2:
SELECT name, source FROM country WHERE area > 1000000
-
Create a query that would select the IDs of the countries with missing sources:
SELECT id FROM country WHERE source IS NULL
-
Create a query that would return the area, population, and population density (a computed column aliased
density
) of every country that has a source.SELECT area, population, (population / area) AS density FROM country WHERE NOT source IS NULL
-
Create a query that would return the list of all sources, without repetition:
SELECT DISTINCT source FROM country
-
Create a query that would select the countries whose source is official (starting with
Official
) or the area is below 1,000,000 km2:SELECT name FROM country WHERE source LIKE 'Official%' OR area < 1000000
-
Create a query that would select the countries whose source is official (starting with
Official
) and the area is below 1,000,000 km2:SELECT name FROM country WHERE source LIKE 'Official%' AND area < 1000000
-
Create a query that would select all the countries except those whose source is official (starting with
Official
):SELECT name FROM country WHERE NOT source LIKE 'Official%'
-
Create a query that would select the countries whose names start with an
N
,O
,P
, ...,X
,Y
, orZ
:SELECT name FROM country WHERE name > 'N%'
-
Create a query that would return all the countries sorted by their name alphabetically:
SELECT name FROM country ORDER BY name
-
Create a query that would return the population density figures of the countries sorted in the descending order. The column should be aliased
density
.SELECT (population / area) AS density FROM country ORDER BY density DESC
-
Create a query that would return the countries sorted by their source alphabetically, and then (if two or more countries share the same source) by their name in the reverse alphabetical order:
SELECT name FROM country ORDER BY source, name DESC
-
Set all sources to
NULL
:UPDATE country SET source = NULL
-
Update the sources for the countries with the population over 1,000,000,000 to
Official
:UPDATE country SET source = 'Official' WHERE population > 1000000000
-
Multiply the area by 100 and add 10 to the population for every country whose ID is greater than 50:
UPDATE country SET area = area * 100, population = population + 10 WHERE id > 50
-
Delete from the table every country whose population is less than 300,000,000:
DELETE FROM country WHERE population < 300000000
-
Delete all countries from the table:
DELETE FROM country
Don’t forget to create a pull request.