Lab 1 : SQL Review

This lab has several parts. You are expected to submit following:

• SQL expressions for 8 queries in PART B, choosing two queries from each category.
• Answers to all questions in PART C.

PART A : Preliminaries

• Review a simple script illustrating invocation of SQL*Plus client, login into oracle server and etc. Try these steps using your own oracle account.
• Change your password using "ALTER USER user_name IDENTIFIED BY new_password;" command in SQL*Plus.
• Review basic SQL*Plus commands for SQL*Plus using a tutorial from Ullman . List commands to carry out the following tasks:
  1. Help
  2. List columns of a table.
  3. Record(spool) your session
  4. Execute SQL commands listed in a file
  5. List all the user-defined tables

PART B : SQL Review

• Download and review country.sql SQL script file.
• Execute the SQL commands in the file by typing "@country" in the command line of SQL*Plus.
• Review the table created via "SELECT * FROM USER_TABLES" and "DESCRIBE" commands.
• Write SQL queries for 8 of the following queries choosing 2 from each sub group.
• This laboratory is invaluable particularly if you have never worked with SQL. It provides a quick review if you have worked with SQL before. Familiarity with SQL is essential to appreciate the material in Csci 5708.

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.

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.

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.

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.

PART C : Data Dictionary in Oracle

1. Write an SQL expression to get information(TABLE_NAME, TABLESPACE_NAME) for the tables whose owner is you.
2. Execute the SQL statement "ANALYZE TABLE country COMPUTE STATISTICS;"
3. Write an SQL expression to determine the number of rows, blocks, average row length for each user table.
4. Write an SQL expression to determine the number of distinct values, number of nulls for each column of COUNTRY table.
   
   
5. How many indices are defined on COUNTRY table? (Hint: Use system catalog)
6. Review the SQL script in country.sql. Does it include "CREATE INDEX ..." statement? Why did Oracle create an index on COUNTRY table?
7. Create a secondary index on COUNTRY.POPULATION field using SQL.
8. Write an SQL expression to list names and properties of user-defined indices. (Hint: list INDEX_NAME, INDEX_TYPE, TABLE_NAME and UNIQUENESS columns in the appropriate tables in the system catalog.)
9. Write an SQL expression to determine blevel, leaf blocks, distinct keys, average leaf blocks per key,average data blocks per key, clustering factor for each index on COUNTRY table.
   
   
10. Execute the SQL statement "CREATE VIEW country_view AS SELECT name, region FROM country;"
11. Write an SQL expression to list names and content of all views defined by your Oracle account. (Hint: Use columns named VIEW_NAME and TEXT from USER_VIEWS table in the system catalog.)

1. Write an SQL expression to list constraints (such as primary key, foreign key, etc.) defined on country table. (Hint: list table_name, constraint_name, constraint_type, constrainted column_names)

1. Write an SQL expression to list the priviledges a user named SCOTT gives to your Oracle account. (Hint: Use ALL_TAB_PRIVS system catolog. list table priviledges where GRANTOR ='SCOTT')