SQL Help

[Lil Frier]

So I am taking a database class this semester, and we're working on our first assignment legitimately related to running queries. I've managed the first 9 queries pretty easily, but the last one has had me stumped for the last hour or two, despite consulting the text and the Internet in general. I'm hoping I can ask my question directly here and get some assistance.

Sorry, for some slightly awkward formatting result, but this is the table the question relates to:

BRANCH_NAME BRANCH_CITY ASSETS
--------------- --------------- ----------
Downtown Brooklyn 900000
Redwood Palo Alto 2100000
Perryridge Horseneck 1700000
Mianus Horseneck 400200
Round Hill Horseneck 8000000
Pownal Bennington 400000
North Town Rye 3700000
Brighton Brooklyn 7000000
Central Rye 400280

The question asks us for the BRANCH_NAME with the most and fewest ASSETS. I can run the MIN() and MAX() functions and get the numbers, and I can do a query based on the minimum and maximum numbers. However, the question requires us to pull the BRANCH_NAME while running the aggregate function(s), and I've not figured out a way to do so.

So, if anyone can give me a hand here, I'd be very appreciative for the assist.

 

[Ken g6]

My SQLite test tells me using MAX in the WHERE clause is an error. Have they taught you subqueries yet?

 

[Lil Frier]

See, that's the stupid part of it. Our course syllabus doesn't have us doing the book chapters on queries for 3 more weeks. Why my teacher expects us to know and handle these things on our own already, I'm not quite sure. More over, if he DID expect us to know this already, I don't know why we're going to go over it in 3 weeks.

So, I don't know about sub-queries. I've not actually been taught about any of it, which is probably why I'm having a rough go with it. Oh, and I had the same issue in trying that solution on my end--you get told something to the effect of not being able to put a "group function" there. I attempted to use grouping, but that just gave the min/max for each branch (which all have just one entry, making that pointless).

 

[Ken g6]

Sounds like you should ask your teacher about this. Does he have office hours?

It seems like the first thing my DB class went over was some kind of mathematical logic. Maybe the goal is to express the queries that way?

 

[Lil Frier]

Oh, I definitely intend to ask him about it. My teachers are good with the office hours thing, this assignment's just do tonight, and I didn't have time to get to it until yesterday, so didn't get to talk to him about it beforehand. Plus, the class only meets in-person once every 2 weeks, and we've not done ANY query-related discussion in-class so far (we're in the 5th week). I figured I'd just ask online before I give up banging my head at it. I'll have to head in Monday afternoon and see if he's around.

Thanks for at least responding and giving me some feedback on it.

 

[Ken g6]

I have to ask, what have they been discussing in class for five weeks without discussing queries?

Anyway, look at subqueries. They're easy, and should do what you want.

 

[kn51]

Yeah, needs sub-selects from the information provided.

Is the expected result set one row or two? Doesn't really matter, you'd still need sub-selects.

Granted, I'm in SQL Server land, but when doing aggregate functions such as min and max you need the group by clause. Now you could easily cheat since you know the dataset and by using the HAVING clause...but that doesn't work out well in the real world. In other words, two selects with a having clause combined with a UNION would be the el-cheapo solution. Your instructor if worth his salt would appreciate the ingenuity of the solution*.

The way that question is worded is shall we say way out there in regards to starting queries. SQL is somewhat of an elegant language, but at the end of the day stuff needs to get done and well it starts to show its faults from a business perspective.

What I'm saying is there is a line on what one can do theoretically versus cut your losses and have others process it down the line. As in, dump the data, send it down the line, have them sort it after you did your best while not getting hung up on delivering exactly what they need. The bean counters are pretty good with Excel and can manipulate data far faster than you can with SQL.

* Talk to your instructor/prof. I enjoy having my students engage me and argue with me. That's how I learn too and you are way ahead of the game.

 

[seamadan]

I'd to the following, but it may not be the most efficient:

SELECT branch_name, assets
FROM table
ORDER BY assets DESC
LIMIT 1

UNION

SELECT branch_name, assets
FROM table
ORDER BY assets ASC
LIMIT 1

 

[Lil Frier]

Thanks for the feedback again, guys. I already turned in the assignment and explained to him what I could and couldn't pull off in a single query. Basically, I gave him that I was able to pull the min and max for the table and then just search for the names matching those numbers, so that's how I gave my answer, even if that's not the perfect solution he wanted.

As to what we've been discussing, it's been a lot of conceptual stuff and the like. We basically did the first two chapters, then skipped 3-5 (all of the query stuff) and when to 6-8, which involve things like relational algebra and entity-relationship stuff. We start the query chapters after the mid-term. Like I said, we meet once every other week, and we just had our third meeting. The first was mostly introductory stuff about the class, and the other two were discussion of the design process (first as a class, then in smaller groups).

What makes it kind of a tough solution, to me, is that we're starting queries 100% on our own. We've not discussed them before or had another assignment require them. So, I have no idea what's considered an acceptable means of solution in these cases. As was suggested, I could just tell the table to sort numerically, then see the answer easily with each end (min and max). Really, it came down to a busy week and a bit of procrastination, as I would have NORMALLY gotten started sooner and discussed this stuff with my teacher before our meeting (I could have had about half an hour of free time before class to talk to him, if I'd gotten a chance to work on it early).

I'm usually really good with talking to my teachers, either about questions I have or something else. I think part of it is this guy MIGHT be new to teaching this class, though I'm not 100% sure on that (I know someone else was teaching it a couple of years ago), so it might be his expecting more from us than he should, or it might be that I just didn't retain enough knowledge from the very small SQL introduction one of my other classes had 3 years ago. If my brain reminds me in the morning, I might give seamadan's solution a try, just to see if I can find a workable solution. Otherwise, I'll probably talk to my teacher Monday or Tuesday after work, depending on how my data mining homework goes next, haha.

Again, I appreciate the responses, I know I forgot about union functions over the years. I've at least learned some stuff and been guided into looking at more, and maybe I'm now further than I need to be, in a good way.

 

[KentState]

OLAP functions are your friend here. Never use TOP/LIMIT/SAMPLE with an ORDER BY if you need guaranteed ordering as most database don't have a set order of operations for the two.

 

[BOOGY_DOG]

If you were required to return ONLY the branch name, then a subquery would have been the best choice.

SELECT branch_name
FROM table
WHERE assets = (SELECT min(assets) from table);

It's possible this could return multiple results if there are more than one min/max branches with the same assets.

 

[wiseguyin]

Umm , would not this work?

select top 1 branchname, min(assets) from table