package test;

import java.sql.*;
import javax.swing.JOptionPane;


public class Test

      {
          public static String zzz = "";
            public static void main (String [] args)
            throws SQLException, ClassNotFoundException
                {
                    String path = "jdbc:mysql://localhost/employees",
                           user = "root",
                           password = "Sparsky";
                    try
                    {
                         Class.forName ("com.mysql.jdbc.Driver");
                    }
                    catch (ClassNotFoundException cnfe)
                    {
                      System.out.println (cnfe.getMessage ());
                	 }

                    Class.forName ("com.mysql.jdbc.Driver");
                    Connection cn =
                        DriverManager.getConnection (path, user, password);
                    Statement st = cn.createStatement ();
                    String sqlCommand = "";
                    sqlCommand =
                    "CREATE TABLE IF NOT EXISTS allEmployees(IDNumber int(9) PRIMARY KEY NOT NULL, firstName VARCHAR(10), middleInitial VARCHAR(4), lastName VARCHAR(15), dateOfBirth DATE,dateOfEmployment DATE, addressLine1 VARCHAR(30), addressLine2 VARCHAR(18), city VARCHAR(18), province VARCHAR(18), postalCode VARCHAR(7), fixedPhoneNumber VARCHAR(12), mobilePhoneNumber VARCHAR(12), salary DECIMAL(8,2));";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "LOAD DATA LOCAL INFILE '/employee.txt' INTO TABLE allEmployees LINES TERMINATED BY '\r\n';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "SELECT firstName, lastName, salary FROM allEmployees WHERE salary=(SELECT max(salary) FROM AllEmployees);";
                    st.executeQuery (sqlCommand);
                    ResultSet rs = st.executeQuery (sqlCommand);
                    while (rs.next ())
                         {
                            String sal = rs.getString("salary");
                            System.out.println(sal);
                         }
                    sqlCommand = "SELECT firstName, lastName, salary FROM allEmployees WHERE salary=(SELECT min(salary) FROM AllEmployees); ";
                    st.executeQuery (sqlCommand);
                    rs = st.executeQuery (sqlCommand);
                    while (rs.next ())
                         {
                            String sal = rs.getString("salary");
                            System.out.println(sal);
                         }
                    sqlCommand = "SELECT SUM(salary) from AllEmployees;";
                    st.executeQuery (sqlCommand);
                    rs = st.executeQuery (sqlCommand);
                    while (rs.next ())
                         {
                            String sal = rs.getString("SUM(salary)");
                            System.out.println(sal);
                         }
                    sqlCommand = "SELECT AVG(salary) from AllEmployees;";
                    st.executeQuery (sqlCommand);
                    rs = st.executeQuery (sqlCommand);
                    while (rs.next ())
                         {
                            String sal = rs.getString("AVG(salary)");
                            System.out.println(sal);
                         }
                    sqlCommand = "SELECT firstName, lastName, dateOfEmployment FROM allEmployees WHERE dateOfEmployment=(SELECT MAX(dateOfEmployment) FROM AllEmployees);";
                    st.executeQuery (sqlCommand);
                    rs = st.executeQuery (sqlCommand);
                    while (rs.next ())
                         {
                            String firstName = rs.getString("firstName");
                            String lastName = rs.getString("lastName");
                            String dateOfEmployment = rs.getString("dateOfEmployment");
                            System.out.println(firstName + "\t" +  lastName + "\t" + dateOfEmployment);
                         }
                    sqlCommand = "SELECT firstName, lastName, dateOfEmployment FROM allEmployees WHERE dateOfEmployment=(SELECT MIN(dateOfEmployment) FROM AllEmployees);";
                    st.executeQuery (sqlCommand);
                    rs = st.executeQuery (sqlCommand);
                    while (rs.next ())
                         {
                            String firstName = rs.getString("firstName");
                            String lastName = rs.getString("lastName");
                            String dateOfEmployment = rs.getString("dateOfEmployment");
                            System.out.println(firstName + "\t" +  lastName + "\t" + dateOfEmployment);
                         }
                    sqlCommand = "SELECT firstName, lastName, dateOfBirth FROM allEmployees WHERE dateOfBirth=(SELECT MAX(dateOfBirth) FROM AllEmployees);";
                    st.executeQuery (sqlCommand);
                    rs = st.executeQuery (sqlCommand);
                    while (rs.next ())
                         {
                            String firstName = rs.getString("firstName");
                            String lastName = rs.getString("lastName");
                            String dateOfBirth = rs.getString("dateOfBirth");
                            System.out.println(firstName + "\t" +  lastName + "\t" + dateOfBirth);
                         }
                    sqlCommand = "SELECT firstName, lastName, dateOfBirth FROM allEmployees WHERE dateOfBirth=(SELECT MIN(dateOfBirth) FROM AllEmployees);";
                    st.executeQuery (sqlCommand);
                    rs = st.executeQuery (sqlCommand);
                    while (rs.next ())
                         {
                            String firstName = rs.getString("firstName");
                            String lastName = rs.getString("lastName");
                            String dateOfBirth = rs.getString("dateOfBirth");
                            System.out.println(firstName + "\t" +  lastName + "\t" + dateOfBirth);
                         }
                    sqlCommand = "SELECT AVG(DATEDIFF(CURDATE(), dateOfBirth) / 365.24) from allEmployees;";
                    st.executeQuery (sqlCommand);
                    rs = st.executeQuery (sqlCommand);
                    while (rs.next ())
                         {
                            String dateOfBirth = rs.getString("AVG(DATEDIFF(CURDATE(), dateOfBirth) / 365.24)");
                            System.out.println(dateOfBirth);
                         }
                    sqlCommand = "ALTER TABLE allEmployees DROP fixedPhoneNumber, DROP mobilePhoneNumber;";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET middleInitial = 'A' WHERE firstName = 'Jane';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET middleInitial = 'B' WHERE year(dateOfBirth) = '1954';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET middleInitial = 'C' WHERE addressLine1 LIKE '%3547%';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET middleInitial = 'D' WHERE city = 'Milton';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET middleInitial = 'E' WHERE salary < 101000;";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET middleInitial = 'F' WHERE day(dateOfBirth) = '8' AND month(dateOfBirth) = '11';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "ALTER TABLE allEmployees ADD gender VARCHAR(2) AFTER lastName;";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "ALTER TABLE allEmployees ADD maritalStatus VARCHAR(2) FIRST;";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "ALTER TABLE allEmployees ADD numChildren VARCHAR(2) AFTER maritalStatus;";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET gender = 'M';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'JANE';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'BETH';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'MARY';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'JILL';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'KATE';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "DELETE FROM allEmployees WHERE IDNumber = '381654729';";
                    st.executeUpdate (sqlCommand);
                    sqlCommand = "SELECT * FROM allEmployees;";
                    rs = st.executeQuery (sqlCommand);
                    while (rs.next ())
                         {
                                int primKey = rs.getInt ("IDNumber");
				String fname = rs.getString ("firstName");
				String mname = rs.getString ("middleInitial");
                                String lname = rs.getString ("lastName");
                                String dateOfBirth = rs.getString ("dateOfBirth");
                                String dateOfEmployment = rs.getString ("dateOfEmployment");
                                String addressLine1 = rs.getString ("addressLine1");
                                String addressLine2 = rs.getString ("addressLine2");
                                String city = rs.getString ("city");
                                String province = rs.getString ("province");
                                String postalCode = rs.getString ("postalCode");
                                String salary = rs.getString ("salary");
                                System.out.println (primKey + "\t" + fname + "\t" + mname + "\t" + lname + "\t" + dateOfBirth + "\t" + dateOfEmployment + "\t" + addressLine1 + "\t" + addressLine2 + "\t" + city + "\t" + province + "\t" + postalCode + "\t" + salary);
						 }
                    sqlCommand = "DROP TABLE allEmployees;";
                    st.executeUpdate (sqlCommand);
                }
      }