/*
      FileName: SQLLab18.java
        Author: Ryan Pennington
          Date: Apr 5, 2010 10:21:13 AM
 Description:
 */
//PACKAGE jdbcexercises;
import JAVA.SQL.*;
PUBLIC class SQLLab18 EXTENDS JAVA.lang.Object
{
    static Connection conn = NULL;
    static Statement stmt = NULL;
    //oracle drive, hostname, AND port NUMBER here
    static String dbURL ="jdbc:oracle:thin:@127.0.0.1:1521:XE";
    static String USER = "Projects";
    static String password = "";


    PUBLIC static void main(String[] args) throws SQLException
    {

        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }
        catch (ClassNotFoundException e)
        {
            SYSTEM.OUT.println(e.getMessage());
        }

        try {
            conn = DriverManager.getConnection(dbURL, USER, password);
            SYSTEM.OUT.println("Connection opened! for driver ==> Oracle 10g");
          SYSTEM.OUT.println("\nNorth American Countries   " + "   # of Customers");
            SYSTEM.OUT.println("=========================    ===================");
            String SQL = "SELECT DISTINCT REPLACE(country, 'USA', 'United States') AS \"North American Countries\", LPAD(COUNT(contactname,20,'*')) as \"# of Customers\" FROM customers WHERE country = 'Canada' OR country = 'USA' OR country = 'Mexico' GROUP BY country";
            ResultSet rs = stmt.executeQuery(SQL);
            WHILE( rs.next())
            {
                SYSTEM.OUT.println(rs.getString(1) + " " + rs.getString(2));
            }
        rs.CLOSE();

        } catch (SQLException e) {
            SYSTEM.err.println(e.getMessage());
        } finally {
            IF(conn != NULL) {conn.CLOSE();}
            SYSTEM.OUT.println("\nConnection Closed!");

        }
    }

}