Help me askme, you're my only hope.
March 9, 2009 5:02 PM Subscribe
I've got an interview for a marketing analytics position that involves SQL - how can i learn about SQL/practice?
I know the basics concepts of SQL (i.e. Select, by, where, join, etc) - but nothing about the actual use - I've never used the program. I understand SQL is a database language. Is there a program most people use to run these databases and if so how is the data usually accessed? I have a few days to learn this stuff and become a decent user. What should I read? How should I practice?
They said they'd like me to work both with 'raw data' and 'through an interface'. What does this mean?
I'm pretty computer savvy, but mostly with more basic applications, including Office, SPSS, and a few others, but I've just never had to use SQL before. I am decent at data analysis so I hope I can make this transition easily.
Throw watchu got at me.
Thanks.
I know the basics concepts of SQL (i.e. Select, by, where, join, etc) - but nothing about the actual use - I've never used the program. I understand SQL is a database language. Is there a program most people use to run these databases and if so how is the data usually accessed? I have a few days to learn this stuff and become a decent user. What should I read? How should I practice?
They said they'd like me to work both with 'raw data' and 'through an interface'. What does this mean?
I'm pretty computer savvy, but mostly with more basic applications, including Office, SPSS, and a few others, but I've just never had to use SQL before. I am decent at data analysis so I hope I can make this transition easily.
Throw watchu got at me.
Thanks.
Once you get the basics down, I recommend using this crazy Russian SQL exercise site to practice. The problems quickly get difficult, but even doing the first few dozen will give you experience writing real queries with a specific result in mind. Good luck!
posted by sen at 5:27 PM on March 9, 2009
posted by sen at 5:27 PM on March 9, 2009
Being able to execute the commands described in the links above would be a good way of becoming familiar with them.
I suspect you don't want to install your RDBMS (although if you have the time that's an option - SQL Server Express or MySQL Community Edition).
There's a website here that leads you through some tutorials and allows you enter your own SQL - for me at least doing is a good part of learning ! (On preview sen's link looks good too)
posted by southof40 at 5:30 PM on March 9, 2009
I suspect you don't want to install your RDBMS (although if you have the time that's an option - SQL Server Express or MySQL Community Edition).
There's a website here that leads you through some tutorials and allows you enter your own SQL - for me at least doing is a good part of learning ! (On preview sen's link looks good too)
posted by southof40 at 5:30 PM on March 9, 2009
... or even ...
There's a website here that leads you through some tutorials and allows you enter your own SQL
posted by southof40 at 5:31 PM on March 9, 2009 [1 favorite]
There's a website here that leads you through some tutorials and allows you enter your own SQL
posted by southof40 at 5:31 PM on March 9, 2009 [1 favorite]
A few sample questions/problems off the top of my head:
Select only the distinct values from a dataset containing duplicates without using the DISTINCT keyword.
for instance, your query from the dataset:
{
cat
cat
cat
dog
ant
dog
}
should return
{cat
ant
dog
}
What is the difference between WHERE and HAVING ?
Create a procedure that will return a count of how many records in each row are either NULL, the empty string, or zero.
What is the difference between UNION and UNION ALL ?
posted by spatula at 5:49 PM on March 9, 2009
Select only the distinct values from a dataset containing duplicates without using the DISTINCT keyword.
for instance, your query from the dataset:
{
cat
cat
cat
dog
ant
dog
}
should return
{cat
ant
dog
}
What is the difference between WHERE and HAVING ?
Create a procedure that will return a count of how many records in each row are either NULL, the empty string, or zero.
What is the difference between UNION and UNION ALL ?
posted by spatula at 5:49 PM on March 9, 2009
Um.
OK. You know the basics. Then it's time to install a database. In terms of user friendliness, I'd suggest mysql. In terms of standards compliance and robustness, pgsql.
Once you've done this, you can use the database's command line client or a third-party client, like SQuirreL, to access it.
"raw data" presumably means via a client like the above. "an interface" presumably means that they have a proprietary interface that pulls/updates records without requiring you to type sql commands.
In addition to the basics, you want to be sure you understand what a null value is, and what kinds of nulls there are. If you're doing analytics, understanding how aggregate functions, "group by" and "having" work will be important.
But. Um. A few days is frankly just going to teach you enough to be dangerous. As a novice, it's very easy to write a query which doesn't quite do what you expect it to do. This will lead to getting back data that appears to be valid, but is not. Which will lead to making business deciosns based on inaccurate data.
posted by orthogonality at 6:00 PM on March 9, 2009 [1 favorite]
OK. You know the basics. Then it's time to install a database. In terms of user friendliness, I'd suggest mysql. In terms of standards compliance and robustness, pgsql.
Once you've done this, you can use the database's command line client or a third-party client, like SQuirreL, to access it.
"raw data" presumably means via a client like the above. "an interface" presumably means that they have a proprietary interface that pulls/updates records without requiring you to type sql commands.
In addition to the basics, you want to be sure you understand what a null value is, and what kinds of nulls there are. If you're doing analytics, understanding how aggregate functions, "group by" and "having" work will be important.
But. Um. A few days is frankly just going to teach you enough to be dangerous. As a novice, it's very easy to write a query which doesn't quite do what you expect it to do. This will lead to getting back data that appears to be valid, but is not. Which will lead to making business deciosns based on inaccurate data.
posted by orthogonality at 6:00 PM on March 9, 2009 [1 favorite]
If you're combining analytics with SQL at your current knowledge level, you and your employer are in for a world of hurt. You need more than a quick run-through - you need to actually take a course on database design and theory.
posted by squorch at 7:10 PM on March 9, 2009
posted by squorch at 7:10 PM on March 9, 2009
Is there a program most people use to run these databases and if so how is the data usually accessed?
A database management system takes care of the whole business of efficiently storing and retrieving data. Think of a database management system as a restaurant, and SQL is a menu: your prospective employer is essentially saying "we're taking you to a restaurant, so you have to know how to order off of menus". You can learn all you want about menus, and you can even look, to an outside observer, like you know what you're doing when the waiter shows up, but if you don't know anything about the restaurant, you're going to order the wrong thing.
So you'll have to learn something about databases other than the language people use to ask them questions. You'll have to learn a little bit about the relational model of data, and enough about indexes and performance to know why one query takes 5 seconds and another one takes 2 hours. Then, if you know the basics of SQL, you'll be able to express the query you want to send to the database in a language the database understands.
That said, most database management systems are server applications, which means you run them and they wait around for a different program, called a client program, to show up and ask them questions. Most database systems come with a simple client that just presents you a prompt, and then you type in SQL and it shows you the results.
They said they'd like me to work both with 'raw data' and 'through an interface'. What does this mean?
Many businesses that use databases write their own clients that are more specifically tuned to their needs. This is probably what they mean by 'through an interface': they have their own client that you can work with instead of the command line.
If you know data analysis (SPSS is a good start), it's just a matter of figuring out how to get the data you want out of the database. You won't be able to get out of it without learning at least a little about databases, but if you're just doing analytics and not actual database management, you're off to a pretty good start.
posted by goingonit at 7:27 PM on March 9, 2009
A database management system takes care of the whole business of efficiently storing and retrieving data. Think of a database management system as a restaurant, and SQL is a menu: your prospective employer is essentially saying "we're taking you to a restaurant, so you have to know how to order off of menus". You can learn all you want about menus, and you can even look, to an outside observer, like you know what you're doing when the waiter shows up, but if you don't know anything about the restaurant, you're going to order the wrong thing.
So you'll have to learn something about databases other than the language people use to ask them questions. You'll have to learn a little bit about the relational model of data, and enough about indexes and performance to know why one query takes 5 seconds and another one takes 2 hours. Then, if you know the basics of SQL, you'll be able to express the query you want to send to the database in a language the database understands.
That said, most database management systems are server applications, which means you run them and they wait around for a different program, called a client program, to show up and ask them questions. Most database systems come with a simple client that just presents you a prompt, and then you type in SQL and it shows you the results.
They said they'd like me to work both with 'raw data' and 'through an interface'. What does this mean?
Many businesses that use databases write their own clients that are more specifically tuned to their needs. This is probably what they mean by 'through an interface': they have their own client that you can work with instead of the command line.
If you know data analysis (SPSS is a good start), it's just a matter of figuring out how to get the data you want out of the database. You won't be able to get out of it without learning at least a little about databases, but if you're just doing analytics and not actual database management, you're off to a pretty good start.
posted by goingonit at 7:27 PM on March 9, 2009
You can download Oracle 11g database with a big set of play around marketing tables and SQL developer for free.
sql developer
database
There is also a full course guide you can download that's really good.
Good luck, I went down this road about 6 months ago and asked a similar question.
posted by zephyr_words at 7:31 PM on March 9, 2009
sql developer
database
There is also a full course guide you can download that's really good.
Good luck, I went down this road about 6 months ago and asked a similar question.
posted by zephyr_words at 7:31 PM on March 9, 2009
Seconding SQL Zoo for tutorials on getting a better grip of queries.
Also, you might also like GalaXQL, an interactive tutorial/game for SQL beginners, which I found out about a long time ago in this thread on the blue. It should also help with putting queries into practice.
... Upon digging further, I see you've already favorited that thread. If you haven't tried the game already, you might find it helpful in tandem with SQL Zoo and other references mentioned above. There's also the O'Reilly book "Learning SQL" by Alan Beaulieu.
posted by macguffin at 7:50 PM on March 9, 2009
Also, you might also like GalaXQL, an interactive tutorial/game for SQL beginners, which I found out about a long time ago in this thread on the blue. It should also help with putting queries into practice.
... Upon digging further, I see you've already favorited that thread. If you haven't tried the game already, you might find it helpful in tandem with SQL Zoo and other references mentioned above. There's also the O'Reilly book "Learning SQL" by Alan Beaulieu.
posted by macguffin at 7:50 PM on March 9, 2009
One more thing -- I'll qualify my comment above by saying that I'm not a SQL expert by any means, but the resources I mentioned specifically were pretty accessible and newbie-friendly for me (YMMV, of course). If you're not yet ready to install your own db and experiment, then they'll get you started.
(FWIW, I do agree with orthogonality that a crash-course in SQL in a few days is enough to be dangerous. Ideally there'd be someone at the job who'd be able to give advice and/or handle the more complex queries.)
posted by macguffin at 8:16 PM on March 9, 2009
(FWIW, I do agree with orthogonality that a crash-course in SQL in a few days is enough to be dangerous. Ideally there'd be someone at the job who'd be able to give advice and/or handle the more complex queries.)
posted by macguffin at 8:16 PM on March 9, 2009
Response by poster: Thanks all for your suggestions so far. From what I can tell they're not looking for me to create/maintain their database but just have basic enough knowledge so that they can teach me how to datamine.
Thanks zephyr_words for that db link - thats the kinda stuff im looking for.
posted by jourman2 at 11:55 PM on March 9, 2009
Thanks zephyr_words for that db link - thats the kinda stuff im looking for.
posted by jourman2 at 11:55 PM on March 9, 2009
orthogonality has pretty much said everything that I want to say.
The best book I can suggest for learning SQL within a few days would be Head First SQL. Put in the effort and you should be able to upload most of the syntaxes into your brain within the allocated timeframe.
And here's a torrent (*ahem) for 'previewing' purposes. I still suggest that you get the deadtree version, really.
posted by joewandy at 12:26 AM on March 10, 2009
The best book I can suggest for learning SQL within a few days would be Head First SQL. Put in the effort and you should be able to upload most of the syntaxes into your brain within the allocated timeframe.
And here's a torrent (*ahem) for 'previewing' purposes. I still suggest that you get the deadtree version, really.
posted by joewandy at 12:26 AM on March 10, 2009
Also, don't waste your time trying to figure out how install and setup oracle DB. Stick to mysql.
posted by joewandy at 12:32 AM on March 10, 2009
posted by joewandy at 12:32 AM on March 10, 2009
For the interview, it's probably more important that you understand the purpose of SQL and WHY you'd be using it rather than the sticky details of how to do everything you'll need to do. Your approach should be to get enough info about SQL that when they present you with the inevitable problem you don't know, you can explain CONCEPTUALLY what you'd do rather than with an exact statement. The reason being, if you know what you want to do you can then convince them you just need to learn the syntax - but if you know syntax but not its purpose, you'll look like either an idiot or a faker.
My quickie tutorial: Think of a SQL database as a big Excel spreadsheet. SQL is really built to be a way to effectively filter results from the data. For instance, "show me sales data for this day of this product type" or "show me all products that cost more than $100".
When they say working with "raw data" and "through an interface", they likely mean they have a big text file or Excel spreadsheet with pageview data (or something like that) and want you to turn it into a pretty graphic of some sort. In that case, you'd have to create a database to store the data, then run statements (to filter the info you want), then turn those results into a pretty graphic. Through an interface could mean a number of things, but that probably means something like a SQL tool (which means you can click rather than type some things, but effectively it's all SQL) or they mean a tool like Google Analytics or WebTrends or something else - but it doesn't sound like they expect you to know everything about it. Just be confident in what you DO know, and don't over-extend yourself with what you don't.
posted by lubujackson at 7:33 AM on March 10, 2009
My quickie tutorial: Think of a SQL database as a big Excel spreadsheet. SQL is really built to be a way to effectively filter results from the data. For instance, "show me sales data for this day of this product type" or "show me all products that cost more than $100".
When they say working with "raw data" and "through an interface", they likely mean they have a big text file or Excel spreadsheet with pageview data (or something like that) and want you to turn it into a pretty graphic of some sort. In that case, you'd have to create a database to store the data, then run statements (to filter the info you want), then turn those results into a pretty graphic. Through an interface could mean a number of things, but that probably means something like a SQL tool (which means you can click rather than type some things, but effectively it's all SQL) or they mean a tool like Google Analytics or WebTrends or something else - but it doesn't sound like they expect you to know everything about it. Just be confident in what you DO know, and don't over-extend yourself with what you don't.
posted by lubujackson at 7:33 AM on March 10, 2009
This thread is closed to new comments.
posted by spacefire at 5:09 PM on March 9, 2009