BYU Student Author: @Boston
Reviewers: @Alex_Garrett, @Christian
Estimated Time to Solve: 20 Minutes
We provide the solution to this challenge using:
Need a program? Click here.
Overview
Barry Bee Benson Company has a weird obsession with the letter “B”. One of your colleagues, Bradford Benson II, was tasked with pulling up some data for the CEO, Barry Bee Benson IV, to look at. He is worried he might have done his SQL queries wrong and wants you to check his work. If he has made a mistake, try and find the reason where Bradford went wrong. Here are the queries along with the number of records, and the first and last records that Bradford got with his queries. Good luck!
Instructions
- Barry wants to see the employees’ first names that start with the letter “B” and that have taken over 77 trips (Barry is also a Luka Doncic fan
). He also wants to see the breakdown by expense purpose and sorted by LName (last name) first, then ExpPurpose both alphabetically and lastly by the number of trips from greatest to least. Use the following headers: FName, LName, ExpPurposeDescr, NumOfTrips.
- Bradford got 40 rows, with Barbeau as the first LName, and Butler as the last LName in his query.
- Next, he would like to see the locations that contain the letter “B” and the total amounts spent on trips to these locations. Order by Location alphabetically and then by the sum from greatest to least. Use the following headers: Location, TotalAmount.
- Bradford got 30 rows, with Zambia as the first Location and Abu Dhabi as the last Location in his query.
- Lastly, Barry would like to see the employees that share the same names as his three sons, Brian, Brent and Bruce, and their titles. Order by FName descending, LName descending, and then by title ascending. Use the following headers: FName, LName, Title. (There may be some duplicate records; that is okay just include them in your solution)
- Bradford got 7 rows, with Brent Manion as the first employee and Brent Herbert-Copley as the last employee in his query.
Data Files
Suggestions and Hints
Be careful with parenthesis with nested INNER JOINs.
The key word LIKE can be helpful to find certain words. The “” can be used as a ‘wild’ character to return any number of characters before or after the “”.
Aggregate functions can be filtered using a HAVING statement after the GROUP BY statement.
Solution
For the first one, he used Last Name in the WHERE field, not First Name, which brought him 40 rows, with Barbeau as the first name. I changed it in the first query to show that.
For the second one, he just made d.Location DESC in the ORDER BY column.
For the final one, he just forget to include Brian in the WHERE statement, which lowered the row number to 7. I tried to attach the doc, but access wasn’t a file formant available to be uploaded. So this is my written solution.
9 Likes
From my understanding of the problem, all we need to do is find the mistakes Bradford made, not write correct code. So I used the Solution Queries in the file and just edited them to be how he did them. Also, does part 3 of the problem have a typo when it says “Brent Manion”?
- Bradford included LName in the WHERE statement.
- Bradford ordered d.Location as DESC instead of ASC.
- Bradford left “Brian” out of his WHERE statement.
13 Likes
In the first query, Bradford mistakenly put LName instead of FName in the WHERE statement.
In the second query, he was mistaken because he sorted the locations in descending order instead of ascending.
In the last query, Bradford forgot to list the name Brian, resulting in a smaller list than anticipated.
3 Likes
In the first query Bradford must have put last name instead of first name in the WHERE statement that would have given him the extra rows.
In the second query he must have forgotten to ORDER BY Location in ascending order not descending order.
In the third one he Left Brian out of his Where statement.
8 Likes
First query - Bradford put “last name” instead of “first name” in the WHERE statement
Second query - Bradford put ORDER BY in descending order instead of ascending
Third query - Bradford didn’t list “Brian” as a name
Fun little challenge!
For the first query, Bradford used LName in the WHERE statement instead of FName
For the second, Bradford just sorted the list in descending order by location instead of ascending
For the third, Bradford forgot to include the name ‘Brian’ in his WHERE statement
I wrote the queries with the changes to get those solutions, but it won’t let me upload my access document.
3 Likes
-
Using the statement WHERE e.LName LIKE “B” returns 40 results. He should have used the WHERE statement for first name, not lost name.
-
Using the statement ORDER BY d.Location DESC makes the query sort in reverse alphabetical order. He should have not included the DESC because SQL automatically sorts in ascending order.
-
Lastly, the WHERE statement that he wrote was "WHERE e.FName LIKE “Brent*” OR e.FName LIKE “Bruce*”. He forgot to include Brian in this WHERE statement.
I got a little confused because I believe there is a typo in the instructions. It should say that Bruce Manion is the first employee, not Brent Manion. I had to look at the solution to figure this one out.
Very good challenge to catch small errors!
3 Likes
This was a great challenge! It really helped me understand the intricacies of SQL. I was able to find three errors. First, Bradford put LName in the WHERE statement instead of FName. Second, he needed to sort by ASC not DESC. And lastly, he did not put the name “Brian” in the list.
1 Like
Thanks for the challenge!
- Bradford should have put FName in the WHERE statement instead of LName
- Bradford should have put the list in ascending order by location instead of it being in descending order
- Bradford didn’t put the name Brian in the WHERE statement
3 Likes
I noticed that in the first query, Bradford used LName in the WHERE statement instead of FName,this should have been done for First name.
In the second mistake, Bradford simply sorted the list in descending order by location instead of ascending
The third mistake was that Bradford didn’t include ‘Brian’ in his WHERE statement.
4 Likes
FName should be in the WHERE Statement not LName
List should be in ascending order not descending
“Brian” is not in the WHERE Statement
I realized I was not as familiar with SQL as I thought!
- Bradford used LName in the WHERE field instead of FName.
- Bradford incorrectly set up his table by throwing a DESC command at d.Location.
- Bradford left “Brian” out of his query.
1 Like
This challenge really helped me learn how to analyze SQL statements and will help me to remember to read the details when I write my own.
- Bradford put last name instead of first name in his WHERE statement.
- He accidentally forgot that his ORDER BY statement needed to be in ascending.
- He left “Brian” out of his WHERE statement.
Thanks!
For the first one, he used LName in the WHERE segment instead of FName.
For the second one, he put DESC instead ASC when ordering the data.
For the last one, he tragically left out his son Brain, neglecting a large portion of the data.
1 Like
For the first query, Bradford put the LName in the WHERE statement instead of using FName.
For the second query, Bradford ordered the location by descending instead of alphabetically.
For the last query, Bradford only included matches for the names Brent and Bruce and not for the name Brian.
1 Like
This was a great way for me to learn to read the SQL code and play around with how crucial it is to be specific in your code.
Solution 1: To separate the rows we use the where function. He had incorrectly used the WHERE clause to filter the LName instead of the FName (since he wanted the first name to start wth “B”)
Solution 2: When you are using the ORDER BY clause the defult function is the ASC which is A-Z he did DESC which is Z-A
Solution 3: He just forgot to add brian in the WHERE clause just need to add him like he added the other two then brian will be there and have 29 rows now
