Adatbázis feladatok MONDIAL adatbázissal Horváth Árpád
2015. október 16.
Határozza meg, hány városban van több mint 500 ezer lakos!
SELECT count(name) FROM city WHERE population > 500000; 468-ban.
SELECT majdnem általálnosan
SELECT (kifejezés [AS alias]|*),... FROM (táblaszer˝ uségek [AS alias]),... WHERE feltétel GROUP BY mez˝ onév,... HAVING csoportosítás_utáni_feltétel ORDER BY (mez˝ onév [DESC]),... LIMIT szám;
Határozza meg, hány országban van olyan város, amelynek több mint 500 ezer lakosa van!
SELECT count(DISTINCT country) FROM city WHERE population > 500000; 468-ban.
Listázza ki azokat az országokat, amelyek lakossága kisebb, mint Budapesté! A lekérdezés olyan legyen, hogyha változik Budapest és az országok lakossága, akkor is helyesen muködjön. ˝ ˝ ˝ A következo mezok szerepeljenek a lekérdezésben: country.name country.population A rendezés a következo˝ szerint legyen: population növekvo˝
SELECT country.name, country.population FROM city, country WHERE city.name = ’Budapest’ AND country.population < city.population ORDER BY country.population;
name
Pitcairn Islands Cocos Islands Holy See (. . . ) Bhutan Kuwait Slovenia Lesotho
population
48 596 840 1822625 1950047 1951443 1970781
Listázza ábécérendben azokat az országokat, amelyben van olyan város, amelynek több mint 3 millió lakosa van! Rendezés ABC-rendben.
SELECT DISTINCT country.name FROM city, country WHERE city.population > 3000000 AND city.country = country.code ORDER BY country.name;
27 ilyen ország van: name
Australia Bangladesh Brazil Chile China Colombia Egypt Germany (. . . ) Spain Thailand Turkey United Kingdom United States Vietnam Zaire
27 ilyen ország van: name
Australia Bangladesh Brazil Chile China Colombia Egypt Germany Hong Kong India Indonesia Iran Iraq Japan Mexico Nigeria Pakistan
name
Peru Russia South Korea Spain Thailand Turkey United Kingdom United States Vietnam Zaire
˝ Határozza meg a 2 millió fonél több lakosú városok listáját. ˝ Szerepeljen rajta a város neve, a lakossága ezer foben egészre kerekítve, és az ország neve. Rendezze csökkeno˝ népességsorrendbe a városokat!
SELECT city.name, round(city.population/1000) AS population1000, country.name FROM city, country WHERE city.population > 2000000 AND city.country = country.code ORDER BY city.population DESC;
város Seoul Mumbai Karachi Mexico City Sao Paulo Moscow Jakarta Tokyo Shanghai Istanbul New York New Delhi Hong Kong Beijing London Tehran Lima Cairo Bangkok
˝ lakosság (ezer fo) 10229 9926 9863 9816 9812 8717 8259 7843 7830 7616 7323 7207 7055 7000 6968 6750 6321 6053 5876
ország South Korea India Pakistan Mexico Brazil Russia Indonesia Japan China Turkey United States India Hong Kong China United Kingdom Iran Peru Egypt Thailand
város Tianjin Lagos Rio de Janeiro Bogota Lahore Sankt Peterburg Kinshasa Shenyang Baghdad Calcutta Santiago Saigon Madras Dhaka Pusan Wuhan Sydney Guangzhou Los Angeles
˝ lakosság (ezer fo) 5770 5686 5533 5238 5085 4838 4655 4540 4478 4400 4318 3924 3841 3839 3814 3750 3657 3580 3486
ország China Nigeria Brazil Colombia Pakistan Russia Zaire China Iraq India Chile Vietnam India Bangladesh South Korea China Australia China United States
város Berlin Bangalore Yokohama Hyderabad Melbourne Hanoi Madrid Buenos Aires Chongqing Ahmadabad Casablanca Alexandria Harbin Chengdu Rome Chicago Ankara Xian Taipei
˝ lakosság (ezer fo) 3472 3302 3256 3146 3081 3056 3041 2988 2980 2955 2941 2917 2830 2810 2791 2784 2782 2760 2626
ország Germany India Japan India Australia Vietnam Spain Argentina China India Morocco Egypt China China Italy United States Turkey China Taiwan
város Kiev Singapore Rangoon Nanjing Osaka Surabaya Zibo Taegu Dalian Pyongyang Jinan Addis Ababa Inchon Havana Salvador Paris Changchun Nagoya Tashkent
˝ lakosság (ezer fo) 2616 2558 2513 2500 2492 2484 2460 2449 2400 2335 2320 2316 2308 2241 2209 2152 2110 2108 2106
ország Ukraine Singapore Myanmar China Japan Indonesia China South Korea China North Korea China Ethiopia South Korea Cuba Brazil France China Japan Uzbekistan
város Belo Horizonte Qingdao Bandung Bucharest Budapest
(81 rows)
˝ lakosság (ezer fo) 2092 2060 2059 2037 2016
ország Brazil China Indonesia Romania Hungary
Listázza azokat a városokat, amelyek tenger (óceánt is beleértve) partján is fekszenek, és folyó partján is. Amely városok nem fekszenek tenger partján, ott a sea értéke NULL, hasonlóan a folyónál. A város neve mellett szerepeljen az ország kódja, a tenger és a folyó neve is. A tenger neve szerint legyen rendezve.
SELECT city, country, sea, river FROM located WHERE sea IS NOT NULL AND river IS NOT NULL ORDER BY sea;
17 ilyen szerepel az adatbázisban: city
country
sea
river
Moulmein Banjul New York Belem Lisbon Porto Vila Nova de Gaia Riga Gdansk Kotka Oulu Arkhangelsk Xai Xai Goteborg Southend on Sea Bremerhaven Le Havre
MYA WAG USA BR P P P LV PL SF SF R MOC S GB D F
Andaman Sea Atlantic Ocean Atlantic Ocean Atlantic Ocean Atlantic Ocean Atlantic Ocean Atlantic Ocean Baltic Sea Baltic Sea Baltic Sea Baltic Sea Barents Sea Indian Ocean Kattegat North Sea North Sea The Channel
Saluen Gambia Hudson River Tocantins Tajo Douro Douro Western Dwina Weichsel Kymijoki Oulujoki Northern Dwina Limpopo Goetaaelv Thames Weser Seine
Ezek már tényleg programozós feladatok, Python ismeret szükséges hozzá
Osszuk fel a Föld felszínét 8 szeletre hosszúsági fokok szerint a ˝ ˝ következoképpen. Az elsoben legyenek a −180o és −135o fok o közöttiek, majd így tovább 45 -onként. Kérdések következnek.
Elso˝ kérdés
Hány 2 milliósnál nagyobb város található egy-egy ilyen szeletben. Adja meg a számok listáját: hosszúság darab
−180.. − 135 −135.. − 90 −90.. − 45 −45..0 0..45 45..90 90..135 135..180
A PostgreSQL-ben PL/Python nyelven definiált függvényével alábbi módon oldható meg:
CREATE FUNCTION eloszlas(poplimit integer) RETURNS int[] AS $$ lowlimits = range(-180, 179, 45) sql_query_template = """SELECT count(name) FROM city WHERE longitude BETWEEN {} and {} AND population > %d""" % poplimit sql_query = sql_query_template.format(lim,lim+45) result = plpy.execute(sql_query) values = [result[0][’count’] for lim in lowlimits ] return values $$ LANGUAGE plpythonu;
A kapott értékek
hosszúság
darab
−180.. − 135 −135.. − 90 −90.. − 45 −45..0
0 2 8 6 16 10 23 4
0..45 45..90 90..135 135..180
˝ Válassza ki az elobbi szeletek legnagyobb városait. hosszúság legnagyobb város neve
−180.. − 135 −135.. − 90 −90.. − 45 −45..0 0..45 45..90 90..135 135..180
DROP FUNCTION legnagyobb(); CREATE FUNCTION legnagyobb() RETURNS varchar(30)[] AS $$ sql_query_template = """SELECT name FROM city WHERE longitude BETWEEN {0} AND {1} AND population (SELECT max(population) FROM city WHERE longitude BETWEEN {0} AND {1} ) < 3 """ values = [] step = 45 for minpop in range(-180, 179, step): sql_query = sql_query_template.format(minpop,minpop+step) biggest_city = plpy.execute(sql_query)[0][’name’] values.append(biggest_city) return values $$ LANGUAGE plpythonu;
A kapott eredmények
hosszúság
legnagyobb város neve
−180.. − 135 −135.. − 90 −90.. − 45 −45..0
Apia Guatemala City Nassau Madrid Luxemburg Kuwait Singapore Tokyo
0..45 45..90 90..135 135..180
