Courtney Ward's Code Samples

SQL Queries

SQL | MySQL

The following SQL queries were created for a database of a competitive swimming organization. The database contains information about swimmers, their caretakers, their participation in events and meets, and their history of performance levels. The queries demonstrate various SQL concepts such as JOINs, GROUP BY, HAVING, CTEs, and EXCEPT.

Query to obtain the names of all primary and secondary caretakers of every swimmer.

SELECT CONCAT(s.fname, ' ', s.lname) AS swimmer, CONCAT(c.fname, ' ', c.lname) AS caretaker, 'primary' AS `type`
FROM swimmer AS s LEFT JOIN caretaker AS c ON (c.CT_Id = s.Main_CT_Id)
UNION
SELECT CONCAT(s.fname, ' ', s.lname) AS swimmer, CONCAT(c.fname, ' ', c.lname) AS caretaker, 'secondary' AS `type`
FROM caretaker AS c RIGHT JOIN othercaretaker AS o USING (CT_Id) LEFT JOIN swimmer AS s USING (SwimmerId)
ORDER BY swimmer;

Query to obtain the names of swimmers who have committed to participating in both a '50M Butterfly' and a '100M Butterfly' event.

WITH t1 AS (SELECT CONCAT(s.FName, ' ', s.LName) AS swimmer, e.Title
FROM swimmer AS s INNER JOIN participation AS p USING(swimmerId) INNER JOIN event AS e USING(eventId)
WHERE p.`Committed` = 1 AND e.Title = '100M Butterfly'),
t2 AS (SELECT CONCAT(s.FName, ' ', s.LName) AS swimmer, e.Title
FROM swimmer AS s INNER JOIN participation AS p USING(swimmerId) INNER JOIN event AS e USING(eventId)
WHERE p.`Committed` = 1 AND e.Title = '50M Butterfly')
SELECT DISTINCT t1.swimmer
FROM t1 INNER JOIN t2 USING (swimmer);

Query to obtain the names of all swimmers who do not have the last name 'Khan' and have participated in three or more events. Include the total numbers of events they each have participated in.

WITH t1 AS (SELECT CONCAT(s.FName, ' ', s.LName) AS Swimmer, s.SwimmerId FROM swimmer AS s WHERE s.lname != 'Khan' )
SELECT t1.Swimmer, COUNT(p.EventId) AS `Number of Events`
FROM participation AS p, t1
WHERE p.SwimmerId = t1.SwimmerId
GROUP BY Swimmer
HAVING COUNT(p.EventId) >= 4;

Query to obtain a list of all swimmers who have participated in three or more events, including a '50M Butterfly' event but not a '200M Freestyle' event.

WITH T1 AS (SELECT CONCAT(s.FName, ' ', s.LName) AS Swimmer, s.SwimmerId, COUNT(p.EventId) AS `Number of Events` FROM participation AS p INNER JOIN swimmer AS s USING (SwimmerId) GROUP BY swimmer HAVING COUNT(p.EventId) >= 3),
T2 AS (SELECT DISTINCT p.SwimmerId, e.Title FROM participation AS p INNER JOIN event AS e USING (EventId) WHERE e.Title = '50M Butterfly'),
T3 AS (SELECT DISTINCT p.SwimmerId, e.Title FROM participation AS p INNER JOIN event AS e USING (EventId) WHERE e.Title = '200M Freestyle')
SELECT DISTINCT T1.Swimmer, T1.`Number of Events` FROM T1 INNER JOIN T2 USING (SwimmerId)
EXCEPT
SELECT DISTINCT T1.Swimmer, T1.`Number of Events` FROM T1 INNER JOIN T3 USING (SwimmerId);

Return to Top

Recursive Descent Parser and Lexical Analyzer

Java

See Project Repository

Select methods from a lexical analyzer and a recursive descent parser created to work with a small mock programming language.

Select helper methods for the Lexical Analyzer

