Please post your solutions to the Dec 5 In-Class Exercise tot this thread.

Best,

Chris

ALTER TABLE my_table ADD COLUMN c CHAR(1) DEFAULT ‘z’ AFTER b;

INSERT INTO my_table(a,b) VALUES (1,”J”),(2,”D”),(3,”B”),(4,”C”);

Change corresponding line to rs = stmt.executeQuery(“SELECT * FROM my_table WHERE a>5”);

CREATE TABLE my_table ( a INT, b VARCHAR(20), c CHAR(1) );

INSERT INTO my_table VALUES (3, "hi", 'H'), (4, "bye", 'B'), (6, "yo", 'Y'), (7, "bro", 'B');

import java.sql.*; class JdbcTest { public static void main(String[] args) { Connection conn = null; ResultSet rs = null; Statement stmt = null; try { conn = DriverManager.getConnection( "jdbc:mysql://localhost/test?" + "user=root&password=root"); stmt = conn.createStatement(); rs = stmt.executeQuery("SELECT * FROM my_table WHERE a > 5"); while (rs.next()) { int a = rs.getInt("a"); String b = rs.getString("b"); String c = rs.getString("c"); System.out.println("a:" + a + "\tb:" + b+ "\tc:" + c); } } catch (SQLException ex) { // handle any errors System.out.println("SQLException: " + ex.getMessage()); System.out.println("SQLState: " + ex.getSQLState()); System.out.println("VendorError: " + ex.getErrorCode()); } finally { if (rs != null) { try { rs.close(); } catch (SQLException sqlEx) { } // ignore rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException sqlEx) { } // ignore stmt = null; } } } }

Output
a:6 b:yo c:Y a:7 b:bro c:B

CREATE TABLE my_table ( a INT, b VARCHAR(20), c CHAR(1) );

INSERT INTO my_table VALUES (6, "four", 'd'), (4, "three", 'c'), (3, "two", 'b'), (2, "one", 'a');

rs = stmt.executeQuery("SELECT * FROM my_table WHERE a > 5"); while (rs.next()) { int a = rs.getInt("a"); String b = rs.getString("b"); String c = rs.getString("c"); System.out.println("a:" + a + "\tb:" + b + "\tc:" + c);
}

output:

a:6 b:four c:d

Hongbin Zheng

CREATE TABLE my_table (

  a INT,

  b VARCHAR(20),

  c CHAR(1)

);

INSERT INTO my_table VALUES

(1, "hello", 'H'), (2, "Amit", 'A'), (3, "Pollet", 'P'), (6, "CS", 'C');

rs = stmt.executeQuery("SELECT * FROM my_table WHERE a > 5");

Output: a:6 b:CS c:C

CREATE TABLE my_table ( a INT, b VARCHAR(20), c CHAR(1) );

INSERT INTO my_table VALUES (1,”hi”,‘a’), (2,”bye”,’b’), (3,”yo”,’c’), (4,”hello,’d’), (6,"test",'e');

rs = stmt.executeQuery("SELECT * FROM my_table WHERE a > 5");

Output: a:6 b:test c:e

JAVA addition: while (rs.next()) { int a = rs.getInt("a"); String b = rs.getString("b"); String c = rs.getString("c"); if (a > 5) System.out.println( "a: " + a + "\n" + "b: " + b + "\n" + "c: " + c + "\n" ); }

MYSQL:

ALTER TABLE my_table ADD COLUMN c CHAR(1);

MariaDB [ICE1205]> UPDATE my_table SET c='a' WHERE a=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [ICE1205]> UPDATE my_table SET c='z' WHERE a=2; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [ICE1205]> UPDATE my_table SET c='p' WHERE a=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

MariaDB [ICE1205]> INSERT INTO my_table VALUES (123, "no", 'c'); Query OK, 1 row affected (0.00 sec)

MariaDB [ICE1205]> INSERT INTO my_table VALUES (123, "", 'r'); Query OK, 1 row affected (0.00 sec)

MariaDB [ICE1205]> DELETE FROM my_table WHERE c='r'; Query OK, 1 row affected (0.01 sec)

MariaDB [ICE1205]> INSERT INTO my_table VALUES (55, "", 'r'); Query OK, 1 row affected (0.01 sec)

MariaDB [ICE1205]> INSERT INTO my_table VALUES (4, "nice", 'e'); Query OK, 1 row affected (0.00 sec)

MariaDB [ICE1205]> INSERT INTO my_table VALUES (10, "cloud", 'l'); Query OK, 1 row affected (0.00 sec)

sherwyn@tapeworm ~/cs157a/ice1205 java -cp JDBC JdbcTest

a: 123

b: no

c: c

a: 55

b:

c: r

a: 10

b: cloud

c: l

CREATE TABLE my_table ( a INT, b VARCHAR(20), c CHAR(1) ); INSERT INTO my_table VALUES (6, "ok", 'day'), (4, "ko", 'cre'), (3, "tete", 'bre'), (2, "gaga", 'aac); rs = stmt.executeQuery("SELECT * FROM my_table WHERE a > 5"); while (rs.next()) { int a = rs.getInt("a"); String b = rs.getString("b"); String c = rs.getString("c"); System.out.println("a:" + a + "\tb:" + b + "\tc:" + c);
} output: a:6 b:ok c:day

CREATE TABLE my_table (a INT, b VARCHAR(20), c CHAR(1)); INSERT INTO my_table VALUES (6, “hello”, “A”), (8, “wassup”, “B”), (4, “bye”, “C”), (3, “lol”, “C”); ... rs = stmt.executeQuery("SELECT * FROM my_table WHERE a > 5"); ...

Output: a:6 b:hello a:8 b:wassup

CREATE TABLE my_table (a INT, b VARCHAR(20), c CHAR(1));

INSERT INTO my_table VALUES (2, "aaaa", "b"), (6, "abaa", "c"), (8, "aaba", "d"), (10, "aaab", "e");

Output: a:6 b:abaa c:c a:8 b:aaba c:d a:10 b:aaab c:e