Simple SQL Queries

Create the country table described in the demonstration material in you database management system. Write SQL queries for the queries listed in following subsections.

Lab. 1) Selection and Projection

  1. List names from rows where name contains 'Ocean'.
  2. List all columns from rows where area is zero.
  3. List names of all regions. The list should not have any duplicates.
  4. List names of populous (i.e. population over 10 Million) countries.
  5. List names of populous (i.e. population over 10 Million) European and Asian countries sorted by per capita GDP.

Lab. 2) Summarization, Aggregation

  1. Count number of countries with area between 0.5 Million Sq. Km. and 1 Million Sq. Km.
  2. List number of countries, total area, average area per country for all regions of the world.
  3. Select regions which have at least 5 countries with population over 10 Million.
  4. List population, GDP, population density for regions of the world in order of GDP.
  5. Create a histogram showing number of countries in following population ranges of 10 Million.

Lab. 3) Nested queries

  1. Find names of countries with GDP more than the total GDP of Africa.
  2. Find the region with largest GDP. Do not use MAX. You will need SUM to compute region totals.
  3. Find the countries in Asia with population larger than the largest population of North American countries.

Lab. 4) Insert, update and delete

  1. Examine the rows with non-zero area but zero gdp and population. Delete these rows assuming these do not constitute countries.
  2. Determine the region for Mauritius. Update region for Mauritius to be Africa.
  3. Update population of United States to reflect 5 percent increase per year since 1995.
  4. Hong Kong merged with China in 1997. Add the population, area, gdp of Hong Kong to China. Delete Hong Kong.
  5. Insert a new country, Korea in Asia region. Its area, population and gdp are sum of each in two countries, 'Korea, North' and 'Korea, South'. Delete 'Korea, North' and 'Korea, South' countries.