/*
* File Name -- project.c
* Programmer -- Robert S Laramee
*
* Description -- This program is for the programming project in
* CS775/875. It is the C application which runs
* the 9 queries. Use either the projectdb.c
* or the projectdb.sql files to populate the
* database. They both set up the same tables.
*
* Date Created -- 11 November, 1997
* Date "Completed" -- 10 December, 1997
*/
#include <stdio.h>
#include "libpq-fe.h"
PGconn *connection; /* global variable to reference connection */
void exit_nicely ();
void error (PGresult*, char*);
int main()
{
/* set up postrgres system variables to use postgres defaults */
char *pghost = NULL; /* host name of the backend server */
char *pgport = NULL; /* port of the backend server */
char *pgoptions = NULL; /* options to start up backendserver */
char *pgtty = NULL; /* debugging tty for the backend server */
/* char *dbname = "rlarameedb11"; */
char dbname[25]; /* database name needs to be read in */
PGresult *result;
char* space = " "; /* use as field separator to display tables */
int i, j, nfields, ntuples;
/* for query number 4 */
float total_pay, total_expense, average;
float jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov, dec;
/* for query number 5 */
char January[10], February[10], March[10], April[10], May[10];
char June[10], July[10], August[10], September[10], October[10];
char November[10], December[10];
char query[250];
/* What's the name of the database the user would like to work with? */
printf("\nPlease enter a database name: ");
scanf("%s", dbname);
printf("\nWorking with the %s database.\n", dbname);
/* establish the connection */
connection = PQsetdb (pghost, pgport, pgoptions, pgtty, dbname);
if (PQstatus (connection) == CONNECTION_BAD)
error (result, "connection to database failed");
/* ------------------------------ Begin Queries ---------------------------*/
sprintf(query, "CREATE TABLE NameAndAmount (ssn int4, fname char16,
lname char16, reportName char16, expenseAmount float4,
expenseMonth int4, expenseDay int4, expenseYear
int4);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE NameAndAmount command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO NameAndAmount
SELECT Employee.ssn, fname, lname, reportName,
expenseAmount, expenseMonth, expenseDay,
expenseYear
FROM Employee, ExpenseReport, ExpenseDetail
WHERE Employee.ssn = ExpenseReport.ssn
AND ExpenseReport.reportID = ExpenseDetail.reportID;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "1st INSERT command failed\n");
}
PQclear(result);
sprintf(query, "CREATE FUNCTION add_expense(int4) RETURNS float4
AS 'SELECT SUM(ExpenseAmount) AS expenses
FROM NameAndAmount
WHERE NameAndAmount.ssn = $1'
LANGUAGE 'sql';");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "1st CREATE FUNCTION command failed\n");
}
PQclear(result);
sprintf(query, "CREATE TABLE TotalExpense (ssn int4, fname char16,
lname char16, expenses float4);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE NameAndAmount command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO TotalExpense
SELECT ssn, fname, lname, add_expense(ssn) AS
expenses
FROM NameAndAmount;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "2nd INSERT command failed\n");
}
PQclear(result);
/* ------------------------------ Query #1 --------------------------- */
printf("\n1. Which employee has charged the most expenses (highest total
dollar value)?\n\n");
sprintf(query, "CREATE FUNCTION max_expense() RETURNS float4
AS 'SELECT MAX(expenses)
FROM TotalExpense'
LANGUAGE 'sql';");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE max_expense() command failed\n");
}
PQclear(result);
sprintf(query, "SELECT DISTINCT ssn, fname, lname, expenses
FROM TotalExpense
WHERE max_expense() = expenses;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
for (i=0; i < PQnfields(result); i++) {
printf("%-15s ", PQfname(result, i));
}
printf("\n--------------------------------------------------------\n");
for (i=0; i < PQntuples(result); i++) {
for (j=0; j < PQnfields(result); j++) {
printf("%-15s ", PQgetvalue(result, i, j));
}
printf("\n");
}
PQclear(result);
/* ------------------------------ Query #2 --------------------------- */
printf("\n2. List all the employees whose total expenses were above
x for 1996, where x is entered by the user.\n\n");
printf("Please enter the expense amount of interest for query 2 (e.g.
500):");
/* this will only accept digits and/or a period! */
scanf("%f", &total_expense);
/* total_expense = 100.00; */
if (isalpha(total_expense)) {
printf("\nSorry, that value wasn't recognized.\n");
}
else if ((total_expense >= 0.0) && (total_expense <= 999999)) {
printf("\n The employees with total_expense above $%.2f are:\n",
total_expense);
sprintf(query, "SELECT DISTINCT ssn, fname, lname, expenses
FROM TotalExpense
WHERE expenses > %f;", total_expense);
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "5th SELECT command failed\n");
}
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
for (i=0; i < PQnfields(result); i++) {
printf("%-15s ", PQfname(result, i));
}
printf("\n-------------------------------------------------------\n");
for (i=0; i < PQntuples(result); i++) {
for (j=0; j < PQnfields(result); j++) {
printf("%-15s ", PQgetvalue(result, i, j));
}
printf("\n");
}
PQclear(result);
} else {
printf("\nSorry, that value wasn't recognized.\n\n");
}
/* ------------------------------ Query #3 --------------------------- */
printf("\n3. How much money (total) does the Marketing department owe
due to expenses charged to their department?\n\n");
sprintf(query, "CREATE TABLE DeptExpense(deptCharged char16, paid
char16, expenseAmount float4, expenseDay int4,
expenseMonth int4, expenseYear int4, category
char16);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE DeptExpense command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO DeptExpense
SELECT deptCharged, paid, expenseAmount, expenseDay,
expenseMonth, expenseYear, category
FROM ExpenseReport, ExpenseDetail, ExpenseCategory
WHERE ExpenseReport.reportID = ExpenseDetail.reportID
AND ExpenseDetail.categoryID = ExpenseCategory.categoryID;
");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "INSERT INTO DeptExpense command failed\n");
}
PQclear(result);
sprintf(query, "CREATE FUNCTION total_expense(char16, char16) RETURNS float4
AS 'SELECT SUM(ExpenseAmount)
FROM DeptExpense
WHERE DeptExpense.deptCharged = $1
AND DeptExpense.paid = $2'
LANGUAGE 'sql';");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE total_expense() failed\n");
}
PQclear(result);
sprintf(query, "SELECT DISTINCT deptCharged,
total_expense(deptCharged, paid) AS
total_expenses
FROM DeptExpense
WHERE DeptExpense.deptCharged = 'marketing'
AND DeptExpense.paid = 'no';");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
for (i=0; i < PQnfields(result); i++) {
printf("%-15s ", PQfname(result, i));
}
printf("\n-----------------------------\n");
for (i=0; i < PQntuples(result); i++) {
for (j=0; j < PQnfields(result); j++) {
printf("%-15s ", PQgetvalue(result, i, j));
}
printf("\n");
}
PQclear(result);
/* ------------------------------ Query #4 --------------------------- */
printf("\n4. List the reports submitted between 1 Feb 1996 and 1 Aug
1996.\n\n");
sprintf(query, "SELECT reportName, daySubmitted, monthSubmitted,
yearSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted > 1
AND monthSubmitted < 8
AND yearSubmitted = 1996;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SOME command failed\n");
}
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
for (i=0; i < PQnfields(result); i++) {
printf("%-15s ", PQfname(result, i));
}
printf("\n-----------------------------------------------------------------\n");
for (i=0; i < PQntuples(result); i++) {
for (j=0; j < PQnfields(result); j++) {
printf("%-15s ", PQgetvalue(result, i, j));
}
printf("\n");
}
PQclear(result);
/* ------------------------------ Query #5 --------------------------- */
printf("\n5. In which month were the most expense reports submitted?\n\n");
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 1;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
jan = PQntuples(result);
printf("There were %.0f reports submitted in January.\n", jan);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 2;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
feb = PQntuples(result);
printf("There were %.0f reports submitted in February.\n", feb);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 3;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
mar = PQntuples(result);
printf("There were %.0f reports submitted in March.\n", mar);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 4;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
apr = PQntuples(result);
printf("There were %.0f reports submitted in April.\n", apr);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 5;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
may = PQntuples(result);
printf("There were %.0f reports submitted in May.\n", may);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 6;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
jun = PQntuples(result);
printf("There were %.0f reports submitted in June.\n", jun);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 7;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
jul = PQntuples(result);
printf("There were %.0f reports submitted in July.\n", jul);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 8;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
aug = PQntuples(result);
printf("There were %.0f reports submitted in August.\n", aug);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 9;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
sep = PQntuples(result);
printf("There were %.0f reports submitted in September.\n", sep);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 10;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
oct = PQntuples(result);
printf("There were %.0f reports submitted in October.\n", oct);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 11;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
nov = PQntuples(result);
printf("There were %.0f reports submitted in November.\n", nov);
PQclear(result);
sprintf(query, "SELECT reportName, monthSubmitted, deptCharged
FROM ExpenseReport
WHERE monthSubmitted = 12;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "1st SELECT command failed\n");
}
dec = PQntuples(result);
printf("There were %.0f reports submitted in December.\n\n", dec);
PQclear(result);
/* Feel free to make fun of the following bit of code. */
if (jan>=feb && jan>=mar && jan>=apr && jan>=may && jan>=jun && jan>=jul
&& jan>=aug && jan>=sep && jan>=oct && jan>=nov && jan>=dec) {
printf("January had the most with %.0f reports submitted. \n", jan);
}
if (feb >=jan && feb >=mar && feb >=apr && feb >=may && feb >=jun &&
feb >=jul && feb >=aug && feb >=sep && feb >=oct && feb >=nov &&
feb >=dec) {
printf("January had the most with %.0f reports submitted. \n", jan);
}
if (mar >=jan && mar >=feb && mar >=apr && mar >=may && mar >=jun &&
mar >=jul && mar >=aug && mar >=sep && mar >=oct && mar >=nov &&
mar >=dec) {
printf("February had the most with %.0f reports submitted. \n", feb);
}
if (apr >=jan && apr >=feb && apr >=mar && apr >=may && apr >=jun &&
apr >=jul && apr >=aug && apr >=sep && apr >=oct && apr >=nov &&
apr >=dec) {
printf("March had the most with %.0f reports submitted. \n", mar);
}
if (may >=jan && may >=feb && may >=mar && may >=apr && may >=jun &&
may >=jul && may >=aug && may >=sep && may >=oct && may >=nov &&
may >=dec) {
printf("April had the most with %.0f reports submitted. \n", apr);
}
if (jun >=jan && jun >=feb && jun >=mar && jun >=apr && jun >=may &&
jun >=jul && jun >=aug && jun >=sep && jun >=oct && jun >=nov &&
jun >=dec) {
printf("May had the most with %.0f reports submitted. \n", may);
}
if (jul >=jan && jul >=feb && jul >=mar && jul >=apr && jul >=may &&
jul >=jun && jul >=aug && jul >=sep && jul >=oct && jul >=nov &&
jul >=dec) {
printf("June had the most with %.0f reports submitted. \n", jun);
}
if (aug >=jan && aug >=feb && aug >=mar && aug >=apr && aug >=may &&
aug >=jun && aug >=jul && aug >=sep && aug >=oct && aug >=nov &&
aug >=dec) {
printf("August had the most with %.0f reports submitted. \n", aug);
}
if (sep >=jan && sep >=feb && sep >=mar && sep >=apr && sep >=may &&
sep >=jun && sep >=jul && sep >=aug && sep >=oct && sep >=nov &&
sep >=dec) {
printf("September had the most with %.0f reports submitted. \n", sep);
}
if (oct >=jan && oct >=feb && oct >=mar && oct >=apr && oct >=may &&
oct >=jun && oct >=jul && oct >=aug && oct >=sep && oct >=nov &&
oct >=dec) {
printf("October had the most with %.0f reports submitted. \n", oct);
}
if (nov >=jan && nov >=feb && nov >=mar && nov >=apr && nov >=may &&
nov >=jun && nov >=jul && nov >=aug && nov >=sep && nov >=oct &&
nov >=dec) {
printf("November had the most with %.0f reports submitted. \n", nov);
}
if (dec >=jan && dec >=feb && dec >=mar && dec >=apr && dec >=may &&
dec >=jun && dec >=jul && dec >=aug && dec >=sep && dec >=oct &&
dec >=nov) {
printf("December had the most with %.0f reports submitted. \n", dec);
}
printf("\n");
/* ------------------------------ Query #6 ---------------------------*/
sprintf(query, "CREATE TABLE Dates(ssn int4, fname char16, lname
char16, reportName char16, monthSubmitted int4,
daySubmitted int4, yearSubmitted int4,
expenseMonth int4, expenseDay int4, expenseYear int4);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE Dates command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Dates
SELECT Employee.ssn, Employee.fname, Employee.lname,
ExpenseReport.reportName,
ExpenseReport.monthSubmitted,
ExpenseReport.daySubmitted,
ExpenseReport.yearSubmitted,
ExpenseDetail.expenseMonth,
ExpenseDetail.expenseDay,
ExpenseDetail.expenseYear
FROM Employee, ExpenseReport, ExpenseDetail
WHERE Employee.ssn = ExpenseReport.ssn
AND ExpenseReport.reportID = ExpenseDetail.reportID");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "INSERT INTO Dates command failed\n");
}
PQclear(result);
/* ----------------------------------------------------------------- */
printf("6. During which month did the greatest total amount of expenses
get charged?\n\n");
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 1;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT January command failed\n");
}
strcpy(January, PQgetvalue(result, 0, 0));
jan = atoi(January);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 2;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT February command failed\n");
}
strcpy(February, PQgetvalue(result, 0, 0));
feb = atoi(February);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 3;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT March command failed\n");
}
strcpy(March, PQgetvalue(result, 0, 0));
mar = atoi(March);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 4;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT April command failed\n");
}
strcpy(April, PQgetvalue(result, 0, 0));
apr = atoi(April);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 5;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT May command failed\n");
}
strcpy(May, PQgetvalue(result, 0, 0));
may = atoi(May);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 6;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "INSERT June command failed\n");
}
strcpy(June, PQgetvalue(result, 0, 0));
jun = atoi(June);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 7;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT July command failed\n");
}
strcpy(July, PQgetvalue(result, 0, 0));
jul = atoi(July);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 8;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT August command failed\n");
}
strcpy(August, PQgetvalue(result, 0, 0));
aug = atoi(August);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 9;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT September command failed\n");
}
strcpy(September, PQgetvalue(result, 0, 0));
sep = atoi(September);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 10;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT October command failed\n");
}
strcpy(October, PQgetvalue(result, 0, 0));
oct = atoi(October);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 11;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT November command failed\n");
}
strcpy(November, PQgetvalue(result, 0, 0));
nov = atoi(November);
PQclear(result);
sprintf(query, "SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE expenseMonth = 12;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT December command failed\n");
}
strcpy(December, PQgetvalue(result, 0, 0));
dec = atoi(December);
PQclear(result);
printf("The total for January was $%.2f. \n", jan);
printf("The total for February was $%.2f. \n", feb);
printf("The total for March was $%.2f. \n", mar);
printf("The total for April was $%.2f. \n", apr);
printf("The total for May was $%.2f. \n", may);
printf("The total for June was $%.2f. \n", jun);
printf("The total for July was $%.2f. \n", jul);
printf("The total for August was $%.2f. \n", aug);
printf("The total for September was $%.2f. \n", sep);
printf("The total for October was $%.2f. \n", oct);
printf("The total for November was $%.2f. \n", nov);
printf("The total for December was $%.2f. \n\n", dec);
/* ...and this bit as well */
if (jan>=feb && jan>=mar && jan>=apr && jan>=may && jan>=jun && jan>=jul
&& jan>=aug && jan>=sep && jan>=oct && jan>=nov && jan>=dec) {
printf("January had the greatest total of $%.2f. \n", jan);
}
if (feb >=jan && feb >=mar && feb >=apr && feb >=may && feb >=jun &&
feb >=jul && feb >=aug && feb >=sep && feb >=oct && feb >=nov &&
feb >=dec) {
printf("February had the greatest total of $%.2f. \n", feb);
}
if (mar >=jan && mar >=feb && mar >=apr && mar >=may && mar >=jun &&
mar >=jul && mar >=aug && mar >=sep && mar >=oct && mar >=nov &&
mar >=dec) {
printf("March had the greatest total of $%.2f. \n", mar);
}
if (apr >=jan && apr >=feb && apr >=mar && apr >=may && apr >=jun &&
apr >=jul && apr >=aug && apr >=sep && apr >=oct && apr >=nov &&
apr >=dec) {
printf("April had the greatest total of $%.2f. \n", apr);
}
if (may >=jan && may >=feb && may >=mar && may >=apr && may >=jun &&
may >=jul && may >=aug && may >=sep && may >=oct && may >=nov &&
may >=dec) {
printf("May had the greatest total of $%.2f. \n", may);
}
if (jun >=jan && jun >=feb && jun >=mar && jun >=apr && jun >=may &&
jun >=jul && jun >=aug && jun >=sep && jun >=oct && jun >=nov &&
jun >=dec) {
printf("June had the greatest total of $%.2f. \n", jun);
}
if (jul >=jan && jul >=feb && jul >=mar && jul >=apr && jul >=may &&
jul >=jun && jul >=aug && jul >=sep && jul >=oct && jul >=nov &&
jul >=dec) {
printf("July had the greatest total of $%.2f. \n", jul);
}
if (aug >=jan && aug >=feb && aug >=mar && aug >=apr && aug >=may &&
aug >=jun && aug >=jul && aug >=sep && aug >=oct && aug >=nov &&
aug >=dec) {
printf("August had the greatest total of $%.2f. \n", aug);
}
if (sep >=jan && sep >=feb && sep >=mar && sep >=apr && sep >=may &&
sep >=jun && sep >=jul && sep >=aug && sep >=oct && sep >=nov &&
sep >=dec) {
printf("September had the greatest total of $%.2f. \n", sep);
}
if (oct >=jan && oct >=feb && oct >=mar && oct >=apr && oct >=may &&
oct >=jun && oct >=jul && oct >=aug && oct >=sep && oct >=nov &&
oct >=dec) {
printf("October had the greatest total of $%.2f. \n", oct);
}
if (nov >=jan && nov >=feb && nov >=mar && nov >=apr && nov >=may &&
nov >=jun && nov >=jul && nov >=aug && nov >=sep && nov >=oct &&
nov >=dec) {
printf("November had the greatest total of $%.2f. \n", nov);
}
if (dec >=jan && dec >=feb && dec >=mar && dec >=apr && dec >=may &&
dec >=jun && dec >=jul && dec >=aug && dec >=sep && dec >=oct &&
dec >=nov) {
printf("December had the greatest total of $%.2f. \n", dec);
}
/* ------------------------------ Query #7 ---------------------------*/
printf("\n7. How did it cost to feed all the employees in 1996?\n\n");
sprintf(query, "CREATE FUNCTION sum_meal(int4) RETURNS float4
AS 'SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE categoryID = $1'
LANGUAGE 'sql';");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE sum_meal() failed\n");
}
PQclear(result);
sprintf(query, "SELECT DISTINCT sum_meal(categoryID) AS total_meal
FROM ExpenseDetail
WHERE categoryID = 1;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT command failed\n");
}
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
for (i=0; i < PQnfields(result); i++) {
printf("%-15s ", PQfname(result, i));
}
printf("\n------------------\n");
for (i=0; i < PQntuples(result); i++) {
for (j=0; j < PQnfields(result); j++) {
printf("%-15s ", PQgetvalue(result, i, j));
}
printf("\n");
}
PQclear(result);
/* ------------------------------ Query #8 ---------------------------*/
printf("\n8. How much did the average employee expense in 1996?\n");
sprintf(query, "CREATE FUNCTION add_expense96() RETURNS float4
AS 'SELECT SUM(ExpenseAmount)
FROM ExpenseDetail
WHERE ExpenseYear = 1996'
LANGUAGE 'sql';");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE add_expense96() failed\n");
}
PQclear(result);
sprintf(query, "SELECT DISTINCT add_expense96() AS
total_expense96
FROM ExpenseDetail;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT add_expense96()command failed\n");
}
strcpy(December, PQgetvalue(result, 0, 0));
total_expense = atoi(December);
PQclear(result);
sprintf(query, "SELECT * FROM Employee;");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT * FROM Employee failed\n");
}
average = total_expense/PQntuples(result);
printf("\nThe total expenditure for 1996 was $%.2f.\n", total_expense);
printf("The total number of employees was %d.\n", PQntuples(result));
PQclear(result);
printf("\nTherefore, the average employee expensed $%.2f in 1996.\n",
average);
/* ------------------------------ Query #9 ---------------------------*/
printf("\n9. How much did the males spend verses the females in 1996?\n");
sprintf(query, "CREATE TABLE Gender(gender char16, expenseAmount float4,
expenseYear int4);");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE Gender command failed\n");
}
PQclear(result);
sprintf(query, "INSERT INTO Gender
SELECT Employee.gender, ExpenseDetail.expenseAmount,
ExpenseDetail.expenseYear
FROM Employee, ExpenseDetail
WHERE Employee.ssn = ExpenseReport.ssn
AND ExpenseReport.reportID = ExpenseDetail.reportID");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "INSERT INTO Gender command failed\n");
}
PQclear(result);
sprintf(query, "CREATE FUNCTION add_male(char16) RETURNS float4
AS 'SELECT SUM(ExpenseAmount)
FROM Gender
WHERE Gender = $1'
LANGUAGE 'sql';");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "CREATE add_expense96() failed\n");
}
PQclear(result);
sprintf(query, "SELECT DISTINCT add_male(gender) AS
total_male
FROM Gender
WHERE gender = 'm';");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT add_male()command failed\n");
}
strcpy(December, PQgetvalue(result, 0, 0));
total_expense = atoi(December);
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
PQclear(result);
sprintf(query, "SELECT DISTINCT add_male(gender) AS
total_female
FROM Gender
WHERE gender = 'f';");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_TUPLES_OK) {
error (result, "SELECT add_male()command failed\n");
}
strcpy(November, PQgetvalue(result, 0, 0));
average = atoi(November);
PQdisplayTuples(result, stdout, 1, space, 1, 1); printf("\n");
PQclear(result);
printf("\nThe males spent $%.2f and the females spent $%.2f.\n",
total_expense, average);
/* ------------------------------ End of Queries ---------------------------*/
sprintf(query, "DROP TABLE NameAndAmount;
DROP TABLE TotalExpense;
DROP TABLE DeptExpense;
DROP TABLE Dates;
DROP TABLE Gender;
");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "DROP TABLE command failed\n");
}
PQclear(result);
sprintf(query, "DROP FUNCTION add_expense(int4);
DROP FUNCTION max_expense();
DROP FUNCTION total_expense(char16, char16);
DROP FUNCTION sum_meal(int4);
DROP FUNCTION add_expense96();
DROP FUNCTION add_male(char16);
");
result = PQexec (connection, query);
if (PQresultStatus (result) != PGRES_COMMAND_OK) {
error (result, "DROP FUNCTION command failed\n");
}
PQclear(result);
/* close connection to database */
exit_nicely (1);
}
/*
* Procedure: error
* Description: print error message, release query result, and terminate
* program using postgres functions PQprintMessage and PQclear
*
* Input: result -- postgres query result
* message -- print string
*
* Side Effects: alters stdout output stream
* invokes exit_nicely to quit, does not return to caller,
*/
void error (PGresult* result, char* message)
{
fprintf (stderr, "%s", PQerrorMessage(connection));
fprintf (stderr, "%s", message);
PQclear (result);
exit_nicely ();
}
/*
* Procedure: exit_nicely
* Description: disconnects from postgres database using
* postgres function PQfinish
*
* Input: global variable connection
*
* Side Effects: alters global variable conn
*/
void exit_nicely()
{
PQfinish(connection);
exit(1);
}