Silver Oak University Students: Mastering SQL and JDBC Before a Java Enterprise Internship

To be ready for a Java enterprise internship, you need three connected skills: writing SQL joins that return correct rows, wrapping related updates in a transaction, and running those statements from Java through JDBC prepared statements with every resource closed properly. The worksheet below practises all three on one small inventory database, and it fits into 14 days of focused work.

Why one inventory database is enough to learn SQL and JDBC together

Most students learn SQL in one lab and JDBC in another, so the two never connect. Enterprise code does not separate them. A typical service method receives a request, validates it, opens a connection, runs several statements, commits or rolls back, and returns a result. If you can do that for an inventory system, you can do it for orders, bookings or payroll, because the patterns repeat.

Inventory is a good teaching domain because it has natural relationships (a product belongs to a category and a supplier), natural multi-step operations (selling an item reduces stock and creates an order line), and natural failure cases (stock cannot go below zero). Those three properties map directly to joins, transactions and error handling.

The inventory schema used throughout this worksheet

The schema is illustrative and deliberately small. Use MySQL or PostgreSQL, whichever your lab or laptop already has.

Insert at least five products, three categories, three suppliers and one product with no supplier (leave supplier_id null). That last row matters for the outer join exercise.

Part A: SQL join exercises on the inventory tables

Write each query yourself before checking it. Predict the row count first, then run it. If your prediction is wrong, find out why; that gap is where learning happens.

1. Inner join. List every product with its category name and supplier name.
2. Left join. List all products, including the one with no supplier, showing “Unassigned” where the supplier is missing (use COALESCE).
3. Anti-join. Find categories that have no products (left join plus a null check).
4. Aggregate with join. Total quantity sold per product, including products never sold (left join from product to order_item, then GROUP BY).
5. Multi-table join. For each order, show customer name, product name, quantity and line total (quantity multiplied by unit_price).
6. HAVING. Suppliers whose products have a combined stock below 20.

Join mistakes that quietly return wrong data

• Putting a filter on the right-hand table in the WHERE clause of a left join, which silently turns it into an inner join. Move the condition into the ON clause when you want to keep unmatched rows.
• Forgetting a join condition and producing a cartesian product. If 5 products and 4 orders return 20 rows, something is missing.
• Counting with COUNT(*) after a left join, which counts the null-extended row as 1. Use COUNT(column_from_right_table).
• Selecting non-aggregated columns that are not in GROUP BY, which some databases tolerate and others reject.

Part B: Transactions with the inventory sale scenario

Selling a product is two or more changes that must succeed or fail together: insert the order, insert the order item, and reduce stock. Practise it first in the SQL console.

START TRANSACTION;
INSERT INTO customer_order (customer_name, order_date) VALUES ('Test User', CURRENT_DATE);
-- use the generated order_id below
INSERT INTO order_item (order_id, product_id, quantity, unit_price) VALUES (1, 101, 2, 499.00);
UPDATE product SET stock_qty = stock_qty - 2 WHERE product_id = 101 AND stock_qty >= 2;
-- if the update affected 0 rows, ROLLBACK; otherwise COMMIT
COMMIT;

Notice the condition stock_qty >= 2 inside the UPDATE. It makes the check and the change a single atomic step, so two simultaneous buyers cannot both take the last item.

Exercises for this part:

• Run the block, then replace COMMIT with ROLLBACK and confirm nothing persisted.
• Try to sell more than the available stock and confirm the whole sale is abandoned.
• Open two sessions and observe what the second session sees before the first commits. Read your database’s documentation on its default isolation level, and write one sentence on what it means.

Part C: JDBC prepared statements, step by step

Now move the same logic into Java. The examples below are illustrative sketches, not tested output; adapt the URL, user and driver to your own setup and add the driver dependency to your project.

A safe read using try-with-resources

String sql = "SELECT p.name, c.name AS category FROM product p "
           + "JOIN category c ON c.category_id = p.category_id "
           + "WHERE p.price <= ?";
try (Connection con = DriverManager.getConnection(url, user, pass);
     PreparedStatement ps = con.prepareStatement(sql)) {
    ps.setBigDecimal(1, maxPrice);
    try (ResultSet rs = ps.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("name") + " - " + rs.getString("category"));
        }
    }
}

The value arrives through a ? placeholder, and the three resources (connection, statement, result set) close automatically in reverse order, even when an exception is thrown.

The sale as a JDBC transaction

try (Connection con = DriverManager.getConnection(url, user, pass)) {
    con.setAutoCommit(false);
    try (PreparedStatement upd = con.prepareStatement(
            "UPDATE product SET stock_qty = stock_qty - ? WHERE product_id = ? AND stock_qty >= ?")) {
        upd.setInt(1, qty);
        upd.setInt(2, productId);
        upd.setInt(3, qty);
        if (upd.executeUpdate() == 0) {
            con.rollback();
            throw new IllegalStateException("Insufficient stock");
        }
        // insert order and order_item here with their own PreparedStatements
        con.commit();
    } catch (SQLException | RuntimeException e) {
        con.rollback();
        throw e;
    }
}

