· 7 years ago · Dec 20, 2018, 09:30 PM
1package jdbc_201624476;
2
3import java.sql.*;
4import java.util.Scanner;
5
6public class Product_Query {
7
8 final static String csvPath = "\'D:\\sample_products\\";
9
10 public static void main(String[] args) {
11
12 String query;
13 ResultSet resultSet;
14 Statement stmt;
15
16 Scanner scan = new Scanner(System.in);
17
18 //Driver check
19 try {
20 Class.forName("org.postgresql.Driver");
21 System.out.println("Driver Registered");
22 }
23 catch(ClassNotFoundException e) {
24 System.out.println("You didn't register driver");
25 }
26
27 //Get connection
28 Connection con = null; //initialize variable
29 try {
30 con = DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "2724");
31 System.out.println("Successfully connected.");
32
33 } catch (SQLException e) {
34 System.out.println("Problem with your connection");
35 }
36
37 //Clearing Leftovers
38 try {
39 stmt = con.createStatement();
40 query = "DROP TABLE IF EXISTS Laptop;\n" +
41 "DROP TABLE IF EXISTS PC;\n" +
42 "DROP TABLE IF EXISTS Printer;\n" +
43 "DROP TABLE IF EXISTS Product;\n;";
44 stmt.execute(query);
45 }
46 catch (SQLException e) {
47 System.out.println("Problem with creating schemas.");
48 }
49
50
51 //Creating schemas
52 try {
53 stmt = con.createStatement();
54 query = "CREATE TABLE IF NOT EXISTS Laptop" +
55 "(\n" +
56 " model CHAR(7) PRIMARY KEY NOT NULL,\n" +
57 " speed REAL,\n" +
58 " ram INT,\n" +
59 " hd INT,\n" +
60 " screen REAL,\n" +
61 " price INT\n" +
62 ");\n" +
63 "\n" +
64 "CREATE TABLE IF NOT EXISTS PC\n" +
65 "(\n" +
66 " model CHAR(8) PRIMARY KEY NOT NULL,\n" +
67 " speed REAL,\n" +
68 " ram INT,\n" +
69 " hd INT,\n" +
70 " price INT\n" +
71 ");\n" +
72 "\n" +
73 "CREATE TABLE IF NOT EXISTS Printer\n" +
74 "(\n" +
75 " model CHAR(8) PRIMARY KEY NOT NULL,\n" +
76 " color BOOL,\n" +
77 " printer_type CHAR(7),\n" +
78 " price INT\n" +
79 ");\n" +
80 "CREATE TABLE IF NOT EXISTS Product\n" +
81 "(\n" +
82 " maker CHAR NOT NULL,\n" +
83 " model CHAR(8) NOT NULL PRIMARY KEY,\n" +
84 " product_type CHAR(8)\n" +
85 ");";
86 stmt.execute(query);
87 }
88 catch (SQLException e) {
89 System.out.println("Problem with creating schemas.");
90 }
91
92 //Copy from csv files
93 try {
94 stmt = con.createStatement();
95 query =
96 "COPY Laptop(model,speed,ram,hd,screen,price)\n" +
97 "FROM " + csvPath + "Laptop.csv\' WITH DELIMITER \',\' CSV HEADER;\n" +
98 "COPY PC(model,speed,ram,hd,price)\n" +
99 "FROM " + csvPath + "PC.csv\' WITH DELIMITER \',\' CSV HEADER;\n" +
100 "COPY Printer(model, color, printer_type, price)\n" +
101 "FROM " + csvPath + "Printer.csv\' WITH DELIMITER \',\' CSV HEADER;\n" +
102 "COPY Product(maker, model, product_type)\n" +
103 "FROM " + csvPath + "Product.csv\' WITH DELIMITER \',\' CSV HEADER;";
104 stmt.executeUpdate(query);
105 }
106 catch (SQLException e) {
107 System.out.println("Problem with copying from csv files.");
108 }
109
110 //Query 1
111 try {
112 stmt = con.createStatement();
113 System.out.println("* Query 1");
114 query =
115 "SELECT AVG(Price)\n"+
116 "FROM Product, PC\n"+
117 "WHERE PC.model = Product.model AND maker=\'A\';";
118 resultSet = stmt.executeQuery(query);
119 if(resultSet.next()) System.out.print("Average price of PC made by maker \'A\' is " + resultSet.getFloat(1) + "($), ");
120 query =
121 "SELECT AVG(Price)\n"+
122 "FROM Product, Printer\n"+
123 "WHERE Printer.model = Product.model AND maker=\'A\';";
124 resultSet = stmt.executeQuery(query);
125 if(resultSet.next()) System.out.print("Average price of Printer made by maker \'A\' is " + resultSet.getFloat(1) + "($)\n");
126 }
127 catch (SQLException e) {
128 System.out.println("Problem with Query 1");
129 }
130
131 //Query 2
132 try {
133 stmt = con.createStatement();
134 System.out.println("* Query 2");
135 query =
136 "SELECT maker,AVG(Screen)\n" +
137 "FROM Product, Laptop\n" +
138 "WHERE Laptop.model = Product.model\n" +
139 "GROUP BY maker\n" +
140 "ORDER BY maker ASC;";
141 resultSet = stmt.executeQuery(query);
142 while(resultSet.next()) System.out.println("Average Screen size of PC made by maker " + resultSet.getString("maker") + " is " + resultSet.getDouble("avg") + "(Inch), ");
143 }
144 catch (SQLException e) {
145 System.out.println("Problem with Query 2");
146 }
147
148 //Query 3
149 try {
150 stmt = con.createStatement();
151 System.out.println("* Query 3");
152 System.out.print("Input a number N, which needs to find maker that makes more model than N: ");
153 int N = scan.nextInt();
154 query = "SELECT DISTINCT maker\n" +
155 "FROM Product P1\n" +
156 "WHERE " + String.valueOf(N) + "<= (SELECT COUNT(*)\n" +
157 "FROM Product P2\n" +
158 "WHERE P1.maker = P2.maker);";
159 resultSet = stmt.executeQuery(query);
160 while(resultSet.next()) System.out.println("Maker " + resultSet.getString("maker") + " makes more models than " + N);
161 }
162 catch (SQLException e) {
163 System.out.println("Problem with Query 3");
164 }
165
166
167 //Query 4
168 try {
169 stmt = con.createStatement();
170 System.out.println("* Query 4");
171 query = "SELECT P1.maker, P1.model\n" +
172 "FROM Product P1, PC\r\n" +
173 "WHERE P1.model = PC.model AND PC.price >= ALL ( SELECT price\n" +
174 " FROM Product NATURAL JOIN PC\n" +
175 " WHERE P1.maker = maker\n" +
176 " )\n" +
177 "ORDER BY P1.maker ASC;";
178 resultSet = stmt.executeQuery(query);
179 while(resultSet.next()) System.out.println("The model " + resultSet.getString("model") + " is the most expensive PC of maker " + resultSet.getString("maker"));
180 }
181 catch (SQLException e) {
182 System.out.println("Problem with Query 4");
183 }
184
185 //Query 5
186 try {
187 stmt = con.createStatement();
188 System.out.println("* Query 5");
189 query = "SELECT AVG(hd)\n" +
190 "FROM PC\n" +
191 "WHERE PC.model = ANY(SELECT DISTINCT Product1.model\n"+
192 "FROM product Product1\n"+
193 "WHERE Product1.maker = any(SELECT DISTINCT Product2.maker\n"+
194 "FROM product Product2\n"+
195 "WHERE Product2.model LIKE \'PR%\') )\n";
196 resultSet = stmt.executeQuery(query);
197 while(resultSet.next()) System.out.println("The average size of hard disk from brand that makes printer is " + resultSet.getDouble("avg") + "(GB)");
198 }
199 catch (SQLException e) {
200 System.out.println("Problem with Query 5");
201 }
202
203 //Query 6
204 try {
205 stmt = con.createStatement();
206 System.out.println("* Query 6");
207 query = "SELECT model\n" +
208 "FROM Laptop Laptop1\n" +
209 "WHERE Laptop1.speed >= ALL(SELECT Laptop2.speed\n" +
210 "FROM Laptop Laptop2);";
211 resultSet = stmt.executeQuery(query);
212 while(resultSet.next()) System.out.println("The fastest laptop model is " + resultSet.getString("model"));
213 }
214 catch (SQLException e) {
215 System.out.println("Problem with Query 6");
216 }
217
218 //Query 7
219 try {
220 stmt = con.createStatement();
221 System.out.println("* Query 7");
222 query = "SELECT product.maker, product.product_type\n" +
223 "FROM product\n"+
224 "WHERE product.model = (SELECT Laptop.model FROM Laptop WHERE product.model = Laptop.model AND Laptop.price = (SELECT MAX(laptop1.price) FROM Laptop laptop1) )\n"+
225 "OR product.model = (SELECT PC.model FROM PC WHERE product.model = PC.model AND PC.price = (SELECT MAX(pc1.price) FROM PC pc1) ) \n"+
226 "OR product.model = (SELECT Printer.model FROM Printer WHERE product.model = Printer.model AND Printer.price = (SELECT MAX(Printer1.price) FROM Printer Printer1) );";
227 resultSet = stmt.executeQuery(query);
228 while(resultSet.next()) System.out.println("The most expensive " + resultSet.getString("product_type")+ " is made by " + resultSet.getString("maker"));
229 }
230 catch (SQLException e) {
231 System.out.println("Problem with Query 6");
232 }
233 //Query 8
234 try {
235 stmt = con.createStatement();
236 System.out.println("* Query 8");
237 query = "UPDATE product SET maker=\'A\' WHERE maker=\'B\'";
238 stmt.executeUpdate(query);
239 System.out.println("Update finished : A's M&A for B");
240 }
241 catch (SQLException e) {
242 System.out.println("Problem with Query 8");
243 }
244
245 //Query 9
246 try {
247 stmt = con.createStatement();
248 System.out.println("* Query 9");
249 query =
250 "SELECT AVG(Price)\n"+
251 "FROM Product, PC\n"+
252 "WHERE PC.model = Product.model AND maker=\'A\';";
253 resultSet = stmt.executeQuery(query);
254 if(resultSet.next()) System.out.print("Average price of PC made by maker \'A\' is " + resultSet.getFloat(1) + "($), ");
255 query =
256 "SELECT AVG(Price)\n"+
257 "FROM Product, Printer\n"+
258 "WHERE Printer.model = Product.model AND maker=\'A\';";
259 resultSet = stmt.executeQuery(query);
260 if(resultSet.next()) System.out.print("Average price of Printer made by maker \'A\' is " + resultSet.getFloat(1) + "($)\n");
261 }
262 catch (SQLException e) {
263 System.out.println("Problem with Query 9");
264 }
265
266 //Query 10
267 try {
268 stmt = con.createStatement();
269 System.out.println("* Query 10");
270 System.out.print("Input a number N, which needs to find maker that makes more model than N: ");
271 int N = scan.nextInt();
272 query = "SELECT DISTINCT maker\n" +
273 "FROM Product P1\n" +
274 "WHERE " + String.valueOf(N) + "<= (SELECT COUNT(*)\n" +
275 "FROM Product P2\n" +
276 "WHERE P1.maker = P2.maker);";
277 resultSet = stmt.executeQuery(query);
278 while(resultSet.next()) System.out.println("Maker " + resultSet.getString("maker") + " makes more models than " + N);
279 }
280 catch (SQLException e) {
281 System.out.println("Problem with Query 10");
282 }
283 scan.close();
284
285 }
286
287}