Homework 1 ANSWERS 1- number of records SELECT Count(*) FROM wb; COUNT(*) ---------- 152 ************************************************************************ 2- totals and averages of population, area, GNI (one query to get these 6 values) SELECT Sum(population),Avg(population),Sum(area),Avg(area),Sum(gni),Avg(gni) FROM wb; SUM(POPULATION) AVG(POPULATION) SUM(AREA) AVG(AREA) SUM(GNI) AVG(GNI) --------------- --------------- ---------- ---------- ---------- ---------- 6154 40.4868421 132764 885.093333 31026.4 221.617143 ************************************************************************ 3- name, population and GNI of records with null surface_areas SELECT name,population,gni FROM wb WHERE area IS NULL; NAME POPULATION GNI ------------------------------------------------------------------ ---------- ---------- HongKongChina 7 167.6 WestBankandGaza 3 3.6 ************************************************************************ 4- number of records with null GNI fields SELECT Count(*) FROM wb WHERE gni IS NULL; COUNT(*) ---------- 12 ************************************************************************ 5- the names and GNI of the 10 states with the largest GNI in descending order SELECT * FROM (SELECT name,gni FROM wb WHERE gni IS NOT NULL ORDER BY gni DESC) WHERE Rownum<=10; NAME GNI ------------------------------------------------------------------ ---------- UnitedStates 10207 Japan 4323.9 Germany 1876.3 UnitedKingdom 1510.8 France 1362.1 China 1234.2 Italy 1100.7 Canada 702 Mexico 597 Spain 596.5 ************************************************************************ 6- the five poorest states (lowest GNI) for which there is data SELECT * FROM (SELECT name,gni FROM wb WHERE gni IS NOT NULL ORDER BY gni) WHERE Rownum<=5; NAME GNI ------------------------------------------------------------------ ---------- Guinea-Bissau .2 GambiaThe .4 Liberia .5 Burundi .7 SierraLeone .7 ************************************************************************ 7- number of states with negative GDP growth rates SELECT Count(*) FROM wb WHERE gdpd<0; COUNT(*) ---------- 18 ************************************************************************ 8- the names and areas of the 10 biggest states in land area SELECT * FROM (SELECT name,area FROM wb WHERE area IS NOT NULL ORDER BY area DESC) WHERE Rownum<=10; NAME AREA ------------------------------------------------------------------ ---------- RussianFederation 17075 Canada 9971 UnitedStates 9629 China 9598 Brazil 8547 Australia 7741 India 3287 Argentina 2780 Kazakhstan 2725 Sudan 2506 ************************************************************************ 9- the sum of the areas of the 10 biggest states (using a datum from the previous query) SELECT Sum(area) FROM wb WHERE area>=2506; SUM(AREA) ---------- 73859 ************************************************************************ 10- names of states containing two r's in any mix of case SELECT name FROM wb WHERE Lower(name) LIKE '%r%r%'; NAME ------------------------------------------------------------------ CentralAfricanRepublic EgyptArabRep. Eritrea IranIslamicRep. KoreaDem.Rep. KoreaRep. KyrgyzRepublic PuertoRico RussianFederation SerbiaandMontenegro SierraLeone SyrianArabRepublic UnitedArabEmirates 13 rows selected. ************************************************************************ 11- names of states containing two consecutive same vowels, i.e. aa,ee,ii,oo, or uu SELECT name FROM wb WHERE name LIKE '%aa%' OR name LIKE '%ee%' OR name LIKE '%ii%' OR name LIKE '%oo%' OR name LIKE '%uu%'; NAME ------------------------------------------------------------------ BosniaandHerzegovina Cameroon Greece SerbiaandMontenegro ************************************************************************ 12- names of states whose names have 'land' SELECT name FROM wb WHERE name LIKE '%land%'; NAME ------------------------------------------------------------------ Finland Ireland Netherlands NewZealand Poland Swaziland Switzerland Thailand 8 rows selected. ************************************************************************ 13- names of states whose names contain 'ia' but do not end in 'ia' SELECT name FROM wb WHERE name LIKE '%ia%' AND name NOT LIKE '%ia'; NAME ------------------------------------------------------------------ BosniaandHerzegovina GambiaThe MacedoniaFYR RussianFederation SerbiaandMontenegro SyrianArabRepublic 6 rows selected. ************************************************************************ 14- names of the states with the shortest names (you can use 2 queries to do this) SELECT Min(Length(name)) FROM wb; MIN(LENGTH(NAME)) ----------------- 4 SQL> SELECT name FROM wb WHERE Length(name)=4; NAME ------------------------------------------------------------------ Chad Cuba Iraq Mali Oman Peru Togo 7 rows selected. ************************************************************************ 15- the names,population,area and density of the 10 most densely populated states. density in people per sq km is derived from population/area*1000. name that column Denstiy. use the round or trunc function to round the density values. SELECT * FROM (SELECT name,population,area,Trunc(population/area*1000,0) AS density FROM wb WHERE area IS NOT NULL ORDER BY density DESC) WHERE Rownum<=10; NAME POPULATION AREA DENSITY ------------------------------------------------------------------ ---------- ---------- ---------- Singapore 4 1 4000 Bangladesh 136 144 944 Mauritius 1 2 500 KoreaRep. 48 99 484 PuertoRico 4 9 444 Lebanon 4 10 400 Netherlands 16 42 380 Japan 127 378 335 Israel 7 21 333 Belgium 10 31 322 10 rows selected. ************************************************************************ 16- the names,population,area and density of the 10 least densely populated states for which there is appropriate data, in order by density SELECT * FROM (SELECT name,population,area,Trunc(population/area*1000,0) AS density FROM wb WHERE area IS NOT NULL ORDER BY density) WHERE Rownum<=10; NAME POPULATION AREA DENSITY ------------------------------------------------------------------ ---------- ---------- ---------- Mongolia 2 1567 1 Australia 20 7741 2 Libya 5 1760 2 Namibia 2 824 2 Mauritania 3 1026 2 Botswana 2 582 3 Canada 31 9971 3 Gabon 1 268 3 Kazakhstan 15 2725 5 CentralAfricanRepublic 4 623 6 ************************************************************************ 17- name, population, percapita GNI of the 10 states with the highest percapita GNI in dollars per person(derived from GNI/population*1000) in order of percapita GNI SELECT * FROM (SELECT name,population,gni,Trunc(gni/population*1000,0) AS gnipercapita FROM wb WHERE gni IS NOT NULL ORDER BY gnipercapita DESC) WHERE Rownum<=10; NAME POPULATION GNI GNIPERCAPITA ------------------------------------------------------------------ ---------- ---------- ------------ Switzerland 7 263.7 37671 UnitedStates 288 10207 35440 Norway 5 175.8 35160 Japan 127 4323.9 34046 Denmark 5 162.6 32520 Sweden 9 231.8 25755 UnitedKingdom 59 1510.8 25606 Finland 5 124.2 24840 Austria 8 192.1 24012 HongKongChina 7 167.6 23942 ************************************************************************ 18- name, population, percapita GNI of the 10 states with the lowest percapita GNI for which there is data, in order of percapita GNI SELECT * FROM (SELECT name,population,gni,Trunc(gni/population*1000,0) AS gnipercapita FROM wb WHERE gni IS NOT NULL ORDER BY gnipercapita) WHERE Rownum<=10; NAME POPULATION GNI GNIPERCAPITA ------------------------------------------------------------------ ---------- ---------- ------------ CongoDem.Rep. 52 5 96 Ethiopia 67 6.5 97 Burundi 7 .7 100 SierraLeone 5 .7 140 Malawi 11 1.7 154 Liberia 3 .5 166 Niger 11 2 181 Tajikistan 6 1.1 183 Eritrea 4 .8 200 Mozambique 18 3.6 200 ************************************************************************ 19- names,GNI,PPP_GNI, and difference between these two GNI values of the states whose GNI is larger than its PPP_GNI SELECT name,gni,pppgni,gni-pppgni FROM wb WHERE gni>pppgni ; NAME GNI PPPGNI GNI-PPPGNI ------------------------------------------------------------------ ---------- ---------- ---------- Japan 4323.9 3481 842.9 Norway 175.8 166 9.8 Sweden 231.8 230 1.8 Switzerland 263.7 232 31.7 ************************************************************************ 20- names,PPP_GNI,GNI and percentage that PPP_GNI is of GNI for the states whose such percentage is more than 500 (i.e. their PPP_GNI is more than 5 times their GNI), ordered by this percentage. Round it to the nearest integer. SELECT name,pppgni,gni,Trunc(pppgni/gni*100) AS "PPPtoGNI%" FROM wb WHERE pppgni/gni>5 ORDER BY "PPPtoGNI%" DESC; NAME PPPGNI GNI PPPtoGNI% ------------------------------------------------------------------ ---------- ---------- ---------- Ethiopia 52 6.5 800 Ghana 42 5.5 763 Cambodia 25 3.8 657 CongoDem.Rep. 32 5 640 Mauritania 5 .8 625 Ukraine 234 37.9 617 Nepal 33 5.5 600 KyrgyzRepublic 8 1.4 571 Burundi 4 .7 571 India 2778 494.8 561 Uganda 33 5.9 559 Rwanda 10 1.8 555 Tajikistan 6 1.1 545 Togo 7 1.3 538 Vietnam 185 34.8 531 LaoPDR 9 1.7 529 Uzbekistan 41 7.8 525 17 rows selected. ************************************************************************ 21- frequency count of the number of names of different lengths. I.e. number of names of length 22, number of length 21 etc. Hint: group by length of name. Order by the lengths. SELECT Length(name),Count(*) FROM wb GROUP BY Length(name) ORDER BY Length(name); LENGTH(NAME) COUNT(*) ------------ ---------- 4 7 5 15 6 21 7 34 8 18 9 14 10 11 11 9 12 3 13 7 14 3 15 2 17 3 18 2 19 1 20 1 22 1 17 rows selected. ************************************************************************ 22- frequency count of the number of names that start with each letter. I.e. number of names that start with A, number of names that start with B etc. Hint: group by leftmost 1 character of name using the Substr() function. Display in decreasing order by count. SELECT Substr(name,1,1),Count(*) FROM wb GROUP BY Substr(name,1,1) ORDER BY Count(*) DESC; SUB COUNT(*) --- ---------- S 16 C 15 M 13 B 11 P 9 G 9 A 9 N 8 T 8 I 7 U 7 L 7 E 6 K 6 H 4 R 3 J 3 F 2 D 2 Z 2 V 2 W 1 Y 1 O 1 24 rows selected.