46|SQL – Barry Bee Benson Co

Challenges
, , ,
41

I was a little confused at first because my code seemed to be right, and then I realized I was supposed to work backwards to find Bradford’s mistakes. But his mistakes were…

  1. Bradford likely put LName in his where statement which caused it to return last names starting with “B” instead of first names.
  2. Bradford accidently had the locations in descending order instead of leaving it in ascending order.
  3. Bradford most likely forgot to include Brian’s name in his LIKE clause
1 Like
42

Errors made by Bradford include employing “LName” in the WHERE statement in the first query, reversing the data sorting order in the second query, and neglecting to include “Brian” in the WHERE clause search in the third query.

I found this challenge to be very useful as I am always having to troubleshoot and look for problems in my SQL code

1 Like
43
  1. He used Lname instead of Fname in the WHERE line which incorrectly weeded out people whose last names started with a B.
  2. In this problem he ordered location in descending order which is what caused Z to be first and A to be last.
  3. In the WHERE line he left out the name Brian which left out a lot of the names that Barry wanted to see.

Fun problem to be on the other side of SQL and trying to figure out how problems arise with code errors. Definitely helped me make more sense of how to identify problems with query lines of code.

1 Like
44
  1. Bradford was supposed to include FName for the WHERE (he used LName instead, which caused it to be filtered by LName instead of FName.
  2. Bradford added a DESC in the ORDER BY for d.location.
  3. Since there is no Brent Manion in the database, it is hard to determine what Bradford did wrong. Assuming that it was actually Bruce Manion at the top of the results, Bradford just forgot to add e.FName LIKE “Brian*” in the WHERE section.
1 Like
45

First Mistake, Bradford used LName instead of FName in the WHERE statement.

Second, he used DESC in the ORDER BY statement.

Third, He must have forgotten to add a search for “Brian” which means he was only getting results for Brent and Bruce.

1 Like
46

This was a great challenge, and I feel like it really helped me to learn about the INNER JOIN function and get better at using it.
Here’s my solution:

  1. Bradford included LName in the WHERE statement when he didn’t need to because the purpose was to only see employees who’s first names started with B.
  2. He just made the ORDER BY function go the wrong way. It needed to be ASC
  3. Bradford forgot to include Brian in his statement, and there were a lot of Brians!

Thanks for the challenge!

2 Likes
47

In the first query, he used LName instead of FName if the WHERE statement
In the second query, he ordered the data in the opposite way
In the third query, he forgot to include Brian in the WHERE statement

This was a great challenge! It helped me learn to use different SQL functions and learn how to check my work efficiently

1 Like
48

For this challenge, I rewrote SQL code that could be used to complete his tasks. I ended up with mostly the same things as the “Solution” tabs, but in the last scenario I used the “IN” command instead of a few “LIKE / OR” commands. It was nice to be able to check my work side-by-side like that, especially when I got stuck!

  1. Bradford seems to have sorted by “LName” instead of “FName”.
  2. Bradford used the default sort, ascending, instead of specifying “DESC”.
  3. Bradford likely omitted “Brian” in his WHERE statement.
1 Like
49

  1. Instead of searching for First Names, he used Last Name which threw off the results. To resolve the issue, use First name instead!

  2. He ordered the location as descending instead of ascending.

  3. He forgot to search for Brian in the where statement.

I can’t attach an Access file.

1 Like
50

Here is my solution!
1: change FName to LName in the WHERE
2: DESC instead of ASC
3: Put Brian in the WHERE

1 Like
51

For the first instruction, Bradford Benson II put LName in the WHERE clause instead of FName.

For the second instruction, Bradford input “ORDER BY d.Location DESC” instead of “ORDER BY d.Location” which is why he got Zambia as the first location.

For the third instruction, Bradford forgot to add e.FName LIKE “Brian*” which is why only 7 results appeared.

1 Like
52

It looks like, instead of using the first name in the WHERE statement, they used LName. The next mistake was that the location was sorted descending when it should have been ascending. Lastly, Brian was excluded from the WHERE statement.

1 Like
53
  1. In the WHERE clause, he used LName when he should have used FName. This returned the wrong results.
  2. He ordered the results in descending order instead of ascending order. This returned the correct results but they were sorted incorrectly.
  3. He did not include Brian in his search. This left out people that should have been included in his query results.
1 Like
54
  1. On the first query, Bradford included “e.LastName” in his WHERE clause, but his boss wanted the query to return employees with first names that start with a B. This happened to return a few more rows than the correct query, but most of the information is completely different across the two results.
  2. On this query, Bradford added “DESC” in his ORDER BY clause after “d.Location”. He could have simply left “d.Location” alone.
  3. I am assuming that Bradford’s first row in the table says “Bruce Manion”. In this case, Bradford excluded "WHERE e.FName LIKE “Brian”.If one assumes based on the prompt that both “Bruce” and “Brian” were excluded, the query only returns 4 rows.
1 Like
55
  1. LName was used instead of FName so he got all the employees with last names starting with B instead of first name.
  2. He should have sorted the Locations as DESC instead of ASC
  3. Did not include Brian in Where statement
1 Like
56

In the first situation, he put Lname in the WHERE statement, which is why Barbeau showed up first. Because he wants to see first names that start with B though, the Where statement should have e.FName that we’re searching for starting with the letter B.
In the second situation, Zambia shows up first because he sorted location by DESC order originally. When you fix that, it brings up Abu Dhabi first instead of last.
For the last situation, I originally got 29 records, and to get just 7 records you leave out Brian in the WHERE statement.

1 Like
57

I found that in the first problem he used LName instead of FName which affected the results that he received.
For the second one he should have left off DESC or typed ASC to get it sorted alphabetically.
In the last problem he simply left out Brian from his WHERE statement which led to him only getting the seven results.

1 Like
58

For the first one Bradford used LName in the WHERE statement instead of FName
For the second one he used DESC for d.Location instead of ASC
For the third one he forgot to include Brian in the WHERE statement

1 Like
59

The mistakes the Bradford made were the following:

  1. He put Lname in his WHERE statement, when it should not have been used.
  2. He sorted using DESC instead of ASC
  3. He did not include Brian in the WHERE statement
1 Like
60

My solutions

Solution 1: SELECT e.FName, e.LName, ep.ExpPurposeDescr, COUNT(t.TripID) AS NumOfTrips
FROM ((Employee AS e INNER JOIN Trip AS t ON e.EmpID=t.EmpID) INNER JOIN TripDetail AS td ON t.TripID=td.TripID) INNER JOIN ExpPurpose AS ep ON td.ExpPurposeID=ep.ExpPurposeID
WHERE e.FName LIKE “B*”
GROUP BY e.FName, e.LName, ep.ExpPurposeDescr
HAVING COUNT (t.TripID) > 77
ORDER BY e.LName, ep.ExpPurposeDescr, COUNT(t.TripID) DESC;

Solution 2: SELECT d.Location, SUM(td.Amount) AS TotalAmount
FROM (Destination AS d INNER JOIN Trip AS t ON d.DestinationID=t.DestinationID) INNER JOIN TripDetail AS td ON t.TripID=td.TripID
WHERE d.Location LIKE “B”
GROUP BY d.Location
ORDER BY d.Location, SUM(td.Amount) DESC;

Solution 3: SELECT e.FName, e.LName, t.Title
FROM Employee AS e INNER JOIN Title AS t ON e.TitleID=t.TitleID
WHERE e.FName LIKE “Brian*” OR e.FName LIKE “Bruce*” OR e.FName LIKE “Brent*”
ORDER BY e.FName DESC , e.LName DESC , t.Title;

3 Likes