Could Use More Real World Examples

[pukster]

The tutorial for SQL is good, don't get me wrong. I just felt like I just came out of college and all of my knowledge didn't apply to the real world. It's one thing to know how to use Foreign IDs CREATE TABLE IF NOT EXISTS person(ID INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(ID),)ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS file(ID INT NOT NULL AUTO_INCREMENT,OWNERID INT NOT NULL,PRIMARY KEY(ID),FOREIGN KEY (OWNERID) REFERENCES person(ID))ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS picture(ID INT NOT NULL AUTO_INCREMENT,FILEID INT NOT NULL,PRIMARY KEY(ID),FOREIGN KEY (FILEID) REFERENCES file(ID))ENGINE=InnoDB; But it's another entirely to remove a picture if it belongs to a certain user. Also it's hard to get the person ID associated with a picture. I don't mean the row, I mean the actual text ID/name associated with a person.

[birbal]

this is the DML part of your database. You need DQL eg SELECT,DELETE to query the tables. foreign keys does not take out the data automaticaly it just states the relation between rows to prevent the data redundancy and to keep the structure intact.

[justsomeguy]

You can get the person who owns a picture with a certain ID like this: SELECT * FROM person WHERE id IN (SELECT ownerid FROM file WHERE id IN (SELECT fileid FROM picture WHERE id = 10)) You could also use a join instead of subqueries. There are usually several ways to solve a given problem, which is why they typically teach the fundamentals instead of trying to say that this is the one way to do a certain thing.

[pukster]

I understand that. All I am saying is that, from the point of view of a novice SQL programmer, the tutorials aren't as helpful as the HTML, javascript, PHP tutorials. With those, I feel they give you all the tools you need to get started. With SQL, I come away feeling like I didn't learn anything at all.

[ShadowMage]

It's hard to give "real-world" examples, because every situation is unique and could require a little different solution. Also, like JSG mentioned, there are usually several solutions to any given task. This is why they teach concepts instead of solutions.

[birbal]

I understand that. All I am saying is that, from the point of view of a novice SQL programmer, the tutorials aren't as helpful as the HTML, javascript, PHP tutorials. With those, I feel they give you all the tools you need to get started. With SQL, I come away feeling like I didn't learn anything at all.

It is not only you it happends same with me when i completed it.It was something like, I knew how to use them but i was confused when to use them. The best will be as you start implementing it in pactical. Choose any project and start with it. As more you start implementing it you will start to relate everything with real world examples.

[pukster]

Sorry for the long absence. I understand you are only supposed to teach the concepts, but look at the CSS tutorial. It gives several examples highlighting the different ways to accomplish, for example, floating elements around with the float:left/right command. The SQL tutorial I find lets you design your database (well it takes experience to design it well) but it does a bad job of telling you how to update that database. It would be like if you taught someone the language specification of ANSI C, but then didn't tell them that they had to put the code in a text file with the '.c' ending, and that they had to issue the command 'gcc'. I found I built my database, then tried to retrieve rows of data, but didn't know how to do it. For example, if I get the ids of all users with the name 'john', how do I iterate those ids?

[justsomeguy]

It would be like if you taught someone the language specification of ANSI C, but then didn't tell them that they had to put the code in a text file with the '.c' ending, and that they had to issue the command 'gcc'.

That's a good example, because they don't have to use GCC. Maybe they're compiling with Visual Studio. Maybe they're using Borland, maybe they're on a Mac and using Xcode. It wouldn't make sense to list instructions for all possible compilers, so instead it would be better to just say that you need to use a compiler to compile the code and pointing the user to other compiler resources to learn more about compilers if they want to and pick one that works for them.

For example, if I get the ids of all users with the name 'john', how do I iterate those ids?

You don't, at least not with SQL. That's where another language comes in. You can use a language like PHP to send the SQL query to the database, and PHP would get the result set back and do the iteration itself. But that's a PHP discussion, not a SQL discussion. The only part SQL plays in that is the SELECT query to get the data in the first place.

[ShadowMage]

It says right on the bottom of the SQL Select tutorial:

Most database software systems allow navigation in the result-set with programming functions, like: Move-To-First-Record, Get-Record-Content, Move-To-Next-Record, etc.Programming functions like these are not a part of this tutorial. To learn about accessing data with function calls, please visit our ADO tutorial or our PHP tutorial.

SQL is only for updating and retrieving data. It has no way to work with the data on it's own. It is up to you to decide what data to supply and what to do with data SQL retrieves for you.