Sanne Derckx/2009-10/Domeinmodellering/Opdracht2/Opdracht

Uitwerking opdracht 2

1. <source lang="sql">SELECT P.pres_name, birth_yr, state_born, count(distinct hobby) FROM president P, pres_hobby H WHERE P.pres_name = H.pres_name GROUP BY P.pres_name, birth_yr, state_born HAVING count (distinct hobby) > 3 ORDER BY 4 DESC, 1 </source>

PRES_NAME          BIRTH_YR  STATE_BORN               COUNT  
CHAR(16)           SMALLINT  CHAR(15)               INTEGER  
-------------------------------------------------------------
Roosevelt T            1858  New York                     7  
Coolidge C             1872  Vermont                      5  
Eisenhower D D         1890  Texas                        5  
-------------------------------------------------------------
-                      5620  -                           17  

2. <source lang="sql">SELECT P.pres_name, state_born, death_age, state_name FROM president P, state S WHERE admin_entered is null AND state_born = state_name order by state_name</source>

PRES_NAME          STATE_BORN        DEATH_AGE  STATE_NAME        
CHAR(16)           CHAR(15)           SMALLINT  CHAR(15)          
------------------------------------------------------------------
Carter J E         Georgia              (null)  Georgia           
Adams J Q          Massachusetts            80  Massachusetts     
Adams J            Massachusetts            90  Massachusetts     
Kennedy J F        Massachusetts            46  Massachusetts     
Pierce F           New Hampshire            64  New Hampshire     
Cleveland G        New Jersey               71  New Jersey        
Van Buren M        New York                 79  New York          
Fillmore M         New York                 74  New York          
Roosevelt T        New York                 60  New York          
Roosevelt F D      New York                 63  New York          
Johnson A          North Carolina           66  North Carolina    
Polk J K           North Carolina           53  North Carolina    
Jackson A          South Carolina           78  South Carolina    
Harrison W H       Virginia                 68  Virginia          
Madison J          Virginia                 85  Virginia          
Taylor Z           Virginia                 65  Virginia          
Washington G       Virginia                 67  Virginia          
Jefferson T        Virginia                 83  Virginia          
Wilson W           Virginia                 67  Virginia          
Monroe J           Virginia                 73  Virginia          
Tyler J            Virginia                 71  Virginia          
------------------------------------------------------------------
-                  -                      1403  -         

3. <source lang="sql">SELECT P.pres_name, state_born, death_age, hobby FROM president P, pres_hobby H WHERE death_age is null</source>

Hallo Sanne, ik zie hier een join staan zonder join-criterium! Voor 99,9% is dat fout!
Ger Paulussen → Sanne Derckx	Remove this comment when resolved!

4. <source lang="sql">SELECT P.pres_name, candidate FROM president P, pres_hobby H, election E WHERE P.pres_name = H.pres_name AND candidate = (SELECT election_year, candidate

               FROM election
               WHERE election_year between 1900 and 1950)</source>

5. <source lang="sql">SELECT pres_name, spouse_name, mar_year FROM president P, pres_marriage M WHERE P.pres_name = M.pres_name</source>

8. <source lang="sql">SELECT state_name, count (pres_name) FROM state S, president P WHERE S.state_name = P.state_born GROUP BY state_name ORDER BY 2 DESC UNION SELECT state_name FROM state WHERE state_name NOT IN (SELECT state_born

                        FROM president)</source>