See Full Lexical Analyzer Code


    /*------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    File Handling Methods
    --------------------------------------------------------------------------------
    ------------------------------------------------------------------------------*/

    /*------------------------------------------------------------------------------
    getFileName() - method to get file name
    ------------------------------------------------------------------------------*/
    private String getFileName()
    {
    Scanner myInput = new Scanner(System.in);
    System.out.println("Enter text file's path, including .txt suffix.");
    return myInput.nextLine();
    }

    /*------------------------------------------------------------------------------
    openFile() - method to open file
    ------------------------------------------------------------------------------*/
    private Boolean openFile()
    {
    try
    {
    myCode = new FileReader(getFileName());
    myReader = new BufferedReader(myCode);
    return true;
    }
    catch (FileNotFoundException e)
    {
    System.out.println("File not found.");
    return false;
    }
    }

    /*------------------------------------------------------------------------------
    closeFile() - method to close file
    ------------------------------------------------------------------------------*/
    private void closeFile()
    {
    try
    {
    myReader.close();
    myCode.close();
    System.out.println("\nParse Completed Successfully. \nClosing File.");
    }
    catch (IOException e)
    {
    System.out.println("File cannot be closed.");
    }
    }

    /*------------------------------------------------------------------------------
    --------------------------------------------------------------------------------
    ERROR Method
    --------------------------------------------------------------------------------
    ------------------------------------------------------------------------------*/
    private void ERROR(String errorType, String errorMsg)
    {
    System.out.printf("\n%s on Line %d.\n%s\nCannot complete parse.\n", errorType, lineNum, errorMsg);
    System.exit(0);
    }

See Full Lexical Analyzer Code

Select Helper Methods for the Recursive Descent Parser

See Full Recursive Descent Parser Code


    /*------------------------------------------------------------------------------
    ERROR() - Method for outputting error statements & exiting program
    ------------------------------------------------------------------------------*/
    private static void ERROR(String errorType, String errorMsg)
    {
    System.out.printf("\n%s in Line %d.\n%s\nCannot complete parse.\n", errorType, code.lineNum, errorMsg);
    System.exit(0);
    }

    /*------------------------------------------------------------------------------
    ERROR() - Method for outputting error statements & exiting program
    ------------------------------------------------------------------------------*/
    private static void PARSEERROR(String expectedLex)
    {
    System.out.printf("\nPARSE ERROR in Line %d.\nExpected %s, got '%s'.\nCannot complete parse.\n", code.lineNum, expectedLex, code.lexeme);
    System.exit(0);
    }

    /*------------------------------------------------------------------------------
    isValidID() - Method to check if ID valid:
    Valid/True if in declar section & not exist in table
    Valid/True if in stmt section & does exist in table
    Else, invalid - produces error
    ------------------------------------------------------------------------------*/
    private static boolean isValidID(String ID)
    {
    //Check if ID in table
    //(Designed to only search ID_Table once for efficiency)
    if (ID_Table.contains(ID))
    {
    //If ID exists, only valid in statement section, not declaration section
    if (!isSTMT)
    {
    ERROR("REDECLARATION ERROR", "'" + code.lexeme +
    "' has already been declared. Cannot redeclare it.");
    return false;
    }

    else {return isSTMT;}
    }
    else
    {
    //If ID doesn't exist - only valid if declaration section, not statement
    if (isSTMT)
    {
    ERROR("UNDECLARED IDENTIFIER ERROR", "Cannot use variable '" +
    code.lexeme + "' before it is declared.");
    return false;
    }
    else {return !isSTMT;}
    }
    }

See Full Recursive Descent Parser Code Return to Top

Basic Client-Server Program

C | Linux

See Project Repository

The following two code snippets come from a basic client-server program created in C for Linux. The Client and Server programs run independently and concurrently to perform simple inter-process communication (IPC) using FIFOs/named pipes.

Client Code Snippet

View Full Code

// Loop & Condition control
// Set false by terminate
bool terminate = false;
// Set false by quit
bool activeUser = true;
bool newUser = true;

