S tructured
Q uery
L anguage

Data Manipulation

Language (DML)

Data Definition

Language (DDL)

Data Query

Language (DQL)

Data Control

Language (DCL)

INSERT INTO [table] (field1, fieldN) VALUES (('a','b'),('foo','bar'))

GRANT SELECT, UPDATE ON TABLE [table] TO user1;

CREATE TABLE [table] (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`field1` varchar(255), `field2` int not null)

SELECT * from [table] WHERE field1='a';

Field ₁

Field ₂

Field _n

Column

Feldbezeichner
Feldtyp
Begrenzungen
Default Value

Tabelle

Field ₁

Field ₂

Field _n

Value

Column

Tabelle

Field ₁

Field ₂

Field _n

Record / Tupel

Value

Column

Tabelle

Relational Database Management System

(RDBMS)

Relation beschreibt die Beziehung eines Tupel zu einem anderen
Nur zwischen Schluesselpaaren moeglich (FK <-> PK)

Relationtypes

one to one (1-1)

Jeder Coupon ist genau einem Customer zugewiesen

Relationtypes

one to Many (1-n)

1 Customer Record hat n Address Records

Relationtypes

Many to Many (m-n)
Junction Object / Table

Ein Buch hat n-Autoren

1 Autor hat n-Bücher geschrieben

Normalformen

was ist schon normal?

Unter Normalisierung eines relationalen Datenschemas (Tabellenstruktur) versteht man die Aufteilung von Attributen (Tabellenspalten) in mehrere Relationen (Tabellen) gemäß den Normalisierungsregeln, so dass eine Form entsteht, die keine vermeidbaren Redundanzen mehr enthält.

Warum?

Ein konzeptionelles Schema, das Datenredundanzen enthält, kann dazu führen, dass bei Änderungen der damit realisierten Datenbank die mehrfach enthaltenen Daten nicht konsistent, sondern nur teilweise und unvollständig geändert werden, womit sie obsolet oder widersprüchlich werden können. Man spricht von auftretenden Anomalien. Zudem belegt mehrfache Speicherung derselben Daten unnötig Speicherplatz. Um Redundanz zu verhindern, normalisiert man solche Tabellen.

1. NF

2. NF

Die dritte Normalform ist erreicht, wenn sich das Relationenschema in 2NF befindet, und kein Nichtschlüsselattribut von einem anderen Nichtschlüsselattribut funktional abhängig ist.

Data Query Language

endlich

select  pstaff.shortname as "Manager Kurzname",
  pclient.person_name as "Kundenname",
  job.jobno as "Jobnummer",
  job.job_name as "Jobname",
  job.job_status_p_job_status as "interner Jobstatus",
  CASE
    when job.job_status_p_job_status = '100' then
      concat('Vorbereitung')
    when job.job_status_p_job_status = '200' then
      concat('Angebot')
    when job.job_status_p_job_status = '300' then
      concat('Durchführung')
    when job.job_status_p_job_status = '301' then
      concat('Durchführung - bereits abgerechnet')
    when job.job_status_p_job_status = '400' then
      concat('Geliefert (',job.job_status_p_job_status,')')
    when job.job_status_p_job_status = '500' then
      concat('Abgerechnet')
    when job.job_status_p_job_status = '600' then
      concat('Abgebrochen')
    else
      concat('unbekannt')
    end as "Jobstatus",
  job.own_service_budget_total as "Budget Gesamt Soll",
  (wb.own_service_total +wb.external_cost_total) as "Budget Gesamt Ist",
    (job.own_service_budget_total - (wb.own_service_total +wb.external_cost_total)) as "Budget Soll/Ist Saldo", 
    CASE when job.own_service_budget_total <> 0 then
        concat((-100 * (wb.own_service_total +wb.external_cost_total) / job.own_service_budget_total) + 100)
    else
        concat('---')
    end as "Budget Soll/Ist in %",
  wb.own_service_total as "Eigenleistung Gesamt",
  wb.external_cost_total as "Fremdleistung Gesamt",
    job.state_p_budget_control_state as "Budget Kontroll-Status",
  job.description as "Beschreibung",
  job.orderno as "Auftragsnummer",
  job.order_date as "Auftragsdatum",
  job.delivery_date as "Lieferdatum"
