Lab 2: Simple SQL Queries

PART A : Preliminaries

• Review a simple script illustrating invocation of sqlplus client, login into oracle system.
• Make sure to change your password immediately after your first login.
• Review basic commands for sqlplus using a tutorial from Ullman.  Review commands to carry out the following tasks:
  1. Listing columns of a table.
  2. listing all the user-defined tables
  3. Recording your session
  4. Executing sql commands listed in a file
  5. Editing Commands

PART B : Simple SQL Queries

• Download country.sql in your working directory and review it.
• Execute the SQL commands in this file by typing in "@" command in sqlplus.
  
  SQL>@country.sql
  
  
• Write SQL queries for the queries listed in following subsections. Submit these SQL expressions and query results in a recorded session using typescript and spooling.

1) Selection and Projection

1. List names from rows where name contains 'Island'.
2. List all columns from rows where area is greater than 10,000,000.
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) North American and South American countries sorted by per capita GDP.

2) Summarization, Aggregation

1. Count number of countries with area between 1.5 Million Sq. Km. and 2 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 population.
5. Create a table showing successive ranges of populations (with each range being 10 million apart) and the number of countries whose populations fall within that range. For example:
   
   

   population	#_of_countries
   0 - 10 million	(number of countries whose population falls within this range)
   10 - 20 million	(number of countries whose population falls within this range)

   etc.

3) Nested queries

1. Find names of countries with GDP more than the average GDP of Europe.
2. Find the region with smallest NON-ZERO GDP. You will need SUM to compute region totals.
3. Find the countries in Europe with population smaller than the average population of South American countries.

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. Change the region from "Commonwealth of Independent States - ..." to "Former Soviet Union"
3. Update population of France to reflect 7 percent decrease 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 the Asia region. Its area, population and gdp are the sum of each in the two countries, 'Korea, North' and 'Korea, South'. Delete 'Korea, North' and 'Korea, South' countries.

5) Table Definitions, Integrity Constraints

1. Review the create table statement defining country table in coutry.sql file. Suggest alternative data types for the columns to reduce the storage space requirements and improve data quality. Briefly justify your suggestions.