SQL Historical 52 comments about SQL in 2004. this very thread is now stored in a MySQL database, queried with SQL, and served by a Rails app written in a language that didn't exist when this thread was posted.
by A forum for my SQL questions to be answered.
First question:
Who knows SQL?
Second question:
If you know SQL, do you work with or have worked with, mySQL?
Third question:
If answered "yes" to both preceding questions, have you used mySQL via UNIX shell?
...Questions directly about SQL coding itself, to follow.
AI Summary
52 Comments
What could it mean if I get the following:
Query OK, 1 row affected, 1 warning (0.00 sec)
I couldn't imagine it to mean that it has to do with there being a syntax error, since it's returning "Query OK." Yet, it seems contradictory, if at the same time it giving the "warning." What does mySQL interpret to be a "warning." And, it's weird, because it's returning this, and I didn't write the statements in question. I could see where if it was something that I personally wrote, it'd have errors in it, since I'm a newbie at SQL. But, it's SQL file that was premade, and I'm just running it.
Should I care that it gave me several of these "warnings?" Or, is it something I can just ignore, since it says "Query OK?" Plus, when I do" SHOW_TABLES; ...it shows that my tables were, in fact created. Weird.
since you're using mysql 4.1, after you've run a query that has warnings, you can issue a command: SHOW WARNINGS;
That will show you any warning generated in the previous query.
Oh, awesome. thanks. I sure wish I had a book on mySQL 4.1. The two books I have about SQL, actually present the information as if you were using Oracle. Having a mySQL book would be very beneficial, instead of trying to determine every time, if something will work in mySQL, or if it's an Oracle thing.
well, all you really need is the searchable docs: http://www.mysql.com/doc/en/index.html
I connect to my college's server via SSH Secure Shell Client (don't know who here's familiar with that, but that shouldn't matter.. you might be able to help any way). So, after connecting to the server and getting a prompt, I log into mySQL, in turn getting the mySQL prompt (which looks simply like "mysql>").
I have a file named "CreatePremiere.sql" in a folder called "CIS331." My question is how to I run this file... meaning what do I type at the mySQL prompt, to run it?
What I tried at the "mysql>" prompt, but didn't work, was this:
\. CIS331/CreatePremiere.sql
I got a file doesn't exist or something like that, so I know I'm typing it wrong, but how should it be typed?
Nevermind. Figured it out. The UNIX shell wanted me to append my user name with an underscore and something else, to my other log in information, before entering into mySQL. And something else that I had problems with, was getting my GUI front-end client's tunneling to work, but I finally got that set up succesfully now as well.
How do you represent an apostrophe, because say there's something like theremin's. I obviously wouldn't be able to do this:
WHERE POSSESSION = 'theremin's'
....So what would I type to represent where an apostrophe is?
good question. it's called string escaping.
read about it here: http://www.mysql.com/doc/en/String_syntax.html
and, basically it would be 'theremin\'s'
just put a \ in front of the '
My statement for the third to the last one:
SELECT ORDER_NUM, ORDER_DATE
FROM ORDERS, ORDER_LINE
WHERE ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM IN
(SELECT PART_NUM
FROM ORDER_LINE, PART
WHERE ORDER_LINE.PART_NUM = PART.PART_NUM AND DESCRIPTION = 'Gas Range');
Man, hope that's right. That really had me pulling out hair.
on the subquery, you want to select the ORDER_NUM, not the PART_NUM.
SQL Gurus, where are ya hiding?
Okay, I'm slowing but surely doing these, but haven't a clue if they're 100 percent correct. But, have a look... for the second to last one I came up with:
SELECT PART_NUM, DESCRIPTION, PRICE, CLASS
FROM PART
WHERE PRICE > ALL
(SELECT PRICE
FROM PART
WHERE CLASS = 'AP');
looks good to me.
If I was creating a new table, does the syntax in this line look right, if I didn't want nulls allowed and wanted it to be a primary key?
COLUMN_NAME_HERE CHAR(5) NOT NULL PRIMARY KEY
looks right to me.
Would the SQL statement for the first one look like:
SELECT ORDER_NUM, ORDER_DATE, PART_NUM
FROM ORDERS, ORDERS_LINE
WHERE ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM IN
(SELECT PART_NUM, DESCRIPTION, CLASS
FROM PART, ORDER_LINE
WHERE PART.PART_NUM = ORDER_LINE.PART_NUM
ORDER BY CLASS
ORDER BY ORDER_NUM);
?
looks like you've got the right idea. i might modify it like this tho:
SELECT ORDER_NUM, ORDER_DATE, PART_NUM
FROM ORDERS, ORDERS_LINE
WHERE ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM AND
ORDER_LINE.ORDER_NUM IN
(SELECT PART_NUM, DESCRIPTION, CLASS
FROM PART, ORDER_LINE
WHERE PART.PART_NUM = ORDER_LINE.PART_NUM
ORDER BY CLASS, ORDER_NUM);
What's the difference between ORDER BY and GROUP BY?.. besides the fact that they're different functions and spelled differently, I mean. Like, when use one and when use the other?
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.
Good to know. Thanks. I haven't tried INNER JOINs yet, because I didn't understand them, but now I do, a little better.
in case I wasn't clear enough:
SELECT col1, col2 FROM table1, table2 WHERE table1.col3 = table2.col9
is an inner join.
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"
come on, you have all the geeks foaming at the mouth now. ask your question bro haha
yeah, me too. yes to all questions.
i do, yes, and yes!
eZabel runs on Linux using MySQL.
What questions do you have?
does that explain why it's so slow...?
haha, good one. but, no.
it's slow because it's programmed in ColdFusion which doesn't lend itself to very efficient code. a lot of stuff is super-slow running code that happens many many times per page load.
excuses excuses. see you need a fast computer. 4 billion hertz and sixty thousand island hard drive, mount that to your swap space and kill -9, oh man so fast
yes, mysql provides it's own shell and im pretty sure you can also invoke shell commands from your user shell
kind of like naked arabians dancing in the sand dunes of mt. ooga dooba
those are samaritans get it right