import java.sql.*;
import java.util.Random;
import java.util.Scanner;


public class Exercise2 {

	private static String url = "jdbc:postgresql://dbteach2/";
	private static String username = "lxe307";
	private static String pass = "wrumelib";

	static Scanner scanny = new Scanner(System.in);

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

		try {
		Class.forName("org.postgresql.Driver");
		} catch (ClassNotFoundException ex) {
		System.out.println("Driver not found");
		}
		System.out.println("PostgreSQL driver registered.")
		;
		Connection conn = null;
		try {
		conn = DriverManager.getConnection(url, username, pass);
		} catch (SQLException ex) {
		ex.printStackTrace();
		}
		if (conn != null) {
		System.out.println("Database accessed!");
		} else {
		System.out.println("Failed to make connection");
		}

	    try
	    {

	    	PreparedStatement dropTable = conn.prepareStatement(
	        "DROP TABLE IF EXISTS Student CASCADE; DROP TABLE IF EXISTS Lecturer CASCADE;" +
	        "DROP TABLE IF EXISTS Module CASCADE;DROP TABLE IF EXISTS Marks CASCADE;"+
	        "DROP TABLE IF EXISTS StudentContact CASCADE; DROP TABLE IF EXISTS NextOfKinContact CASCADE; " +
	        "DROP TABLE IF EXISTS Titles CASCADE;DROP TABLE IF EXISTS Type CASCADE;");

	        	dropTable.executeUpdate();
	            System.out.println("Tables dropped");

	    		try {
	    			conn.setAutoCommit(false);
	    		}
	    		catch (SQLException ex){
	    			ex.printStackTrace();
	    		}

	         PreparedStatement createTitlesT = conn.prepareStatement(
	         "CREATE TABLE Titles("+
	         	"titleID                INTEGER NOT NULL UNIQUE,"+
	            "titleString            CHAR(30), CHECK (titleString ='Mr' OR "+
	                                         "titleString ='Mrs' OR "+
	                                         "titleString = 'Ms' OR "+
	                                         "titleString ='Miss' OR "+
	                                         "titleString ='Dr'  OR "+
	                                         "titleString ='Master' ),"+
	            "PRIMARY KEY(titleID)"+
	            ");"
	            );
	         createTitlesT.executeUpdate();
	         System.out.println("Titles Table created");

	         String titlesString = "Insert into Titles VALUES (1, 'Mr');"+
	         					   "Insert into Titles VALUES (2, 'Mrs');"+
	        	        		   "Insert into Titles VALUES (3, 'Ms');"+
	    	        		       "Insert into Titles VALUES (4, 'Miss');"+
		        		           "Insert into Titles VALUES (5, 'Dr');"+
		        		           "Insert into Titles VALUES (6, 'Master');";
	         PreparedStatement addTitles = conn.prepareStatement(titlesString);
	         addTitles.executeUpdate();

	    	 PreparedStatement createTypeT = conn.prepareStatement(
	    	 "CREATE TABLE Type("+
	    	 	"typeID							INTEGER NOT NULL UNIQUE,"+
	    	 	"typeString						CHAR(30),"+
	    	 	"PRIMARY KEY(typeID)"+
	    	 	");"
	    	 	);
	    	 createTypeT.executeUpdate();
	    	 System.out.println("Type Table created");


	         String typeString = "Insert into Type VALUES (0, 'sit');"+
	         					 "Insert into Type VALUES (1, 'resit');"+
	        	        		 "Insert into Type VALUES (2, 'repeat');";
	         PreparedStatement addType = conn.prepareStatement(typeString);
	         addType.executeUpdate();

	         PreparedStatement createStudentT = conn.prepareStatement(
	         "CREATE TABLE Student("+
	         	"studentID						INTEGER NOT NULL UNIQUE,"+
	         	"titleID						INTEGER NOT NULL,"+
	         	"forename						CHAR(20),"+
	         	"familyName   					CHAR(30),"+
	         	"dateOfBirth					DATE,"+//maybe check constraints???
	         	"PRIMARY KEY(studentID),"+
	         	"FOREIGN KEY(titleID) REFERENCES Titles(titleID)"+
	         	");"
	         	);
	         createStudentT.executeUpdate();
	         System.out.println("Student Table created");


	         Random randy = new Random();
	         String addStudents="";
	         for(int i=1;i<101;i++) {

	        	 addStudents = addStudents+"Insert into Student VALUES (" + i + ", " +
	        	 ((Integer)(1+randy.nextInt(5))).toString() + ", '" + RandomName.getForename() +
	        	 "', '" + RandomName.getSurname() + "', '10-09-1994');";
	         }
	         PreparedStatement addStudentT = conn.prepareStatement(addStudents);
	         addStudentT.executeUpdate();


	         PreparedStatement createLecturerT = conn.prepareStatement(
	         "CREATE TABLE Lecturer("+
	         	"lecturerID						INTEGER NOT NULL UNIQUE,"+
	         	"titleID						INTEGER NOT NULL,"+
	         	"foreName						CHAR(20),"+
	         	"familyName						CHAR(30),"+
	         	"PRIMARY KEY(lecturerID),"+
	         	"FOREIGN KEY(titleID) REFERENCES Titles(titleID)"+
	         	");"
	         	);
	         createLecturerT.executeUpdate();
	         System.out.println("Lecturer Table created");

	         String addLecturers="";
	         for(int a=1; a<6; a++) {

	        	 addLecturers = addLecturers+"Insert into Lecturer VALUES (" + a + ", " +
	        	 ((Integer)(1+randy.nextInt(5))).toString() + ", '" + RandomName.getForename() +
	        	 "', '" + RandomName.getSurname() + "');";
	         }
	         PreparedStatement addLecturerT = conn.prepareStatement(addLecturers);
	         addLecturerT.executeUpdate();

	         PreparedStatement createModuleT = conn.prepareStatement(
	         "CREATE TABLE Module("+
	         	"moduleID						INTEGER NOT NULL UNIQUE,"+
	         	"moduleName						CHAR(50),"+
	         	"moduleDescription				CHAR(50),"+
	         	"lecturerID						INTEGER NOT NULL,"+
	         	"PRIMARY KEY(moduleID),"+
	         	"FOREIGN KEY(lecturerID) REFERENCES Lecturer(lecturerID)"+
	         	");"
	            );
	         createModuleT.executeUpdate();
	         System.out.println("Module Table created");

	         String addModules="";
	         for(int i=1; i<101; i++) {
	        	 addModules = addModules+"Insert into Module VALUES (" + i + ", 'Module Name "
	        	 + i + "', 'Module Description " + i + "', '" +
	        	 ((Integer)(1+randy.nextInt(5))).toString() + "');";
	         	}
	         PreparedStatement addModuleT = conn.prepareStatement(addModules);
	         addModuleT.executeUpdate();

	         PreparedStatement createMarksT = conn.prepareStatement(
	         "CREATE TABLE Marks("+
	         	"year							CHAR(10),"+
	         	"mark							INTEGER,"+
	         	"notes							CHAR(100),"+
	         	"typeID							INTEGER NOT NULL,"+
	         	"moduleID 						INTEGER NOT NULL,"+
	         	"studentID						INTEGER NOT NULL,"+
	            "FOREIGN KEY(moduleID) REFERENCES Module(moduleID),"+
	            "FOREIGN KEY(studentID) REFERENCES Student(studentID)"+
	         	");"
	         	);
	         createMarksT.executeUpdate();
	         System.out.println("Marks Table created");

	         String addMarks="";
	         for(int b=1; b<101; b++) {
	        	 addMarks = addMarks+"Insert into Marks VALUES ('2013-14 ', " +
	        	 ((Integer)(1+randy.nextInt(100))).toString() +
	        	 ", 'Notes '," + ((Integer)(randy.nextInt(3))).toString() +
	        	 ", " + ((Integer)(1+randy.nextInt(100))).toString() +
	        	 ", " + ((Integer)(b)).toString() + ");";
	         }

	         PreparedStatement addMarksT = conn.prepareStatement(addMarks);
	         addMarksT.executeUpdate();

	         PreparedStatement alterMarksT = conn.prepareStatement(
	         "ALTER TABLE Marks ADD CONSTRAINT marks_Key UNIQUE (studentID, moduleID);");
	         alterMarksT.executeUpdate();

	         PreparedStatement createStudentContactT = conn.prepareStatement(
	         "CREATE TABLE StudentContact("+
	         	"studentID						INTEGER NOT NULL UNIQUE,"+
	         	"eMailAddress					CHAR(50),"+
	         	"postalAddress 					CHAR(90),"+
	         	"FOREIGN KEY(studentID) references Student(studentID)"+
	         	");"
	         	);
	         createStudentContactT.executeUpdate();
	         System.out.println("StudentContact table created");

	         String addStuCon="";
	         for(int c=1; c<101; c++) {
	         	        addStuCon=addStuCon+"Insert into StudentContact VALUES (" + c
	         	        + ", 'eMailAddress " + c + "', 'postalAddress " + c + "');";
	         	        }

	         PreparedStatement addStuConT = conn.prepareStatement(addStuCon);
	         addStuConT.executeUpdate();

	         PreparedStatement createNextOfKinContactT = conn.prepareStatement(
	         "CREATE TABLE NextOfKinContact("+
	         	"studentID						INTEGER NOT NULL UNIQUE,"+
	         	"eMailAddress   				CHAR(50),"+
	         	"postalAddress					CHAR(90),"+
	         	"FOREIGN KEY(studentID) REFERENCES Student(studentID)"+
	         	");"
	         	);
	         createNextOfKinContactT.executeUpdate();
	         System.out.println("NextOfKinContact table created");

	         String addNextKin="";
	         for(int d=1;d<101;d++) {
	        	 addNextKin=addNextKin+"Insert into NextOfKinContact VALUES (" + d
	        	 + ", 'eMailAddress " + d + "', 'postalAddress " + d + "');";
	         }

	         PreparedStatement addNextKinT = conn.prepareStatement(addNextKin);
	         addNextKinT.executeUpdate();

	 	    try {
		    	conn.commit();
		    	conn.setAutoCommit(true);
		    }
		    catch (SQLException ex) {
		    	ex.printStackTrace();
		    }

	    }

