# SQL

# o++oPS

### Query1:

o++oPS-Result:

STID, | NAME, | LOC?, | SAL, | DEPT, ( | COURSE, | MARK l), ( | PROJ, | HOURS m) m |

1245 | Sophia | Berlin | 400 | CS | Algebra | 3 | Mao | 5 |

Database | 1 | Ming | 4 | |||||

Otto | 1 | Otto | 6 |

Problems of corresponding SQL-solution:

1. The SQL-Query refers to 3 tables

2. The SQL-Result table has to be in 1.Normal-form; therefore a student with 10 exams and 10 projects appears 100 times for example

3. SQL requires 4 join conditions

an intermediate query

SELECT *

FROM students1

WHERE “Algebra“ in (SELECT COURSE FROM exams1 WHERE students1.STID=exams1.STID)

contains only students1 and no exams1-data.

and the intermediate query

SELECT *

FROM students1, exams1

WHERE COURSE=“Algebra“ and students1.STID=exams1.STID

contains only Algebra-Records; an additional condition
COURSE=”Database” makes no sense.

### Query2:

o++oPS-Result:

NAME, ( | COURSE, | MARK m) b |

Clara | OCaml | 2 |

Sophia | Algebra | 3 |

Otto | 1 |

Problems of corresponding SQL-solution:

1. SQL requires a join

2. In SQL it is difficult to collect sets within multisets

3. SQL does not know structured output

### Query 3:

o++oPS-Result:

ANZ, ( | COURSE, | ANZ, ( | NAME, | STID m) m) |

4 | Algebra | 1 | Ernst | 1234 |

Apel | 1 | Kaethe | 5678 | |

Database | 2 | Clara | 3456 | |

Sophia | 1245 | |||

History | 1 | Ernst | 1234 | |

Otto | 1 | Sophia | 1245 | |

Repin | 1 | Kaethe | 5678 |

Here it is interesting that the total aggregation is not the sum of the partial aggregations.

Problems of a corresponding SQL solution:

1. The aggregations have to be computed in independent queries

2. SQL requires again joins

3. SQL requires a groupby (GROUPBY COURSE)

4. In the GROUPBY query NAME and STID are not allowed.