SQL

o++oPS

Query1:

        o++oPS-Result:

  M( STID, NAME, LOC?, SAL, DEPT, L( COURSE, MARK),  M( PROJ, HOURS))
    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:

  B( NAME, M( COURSE, MARK))
    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, M( COURSE, ANZ, M( NAME, STID))  
  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.