connect to dbs Datenbankverbindungsinformationen Datenbank-Server = DB2/NT64 10.5.1 SQL-Berechtigungs-ID = Zorbing Aliasname der lokalen Datenbank = DBS SELECT pres_name, birth_year FROM president WHERE state_born = 'Texas' PRES_NAME BIRTH_YEAR -------------------- ----------- Eisenhower D D 1890 Kennedy J F 1917 2 Satz/Sätze ausgewählt. SELECT p.pres_name, p.birth_year, p.years_serv, p.party, p.state_born FROM president p WHERE death_age IS NULL PRES_NAME BIRTH_YEAR YEARS_SERV PARTY STATE_BORN -------------------- ----------- ----------- -------------------- -------------------- Bush G 1924 4 Republican Massachusetts Carter J E 1924 4 Democratic Georgia Clinton W J 1946 8 Democratic Arkansas Ford G R 1913 5 Republican Georgia Nixon R M 1913 5 Republican Nebraska Reagan R 1911 8 Republican Illinois 6 Satz/Sätze ausgewählt. SELECT DISTINCT party FROM president PARTY -------------------- Demo-Rep Democratic Federalist Republican Whig 5 Satz/Sätze ausgewählt. SELECT * FROM president p LEFT JOIN state s ON p.state_born = s.state_name LEFT OUTER JOIN pres_hobby h ON p.pres_name = h.pres_name LEFT OUTER JOIN pres_marriage m ON p.pres_name = m.pres_name LEFT OUTER JOIN election e ON p.pres_name = e.candidate WHERE p.pres_name LIKE '%Bush%' PRES_NAME BIRTH_YEAR YEARS_SERV DEATH_AGE PARTY STATE_BORN STATE_NAME YEAR_ENTERED PRES_NAME HOBBY PRES_NAME SPOUSE_NAME PR_AGE SP_AGE NR_CHILDREN MAR_YEAR ELECTION_YEAR CANDIDATE VOTES WINNER_LOSER_INDIC -------------------- ----------- ----------- ----------- -------------------- -------------------- -------------------- ------------ -------------------- -------------------- -------------------- -------------------- ----------- ----------- ----------- ----------- ------------- -------------------- ----------- -------------------- Bush G 1924 4 - Republican Massachusetts Massachusetts 1776 Bush G Sailing Bush G Pierce B 20 19 5 1945 1988 Bush G 426 W Bush G 1924 4 - Republican Massachusetts Massachusetts 1776 Bush G Sailing Bush G Pierce B 20 19 5 1945 1992 Bush G 168 L 2 Satz/Sätze ausgewählt. SELECT * FROM president WHERE party = 'Republican' OR state_born = 'Texas' PRES_NAME BIRTH_YEAR YEARS_SERV DEATH_AGE PARTY STATE_BORN -------------------- ----------- ----------- ----------- -------------------- -------------------- Arthur C A 1830 3 56 Republican Vermont Bush G 1924 4 - Republican Massachusetts Coolidge C 1872 5 60 Republican Vermont Eisenhower D D 1890 8 79 Republican Texas Ford G R 1913 5 - Republican Georgia Garfield J A 1831 0 49 Republican Ohio Grant U S 1822 8 63 Republican Ohio Harding W G 1865 2 57 Republican Ohio Harrison B 1833 4 67 Republican Ohio Hayes R B 1822 4 70 Republican Ohio Hoover H C 1871 4 90 Republican Iowa Kennedy J F 1917 2 46 Democratic Texas Lincoln A 1809 4 56 Republican Kentucky McKinley W 1843 4 58 Republican Ohio Nixon R M 1913 5 - Republican Nebraska Reagan R 1911 8 - Republican Illinois Roosevelt T 1858 7 60 Republican New York Taft W H 1857 4 72 Republican Ohio 18 Satz/Sätze ausgewählt. SELECT * FROM president WHERE years_serv IN (4, 8, 12) PRES_NAME BIRTH_YEAR YEARS_SERV DEATH_AGE PARTY STATE_BORN -------------------- ----------- ----------- ----------- -------------------- -------------------- Adams J 1735 4 90 Federalist Massachusetts Adams J Q 1767 4 80 Demo-Rep Massachusetts Buchanan J 1791 4 77 Democratic Pennsylvania Bush G 1924 4 - Republican Massachusetts Carter J E 1924 4 - Democratic Georgia Cleveland G 1837 8 71 Democratic New Jersey Clinton W J 1946 8 - Democratic Arkansas Eisenhower D D 1890 8 79 Republican Texas Grant U S 1822 8 63 Republican Ohio Harrison B 1833 4 67 Republican Ohio Hayes R B 1822 4 70 Republican Ohio Hoover H C 1871 4 90 Republican Iowa Jackson A 1767 8 78 Democratic South Carolina Jefferson T 1743 8 83 Demo-Rep Virginia Johnson A 1808 4 66 Democratic North Carolina Lincoln A 1809 4 56 Republican Kentucky Madison J 1751 8 85 Demo-Rep Virginia McKinley W 1843 4 58 Republican Ohio Monroe J 1758 8 73 Demo-Rep Virginia Pierce F 1801 4 64 Democratic New Hampshire Polk J K 1795 4 53 Democratic North Carolina Reagan R 1911 8 - Republican Illinois Roosevelt F D 1882 12 63 Democratic New York Taft W H 1857 4 72 Republican Ohio Van Buren M 1782 4 79 Democratic New York Wilson W 1856 8 67 Democratic Virginia 26 Satz/Sätze ausgewählt. SELECT * FROM state ORDER BY year_entered STATE_NAME YEAR_ENTERED -------------------- ------------ Massachusetts 1776 Pennsylvania 1776 Virginia 1776 Connecticut 1776 South Carolina 1776 Maryland 1776 New Jersey 1776 Georgia 1776 New Hampshire 1776 Delawhare 1776 New York 1776 North Carolina 1776 Rhode Island 1776 Vermont 1791 Kentucky 1792 Tennesee 1796 Ohio 1803 Louisiana 1812 Indiana 1816 Mississippi 1817 Illinois 1818 Alabama 1819 Maine 1820 Missouri 1821 Arkansas 1836 Michigan 1837 Florida 1845 Texas 1845 Iowa 1846 Wisconsin 1848 California 1850 Minnesota 1858 Oregon 1859 Kansas 1861 West Virginia 1863 Nevada 1864 Nebraska 1867 Colorado 1876 North Dakota 1889 South Dakota 1889 Montana 1889 Washington 1889 Idaho 1890 Wyoming 1890 Utah 1896 Oklahoma 1907 New Mexico 1912 Arizona 1912 Alaska 1959 Hawaii 1959 50 Satz/Sätze ausgewählt. SELECT COUNT(*) AS count FROM election WHERE winner_loser_indic = 'L' UNION SELECT COUNT(*) FROM election WHERE winner_loser_indic = 'W' COUNT ----------- 12 42 2 Satz/Sätze ausgewählt. SELECT * FROM president p INNER JOIN election e ON p.pres_name = e.candidate WHERE e.votes = ( SELECT MAX(votes) FROM election ) PRES_NAME BIRTH_YEAR YEARS_SERV DEATH_AGE PARTY STATE_BORN ELECTION_YEAR CANDIDATE VOTES WINNER_LOSER_INDIC -------------------- ----------- ----------- ----------- -------------------- -------------------- ------------- -------------------- ----------- -------------------- Reagan R 1911 8 - Republican Illinois 1984 Reagan R 525 W 1 Satz/Sätze ausgewählt. SELECT * FROM president WHERE pres_name IN ( SELECT candidate FROM election WHERE winner_loser_indic = 'W' GROUP BY candidate HAVING COUNT(candidate) <> 2 ) PRES_NAME BIRTH_YEAR YEARS_SERV DEATH_AGE PARTY STATE_BORN -------------------- ----------- ----------- ----------- -------------------- -------------------- Adams J 1735 4 90 Federalist Massachusetts Bush G 1924 4 - Republican Massachusetts Carter J E 1924 4 - Democratic Georgia Johnson L B 1908 5 65 Democratic California 4 Satz/Sätze ausgewählt.