-- Tristan Sabol 
-- Due Date 1/13/25
-- SQL Presidents
-- This program creates a database to store information about the presedints.

-- Create the database and set its name.
create database presidents;
-- Switch to the presidents database.
use presidents;
-- Create a table with fields for presidents information.
create table presidentsData (
    Presidential_num int primary key auto_increment,
    Last_Name varchar(20),
    First_Name varchar(20),
    Party varchar(50),
    Nickname varchar(50),
    Year_of_Birth varchar(5),
    Year_of_Death varchar(5),
    Term varchar(5),
    Decade varchar(6),
    Other_Positions varchar(255),
    Major_Accomplishments varchar(255),
    Wars_During_Office varchar(80)
);
-- Create  index's for the model column to optimize queries.
create index idx_Last_Name ON presidentsData(Last_Name);
create index idx_Party ON presidentsData(Party);

-- Describe the presidentsData table to verify its structure.
Describe presidentsData;

-- Insert values into the CarsData table.
insert into presidentsData
(Last_Name, First_Name, Party, Nickname, Year_of_Birth, Year_of_Death, Term, Decade, Other_Positions, Major_Accomplishments, Wars_During_Office)
value
( 'Washington', 'George ', 'nonpartisan ', 'Father of His Country', '1732', '1799', '2', '1789', 'Military Leader', 'Establishing the cabinet', '3'),
( 'Adams', 'John ', 'Federalist', 'His Rotundity', '1735', '1826', '1', '1797', 'political philosopher', 'The Convention of Montefontaine', '0'),
( 'Jefferson', 'Thomas  ', 'Republican Party', 'Long Tom', '1743', '1826', '2', '1801', 'Congressman', 'Abolished the Alien and Sedition Acts', '1'),
( 'Madison', 'James  ', 'Republican Party', 'Little Jemmy', '1751', '1836', '2', '1809', 'State legislator', ' Key role in drafting the Constitution', '1'),
( 'Monroe', 'James  ', 'Republican Party', ' Last Cocked Hat', '1758', '1831', '2', '1817', 'US Senator', 'Monroe Doctrine', '1'),
( 'Adams', 'John Quincy  ', 'Republican Party', 'Old Man Eloquent', '1767', '1848', '1', '1825', 'State legislator', 'Contributions to education and scientific advancement', ' 1'),
( 'Jackson', 'Andrew  ', 'Democratic Party', 'Jackass', '1767', '1845', '2', '1829', ' US Senator', 'signed the Indian Removal Act', '4'),
( 'Buren', 'Martin ', 'Democratic Party', 'Blue Whiskey Van ', '1782', '1862', '1', '1837', 'US Senator	', 'anti-slavery stances', '2'),
( 'Harrison', 'William  ', 'Whig Party', 'Old Granny', '1773', '1841', '1', '1841', 'Territorial governor', 'cavalry to victory at the Battle of the Thames', '2'),
( 'Tyler', 'John ', 'Whig Party', 'His Accidency', '1790', '1862', '0', '1841', 'US. Representative', '1st to succeed a dead president', '3'),
( 'Polk', 'James  ', 'Democratic Party', 'Young Hickory', '1795', '1849', '1', '1845', 'US Representative', 'presided over the Mexican War', '5'),
( 'Taylor', 'Zachary ', 'Whig Party', 'Old Rough and Ready', '1784', '1850', '1', '1849', 'Military', 'Compromise of 1850', '4'),
( 'Fillmore', 'Millard ', 'Whig Party', 'Last of the Whigs', '1800', '1874', '0', '1850', 'US Representative', 'Passed compromise of 1850, allowing slavery in the South', '5'),
( 'Pierce', 'Franklin  ', 'Democratic Party', 'Purse', '1804', '1869', '1', '1853', 'US Representative', 'start of Republican party.', '8'),
( 'Buchanan', 'James  ', 'Democratic Party', 'Old Buck', '1791', '1868', '1', '1857', 'US Senator', 'compromise to keep the South from seceding from the Union', '7'),
( 'Lincoln', 'Abraham  ', 'Republican Party', 'Abe', '1822', '1865', '2', '1861', 'Military', 'Opposed slavery', '8'),
( 'Johnson', 'Andrew  ', 'Democratic Party', 'Sir Veto', '1808', '1875', '0', '1865', 'State governor', 'Democrat VP to serve under a Republican president', '8'),
( 'Grant', 'Ulysses  ', 'Republican Party', 'Butcher', '1822', '1885', '2', '1869', 'Military', 'Republican-Union Hero General', '5'),
( 'Hayes', 'Rutherford  ', 'Republican Party', 'Dark Horse President', '1822', '1893', '1', '1877', 'US Representative', 'Republican one of only 3 Presidents to lose the popular vote', '2'),
( 'Garfield', 'James  ', 'Republican Party', 'Canal Boy', '1831', '1881', '1', '1881', 'Ordained minister', 'Republican-Last president born in a log cabin', '1'),
( 'Arthur', 'Chester  ', 'Republican Party', 'Chet', '1829', '1886', '0', '1881', 'Lawyer', 'Republican-Pendleton Act, helped create the Civil Service.', '2'),
( 'Cleveland', 'Grover  ', 'Democratic Party', 'Big Steve', '1837', '1908', '1', '1885', 'Lawyer', 'Successful 1st term. expanded the civil service', '4'),
( 'Harrison', 'Benjamin  ', 'Republican Party', 'Human Iceberg', '1833', '1901', '1', '1889', 'Military', 't. Added 6 states. Caught between reforms who were fighting the spoils system', '2'),
( 'Cleveland', 'Grover ', 'Democratic Party', 'Big Steve', '1837', '1908', '1', '1893', 'Lawyer', 'Only president to serve non-consecutive terms', '4'),
( 'McKinley', 'William  ', 'Republican Party', 'Major', '1843', '1901', '2', '1897', 'Lawyer', 'Republican-Spanish American War,', '6'),
( 'Roosevelt', 'Theodore  ', 'Republican Party', 'Teddy', '1858', '1919', '2', '1901', 'policeman', ', passed clean food acts', '7'),
( 'Taft', 'William  ', 'Republican Party', 'Big Bill', '1857', '1930', '1', '1909', 'dean', 'Republican, continued Roosevelts policies- broke up trusts', '0'),
( 'Wilson', 'Woodrow  ', 'Democratic Party', 'Phrasemaker', '1856', '1924', '2', '1913', 'Academic', 'Democratic, Pres, during WW1,', '10'),
( 'Harding', 'Warren  ', 'Republican Party', 'Wobbly Warren', '1865', '1923','1','1921','Journalist', 'Republican- died in office just as the Teapot Dome scandal became public', '6'),
( 'Coolidge', 'Calvin  ', 'Republican Party', 'Cal', '1872', '1933', '1', '1923', 'Lawyer', 'Republican-Minimum government interference.', '5'),
( 'Hoover', 'Herbert ', 'Republican Party', 'Chief', '1884', '1972', '1', '1929', 'Businessman', 'Republican-Geologist. "The Great Humanitarian"', '2'),
( 'Roosevelt', 'Franklin ', 'Democratic Party', 'FDR', '1882', '1945', '4', '1933', 'Lawyer', 'Led America out of the Depression and through victory in WWII.', '2'),
( 'Truman', 'Harry  ', 'Democratic Party', 'Haberdasher Harry', '1884', '1972', '2', '1945', 'Farmer', 'Democratic-Made choice to drop the atom bomb to prevent invasion of Japan.', '3'),
( 'Eisenhower', 'Dwight  ', 'Republican Party', 'Ike', '1890', '1969', '2', '1953', 'Military officer,', 'Ended war in Korea', '5'),
( 'Kennedy', 'John  ', 'Democratic Party', 'American Erlander', '1917', '1963', '1', '1961', 'Journalist', 'defused Cuban Missile Crisis, 1st Catholic Pres., Peace Corps.', '3'),
( 'Johnson', 'Lyndon  ', 'Democratic Party', 'Bull Johnson', '1908', '1973', '2', '1963', 'Teacher', 'Democratic-Finished Kennedys passing of Civil Rights Act. ', '3'),
( 'Nixon', 'Richard  ', 'Republican Party', 'Tricky Dick', '1913', '1994', '2', '1969', 'US Representative', 'Republican-Known for foreign policy. SALT treaty, Détente,', '1'),
( 'Ford', 'Gerald  ', 'Republican Party', 'Jerry', '1913', '2006', '0', '1974', 'Lawyer', 'Republican-Only president never elected to Pres or VP. Saw fall', '2'),
( 'Carter', 'Jimmy  ', 'Democratic Party', 'Jimmy', '1924', '2024', '1', '1977', 'Military', 'Democratic-Naval officer on nuclear submarines, Gas Shortages', '0'),
( 'Reagan', 'Ronald  ', 'Republican Party', 'Dutch', '1911', '2004', '2', '1981', 'Military', ' Two terms--oversaw fall of Communism.', '5'),
( 'Bush', 'George H.W ', 'Republican Party', 'Papa Bush', '1924', '2018', '1', '1989', 'Businessman', ' First Gulf War, passage of ADA', '4'),
( 'Clinton', 'Bill  ', 'Democratic Party', 'Bubba', '1946', 'Havent', '2', '1993', 'Lawyer', 'Democratic, passed NAFTA (no tariffs between US, Canada, & Mexico)', '3'),
( 'Bush', 'George W ', 'Republican Party', 'Dubya', '1946', 'Havent', '2', '2001', 'Businessman', 'during 9/11, 2nd Gulf War, economic downturn, debt', '1'),
( 'Obama', 'Barack  ', 'Democratic Party', 'Barry O’Bomber', '1961', 'Havent', '2', '2009', 'Lawyer', 'Passage of "ObamaCare"', '2'),
( 'Trump', 'Donald  ', 'Republican Party', 'President Snowflake', '1946', 'Havent', '1', '2017', 'Businessman', 'America gained 7 million new jobs – more than three times .', '0'),
( 'Biden', 'Joe  ', 'Democratic Party', 'Sleepy Joe', '1942', 'Havent', '1', '2021', 'Lawyer', 'Lowering Costs, Tackling Junk Fees, and Promoting Competition', '3')
;
-- Where There are no wars 
select First_Name AS "First", Last_Name AS "Last", Wars_During_Office AS "Wars" FROM presidentsData WHERE Wars_During_Office = "0";
-- Where They severd for one term
select First_Name AS "First", Last_Name AS "Last", Term AS "Terms" FROM presidentsData WHERE Term = "1";
-- Where They severd for two term
select First_Name AS "First", Last_Name AS "Last", Term AS "Terms" FROM presidentsData WHERE Term = "2";
-- Where there first name is Jamesz
select First_Name AS "First", Last_Name AS "Last" FROM presidentsData WHERE First_Name = "James";
-- The Party they belong to this one is Democratic 
select First_Name AS "First", Last_Name AS "Last", Party AS "Party" FROM presidentsData WHERE Party = "Democratic Party"; 
-- The Party they belong to this one is Republican 
select First_Name AS "First", Last_Name AS "Last", Party AS "Party" FROM presidentsData WHERE Party = "Republican Party";
-- Where there last name starts with a M
select First_Name AS "First", Last_Name AS "Last" FROM presidentsData WHERE Last_Name LIKE 'M%';
-- Where They severd for more tham two term
select First_Name AS "First", Last_Name AS "Last",  Term AS "Terms" FROM presidentsData WHERE Term > '2';
-- The party is not in Democratic or Republican
select First_Name AS "First", Last_Name AS "Last", Party AS "Party" FROM presidentsData WHERE Party not in ("Republican Party", "Democratic Party") ;





-- This deletes the datbase from the computer 
drop database presidents;