import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author sergioelementary
 */
public class Ej5_consultaSQLite {
    static String sqlite_jdbd_driver="org.sqlite.JDBC";
    static String prefix="jdbc:"+"sqlite:";
    static String hostName="";
    static String urlFolder="/home/sergioelementary/ZEjemploBD_AD/";
    static String dbName="ejemplo1.db";



    static String driver=sqlite_jdbd_driver;

    static String user=""; //"user";
    static String password="";

    static String url=prefix+hostName+urlFolder+dbName;

    public static void main(String[] args) {
        statementQueryExample();


    }

    public static void statementQueryExample()
//	para cada NOMBRE de departamento el salario medio de sus empleados
//    y el Salario del empleado con mayor salario
    { String query="SELECT d.dnombre,"
                    + "(SELECT AVG(salario)from empleados where dept_no=d.dept_no) salarioMedio,"
                    + "(SELECT apellido FROM empleados WHERE dept_no=d.dept_no AND salario=(SELECT MAX(salario)from empleados where dept_no=d.dept_no)),"
                    + "(SELECT MAX(salario)from empleados where dept_no=d.dept_no) salarioMax "
                    + "FROM empleados e,departamentos d WHERE d.dept_no=e.dept_no GROUP BY d.dnombre";// GROUP BY para que muestre 1 por departamento

	 try{
	 	 //Load the driver in RAM
		 Class.forName(driver);

                //Connect to DB
		 Connection connection=DriverManager.getConnection(url,user,password);

		 Statement statement=connection.createStatement();
		 ResultSet result=statement.executeQuery(query);

                 String departamento="";
		 //Iterate on the 'ResultSet' to process each row
		 while(result.next()){//There are still rows to get

                        System.out.println(result.getString(1)+"\t salario medio= "+result.getString(2)+"\t Apellido maxSalario= "+result.getString(3)
                                +"\t"+result.getString(4));
		 }

		 result.close(); //close ResultSet
		 statement.close();//close Statement
		 connection.close();//close Connection

	 }catch(ClassNotFoundException cnfe){
			System.out.printf("Not found the jdbc driver %s\n", driver);
	 }catch (SQLException sqle){
			System.out.println("SQL Exception");
	 }
	}

}