SHARE
TWEET

Untitled

a guest Apr 30th, 2018 93 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE
  2.  
  3. CREATE TABLE smgpresidents(winner VARCHAR2(15) NOT NULL,w_party VARCHAR2(15), w_state VARCHAR2(15),PRIMARY KEY(winner));
  4. CREATE TABLE smglosers(loser VARCHAR2(15) NOT NULL,l_party VARCHAR2(15),PRIMARY KEY(loser));
  5. CREATE TABLE smgelectionwinner(elect_year VARCHAR2(4) NOT NULL,winner VARCHAR2(15),w_votes NUMBER,PRIMARY KEY(elect_year));
  6. CREATE TABLE smgelectionloser(elect_year VARCHAR2(4) NOT NULL,loser VARCHAR2(15) NOT NULL, l_votes NUMBER,PRIMARY KEY(elect_year,loser));
  7.  
  8.  
  9.  
  10.  
  11.  
  12. DROP
  13.  
  14. DROP TABLE smgpresidents;
  15. DROP TABLE smglosers;
  16. DROP TABLE smgelectionwinner;
  17. DROP TABLE smgelectionloser;
  18.  
  19.  
  20.  
  21.  
  22.  
  23. INSERT
  24. INSERT INTO smgpresidents VALUES ('EISENHOWER','REPUBLICAN','TEXAS');
  25. INSERT INTO smgpresidents VALUES ('KENNEDY','DEMOCRAT','MASS');
  26. INSERT INTO smgpresidents VALUES ('JOHNSON','DEMOCRAT','TEXAS');
  27. INSERT INTO smgpresidents VALUES ('NIXON','REPUBLICAN','CALIF');
  28. INSERT INTO smgpresidents VALUES ('CARTER','DEMOCRAT','NULL');
  29. INSERT INTO smgpresidents VALUES ('REAGAN','REPUBLICAN','NULL');
  30. INSERT INTO smgpresidents VALUES ('BUSH','REPUBLICAN','NULL');
  31. INSERT INTO smgpresidents VALUES ('CLINTON','DEMOCRAT','NULL');
  32.  
  33.  
  34. INSERT INTO smglosers VALUES('STEVENSON','DEMOCRAT');
  35. INSERT INTO smglosers VALUES('NIXON','REPUBLICAN');
  36. INSERT INTO smglosers VALUES('GOLDWATER','REPUBLICAN');
  37. INSERT INTO smglosers VALUES('HUMPHREY','DEMOCRAT');
  38. INSERT INTO smglosers VALUES('WALLACE','INDEPENDENT');
  39. INSERT INTO smglosers VALUES('McGOVERN','DEMOCRAT');
  40. INSERT INTO smglosers VALUES('FORD','DEMOCRAT');
  41. INSERT INTO smglosers VALUES('CARTER','DEMOCRAT');
  42. INSERT INTO smglosers VALUES('ANDERSON','INDEPENDENT');
  43. INSERT INTO smglosers VALUES('MONTALE','DEMOCRAT');
  44. INSERT INTO smglosers VALUES('DOUKAKIS','DEMOCRAT');
  45. INSERT INTO smglosers VALUES('BUSH','REPUBLICAN');
  46. INSERT INTO smglosers VALUES('PERAULT','INDEPENDENT');
  47.  
  48.  
  49. INSERT INTO smgelectionwinner VALUES('1952','EISENHOWER',442);
  50. INSERT INTO smgelectionwinner VALUES('1956','EISENHOWER',447);
  51. INSERT INTO smgelectionwinner VALUES('1960','KENNEDY',303);
  52. INSERT INTO smgelectionwinner VALUES('1964','JOHNSON',486);
  53. INSERT INTO smgelectionwinner VALUES('1968','NIXON',301);
  54. INSERT INTO smgelectionwinner VALUES('1972','NIXON',520);
  55. INSERT INTO smgelectionwinner VALUES('1976','CARTER',297);
  56. INSERT INTO smgelectionwinner VALUES('1980','REAGAN',489);
  57. INSERT INTO smgelectionwinner VALUES('1984','REAGAN',525);
  58. INSERT INTO smgelectionwinner VALUES('1988','BUSH',426);
  59. INSERT INTO smgelectionwinner VALUES('1992','CLINTON',NULL);
  60.  
  61.  
  62. INSERT INTO smgelectionloser VALUES('1952','STEVENSON',89);
  63. INSERT INTO smgelectionloser VALUES('1956','STEVENSON',73);
  64. INSERT INTO smgelectionloser VALUES('1960','NIXON',219);
  65. INSERT INTO smgelectionloser VALUES('1964','GOLDWATER',52);
  66. INSERT INTO smgelectionloser VALUES('1968','HUMPHREY',191);
  67. INSERT INTO smgelectionloser VALUES('1968','WALLACE',46);
  68. INSERT INTO smgelectionloser VALUES('1972','McGOVER',17);
  69. INSERT INTO smgelectionloser VALUES('1976','FORD',240);
  70. INSERT INTO smgelectionloser VALUES('1980','CARTER',49);
  71. INSERT INTO smgelectionloser VALUES('1980','ANDERSON',0);
  72. INSERT INTO smgelectionloser VALUES('1984','MONDALE',13);
  73. INSERT INTO smgelectionloser VALUES('1988','DOUKAKIS',41);
  74. INSERT INTO smgelectionloser VALUES('1992','BUSH',NULL);
  75. INSERT INTO smgelectionloser VALUES('1992','PERAULT',NULL);
  76.  
  77.  
  78.  
  79.  
  80.  
  81.  
  82.  
  83. SELECT
  84.  
  85. SELECT * FROM smgPresidents;
  86.  
  87. SELECT * FROM smgPresidents ORDER BY winner ASC;
  88.  
  89. SELECT winner FROM smgElectionWinner GROUP BY winner;
  90.  
  91. SELECT * FROM smgPresidents ORDER BY W_party, Winner ASC;
  92.  
  93. SELECT * FROM smgPresidents WHERE w_party = 'REPUBLICAN';
  94.  
  95. SELECT * FROM smgPresidents WHERE w_party = 'REPUBLICAN' ORDER BY winner ASC;
  96.  
  97. SELECT * FROM smgPresidents WHERE winner = 'NIXON';
  98. SELECT * FROM smgLosers WHERE loser = 'NIXON';
  99. SELECT * FROM smgElectionwinner WHERE winner = 'NIXON';
  100. SELECT * FROM smgElectionLoser WHERE loser = 'NIXON';
  101.  
  102. SELECT * FROM smgElectionLoser WHERE l_votes < 80;
  103.  
  104. SELECT loser FROM smgElectionLoser GROUP BY loser HAVING COUNT(*) > 1;
  105.  
  106. SELECT * FROM smgElectionWinner, smgElectionLoser, smgPresidents, smgLosers
  107. WHERE smgElectionWinner.elect_year = smgElectionLoser.elect_year
  108. AND smgPresidents.winner = smgElectionwinner.winner
  109. AND smgElectionLoser.loser = smgLosers.loser;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top