46|SQL – Barry Bee Benson Co

[Thomas_Giles]

For the first one I fixed it to include LName in the where statement.
The second one I ordered the location to Descending instead of Ascending in the Order by
The final one had Brian left out of his WHERE statement

[Rachel_Cluff]

1. Bradford put LName in the WHERE statement instead of using FName.
2. Bradford sorted the locations in DESC instead of ASC.
3. Bradford did not list Brian in the names, which created a smaller list.

[Tyler_Keller]

Challenge76_Data.xlsx (30.3 KB)

Attached is my file with the original access file solutions adjusted to pull up the queries that Bradford would have run. I found one mistake on all three solutions and was able to adjust them to pull up the same things that Bradford queried.

I really enjoyed this Tech Hub Challenge as it was a good review of trouble shooting SQL code. Thanks for the practice!

[Tyler_Keller]

I like that you provided the solution with the whole code written out. Nice!

[Jeffrey_Bassett]

In the First Problem he accidently set his WHERE to search for Last Names, then he ordered Descending rather than Ascending, and finally there was a mistake in the Omission of Brian.

[Zach_Moffat]

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;

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;

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;

These were my solutions. However, as an SQL beginner, I thought this was super hard. I will continue to practice though!

[Matthew_Lloyd]

1. Last name was used in WHERE statement instead of First Name
2. Location was used in DESC instead of ASC
3. “Brian” was left out of his WHERE Statement

[Nathan_Farrell]

Used Last name instead of first name in Where Statement, sorted locations in Descending instead of Ascending, did not list the name Brian on WHERE Statement… These are all the mistakes that Bradford made

[Jared_Brady]

These are the solutions I got for each of the 3 problems. I really enjoyed going through an SQL function and correcting it. It’s different from what we’ve been doing. It made me realize how much more practice I need in this area.

[Julianne_Goodin]

This problem spotting the errors was really useful for learning SQL!
The first error was that Bradford put LName instead of FName in the WHERE statement.
For the second error, Bradford accidentally sorted the data in DESC order instead of ASC.
For the third error, he didn’t write “Brian” in his WHERE statement.

[Aaron_Gill]

1. In the first query, he used LName in the WHERE statement instead of FName
2. He sorted the list in the wrong way (used DESCwhen he should have used ASC)
3. He left out Brian

[Matthew_Inkley]

1. Instead of using the WHERE statement to find first names to start with a “B”, He searched for last names to start with a “B”. This returned the incorrect number of 40 rows instead of 31.
2. He got the correct number but it was in the opposite order. He set d.Location to DESC.
3. Bradford did not search for “Brian” in the WHERE statement, which only returns the 7 results. There is no Brent Manion in the database.

[Andrew_Winder]

In his first query he must of made the mistake of using Last Name in the WHERE clause instead of using First Name.
His second mistake would be that in the ORDER BY clause he used DESC instead of ASC.
The third query must have been wrong because he forgot to include Brian in the WHERE statement.

[Ryan_OReilly]

1. Bradford put LName in the WHERE clause instead of FName
2. Bradford mixed up ASC and DESC for the Location
3. Bradford forgot to put Brian in the WHERE clause

I enjoyed how we had to figure out what was missing in Bradford’s query it helped me think through the coding more thoroughly. It was a good review for the INNER JOIN clauses and I had forgotten to put in IN after WHERE so that took a little while to figure out on that third point.

[Alyssa_Oldham]

For the first code, Bradford included LName in the WHERE clause instead of FName. With this change, Bradford should only have 31 records with Archibald as the first LName and McDonald as the Last.

For the second, Bradford used DESC in the ORDER BY statement causing the outputs to be put from Z-A rather than alphabetically.

For the final query, Bradford forgot to include “Brian” in his WHERE statement, causing the records to be understated.

By correcting these few errors, Bradford can more accurately pull the data he is looking for.

[Isaac_Cannon]

For the first Query Bradford had put LName in the WHERE statement when it should have been FName.

In the second one, in the ORDER BY statement, the locations are sorted in descending order when it should have been ascending.

In the third query, Bradford left out Brian in the WHERE statement.

[Drew_Warnick]

These are the screenshots I took of the work I performed. I found this assignment interesting and helpful with the LIKE function.

[Hayden_Morrey]

The issue with the first query is that Bradford used the LName attribute in the WHERE clause when he should have used FName

For the second query he ordered location in descending order (DESC) rather than by ascending order (ASC)

The third query had the issue of not including Brian in the WHERE statement which resulted in an incomplete list.

This was a good challenge for me to remember how to join tables together and learn how to use the WHERE clause correctly. Bradford’s common errors were in the WHERE clause which is where I have made errors as I learned SQL coding.

[Keldon_LaRose]

This was a really fun one to do!

1. I noticed he sorted his data by the Last Name and not the First Name. I also got 31 rows when he got 40 because of this
2. He definitely forgot to do the WHERE clause which would factor out locations that didn’t start with B. He shouldnt of gotten Zambia as his first answer. I also only got 14 rows
3. Looks like he didn’t even search for the names of his other two sons Brian and Bruce! I got 29 rows when he said he only got 7

Fun challenge!

[Kate_Debenham]

The problem that Bradford made in the first query is that he used LName in the WHERE statement, rather than FName.

In the second query, Bradford sorted the list DSC rather than ASC.

In the third query, Bradford did not include his son Brian in the WHERE statement.