The SQL Thread

Post Reply New Topic

I'm starting to verse myself in mySQL - note just starting. I have a very preliminary understanding of databases from my Access course and from playing around with OOo Base, but I thought I would expand on that since I love datamining. I used to do it by hand and paper but now I'm trying to do it by computer.

Anyways, as I leaf through the tutorials, the query function seems a lot less intimidating than Access or Base. But maybe because those things first verse you on the tables more so than the queries as opposed to SQL gets you on the queries first and then the tables. Fair enough.

Anyways, I trust that there are other users out there?

Hi Xelebes,

yes, sure, here are quite a lot of computerfreaks and people making their living by programming (like myself)

Sorry, my english isn't good enough to get the details of the second paragraph. I always understood, that Access is queried with SQL too. Actually I wrote quite some SQL on Access databases. So there shouldn't be any difference.
Or do you mean those neat graphical user interfaces of Access, that allow to click or dragdrop queries? In the end they all generate SQL anyway. But it's far better to be able to write SQL by hand. There are things that get extremely complicated with a gui interface. I oftn started with the Access tool, then took the generated SQL and changed that to the form I finaly wanted.
This is especially important, if you want to change some values in the SQL at runtime, based on some values the user enters for example.

Anyway, I would recommend to learn a programming language as well, since it's more fun and allows you to do a lot of things that SQL can't handle. Maybe have a look at Ruby, which is a great language.

Yes I have done for yonks. I have always loved SQL, db in general. mySQL is definitely worth learning. It is widely used and not difficult to learn others like postgreSQL.

Access isn't really a viable backend for anything outside of a small office app. I left it long ago.

SQL is pretty intuitive. I take it you are already reading tutorials on mSQL. Also refer to mySQL docs.

Additional advice/pointers I could give you:

Learn a bit about SQL standard and then you will be a cut above the average Joe. SQL standard is the blueprint on which the various SQL syntaxes are based on. Because SQL is so initiative, and works very effectively, all the SQL flavours tend to follow it quite closely and will refer to it in their docs so look out for this (blink and you miss). It makes switching databases easier. The best ones don't mess up what shouldn't be messed up. However all but the leanest ones tend to have additional functionality often called 'propriety extensions' which doesn’t interfere with the bog standard RDMS, but are sometimes useful. It is simply worth knowing where your flavour is different from the standard, and why. It can be for good reasons. This is something that tends to come over time, so I wouldn't worry too much now if you don't get everything. There are some common sense recommendations in SQL standard that are easy to break, if you are not aware of them and sometimes you do want to break them.

I definitely agree with PilotPirx about learning a programming language to complement. However some programmers really don't get db fully and don't particularly care. So there is really nothing wrong with learning your onions so to speak. One thing, I'm sure you will appreciate in due course is, whist SQL is fairly elegant on its own it is really kind of messy having chunks of it embedded in object based applications. For this reason the came up with something called object relational mapping, a really intuitive way to map a relational data source to an object 'model'. It is not the same as object oriented databases which have only been moderately successful, but it is a binding which handles much of the SQL behind the scenes. One of the most well known ORM implementation is ActiveRecord, which is available in Ruby and Ruby on Rails. When it implemented with it’s buddy migrations it is fairly database neutral and included the binding to various database types, with new ones being created all the time. It would be a good idea to learn ORM and SQL together. ORM is very powerful but not totally perfect. Having an idea of the underlying SQL will serve you well, because you will be inserting the odd snippet but may choose to optimise thing in places, possibly feeding whole SQL statements if necessary.

I have very few headaches with mySQL. Especially not on the query side, even with fairly complex problems if you don’t enjoy coming up with a solutions then clearly there is something wrong with you. j/k It is fun, sure beats trying to find a bug in masses of spaghetti code you weren’t responsible for. I get a similar enjoyment out of pattern/expression matching, figuring out various file specs, etc.

No particular reason for mentioning this now but there are things that db does well internally and things that can be handled better with external plug-ins. For instance many of the dbs don’t have the greatest fulltext searching. You may choose to use search engines such a sphinx later on when you have a good grounding,

