Week 6 - querious questions
This week we went back to SQL with full steam. There were your run of the mill MIN() and MAX() type queries, but also a lot of sub-queries that took me a bit to figure out. The syntax that SQL wants for sub queries threw me off a bit but it seems like you can really do a lot stringing things together. You can also write things that may seem nice to you but turn out to look absolutely terrible. So the big take away for me was to not make a mess, like with any software project. Nobody cares how smart you think and clever you think you are if your query, script, program, whatever looks like junk. It's just not useful.
Here is an example from the homework that I like, for figuring out who the best student was in a given course code.
You could write that query a lot of ways, including one giant line that is a big hassle to read. But as written above it is clear what is going on. We are interested in the last name of the student we will call the best student. Their name is in the student table but the grades are in the enrollment table so we will join those tables together. The unique identifier of interest is the student number so use that for the "on" condition. To round it all out we are looking for the best grade, that's where the sub-query formatting comes in. It is broken into "where a > b" but we can expand the b part out by putting parenthesis around it. That lets us chill out and start over from the beginning with a query that figures out what the max grade was for class # 10014. When it all runs then we end up with the student with the highest grade from class 10014.
I still don't know what I'm doing as far as SQL goes but at least I know what to look for and the structure of it. To really be able to get much done I think you need a lot of weeks of repetition on real world problems where you fiddle around for a while. It is clear that sub-queries really let you start doing useful work instead of just finding simple pieces of information.
No comments:
Post a Comment