from   
            job
            left join
    person as pstaff on (job.staff_2_person = pstaff.urno)
  left join
    person as pclient on (job.client_2_person = pclient.urno)
  left join (
    select warehouse_budgetcontrol.job_2_job,
      max(warehouse_budgetcontrol.own_service_total) as own_service_total ,
      max(warehouse_budgetcontrol.own_service_total_warn_exceed) as own_service_total_warn_exceed,
      max(warehouse_budgetcontrol.external_cost_total) as external_cost_total
      from warehouse_budgetcontrol group by warehouse_budgetcontrol.job_2_job
    ) as wb on (job.urno = wb.job_2_job)
  where 
            -- Budget Controll is active
    job.budget_control = true
    AND cast(job.job_status_p_job_status as int) NOT IN (500,600,301)

Zur einstimmung - ein einfaches Beispiel

select  pstaff.shortname as "Manager Kurzname",
  pclient.person_name as "Kundenname",
  job.jobno as "Jobnummer",
  job.job_name as "Jobname",
  job.job_status_p_job_status as "interner Jobstatus",
  CASE
    when job.job_status_p_job_status = '100' then
      concat('Vorbereitung')
    when job.job_status_p_job_status = '200' then
      concat('Angebot')
    when job.job_status_p_job_status = '300' then
      concat('Durchführung')
    when job.job_status_p_job_status = '301' then
      concat('Durchführung - bereits abgerechnet')
    when job.job_status_p_job_status = '400' then
      concat('Geliefert (',job.job_status_p_job_status,')')
    when job.job_status_p_job_status = '500' then
      concat('Abgerechnet')
    when job.job_status_p_job_status = '600' then
      concat('Abgebrochen')
    else
      concat('unbekannt')
    end as "Jobstatus",
  job.own_service_budget_total as "Budget Gesamt Soll",
  (wb.own_service_total +wb.external_cost_total) as "Budget Gesamt Ist",
    (job.own_service_budget_total - (wb.own_service_total +wb.external_cost_total)) as "Budget Soll/Ist Saldo", 
    CASE when job.own_service_budget_total <> 0 then
        concat((-100 * (wb.own_service_total +wb.external_cost_total) / job.own_service_budget_total) + 100)
    else
        concat('---')
    end as "Budget Soll/Ist in %",
  wb.own_service_total as "Eigenleistung Gesamt",
  wb.external_cost_total as "Fremdleistung Gesamt",
    job.state_p_budget_control_state as "Budget Kontroll-Status",
  job.description as "Beschreibung",
  job.orderno as "Auftragsnummer",
  job.order_date as "Auftragsdatum",
  job.delivery_date as "Lieferdatum"
from  
            job
            left join
    person as pstaff on (job.staff_2_person = pstaff.urno)
  left join
    person as pclient on (job.client_2_person = pclient.urno)
  left join (
    select warehouse_budgetcontrol.job_2_job,
      max(warehouse_budgetcontrol.own_service_total) as own_service_total ,
      max(warehouse_budgetcontrol.own_service_total_warn_exceed) as own_service_total_warn_exceed,
      max(warehouse_budgetcontrol.external_cost_total) as external_cost_total
      from warehouse_budgetcontrol group by warehouse_budgetcontrol.job_2_job
    ) as wb on (job.urno = wb.job_2_job)
  where 
            -- Budget Controll is active
    job.budget_control = true
    AND cast(job.job_status_p_job_status as int) NOT IN (500,600,301)

SELECT * FROM 
  [DealerAppointmentConfirmation - Live] e 
WHERE
  e.AppointmentDateTime > DATEADD(minute, 481, GETDATE())
  AND e.AppointmentDateTime < DATEADD(minute, 540, GETDATE())
  AND e.Cancelled != 1