Last edited by 0_equals_true on 05 Dec 2008, 9:48 am, edited 1 time in total.

Oh if you get stuck with anything let me know will try to help.

Very good post 0_equals_true.

Yep, but it's always the lack of time. We use Ruby on Rails for web development. So most of our queries use ActiveRecord finds.
But sometimes we get stuck and have to use plain SQL. Most of the time I get things running, but for some tricky things we fall back to load records and then sort that out with Ruby code. Not optimal, since the databae should do things better and faster, using it's indexes.

So: Do you know any really good SQL books, that cover more complex queries? Most of those in book stores stop where it starts to become interesting. Maybe mentioning some things like subqueries, but missing the important things.
Another question often left open is the database design as such, especially where to place indexes.

I don't have any DB training, but I basically learned SQL working in MSN. Part of my job there was building N-million user replicated databases and running performance and security tests against the back-end.

I've always been under the impression that you use formal indexes when your queries filter on a specific field. If you don't filter on it, no need for an index. The only time I could see an index slowing you down is if you have a database with a large percentage of insert operations since the index would have to be modified each time.

Today, I have a bunch of Access databases that we use in our small office from a web-based front end. We use Access because it's easy to deploy and replicate. Migrating to a more scalable product shouldn't be much work - the main difference is opening and closing the database on each web page. If you put that code in an include file, you should be able to change those two functions and move the entire web site to a new database system.

Eh, for code I haven't had much help in learning one. It often feels like I need swomeone to teach me code. For databases, it's much like using a spreadsheet and not too different from that. Closest I have come to actual hard code is music sequencing in a tracker or in Q-Basic (oh the days on MS-DOS.)

Well whatever learn mySQL first then, nothing wrong with that. I got into programming through my obsession with CAD, I wanted to automate stuff. If you can learn mySQL you can learn any programming language seriously. mySQL even has accreditation. Whether or not the accreditation is recognised/you actually require it...probably not all that much but if you learn it you really know you stuff (especially if you get up to cluster level). I looked into a while ago the tests have a high fail rate, because experienced programmers go in and think they are going to pass with little studying but they really don't know the details. There is a difference between a programmer who has a basic understanding of SQL or the mysql admin and looks up the rest AND somebody who really knows what they are talking about. I took some mock ones it is a good way to see where you are.

Err...please don't say that db is like spreadsheets :cringe:. I'll let you off that one, honestly.

No it really is. Just that you're setting up tables, as you might in a spreadsheet, but with a whole lot more functionality.

Just saying that is how I look at a database - bunch of spreadsheets made into tables for referencing.

Cheers. I don’t profess to be a (my)SQL guru, I just learnt some things over the years. Don’t get me wrong, there is nothing particularly wrong with ActiveRecord in fact it is great, and it is doing what it aught to on the most part. Also sometimes the efficiency margin is exaggerated. Things can be a bit counter intuitive sometimes with efficiency and databases and it varies quite a bit. So it is not always like that. Yes there are several areas where ActiveRecord record does go the long way round, but it is more a question of whether it is causing problems on a realistic load. Knowing a bit about the query optimizer will help diagnosis Abandoning ActiveRecord all together would be silly. Besides it handles some things like common de-normalisation tasks (like counts) better than most people would, and that is what code is for. It handles transactions well too. ActiveRecord is binding specific so there may be an adapter that can handle things better, such as knowing when code beats queries and you can always extend with your own custom query objects. The important thing is that the abstraction it offers you is worth it, which it usually is.


That reminds me @Xelebes you need to know the difference between ‘normalised’, ‘not normalised’ and ‘de-normalised’. V important.

Tbh I don’t really know about books. I learnt it like most people did, along side programming, except I kept coming back because I was interested in a more than rational way if you get what I mean. Are you only talking about SQL in general or are you talking database specific? I did a quick search:

->The Art of SQL seems to come highly recommended. It is a pretty advanced strategy based book.

->SQL Cookbook might be a good starter for going beyond basic level.

Books on optimisation are pretty controversial a. because they either don’t really want to dob anyone in or have allegiances, b. it can get pretty contentious and is always subject to change. It is better to learn how to asses your performance, rather than what somebody tells you c. it might not be very useful for your case.

