Module 2: Databases and Java

Looking for ‘database management with java and sql module 2 answers‘?

In this post, I provide accurate answers and detailed explanations for Module 2: Databases and Java of Course 4: Database Management with Java and SQL – Amazon Junior Software Developer Professional Certificate.

Whether you’re preparing for quizzes or brushing up on your knowledge, these insights will help you master the concepts effectively. Let’s dive into the correct answers and detailed explanations for each question.

Knowledge check: Introduction to JDBC

Practice Assignment

1. Which format is correct for a JDBC URL in Java to connect to a MySQL database?

  • jdbc://mysql:localhost:3306/database_name
  • mysql://localhost:3306/database_name
  • mysql:jdbc://localhost:3306/database_name
  • jdbc:mysql://localhost:3306/database_name

Explanation: The correct JDBC URL format for MySQL includes the jdbc:mysql prefix followed by the host, port, and database name. For example:

2. A developer wants to connect to a MySQL database on clicknbuy on localhost, running on port 3308. The credentials are username: admin, password: root. Which options correctly describe the steps or JDBC URL? Select all that apply.

  • The JDBC URL should be: jdbc:mysql://localhost:3306/clicknbuy.
  • Use the DriverManager.getConnection(“jdbc:mysql://localhost:3308/clicknbuy”, “admin,” “root”) method to establish the connection.
  • The JDBC URL should be: jdbc:mysql://localhost:3308/clicknbuy.
  • Use the DriverManager.getConnection(“jdbc:mysql://localhost:3308/clicknbuy”, “root,” “admin”) method to establish the connection.

Explanation:
The JDBC URL specifies the protocol, host, port, and database name:jdbc:mysql://localhost:3308/clicknbuy

The DriverManager.getConnection method takes the JDBC URL, username (admin), and password (root) as arguments.

3. Which of the following are the correct steps to execute a query using Connection and Statement in JDBC? Select all that apply.

  • Execute the query using the Statement object.
  • Establish a connection using the Connection object.
  • Create a PreparedStatement object.
  • Load the JDBC driver.

Explanation:

  1. Load the JDBC driver (if not using auto-loading in modern Java versions).
  2. Establish a connection using the DriverManager or DataSource.
  3. Execute the query using a Statement or PreparedStatement.
    Creating a PreparedStatement is optional and used when dynamic parameter binding is required.

4. Which of the following methods is used to move the cursor forward one row in a ResultSet in JDBC?

  • next()
  • last()
  • first()
  • previous()

Explanation: The ResultSet.next() method moves the cursor to the next row in the result set. It returns true if there is another row, otherwise false.

5. Which statements about parameterized queries in PreparedStatement in JDBC are true? Select all that apply.

  • Parameterized queries help prevent syntax errors in SQL statements.
  • Parameterized queries allow setting values dynamically using placeholders (?).
  • Parameterized queries improve performance by pre-compiling the SQL query.
  • Parameterized queries can only be used for SELECT statements.

Explanation:

  • Parameterized queries prevent SQL injection and syntax errors by using placeholders (?).
  • The SQL query is precompiled, improving performance when executing the same query multiple times with different parameters.
  • Parameterized queries can be used for all types of SQL statements, not just SELECT.

Knowledge check: Connection management

Practice Assignment

6. Which of the following is a key difference between using DriverManager and DataSource for database connection management?

  • DriverManager is used for multi-database connections, while DataSource can only manage a single database.
  • DriverManager supports connection pooling, while DataSource does not.
  • DataSource requires external libraries, whereas DriverManager does not.
  • DataSource provides a more flexible and configurable way of managing connections, supporting connection pooling.

Explanation:

  • DriverManager is a straightforward way to get database connections but lacks advanced features like connection pooling.
  • DataSource supports connection pooling, making it ideal for production environments requiring efficient resource management and better scalability.

7. True or False: The try-with-resources statement is the preferred way to manage database resources like Connection, Statement, and ResultSet.

  • True
  • False

Explanation:
The try-with-resources statement automatically closes resources (such as Connection, Statement, and ResultSet) that implement the AutoCloseable interface, ensuring proper resource management and avoiding memory leaks.

8. Which of the following properties are typically included in a database connection configuration to ensure secure and optimal database access? Select all that apply.

  • Database Driver Class Name
  • Password
  • URL
  • Username

Explanation:
These properties are essential for establishing a connection:

  • URL provides the database’s location.
  • Username and Password are credentials for authentication.

9. Which of the following statements correctly describes the lifecycle of a database connection and best practices for managing connections? Select all that apply.

  • Always closing a database connection, statement, and result set in a finally block or using a try-with-resources statement is considered a best practice.
  • Opening and closing a new connection for each SQL query is an optimal strategy for managing connections in high-concurrency environments.
  • Using a connection pool helps optimize resource usage by reusing existing connections instead of creating new ones for each database operation.
  • It is recommended to keep a database connection open for the entire lifecycle of an application to avoid the overhead of opening and closing connections.

Explanation:

  1. Closing resources prevents resource leaks.
  2. Connection pools enhance performance in high-concurrency environments by reusing connections.
  3. Opening and closing a connection for each query increases overhead and is not optimal for high-concurrency systems.
  4. Keeping a connection open for the entire application lifecycle wastes resources and is not recommended.

10. Which of the following code snippets correctly opens a connection, executes a query, and closes the resources properly?

Knowledge check: Data definition language

Practice Assignment

11. True or False: In JDBC, the execute() method can be used to run both DDL and DML SQL statements.

  • True
  • False

Explanation:
The execute() method is versatile and can be used for DDL (Data Definition Language) statements like CREATE TABLE or DROP TABLE as well as DML (Data Manipulation Language) statements like INSERT, UPDATE, and DELETE.

12. You’re developing a Java application for an e-commerce platform that requires creating a new table named products to store product details (e.g. ID and name). The products table will be used to manage inventory data effectively.

Which SQL statement would you pass to the stmt.execute() method to create this table correctly?

  • INSERT INTO products (id, name) VALUES (1, ‘Phone’);
  • CREATE products (id INT PRIMARY KEY, name VARCHAR(50));
  • DROP TABLE products;
  • CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(50));

Explanation:
This statement correctly creates the products table with two columns:

  • id: an integer column that serves as the primary key.
  • name: a variable-length text column with a maximum length of 50 characters.
    Other options like INSERT or DROP do not create tables.

13. Using JDBC, how can you safely drop the products table without throwing an exception if it doesn’t exist?

  • stmt.execute(“DROP TABLE IF EXISTS products”);
  • stmt.execute(“DROP TABLE products”);
  • stmt.execute(“TRUNCATE TABLE products”);
  • stmt.execute(“DROP IF EXISTS products”);

Explanation:
Using the IF EXISTS clause ensures the statement does not throw an error if the table doesn’t exist. This is a best practice for managing database schemas safely.

14. What is the purpose of setting conn.setAutoCommit(false); in JDBC?

  • To manually control when transactions are committed.
  • To automatically close the connection.
  • To automatically commit each SQL statement executed.
  • To roll back transactions automatically.

Explanation:
By default, JDBC automatically commits each SQL statement. Setting AutoCommit to false allows the developer to manually commit or roll back transactions, providing more control over database operations.

15. Complete the following JDBC code that performs an update operation and prints the number of rows affected.

  • conn.commit()
  • pstmt.executeUpdate()
  • pstmt.execute()
  • pstmt.getUpdateCount()

Explanation:
The executeUpdate() method is used to execute SQL statements like INSERT, UPDATE, or DELETE. It returns the number of rows affected by the operation, which can then be printed or logged. Other options like execute() are not specific to updates, and getUpdateCount() retrieves the count but does not execute the statement itself.

Module quiz: Databases and Java

Practice Assignment

16. What’s wrong with the following statement about JDBC architecture: The JDBC API translates Java method calls into database-specific calls?

  • The JDBC Driver, not the API, translates Java method calls into database-specific calls.
  • The ResultSet translates method calls into database-specific calls.
  • JDBC does not support database-specific calls.
  • The JDBC API manages connection pooling, not method call translation.

Explanation: The JDBC API defines a standard set of interfaces for database operations, but the actual translation of Java method calls to database-specific calls is performed by the JDBC driver.

17. Which of the following code snippets correctly uses the executeQuery() method to execute a SELECT statement in JDBC?

Explanation: The executeQuery() method is specifically designed for executing SQL SELECT statements and returns a ResultSet containing the query results.

18. True or False: JDBC allows handling SQL transactions through methods like commit() and rollback().

  • True
  • False

Explanation: JDBC provides transaction management capabilities through methods like commit(), rollback(), and setAutoCommit(false) to ensure atomicity and control over transaction execution.

19. When using a DataSource to manage connections, which method is called to obtain a connection object?

  • openConnection()
  • establishConnection()
  • createConnection()
  • getConnection()

Explanation: The getConnection() method is used to retrieve a Connection object from a DataSource.

20. Identify and fix the error in the following code, which attempts to create a table:

What needs to be done to fix the error?

  • Change stmt.executeUpdate() to stmt.executeQuery().
  • Replace MAKE TABLE with CREATE TABLE.
  • Add AUTO_INCREMENT to the id column definition.
  • Change name VARCHAR(50) to name STRING(50).

Explanation: The SQL command MAKE TABLE is not valid. The correct SQL command to create a table is CREATE TABLE.

21. Identify and fix the error in the following code, which attempts to handle an SQLException:

  • Change executeUpdate() to executeQuery().
  • Replace e.log() with System.out.println(“Error: ” + e.getMessage()) and e.printStackTrace().
  • Add a finally block to close the connection.
  • Change connection.createStatement() to connection.prepareStatement().

Explanation: The SQLException class does not have a log() method. Instead, use getMessage() to retrieve the error message and printStackTrace() for debugging.

22. Identify and fix the error in the following code, which attempts to update a record in a table:

What needs to be done to fix the error?

  • Change stmt.executeUpdate() to stmt.executeQuery().
  • Replace MODIFY with UPDATE.
  • Add WHERE before users.
  • Change name=’John’ to name=’Doe’.

Explanation: The UPDATE command is used to modify data in a table. The MODIFY keyword is not valid in this context.

23. Which SQL command should you use to add a column to an existing table?

  • ALTER TABLE table_name ADD column_name column_type;
  • UPDATE TABLE table_name ADD column_name column_type;
  • INSERT INTO TABLE
  • ADD COLUMN

Explanation: The ALTER TABLE command is used to modify an existing table, including adding new columns.

24. What is the expected outcome after the following code runs?

  • The SQL command fails because the executeQuery() method should be used.
  • The courses table is deleted from the database.
  • Nothing happens, and the table remains in the database.
  • The courses table is modified but not deleted.

Explanation: The DROP TABLE command removes the specified table and its data from the database.

25. You are transferring $500 from Account A to Account B. The debit from Account A and the credit to Account B must either occur together or not at all. Which JDBC feature ensures this?

  • Transaction management
  • Connection pooling
  • Batch processing
  • Savepoints

Explanation: Transaction management ensures that multiple operations, such as the debit and credit in this case, are treated as a single atomic unit of work. If any part of the transaction fails, the changes can be rolled back.

Related contents:

Module 1: Introduction to databases
Module 3: Displaying data
Module 4: Final project and assessment: Data structures and algorithms

You might also like:

Course 1: Introduction to Software Development
Course 2: Programming with Java
Course 3: Data Structures and Algorithms
Course 5: Fullstack Web Development
Course 6: Generative AI in Software Development
Course 7: Application Development

Leave a Reply