CREATE TABLE TEAM ( T_NAME VARCHAR (20) UNIQUE, T_RATING INTEGER, PRIMARY KEY (T_NAME) ); millebe=> SELECT * FROM TEAM; t_name |t_rating ----------+-------- Orioles | 58 Red Sox | 58 Yankees | 53 Devil Rays| 50 Blue Jays | 43 Tigers | 63 White Sox | 50 Indians | 48 Royals | 43 Twins | 37 (10 rows) - - - - - - - - - - CREATE TABLE MANAGER ( M_NAME VARCHAR (20), T_NAME VARCHAR (20) REFERENCES TEAM ON UPDATE CASCADE ON DELETE SET NULL, PRIMARY KEY (M_NAME) ); millebe=> SELECT * FROM MANAGER; m_name |t_name ----------+---------- Torre |Yankees Williams |Red Sox Hargrove |Orioles Fregosi |Blue Jays Rothschild|Devil Rays Manuel |White Sox Garner |Tigers Kelly |Twins Muser |Royals Nunez |Indians (10 rows) - - - - - - - - - - CREATE TABLE ASSISTANT ( A_NAME VARCHAR (20), M_NAME VARCHAR (20) REFERENCES MANAGER ON UPDATE CASCADE ON DELETE CASCADE, T_NAME VARCHAR (20) REFERENCES TEAM ON UPDATE CASCADE ON DELETE SET NULL, PRIMARY KEY (A_NAME) ); millebe=> SELECT * FROM ASSISTANT; a_name |m_name |t_name -----------+----------+---------- Chambliss |Torre |Yankees Stottlemyre|Torre |Yankees Rice |Williams |Red Sox Kerrigan |Williams |Red Sox Crowley |Hargrove |Orioles Wiley |Hargrove |Orioles Gaston |Fregosi |Blue Jays Langford |Fregosi |Blue Jays Roberts |Rothschild|Devil Rays Williams |Rothschild|Devil Rays Joshua |Manuel |White Sox Contreras |Manuel |White Sox Jones |Manuel |Indians Pole |Manuel |Indians Madlock |Garner |Tigers Warthen |Garner |Tigers Ullger |Kelly |Twins Such |Kelly |Twins Johnson |Muser |Royals Strom |Muser |Royals (20 rows) - - - - - - - - - - CREATE TABLE CATCHER ( C_NO INTEGER, C_NAME VARCHAR (20), BATTING_AVG FLOAT, C_RATING INTEGER, T_NAME VARCHAR (20) REFERENCES TEAM ON UPDATE CASCADE ON DELETE SET NULL, PRIMARY KEY (C_NO, C_NAME) ); millebe=> SELECT * FROM CATCHER; p_no|p_name |batting_avg|p_rating|t_name ----+---------+-----------+--------+---------- 20|Posada | 0.204| 78|Yankees 10|Hatteberg| 0.265| 82|Red Sox 26|Fordyce | 0.232| 43|Orioles 30|Castillo | 0.276| 46|Blue Jays 8|DiFelice | 0.302| 67|Devil Rays 8|Johnson | 0.176| 86|White Sox 15|Alomar Jr| 0.315| 84|Indians 53|Cardona | 0.267| 62|Tigers 39|Moeller | 0.287| 56|Twins 22|Ortiz | 0.298| 75|Royals (10 rows) - - - - - - - - - - CREATE TABLE INFIELDER ( I_NO INTEGER, I_NAME VARCHAR (20), BATTING_AVG FLOAT, I_RATING INTEGER, T_NAME VARCHAR (20) REFERENCES TEAM ON UPDATE CASCADE ON DELETE SET NULL, PRIMARY KEY (I_NO, I_NAME) ); millebe=> SELECT * FROM INFIELDER; p_no|p_name |batting_avg|p_rating|t_name ----+-----------+-----------+--------+---------- 18|Brosius | 0.229| 76|Yankees 2|Jeter | 0.317| 54|Yankees 11|Knoblauch | 0.238| 86|Yankees 24|Martinez | 0.364| 97|Yankees 14|Sojo | 0.239| 75|Yankees 13|Vizcaino | 0.316| 68|Yankees 29|Alexander | 0.211| 86|Red Sox 11|Brogna | 0.196| 67|Red Sox 23|Daubach | 0.248| 92|Red Sox 5|Garciaparra| 0.372| 86|Red Sox 3|Lansing | 0.194| 65|Red Sox 30|Offerman | 0.255| 58|Red Sox 13|Coffie | 0.199| 67|Orioles 18|Conine | 0.18| 59|Orioles 11|DeShields | 0.256| 68|Orioles 1|Garcia | 0.271| 86|Orioles 10|Lewis | 0.234| 56|Orioles 8|Ripken | 0.219| 68|Orioles 7|Batista | 0.189| 56|Blue Jays 18|Bush | 0.225| 86|Blue Jays 25|Delgado | 0.257| 78|Blue Jays 8|Gonzalez | 0.222| 86|Blue Jays 4|Grebeck | 0.287| 46|Blue Jays 31|Woodward | 0.274| 68|Blue Jays 66|Brewer | 0.298| 86|Devil Rays 1|Cairo | 0.312| 86|Devil Rays 9|Castilla | 0.276| 56|Devil Rays 13|Guillen | 0.248| 68|Devil Rays 21|Huff | 0.215| 86|Devil Rays 10|Johnson | 0.278| 65|Devil Rays 17|Caruso | 0.283| 84|White Sox 24|Crede | 0.275| 63|White Sox 5|Durham | 0.237| 65|White Sox 47|Graffanino | 0.245| 45|White Sox 14|Konerko | 0.176| 81|White Sox 31|Norton | 0.27| 71|White Sox 22|Allen | 0.234| 73|Tigers 44|Clark | 0.269| 75|Tigers 8|Cruz | 0.231| 77|Tigers 9|Easley | 0.178| 72|Tigers 18|Fick | 0.201| 67|Tigers 7|Palmer | 0.271| 75|Tigers 38|Blake | 0.219| 67|Twins 1|Canizaro | 0.253| 83|Twins 8|Coomer | 0.247| 56|Twins 15|Guzman | 0.233| 65|Twins 7|Hocking | 0.255| 83|Twins 47|Koskie | 0.289| 82|Twins 19|Delgado | 0.277| 45|Royals 3|Febles | 0.234| 43|Royals 6|McCarty | 0.256| 25|Royals 1|Sanchez | 0.281| 64|Royals 8|Ordaz | 0.218| 43|Royals 16|Randa | 0.189| 63|Royals 12|Alomar | 0.319| 91|Indians 17|Fryman | 0.245| 74|Indians 8|McDonald | 0.234| 71|Indians 23|Segui | 0.265| 76|Indians 25|Thome | 0.32| 93|Indians 13|Vizquel | 0.299| 93|Indians (60 rows) - - - - - - - - - - CREATE TABLE OUTFIELDER ( O_NO INTEGER, O_NAME VARCHAR (20), BATTING_AVG FLOAT, O_RATING INTEGER, T_NAME VARCHAR (20) REFERENCES TEAM ON UPDATE CASCADE ON DELETE SET NULL, PRIMARY KEY (O_NO, O_NAME) ); millebe=> SELECT * FROM OUTFIELDER; p_no|p_name |batting_avg|p_rating|t_name ----+-----------+-----------+--------+---------- 35|Bellinger | 0.127| 23|Yankees 33|Canseco | 0.286| 54|Yankees 28|Justice | 0.301| 73|Yankees 21|oNeill | 0.294| 94|Yankees 19|Polonia | 0.298| 77|Yankees 51|Williams | 0.275| 86|Yankees 56|Alcantara | 0.189| 74|Red Sox 19|Bichette | 0.234| 57|Red Sox 54|Burkhart | 0.299| 75|Red Sox 99|Coleman | 0.233| 75|Red Sox 18|Cummings | 0.217| 86|Red Sox 2|Everett | 0.313| 86|Red Sox 9|Anderson | 0.301| 63|Orioles 88|Belle | 0.274| 65|Orioles 57|Garcia | 0.291| 54|Orioles 23|Hubbard | 0.222| 75|Orioles 40|Kingsale | 0.211| 67|Orioles 32|Matos | 0.255| 84|Orioles 15|Cordova | 0.276| 64|Blue Jays 23|Cruz | 0.287| 65|Blue Jays 20|Fullmer | 0.298| 65|Blue Jays 27|Greene | 0.258| 86|Blue Jays 14|Martinez | 0.237| 65|Blue Jays 43|Mondesi | 0.233| 56|Blue Jays 28|Cox | 0.286| 67|Devil Rays 30|Guillen | 0.245| 67|Devil Rays 15|Kelly | 0.251| 85|Devil Rays 44|Timmons | 0.211| 54|Devil Rays 14|Tyner | 0.267| 85|Devil Rays 23|Vaughn | 0.312| 67|Devil Rays 25|Abbot | 0.305| 85|White Sox 3|Baines | 0.23| 65|White Sox 26|Christensen| 0.211| 56|White Sox 45|Lee | 0.207| 85|White Sox 30|Ordonez | 0.286| 64|White Sox 35|Thomas | 0.318| 73|White Sox 24|Becker | 0.298| 75|Tigers 34|Encarnacion| 0.211| 44|Tigers 19|Gonzalez | 0.209| 65|Tigers 4|Higginson | 0.293| 65|Tigers 29|Magee | 0.201| 47|Tigers 43|McMillon | 0.219| 87|Tigers 40|Barnes | 0.123| 84|Twins 30|Buchanan | 0.216| 46|Twins 48|Hunter | 0.176| 63|Twins 11|Jones | 0.136| 63|Twins 50|Lawton | 0.187| 73|Twins 27|Ortiz | 0.304| 75|Twins 15|Beltran | 0.207| 84|Royals 18|Damon | 0.244| 85|Royals 7|Dunwoody | 0.21| 57|Royals 24|Dye | 0.215| 85|Royals 14|Quinn | 0.257| 75|Royals 33|Branyan | 0.3| 72|Indians 7|Lofton | 0.294| 88|Indians 61|Perry | 0.205| 86|Indians 24|Ramirez | 0.299| 89|Indians 10|Roberts | 0.235| 85|Indians 51|Cruz | 0.234| 57|Indians (59 rows) - - - - - - - - - - CREATE TABLE PITCHER ( P_NO INTEGER, P_NAME VARCHAR (20), ERA FLOAT, P_RATING INTEGER, T_NAME VARCHAR (20) REFERENCES TEAM ON UPDATE CASCADE ON DELETE SET NULL, PRIMARY KEY (P_NO, P_NAME) ); millebe=> SELECT * FROM PITCHER; p_no|p_name | era|p_rating|t_name ----+---------+----+--------+---------- 22|Clemens |3.21| 56|Yankees 36|Cone | 0| 74|Yankees 17|Gooden | 9| 88|Yankees 26|Hernandez|3.94| 79|Yankees 12|Neagle | 4.3| 87|Yankees 46|Pettitte |2.84| 68|Yankees 46|Arrojo |3.54| 77|Red Sox 47|Beck |2.76| 73|Red Sox 37|Cormier |3.76| 66|Red Sox 63|Crawford |5.34| 78|Red Sox 55|Croushore|3.54| 88|Red Sox 45|Martinez |2.34| 97|Red Sox 45|Brea |6.23| 76|Orioles 19|Erickson |4.56| 69|Orioles 27|Groom |5.34| 56|Orioles 41|Johnson |6.77| 67|Orioles 75|Mills |3.44| 76|Orioles 29|Spurgeon |6.11| 67|Orioles 51|Borbon |1.89| 87|Blue Jays 26|Carpenter|3.49| 67|Blue Jays 37|Castillo |3.65| 71|Blue Jays 40|DeWitt |7.34| 73|Blue Jays 47|Escobar |2.33| 78|Blue Jays 50|Hamilton |6.44| 54|Blue Jays 40|Alvarez |5.23| 57|Devil Rays 38|Creek |2.45| 84|Devil Rays 48|Eiland |5.32| 73|Devil Rays 45|Enders |5.34| 44|Devil Rays 0|Fiore |2.54| 86|Devil Rays 57|Guzman |5.67| 68|Devil Rays 37|Baldwin |4.12| 65|White Sox 44|Bradford |1.21| 73|White Sox 50|Lowe |1.23| 76|White Sox 40|Parque |4.65| 86|White Sox 41|Simas |7.45| 74|White Sox 32|Wells |4.23| 67|White Sox 46|Bere |1.11| 75|Indians 50|Brower |1.21| 65|Indians 49|Brown |5.45| 56|Indians 34|Burba |7.89| 87|Indians 31|Finley |2.45| 65|Indians 41|Nagy |2.01| 89|Indians 14|Anderson |7.34| 67|Tigers 49|Bernero |6.34| 87|Tigers 20|Blair |2.87| 67|Tigers 26|Brocail |4.04| 57|Tigers 40|Cruz |2.34| 85|Tigers 59|Jones |4.06| 75|Tigers 59|Cressend |3.02| 63|Twins 18|Guardado |3.29| 67|Twins 32|Hawkins |6.23| 84|Twins 51|Kinney |2.33| 65|Twins 19|Lincoln |1.11| 65|Twins 46|Wells |6.11| 55|Twins 52|Bottalico|7.02| 56|Royals 49|Fussel | 6.2| 68|Royals 54|Larkin |3.43| 57|Royals 57|Mullen |2.01| 81|Royals 59|Meadows |6.43| 81|Royals 45|Moreno |3.42| 83|Royals (60 rows) - - - - - - - - - - CREATE TABLE GAME ( T_NAME1 VARCHAR (20), T_NAME2 VARCHAR (20), WINNER VARCHAR (20), PRIMARY KEY (T_NAME1, T_NAME2) ); millebe=> SELECT * FROM GAME; t_name1 |t_name2 |winner ----------+---------+--------- Orioles |Tigers |Orioles Red Sox |White Sox|Red Sox Yankees |Indians |Indians Devil Rays|Royals |Royals Blue Jays |Twins |Twins Orioles |Twins |Twins Red Sox |Royals |Royals Yankees |Tigers |Yankees Devil Rays|White Sox|White Sox Blue Jays |Indians |Indians (10 rows) - - - - - - - - - - Show who catches for each of the pitchers. CREATE VIEW PITCHER_CATCHER AS SELECT P_NAME, C_NAME FROM CATCHER, PITCHER WHERE CATCHER.T_NAME = PITCHER.T_NAME; - - - - - - - - - - Update the winner of the Orioles vs. Tigers game so that the Tigers win. UPDATE GAME SET WINNER = 'Tigers' WHERE WINNER = 'Orioles'; millebe=> SELECT * FROM GAME; t_name1 |t_name2 |winner ----------+---------+--------- Red Sox |White Sox|Red Sox Yankees |Indians |Indians Devil Rays|Royals |Royals Blue Jays |Twins |Twins Orioles |Twins |Twins Red Sox |Royals |Royals Yankees |Tigers |Yankees Devil Rays|White Sox|White Sox Blue Jays |Indians |Indians Orioles |Tigers |Tigers (10 rows) - - - - - - - - - - The Twins fire their manager. His assistants should step down as well. DELETE FROM MANAGER WHERE M_NAME = 'Kelly'; millebe=> SELECT * FROM MANAGER; m_name |t_name ----------+---------- Torre |Yankees Williams |Red Sox Hargrove |Orioles Fregosi |Blue Jays Rothschild|Devil Rays Manuel |White Sox Garner |Tigers Muser |Royals Nunez |Indians (9 rows) millebe=> SELECT * FROM ASSISTANT; a_name |m_name |t_name -----------+----------+---------- Chambliss |Torre |Yankees Stottlemyre|Torre |Yankees Rice |Williams |Red Sox Kerrigan |Williams |Red Sox Crowley |Hargrove |Orioles Wiley |Hargrove |Orioles Gaston |Fregosi |Blue Jays Langford |Fregosi |Blue Jays Roberts |Rothschild|Devil Rays Williams |Rothschild|Devil Rays Joshua |Manuel |White Sox Contreras |Manuel |White Sox Jones |Manuel |Indians Pole |Manuel |Indians Madlock |Garner |Tigers Warthen |Garner |Tigers Johnson |Muser |Royals Strom |Muser |Royals (18 rows) - - - - - - - - - - Do not let a team sign a bad pitcher. CREATE ASSERTION GOOD_PITCHER CHECK (SELECT * FROM PITCHER WHERE ERA < 5.00); - - - - - - - - - - millebe=> INSERT INTO TEAM VALUES ('Yankees', 99); ERROR: Cannot insert a duplicate key into unique index team_pkey