/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package databaza;

import java.sql.*;

/**
 *
 * @author student
 */
public class Main {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // TODO code application logic here
        String userName = "root";
        String password = "";
        String URL = "jdbc:mysql://localhost/zamestnanci";
        Connection con = null;
        Statement stmt = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(URL, userName, password);
        } catch (Exception e) {
            System.out.println("SQL Exception: " + e.toString());
        }

        // vsetci
        try {
            stmt = con.createStatement();

            String SQL = "SELECT * FROM zamestnanci";
            ResultSet result = stmt.executeQuery(SQL);
            System.out.println("Vsetci:");
            while (result.next()) {
                System.out.println(result.getString("meno") + ":"
                        + result.getString("pocet_deti") + ":"
                        + result.getString("bydlisko") + ":"
                        + result.getString("stav"));
            }
            System.out.println();
        } catch(SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
        }

        // bezdetny
        try {
            stmt = con.createStatement();

            String SQL = "SELECT * FROM zamestnanci WHERE pocet_deti < 1";
            ResultSet result = stmt.executeQuery(SQL);
            System.out.println("Bezdetny:");
            while (result.next()) {
                System.out.println(result.getString("meno") + ":"
                        + result.getString("pocet_deti") + ":"
                        + result.getString("bydlisko") + ":"
                        + result.getString("stav"));
            }
            System.out.println();
        } catch(SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
        }

         // nitrancania
        try {
            stmt = con.createStatement();

            String SQL = "SELECT * FROM zamestnanci WHERE bydlisko LIKE '%Nitra%'";
            ResultSet result = stmt.executeQuery(SQL);
            System.out.println("Nitrancania:");
            while (result.next()) {
                System.out.println(result.getString("meno") + ":"
                        + result.getString("pocet_deti") + ":"
                        + result.getString("bydlisko") + ":"
                        + result.getString("stav"));
            }
            System.out.println();
        } catch(SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
        }


        // priemerny pocet deti
        try {
            stmt = con.createStatement();

            String SQL = "SELECT AVG(pocet_deti) AS pocet_deti FROM zamestnanci";
            ResultSet result = stmt.executeQuery(SQL);
            System.out.println("Priemerny pocet deti:");
            while (result.next()) {
                System.out.println(result.getString("pocet_deti"));
            }
            System.out.println();
        } catch(SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
        }

         // pocet slobodnych
        try {
            stmt = con.createStatement();

            String SQL = "SELECT count(stav) AS stav FROM zamestnanci WHERE stav LIKE 'slobodny'";
            ResultSet result = stmt.executeQuery(SQL);
            System.out.println("Pocet slobodnych:");
            while (result.next()) {
                System.out.println(result.getString("stav"));
            }
            System.out.println();
        } catch(SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
        }

         // pocet ludi mimo NR
        try {
            stmt = con.createStatement();

            String SQL = "SELECT count(bydlisko) AS stav FROM";
            ResultSet result = stmt.executeQuery(SQL);
            System.out.println("Pocet ludi mimo NR:");
            while (result.next()) {
                System.out.println(result.getString("stav"));
            }
            System.out.println();
            stmt.close();
            con.close();
        } catch(SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
        }

    }

}