Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE
- CREATE TABLE smgpresidents(winner VARCHAR2(15) NOT NULL,w_party VARCHAR2(15), w_state VARCHAR2(15),PRIMARY KEY(winner));
- CREATE TABLE smglosers(loser VARCHAR2(15) NOT NULL,l_party VARCHAR2(15),PRIMARY KEY(loser));
- CREATE TABLE smgelectionwinner(elect_year VARCHAR2(4) NOT NULL,winner VARCHAR2(15),w_votes NUMBER,PRIMARY KEY(elect_year));
- CREATE TABLE smgelectionloser(elect_year VARCHAR2(4) NOT NULL,loser VARCHAR2(15) NOT NULL, l_votes NUMBER,PRIMARY KEY(elect_year,loser));
- DROP
- DROP TABLE smgpresidents;
- DROP TABLE smglosers;
- DROP TABLE smgelectionwinner;
- DROP TABLE smgelectionloser;
- INSERT
- INSERT INTO smgpresidents VALUES ('EISENHOWER','REPUBLICAN','TEXAS');
- INSERT INTO smgpresidents VALUES ('KENNEDY','DEMOCRAT','MASS');
- INSERT INTO smgpresidents VALUES ('JOHNSON','DEMOCRAT','TEXAS');
- INSERT INTO smgpresidents VALUES ('NIXON','REPUBLICAN','CALIF');
- INSERT INTO smgpresidents VALUES ('CARTER','DEMOCRAT','NULL');
- INSERT INTO smgpresidents VALUES ('REAGAN','REPUBLICAN','NULL');
- INSERT INTO smgpresidents VALUES ('BUSH','REPUBLICAN','NULL');
- INSERT INTO smgpresidents VALUES ('CLINTON','DEMOCRAT','NULL');
- INSERT INTO smglosers VALUES('STEVENSON','DEMOCRAT');
- INSERT INTO smglosers VALUES('NIXON','REPUBLICAN');
- INSERT INTO smglosers VALUES('GOLDWATER','REPUBLICAN');
- INSERT INTO smglosers VALUES('HUMPHREY','DEMOCRAT');
- INSERT INTO smglosers VALUES('WALLACE','INDEPENDENT');
- INSERT INTO smglosers VALUES('McGOVERN','DEMOCRAT');
- INSERT INTO smglosers VALUES('FORD','DEMOCRAT');
- INSERT INTO smglosers VALUES('CARTER','DEMOCRAT');
- INSERT INTO smglosers VALUES('ANDERSON','INDEPENDENT');
- INSERT INTO smglosers VALUES('MONTALE','DEMOCRAT');
- INSERT INTO smglosers VALUES('DOUKAKIS','DEMOCRAT');
- INSERT INTO smglosers VALUES('BUSH','REPUBLICAN');
- INSERT INTO smglosers VALUES('PERAULT','INDEPENDENT');
- INSERT INTO smgelectionwinner VALUES('1952','EISENHOWER',442);
- INSERT INTO smgelectionwinner VALUES('1956','EISENHOWER',447);
- INSERT INTO smgelectionwinner VALUES('1960','KENNEDY',303);
- INSERT INTO smgelectionwinner VALUES('1964','JOHNSON',486);
- INSERT INTO smgelectionwinner VALUES('1968','NIXON',301);
- INSERT INTO smgelectionwinner VALUES('1972','NIXON',520);
- INSERT INTO smgelectionwinner VALUES('1976','CARTER',297);
- INSERT INTO smgelectionwinner VALUES('1980','REAGAN',489);
- INSERT INTO smgelectionwinner VALUES('1984','REAGAN',525);
- INSERT INTO smgelectionwinner VALUES('1988','BUSH',426);
- INSERT INTO smgelectionwinner VALUES('1992','CLINTON',NULL);
- INSERT INTO smgelectionloser VALUES('1952','STEVENSON',89);
- INSERT INTO smgelectionloser VALUES('1956','STEVENSON',73);
- INSERT INTO smgelectionloser VALUES('1960','NIXON',219);
- INSERT INTO smgelectionloser VALUES('1964','GOLDWATER',52);
- INSERT INTO smgelectionloser VALUES('1968','HUMPHREY',191);
- INSERT INTO smgelectionloser VALUES('1968','WALLACE',46);
- INSERT INTO smgelectionloser VALUES('1972','McGOVER',17);
- INSERT INTO smgelectionloser VALUES('1976','FORD',240);
- INSERT INTO smgelectionloser VALUES('1980','CARTER',49);
- INSERT INTO smgelectionloser VALUES('1980','ANDERSON',0);
- INSERT INTO smgelectionloser VALUES('1984','MONDALE',13);
- INSERT INTO smgelectionloser VALUES('1988','DOUKAKIS',41);
- INSERT INTO smgelectionloser VALUES('1992','BUSH',NULL);
- INSERT INTO smgelectionloser VALUES('1992','PERAULT',NULL);
- SELECT
- SELECT * FROM smgPresidents;
- SELECT * FROM smgPresidents ORDER BY winner ASC;
- SELECT winner FROM smgElectionWinner GROUP BY winner;
- SELECT * FROM smgPresidents ORDER BY W_party, Winner ASC;
- SELECT * FROM smgPresidents WHERE w_party = 'REPUBLICAN';
- SELECT * FROM smgPresidents WHERE w_party = 'REPUBLICAN' ORDER BY winner ASC;
- SELECT * FROM smgPresidents WHERE winner = 'NIXON';
- SELECT * FROM smgLosers WHERE loser = 'NIXON';
- SELECT * FROM smgElectionwinner WHERE winner = 'NIXON';
- SELECT * FROM smgElectionLoser WHERE loser = 'NIXON';
- SELECT * FROM smgElectionLoser WHERE l_votes < 80;
- SELECT loser FROM smgElectionLoser GROUP BY loser HAVING COUNT(*) > 1;
- SELECT * FROM smgElectionWinner, smgElectionLoser, smgPresidents, smgLosers
- WHERE smgElectionWinner.elect_year = smgElectionLoser.elect_year
- AND smgPresidents.winner = smgElectionwinner.winner
- AND smgElectionLoser.loser = smgLosers.loser;
Add Comment
Please, Sign In to add comment