Lab 1 : 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 fromUllman. 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 '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.

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.

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.

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.

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.