ASSIGNMENT-2 ADVANCED JAVA
Assignment Questions on JDBC: Use the below table for the programs
Create table employees(eno number, ename varchar2(20), esal
number(10, 2), eaddr varchar2(20));
1. Write a program to create a table
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateTableExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "yourMySQLUsername"; // Use a valid MySQL user
static final String PASS = "dbms123";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement()) {
String sql = "CREATE TABLE employees (" +
"eno INT PRIMARY KEY, " +
"ename VARCHAR(20) NOT NULL, " +
"esal DECIMAL(10, 2), " +
"eaddr VARCHAR(20))";
stmt.executeUpdate(sql);
System.out.println("Table created successfully...");
} catch (SQLException e) {
System.err.println("SQL State: " + e.getSQLState());
System.err.println("Error Code: " + e.getErrorCode());
System.err.println("Message: " + e.getMessage());
e.printStackTrace();
DEPTH OF MCA Page 1
ASSIGNMENT-2 ADVANCED JAVA
Output:
Table created successfully...
DEPTH OF MCA Page 2
ASSIGNMENT-2 ADVANCED JAVA
2. Write a program to delete a table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteTableExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "yourUsername";
static final String PASS = "yourPassword";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement()) {
String sql = "DROP TABLE employees";
stmt.executeUpdate(sql);
System.out.println("Table deleted successfully...");
} catch (SQLException e) {
e.printStackTrace();
} }}
Output:
Table deleted successfully...
Table does exist
SQL State: 42S02
Error Code: 1051
Message: Unknown table 'yourDatabaseName.employees'
DEPTH OF MCA Page 3
ASSIGNMENT-2 ADVANCED JAVA
3. Write a program to insert a record into table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertRecordExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement()) {
String sql = "INSERT INTO employees (eno, ename, esal, eaddr) " +
"VALUES (1, 'Rakshith', 50000.00, '123 Main St')";
stmt.executeUpdate(sql);
System.out.println("Record inserted successfully...");
} catch (SQLException e) {
e.printStackTrace();
}}}
Output:
Record inserted successfully...
DEPTH OF MCA Page 4
ASSIGNMENT-2 ADVANCED JAVA
4. Write a program to insert multiple records into a table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class InsertMultipleRecordsExample {
static final String DB_URL =
"jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
String sql = "INSERT INTO employees (eno, ename, esal, eaddr) VALUES
(?, ?, ?, ?)";
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
conn.setAutoCommit(false); // First record
pstmt.setInt(1, 2);
pstmt.setString(2, "Jane Smith");
pstmt.setBigDecimal(3, java.math.BigDecimal.valueOf(60000.00));
pstmt.setString(4, "456 Oak St");
pstmt.executeUpdate();
pstmt.setInt(1, 3);
pstmt.setString(2, "Mike Johnson");
pstmt.setBigDecimal(3, java.math.BigDecimal.valueOf(55000.00));
DEPTH OF MCA Page 5
ASSIGNMENT-2 ADVANCED JAVA
pstmt.setString(4, "789 Pine St");
pstmt.executeUpdate();
conn.commit();
System.out.println("Multiple records inserted successfully...");
} catch (SQLException e) {
System.err.println("SQL State: " + e.getSQLState());
System.err.println("Error Code: " + e.getErrorCode());
System.err.println("Message: " + e.getMessage());
e.printStackTrace();
Output:
Multiple records inserted successfully...
DEPTH OF MCA Page 6
ASSIGNMENT-2 ADVANCED JAVA
5. Write a program to update a record in the table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class UpdateRecordExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
String sql = "UPDATE employees SET esal = 70000 WHERE eno =1 ";
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setBigDecimal(1, java.math.BigDecimal.valueOf(70000.00));
pstmt.setInt(2, 1);
int rowsAffected = pstmt.executeUpdate();
if (rowsAffected > 0) {
System.out.println("Record updated successfully...");
} else {
System.out.println("No record found with the specified eno.");
} catch (SQLException e) {
System.err.println("SQL State: " + e.getSQLState());
System.err.println("Error Code: " + e.getErrorCode());
System.err.println("Message: " + e.getMessage());
e.printStackTrace();
DEPTH OF MCA Page 7
ASSIGNMENT-2 ADVANCED JAVA
Output:
Record updated successfully...
DEPTH OF MCA Page 8
ASSIGNMENT-2 ADVANCED JAVA
6. Write a program to update multiple records in the table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class UpdateMultipleRecordsExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
String sql = "UPDATE employees SET esal = esal + ? WHERE esal < ?";
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setBigDecimal(1, java.math.BigDecimal.valueOf(5000.00));
pstmt.setBigDecimal(2, java.math.BigDecimal.valueOf(60000.00));
int rowsAffected = pstmt.executeUpdate();
if (rowsAffected > 0) {
System.out.println("Multiple records updated successfully...");
} else {
System.out.println("No records found with esal < 60000.");
} catch (SQLException e) {
System.err.println("SQL State: " + e.getSQLState());
System.err.println("Error Code: " + e.getErrorCode());
System.err.println("Message: " + e.getMessage());
e.printStackTrace();
DEPTH OF MCA Page 9
ASSIGNMENT-2 ADVANCED JAVA
Output:
Multiple records updated successfully...
DEPTH OF MCA Page 10
ASSIGNMENT-2 ADVANCED JAVA
7. Write a program to delete a record from the table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteRecordExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement()) {
String sql = "DELETE FROM employees WHERE eno = 2";
stmt.executeUpdate(sql);
System.out.println("Record deleted successfully...");
} catch (SQLException e) {
e.printStackTrace();
}}}
Output:
Record deleted successfully...
DEPTH OF MCA Page 11
ASSIGNMENT-2 ADVANCED JAVA
8. Write a program to delete multiple records from the
table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteMultipleRecordsExample {
static final String DB_URL ="jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER,
PASS);
Statement stmt = conn.createStatement()) {
String sql = "DELETE FROM employees WHERE esal < 55000";
stmt.executeUpdate(sql);
System.out.println("Multiple records deleted successfully...");
} catch (SQLException e) {
e.printStackTrace();
DEPTH OF MCA Page 12
ASSIGNMENT-2 ADVANCED JAVA
Output:
No Records Deleted
If there are no records that match the deletion criteria (i.e., no employee has a salary less than
55,000), the output will be:
DEPTH OF MCA Page 13
ASSIGNMENT-2 ADVANCED JAVA
9. Write a program to select all the records from the
table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SelectAllRecordsExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "yourUsername";
static final String PASS = "yourPassword";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement()) {
String sql = "SELECT * FROM employees";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
int eno = rs.getInt("eno");
String ename = rs.getString("ename");
double esal = rs.getDouble("esal");
String eaddr = rs.getString("eaddr");
System.out.print("ID: " + eno);
System.out.print(", Name: " + ename);
System.out.print(", Salary: " + esal);
System.out.println(", Address: " + eaddr);
DEPTH OF MCA Page 14
ASSIGNMENT-2 ADVANCED JAVA
rs.close();
} catch (SQLException e) {
e.printStackTrace();
Output:
DEPTH OF MCA Page 15
ASSIGNMENT-2 ADVANCED JAVA
10. Write a program to select all rows from the table
based on sorting order of salaries.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SelectSortedBySalaryExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement()) {
String sql = "SELECT * FROM employees ORDER BY esal";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
int eno = rs.getInt("eno");
String ename = rs.getString("ename");
double esal = rs.getDouble("esal");
String eaddr = rs.getString("eaddr");
System.out.print("ID: " + eno);
System.out.print(", Name: " + ename);
System.out.print(", Salary: " + esal);
System.out.println(", Address: " + eaddr);
DEPTH OF MCA Page 16
ASSIGNMENT-2 ADVANCED JAVA
rs.close();
} catch (SQLException e) {
e.printStackTrace();
Output:
DEPTH OF MCA Page 17
ASSIGNMENT-2 ADVANCED JAVA
11. Write a program to select a particular column from
the table.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SelectParticularColumnExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement()) {
String sql = "SELECT ename FROM employees";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
String ename = rs.getString("ename");
System.out.println("Name: " + ename);
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} }}
DEPTH OF MCA Page 18
ASSIGNMENT-2 ADVANCED JAVA
Output:
DEPTH OF MCA Page 19
ASSIGNMENT-2 ADVANCED JAVA
12. Write a program to select range of records based on
address.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SelectRangeByAddressExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
Statement stmt = conn.createStatement()) {
String sql = "SELECT * FROM employees WHERE eaddr LIKE '123%'";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
int eno = rs.getInt("eno");
String ename = rs.getString("ename");
double esal = rs.getDouble("esal");
String eaddr = rs.getString("eaddr");
System.out.print("ID: " + eno);
System.out.print(", Name: " + ename);
System.out.print(", Salary: " + esal);
System.out.println(", Address: " + eaddr);
DEPTH OF MCA Page 20
ASSIGNMENT-2 ADVANCED JAVA
rs.close();
} catch (SQLException e) {
e.printStackTrace();
Output:
DEPTH OF MCA Page 21
ASSIGNMENT-2 ADVANCED JAVA
13. Write a program to select range of records based on
salaries.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SelectRangeBySalaryExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
double minSalary = 50000;
double maxSalary = 70000;
String sql = "SELECT * FROM employees WHERE esal BETWEEN 50000 AND 60000";
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setDouble(1, minSalary);
pstmt.setDouble(2, maxSalary);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
int eno = rs.getInt("eno");
String ename = rs.getString("ename");
double esal = rs.getDouble("esal");
String eaddr = rs.getString("eaddr");
DEPTH OF MCA Page 22
ASSIGNMENT-2 ADVANCED JAVA
System.out.print("ID: " + eno);
System.out.print(", Name: " + ename);
System.out.print(", Salary: " + esal);
System.out.println(", Address: " + eaddr);
} catch (SQLException e) {
System.err.println("SQL Exception: " + e.getMessage());
e.printStackTrace();
Output:
DEPTH OF MCA Page 23
ASSIGNMENT-2 ADVANCED JAVA
14. Write a program to select range of records based on
initial characters of the employee name.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SelectRangeByNameExample {
static final String DB_URL = "jdbc:mysql://localhost:3306/yourDatabaseName";
static final String USER = "SYSTEM";
static final String PASS = "dbms123";
public static void main(String[] args) {
String initialChars = "Jo";
String sql = "SELECT * FROM employees WHERE ename LIKE ‘%R’";
try (Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, initialChars + "%");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
int eno = rs.getInt("eno");
String ename = rs.getString("ename");
double esal = rs.getDouble("esal");
String eaddr = rs.getString("eaddr");
System.out.print("ID: " + eno);
System.out.print(", Name: " + ename);
System.out.print(", Salary: " + esal);
DEPTH OF MCA Page 24
ASSIGNMENT-2 ADVANCED JAVA
System.out.println(", Address: " + eaddr);
} catch (SQLException e) {
System.err.println("SQL Exception: " + e.getMessage());
e.printStackTrace();
Output:
DEPTH OF MCA Page 25
