Question

What happens when you try to compile and run the following code?
String query = "INSERT INTO Invoices (InvoiceDate InvoiceTotal) "
             + "VALUES ('2/10/01', '443.55')";
Statement statement = connection.createStatement();
statement.executeUpdate(query);

	a.	A SQLException is thrown at runtime because the executeUpdate method can’t accept a String object.
	b.	A SQLException is thrown at runtime because the SQL statement is coded incorrectly.
	c.	An error occurs at compile time because the SQL statement is coded incorrectly.
	d.	This code compiles and runs without any problems.

1. Assuming an appropriate database driver is available when the code below is executed, which of the following statements is true?
   private Connection getConnection() throws SQLException {
       String dbUrl = "jdbc:sqlite:products.sqlite";
       Connection connection = DriverManager.getConnection(dbUrl);
       return connection;
   }

   	a.	The driver must be loaded before this code is executed.
   	b.	The getConnection() method automatically loads the driver based on the database URL.
   	c.	The driver is loaded when the variable named dbUrl is declared.
   	d.	The driver won’t be loaded and an SQLException will be thrown.

1 points

Question 8

1. Given the code below, which of the following statements will delete all records from the Invoices table that have a value of 0 in the InvoiceTotal column?
   String query = "DELETE FROM Invoices " +
                  "WHERE InvoiceTotal = 0 ";
   Statement statement = connection.createStatement();

   	a.	statement.executeQuery(query);
   	b.	statement.execute();
   	c.	statement.deleteRow();
   	d.	statement.executeUpdate(query);

1 points

Question 9

1. If InvoiceID is an INTEGER type in a SQLite database and InvoiceDate is a TEXT type, which of the following statements returns those columns of the current row in the dueInvoices result set created by the code shown below?
   Statement statement = connection.createStatement();
   String query = "SELECT InvoiceID, InvoiceDate "
                + "FROM Invoices "
                + "WHERE InvoiceTotal >= 0 "
                + "ORDER BY InvoiceDate ASC";
   ResultSet dueInvoices = statement.executeQuery(query);
   dueInvoices.next();

   	a.	int id = dueInvoices.getInt(0); String date = dueInvoices.getString(1);
   	b.	int id = dueInvoices.getInt("InvoiceID"); String date = dueInvoices.getString("InvoiceDate");
   	c.	int id = dueInvoices.getInteger("InvoiceID"); String date = dueInvoices.getText("InvoiceDate");
   	d.	int id = dueInvoices.getInteger(1); String date = dueInvoices.getText(2);

1 points

Question 10

1. What rows are deleted after the code that follows is executed?
   String sql = "DELETE FROM Customer "
              + "WHERE FirstName = ?";
   PreparedStatement ps = connection.prepareStatement(sql);
   ps.setString(1, "John");
   ps.executeUpdate();

   	a.	The first row in the Customers table.
   	b.	The first row in the Customers table where the FirstName column is “John”
   	c.	All rows from the Customers table
   	d.	All rows in the Customers table where the FirstName column is “John”

Answer 1

Hi,

Q.1.String query = "INSERT INTO Invoices (InvoiceDate InvoiceTotal) "
             + "VALUES ('2/10/01', '443.55')";
Statement statement = connection.createStatement();
statement.executeUpdate(query);

Answer :(B)-A SQLException is thrown at runtime because the SQL statement is coded incorrectly.

"INSERT INTO Invoices (InvoiceDate , InvoiceTotal) "
             + "VALUES ('2/10/01', '443.55')";

Here this statement is missing a comma in between the table column(InvoiceDate , InvoiceTotal) .while compiling code won't raise any error but while runtime it will raise sqlerror for incorrect query.

Q.2 Assuming an appropriate database driver is available when the code below is executed, which of the following statements is true?
private Connection getConnection() throws SQLException {
    String dbUrl = "jdbc:sqlite:products.sqlite";
    Connection connection = DriverManager.getConnection(dbUrl);
    return connection;

}

Answer:(A)-The driver must be loaded before this code is executed.

The programming involved to establish a JDBC connection is fairly simple. Here are these simple four steps −

• Import JDBC Packages: Add import statements to your Java program to import required classes in your Java code.

• Register JDBC Driver: This step causes the JVM to load the desired driver implementation into memory so it can fulfill your JDBC requests.

• Database URL Formulation: This is to create a properly formatted address that points to the database to which you wish to connect.

• Create Connection Object: Finally, code a call to the DriverManager object's getConnection( ) method to establish actual database connection.

Here we are missing the second step .Register JDBC Driver

You can register using below code:

try {
   Driver myDriver = new oracle.jdbc.driver.OracleDriver();
   DriverManager.registerDriver( myDriver );
}
catch(ClassNotFoundException ex) {
   System.out.println("Error: unable to load driver class!");
   System.exit(1);
}

Q.3.Given the code below, which of the following statements will delete all records from the Invoices table that have a value of 0 in the InvoiceTotal column?
String query = "DELETE FROM Invoices " +
               "WHERE InvoiceTotal = 0 ";
Statement statement = connection.createStatement();

Answer: (d)-statement.executeUpdate(query);

For both update and delete process in database executeUpdate() is used.

Q.4 .If InvoiceID is an INTEGER type in a SQLite database and InvoiceDate is a TEXT type, which of the following statements returns those columns of the current row in the dueInvoices result set created by the code shown below?
Statement statement = connection.createStatement();
String query = "SELECT InvoiceID, InvoiceDate "
             + "FROM Invoices "
             + "WHERE InvoiceTotal >= 0 "
             + "ORDER BY InvoiceDate ASC";
ResultSet dueInvoices = statement.executeQuery(query);
dueInvoices.next();

Answer: (b) int id = dueInvoices.getInt("InvoiceID");
String date = dueInvoices.getString("InvoiceDate");

To fetch table data from resultset we used table column name and desired columntype

Q.5.What rows are deleted after the code that follows is executed?
String sql = "DELETE FROM Customer "
           + "WHERE FirstName = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setString(1, "John");//1 specifies the first parameter in the query
ps.executeUpdate();

Answer:(d)-All rows in the Customers table where the FirstName column is “John”

The PreparedStatement interface is a subinterface of Statement. It is used to execute parameterized query.

Let's see the example of parameterized query:

1. String sql="insert into emp values(?,?,?)";  

As you can see, we are passing parameter (?) for the values. Its value will be set by calling the setter methods of PreparedStatement.

Hope the answer is helpful