Skip to content

Instantly share code, notes, and snippets.

@knakayama

knakayama/garbage.sql

Last active January 15, 2021 13:53
Show Gist options
  • Save knakayama/bbabdb54b0470aea5218b93aa02c0515 to your computer and use it in GitHub Desktop.
Save knakayama/bbabdb54b0470aea5218b93aa02c0515 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
garbage.sql
--- Create a database in your MySQL. Name it as HR.
CREATE DATABASE `HR`;
USE `HR`;
--- For the given/attached flat file, design a good database (in terms of different tables) for that dataset and migrate the data in the created database in 1 (use ETL or Data Wrangling tool).
CREATE TABLE Employee (
Employee_Name VARCHAR(255),
EmpID INT(255),
Salary INT(255),
Termd VARCHAR(255),
PositionID INT(255),
Position VARCHAR(255),
State CHAR(2),
Zip INT(255),
DOB DATE,
Sex CHAR(1),
MaritalDesc VARCHAR(255),
CitizenDesc VARCHAR(255),
HispanicLatino VARCHAR(255),
RaceDesc VARCHAR(255),
DateofHire DATE,
DateofTermination DATE,
TermReason VARCHAR(255),
EmploymentStatus VARCHAR(255),
Department VARCHAR(255),
ManagerName VARCHAR(255),
ManagerID INT(255),
PerformanceScore VARCHAR(255),
EmpSatisfaction INT(255),
DaysLateLast30 INT(255),
Absences INT(255)
);
LOAD DATA LOCAL INFILE '/hr/HRDataset.csv' INTO TABLE Employee
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@Employee_Name, @EmpID, @Salary, @Termd, @PositionID, @Position, @State, @Zip, @DOB, @Sex, @MaritalDesc, @CitizenDesc, @HispanicLatino, @RaceDesc, @DateofHire, @DateofTermination, @TermReason, @EmploymentStatus, @Department, @ManagerName, @ManagerID, @PerformanceScore, @EmpSatisfaction, @DaysLateLast30, @Absences)
SET Employee_Name = @Employee_Name,
EmpID = @EmpID,
Salary = @Salary,
Termd = @Termd,
PositionID = @PositionID,
Position = @Position,
State = @State,
Zip = @Zip,
DOB = STR_TO_DATE(@DOB, '%m/%d/%Y'),
Sex = @Sex,
MaritalDesc = @MaritalDesc,
CitizenDesc = @CitizenDesc,
HispanicLatino = @HispanicLatino,
RaceDesc = @RaceDesc,
DateofHire = STR_TO_DATE(@DateofHire, '%m/%d/%Y'),
DateofTermination = STR_TO_DATE(@DateofTermination, '%m/%d/%Y'),
TermReason = @TermReason,
EmploymentStatus = @EmploymentStatus,
Department = @Department,
ManagerName = @ManagerName,
ManagerID = @ManagerID,
PerformanceScore = @PerformanceScore,
EmpSatisfaction = @EmpSatisfaction,
DaysLateLast30 = @DaysLateLast30,
Absences = @Absences;
--- Use simple SQL statement to extract Names, Gender, Race Description, and the Performance Score of the Employee.
SELECT Employee_Name, Sex, RaceDesc, PerformanceScore FROM `Employee`;
+---------------------------+------+----------------------------------+-------------------+
| Employee_Name | Sex | RaceDesc | PerformanceScore |
+---------------------------+------+----------------------------------+-------------------+
| Adinolfi, Wilson K | M | White | Exceeds |
| Ait Sidi, Karthikeyan | M | White | Fully Meets |
| Akinkuolie, Sarah | F | White | Fully Meets |
| Alagbe,Trina | F | White | Fully Meets |
| Anderson, Carol | F | White | Fully Meets |
| Anderson, Linda | F | White | Exceeds |
| Andreola, Colby | F | White | Fully Meets |
| Athwal, Sam | M | White | Fully Meets |
| Bachiochi, Linda | F | Black or African American | Fully Meets |
| Bacong, Alejandro | M | White | Fully Meets |
| Baczenski, Rachael | F | Black or African American | Fully Meets |
| Barbara, Thomas | M | Black or African American | Fully Meets |
| Barbossa, Hector | M | Black or African American | Exceeds |
| Barone, Francesco A | M | Two or more races | Fully Meets |
| Barton, Nader | M | White | Fully Meets |
| Bates, Norman | M | White | Fully Meets |
| Beak, Kimberly | F | White | Exceeds |
| Beatrice, Courtney | F | White | Fully Meets |
| Becker, Renee | F | White | Fully Meets |
| Becker, Scott | M | Asian | Fully Meets |
| Bernstein, Sean | M | White | Fully Meets |
| Biden, Lowan M | F | Asian | Fully Meets |
| Billis, Helen | F | White | Exceeds |
| Blount, Dianna | F | White | Needs Improvement |
| Bondwell, Betsy | F | White | Fully Meets |
| Booth, Frank | M | Black or African American | Fully Meets |
| Boutwell, Bonalyn | F | Black or African American | Fully Meets |
| Bozzi, Charles | M | Asian | Fully Meets |
| Brill, Donna | F | White | Fully Meets |
| Brown, Mia | F | Black or African American | Fully Meets |
| Buccheri, Joseph | M | White | Fully Meets |
| Bugali, Josephine | F | Black or African American | Fully Meets |
| Bunbury, Jessica | F | Black or African American | Fully Meets |
| Burke, Joelle | F | Black or African American | Fully Meets |
| Burkett, Benjamin | M | White | Fully Meets |
| Cady, Max | M | White | Fully Meets |
| Candie, Calvin | M | White | Exceeds |
| Carabbio, Judith | F | White | Fully Meets |
| Carey, Michael | M | Black or African American | Fully Meets |
| Carr, Claudia N | F | Black or African American | Fully Meets |
| Carter, Michelle | F | White | Fully Meets |
| Chace, Beatrice | F | White | Fully Meets |
| Champaigne, Brian | M | White | Fully Meets |
| Chan, Lin | F | White | Fully Meets |
| Chang, Donovan E | M | White | Fully Meets |
| Chigurh, Anton | M | Black or African American | Fully Meets |
| Chivukula, Enola | F | White | Fully Meets |
| Cierpiszewski, Caroline | F | Black or African American | Fully Meets |
| Clayton, Rick | M | White | Fully Meets |
| Cloninger, Jennifer | F | White | Fully Meets |
| Close, Phil | M | White | Fully Meets |
| Clukey, Elijian | M | White | Exceeds |
| Cockel, James | M | White | Fully Meets |
| Cole, Spencer | M | Black or African American | Needs Improvement |
| Corleone, Michael | M | White | Needs Improvement |
| Corleone, Vito | M | Black or African American | Exceeds |
| Cornett, Lisa | F | White | Fully Meets |
| Costello, Frank | M | White | Fully Meets |
| Crimmings, Jean | F | White | Fully Meets |
| Cross, Noah | M | White | Fully Meets |
| Daneault, Lynn | F | White | Fully Meets |
| Daniele, Ann | F | White | Fully Meets |
| Darson, Jene'ya | F | White | Fully Meets |
| Davis, Daniel | M | Two or more races | Fully Meets |
| Dee, Randy | M | White | Fully Meets |
| DeGweck, James | M | White | Fully Meets |
| Del Bosque, Keyla | F | Black or African American | Fully Meets |
| Delarge, Alex | M | Two or more races | PIP |
| Demita, Carla | F | Black or African American | Fully Meets |
| Desimone, Carl | M | White | PIP |
| DeVito, Tommy | M | White | Fully Meets |
| Dickinson, Geoff | M | White | Fully Meets |
| Dietrich, Jenna | F | White | PIP |
| DiNocco, Lily | F | Black or African American | Needs Improvement |
| Dobrin, Denisa S | F | White | Fully Meets |
| Dolan, Linda | F | White | Fully Meets |
| Dougall, Eric | M | Black or African American | Exceeds |
| Driver, Elle | F | White | Exceeds |
| Dunn, Amy | F | White | Fully Meets |
| Dunne, Amy | F | White | Fully Meets |
| Eaton, Marianne | F | White | Fully Meets |
| Engdahl, Jean | M | White | Fully Meets |
| England, Rex | M | White | Fully Meets |
| Erilus, Angela | F | White | PIP |
| Estremera, Miguel | M | White | Needs Improvement |
| Evensen, April | F | White | Needs Improvement |
| Exantus, Susan | F | Black or African American | Needs Improvement |
| Faller, Megan | F | Black or African American | Fully Meets |
| Fancett, Nicole | F | Black or African American | Fully Meets |
| Ferguson, Susan | F | White | Fully Meets |
| Fernandes, Nilson | M | White | PIP |
| Fett, Boba | M | White | PIP |
| Fidelia, Libby | F | White | Fully Meets |
| Fitzpatrick, Michael J | M | White | Fully Meets |
| Foreman, Tanya | F | White | Fully Meets |
| Forrest, Alex | M | White | PIP |
| Foss, Jason | M | Black or African American | Exceeds |
| Foster-Baker, Amy | F | White | Fully Meets |
| Fraval, Maruk | M | Black or African American | Fully Meets |
| Galia, Lisa | F | White | Fully Meets |
| Garcia, Raul | M | White | Fully Meets |
| Gaul, Barbara | F | Black or African American | Fully Meets |
| Gentry, Mildred | F | Black or African American | Fully Meets |
| Gerke, Melisa | F | Black or African American | Fully Meets |
| Gill, Whitney | F | Black or African American | Fully Meets |
| Gilles, Alex | M | Black or African American | Needs Improvement |
| Girifalco, Evelyn | F | Two or more races | Exceeds |
| Givens, Myriam | F | White | Fully Meets |
| Goble, Taisha | F | White | Fully Meets |
| Goeth, Amon | M | White | Fully Meets |
| Gold, Shenice | F | White | Fully Meets |
| Gonzalez, Cayo | M | Black or African American | Exceeds |
| Gonzalez, Juan | M | Black or African American | PIP |
| Gonzalez, Maria | F | White | Fully Meets |
| Good, Susan | F | White | Fully Meets |
| Gordon, David | M | White | Fully Meets |
| Gosciminski, Phylicia | F | American Indian or Alaska Native | Fully Meets |
| Goyal, Roxana | F | Asian | Fully Meets |
| Gray, Elijiah | M | White | Fully Meets |
| Gross, Paula | F | White | Fully Meets |
| Gruber, Hans | M | Black or African American | Fully Meets |
| Guilianno, Mike | M | Two or more races | Fully Meets |
| Handschiegl, Joanne | F | White | Fully Meets |
| Hankard, Earnest | M | White | Fully Meets |
| Harrington, Christie | F | White | Fully Meets |
| Harrison, Kara | F | White | Exceeds |
| Heitzman, Anthony | M | White | Fully Meets |
| Hendrickson, Trina | F | White | Fully Meets |
| Hitchcock, Alfred | M | American Indian or Alaska Native | Fully Meets |
| Homberger, Adrienne J | F | White | Fully Meets |
| Horton, Jayne | F | White | Fully Meets |
| Houlihan, Debra | F | White | Fully Meets |
| Howard, Estelle | F | Black or African American | Fully Meets |
| Hudson, Jane | F | White | Fully Meets |
| Hunts, Julissa | F | White | Fully Meets |
| Hutter, Rosalie | F | White | Fully Meets |
| Huynh, Ming | F | White | Fully Meets |
| Immediato, Walter | M | Asian | Needs Improvement |
| Ivey, Rose | F | White | Fully Meets |
| Jackson, Maryellen | F | Black or African American | Fully Meets |
| Jacobi, Hannah | F | White | Fully Meets |
| Jeannite, Tayana | F | American Indian or Alaska Native | Exceeds |
| Jhaveri, Sneha | F | White | Fully Meets |
| Johnson, George | M | White | Exceeds |
| Johnson, Noelle | F | Asian | Fully Meets |
| Johnston, Yen | F | White | Exceeds |
| Jung, Judy | F | Black or African American | Fully Meets |
| Kampew, Donysha | F | White | Fully Meets |
| Keatts, Kramer | M | White | Fully Meets |
| Khemmich, Bartholemew | M | White | Fully Meets |
| King, Janet | F | White | Fully Meets |
| Kinsella, Kathleen | F | White | Fully Meets |
| Kirill, Alexandra | F | White | Fully Meets |
| Knapp, Bradley J | M | Black or African American | Fully Meets |
| Kretschmer, John | M | Asian | Fully Meets |
| Kreuger, Freddy | M | Black or African American | Fully Meets |
| Lajiri, Jyoti | M | White | Fully Meets |
| Landa, Hans | M | White | Fully Meets |
| Langford, Lindsey | F | Asian | Fully Meets |
| Langton, Enrico | M | White | Fully Meets |
| LaRotonda, William | M | Black or African American | Fully Meets |
| Latif, Mohammed | M | White | Fully Meets |
| Le, Binh | F | Asian | Fully Meets |
| Leach, Dallas | F | Asian | Fully Meets |
| LeBlanc, Brandon R | M | White | Fully Meets |
| Lecter, Hannibal | M | Asian | Fully Meets |
| Leruth, Giovanni | M | Black or African American | Fully Meets |
| Liebig, Ketsia | F | White | Exceeds |
| Linares, Marilyn | F | White | Fully Meets |
| Linden, Mathew | M | White | Fully Meets |
| Lindsay, Leonara | F | Black or African American | Exceeds |
| Lundy, Susan | F | White | Fully Meets |
| Lunquist, Lisa | F | Black or African American | Exceeds |
| Lydon, Allison | F | Black or African American | Fully Meets |
| Lynch, Lindsay | F | Black or African American | Exceeds |
| MacLennan, Samuel | M | White | Fully Meets |
| Mahoney, Lauren | F | White | Fully Meets |
| Manchester, Robyn | F | White | Fully Meets |
| Mancuso, Karen | F | Two or more races | Fully Meets |
| Mangal, Debbie | F | White | Fully Meets |
| Martin, Sandra | F | Asian | Fully Meets |
| Maurice, Shana | F | White | Fully Meets |
| Carthy, B'rigit | F | Black or African American | Fully Meets |
| Mckenna, Sandy | F | Black or African American | Fully Meets |
| McKinzie, Jac | M | Two or more races | Fully Meets |
| Meads, Elizabeth | F | Black or African American | Fully Meets |
| Medeiros, Jennifer | F | White | Fully Meets |
| Miller, Brannon | M | Hispanic | Fully Meets |
| Miller, Ned | M | White | PIP |
| Monkfish, Erasumus | M | White | Fully Meets |
| Monroe, Peter | M | Black or African American | Needs Improvement |
| Monterro, Luisa | F | Black or African American | Exceeds |
| Moran, Patrick | M | Black or African American | Fully Meets |
| Morway, Tanya | F | White | Fully Meets |
| Motlagh, Dawn | F | White | Fully Meets |
| Moumanil, Maliki | M | Black or African American | Fully Meets |
| Myers, Michael | M | Asian | Fully Meets |
| Navathe, Kurt | M | Asian | Fully Meets |
| Ndzi, Colombui | M | Black or African American | Fully Meets |
| Ndzi, Horia | M | White | Fully Meets |
| Newman, Richard | M | White | Fully Meets |
| Ngodup, Shari | F | Black or African American | Exceeds |
| Nguyen, Dheepa | F | Two or more races | Fully Meets |
| Nguyen, Lei-Ming | F | White | Fully Meets |
| Nowlan, Kristie | F | White | Fully Meets |
| O'hare, Lynn | F | Two or more races | PIP |
| Oliver, Brooke | F | Asian | Fully Meets |
| Onque, Jasmine | F | White | Fully Meets |
| Osturnka, Adeel | M | White | Exceeds |
| Owad, Clinton | M | Black or African American | Needs Improvement |
| Ozark, Travis | M | White | Fully Meets |
| Panjwani, Nina | F | White | Fully Meets |
| Patronick, Lucas | M | Black or African American | Exceeds |
| Pearson, Randall | M | White | Fully Meets |
| Smith, Martin | M | Black or African American | Needs Improvement |
| Pelletier, Ermine | F | Asian | Needs Improvement |
| Perry, Shakira | F | White | Fully Meets |
| Peters, Lauren | F | White | Exceeds |
| Peterson, Ebonee | F | White | Fully Meets |
| Petingill, Shana | F | Asian | Fully Meets |
| Petrowsky, Thelma | F | Black or African American | Exceeds |
| Pham, Hong | M | Asian | Fully Meets |
| Pitt, Brad | M | White | Fully Meets |
| Potts, Xana | F | Black or African American | Fully Meets |
| Power, Morissa | F | Asian | Fully Meets |
| Punjabhi, Louis | M | White | Fully Meets |
| Purinton, Janine | F | White | Fully Meets |
| Quinn, Sean | M | Black or African American | Fully Meets |
| Rachael, Maggie | F | Black or African American | Fully Meets |
| Rarrick, Quinn | M | White | Fully Meets |
| Ren, Kylo | M | White | Fully Meets |
| Rhoads, Thomas | M | White | Fully Meets |
| Rivera, Haley | F | Asian | Exceeds |
| Roberson, May | F | Black or African American | Fully Meets |
| Robertson, Peter | M | White | Fully Meets |
| Robinson, Alain | M | Black or African American | Fully Meets |
| Robinson, Cherly | F | White | Needs Improvement |
| Robinson, Elias | M | White | Exceeds |
| Roby, Lori | F | White | Fully Meets |
| Roehrich, Bianca | F | White | Fully Meets |
| Roper, Katie | F | Black or African American | Fully Meets |
| Rose, Ashley | F | White | Fully Meets |
| Rossetti, Bruno | M | White | Fully Meets |
| Roup,Simon | M | White | Fully Meets |
| Ruiz, Ricardo | M | Black or African American | Fully Meets |
| Saada, Adell | F | White | Fully Meets |
| Saar-Beckles, Melinda | F | Black or African American | Needs Improvement |
| Sadki, Nore | M | White | Fully Meets |
| Sahoo, Adil | M | White | Fully Meets |
| Salter, Jason | M | Black or African American | Fully Meets |
| Sander, Kamrin | F | Black or African American | Fully Meets |
| Sewkumar, Nori | F | Asian | Fully Meets |
| Shepard, Anita | F | White | Fully Meets |
| Shields, Seffi | F | White | Fully Meets |
| Simard, Kramer | M | White | Fully Meets |
| Singh, Nan | F | White | Fully Meets |
| Sloan, Constance | F | White | Fully Meets |
| Smith, Joe | M | White | Exceeds |
| Smith, John | M | Black or African American | Needs Improvement |
| Smith, Leigh Ann | F | Black or African American | Fully Meets |
| Smith, Sade | F | White | Fully Meets |
| Soto, Julia | F | Black or African American | Fully Meets |
| Soze, Keyser | M | Asian | Fully Meets |
| Sparks, Taylor | F | White | PIP |
| Spirea, Kelley | F | White | Fully Meets |
| Squatrito, Kristen | F | White | Exceeds |
| Stanford,Barbara M | F | Two or more races | Fully Meets |
| Stansfield, Norman | M | White | PIP |
| Steans, Tyrone | M | White | Fully Meets |
| Stoica, Rick | M | Asian | Fully Meets |
| Strong, Caitrin | F | Black or African American | Fully Meets |
| Sullivan, Kissy | F | Black or African American | Fully Meets |
| Sullivan, Timothy | M | White | Fully Meets |
| Sutwell, Barbara | F | Asian | Fully Meets |
| Szabo, Andrew | M | White | Exceeds |
| Tannen, Biff | M | White | Fully Meets |
| Tavares, Desiree | F | Black or African American | Fully Meets |
| Tejeda, Lenora | F | White | Fully Meets |
| Terry, Sharlene | F | Black or African American | Fully Meets |
| Theamstern, Sophia | F | White | Fully Meets |
| Thibaud, Kenneth | M | White | Fully Meets |
| Tippett, Jeanette | F | Black or African American | Fully Meets |
| Torrence, Jack | M | White | Exceeds |
| Trang, Mei | F | White | Needs Improvement |
| Tredinnick, Neville | M | White | Fully Meets |
| True, Edward | M | Black or African American | Fully Meets |
| Trzeciak, Cybil | F | White | Fully Meets |
| Turpin, Jumil | M | White | Fully Meets |
| Valentin,Jackie | F | Two or more races | Fully Meets |
| Veera, Abdellah | M | White | Exceeds |
| Vega, Vincent | M | White | Fully Meets |
| Villanueva, Noah | M | Asian | Fully Meets |
| Voldemort, Lord | M | Black or African American | Fully Meets |
| Volk, Colleen | F | White | Exceeds |
| Von Massenbach, Anna | F | White | Fully Meets |
| Walker, Roger | M | Black or African American | Fully Meets |
| Wallace, Courtney E | F | Black or African American | Fully Meets |
| Wallace, Theresa | F | White | Needs Improvement |
| Wang, Charlie | M | Asian | Fully Meets |
| Warfield, Sarah | F | Asian | Fully Meets |
| Whittier, Scott | M | White | Fully Meets |
| Wilber, Barry | M | White | Fully Meets |
| Wilkes, Annie | F | White | Fully Meets |
| Williams, Jacquelyn | F | Black or African American | Fully Meets |
| Winthrop, Jordan | M | White | Exceeds |
| Wolk, Hang T | F | White | Fully Meets |
| Woodson, Jason | M | White | Fully Meets |
| Ybarra, Catherine | F | Asian | PIP |
| Zamora, Jennifer | F | White | Exceeds |
| Zhou, Julia | F | White | Fully Meets |
| Zima, Colleen | F | Asian | Fully Meets |
+---------------------------+------+----------------------------------+-------------------+
311 rows in set (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment