1.

CREATE SEQUENCE serial_member START 0;

CREATE TABLE gymmember (
    membershipID INTEGER PRIMARY KEY , DEFAULT nextval('serial_member')
    firstname VARCHAR(10),
    lastname VARCHAR(20),
    traininglevel INTEGER,
    birthdate DATE
);

CREATE SEQUENCE serial_instructor START 0;

CREATE TABLE instructor (
  instructorID    INTEGER  PRIMARY KEY DEFAULT nextval('serial_instructor'),
  firstname       VARCHAR(10),
  lastname        VARCHAR(20),
  isdietician     BOOL
);

CREATE SEQUENCE serial_class START 0;

CREATE TABLE class (
  classID         INTEGER PRIMARY KEY DEFAULT nextval('serial_class'),
  instructorID    INTEGER REFERENCES instructor(instructorID),
  weekday         VARCHAR(9),
  room            CHAR(5),
  starttime       TIME,
  occurencenumber INTEGER
);

CREATE SEQUENCE serial_test START 0;

CREATE TABLE test (
  testID     INTEGER  PRIMARY KEY DEFAULT nextval('serial_test'),
  maxpoints  INTEGER
);

CREATE TABLE taketest (
  membershipID      INTEGER REFERENCES gymmember(membershipID),
  testID            INTEGER REFERENCES test(testID),
  achievedpoints    INTEGER,
  PRIMARY KEY (membershipID, testID)
);

CREATE TABLE participates (
  membershipID      INTEGER REFERENCES gymmember(membershipID),
  classID           INTEGER REFERENCES class(classID),
  PRIMARY KEY (membershipID, classID)
);

2.

1.1
SELECT name, domain
FROM god
WHERE olympian = true
ORDER BY name DESC;


1.2
SELECT name, death_toll
FROM disaster NATURAL JOIN caused s
JOIN god ON god.name = s.god_name
WHERE did >= 1 AND death_toll > 11 AND olympian = true
ORDER BY  name DESC, death_toll DESC;


3.
SELECT name, strength
FROM demigod
WHERE strength > 9000 AND name NOT IN (
  SELECT DISTINCT name
  FROM demigod JOIN heroic_deed 
  ON demigod.name = heroic_deed.demigod_name
  WHERE date >= '0039-05-07');
  
7.
SELECT DISTINCT oid, item, amount
FROM offering NATURAL JOIN offered_to s
JOIN god ON god.name = s.god_name
WHERE olympian = true AND amount >= ALL ( 
  SELECT amount
  FROM offering);
  
9.
SELECT name, birthday 
FROM human JOIN worships 
 ON human.name = worships.human_name
 AND human.birthday = worships.human_birthday
GROUP BY human.name, human.birthday
HAVING COUNT(worships.god_name) = (SELECT COUNT(name)
  FROM god);
  
10.
SELECT s.human_name
FROM (SELECT human_name, human_birthday
 FROM worships
 WHERE god_name = 'Zeus'
 UNION ALL
 SELECT human_name, human_birthday
 FROM offered_to
 WHERE god_name = 'Zeus') s
WHERE (s.human_name, s.human_birthday)
NOT IN (SELECT human_name, human_birthday
 FROM parents
 WHERE god_name = 'Zeus');