Zeige jene Records der Tabelle "DealerAppointmentConfirmation - Live", von nun an "e" genannt, welche ein AppointmentDate Wert zwischen "jetzt in 481Minuten" und "jetzt in 540Minuten" aufweisen und nicht Abgebrochen wurden.

Operator

Command

Column

Function

Condition

Source

Alias

Filter und Operatoren

Operator	Bedeutung	Bespiel
=	Gleich	field1 = 123 ; field1 = "foobar"
>	Größer als	field1 > 1;
<	Kleiner als	field1 < 10;
>=	Größer als oder gleich	field1 >= 5;
<=	Kleiner als oder gleich	field1 <= 12;
<>	Ungleich	field1 <> 10;
!=	Nicht gleich (kein ISO-Standard)	boolfield != 0;
[ NOT ] LIKE	String-Suche	field1 LIKE "%test"; field2 LIKE "_est";
[ NOT ] IN	in einer Menge von x	field1 IN (1,3,5,7,10); field2 IN ("abc","foo","bar");

Einleitung mit WHERE oder ON

Verkettung mit AND oder OR

Aggregation und Gruppierung

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns

SELECT * FROM table GROUP BY field1, fieldN

Funktion	Bedeutung
AVG(field)	Mittelwert
SUM(field)	Summe aller Werte
COUNT(field)	Anzahl der Elemente
MIN(field)	kleinster Wert eines Feldes
MAX(field)	groesster Wert eines Feldes

Group wird immer auf das gesamte RecordSet ausgefuehrt

The SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SELECT
  project.name, SUM(project.amount) AS SubTotal
FROM
    projects
WHERE
    amount => 1000
GROUP BY
    project
HAVING
    SubTotal > 10000;

select
  pstaff.shortname as "Manager Kurzname",
  pclient.person_name as "Kundenname",
  job.jobno as "Jobnummer",
  job.job_name as "Jobname",
  job.job_status_p_job_status as "interner Jobstatus",
  CASE
    when
      job.job_status_p_job_status = '100' 
    then
      concat('Vorbereitung') 
    when
      job.job_status_p_job_status = '200' 
    then
      concat('Angebot') 
    when
      job.job_status_p_job_status = '300' 
    then
      concat('Durchführung') 
    when
      job.job_status_p_job_status = '301' 
    then
      concat('Durchführung - bereits abgerechnet') 
    when
      job.job_status_p_job_status = '400' 
    then
      concat('Geliefert (', job.job_status_p_job_status, ')') 
    when
      job.job_status_p_job_status = '500' 
    then
      concat('Abgerechnet') 
    when
      job.job_status_p_job_status = '600' 
    then
      concat('Abgebrochen') 
    else
      concat('unbekannt') 
  end
  as "Jobstatus", job.own_service_budget_total as "Budget Gesamt Soll", 
  (
    wb.own_service_total + wb.external_cost_total 
  )
  as "Budget Gesamt Ist", 
  (
    job.own_service_budget_total - (wb.own_service_total + wb.external_cost_total) 
  )
  as "Budget Soll / Ist Saldo", 
  CASE
    when
      job.own_service_budget_total <> 0 
    then
      concat(( - 100 * (wb.own_service_total + wb.external_cost_total) / job.own_service_budget_total) + 100) 
    else
      concat('---') 
  end
  as "Budget Soll / Ist in % ",
wb.own_service_total as "Eigenleistung Gesamt",
wb.external_cost_total as "Fremdleistung Gesamt",
job.state_p_budget_control_state as "Budget Kontroll - Status",
job.description as "Beschreibung",
job.orderno as "Auftragsnummer",
job.order_date as "Auftragsdatum",
job.delivery_date as "Lieferdatum" 
from
  job 
  left join
    person as pstaff 
    on (job.staff_2_person = pstaff.urno) 
  left join
    person as pclient 
    on (job.client_2_person = pclient.urno) 
  left join
    (
      select
        warehouse_budgetcontrol.job_2_job,
        max(warehouse_budgetcontrol.own_service_total) as own_service_total,
        max(warehouse_budgetcontrol.own_service_total_warn_exceed) as own_service_total_warn_exceed,
        max(warehouse_budgetcontrol.external_cost_total) as external_cost_total 
      from
        warehouse_budgetcontrol 
      group by
        warehouse_budgetcontrol.job_2_job 
    )
    as wb 
    on (job.urno = wb.job_2_job) 
