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:
Listing columns of a table.
listing all the user-defined tables
Recording your session
Executing sql commands listed in a file
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
List names from rows where name contains 'Island'.
List all columns from rows where area is greater than
10,000,000.
List names of all regions. The list should not have any
duplicates.
List names of populous (i.e. population over 10 Million)
countries.
List names of populous (i.e. population over 10 Million)
North American and South American countries sorted by per
capita GDP.
2) Summarization, Aggregation
Count number of countries with area between 1.5 Million
Sq. Km. and 2 Million Sq. Km.
List number of countries, total area, average area per
country for all regions of the world.
Select regions which have at least 5 countries with
population over 10 Million.
List population, GDP, population density for regions of
the world in order of population.
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
Find names of countries with GDP more than the average
GDP of Europe.
Find the region with smallest NON-ZERO GDP. You will need
SUM to compute region totals.
Find the countries in Europe with population smaller than
the average population of South American countries.
4) Insert, update and delete
Examine the rows with non-zero area but zero gdp and
population. Delete these rows assuming these do not
constitute countries.
Change the region from "Commonwealth of Independent
States - ..." to "Former Soviet Union"
Update population of France to reflect 7 percent decrease
per year since 1995.
Hong Kong merged with China in 1997. Add the population,
area, gdp of Hong Kong to China. Delete Hong Kong.
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
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.