2018-12-05
Dec 5 In-Class Exercise.
Please post your solutions to the Dec 5 In-Class Exercise tot this thread.
Best,
Chris
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”);(Edited: 2018-12-05)
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
(Edited: 2018-12-05) 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
(Edited: 2018-12-05) 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
(Edited: 2018-12-05) 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
(Edited: 2018-12-05) 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
(Edited: 2018-12-06) 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
(Edited: 2018-12-12) 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
2018-12-06
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 (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
2018-12-07
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
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
(c) 2024 Yioop - PHP Search Engine