where
  -- Budget Controll is active
  job.budget_control = true 
  AND cast(job.job_status_p_job_status as int) NOT IN 
  ( 500, 600, 301 )

Select b.JobID,
    b.EventDate,
    b.BounceCategoryID,
    b.BounceTypeID,
    b.SMTPCode,
    b.SubscriberID,
    b.Domain,
    b.BounceCategory,
    b.BounceSubcategoryID,
    b.BounceSubcategory,
    b.BounceType,
    b.SMTPBounceReason,
    b.SMTPMessage,
    s.EmailAddress
from [_Bounce] b
join [_Subscribers] s
on b.SubscriberID = s.SubscriberID
where b.BounceCategory = 'Hard bounce'

SELECT * FROM [retention] r
WHERE
    r.creation_date >= '2017-09-25 10:12'
AND r.creation_date <= '2017-9-27 04:03'

select 
    a.customer_id,
    a.email
from [account] a
join [_Complaint] c
    on a.customer_id = c.SubscriberKey

All Complaints

Filtering Retention by CreationDate

All HardBounces

select 
    i.customer_id,
    i.first_name,
    i.last_name,
    i.email,
    i.opt_out_token
from [IP_Warmup_T-Online_new] i
    left join [IP_WarmUp_T-Online_send] s
    on
        i.customer_id = s.customer_id
    where
        s.customer_id Is Null

select
  i.customer_id,
  i.first_name,
  i.last_name,
  i.email,
  i.opt_out_token,
  j.JourneyName,
  j.VersionNumber,
  ja.ActivityName as 'EmailName',
  s.EventDate as 'SendTime',
  su.EmailAddress,
  su.SubscriberKey as 'ContactKey',
  s.SubscriberID as 'ContactID',
  s.JobID,
  s.ListID,
  s.BatchID 
from
  [_Bounce] s 
  join
    [_JourneyActivity] ja 
    on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID 
  join
    [_Journey] j on ja.VersionID = j.VersionID 
  join
    [_Subscribers] su  on s.SubscriberID = su.SubscriberID 
  join
    [IP_Warmup_T - Online_new] i on i.customer_id = s.SubscriberKey 
where
  ja.ActivityType in ( 'EMAIL', 'EMAILV2')
  and j.JourneyName = 'IP_Warmup_T-Online' 
  and s.BounceCategory = 'Block bounce'

IPWarumUp T-Online Block Bounces

IPWarmup T-Online get NotSent

select * from [IP_WarmUp_T-Online_blockbounced] bb
Union
select * from [IP_WarmUp_T-Online_notsend] ns

SELECT
  * 
FROM
  [DealerAppointmentConfirmation - Live] e 
WHERE
  e.AppointmentDateTime > DATEADD(minute, 481, GETDATE()) 
  AND e.AppointmentDateTime < DATEADD(minute, 540, GETDATE()) 
  AND e.Cancelled != 1

DealerAppointmentReminder

Merge Bounces and NotSent

Select l.customer_id From [listing] l
Inner Join [fsbo_customer] f
On l.customer_id = f.customer_id
Where f.marketing_action_approved = 1 And l.vehicle_category = 'Car'
And DATEDIFF(day, l.creation_date, GETDATE()) = 1

Filtering Listing Data Extension

Tabelle

Tabelle

Tabelle

Relational Database Management System

(RDBMS)

Relationtypes

Relationtypes

Relationtypes

Normalformen

was ist schon normal?

Warum?

Die dritte Normalform ist erreicht, wenn sich das Relationenschema in 2NF befindet, und kein Nichtschlüsselattribut von einem anderen Nichtschlüsselattribut funktional abhängig ist.

Data Query Language

endlich

Zur einstimmung - ein einfaches Beispiel

Filter und Operatoren

Aggregation und Gruppierung

The SQL HAVING Clause

Happy Hacking