4 night SQL primer?
August 18, 2006 1:16 PM Subscribe
Can you help me with a primer on SQL?
I am a techie going on an interview for a new job. Mostly I work with end users, but I have some server and network experience. I have been at my current job for 6 years now. It's time to go.
My resume was requested for higher level support position that may lead to programming, which is what I would really like to do. I was just called with my interview time and date today after submitting resume yesterday. I was told to get a primer on SQL before the interview (9:00 AM Tuesday 22nd, August.) Eventually there will be some SQL skills needed for the job. They will know that my only SQL experience is configuring DSNs (yes, a monkey can do that.) The idea is NOT to dupe them into thinking I have vastly more SQL experience than I do. The idea is to allow me to go in and be able to converse intelligently about SQL to some degree and to let them know that I have started looking into skills needed for the position; showing them that I am eager and ready to learn whatever skills the job takes that i don't already posess. I am trying to learn in 4 nights what can reasonably be learned about SQL in that amount of time.
Again, I am not trying to fool them here. They will be aware of my level of experience. I am trying to show initiative and have something akin to an intelligent conversation regarding SQL.
So, what should I read or do to get something of a SQL primer in 4 nights?
I am a techie going on an interview for a new job. Mostly I work with end users, but I have some server and network experience. I have been at my current job for 6 years now. It's time to go.
My resume was requested for higher level support position that may lead to programming, which is what I would really like to do. I was just called with my interview time and date today after submitting resume yesterday. I was told to get a primer on SQL before the interview (9:00 AM Tuesday 22nd, August.) Eventually there will be some SQL skills needed for the job. They will know that my only SQL experience is configuring DSNs (yes, a monkey can do that.) The idea is NOT to dupe them into thinking I have vastly more SQL experience than I do. The idea is to allow me to go in and be able to converse intelligently about SQL to some degree and to let them know that I have started looking into skills needed for the position; showing them that I am eager and ready to learn whatever skills the job takes that i don't already posess. I am trying to learn in 4 nights what can reasonably be learned about SQL in that amount of time.
Again, I am not trying to fool them here. They will be aware of my level of experience. I am trying to show initiative and have something akin to an intelligent conversation regarding SQL.
So, what should I read or do to get something of a SQL primer in 4 nights?
GalaxQL (works on any platform).
posted by Blazecock Pileon at 1:34 PM on August 18, 2006 [1 favorite]
posted by Blazecock Pileon at 1:34 PM on August 18, 2006 [1 favorite]
If you have Access, you might want to play around with the query builder and then check out the SQL view. This is a good way to play around with some things after you do a bit of book learning elsewhere.
posted by utsutsu at 1:36 PM on August 18, 2006
posted by utsutsu at 1:36 PM on August 18, 2006
Best answer: SQL about set theory, or more correctly, relational algebra. Read a brief primer on that.
On the practical side, there are four fundamental operations,
* selects, which show some subset of a table(as) data
* deletes, which remove some subset of a table's rows,
* inserts, which add rows to a table,
* updates, which (possibly) change data in a table
(By subset, I mean any subset, including the empty set, and not necessarily a proper subset.)
Tables are related by joins, in which some datum or key in one is related by an operator to a datum or key in another table. The relational operator is often equality, but can in fact be any binary operator or even a binary function. (By binary, I mean, "takes two operands".)
Tables are related, as I mentioned above, by datums or more properly keys; a row in a table may relate to zero, one, or many rows in another table; this is referred to as the cardinality of the relation.
EF Codd, among other, pioneered the idea of normal form in relational databases. There are commonly held to be five or six normal forms, but the most important summary of normal form is simple: every entity that your database models should be represented by one row and one row only, and every row should model an entity or a relationship. Read a primer on normal norm.
Practical tips: first, write a view. whatever you're doing, it'll be simpler and clearer if you write a view for every calculation or sub-calculation you do. write a view that encapsulates each join, write a view that encapsulates each transformation. Almost anything you want to do can be done in a view.
Don't conflate data. Each table ought to unambiguously model one thing (one kind of entity) and only one thing; each column should express one and only one attribute of that thing. Different kinds of entities below in different tables.
Metadata is your friend. Your database platform will provide some metadata; what it doesn't provide you should add. Since metadata is data, all the rules for modeling data apply.
Experiment. sit down at a database and ask yourself, "How can I show all left-handed redheads who at one time lived in Poughkeepsie", and write the query that does that. Piece it together by writing views that show you all red-heads, all left-handers, and all people who have lived in Poughkeepsie.
You may want to start with the postgresql SQL tutorial; I mention that one in particular only because some of the example SQL in it, I wrote or corrected.
I'd offer you an intensive tutorial over the weekend (for a steep price), but you're probably better off just grabbing an on-line tutorial, working through it, and asking follow-up questions here.
And by work through, I mean install a database and a front end, and run actual queries. (And then you can also honestly say at the interview "I have database X installed at home".) I'd avoid Access (it uses its own weird terminology), in favor of either MS SQL Server Express (it actually has the best query UI I've seen, to my surprise) or postgresql (it's the most ANSI conformant db) -- both are free. GalaxQL is a good tutorial two, but make sure you understand that it's based on SQLlite, and what that means
posted by orthogonality at 1:46 PM on August 18, 2006 [2 favorites]
On the practical side, there are four fundamental operations,
* selects, which show some subset of a table(as) data
* deletes, which remove some subset of a table's rows,
* inserts, which add rows to a table,
* updates, which (possibly) change data in a table
(By subset, I mean any subset, including the empty set, and not necessarily a proper subset.)
Tables are related by joins, in which some datum or key in one is related by an operator to a datum or key in another table. The relational operator is often equality, but can in fact be any binary operator or even a binary function. (By binary, I mean, "takes two operands".)
Tables are related, as I mentioned above, by datums or more properly keys; a row in a table may relate to zero, one, or many rows in another table; this is referred to as the cardinality of the relation.
EF Codd, among other, pioneered the idea of normal form in relational databases. There are commonly held to be five or six normal forms, but the most important summary of normal form is simple: every entity that your database models should be represented by one row and one row only, and every row should model an entity or a relationship. Read a primer on normal norm.
Practical tips: first, write a view. whatever you're doing, it'll be simpler and clearer if you write a view for every calculation or sub-calculation you do. write a view that encapsulates each join, write a view that encapsulates each transformation. Almost anything you want to do can be done in a view.
Don't conflate data. Each table ought to unambiguously model one thing (one kind of entity) and only one thing; each column should express one and only one attribute of that thing. Different kinds of entities below in different tables.
Metadata is your friend. Your database platform will provide some metadata; what it doesn't provide you should add. Since metadata is data, all the rules for modeling data apply.
Experiment. sit down at a database and ask yourself, "How can I show all left-handed redheads who at one time lived in Poughkeepsie", and write the query that does that. Piece it together by writing views that show you all red-heads, all left-handers, and all people who have lived in Poughkeepsie.
You may want to start with the postgresql SQL tutorial; I mention that one in particular only because some of the example SQL in it, I wrote or corrected.
I'd offer you an intensive tutorial over the weekend (for a steep price), but you're probably better off just grabbing an on-line tutorial, working through it, and asking follow-up questions here.
And by work through, I mean install a database and a front end, and run actual queries. (And then you can also honestly say at the interview "I have database X installed at home".) I'd avoid Access (it uses its own weird terminology), in favor of either MS SQL Server Express (it actually has the best query UI I've seen, to my surprise) or postgresql (it's the most ANSI conformant db) -- both are free. GalaxQL is a good tutorial two, but make sure you understand that it's based on SQLlite, and what that means
posted by orthogonality at 1:46 PM on August 18, 2006 [2 favorites]
Best answer: I have been reading O'Reilly's *Learning SQL* by Alan Beaulieu and it has been excellent so far. An easy-to-follow read and very quick. I already use SQL for my job and I'm amazed at some of the fundamentals that I'm just discovering through this book.
posted by cadge at 1:49 PM on August 18, 2006 [1 favorite]
posted by cadge at 1:49 PM on August 18, 2006 [1 favorite]
Are you talking server-side SQL? Here's a PHP/MySQL tutorial. Or just in-house database programming for Access? Here's a simple SQL tutorial.
posted by beatrice at 2:06 PM on August 18, 2006
posted by beatrice at 2:06 PM on August 18, 2006
beatrice writes "Or just in-house database programming for Access? Here's a simple SQL tutorial."
The first sentence of second page of that tutorial starts off with two errors, both of which will make experienced interviewers groan:SQL (pronounced "ess-que-el") stands for Structured Query Language.
posted by orthogonality at 2:11 PM on August 18, 2006
The first sentence of second page of that tutorial starts off with two errors, both of which will make experienced interviewers groan:
posted by orthogonality at 2:11 PM on August 18, 2006
beatrice writes "Are you talking server-side SQL? Here's a PHP/MySQL tutorial. "
Not to pick on beatrice, but the above link contains this gem: "MySQL is a small, compact database server ideal for small - and not so small - applications."
No, it's not. First, MySQL's approach to indexing is god-awful. second, it's not conformance, in god-awful ways, e.g., until recently it didn't support views, and still in MySQL 5.1 (from the docs, section 13.10.2): "Currently, you cannot update a table and select from the same table in a subquery."
These have real consequences both for learning and performance. MySQL is a great db if you never have to do an update. But it's at best unlike any other mainstream RDBMS. If you learn SQL on it, you're in for a lot of "un-learning" when you move to any other RDBMS. No offense, to Mac or mainframe users, but it's like making Objective-C your first language or VMS your first OS.
posted by orthogonality at 2:18 PM on August 18, 2006
Not to pick on beatrice, but the above link contains this gem: "MySQL is a small, compact database server ideal for small - and not so small - applications."
No, it's not. First, MySQL's approach to indexing is god-awful. second, it's not conformance, in god-awful ways, e.g., until recently it didn't support views, and still in MySQL 5.1 (from the docs, section 13.10.2): "Currently, you cannot update a table and select from the same table in a subquery."
These have real consequences both for learning and performance. MySQL is a great db if you never have to do an update. But it's at best unlike any other mainstream RDBMS. If you learn SQL on it, you're in for a lot of "un-learning" when you move to any other RDBMS. No offense, to Mac or mainframe users, but it's like making Objective-C your first language or VMS your first OS.
posted by orthogonality at 2:18 PM on August 18, 2006
Response by poster: Thanks all. Keep 'em coming. After my 4 nights are up I'll mark my favorite. Great answers so far.
posted by horseblind at 2:23 PM on August 18, 2006
posted by horseblind at 2:23 PM on August 18, 2006
"Sams Teach Yourself SQL in 10 Minutes" by Ben Forta. In addition to being a great how-to guide that is, as you require, a fast read, but you will find it to be an invaluable reference guide after the fact.
posted by robhuddles at 2:51 PM on August 18, 2006
posted by robhuddles at 2:51 PM on August 18, 2006
I found this one entirely by accident about a year ago, but SQL Zoo is a very good resource. It's page is titled "A Gentle Introduction to SQL", and that's just what it is. It assumes no prior knowledge, the 'lessons' are small and easy to digest, and each comes with a 'quiz' at the end that's LIVE. That is to say, you get a question, and have to type in the appropriate SQL statements to receive the coveted "Well done. Your answer is correct.
Four nights would be plenty of time to check out this site and learn the basics. The "quizzing" lets you test your knowlege right away. You really could do a lot worse than SQL Zoo.
posted by Wild_Eep at 2:52 PM on August 18, 2006 [1 favorite]
Four nights would be plenty of time to check out this site and learn the basics. The "quizzing" lets you test your knowlege right away. You really could do a lot worse than SQL Zoo.
posted by Wild_Eep at 2:52 PM on August 18, 2006 [1 favorite]
Wild_Eep writes "You really could do a lot worse than SQL Zoo."
That's pretty cool. Fair warning, though, it appears the database is missing some data, as some of the sample questions just don't have answers, e.g., 3b in section 2a, "Find the region for which all countries have a population of 0". (Infact no countries have a pop. of zero, though Vatican's pop is null.)
posted by orthogonality at 3:16 PM on August 18, 2006
That's pretty cool. Fair warning, though, it appears the database is missing some data, as some of the sample questions just don't have answers, e.g., 3b in section 2a, "Find the region for which all countries have a population of 0". (Infact no countries have a pop. of zero, though Vatican's pop is null.)
posted by orthogonality at 3:16 PM on August 18, 2006
orthogonality: The first sentence of second page of that tutorial starts off with two errors, both of which will make experienced interviewers groan: SQL (pronounced "ess-que-el") stands for Structured Query Language.
Actually, that sentence is error-free. Certain implementations and certain developers prefer 'sequel' over SQL, but the initialism is more formally acceptable. Structured Query Language is also correct.
posted by wackybrit at 3:33 PM on August 18, 2006
Actually, that sentence is error-free. Certain implementations and certain developers prefer 'sequel' over SQL, but the initialism is more formally acceptable. Structured Query Language is also correct.
posted by wackybrit at 3:33 PM on August 18, 2006
How about you install XAMPP on your own computer and actually play with your own MySQL database, administered through PHPMyAdmin.
Set yourself some task like, take your three favourite movie stars, the list of characters they played and the list of movies.
Create three tables for stars, roles and movies, and work out the queries needed to produce
"[STAR] played [CHARACTER] in [MOVIE], [YEAR]"
in various combinations and orders. Or, you know, musicians who played on albums, sports stars who played on teams, whatever works for you. Then move up to more complicated things like
[MOVIE] starred:
* [STAR] as [CHARACTER],
* [STAR2 as [CHRACTER2]
etc.
You should come up against a bunch of practical issues like "what kind of field do you need for such-and-such data" and you should use unique, auto-incrementing IDs in each of the tables.
Bonus points if you don't type the data in, you figure out how to insert it via SQL INSERT statements.
posted by AmbroseChapel at 3:35 PM on August 18, 2006
Set yourself some task like, take your three favourite movie stars, the list of characters they played and the list of movies.
Create three tables for stars, roles and movies, and work out the queries needed to produce
"[STAR] played [CHARACTER] in [MOVIE], [YEAR]"
in various combinations and orders. Or, you know, musicians who played on albums, sports stars who played on teams, whatever works for you. Then move up to more complicated things like
[MOVIE] starred:
* [STAR] as [CHARACTER],
* [STAR2 as [CHRACTER2]
etc.
You should come up against a bunch of practical issues like "what kind of field do you need for such-and-such data" and you should use unique, auto-incrementing IDs in each of the tables.
Bonus points if you don't type the data in, you figure out how to insert it via SQL INSERT statements.
posted by AmbroseChapel at 3:35 PM on August 18, 2006
The first sentence of second page of that tutorial starts off with two errors, both of which will make experienced interviewers groan: SQL (pronounced "ess-que-el") stands for Structured Query Language.
Orthogonality, as an experienced Oracle interviewer, I would somewhat disagree. I do prefer sequel to S-Q-L, but the terms are 100% interchangeable.
The well respected and read Eddie Awad’s blog corroborates this.
a fun read either way....
posted by Slenny at 3:38 PM on August 18, 2006
Orthogonality, as an experienced Oracle interviewer, I would somewhat disagree. I do prefer sequel to S-Q-L, but the terms are 100% interchangeable.
The well respected and read Eddie Awad’s blog corroborates this.
a fun read either way....
posted by Slenny at 3:38 PM on August 18, 2006
wackybrit writes "Actually, that sentence is error-free. "
"SEQUEL", the original name, was a "structured English query language"; because of trademark issues, it was renamed "SQL" but continued to be pronounced "sequel". See the original paper here.
posted by orthogonality at 3:45 PM on August 18, 2006
"SEQUEL", the original name, was a "structured English query language"; because of trademark issues, it was renamed "SQL" but continued to be pronounced "sequel". See the original paper here.
posted by orthogonality at 3:45 PM on August 18, 2006
Oh wow. I just thought, I remember using some quick web tutorials, there have to be a lot out there. But didn't look too closely at the one I linked. Oops, sorry.
posted by beatrice at 3:47 PM on August 18, 2006
posted by beatrice at 3:47 PM on August 18, 2006
orthogonality: Type define:sql into Google and at least 20 worthwhile sources define it as Structured Query Language, as does Wikipedia (which should never be a definitive source, of course!)
posted by wackybrit at 4:07 PM on August 18, 2006
posted by wackybrit at 4:07 PM on August 18, 2006
So we're discussing the eternal question:
"If something originally stands for something, and then for purely legal/copyright reasons it's not allowed to stand for that thing any more, what is its true name?"
For instance: what's Eminem's true name?
But it's not helping the OP, now is it?
posted by AmbroseChapel at 4:53 PM on August 18, 2006
"If something originally stands for something, and then for purely legal/copyright reasons it's not allowed to stand for that thing any more, what is its true name?"
For instance: what's Eminem's true name?
But it's not helping the OP, now is it?
posted by AmbroseChapel at 4:53 PM on August 18, 2006
Unless it comes up at the interview of course.
posted by AmbroseChapel at 4:54 PM on August 18, 2006
posted by AmbroseChapel at 4:54 PM on August 18, 2006
I thought I knew SQL until I took a course involving SQL and relational algebra. Learning some background will put you much further ahead than simply reading php/mysql tutorials online. (Not that those tutorials aren't a great starting point)
posted by null terminated at 2:07 AM on August 19, 2006
posted by null terminated at 2:07 AM on August 19, 2006
The number one problem new SQL programmers tend to have, IME, is that there's sort of an impedance mismatch between SQL itself and *whatever* programming language you're working it; you'll want to plan on reading lots of code, or using someone's wrapper library, or both.
And yeah, some people use the name pronunciation as a shibboleth; Pascal's Wager says to say it as "Sequel"; the people who expect that will be happy, the people who expect S-Q-L will by and large not care, and the few S-Q-L partisans who use it as a shibboleth in the opposite direction...
you don't want to work for anyway, because they're anal about something that's fairly easy to prove incorrect. :-)
posted by baylink at 9:30 AM on August 19, 2006
And yeah, some people use the name pronunciation as a shibboleth; Pascal's Wager says to say it as "Sequel"; the people who expect that will be happy, the people who expect S-Q-L will by and large not care, and the few S-Q-L partisans who use it as a shibboleth in the opposite direction...
you don't want to work for anyway, because they're anal about something that's fairly easy to prove incorrect. :-)
posted by baylink at 9:30 AM on August 19, 2006
Oh, and it wouldn't hurt you to at least know the name of SQL maven Joe Celko, though his books will mostly be way over your head... this month.
posted by baylink at 9:35 AM on August 19, 2006
posted by baylink at 9:35 AM on August 19, 2006
This thread is closed to new comments.
posted by wackybrit at 1:22 PM on August 18, 2006