Two details to study: setAutoCommit(false) is what makes the group atomic, and executeUpdate() returns the affected row count, which is how you detect the “not enough stock” case.

Mistakes in JDBC code that interviewers and mentors spot quickly

A note on placeholders: they work for values, not for table or column names. If you must choose a column to sort by, map user input to a fixed list of allowed column names in Java rather than concatenating the input.

Worked example: tracing one bug from wrong report to fixed query

This example is illustrative. A student writes a report of units sold per product:

SELECT p.name, COUNT(*) AS sold
FROM product p
LEFT JOIN order_item oi ON oi.product_id = p.product_id
GROUP BY p.name;

Suppose the inventory holds a product “USB Cable” that has never been ordered. The report shows sold = 1 for it. The cause: the left join keeps the cable with null order columns, and COUNT(*) counts that row. Two further problems exist. COUNT counts order lines rather than units, and grouping by name merges two different products that share a name.

SELECT p.product_id, p.name, COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM product p
LEFT JOIN order_item oi ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name;

Now the cable shows 0, quantities are summed, and products are grouped by their key. Wrap this in a Java method with a PreparedStatement and a try-with-resources block, and you have a complete, reviewable feature. The lesson is to test with the awkward row (never sold, no supplier), not only with the happy path.

Silver Oak University students: planning SQL and JDBC practice around your own rules

Silver Oak University is a private university in Ahmedabad, Gujarat. This guide has no verified, official information on the university’s internship or industrial training rules, its syllabus for database or Java courses, its academic calendar, or how its training and placement cell handles outside internships. So nothing here should be read as a statement of what the university requires or accepts.

That matters for how you use this worksheet. Your department may already prescribe a DBMS course, a Java lab or a project, and the database system used in your lab may differ from the one in this article. The SQL concepts transfer, but syntax details such as auto-increment columns, date functions and transaction keywords vary between products. Practise on the system you will actually use in the internship, which your mentor can tell you.

Also check whether an internship you do independently can be reflected in your records. Whether it counts for any requirement is a decision for your department, not for this article or for the internship provider. A free online internship such as the free two-week CareerSwami internship offers an offer letter and a completion certificate after successful completion; what your college does with such a certificate is its own call.

Verification steps:

1. Ask your class coordinator or head of department, in writing, whether an online internship of 14 days can be recorded, and under which rule or circular.
2. Request the current syllabus for your database and Java courses from your department office, and compare its topics against Parts A to C.
3. Ask the lab in-charge which DBMS and JDBC driver versions are installed on lab machines, and whether you may install your own.
4. Ask the training and placement cell what documents (offer letter, certificate, report) they expect for any internship you complete.
5. Find your semester break dates in the official academic calendar from the university office so you can schedule the 14 days.

A 14-day schedule for SQL, transactions and JDBC practice

Self-check checklist before your first internship task

• I can explain the difference between inner, left and anti-joins using the inventory tables.
• I can predict the row count of a join before running it.
• I can say what atomicity means and show it with a rollback.
• No query in my code concatenates user input.
• Every Connection, PreparedStatement and ResultSet is opened in try-with-resources.
• My multi-step operation turns autocommit off and rolls back on failure.
• My credentials are not in committed source files.
• I tested the awkward cases: empty result, null supplier, insufficient stock.

Frequently Asked Questions

Do I need to know Spring or Hibernate before a Java enterprise internship?

Plain JDBC first is the better order. Frameworks such as Spring JDBC and Hibernate wrap the same concepts of connections, statements and transactions. If you understand what they do underneath, the frameworks make far more sense and debugging becomes easier.

Why is PreparedStatement better than Statement?

It separates the SQL structure from the values, which prevents SQL injection and avoids quote-handling bugs. It also lets the database reuse the parsed statement, which can help when you run the same query many times.

Is try-with-resources enough to avoid connection leaks?

For code that opens its own connections, yes, provided every resource is declared in the try header. In larger applications you will borrow connections from a pool, and closing them returns them to the pool, so the same habit applies.

When should I use a transaction?

Whenever two or more changes only make sense together, such as creating an order line and reducing stock. A single independent read or insert usually does not need an explicit transaction.

Which database should I practise on?

Use the one your internship or lab uses. If you do not know, MySQL or PostgreSQL are both common choices for learning, and the core join and transaction concepts are the same across them.

Can I finish this worksheet in 14 days alongside college classes?

The schedule above assumes roughly two focused hours a day. If your timetable is heavier, keep Parts A to C in order and shorten the DAO refactor rather than skipping transaction practice.

Will this worksheet earn credit at my college?

That depends entirely on your department’s rules, which this article cannot confirm. Ask in writing, as described in the verification steps above.

What should I put on GitHub after the 14 days?

Your SQL scripts, the Java project, and a README explaining the schema, how to run the program and what each transaction guarantees. Leave out passwords and local configuration files.

Related Java & Enterprise Software guides

• Tantia University: A Phone-Only Plan for Learning Java When You Lack a Laptop
• Lamrin Tech Skills University Students: Reading Legacy Java Code During an Internship
• Oriental University Students: Unit Testing With JUnit in Your First Java Internship
• All Java & Enterprise Software internship guides