Personally my approach would be to choose database that has the functionality you need and then try and get the best out of it. There is nothing wrong with mixing engines if it suits you. ‘External indexing’ is a bit of a misnomer. A database engine could be designed in one way and have really good fulltext indexing system included, but that is more a question of what they are willing to support, whether they will give it their stamp of approval. How it works is another matter. Some extensions may choose to integrate seamlessly, others you are going to have to tell it what to do, but that doesn’t mean it isn’t worth it. So it is always worth checking out what extensions are available.

I did download the ->mysql cert study guides before. Was pretty good (even if you don’t wish to do the certs, I haven’t yet). Not all of them are on development side there is the admin side too. You can skip what you know and go to the area you don’t. It definitely covers the specific of a particular area in detail, because that is what it is for. So you might want to look at this first.

Be wary of alternative study guides cert or test simulations. There is nothing like the actual test, the official book doesn’t give you answers to everything on the test. Mock tests are usually very poor.

For subqueries read the docs:
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html

Subqueries didn’t happen one day, they were a bit of an evolution. I remember a time when subqueries weren’t standard. Subqueries in the FROM clause were treated like another thing altogether and were called something like ‘associative tables’ (can’t actually remember the exact phrase) and not called subqueries (as that would get people too excited), but now they are lumped in with the various form of subqueries. Anyway somebody wanted a complex solution to do with some recruitment app. I was used to using a version of mySQL that supported subqueries, his version didn’t. I exploited a particular quirk in his version of mySQL which doesn’t work in later versions that enabled me to do something where you would normally have to resort to subqueries. It worked really well, I was kicking myself. It was quite specific to the problem though. For things like subqueries it is worth looking and the optimisation section of the docs. Sometimes a subquery can be faster that a join, some times this the other way round, etc. It varies quite a bit that is the short answer. As you will know, just because it is in one statement and looks good doesn’t mean that the query optimizer isn’t going to break it up and do multiple tasks.

I understand what you are saying, however I wouldn't think of it that way. I might be better if you think of tables as a type of data storage/reference abstract that is sometimes visually represented in a way that superficially looks like a spreadsheet. Spreadsheet programs have their own abstract and the spreadsheet is the interface. It is not a question of functionality, spreadsheet programs and relational databases are good for different things (granted Excel also has limited quasi database functionality but it not comparable). Spread sheets do have their own dataset but are different from typical relational databases, but both are useful in data mining. Spreadsheets work more like interactive legers. If you change one cell in can have and knock on effect on others. This works particularly well with snapshot calculations, which is why it is not uncommon for databases to export to a spreadsheet a snapshot and then create chart or report. In spreadsheets each cell can be a separate type and mean anything you want, there isn’t so much inherent relationships. You can put a number of different things on a single spread sheets, it doesn’t have a strict convention. Normalised databases don't hold what can be easily calculated. Spread sheets can do because they are a visual representation/interface. Relational databases are about relationship between tables such as an order has many line items. So the is a relationship between the orders in the order in the orders table and its line items in the line items table. That is a ‘one to many’ relationship, one of the possible associations.

A spread sheet wouldn’t bother to define all the empty cells only the ones you are using and the relationships between cells. Therefore the file spec for the storage of cells and the spread sheet interface are different things.

SELECT [SMART.ASPIES] FROM [WRONG PLANET] INSERT INTO [COMPUTING JOBS]

I'm actually also beginning to learn SQL (though the servers we have are running MSSQL 2000 and MSSQL 2005 so I'm not learning MySQL). I looked at it the same way originally. I thought of it as a spreadsheet containing data. And that's how I actually did a lot of the data input. The spreadsheets already existed I just imported them from excel into SQL.

However the more I get into the more I see the differences between excel. And I'm starting to hit roadblocks. I'll probably be taking an SQL course at NAIT soon.

You going to NAIT? Cool, me too.

Yeah and the courses I've been looking into directly relate to what I'm doing at work so I think I can get the company to pay for them.

The following web interfaces can be used to control mysql.

webmin and phpmyadmin. Both are good to setup and fix mysql databases.