I did all the ones I could, but had trouble coming up with like the last 6 of the SQL statements required. So, I put what the database looks like, and the problems I couldn't figure out into Word. And, it can be viewed either in Word here, RTF here, or HTM here. A big thanks in advance to whoever winds up helping me, cuz this is really killing me right now. I only managed to do like 3 of the problems, which is bad.
These shouldn't be too bad... I don't think just posting answers is really going to help, anything/questions in particular you're having a problem with?
No, actually. haha These are my questions.
Maybe you can tell me this... Say I have my SQL statements typed out in something.sql. Did you ever work with mySQL Control Center? 'Cause instead of running sql file via command line, I wanna try to run it via GUI using mySQL Command Center this time. How would I do so. I already have mySQL Command Center on my computer, and I have it all configured for tunneling, since the remote server I'll be running it off of, required me to, an all. So, that's all take care of, but what's the next step? How would I run something.sql using the mySQL Command Center GUI?
Ok, well for #2 on that page..
you basically want to join the REP and CUSTOMER tables on the REP_NUM column, and also CUSTOMER.CREDIT_LIMIT = '$10,000.00'. Then just add in the DISTINCT keyword in the select statement to only retrieve each REP once.
Thank you Ian. Every bit helps.
So, it'd be...
SELECT DISTINCT(REP_NUM, LAST_NAME, FIRST_NAME)
FROM REP
WHERE REP.REP_NUM = CUSTOMER.REP_NUM
AND CUSTOMER.CREDIT_LIMIT = '$10,000'
?
looks pretty good to me.
or you could do an inner join.
how would you write that?
well.. a select with a WHERE clause that matches two columns from different tables IS an INNER JOIN (by definition).
you can use the following syntax if you like.
SELECT DISTINCT(REP_NUM, LAST_NAME, FIRST_NAME)
FROM REP INNER JOIN CUSTOMER
ON REP.REP_NUM = CUSTOMER.REP_NUM
WHERE CUSTOMER.CREDIT_LIMIT = '$10,000'
hehehe... just being difficult.
question 3 is confusing, because it mentions parts are in "pairs", and I don't see how they are paired at all. I don't really understand the question. But, maybe what they're looking for is a GROUP BY on the CLASS column.
I think they use the word pair, because there's more than one item in each class. For example, there's not one, but 3 items in class HW (Iron, Cordless Drill, Stand Mixer)... and for some twisted reason, they call that a pair, I guess.
This is what I came up with and hopefully it's what's wanted...
SELECT PART_NUM, DESCRIPTION, CLASS
FROM PART
GROUP BY CLASS
ORDER BY CLASS;
some of the things in there seem to require more than 1 query, such as the first one, and the last one.
what version of mysql are you doing this on? 4.1 supports subqueries, but 4.0 doesn't. do you have to answer each question with a single query?
Using subqueries is alright except in the case of the second problem where i wasn't supposed to use one, but we're already pass that now, so in the case of the remainder of the problems, yes, subqueries can be utilized. And, not using subqueries is fine too, if that's what you favor. Which ever way is good. If you see that using more that a single statement seems best over using a subquery or more is best, than go with that way. I'm unsure of which version this is, but subqueries are supported as well.
ok, Found out what version.
"Your MySQL connection id is ***** to server version: 4.1.1-alpha-log"