/[cvs.andrew.net.au]/uni/COMP2400/q6.sql
ViewVC logotype

Diff of /uni/COMP2400/q6.sql

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1.2 by apollock, Tue Aug 10 01:28:52 2004 UTC revision 1.5 by apollock, Wed Aug 11 22:48:11 2004 UTC
# Line 4  Line 4 
4  -- work. It has not been produced, in whole or in part, by another person.  -- work. It has not been produced, in whole or in part, by another person.
5  -- (signed) Andrew Pollock 4137129  -- (signed) Andrew Pollock 4137129
6    
7  TTITLE "COMP2400: Assignment 1: Question 6"  -- Find the couple(s) with the most children
8    
9  SELECT motherid, fatherid  TTITLE "COMP2400: Assignment 1: Question 6|Andrew Pollock (4137129)"
10  FROM parents  
11  GROUP BY motherid, fatherid  COLUMN Mother HEADING "Mother"
12  HAVING COUNT(personid) = (SELECT MAX(COUNT(personid))  COLUMN Father HEADING "Father"
13                            FROM parents  COLUMN Children HEADING "Number|of children"
14                            GROUP BY motherid, fatherid);  
15    SELECT mother.firstname || ' ' || mother.surname AS Mother, father.firstname || ' ' || father.surname AS Father, COUNT(p.personid) AS Children
16    FROM parents p, person mother, person father
17    WHERE p.motherid = mother.personid
18    AND p.fatherid = father.personid
19    GROUP BY mother.firstname, mother.surname, father.firstname, father.surname
20    HAVING COUNT(p.personid) = (SELECT MAX(COUNT(personid))
21                               FROM parents
22                               GROUP BY motherid, fatherid);
23    
24  EXIT;  EXIT;

Legend:
Removed from v.1.2  
changed lines
  Added in v.1.5

  ViewVC Help
Powered by ViewVC 1.1.22