public static DataTable selectEducationLevels()
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM Education");
return dt;
}
public static DataTable selectOccupationClusterId(int Id)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT ClusterId FROM occupation WHERE Id = " + Id + "");
return dt;
}
public static DataTable selectStudents(string group)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT s.studentId, s.FirstName, s.LastName, s.MarriedTo, " +
"s.Sex, s.GPA, o.Occupation, s.Salary, s.NetMonthlyIncome, m.MaritalStatus, " +
"s.ChildrenNumber, s.childSupport, s.CreditScore " +
"FROM student s, schools sch, occupation o, education e, credit c, MaritalStatus m " +
"WHERE c.creditId = s.creditUseId AND e.EducationId = s.EducationId AND m.Id = s.MaritalStatusId " +
"AND o.Id = s.occupationId AND sch.SchoolId = s.schoolId AND s.studentGroup = '" + group + "'");
return dt;
}
public static DataTable selectCreditUse()
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM credit");
return dt;
}
public static DataTable selectSchools()
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM schools ORDER BY schoolName");
return dt;
}
public static DataTable selectStudent(int studentId)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM student WHERE studentId = " + studentId + "");
return dt;
}
public static DataTable selectNumberofDivorcedStudentsWithChildren(string group)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT COUNT(maritalstatusId)*.6 AS number FROM student WHERE maritalstatusId = 5 AND studentGroup = '" + group + "'");
return dt;
}
public static DataTable selectSalary(int occupationId)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT annualGrossSalary FROM occupation WHERE Id = " + occupationId + "");
return dt;
}
public static DataTable selectNextOccupationId()
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT ID FROM occupation ORDER BY ID desc LIMIT 1");
return dt;
}
public static DataTable selectNextSchoolId()
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT SchoolId FROM Schools ORDER BY SchoolId desc LIMIT 1");
return dt;
}
public static DataTable selectMarriedMales(string group)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM Student WHERE StudentGroup = '" + group + "' AND MaritalStatusID = 3 AND Sex = 'Male' ORDER BY gpa DESC");
return dt;
}
public static DataTable selectMaricalStatusIds()
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM MaritalStatus");
return dt;
}
public static DataTable selectMaleDesireChildren(int id)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT ChildrenNumber FROM Student WHERE StudentId = " + id + "");
return dt;
}
public static DataTable selectOccupations()
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM occupation");
return dt;
}
public static DataTable selectNumberofDivorcedWithChildrenMales(string group)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT COUNT(*) AS number FROM student WHERE maritalstatusid = 4 AND sex = 'Male' AND studentgroup = '" + group + "'");
return dt;
}
public static DataTable selectOccupationsByCluster(int Id)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM occupation WHERE clusterid = " + Id + " ORDER BY annualgrosssalary DESC");
return dt;
}
public static DataTable selectNumberOfMalesYes(string group)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT COUNT(*) AS maleYes FROM Student WHERE StudentGroup = '" + group + "' AND Sex = 'Male' AND married = 'Yes'");
return dt;
}
public static DataTable selectSchool(int schoolId)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT schoolName FROM schools WHERE schoolId = " + schoolId + "");
return dt;
}
public static DataTable selectNumberOfSingleStudents(string group)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT COUNT(*) AS single FROM Student WHERE StudentGroup = '" + group + "' AND MaritalStatusId != 3");
return dt;
}
public static DataTable selectSingleAfterTaxes(int occupationId)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT SingleAfterTaxes FROM occupation WHERE Id = " + occupationId + "");
return dt;
}
public static DataTable selectNumberOfStudents(string group)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT COUNT(*) AS studentNo FROM Student WHERE StudentGroup = '" + group + "'");
return dt;
}
public static DataTable selectStudentLoans(int occupationId)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT studentLoans FROM occupation WHERE Id = " + occupationId + "");
return dt;
}
public static DataTable selectNumberOfStudentsNo(string group)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT COUNT(*) AS [No] FROM Student WHERE StudentGroup = '" + group + "' AND Married = 'No'");
return dt;
}
public static DataTable selectClusters()
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM cluster");
return dt;
}
public static DataTable selectOccupation(int id)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM occupation WHERE Id = " + id + "");
return dt;
}
public static DataTable selectDivorcedStudentsWithChildren(string group, int number)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT * FROM Student WHERE StudentGroup = '" + group + "' AND MaritalStatusId = 5 LIMIT " + number + "");
return dt;
}
public static DataTable selectEducationLevel(int occupationId)
{
DB d1 = new DB();
DataTable dt = d1.Select("SELECT RequiredEducationLevelId FROM Occupation WHERE Id = " + occupationId + "");
return dt;
}
