5  SQL-Anfragen

5.1 Grundlegende Anfragen

select real_name, created_at
from twitter_user tu
where typ = 'lobby' and 
date(created_at) <  timestamp '2009-06-30'
order by created_at

A 1.1

real_name	created_at
Sascha Lobo	2007-05-08 21:10:26
netzpolitik	2007-10-24 14:34:50
Ulrich Müller	2009-01-07 14:50:51
Mehr Demokratie e.V.	2009-04-02 19:36:30
CCC Updates	2009-04-16 14:04:59
abgeordnetenwatch.de	2009-04-25 04:14:23
LobbyControl	2009-05-07 14:48:55

select twitter_name, like_count
from twitter_user tu , tweet t 
where tu.id = t.author_id
and t.like_count between 22000 and 25000;

A 1.2

twitter_name	like_count
SWagenknecht	24656
MAStrackZi	22713
n_roettgen	22974
n_roettgen	24329

select distinct h.txt
from 
    twitter_user tu , 
    tweet t , 
    hashtag_posting hp , 
    hashtag h 
where tu.id = t.author_id  and 
    t.id = hp.tweet_id and
    hp.hashtag_id = h.id and 
    tu.real_name = 'LobbyControl' and 
    t.created_at between '2023-01-01' and '2023-01-15'
order by h.txt 

A 1.3

txt
ampel
autogipfel
autolobby
bundestag
eu
exxon
exxonknew
korruptionsskandal
lindner
lobbyregister

select *
from hashtag h
where h.id in (
    select hp1.hashtag_id 
    from hashtag_posting hp1, hashtag_posting hp2
    where hp1.tweet_id = hp2.tweet_id and
    hp1.hashtag_id = hp2.hashtag_id and not
    hp1.pos_start = hp2.pos_start
)

Anzahl der Ergebnisse:

A 1.4 Anzahl der Ergebnisse

count
437

select real_name, follower_count
from twitter_user
where created_at <= '2010-01-01'
and follower_count >= all (
    select follower_count
    from twitter_user
    where created_at <= '2010-01-01'
)

A 1.5

real_name	follower_count
Sascha Lobo	761419

5.2 String-Funktionen

select txt 
from tweet
where txt ilike '%openai%'
and retweet_count >= 20

A 2.1

