Jarekz: Utworzono nową stronę "= TI:WTBD/Ćwiczenia 14 = == Sprawdzian 2 - SQL. == === Grupa 1 === Plik Pl_lud_2010.txt zawiera dane o ludności Polski (wg. GUS) na koni..."

2015-05-23T15:06:57Z

Utworzono nową stronę "= TI:WTBD/Ćwiczenia 14 = == Sprawdzian 2 - SQL. == === Grupa 1 === Plik Pl_lud_2010.txt zawiera dane o ludności Polski (wg. GUS) na koni..."

Nowa strona

= [[TI:WTBD]]/Ćwiczenia 14 =
== Sprawdzian 2 - SQL. ==
=== Grupa 1 ===

Plik [[media:Pl_lud_2010.txt|Pl_lud_2010.txt]] zawiera dane o ludności Polski (wg. GUS) na koniec 2010 r., w rozbiciu na powiaty. Dane powiatów pogrupowane są w bloki, odpowiadające województwom, do których należą.

1. Proszę napisac program, który na podstawie tego pliku tworzy bazę SQLite z tabelą POWIATY i tabelą WOJEWODZTWA, wraz ze związkiem opisującym przynależność każdego powiatu do jednego z województw, oraz informacją o populacji każdego z powiatów.

2. Napisać zapytania SQL odpowiadające na pytania:

a) ile wynosiła populacja najbardziej i najmniej ludnego z województw (oraz jakie były ich nazwy)

b) dla każdego województwa, ile wynosiła średnia liczba ludności dla jego powiatów

c) ile wynosił stosunek ludności powiatów miejskich (grodzkich; tj. nazwanych nazwą miasta) do całkowitej ludności Polski

3. Włączyć te zapytania do powyższego programu jako wykonywane w zależności od argumentu wywołania

Wynikiem powinien być pojedynczy plik 'ludnosc.py', którego wywołanie z argumentem ''create'' tworzy i wypełnia tabele, a z argumentami ''q1'', ''q2'' i ''q3'' podaje odpowiednio wyniki zapytania a), b) i c).

=== przykład rozwiązania dla zadania gr. 1 ===

<source lang="python">
#! /usr/bin/python
# coding: utf-8

DEF_TABEL = ('''
create table if not exists WOJEWODZTWA
(ID int primary key, NAZWA)''',
'''
create table if not exists POWIATY
(ID int primary key, NAZWA, W_ID references WOJEWODZTWA, LUDNOSC int)'''
)

def wstaw_dane(conn, plik):
lines = open(plik)
w_id, p_id = 0, 0
for l in lines:
l = l.decode('utf-8')
if not l.startswith(u'\t'): # to jest nagłówek, początek danych dla kolejnego województwa
w_id += 1
w_nazwa = l.strip(u':\n')
conn.execute('''
insert into WOJEWODZTWA (ID, NAZWA) values (?,?)
''', (w_id, w_nazwa))
else: # to jest linijka opisująca powiat aktualnego województwa
row = l.strip().split(u'|')
if len(row) == 2:
p_id += 1
conn.execute('''
insert into POWIATY (ID, W_ID, NAZWA, LUDNOSC) values (?,?,?,?)
''', (p_id, w_id, row[0], int(row[1])))
conn.commit()
lines.close()

QUERIES = ( '''select w.NAZWA, sum(p.LUDNOSC) as POPULACJA
from WOJEWODZTWA w
join POWIATY p on w.ID=p.W_ID
group by w.NAZWA
having POPULACJA=(
select sum(LUDNOSC)
from POWIATY
group by W_ID
order by sum(LUDNOSC) desc
limit 1)
or POPULACJA=(
select sum(LUDNOSC)
from POWIATY
group by W_ID
order by sum(LUDNOSC) asc
limit 1)
order by POPULACJA desc''', # zapytanie dla podpunktu a)

'''select w.NAZWA, sum(p.LUDNOSC)/count(*) as SREDNIA_POWIATU
from WOJEWODZTWA w
join POWIATY p on w.ID=p.W_ID
group by w.NAZWA
order by w.NAZWA
''', # zapytanie dla podpunktu b)

'''select 1.*sum(p.LUDNOSC)/(select sum(LUDNOSC) from POWIATY) as ULAMEK_W_P_MIEJSKICH
from POWIATY p
where p.NAZWA like 'm. %' ''' # zapytanie dla podpunktu c)
)

if __name__ == '__main__':
from sqlite3 import connect
from sys import argv
conn = connect('pl_lud.db')
if argv[1] == 'create':
for command in DEF_TABEL:
print command
conn.execute(command)
print u'... wykonane'.encode('utf-8')
wstaw_dane(conn, 'pl_lud_2010.txt')
elif argv[1] == 'drop':
conn.execute('drop table if exists POWIATY')
conn.execute('drop table if exists WOJEWODZTWA')
elif argv[1] in ('q1', 'q2', 'q3'):
c = conn.execute(QUERIES[int(argv[1][1])-1])
result = [list(x[0] for x in c.description)]
result.extend(c.fetchall())
for line in result: # nie silimy się na specjalnie wymyślną prezentację wyniku ..
print u' | '.join(unicode(x) for x in line).encode('utf-8')
else:
print >> sys.stderr, u'Błędny argument.'.encode('utf-8')
conn.close()

