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.