txt
RT @_SilkeHahn: Guten Morgen: #OpenAI ist doch schon lange #ClosedAI. Seit der ersten Milliarde durch Microsoft 2019 lassen sie sich nicht mehr in die Karten schauen. @netzpolitik_org legt gekonnt den Finger in offene Wunden. Der “Technical Report” schweigt sich auf 98 https://t.co/ix1EYCAOKG… https://t.co/hAW5rbRUtH
RT @DrScheuch: Die meisten Aerosolforscher haben damals schon sehr starke Zweifel am Sinn von Ausgangssperren geäußert, (#openairstattausgangssperre) wurden aber nicht gehört. @Karl_Lauterbach, die NoCovid Modellierer und @janoschdahmen waren die lautesten Befürworter. https://t.co/bL7QMaoyKP

select txt, char_length(txt) as Laenge
from named_entity
where char_length(txt) >= all (
    select char_length(txt)
    from named_entity
)

A 2.2

txt	laenge
Arbeitsgemeinschaft Sozialdemokratischer Frauen	47

select txt
from named_entity
where char_length(txt) >= 4
and txt like reverse(txt) 

A 2.3

txt
DAAD
GASAG
CIMIC
ABBA

5.3 Exists-Operatoren

select *
from named_entity ne
where not exists (
    select *
    from named_entity_posting nep 
    where nep.named_entity_id = ne.id 
)

Anzahl der Ergebnisse:

A 3.1 Anzahl der Ergebnisse

count
621

select txt 
from tweet t
where exists (
    select *
    from hashtag_posting hp, hashtag h 
    where hp.hashtag_id = h.id 
    and t.id = hp.tweet_id 
    and h.txt = 'klima'
) and exists (
    select *
    from named_entity_posting nep, named_entity ne 
    where nep.named_entity_id = ne.id 
    and t.id = nep.tweet_id 
    and ne.txt = 'Berlin'
)

A 3.2

txt
😸 Erinnerung: Bis 31.01.2023 um 23:59 (Europe/Berlin 🕛) könnt ihr noch Themenvorschläge zur #nr23 machen. Z.B. zu #Klima-,#Sozial-,#Sport-,#Medizin-, #Lokal-, #Nonprofit- #Datenjournalismus, #crossborder #Diversität #Pressefreiheit etc.
Call for Papers: https://t.co/zyyNCsFC0y https://t.co/aDfQOzRG1W
RT @DieLinke_HH: Das #Klima retten, nicht den #Kapitalismus! Heute haben wir mit 12.000 anderen in #Hamburg beim #Klimastreik protestiert. 👏

Klare Ansage an Rot/Grün in Hamburg und die Ampel in Berlin: Der Stillstand bei der Klimapolitik muss aufhören! 🌿 https://t.co/jbfQrCcQuh | |So sieht die #Verkehrswende von @spdhh und @GRUENE_Hamburg aus🤦

Frei nach Fairy Ultra: Während in Berlin schon das #9EuroTicket anläuft, werden in #Hamburg noch die Preise erhöht

Soziale #Klimapolitik geht anders!

@9euroforever #Klima @LinksfraktionHH https://t.co/Fi0eC8km1M |

select tu.twitter_name , tu.real_name
from twitter_user tu 
where exists (
    select *
    from tweet t , conversation c 
    where t.author_id = tu.id and 
    t.id = c.id and 
    array_length(c.tweets, 1) >= 70 and 
    t.created_at >= '2023-02-15'
)

A 3.3

twitter_name	real_name
salomon_alex	Alexander Salomon
HendrikWuest	Hendrik Wüst

5.4 Aggregat-Funktionen und Gruppierung

select ne.id, ne.txt, count(*) as Anzahl
from named_entity ne , named_entity_posting nep 
where ne.id = nep.named_entity_id 
group by ne.id 
order by count(*) desc

Anzahl der Ergebnisse:

A 4.1 Anzahl der Ergebnisse

count
15744

select tu.real_name, count(*) as anzahl
from twitter_user tu , tweet t
where tu.id = t.author_id and 
tu.typ = 'politician' and 
t.created_at > '2022-01-01' and
tu.tweet_count > 2000 
group by tu.id
order by anzahl desc

Anzahl der Ergebnisse:

A 4.2 Anzahl der Ergebnisse

anzahl_der_ergebnisse
913

with erg(id, anzahl) as (
    select nep.tweet_id, count(*) anzahl
    from named_entity_posting nep, tweet t 
    where nep.tweet_id = t.id
    group by tweet_id 
    having count(*) >= all (
        select count(*)
        from named_entity_posting nep 
        group by tweet_id  
    )
)
select created_at, txt
from tweet
where id in (
  select id
  from erg
)

A 4.3

created_at	txt
2023-01-06 07:50:05	Adrian, Alexander, Ariturel, Björn, Christian, Christian, Christian, Christopher, Cornelia, Danny, Dirk, Frank, Heiko, Johannes, Kai, Katharina, Kurt, Maik, Martin, Michael, Oliver, Robbin, Roman, Sandra, Scott, Stefanie, Stefan, Stephan, Stephan, Sven.

Verdächtig? 🕵️‍♂️ @cduberlin |

select date(created_at), count(*)
from tweet
group by date(created_at)
having date(created_at) > '2022-12-31'
and count(*) >= all (
    select count(*)
    from tweet
    group by date(created_at)
    having date(created_at) > '2022-12-31'
)

A 4.4

date	count
2023-01-25	3568

5.5 Feedback

Punkte: 42.5/43     

Zur Aufgabe 1:

1.-5. Richtig

Zur Aufgabe 2:

1.-3. Richtig

Zur Aufgabe 3:

1.-3. Richtig

Zur Aufgabe 4:

1. Richtig 2. Gemeint waren nur Tweets im Datensatz, sonst wäre das etwas einfach (mit HAVING anzahl >2000) =- 0.5 P.

3.-4. Richtig