</source>

=== Grupa 2 ===

Plik [[media:Pl_lud_2010.csv.txt|Pl_lud_2010.csv]] zawiera dane o ludności Polski (wg. GUS) na koniec 2010 r., w rozbiciu na powiaty. Linijki zaczynające się od '##' otwierają
bloki odpowiadające poszczególnym województwom.

1. Proszę napisac program, który na podstawie tego pliku tworzy bazę SQLite z tabelą
POWIATY i tabelą WOJEWODZTWA, wraz ze związkiem opisującym przynależność
każdego powiatu do jednego z województw, oraz informacją o liczbie ludności
każdego z powiatów.

2. Napisać zapytania SQL odpowiadające na pytania:

a) ile wynosiła liczba ludności każdego z województw (w kolejności malejącej liczby)

b) jaki był powiat o największej liczbie ludności w każdym z województw (jego nazwa
i liczba ludności) w kolejności alfabetycznej wg. nazw województw

c) jaka część powiatów w kraju miała zaludnienie przekraczające średnią dla powiatu
w kraju

3. Włączyć te zapytania do powyższego programu jako wykonywane w zależności
od argumentu wywołania

Wynikiem powinien być pojedynczy plik 'ludnosc.py', którego wywołanie z argumentem
'create' tworzy i wypełnia tabele, a z argumentami 'q1', 'q2' i 'q3' podaje
odpowiednio wyniki zapytania a), b) i c).

=== przykład rozwiązania dla gr. 2 ===

<source lang="python">
#! /usr/bin/python
# coding: utf-8

DEF_TABEL = ('''
create table if not exists WOJEWODZTWA
(ID int primary key, NAZWA)''',
'''
create table if not exists POWIATY
(ID int primary key, NAZWA, W_ID references WOJEWODZTWA, LUDNOSC int)'''
)

def wstaw_dane(conn, plik):
lines = open(plik)
w_id, p_id = 0, 0
for l in lines:
row = tuple(x.strip() for x in l.decode('utf-8').split(','))
if row[0].startswith('##'): # początek kolejnego województwa
w_id += 1
conn.execute('''
insert into WOJEWODZTWA (ID, NAZWA) values (?,?)
''', (w_id,) + row[1:])
else: # kolejny powiat z aktualnego województwa
p_id += 1
conn.execute('''
insert into POWIATY (ID, W_ID, NAZWA, LUDNOSC) values (?,?,?,?)
''', (p_id, w_id, row[0], int(row[1])))
conn.commit()
lines.close()

QUERIES = ('''
select w.NAZWA, sum(p.LUDNOSC) as SUMA_LUDNOSCI
from POWIATY p join WOJEWODZTWA w on w.ID=p.W_ID
group by w.NAZWA
order by SUMA_LUDNOSCI desc''', # zapytanie dla podpunktu a)

'''
select w.NAZWA as NAZWA_W, p.NAZWA as NAZWA_P, p.LUDNOSC as LUDNOSC_MAX
from WOJEWODZTWA w join POWIATY p on w.ID=p.W_ID
where p.LUDNOSC=(
select max(LUDNOSC) from POWIATY where W_ID=w.ID)
order by w.NAZWA;
''', # zapytanie dla podpunktu b)

'''
select 1.*count(*)/(select count(*) from POWIATY) as DUZYCH_POWIATOW
from POWIATY
where LUDNOSC > (select sum(LUDNOSC)/count(*) from POWIATY)
''' # zapytanie dla podpunktu c)
)

if __name__ == '__main__':
from sqlite3 import connect
from sys import argv
conn = connect('pl_lud.db')
if argv[1] == 'create':
for command in DEF_TABEL:
print command
conn.execute(command)
wstaw_dane(conn, 'pl_lud_2010_00_03.csv')
elif argv[1] == 'drop': # tego nie musiało być, ale się przydaje
conn.execute('drop table if exists POWIATY')
conn.execute('drop table if exists WOJEWODZTWA')
elif argv[1] in ('q1', 'q2', 'q3'):
c = conn.execute(QUERIES[int(argv[1][1])-1])
result = [list(x[0] for x in c.description)]
result.extend(c.fetchall())
for line in result: # nie silimy się nadmiernie w prezentacji wyniku ...
print u' | '.join(unicode(x) for x in line).encode('utf-8')
else:
print >> sys.stderr, u'Błędny argument.'.encode('utf-8')
conn.close()

</source>

/Cw14 - Historia wersji

Jarekz: Utworzono nową stronę "= TI:WTBD/Ćwiczenia 14 = == Sprawdzian 2 - SQL. == === Grupa 1 === Plik Pl_lud_2010.txt zawiera dane o ludności Polski (wg. GUS) na koni..."