-- Tristan Sabol 
-- Due Date 11/19/24
-- SQL Music Database
-- This program creates a database to store information about the TOP 200 songs.
-- Create the database
CREATE DATABASE Music;

-- Switch to the Music database
USE Music;

-- Create the MusicData table
CREATE TABLE MusicData (
    id INT PRIMARY KEY AUTO_INCREMENT,
    Artist VARCHAR(60),
    SongTitle VARCHAR(60),
    Album VARCHAR(60),
    Year_ VARCHAR(50),
    Peaked VARCHAR(50),
    HowLongOnCharts VARCHAR(60)
);

-- Create indexes
CREATE INDEX idx_artist ON MusicData(Artist);
CREATE INDEX idx_year ON MusicData(Year_);

-- Describe the table structure
DESCRIBE MusicData;

-- Insert values into the MusicData table
INSERT INTO MusicData (Artist, SongTitle, Album, Year_, Peaked, HowLongOnCharts)
VALUES 
('David Bowie', 'Changes', 'Hunky Dory', '1971', '41', '2'),
('Aerosmith', 'Dream On', 'Aerosmith', '1973', '6', '3'),
('Marvin Gaye', 'Sexual Healing', 'Live!', '1982', '3', '10'),
('Ann Peebles', "I Can't Stand The Rain", "I Can't Stand The Rain", '1973', '155', '7'),
('James Brown', 'Get Up(II Feel Like Being A) Sex Machine', 'The Sounds Of James Brown','1988', '15', '2'),
('Pasty Cline', 'Crazy', 'Showcase', '1998', '9', '11'),
('PJ Harvey', 'Rid Of Me', 'Rid of Me', '1993', '158', '2'),
('The Rolling Stones', 'Wild Horses', 'Singles', '1992', '98', '12'),
('Geto Boys', 'Mind Playing Tricks On Me', 'We Cant Be Stopped', '1991', '23', '20'),
('Boobie Gentry', 'Ode To Billie Joe', 'Ode To Billie Joe', '1967', '1 ', '4'),
('N.W.A.', 'Fuck Tha Police', 'Straight Outta Compton', '1988', '49 ', '3'),
('David Bowie', 'Space Oddity', 'Space Oddity', '1969', '5', '25'),
('The Jimi Hendrix Experience', 'Little Wing', 'Axis: Bold As Love', '1967', '0', '0'),
('Bob Dylan', 'Subterranean Homesick Blues', 'Bringing It All Back Home', '1965', '9', '9'),
('The Staple Singers', 'Ill Take You There', 'Be Altitude: Respect Yourself', '1972', '1', '15'),
('Michael Jackson', 'Beat It', 'Thriller', '1982', '1', '3'),
('Sinead OConnor', 'Nothing Compares 2 U', 'The Family', '1990', '1', '4'),
('Stevie Wonder', 'You Are The Sunshine Of My Life', 'Talking Book', '1972', '1', '3'),
('Simon and Garfunkel', 'The Sounds Of Silence', 'Sounds Of Silence', '1964', '1', '2'),
('The Byrds', 'Eight Miles High', 'Fifth Dimension', '1966', '14', '5'),
('Lou Reed', 'Walk On The Wild Side', 'Transformer', '1992', '16', '14'),
('Pink Floyd', 'Comfortably Numb', 'The Wall', '1979', '6', '12'),
('Billie Eilish', 'Bad Guy', 'When We All Fall Asleep Where Do We Go?', '2019', '2', '68'),
('Van Halen', 'Jump', '1984', '1983', '1', '5'),
('The Kinks', 'You Really Got Me', 'Kinks', '1964', '7', '2'),
('The Flamingos', 'I Only Have Eyes For You', 'Flamingo Serenade', '1959', '2', '3'),
('R.E.M.', 'Radio Free Europe', 'Murmur', '1983', '25', '5'),
('Television', 'Marquee Moon', ' Marquee Moon', '1977', '0', '0'),
('Nina Simone', 'Mississippi Goddam', 'Pirate Jenny', '1964', '0 ', '0'),
('Louis Armstrong', 'What A Wonderful World', 'What A Wonderful World', '1967', '1', '14'),
('The Five Satins', 'In The Still Of The Night', 'self-titled 1987', '1956', '24', '24'),
('Tom Petty and the Heartbreakers', 'American Girl', 'American Girl', '1976', '40', '1'),
('Dusty Springfield', 'Son Of A Preacher Man', 'Dusty In Memphis', '1968', '5', '25'),
('Eminem', 'Lose Yourself', '8 Mile', '2002', '1', '12'),
('Mott The Hoople', 'All The Young Dudes', 'All The Way From Stockholm To Philadelphia', '1972', '37', '1'),
('Hank Williams', 'Im So Lonley I Could Cry', 'Vol. IV', '1949', '0', '0'),
('Bob Dylan', 'Mr.Tamborine Man', 'Mr. Tambourine Man','1965', '1', '14'),
('Fleetwood Mac', 'Landslide', 'Life Becoming A Landslide (Live)', '1975', ' 88', '1'),
('Nick Drake', 'Pink Moon', ' Pink Moon', '1972', '27', '3'),
('Madonna', 'Into the Groove', 'Like a Virgin', '1985', '1', '4'),
('R.E.M.', 'Nightswimming', 'Automatic For The People', '1992', '27', '1'),
('The Who', 'Baba ORiley', 'Whos Next', '1971', '55', '1'),
('The Meters', 'Cissy Strut', 'The Meters', '1969', '0', '0'),
('Sonic Youth', 'Teenage Riot', 'Daydream Nation', '1997', '99', '1'),
('The Kingsmen', 'Louie Louie', 'The Kingsmen In Person', '1963', '26', '1'),
('The Strokes', 'Last Nite', 'Is This It', '1987', '2', '1'),
('Howlin Wolf', 'Spoonful', 'The Lovin Spoonful', '1960', '0', '0'),
('Rick James', 'Super Freak', 'Street Songs', '1981', '70', '1'),
('Creedence Clearwater Revivalm', 'Proud Mary', 'Bayou Country', '1969', '23', '1'),
('The Shirelles', 'Will You Love Me Tomorrow', 'Tonights the Night', '1960', '4', '1'),
('Green Day', 'Basket Case', 'Dookie', '1994', '7', '1'),
('Elton John', 'Rocket Man', 'Honky Château', '1972', '2', '1'),
('Led Zeppelin', 'Kashmir', 'Physical Graffiti', '1975', '80', '1'),
('Fats Domino', 'Blueberry Hill', 'Blueberry Hill (LP)', '1956', '6', '1'),
('James Taylor', 'Fire and Rain', 'Sweet Baby James', '1970', '42', '1'),
('Outkast', 'Ms. Jackson', 'Sole Sunday', '2000', '48', '1'),
('The Rolling Stones', 'Jumpin’ Jack Flash', 'Child of the Moon', '1968', '1', '2'),
('The Clash', 'London Calling', 'London Calling', '1980', '11', '1'),
('George Jones', 'He Stopped Loving Her Today', 'I Am What I Am', '1980', '0', '0'),
('Rod Stewart', 'Maggie May', 'Every Picture Tells a Story', '1971', '31', '1'),
('Bob Marley and the Wailers', 'No Woman No Cry', 'Natty Dread', '1975', '22', '1'),
('Madonna', 'Vogue', ' Live at the Reunion Arena Dallas', '1990 ', '1', '4'),
('Blondie', 'Heart of Glass', 'Parallel Lines', '1979', '1', '4'),
('Ariana Grande', 'Thank U Next', 'Thank U Next', '2018' , '1', '6'),
('Otis Redding', 'Try a Little Tenderness', 'Complete & Unbelievable: The Otis Redding Dictionary of Soul', '1966', '46', '1'),
('The Beatles', 'She Loves You', 'Twist and Shout', '1964', '1', '6'),
('Tina Turner', 'Whats Love Got to Do With It', 'Private Dancer', '1984', '3', '1'),
('Journey', 'Don’t Stop Believin', 'Escape', '1981', '6', '2'),
('Eric B. and Rakim', 'Paid in Full', 'Paid in Full', '1987', '15', '1'),
('Ben E .King', 'Stand By Me', 'Dont Play That Song!', '1961', '1', '3'),
('Martha and the Vandellas', 'Dancing In The Street', 'Dance Party', '1964', '28', '1'),
('Drake feat. Majid Jordan', 'Hold On Were Going Home', 'Nothing Was The Same', '2013', '4', '1'),
('Led Zeppelin', 'Whole Lotta Love', 'Led Zeppelin II', '1969', '21', '1'),
('TLC', 'Waterfalls', 'CrazySexyCool ', '1995', '4', '1'),
('George Michael', 'Freedom! 90', 'Listen Without Prejudice', '1990', '28', '1'),
('Sex Pistols', 'Anarchy in the U.K.', 'Nevermind The Bollocks', '1977', '38', '3'),
('Buddy Holly', 'That’ll Be the Day', 'Thatll Be the Day', '1957', '85', '1'),
('Talking Heads', 'This Must Be the Place (Naive Melody)', 'Speaking in Tongues', '1983', '51', '1 '),
('The Impressions', 'People Get Ready', 'People Get Ready', '1965', '0', '0'),
('The Beatles', 'Let It Be', 'Let It Be', '1970', '2', '1'),
('Stevie Wonder', 'Higher Ground', 'Innervisions', '1973', '4', '12'),
('R.E.M.', 'Losing My Religion', 'Out of Time', '1991', '4', '30'),
('Bruce Springsteen', 'Thunder Road', 'Born to Run', '1975', '23', '12'),
('The Beatles', 'Something', 'Abbey Road', '1969', '3', '13'),
('Sly and the Family Stone', 'Everyday People', 'Stand!', '1968', '1', '17'),
('The Cure', 'Just Like Heaven', 'Kiss Me, Kiss Me, Kiss Me', '1987', '29', '14'),
('Wu-Tang Clan', 'C.R.E.A.M', 'Enter the Wu-Tang (36 Chambers)', '1993', '8', '12'),
('The Rolling Stones', 'Sympathy for the Devil', 'Beggars Banquet', '1968', '5', '9'),
('David Bowie', 'Life on Mars?', 'Hunky Dory', '1971', '3', '22'),
('The Jackson 5', 'I Want You Back', 'Diana Ross Presents The Jackson 5', '1969', '1', '17'),
('Alanis Morissette', 'You Oughta Know', 'Jagged Little Pill', '1995', '6', '21'),
('Chuck Berry', 'Maybelline', 'Chuck Berry Is On Top', '1955', '1', '17'),
('Yeah Yeah Yeahs', 'Maps', 'Fever to Tell', '2003', '1', '20'),
('Bob Dylan', 'Blowin’ in the Wind', 'The Freewheelin’ Bob Dylan', '1963', '2', '12'),
('Bee Gees', 'Stayin’ Alive', 'Saturday Night Fever Soundtrack', '1977', '1', '18'),
('The Beatles', 'In My Life', 'Rubber Soul', '1965', '5', '16'),
('Patti Smith', 'Gloria', 'Horses', '1975', '5', '24'),
('Jay-Z', '99 Problems', 'The Black Album', '2003', '1', '8'),
('Oasis', 'Wonderwall', '(What’s the Story) Morning Glory?', '1995', '8', '18'),
('Whitney Houston', 'I Will Always Love You', 'The Bodyguard Soundtrack', '1992', '1', '40'),
('Kelly Clarkson', 'Since U Been Gone', 'Breakaway', '2004', '2', '22'),
('Little Richard', 'Good Golly, Miss Molly', 'Here’s Little Richard', '1958', '10', '20'),
('UGK feat. Outkast', 'Int’l Players Anthem (I Choose You)', 'Underground Kingz', '2007', '1', '12'),
('Aretha Franklin', '(You Make Me Feel Like) A Natural Woman', 'Lady Soul', '1967', '8', '14'),
('The Beatles', 'Hey Jude', 'Hey Jude (single)', '1968', '1', '19'),
('Guns N’ Roses', 'Sweet Child O’ Mine', 'Appetite for Destruction', '1988', '1', '30'),
('LCD Soundsystem', 'All My Friends', 'Sound of Silver', '2007', '2', '17'),
('The Rolling Stones', 'Tumbling Dice', 'Exile on Main St.', '1972', '7', '14'),
('Prince', 'Kiss', 'Parade', '1986', '1', '11'),
('Al Green', 'Let’s Stay Together', 'Let’s Stay Together', '1972', '1', '16'),
('Bob Dylan', 'Desolation Row', 'Highway 61 Revisited', '1965', '3', '23'),
('Adele', 'Rolling in the Deep', '21', '2010', '1', '72'),
('The Velvet Underground', 'I’m Waiting for the Man', 'The Velvet Underground & Nico', '1967', '22', '13'),
('Ray Charles', 'What’d I Say', 'What’d I Say', '1959', '6', '24'),
('Amy Winehouse', 'Back to Black', 'Back to Black', '2006', '2', '28'),
('The Four Tops', 'Reach Out (I’ll Be There)', 'Reach Out', '1966', '1', '14'),
('The Modern Lovers', 'Roadrunner', 'The Modern Lovers', '1976', '29', '5'),
('Johnny Cash', 'I Walk the Line', 'Johnny Cash with His Hot and Blue Guitar', '1956', '17', '43'),
('Pulp', 'Common People', 'Different Class', '1995', '2', '22'),
('Leonard Cohen', 'Hallelujah', 'Various Positions', '1984', '4', '17'),
('Beyoncé', 'Formation', 'Lemonade', '2016', '1', '18'),
('The Beatles', 'Yesterday', 'Help!', '1965', '1', '23'),
('Tracy Chapman', 'Fast Car', 'Tracy Chapman', '1988', '6', '23'),
('Elvis Presley', 'Suspicious Minds', 'From Elvis in Memphis', '1969', '1', '15'),
('Taylor Swift', 'All Too Well', 'Red', '2012', '1', '45'),
('Chic', 'Good Times', 'Risqué', '1979', '1', '30'),
('Bob Dylan', 'Tangled Up in Blue', 'Blood on the Tracks', '1975', '2', '20'),
('Simon and Garfunkel', 'Bridge Over Troubled Water', 'Bridge Over Troubled Water', '1970', '1', '16'),
('Earth, Wind & Fire', 'September', 'The Best of Earth, Wind & Fire, Vol. 1', '1978', '8', '22'),
('Ramones', 'Blitzkrieg Bop', 'Ramones', '1976', '5', '19'),
('Dolly Parton', 'Jolene', 'Jolene', '1973', '1', '15'),
('U2', 'One', 'Achtung Baby', '1991', '1', '12'),
('Led Zeppelin', 'Stairway to Heaven', 'Led Zeppelin IV', '1971', '1', '12'),
('Kate Bush', 'Running Up That Hill', 'Hounds of Love', '1985', '3', '22'),
('Grandmaster Flash and the Furious Five', 'The Message', 'The Message', '1982', '4', '15'),
('The Band', 'The Weight', 'Music from Big Pink', '1968', '1', '16'),
('Sly and the Family Stone', 'Family Affair', 'There’s a Riot Goin’ On', '1971', '1', '25'),
('Missy Elliott', 'Work It', 'Under Construction', '2002', '2', '24'),
('Madonna', 'Like a Prayer', 'Like a Prayer', '1989', '1', '34'),
('Smokey Robinson and the Miracles', 'The Tracks of My Tears', 'Going to a Go-Go', '1965', '16', '21'),
('The Beach Boys', 'Good Vibrations', 'Smiley Smile', '1966', '1', '24'),
('Donna Summer', 'I Feel Love', 'I Remember Yesterday', '1977', '6', '13'),
('Dionne Warwick', 'Walk on By', 'Walk on By', '1964', '6', '18'),
('Daddy Yankee', 'Gasolina', 'Barrio Fino', '2004', '1', '34'),
('Lauryn Hill', 'Doo Wop (That Thing)', 'The Miseducation of Lauryn Hill', '1998', '1', '36'),
('Radiohead', 'Idioteque', 'Kid A', '2000', '6', '18'),
('Elton John', 'Tiny Dancer', 'Madman Across the Water', '1971', '41', '8'),
('M.I.A.', 'Paper Planes', 'Kala', '2007', '4', '26'),
('Kendrick Lamar', 'Alright', 'To Pimp a Butterfly', '2015', '1', '28'),
('Michael Jackson', 'Billie Jean', 'Thriller', '1983', '1', '39'),
('The Temptations', 'My Girl', 'The Temptations Sing Smokey', '1964', '1', '17'),
('Bob Marley and the Wailers', 'Redemption Song', 'Uprising', '1980', '9', '20'),
('Joy Division', 'Love Will Tear Us Apart', 'Closer', '1980', '13', '10'),
('The Jimi Hendrix Experience', 'All Along the Watchtower', 'Electric Ladyland', '1968', '5', '20'),
('Otis Redding', '(Sittin’ On) the Dock of the Bay', 'The Dock of the Bay', '1968', '1', '16'),
('Prince and the Revolution', 'When Doves Cry', 'Purple Rain', '1984', '1', '25'),
('The White Stripes', 'Seven Nation Army', 'Elephant', '2003', '2', '66'),
('Little Richard', 'Tutti-Frutti', 'Heres Little Richard', '1955', '17', '5'),
('James Brown', 'Papa’s Got a Brand New Bag', 'Papa’s Got a Brand New Bag', '1965', '8', '12'),
('Chuck Berry', 'Johnny B. Goode', 'Chuck Berry Is on Top', '1958', '8', '23'),
('Notorious B.I.G.', 'Juicy', 'Ready to Die', '1994', '1', '36'),
('The Rolling Stones', '(I Can’t Get No) Satisfaction', 'Out of Our Heads', '1965', '1', '16'),
('Lorde', 'Royals', 'Pure Heroine', '2013', '1', '50'),
('Dr. Dre feat. Snoop Doggy Dogg', 'Nuthin’ but a ‘G’ Thang', 'The Chronic', '1992', '2', '21'),
('Talking Heads', 'Once in a Lifetime', 'Remain in Light', '1980', '14', '8'),
('Bruce Springsteen', 'Born to Run', 'Born to Run', '1975', '3', '15'),
('Joni Mitchell', 'A Case of You', 'Blue', '1971', '3', '18'),
('Kanye West feat. Pusha T', 'Runaway', 'My Beautiful Dark Twisted Fantasy', '2010', '12', '41'),
('The Beatles', 'A Day in the Life', 'Sgt. Pepper’s Lonely Hearts Club Band', '1967', '1', '16'),
('David Bowie', 'Heroes', 'Heroes', '1977', '24', '26'),
('The Ronettes', 'Be My Baby', 'Presenting the Fabulous Ronettes Featuring Veronica', '1963', '2', '22'),
('Billie Holiday', 'Strange Fruit', 'Strange Fruit', '1939', '16', '4'),
('Robyn', 'Dancing on My Own', 'Body Talk', '2010', '8', '51'),
('John Lennon', 'Imagine', 'Imagine', '1971', '3', '20'),
('Prince and the Revolution', 'Purple Rain', 'Purple Rain', '1984', '2', '30'),
('Queen', 'Bohemian Rhapsody', 'A Night at the Opera', '1975', '1', '40'),
('Beyoncé feat. Jay-Z', 'Crazy in Love', 'Dangerously in Love', '2003', '1', '40'),
('The Beatles', 'I Want to Hold Your Hand', 'Meet the Beatles!', '1964', '1', '15'),
('The Kinks', 'Waterloo Sunset', 'Something Else by The Kinks', '1967', '2', '12'),
('The Rolling Stones', 'Gimme Shelter', 'Let It Bleed', '1969', '1', '33'),
('Stevie Wonder', 'Superstition', 'Talking Book', '1972', '1', '16'),
('The Beach Boys', 'God Only Knows', 'Pet Sounds', '1966', '39', '3'),
('Outkast', 'Hey Ya!', 'Speakerboxxx/The Love Below', '2003', '1', '49'),
('Fleetwood Mac', 'Dreams', 'Rumours', '1977', '1', '19'),
('Missy Elliott', 'Get Ur Freak On', 'Miss E... So Addictive', '2001', '7', '26'),
('The Beatles', 'Strawberry Fields Forever', 'Magical Mystery Tour', '1967', '8', '13'),
('Marvin Gaye', 'What’s Going On', 'What’s Going On', '1971', '2', '26'),
('Nirvana', 'Smells Like Teen Spirit', 'Nevermind', '1991', '6', '57'),
('Bob Dylan', 'Like a Rolling Stone', 'Highway 61 Revisited', '1965', '2', '30'),
('Sam Cooke', 'A Change Is Gonna Come', 'Ain’t That Good News', '1964', '9', '15'),
('Public Enemy', 'Fight the Power', 'Fear of a Black Planet', '1989', '1', '20'),
('Aretha Franklin', 'Respect', 'I Never Loved a Man the Way I Love You', '1967', '1', '13'),
('The Beatles', 'Hey Jude', 'Single', '1968', '1', '19'),
('Elvis Presley', 'Hound Dog', 'Single', '1956', '1', '11'),
('Chuck Berry', 'Johnny B. Goode', 'Chuck Berry Is on Top', '1958', '2', '15'),
('The Rolling Stones', '(I Can’t Get No) Satisfaction', 'Out of Our Heads', '1965', '1', '14'),
('Aretha Franklin', 'Respect', 'I Never Loved a Man the Way I Love You', '1967', '1', '12'),
('The Beach Boys', 'Good Vibrations', 'Smiley Smile', '1966', '1', '14'),
('Marvin Gaye', 'What’s Going On', 'What’s Going On', '1971', '2', '11'),
('Sam Cooke', 'A Change Is Gonna Come', 'Ain’t That Good News', '1964', '9', '10');