/* Protocol for Client to Server Msgs */
typedef struct SysCall
{
int clientNum;
int callNum;
int numParam;
int sizeParams[MAX_NUM_PARAM];
char params[MAX_NUM_PARAM][MAX_SIZE_PARAM+1];
}SysCall;

/* Protocol for Server to Client Msgs */
typedef struct RtnVal
{
char msg[300];
}RtnVal;

/* Protocol instances */
struct SysCall nextCall;
struct RtnVal sysResponse;

/* Used to debug/verify system call data being sent */
void printSysCall (SysCall *callToPrint);

int main()
{
/* loop through multiple users until one terminates program */
while (!terminate)
{
// Reset bools for new user
activeUser = true;
newUser = true;

// Update user number (limit pid length to 3 digits to make filename fit char[13])
pid += 1;
if (pid > 999 || pid < 0)
{ pid = 1; }

//Add leading zeros to file name to make number 3 digits
if (pid < 10)
{sprintf(userFifo, "%s%s%d", FIFO, "00", pid);}
else if (pid < 100)
{sprintf(userFifo, "%s%s%d", FIFO, "0", pid);}
else if (pid < 1000)
{sprintf(userFifo, "%s%d", FIFO, pid);}

printf("Preparing for new user.\n\n");
printf("New User FIFO: %s\n", userFifo);

//Open server FIFO in Write mode
writefd = open(FIFO0, O_WRONLY, 0);
printf("Opened server FIFO write Mode.\n");

//Create Own input fifo
mkfifo(userFifo, 420);
printf("Input FIFO created.\n");




// loop menu for single user
while (activeUser)
{
//If new user, set up connection
if(newUser)
{
//Ensure setup happens only once per user
newUser = false;
//Prepare syscall for setup
nextCall.clientNum = pid;
nextCall.callNum = 1;
nextCall.numParam = 1;
nextCall.sizeParams[0] = 13;
for (int i = 0; i < nextCall.sizeParams[0]; i++)
{
nextCall.params[0][i] = userFifo[i];
}

// Provide client fifo address to server via server FIFO
// Show & Send system call struct to server
//printSysCall(&nextCall); //used for debugging
write(writefd, &nextCall, sizeof(struct SysCall));

printf("Opening client FIFO read Mode.\n");
// Will block until Server opens this fifo in write mode
readfd = open(userFifo, O_RDONLY, 0); //wait for server to connect & send
printf("Client FIFO Opened by Server in Write Mode.\nBlock Removed.\n\n\n");
printf("Server connection ready for user input.\n\n");
} //End client setup
// If not new user, ask user what to do
else
{
printf("What would you like to do?\n\n1. Make System Call\n2. Exit\n3. Terminate Server & Exit\n\nEnter Selection Number: ");
scanf("%d", &menuOption);


switch (menuOption)
{
case 1:
printf("Make System Call Selected\n\n");

bool invalidChoice = true;
while (invalidChoice)
{
printf("What System Call do you want to make?\n\n1. Number to Text\n2. Text to Number\n3. Store Data\n4. Recall Data\n\nEnter Selection Number: ");
int choice;
choice = getValidInt(1,4);
if (choice == -1)
{
invalidChoice = true;
}
else
{
nextCall.callNum = choice;
invalidChoice = false;
}
} //End client main system request menu

printf("You entered: %d\n", nextCall.callNum);
// Update call nums menu numbers 1-4 to be 2-5 system call numbers
nextCall.callNum += 1;

/* Skip setting Parameters */
if (nextCall.callNum == 5)
{
nextCall.numParam = 0;
} //Finish no parameter condition

/* Get Parameters */
else
{
invalidChoice = true;
while (invalidChoice)
{
printf("How Many Parameters?\n(Must be less than %d)\n", MAX_NUM_PARAM);
int numP;
numP = getValidInt(0,MAX_NUM_PARAM);
if (numP == -1)
{
invalidChoice = true;
}
else
{
nextCall.numParam = numP;
invalidChoice = false;
}
}

printf("Number of Parameters: %d\n\n", nextCall.numParam);

// n is index for parameter
for (int n = 0; n < nextCall.numParam; n++)

{
printf("Provide Parameter %d:\n(%d characters or less)\n", n + 1, MAX_SIZE_PARAM);
scanf("%25s", nextCall.params[n]);
scanf(CLEARBUFFER); //clear any characters over 25

nextCall.sizeParams[n] = strlen(nextCall.params[n]);
printf("The size of input is %d\n", nextCall.sizeParams[n]);

// m is index for c in parameter string
for(int m=0; m < nextCall.sizeParams[n]; m++)
{
printf("%c", nextCall.params[n][m]);
}
printf("\n");
}
} // finish collect parameter condition

break;

/* Exit */
case 2:
nextCall.callNum = 0;
nextCall.numParam = 0;

// set bool to break inner loop & go to next user
activeUser = false;

break;

/* Terminate */
case 3:
nextCall.callNum = -1;
nextCall.numParam = 0;

// Set bools to break both loops
activeUser = false;
terminate = true;
break;

default:
printf("Invalid menu option.\nReturning to Menu.\n\n");
//discard input up to newline - takes care of removing non int values before next scan
scanf(CLEARBUFFER);
}// end menu switch case
//printSysCall(&nextCall);//used for debugging

// Send system call struct to server
write(writefd, &nextCall, sizeof(struct SysCall));

// Read system response if system call made
if (menuOption == 1)
{
read(readfd, &sysResponse, sizeof(RtnVal));
printf("System Call Result:\n%s\n\n", sysResponse.msg);
}}//end else for non new user
} // end loop for current user System Calls (exit loop on exit or terminate conditions)

View Full Code

Server Code Snippet

View Full Code

/* Protocol for Client to Server Msgs */
typedef struct SysCall
{
int clientNum;
int callNum;
int numParam;
int sizeParams[MAX_NUM_PARAM];
char params[MAX_NUM_PARAM][MAX_SIZE_PARAM+1];
}SysCall;

/* Struct to hold data for current client */
typedef struct client
{
char clientFIFO[13];
int clientPid;
char storedData[MAX_NUM_PARAM][MAX_SIZE_PARAM+1];
int countStoredData;
}client;

/* Protocol for Server to Client Msgs */
typedef struct RtnVal
{
char msg[300];

}RtnVal;

/* Struct Instances */
SysCall currentCall;
client currentClient;
RtnVal sysResponse;

/* Prints formatted system call */
void printSysCall (SysCall *callToPrint);

/* Data & Functions for Number-Word Translations */
char numbers[10][2]={"0","1","2","3","4","5","6","7","8","9"};
char numWords[10][6] = {"zero","one","two","three","four","five","six","seven","eight","nine"};
int wordToNum(char word[]);
int numToWord(char num[]);

int main()
{
//Create well-known/receiving fifo
mkfifo(FIFO0, 420);
printf("Well-known FIFO created.\n");

// Open receiving FIFO in Read mode
// Will block until client opens this fifo in write mode
printf("Opening Well-known FIFO Read Mode.\n");
readfd = open(FIFO0, O_RDONLY, 0);
printf("\nWell-known FIFO Opened by Client in Write Mode.\nBlock Removed.\n\n");

//Loop Control
bool terminate = false;
bool quit = false;

/* Loop through users */
while (!terminate)
{
/* Loop through single user's system calls */
while (!quit)
{
//Read & Display System call
printf("Waiting for Client Input \n\n");
read(readfd, ¤tCall, sizeof(struct SysCall));
printSysCall(¤tCall);

/* Handles all possible system call numbers */
switch (currentCall.callNum)
{
/* Connect server & client */
case 1:
//Get address for client fifo
strcpy(currentClient.clientFIFO, currentCall.params[0]);
currentClient.clientPid = currentCall.clientNum;
// for (int i = 0; i < currentCall.sizeParams[0]; i++)
// {
//     currentClient.clientFIFO[i] = currentCall.params[0][i];
// }
//Open client fifo in write mode
writefd = open(currentClient.clientFIFO, O_WRONLY, 0);
printf("Opened client FIFO write Mode.\n\n");
break;
/* Terminate Server & Client */
case -1:
terminate = true;
/* Quit: Current Client Connection */
case 0:
quit = true;
break;
/* Convert int number 0-9 to English string word for number */
case 2:
int wordIndex = numToWord(currentCall.params[0]);
if (wordIndex == -1)
{
//sysResponse.msgLen = 0;
strcpy(sysResponse.msg, "Error: Provided input was not a number 0-9.\n");
}
else
{
//sysResponse.msgLen = strlen(numWords[wordIndex]);
strcpy(sysResponse.msg, numWords[wordIndex]);

}
write(writefd, &sysResponse, sizeof(RtnVal));
break;
/* Convert English string word for number 0-9 to int number */
case 3:
int numIndex = wordToNum(currentCall.params[0]);
if (numIndex == -1)
{
//sysResponse.msgLen = 0;
strcpy(sysResponse.msg, "Error: Provided input was not the English word for a number 0-9.\n");
}
else
{
//sysResponse.msgLen = strlen(numWords[wordIndex]);
strcpy(sysResponse.msg, numbers[numIndex]);
}
write(writefd, &sysResponse, sizeof(RtnVal));
break;
/* Store Value */
case 4:
currentClient.countStoredData = currentCall.numParam;
strcpy(sysResponse.msg, "The following data was stored: \n");
for (int i=0; i < currentCall.numParam; i++)
{
strcpy(currentClient.storedData[i], currentCall.params[i]);
strcat(sysResponse.msg, currentClient.storedData[i]);
strcat(sysResponse.msg, "\n");
}
write(writefd, &sysResponse, sizeof(RtnVal));
break;

/* Return Stored Value */
case 5:
strcpy(sysResponse.msg, "Retrieved the following data from storage: \n");
for (int i=0; i < currentClient.countStoredData; i++)
{
strcat(sysResponse.msg, currentClient.storedData[i]);
strcat(sysResponse.msg, "\n");
}
currentClient.countStoredData = 0;
printf("Sending data:\n%s\n", sysResponse.msg);
write(writefd, &sysResponse, sizeof(RtnVal));
break;
default:
printf("Invalid system call.");

}// End switch case

}// end quit loop
//Close client fifo
printf("Closing & Deleting client FIFO.\n");
unlink(currentClient.clientFIFO);
quit = false;
} // end terminate loop
// Close server fifo & Terminate server program
printf("Closing & Deleting server FIFO.\n");
unlink(FIFO0);
printf("FIFOs closed & deleted.\nServer program terminating.\n");
return 0;
}

/* Prints to screen formatted system call from struct data */
void printSysCall (SysCall *callToPrint)
{
printf("\n\nSystem Call Received:\n\n");
printf("Client Number: %d\n", callToPrint->clientNum);
printf("Call Number: %d\n", callToPrint->callNum);
printf("Number of Parameters: %d\n", callToPrint->numParam);
for (int i = 0; i < callToPrint->numParam; i++)
{
printf("\nParameter %d\n", i+1);
printf("\tSize: %d   Value: ", callToPrint->sizeParams[i]);
printf("%s\n", callToPrint->params[i]);
printf("\n");
}
printf("\n\n");
}

/*Look up string in number array & return number as int or -1 for error*/
int wordToNum(char word[])
{
for (int i = 0; i < sizeof(numWords); i++)
{
if (strcmp(numWords[i], word) == 0)
{ return i; }
}
{ return -1; }
}

/*Look up integer 0-9 in string form to find & return index of English word for number or -1 for error*/
int numToWord(char num[])
{
for (int i = 0; i < sizeof(numbers); i++)
{
if (strcmp(numbers[i], num) == 0)
{ return i; }
}
{ return -1; }
}

View Full Code

The video below showcase the client and server programs in action.

Return to Top