	    catch (SQLException e)
	    {
	            e.printStackTrace();
	    }


	//Scanner s = new Scanner(System.in);
	String userChoice = "0"; //user's input
	int option = 0; //what the program will do
	do {
		System.out.println("Menu");
		System.out.println("Press 1 to register new student");
		System.out.println("Press 2 to add marks");
		System.out.println("Press 3 to view student transcript");
		System.out.println("Press 4 to view report");
		System.out.println("Press 5 to quit");
		System.out.println("");
		userChoice = scanny.nextLine();
		System.out.println(userChoice);
		if(userChoice.equals("1"))
		{
			option=1;
			System.out.println("1 entered");

		}
		else if(userChoice.equals("2"))
		{
			option=2;
			System.out.println("2 entered");

		}
		else if(userChoice.equals("3"))
		{
			option=3;
			System.out.println("3 entered");

		}
		else if(userChoice.equals("4"))
		{
			option=4;
			System.out.println("4 entered");

		}
		else if(userChoice.equals("5"))
		{
			option=5;
			System.out.println("5 entered");

		}else {
			option=0;
		}

			if(option== 1)
			{
				System.out.println("A new student is being registered");
				newStudent(conn);
			}
			else if (option== 2)
			{
				System.out.println("Marks are being added");
				addMark(conn);
			}
			else if (option== 3)
			{
				System.out.println("A transcript is being produced");
				produceTranscript(conn);

			}
			else if (option== 4)
			{
				System.out.println("A report is being produced");
				produceReport(conn);

			}


		} while(option != 5);
	}

    public static boolean validYear(String year)
    {
            boolean valid = true;
            if(year.length() != 7) //checks length of input is 7, e.g. 2014-15
            {
                    valid = false;
            }
            else
            {
                    String f1 = year.substring(0, 4); //stores first 4 elements, e.g. 2014
                    String f2 = year.substring(2, 4); //stores decade date of first year, e.g. 14
                    String s1 = year.substring(5, 7); //stores decade date of first year, e.g. 15

                    try
                    {
                            int if1 = Integer.parseInt(f1);
                            int if2 = Integer.parseInt(f2);
                            int is1 = Integer.parseInt(s1);

                            if(if1 < 1900 || if1 > 2014 || is1 != if2 + 1)
                            {
                                    valid = false;
                            }
                    }
                    catch (NumberFormatException e)
                    {
                            valid = false;
                    }
            }
            return valid;
    }

    public static boolean vDate(String date)
    {
            boolean valid = true;

            String day = date.substring(8, 10);
            String month = date.substring(5, 7);
            String year = date.substring(0, 4);

            int iyear = Integer.parseInt(year);
            int imonth = Integer.parseInt(month);
            int iday = Integer.parseInt(day);

            if(iday < 1 || (month.equals("02") && iday > 28) || imonth < 1 || imonth > 12 || iyear < 1900 || iyear > 2015 || ((!month.equals("09"))||(!month.equals("04"))
            		||(!month.equals("06"))||(!month.equals("11")))&& iday >30 || iday > 31)
            {
                    valid = false;
            }

            return valid;
    }

    public static boolean isInteger(String i)
    {
            boolean isint = true;

            try {Integer.parseInt(i);}
            catch (NumberFormatException e) {isint = false;}

            return isint;
    }

    public static void addMark(Connection conn) throws SQLException
    {
            System.out.println("Enter the student's ID:");
            String studentID = scanny.nextLine();

            while(!isInteger(studentID))
            {
                    System.out.println("Please enter a valid student ID in integer form:");
                    studentID = scanny.nextLine();
            }

            Statement stmt = conn.createStatement();

            ResultSet students = stmt.executeQuery("SELECT * FROM Student WHERE studentID = " + studentID + ";");

            while(!students.next())
            {
                    System.out.println("Student does not exist. Please enter a valid student ID:");
                    studentID = scanny.nextLine();

                    while(!isInteger(studentID))
                    {
                            System.out.println("Please enter a valid student ID in integer form:");
                            studentID = scanny.nextLine();
                    }

                    students = stmt.executeQuery("SELECT * FROM Student WHERE studentID = " + studentID + ";");
            }

            System.out.println("Enter the module ID:");
            String moduleID = scanny.nextLine();

            while(!isInteger(moduleID))
            {
                    System.out.println("Please enter a valid module ID in integer form:");
                    moduleID = scanny.nextLine();
            }

            ResultSet modules = stmt.executeQuery("SELECT * FROM Module WHERE moduleID = " + moduleID + ";");

            while(!modules.next())
            {
                    System.out.println("Module does not exist. Please enter a valid module ID:");
                    moduleID = scanny.nextLine();

                    while(!isInteger(moduleID))
                    {
                            System.out.println("Please enter a valid module ID in integer form:");
                            moduleID = scanny.nextLine();
                    }

                    modules = stmt.executeQuery("SELECT * FROM Module WHERE moduleID = " + moduleID + ";");
            }

            System.out.println("Enter the year in the format 2010-11:");
            String year = scanny.nextLine();

            while(!validYear(year))
            {
                    System.out.println("Please enter a valid year in the form 2010-11:");
                    year = scanny.nextLine();
            }

            System.out.println("Enter the type ID:");
            String typeID = scanny.nextLine();

            while(!isInteger(typeID))
            {
                    System.out.println("Please enter a valid type ID in integer form:");
                    typeID = scanny.nextLine();
            }

            ResultSet types = stmt.executeQuery("SELECT * FROM Type WHERE typeID = " + typeID + ";");

            while(!types.next())
            {
                    System.out.println("Type does not exist. Please enter a valid type ID:");
                    typeID = scanny.nextLine();

                    while(!isInteger(typeID))
                    {
                            System.out.println("Please enter a valid type ID in integer form:");
                            typeID = scanny.nextLine();
                    }

                    types = stmt.executeQuery("SELECT * FROM Type WHERE typeID = " + typeID + ";");
            }

            System.out.println("Enter the mark:");
            String mark = scanny.nextLine();

            while(!isInteger(mark) || Integer.parseInt(mark) < 0 || Integer.parseInt(mark) > 100)
            {
                    System.out.println("Please enter an integer mark between 0 and 100:");
                    mark = scanny.nextLine();
            }

            System.out.println("Enter any notes you wish to make:");
            String notes = scanny.nextLine();

            if(notes.length() > 100)
            {
                    System.out.println("Please do not exceed 100 characters:");
                    notes = scanny.nextLine();
            }

            int n = -1;
            System.out.println("Insert into Marks VALUES ('" + year + "', " + mark + ", '" + notes + "', " + typeID + ", " + moduleID + ", " + studentID + ");");
            n = stmt.executeUpdate("Insert into Marks VALUES ('" + year + "', " + mark + ", '" + notes + "', " + typeID + ", " + moduleID + ", " + studentID + ");");
            System.out.println("Mark recorded");
    }

    public static void newStudent(Connection conn) throws SQLException
    {
            System.out.println("Enter the student's forename:");
            String forename = scanny.nextLine();

            while(forename.trim().isEmpty() || forename.length() > 20 || forename.matches(".*\\d.*"))
            {
                    System.out.println("Forename cannot be null, contain integer values or exceed 20 characters. Please enter it again:");
                    forename = scanny.nextLine();
            }

            System.out.println("Enter the student's family name:");
            String surname = scanny.nextLine();

            while(surname.trim().isEmpty() || surname.length() > 20 || surname.matches(".*\\d.*"))
            {
                    System.out.println("Family name cannot be null, contain integer values or exceed 20 characters. Please enter it again:");
                    surname = scanny.nextLine();
            }

            System.out.println("Enter the student's date of birth in the format YYYY-MM-DD");
            String dob = scanny.nextLine();

            while(!dob.matches("([0-9]{4})-([0-9]{2})-([0-9]{2})") || !vDate(dob))
            {
                    System.out.println("Date of birth must be in the format YYYY-MM-DD, and must not be in the future. Please enter it again:");
                    dob = scanny.nextLine();
            }

            System.out.println("Enter the student's corresponding title ID:");
            String titleID = scanny.nextLine();

            while(!isInteger(titleID))
            {
                    System.out.println("Please enter a valid title ID in integer form:");
                    titleID = scanny.nextLine();
            }

            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM Titles WHERE titleID = " + titleID + ";");


            ResultSet titles = conn.prepareStatement("SELECT * FROM Titles WHERE titleID = " + titleID + ";").executeQuery();

            while(!titles.next())
            {
                    System.out.println("Title does not exist. Please enter a valid title ID:");
                    titleID = scanny.nextLine();

                    while(!isInteger(titleID))
                    {
                            System.out.println("Please enter a valid title ID in integer form:");
                            titleID = scanny.nextLine();
                    }

                    titles = stmt.executeQuery("SELECT * FROM Titles WHERE titleID = " + titleID + ";");
            }

            ResultSet maxID = conn.prepareStatement("SELECT MAX(studentID) FROM Student;").executeQuery();
            int max=0;
            while(maxID.next()) {
            max = maxID.getInt("max");
            }

            stmt = conn.prepareStatement("INSERT INTO Student VALUES(" + (max+1) + ", " + titleID + ", '" + forename + "', '" + surname + "', '" + dob + "');");
            stmt.execute();

            //int n = -1;

            //int Year = Integer.parseInt(dobYear)
            //int Month = Integer.parseInt(month);
            //int Day = Integer.parseInt(day);
            //String dateofBirth = Year+"-"+Month+"-"+Day;
            /*System.out.println("Insert into Student VALUES(" + (max+1) + ", " + titleID + ", '" + forename + "', '" + surname + "', '" + dob + "');");
            stmt.execute("INSERT INTO Student VALUES(" + (max+1) + ", " + titleID + ", '" + forename + "', '" + surname + "', '" + dob + "');");
            stmt.execute("INSERT INTO StudentContact VALUES (" + (max+1) + ", 'student" + (max+1) + "@bham.student.com', 'student" + (max+1) + " street');");
            stmt.execute("INSERT INTO NextOfKinContact VALUES (" + (max+1) + ", 'nextOfKin" + (max+1) + "@bham.kin.com', 'nextOfKin" + (max+1) + " street');");
            /*String SQLcommand1 = "INSERT INTO Student ( studentID, titleID, forename, surname, dob) " +
            					"VALUES ("+ (max+1)+", "+ titleID + ", '" + forename + "', '" + surname + "', '" + dob + "');";*/


            System.out.println("Student added");

    }

    public static void produceTranscript(Connection conn) throws SQLException
    {
            System.out.println("Please enter a student's ID to receive their transcript:");
            String studentID = scanny.nextLine();

            while(!isInteger(studentID))
            {
                    System.out.println("Please enter a valid student ID in integer form:");
                    studentID = scanny.nextLine();
            }

            Statement stmt = conn.createStatement();

            ResultSet students = stmt.executeQuery("SELECT * FROM Student WHERE studentID = " + studentID + ";");

            while(!students.next())
            {
                    System.out.println("Student does not exist. Please enter a valid student ID:");
                    studentID = scanny.nextLine();

                    while(!isInteger(studentID))
                    {
                            System.out.println("Please enter a valid student ID in integer form:");
                            studentID = scanny.nextLine();
                    }

                    students = stmt.executeQuery("SELECT * FROM Student WHERE studentID = " + studentID + ";");
            }

            int titleID = students.getInt("titleID");
            String forename = students.getString("forename").trim();
            String surname = students.getString("familyName").trim();
            String dob = students.getString("dateOfBirth");

            ResultSet titles = stmt.executeQuery("SELECT * FROM Titles WHERE titleID = " + titleID + ";");
            titles.next();
            String title = titles.getString("titleString").trim();

            Statement marksstatement = conn.createStatement();

            ResultSet marks = marksstatement.executeQuery("SELECT * FROM Marks WHERE studentID = " + studentID + ";");
            if(marks.next())
            {
                    String year = marks.getString("year").trim();
                    int moduleID = marks.getInt("moduleID");
                    int mark = marks.getInt("mark");
                    String notes = marks.getString("notes");
                    int typeID = marks.getInt("typeID");

                    ResultSet modules = stmt.executeQuery("SELECT * FROM Module WHERE moduleID = " + moduleID + ";");
                    modules.next();

                    String moduleName = modules.getString("moduleName").trim();

                    ResultSet types = stmt.executeQuery("SELECT * FROM Type WHERE typeID = " + typeID + ";");
                    types.next();

                    String type = types.getString("typeString");

                    System.out.println("Student ID: " + studentID);
                    System.out.println(title + ". " + forename + " " + surname);
                    System.out.println("Born on " + dob);
                    System.out.println();
                    System.out.println(year + ":");
                    System.out.println(moduleName + ", " + moduleID);
                    System.out.println("Mark: " + mark);
                    System.out.println("Type: " + type);

                    while(marks.next())
                    {
                            year = marks.getString("year").trim();
                            moduleID = marks.getInt("moduleID");
                            mark = marks.getInt("mark");
                            notes = marks.getString("notes");
                            typeID = marks.getInt("typeID");

                            modules = stmt.executeQuery("SELECT * FROM Module WHERE moduleID = " + moduleID + ";");
                            modules.next();

                            moduleName = modules.getString("moduleName").trim();

                            types = stmt.executeQuery("SELECT * FROM Type WHERE typeID = " + typeID + ";");
                            types.next();

                            type = types.getString("typeString");

                            System.out.println();
                            System.out.println(year + ":");
                            System.out.println(moduleName + ", " + moduleID);
                            System.out.println("Mark: " + mark);
                            System.out.println("Type: " + type);
                    }
            }
            else
            {
                    System.out.println("Student ID: " + studentID);
                    System.out.println(title + ". " + forename + " " + surname);
                    System.out.println("Born on " + dob);
                    System.out.println();
                    System.out.println("No marks found");
            }
    }
    public static void produceReport(Connection conn) throws SQLException
    {
            System.out.println("Enter a year in the form 2010-11:");
            String year = scanny.nextLine();

            while(!validYear(year))
            {
                    System.out.println("Please enter a valid year in the form 2010-11:");
                    year = scanny.nextLine();
            }

            System.out.println("Enter a module ID:");
            String moduleID = scanny.nextLine();

            while(!isInteger(moduleID))
            {
                    System.out.println("Please enter a valid module ID in integer form:");
                    moduleID = scanny.nextLine();
            }

            Statement mark = conn.createStatement();

            ResultSet marks = mark.executeQuery("SELECT * FROM Marks WHERE year = '" + year + "' AND moduleID = " + moduleID + ";");

            if(!marks.next())
            {
                    System.out.println("No marks exist from the given year and module.");
            }
            else
            {
                    Statement module = conn.createStatement();
                    ResultSet modules = module.executeQuery("SELECT * FROM Module WHERE moduleID = " + moduleID + ";");
                    modules.next();
                    String moduleName = modules.getString("moduleName");
                    String lecturerID = modules.getString("lecturerID");

                    Statement lecturer = conn.createStatement();
                    ResultSet lecturers = lecturer.executeQuery("SELECT * FROM Lecturer WHERE lecturerID = " + lecturerID + ";");
                    lecturers.next();
                    String titleID = lecturers.getString("titleID");
                    String foreName = lecturers.getString("foreName");
                    String familyName = lecturers.getString("familyName");

                    Statement noOfStudent = conn.createStatement();
                    ResultSet noOfStudents = noOfStudent.executeQuery("SELECT COUNT (DISTINCT studentID) FROM Marks WHERE year ='" + year +  "' AND moduleID = " + moduleID + ";");
                    noOfStudents.next();

                    int number = Integer.parseInt(noOfStudents.getString("count"));

                    Statement marksum = conn.createStatement();
                    ResultSet marksums = marksum.executeQuery("SELECT SUM(mark) FROM Marks WHERE year = '" + year + "' AND moduleID = " + moduleID + ";");
                    marksums.next();

                    String sum = marksums.getString("sum");
                    int average = Integer.parseInt(sum) / number;

                    Statement fail = conn.createStatement();
                    ResultSet fails = fail.executeQuery("SELECT COUNT(mark) FROM Marks WHERE year = '" + year + "' AND moduleID = " + moduleID + " AND mark < 40;");
                    fails.next();
                    int failures = Integer.parseInt(fails.getString("count"));

                    float percentage = (((float)failures / (float)number) * 100);

                    Statement title = conn.createStatement();
                    ResultSet titles = title.executeQuery("SELECT * FROM Titles WHERE titleID = " + titleID + ";");
                    titles.next();
                    String lectTitle = titles.getString("titleString");

                    System.out.println();
                    System.out.println("Module ID: " + moduleID);
                    System.out.println("Module name: " + moduleName);
                    System.out.println("Lecturer: " + lectTitle.trim() + ". " + foreName.trim() + " " + familyName.trim());
                    System.out.println("Number of students with marks recorded: " + number);
                    System.out.println("Average mark: " + average);
                    System.out.println("Number of failures: " + failures);
                    System.out.println("Percentage of failures: " + percentage + "%");
                    System.out.println();
            }
    }
}