· 8 years ago · Aug 15, 2018, 12:26 AM
1package test;
2
3import java.sql.*;
4import javax.swing.JOptionPane;
5
6
7public class Test
8
9 {
10 public static String zzz = "";
11 public static void main (String [] args)
12 throws SQLException, ClassNotFoundException
13 {
14 String path = "jdbc:mysql://localhost/employees",
15 user = "root",
16 password = "Sparsky";
17 try
18 {
19 Class.forName ("com.mysql.jdbc.Driver");
20 }
21 catch (ClassNotFoundException cnfe)
22 {
23 System.out.println (cnfe.getMessage ());
24 }
25
26 Class.forName ("com.mysql.jdbc.Driver");
27 Connection cn =
28 DriverManager.getConnection (path, user, password);
29 Statement st = cn.createStatement ();
30 String sqlCommand = "";
31 sqlCommand =
32 "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));";
33 st.executeUpdate (sqlCommand);
34 sqlCommand = "LOAD DATA LOCAL INFILE '/employee.txt' INTO TABLE allEmployees LINES TERMINATED BY '\r\n';";
35 st.executeUpdate (sqlCommand);
36 sqlCommand = "SELECT firstName, lastName, salary FROM allEmployees WHERE salary=(SELECT max(salary) FROM AllEmployees);";
37 st.executeQuery (sqlCommand);
38 ResultSet rs = st.executeQuery (sqlCommand);
39 while (rs.next ())
40 {
41 String sal = rs.getString("salary");
42 System.out.println(sal);
43 }
44 sqlCommand = "SELECT firstName, lastName, salary FROM allEmployees WHERE salary=(SELECT min(salary) FROM AllEmployees); ";
45 st.executeQuery (sqlCommand);
46 rs = st.executeQuery (sqlCommand);
47 while (rs.next ())
48 {
49 String sal = rs.getString("salary");
50 System.out.println(sal);
51 }
52 sqlCommand = "SELECT SUM(salary) from AllEmployees;";
53 st.executeQuery (sqlCommand);
54 rs = st.executeQuery (sqlCommand);
55 while (rs.next ())
56 {
57 String sal = rs.getString("SUM(salary)");
58 System.out.println(sal);
59 }
60 sqlCommand = "SELECT AVG(salary) from AllEmployees;";
61 st.executeQuery (sqlCommand);
62 rs = st.executeQuery (sqlCommand);
63 while (rs.next ())
64 {
65 String sal = rs.getString("AVG(salary)");
66 System.out.println(sal);
67 }
68 sqlCommand = "SELECT firstName, lastName, dateOfEmployment FROM allEmployees WHERE dateOfEmployment=(SELECT MAX(dateOfEmployment) FROM AllEmployees);";
69 st.executeQuery (sqlCommand);
70 rs = st.executeQuery (sqlCommand);
71 while (rs.next ())
72 {
73 String firstName = rs.getString("firstName");
74 String lastName = rs.getString("lastName");
75 String dateOfEmployment = rs.getString("dateOfEmployment");
76 System.out.println(firstName + "\t" + lastName + "\t" + dateOfEmployment);
77 }
78 sqlCommand = "SELECT firstName, lastName, dateOfEmployment FROM allEmployees WHERE dateOfEmployment=(SELECT MIN(dateOfEmployment) FROM AllEmployees);";
79 st.executeQuery (sqlCommand);
80 rs = st.executeQuery (sqlCommand);
81 while (rs.next ())
82 {
83 String firstName = rs.getString("firstName");
84 String lastName = rs.getString("lastName");
85 String dateOfEmployment = rs.getString("dateOfEmployment");
86 System.out.println(firstName + "\t" + lastName + "\t" + dateOfEmployment);
87 }
88 sqlCommand = "SELECT firstName, lastName, dateOfBirth FROM allEmployees WHERE dateOfBirth=(SELECT MAX(dateOfBirth) FROM AllEmployees);";
89 st.executeQuery (sqlCommand);
90 rs = st.executeQuery (sqlCommand);
91 while (rs.next ())
92 {
93 String firstName = rs.getString("firstName");
94 String lastName = rs.getString("lastName");
95 String dateOfBirth = rs.getString("dateOfBirth");
96 System.out.println(firstName + "\t" + lastName + "\t" + dateOfBirth);
97 }
98 sqlCommand = "SELECT firstName, lastName, dateOfBirth FROM allEmployees WHERE dateOfBirth=(SELECT MIN(dateOfBirth) FROM AllEmployees);";
99 st.executeQuery (sqlCommand);
100 rs = st.executeQuery (sqlCommand);
101 while (rs.next ())
102 {
103 String firstName = rs.getString("firstName");
104 String lastName = rs.getString("lastName");
105 String dateOfBirth = rs.getString("dateOfBirth");
106 System.out.println(firstName + "\t" + lastName + "\t" + dateOfBirth);
107 }
108 sqlCommand = "SELECT AVG(DATEDIFF(CURDATE(), dateOfBirth) / 365.24) from allEmployees;";
109 st.executeQuery (sqlCommand);
110 rs = st.executeQuery (sqlCommand);
111 while (rs.next ())
112 {
113 String dateOfBirth = rs.getString("AVG(DATEDIFF(CURDATE(), dateOfBirth) / 365.24)");
114 System.out.println(dateOfBirth);
115 }
116 sqlCommand = "ALTER TABLE allEmployees DROP fixedPhoneNumber, DROP mobilePhoneNumber;";
117 st.executeUpdate (sqlCommand);
118 sqlCommand = "UPDATE allEmployees SET middleInitial = 'A' WHERE firstName = 'Jane';";
119 st.executeUpdate (sqlCommand);
120 sqlCommand = "UPDATE allEmployees SET middleInitial = 'B' WHERE year(dateOfBirth) = '1954';";
121 st.executeUpdate (sqlCommand);
122 sqlCommand = "UPDATE allEmployees SET middleInitial = 'C' WHERE addressLine1 LIKE '%3547%';";
123 st.executeUpdate (sqlCommand);
124 sqlCommand = "UPDATE allEmployees SET middleInitial = 'D' WHERE city = 'Milton';";
125 st.executeUpdate (sqlCommand);
126 sqlCommand = "UPDATE allEmployees SET middleInitial = 'E' WHERE salary < 101000;";
127 st.executeUpdate (sqlCommand);
128 sqlCommand = "UPDATE allEmployees SET middleInitial = 'F' WHERE day(dateOfBirth) = '8' AND month(dateOfBirth) = '11';";
129 st.executeUpdate (sqlCommand);
130 sqlCommand = "ALTER TABLE allEmployees ADD gender VARCHAR(2) AFTER lastName;";
131 st.executeUpdate (sqlCommand);
132 sqlCommand = "ALTER TABLE allEmployees ADD maritalStatus VARCHAR(2) FIRST;";
133 st.executeUpdate (sqlCommand);
134 sqlCommand = "ALTER TABLE allEmployees ADD numChildren VARCHAR(2) AFTER maritalStatus;";
135 st.executeUpdate (sqlCommand);
136 sqlCommand = "UPDATE allEmployees SET gender = 'M';";
137 st.executeUpdate (sqlCommand);
138 sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'JANE';";
139 st.executeUpdate (sqlCommand);
140 sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'BETH';";
141 st.executeUpdate (sqlCommand);
142 sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'MARY';";
143 st.executeUpdate (sqlCommand);
144 sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'JILL';";
145 st.executeUpdate (sqlCommand);
146 sqlCommand = "UPDATE allEmployees SET gender = 'F' WHERE firstName = 'KATE';";
147 st.executeUpdate (sqlCommand);
148 sqlCommand = "DELETE FROM allEmployees WHERE IDNumber = '381654729';";
149 st.executeUpdate (sqlCommand);
150 sqlCommand = "SELECT * FROM allEmployees;";
151 rs = st.executeQuery (sqlCommand);
152 while (rs.next ())
153 {
154 int primKey = rs.getInt ("IDNumber");
155 String fname = rs.getString ("firstName");
156 String mname = rs.getString ("middleInitial");
157 String lname = rs.getString ("lastName");
158 String dateOfBirth = rs.getString ("dateOfBirth");
159 String dateOfEmployment = rs.getString ("dateOfEmployment");
160 String addressLine1 = rs.getString ("addressLine1");
161 String addressLine2 = rs.getString ("addressLine2");
162 String city = rs.getString ("city");
163 String province = rs.getString ("province");
164 String postalCode = rs.getString ("postalCode");
165 String salary = rs.getString ("salary");
166 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);
167 }
168 sqlCommand = "DROP TABLE allEmployees;";
169 st.executeUpdate (sqlCommand);
170 }
171 }