175|SQL&CHATGPT – Shelter Shenanigans Again

[Millie_K_B]

BYU Student Author: @Millie_K_B
Reviewers: @jimmy_han, @Sterling_Lane
Estimated Time to Solve: 60 Minutes

We provide the solution to this challenge using:

• SQL
• ChatGPT 3.5

Need a program? Click here.

Overview
This challenge is correlated with 173|SQL – Shelter Shenanigans and 171|SQL – Shelter Shenanigans Sequel. It is not necessary to complete either challenge before completing this one, although doing so will increase your familiarity with the database.

You’re still waiting on the call from your dream job and volunteering at the animal shelter in the meantime. During your time there, you’ve noticed that the existing database lacks certain data and functionality. For example, there’s no straightforward way to determine if an animal is a dog, cat, or rabbit. You remember when your boss asked for information on all the rabbits, and you had to sift through the database to find the rabbit breeds and filter the data accordingly. Fortunately, there were only two rabbit breeds, but you shudder to think of doing the same kind of analysis for all the dog breeds.

With these database issues in mind, you write a to-do list and grab a Pepsi. Today, you will add the data needed for future questions you anticipate getting from your boss.

Instructions
We will be adding our own newly generated data to the database. Since this data is randomly generated, your results will vary from the provided solution file, but your database structure should be identical with correctly established relationships. You may use ChatGPT or any other generative AI program. Thiis challenge will reference ChatGPT 3.5 as that is what was used to create the solution. You may also use whatever SQL software you feel comfortable using. If you choose to use something other than Access, there is an Excel spreadsheet provided for you to use.

1. First on the list is sorting all the animals by dog, cat, or rabbit. Since you don’t know which breeds belong to which type of animal, use ChatGPT to generate this information.
   a. You may pick one of two options to include this data in the database. The first option is to create a new table, AnimalBreeds, with all of the breeds and what type of animal each breed is, then use a primary/foreign key set to connect the Animals table with the AnimalBreeds table. The second option is to add a new column to the Animals table to record AnimalType. The solution will show the second option.
   b. There is a breed type, “Mixed Breed,” that will only refer to dogs.
   c. As it turns out, this was incredibly important to accomplish first. A wealthy donor just called the Central location and offered to pay the adoption fees of all the dogs there, and you need to let the donor know how much it will cost them. Use an SQL query to find the sum of all the adoption fees for dogs at the Central location.

2. With that handled, you move to the next task on your list. There currently is no way to track the shelters’ volunteers!
   a. Each location should have at least two assigned volunteers. Available hours should range from 5 to 15 and start dates should fall in the same range as Employee hire dates.
   b. Also create a primary key based on each volunteer’s start date, so that the first volunteer to start has VolunteerID 201. This way, the volunteer IDs will be visibly different from the employee IDs, which start at 101.
   c. Be sure to define relationships between your new Volunteer table and existing tables as needed.

   Generate a volunteer table with 14 rows and the following column names:

   • First_Name
   • Last_Name
   • LocationID
   • Available_Hours
   • Start_Date
   • Phone_Number

3. After adding the Volunteer table, you realize the Employee table does not include phone numbers. Generate the correct number of phone numbers and add a phone number column to the Employee table. Make sure there are no duplicate phone numbers.
   a. If you’d like extra practice, consider what other information would be useful to have in the Employee table, such as wage data or certification types, and generate and add the data. Some of these types of data would be best entered as separate tables, joined to the Employee table with foreign keys. ChatGPT could give you ideas on what information to add.

4. While working with the Employee table, you notice that no one has updated the table since Chloe Martin left.
   a. Remove Chloe Martin’s employee information, making note of her role and LocationID before doing so, as it will be needed for her replacement.
   b. Add a row for her replacement, Janis Morgan. She was hired on March 28, 2024, and her employee ID will be the next sequential number in the employee ID column.

Data Files

• Challenge175_Data.accdb
• Challenge175_Data.xlsx
• Challenge175_Data_Dictionary

Suggestions and Hints

• There are at least three ways to add data to your SQL database: using SQL language (INSERT and DELETE will be helpful), using your SQL program’s built-in tools, or adding data to an Excel spreadsheet and loading the spreadsheet into your SQL program. Any route is acceptable for this challenge, although we encourage you to use SQL language as much as possible.
  NOTE: The solution file does not show the INSERT and DELTE queries for step 4, as Access does not provide an easy way to view those queries after they are run.
• If ChatGPT refuses to provide you with generated phone numbers, ask for a series of 10-digit numbers and then format them as phone numbers.

Solution

ChatGPT Prompts

Here is a link to the ChatGPT prompts as well as screenshots:

Challenge175_Solution.accdb
Solution Video: Challenge 175|SQL&CHATGPT – Shelter Shenanigans Again

[Sterling_Lane]

Time to Complete: 50
Rating: Advanced
This was a fun challenge and revisit to a familiar dataset. I really liked the inclusion of Generative AI as well as practicing with the INSERT and DELETE statements.

Query 1 - Adoption Fees at the Central Location (SQL) - I took the AnimalBreeds table approach for this one.

SELECT SUM(Adoption_Fee) AS TotalCost
FROM Animals INNER JOIN AnimalBreeds ON Animals.Breed = AnimalBreeds.Breed
WHERE LocationID = (SELECT LocationID FROM Location WHERE Location_Name = "Central")
AND AnimalType = "Dog";

Question 2 - Generating the Volunteer Table (GPT 4)

Question 3 - Generating the Phone Numbers (GPT 4)

Question 4 - Updating the Employee Table (SQL)

Deleting Chloe Martin:

DELETE *
FROM Employee
WHERE F_Name = 'Chloe' AND L_Name = 'Martin';

Adding Janis Morgan:

INSERT INTO Employee ( EmployeeID, F_Name, L_Name, Hire_Date, Role, LocationID, Phone_Number )
VALUES (124, 'Janis', 'Morgan', '2024-03-31', 'Vet Tech', 2, '555-123-4567');

[Abraham_Reyes_Leon]

Time to complete: 1:20 minutes
Rating: Advanced
I successfully completed all four steps of the challenge, though there were some difficulties along the way. One of the main challenges was identifying the correct type of animal based on its breed. I had to manually input the column into ChatGPT to make it work. Additionally, since I had never used Microsoft Access to work with tables before, ChatGPT was incredibly helpful in guiding me through the process and understanding the tool’s features. For instance, I didn’t initially know how to access the SQL view, but ChatGPT walked me through the steps to locate and use it effectively. Similarly, when I needed to import the table with the volunteers, I relied on ChatGPT to guide me in using the right tools to successfully add and integrate the tables.