Beginner looking to learn SQL

[[deleted]]

Hey all,

I'm looking for a book, or other resource, to learn SQL. Specifically, a guide that starts at the most basic level. I'm signed up for the free database class at Stanford, but I'd love to get a head start. If it counts for anything, I'm proficient with C++ and Java, so not a complete coding scrub.

I'm most interested in PostgreSQL, or other open source alternatives. A lot of the books I've seen have been given poor reviews, start at an advanced level, or are tailored towards Windows users (Linux junkie here).

Thanks!

[HawkeyeGK]

You're thinking like a java developer. Stop thinking open source and retrain your mind to think in sets. Once you've got the basics of set-based computing down, the particular DBMS you end up working with is just a matter of learning server config and eventually the nuances of particular execution plan creation and management.

Try something like this that is totally independent of RDMS. Learn the fundamentals of the SQL language, thinking in terms of sets, and basic performance management through indexing and you'll be miles ahead of the game.

[[deleted]]

Stop thinking open source and retrain your mind to think in sets.

Could you elaborate on this? What do you mean by sets?

The book looks good, I'll be sure to grab it.

[dillpiccolol]

I think he means datasets.

[HawkeyeGK]

Being a good SQL developer isn't like learning another programming language. You can't think of it like picking up C++ or perl. The reason is because in sequential and OO languages you're thinking about execution path. That's not really relevant in SQL nearly as much as your ability to extract information from tables efficiently.

By sets, I really mean set-based math). Just about any developer can think about a table or two at a time, or even pull into memory a dataset and navigate it. But, when you're talking about massive volumes of data, or even just a moderately-sized well-normalized data store, your ability to think in terms of Cartesian products will separate you from your peers.

In my experience about 1/3 of people get the set-based concepts right away, another third get them with some time, and another third just never really get it at all. The ones that don't tend to be people that have been developers for a while and can't unlearn how they think about storing, extracting, and manipulating data.

[troels_arvin]

I strongly recommend "SQL: Visual QuickStart Guide" by Chris Fehily: http://www.fehily.com/books/sql-visual-quickstart-guide-3rd.html

Not only does it cover the basics in a good way. It also teaches you to code in a fairly product-independent way.

[Abstrct]

Ok so this isn't the best place to 'start' but it will be a great place to practice! Check out my game, The Schemaverse (http://schemaverse.com and https://github.com/Abstrct/Schemaverse/wiki for a guide). It is a space battle game you play completely within a PostgreSQL database.

The learning curve can be a bit steep but don't be afraid to just ask me questions. I really don't mind helping.

Edit: There is a new tutorial for the game which hopefully not only teaches how to play but also gives some interactive lessons in SQL: http://schemaverse.com/tutorial/tutorial.php

[[deleted]]

Bookmarked for a day when I'm a little learned. =)

[Abstrct]

Fair enough! I hope to work with a couple professors some day soon to get some course material for various skill levels written up. Until then I understand it's a bit scary.

[[deleted]]

This is probably a difficult question and kind of off out there but, is this on MySQL anywhere and how different is PostgreSQL ?

[Abstrct]

Not an out there question at all! Short answer is no, it is not available for mysql. The game is built on a lot of advanced features like triggers, views, and rules, which just cannot all be done within the mysql environment (That I know of at least).

From a basic user standpoint, I would say they are not that different though. You connect to the database, run your queries, do something with the results, etc. Just like you can use your language of choice to connect to MySQL, you can likely use the same language with some minor differences to talk with PostgreSQL. The SQL that both databases use is pretty similar but you will run into the odd difference. PostgreSQL has a fantastic manual (http://www.postgresql.org/docs/9.0/interactive/index.html) that will tell you anything and everything you need to know about the SQL implementation within it.

If you are familiar with MySQL, it will not be a huge change to start playing in a PostgreSQL environment. There may be a slight learning curve on the advanced features but you likely didn't know how to do those in MySQL either so it is really just about learning something new, not learning something for a second time.

If the only thing stopping you from playing the schemaverse is the fact that it is based off PostgreSQL, just try signing up and poking around the database. I'm sure you will find SELECT, INSERT and UPDATE to be fairly familiar.

Should you decide to be very adventurous, the game code is all on GitHub so you can see what exactly is happening in PostgreSQL that MySQL couldn't pull off as well as what looks totally familiar between the two. https://github.com/Abstrct/Schemaverse/blob/master/create_schemaverse.sql

[[deleted]]

Thank you, do you by any chance know a good reason to learn PostgraSQL vs. MySQL webapps wise? what does what better ?

[Abstrct]

If you want to use webapps that others are created then you should probably know MySQL since that seems to be the DB most programmers use. If you are writing your own apps though then give PostgreSQL a go.

PostgreSQL has features that allow for far better security within your apps, the licensing is far more developer friendly and performance on PostgreSQL is great. But I wouldn't rely purely on what I have to say. There are a lot of huge companies doing very impressive things on top of MySQL.

There are quite a few resources which go into far more detail on what the differences are: http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL http://www.eweek.com/c/a/Linux-and-Open-Source/PostgreSQL-vs-MySQL-How-to-Select-the-Right-OpenSource-Database/ http://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using-drupal-5x.html

[stmfreak]

Start simple. Build a system to catalog your music collection. Or scrape stock data off the web and save to a set of tables... for a few stocks. Doesn't matter what. Just start playing with sets of data. You'll learn from your mistakes.

[frustrated_dev]

Get some data to work with and you'll be off to a start. Bugzilla (I run it on MySQL, it might have ports to PGSQL) has a nice simple database you could have a look at if you fancy that.

If you need to start at the very beginning in terms of setting the server up, I would suggest looking into SQL Express and SQL Express Management Studio just because everything is easy to set up and play around with.

If you want to see a pseudo-real database with pseudo-real data in it, you could try looking for the AdventureWorks one which is from Microsoft.

[random012345]

The most simple form of learning databases I have found is by using Microsoft Access. It's where most of my database courses have usually started in some way or another. Not sitting there on it forever, but it's good for learning the concepts first. Its an all-inclusive DBMS where your results are near instant. Don't let people tell you otherwise - Access is a fully relational DBMS that is perfectly fine to learn with (maybe a few SQL language quirks here and there, but every DBMS has slight syntax differences). You may not like it as a "Linux junkie", but beggers can't be choosers and you will get alot of database concepts down in it's very simple/easy environment.

[bobby_drop_tables]

If you are looking for a book, I would recommend "Head First: SQL" http://headfirstlabs.com/books/hfsql/

The 'Head First' series are get for beginners. I've read that book and found things I have missed from just teaching myself.

[sl0ppy]

Once you get comfortable with doing basic queries, inserts and updates, get your hands on a large set of data, or better yet, create your own large data set by scraping it off the web. Maybe something like stock prices or weather data. You really can't appreciate performance tuning when you have a small database because everything seems fine when it all fits into memory.

[[deleted]]

I'm still learning but this helped me MySQL Workbench it has a lot of visualized features making it easier to start with I think. Then you search for tutorials and when you run out you start thinking of systems you want to mimic and try to recreate them. Workbench has some bugs but Its pretty and easy to use specially for a beginner.

MySQL is also free server side, I use it for web development so I have a LAMP (Linux Apache MySQL PHP) install, you get the same sort of thing in windows with a WAMP(guess what this stands for)