package com.roshka.jdbc;

import java.sql.*;


public class Q10 {

	public static void main( String [] args ) {
		String url = "jdbc:postgresql://localhost:5433/testdb";
		String user = "test";
		String password = "pqntslc";
		try {
			/* QUERY
			 * select distinct fullname 
			 * from ( (enrollments en 
			 * left join students st)
			 * on en.student_iid = st.student_iid) as en_st
			 * left join courses co 
			 * on en_st.course_iid = co.course_iid) as en_st_co
			 * where en_st_co.code = 'a1010';
			 *  */ 
			
			Connection connection = DriverManager.getConnection(url, user,password);
			System.out.println("estoy conectado a la base de datos");
			Statement st = connection.createStatement();
			String query = "SELECT DISTINCT fullname "
							+ "FROM ((enrollments en "
							+ "LEFT JOIN students st "
							+ "ON en.student_iid = st.student_iid) AS en_st "
							+ "LEFT JOIN courses co "
							+ "ON en_st.course_iid = co.course_iid) AS en_st_co "
							+ "WHERE en_st_co.code = 'a1010';";
			
			ResultSet rs = st.executeQuery(query);
			while(rs.next() ) {
				String fullname = rs.getString("fullname");
				System.out.println(fullname);
			}
		}catch(SQLException e) {
			e.printStackTrace();;
		}
	}
}