SQL> spool lab1rupa; 1.1 SQL> select name, population from country where region = 'South America' order by population; NAME POPULATION -------------------------------------------------- ---------- Falkland Islands (Islas Malvinas) 2317 French Guiana 145270 Suriname 429544 Guyana 723774 Uruguay 4222716 Paraguay 6358198 Bolivia 8896254 Ecuador 11890950 Chile 15161216 Venezuela 22004773 Peru 25087372 NAME POPULATION -------------------------------------------------- ---------- Argentina 35292742 Colombia 37200251 Brazil 161737489 14 rows selected. 1.2 SQL> select * from country where region = 'North America' and population < 10000000; NAME -------------------------------------------------- REGION AREA ------------------------------------------------------------ ---------- POPULATION GDP ---------- ---------- Saint Pierre and Miquelon North America 242 6757 66000000 Bermuda North America 50 61629 1700000000 NAME -------------------------------------------------- REGION AREA ------------------------------------------------------------ ---------- POPULATION GDP ---------- ---------- 1.3 SQL> select name, gdp, area from country where gdp < 6000000000 and gdp > 5000000000 order by area desc; NAME GDP AREA -------------------------------------------------- ---------- ---------- Namibia 5800000000 825418 Gabon 5600000000 267670 Haiti 5600000000 27750 Mali 5400000000 1 1.4 SQL> select distinct region from country where name like '%x%'; REGION ------------------------------------------------------------ Europe North America SQL> select distinct region,name from country where name like '%x%'; REGION ------------------------------------------------------------ NAME -------------------------------------------------- Europe Luxembourg North America Mexico 2.1 SQL> select distinct count(*) from country where region = 'Africa'; COUNT(*) ---------- 59 2.2 SQL> select avg(area) from country where region='Central America and the Caribbean'; AVG(AREA) ---------- 23715.25 2.3 SQL> select region from country where population > 100000000 group by region having count(*) >=5; REGION ------------------------------------------------------------ Asia 2.4 SQL> select region , sum(gdp)/sum(population) from country where population > 0 group by region order by sum(gdp)/sum(population) desc; REGION ------------------------------------------------------------ SUM(GDP)/SUM(POPULATION) ------------------------ North America 20828.686 Europe 15955.2562 Oceania 14730.6655 REGION ------------------------------------------------------------ SUM(GDP)/SUM(POPULATION) ------------------------ Arctic Region 13781.5713 World 5477.50276 South America 5408.7331 REGION ------------------------------------------------------------ SUM(GDP)/SUM(POPULATION) ------------------------ Middle East 4903.17415 Ethnic Groups in Eastern Europe, Europe 3842.60002 Southeast Asia 3570.23052 REGION ------------------------------------------------------------ SUM(GDP)/SUM(POPULATION) ------------------------ Commonwealth of Independent States - European States 3364.90169 Asia 3011.44485 Central America and the Caribbean 2719.637 REGION ------------------------------------------------------------ SUM(GDP)/SUM(POPULATION) ------------------------ Commonwealth of Independent States - Central Asian States 2389.40072 Africa 1472.4112 14 rows selected. 3.1 SQL> INSERT INTO country (name, area, population) VALUES ('CCC', 9999, 1); 1 row created. 3.2 SQL> UPDATE country SET population = (population + 1000000) WHERE gdp > 10000000000; 109 rows updated. 3.3 SQL> delete from country where region is null; 1 row deleted. SQL> spool off; SQL> quit Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bit Production With the Partitioning, OLAP and Data Mining options tiwrupa@cello (/home/grad04/tiwrupa) %