-- 1. How many hits are from the 70’s
SELECT COUNT(*) FROM MusicData WHERE Year_ LIKE '197%';

-- 2. How many hits are from the 80s
SELECT COUNT(*) FROM MusicData WHERE Year_ LIKE '198%';

-- 3. How many peaked at “2”
SELECT COUNT(*) FROM MusicData WHERE Peaked = '2';

-- 4. How many peaked longer than “10” weeks
SELECT COUNT(*) FROM MusicData WHERE HowLongOnCharts > '10';

-- 5. Which songs have “Love” in the title
SELECT * FROM MusicData WHERE SongTitle LIKE '%Love%';

-- 6. Display all of the hits from “Led Zeppelin”
SELECT * FROM MusicData WHERE Artist = 'Led Zeppelin';

-- 7. Display all of the hits that were number “1” in the year “1983”
SELECT * FROM MusicData WHERE Peaked = '1' AND Year_ = '1983';

-- 8. Display all of the hits in 1976 that were not number “1”
SELECT * FROM MusicData WHERE Year_ = '1976' AND Peaked <> '1';

-- 9. How many times did “Michael Jackson” make the top 200
SELECT COUNT(*) FROM MusicData WHERE Artist = 'Michael Jackson';

-- 10. Display all of the hits from the 60’s
SELECT * FROM MusicData WHERE Year_ LIKE '196%';

-- 11. Display all of the hits that have “Rock” in the title
SELECT * FROM MusicData WHERE SongTitle LIKE '%Rock%';

-- 12. Display all of the hits from the 00s
SELECT * FROM MusicData WHERE Year_ LIKE '200%';

-- 13. How many hits were in the 00s
SELECT COUNT(*) FROM MusicData WHERE Year_ LIKE '200%';

-- 14. Display all of the hits that peaked below “2”
SELECT * FROM MusicData WHERE Peaked < '2';

-- 15. Display all the hits that peaked at 1 from “Elvis Presley”
SELECT * FROM MusicData WHERE Artist = 'Elvis Presley' AND Peaked = '1';
drop database Music;