Challenge Sponsor: @David_Wood
BYU Author: @David_Wood
Estimated Time to Solve: 15 Minutes
This challenge requires you to use an AI chatbot like ChatGPT or Bard.
SWAG Details
A key part of the mission of TechHub.training is to have “A Lot of Fun”. This challenge is designed for you to have fun exploring a new technology and to benefit other university professors.
- Two winners will be selected by June 30, 2023. The winner with the highest number of likes on this website will be chosen, and another participant will be randomly selected. The winning individual, if located within the US, will receive a handcrafted pen created by David Wood via mail. The competition is open to all individuals and not restricted to university professors.
Overview
ChatGPT and other chatbots possess the potential to significantly disrupt various industries, both positively and negatively. In this challenge, our focus lies in exploring how ChatGPT can positively disrupt university accounting education by assisting faculty members in efficiently developing new and interesting materials for their students.
In this challenge, utilize a chatbot to create a vignette that effectively teaches, demonstrates, or tests a crucial accounting concept within your area of accounting expertise. The vignette can be simple or advanced, and it has the flexibility to be either creative and enjoyable or, if one wishes, boring (though it is worth noting that uninteresting submissions will be apparent to everyone!). The most important aspect is that it accurately imparts an accounting concept. Additionally, you are required to generate three questions related to the chosen concept.
Remember to cast your vote for the vignettes that you think are the best!
Please reply using this template:
Topic area: AIS, audit, data analytics, financial, managerial, tax, etc. and then list topic
Class level: Freshman/Sophomore, Junior/Senior, Masters, Ph.D.
Vignette: List it here
3 Questions: List them here (you can use various question types)
Answers to questions: Make sure to hide them using [spoiler] tag
Time to develop: List how long it took to develop your answer
Prompts used: Describe the process you used or list the exact prompts you used.
Example:
Topic area: Accounting Data Analytics: SQL Queries
Class level: Junior/Senior
Vignette:
In a corner of the universe where universities have healthy budgetary allocations and seemingly funny mascots, universities are getting competitive about halftime shows. They are now purchasing bizarre equipment for their mascots to perform outlandishly hilarious skits. The items range from confetti cannons to minuscule bicycles. The following tables provide some insight into the peculiarities of these mascot purchases.
Table Inventory
| inventory_id | item_name | price |
|---|---|---|
| 1 | Confetti Cannon | 200.00 |
| 2 | Mascot Mini-Bike | 450.00 |
| 3 | Giant Prop Book | 150.00 |
| 4 | Banana Peel | 15.00 |
| 5 | Mascot Jetpack | 1200.00 |
Table University
| university_id | university_name | mascot_name |
|---|---|---|
| 1 | Happyville College | Happy Hare |
| 2 | Chuckles University | Chuckling Cheetah |
| 3 | Laughter Institute | Laughing Llama |
| 4 | Hilarious Academy | Hilarious Hippo |
| 5 | Jest Junior College | Jesting Jellyfish |
Table Purchases
| purchase_id | university_id | inventory_id | quantity |
|---|---|---|---|
| 1 | 1 | 1 | 5 |
| 2 | 2 | 3 | 3 |
| 3 | 3 | 4 | 10 |
| 4 | 4 | 2 | 2 |
| 5 | 5 | 5 | 1 |
3 Questions:
Question 1: The Board of University Mascots has asked for a report. They want to know the total amount each university has spent on equipment for their mascots. Write an SQL query to calculate the total amount spent by each university on equipment.
Question 2: The SQL statement below is meant to determine which universities have bought a ‘Banana Peel’, but it returns an error. Identify the two errors in the code and correct them.
SELECT university_name
FROM University
RIGHT JOINS Purchases ON University.university_id = Purchases.university_id
WHERE inventory_id = 'Banana Peel';Question 3: Below is an output table from a SQL query. Which of the following SQL queries generated this output?
| university_name | total_spent |
|---|---|
| Happyville College | 1000.00 |
| Chuckles University | 450.00 |
| Hilarious Academy | 900.00 |
SQL Queries:
a)
SELECT U.university_name, SUM(P.quantity * I.price) as total_spent
FROM University U
INNER JOIN Purchases P ON U.university_id = P.university_id
INNER JOIN Inventory I ON P.inventory_id = I.inventory_id
GROUP BY U.university_name;b)
SELECT U.university_name, SUM(I.price) as total_spent
FROM University U
INNER JOIN Purchases P ON U.university_id = P.university_id
INNER JOIN Inventory I ON P.inventory_id = I.inventory_id
GROUP BY U.university_name;c)
SELECT U.university_name, AVG(P.quantity * I.price) as total_spent
FROM University U
INNER JOIN Purchases P ON U.university_id = P.university_id
INNER JOIN Inventory I ON P.inventory_id = I.inventory_id
GROUP BY U.university_name;d)
SELECT U.university_name, SUM(I.price) as total_spent
FROM University U
LEFT JOIN Purchases P ON U.university_id = P.university_id
LEFT JOIN Inventory I ON P.inventory_id = I.inventory_id
GROUP BY U.university_name;Answers to questions:
Answer 1:
SELECT U.university_name, SUM(P.quantity * I.price) as total_spent
FROM University U
INNER JOIN Purchases P ON U.university_id = P.university_id
INNER JOIN Inventory I ON P.inventory_id = I.inventory_id
GROUP BY U.university_name;
This SQL query performs an INNER JOIN on all three tables using their keys, then groups the results by the university name and calculates the total amount spent by each university on equipment.
Answer 2:
SELECT U.university_name
FROM University U
JOIN Purchases P ON U.university_id = P.university_id
JOIN Inventory I ON P.inventory_id = I.inventory_id
WHERE I.item_name = 'Banana Peel';The two errors in the original code are:
-
RIGHT JOINSis syntactically incorrect. It should beRIGHT JOINor justJOINbecause a right join is not necessary in this scenario. - In the WHERE clause, ‘Banana Peel’ is an item name, not an inventory ID. Hence,
inventory_id = 'Banana Peel'should be replaced withI.item_name = 'Banana Peel'.
Answer 3:
The correct answer is (a)
a) This SQL query correctly calculates the total spent by each university by summing up the product of quantity and price for each item purchased.
b) This query would incorrectly calculate the total amount spent as it does not take into account the quantity of items purchased.
c) This query would give the average amount spent on each item, not the total amount.
d) This query would include universities that haven’t made any purchases in the result set, due to the LEFT JOIN, and it also fails to consider the quantity of purchases.
Time to develop: 10 minutes to write the challenge, 10 minutes to format for posting.
Prompts used:
This is the prompt I used:
You will help me write a very short case about SQL for a student in an accounting data analytics class.
Step 1: Create 3 data tables from a 3NF relational database that has several rows of example data in each table. The tables should be for (1) inventory, (2) university and mascot, and (3) purchases. Make the data purchasing data about funny university mascot purchases. The universities are purchasing equipment to give to their mascots to do halftime skits. Have a 1 paragraph description about the data and situation. Make this paragraph concise and funny. Show the tables with 5 to 7 rows of data each.
Step 2: generate 3 questions using the generated data.
Question 1 should ask the student to write an SQL question querying the data. The question should require joining all three tables, using a where clause, and performing a calculation. Give a scenario that the student must answer.
Question 2 should generate an SQL statement that does not work and ask the student to identify what is wrong with the code and fix it. Have 2 errors in the code. One error should be in the join statement and one error in the where statement. The question should describe the desired outcome in sufficient detail the student can identify the error in the SQL code.
Question 3 should show a table generated from an SQL query. The question should then list 4 different queries and ask the student to select which query produced the output. Only one query should be correct and the other queries should produce something similar to but slightly different than the correct answer.
Step 3: List the answers and a description of why the answer is correct for each question.
Solution
We will post the winners here and on the TechHub.training LinkedIn Page when the challenge ends.
