LessThanDot

Hi Koen,

Is there a training kit for 70-466 and 70-467.

Could you provide some links of free sources for these 2 exams.

Regards,
Shezan

good idea...

@Peter: the principles of setting up a VM for Azure would be the same. Not sure about the domain controller though. I left it out, but you might include it for your VM in the cloud.

I've been meaning to test this out, but here in Belgium we have quite slow upload speeds, so uploading 40GB into Azure is a bit problematic.

Koen, would these instructions apply well to the Azure services using VMs? Any warnings if we want to set up a lab in the cloud?

Fabien T: Thanks for the feedback. I'm torn on the first one because I am also using the list of exercises as a visualization of the progress you're making, I'll think on it some more and see if I can find a way to get both. I like the visualization idea, I had considered using them in individual exercises later on, but having one for the involved tables all along would be useful. I'm adding the defined regions for the text block to the list too, I need to work out how to implement it without losing too much vertical space or making it too busy, but I agree some uniformity there would help break up the big wall of text and make it easier to consume.

Walter: Thanks, I have a version number in the bottom right that I stamp on from the build process, I'll add in the date as well. I've also started using the @sqlishard twitter account to announce updates, I'll do a follow-up post shortly with more information, as several people have asked for ways to stay up to know when exercises or features are added.

Philip: But if I had named it SQLisDifficult it would have been harder to type ;)

Maybe you should take a look at Vagrant. It lets you automate the setup of a Virtual machine.

Nice article, but I am often amused by people not knowing the difference between "hard" and "difficult". Granite might me referred to as hard and cotton wool might be referred to as soft, whereas applied mathemactics would be difficult for most 5 year olds to master. The opposite of "easy" is not "hard"; it is "difficult". How difficult is that to remember? :-)

Excellent work! I would suggest you put a date last updated somewhere on the page so we can tell how old the site is and when updates are done. Also, I don't mind the "raw" interface at all. All that typing is good for memory retention. Can't wait for an update to this site.

W00t! Virtualization is one of the next evolutions of the IT career, and it's amazing to be on the forefront of technology. I'm very happy to help you gain some comfort in this new extension of data science!

Hello,
I am new to the blog...and SSIS. I have tried all of the above. And I now get a file that says System._ComObject. Before I was getting one that said had the begining tags I needed (opening and closing roots). But no information. Each time I would change one thing I would get a million errors...so. Now I don't know what works and what does not. So the actual XML file I need I have a SQL script for...and the XML file comes out perfect for my needs. But I don't know how to save it and then be able to move it to a share drive, so that the information contained insided can be uploaded to another application...which REQUIRES XML Explicit format.
Can you help?
Nicole Wyscaver

This worked for the US values but I tried to apply the same logic and scripts to create a proximity lookup for Canada (I used the CA.txt file) and I got an error stating that latitude had to be in the range -90 to 90. This affects about 30% of the addresses in Canada.
How is this possible when the ranges are supposed to be -180 to 180? If anyone knows why I am getting this error, please let me know. Thank you.

@Sas: sure. You can take the exams in any order you want. However, you can only get the MCSE certification if you also get the MCSA certification.

So taking only the 70-466 exam without doing any other exam is pointless regarding certifications.

Hey.. Congratulation..
I want to know can I directly write 70-466 exam without writting 70-461,70-462,70-463.

Nice idea, and a clean/cool realisation.

A few more ideas/comments that come up to my mind after a few moments with your app:

----

1/ The menu on the could easily be hidden.
Once you start on an exercise, maybe you'll follow one after another, no need to have that menu in this case (maybe just a reminder that pops up to see where you are, but no need to eat a third of the screen)

----

2/ Let the user have a look at the model.
Present the user with a graph representing the model, give an overview and sample data.
Without a clear context, exercises become unecessary hard, and a bit too abstract.
A picture is worth a thousands words
This could also be a great opportunity to introduce data modeling, how and why we need relations between tables (eg: for users coming from excel/access who tend to put all data in a flat format)

----

3/ Make instruction clear.
The text block is a bit too uniform.
User needs to see clearly the 3 sections:
- Explanations
- Example
- Exercise
Maybe use some folding panels to make a clear separation (although it woulb be cool to be able to see all three at once)
Emphasis on the text elements that matter in the exercise summary (use bold text and/or color to highlight table names and column names, much like Zelda games)
Following point #2, exercises could also provide a sample of the expected result data, this could be really useful for LEFT/RIGHT joins examples (eg: for an hypotetical RIGHT JOIN exercise, show all rows resulting from the LEFT JOIN exercise, and rule out the rows that are filtered out, helps the user spot the difference, what it really does)

----

Keep up the good work, that's a nice initiative, and a useful quick start for users new to SQL

Note: maybe change the name, SQL is hard indeed, but haters gonna hate ;)

Alex, I haven't attempted that change before, so I'm not sure what to tell you to change. Again, I'd suggest making a backup of the config file, making changes, and testing it - in a development environment first, of course.

chris: Thanks for the feedback :) I don't know anything about submitting to code academy, but there were a couple reasons I built this on my own such as not requiring registration.

4.0 does seem to be causing a lot of problems, it has the next highest error rate after 1.0 and 1.2 (which I just modified last night). 2.6 seems to be getting a pretty high rate of success (90%), so it's lower priority to tweak at the moment.

At least for now I am not setup for taking submissions, but given the amount of feedback I've gotten, I may setup a feedback site to accept submissions of both new SQL statements and other features like the certificate and training ideas. Still waiting for the morning coffee to brew, so I'll let that one simmer for a while.

I hadn't seen plsqlchallenge, thanks :)

Really nice. I really enjoy the codecademy stuff. Can you submit this there?

On S2.6, your use of the word 'plus' in the question is a little confusing.

On S4.0, (as an old sql guy) I can come up with lots of answers for the question that generate the right result, but it took me a while to get the "right" result.

I assume you've got some sort of database with questions, and answers. Here's a million $ idea for you...enable some framework where i can submit my sql questions and answers and license to companies that may want some special training over and above what you offer.

Along with the previous suggestion, I'd be happy to help devise questions and answers. I'm primarily an Oracle guy, so i know there's a slightly different flavor as statements get more complex for SQL Server, MYSQL, etc. There's lots of room for growth with stored procs, functions, triggers, indexes, dba stuff...etc.

Anyway i can print a "Completed" certificate i can show my boss or hang in my office?

Have you seen http://www.plsqlchallenge.com/ ?

Keep up the good work. Let me know if you need help with any parts.

Thanks for the detailed feedback David. I've noticed several people had issues with S4.0, part of that is likely because I skipped a few intended exercises (4 is the first multi-table one and it helped me solidify what the sample tables would be and the change deployment process for generating sample data for multiple tables).

I'm not set up to accept pull requests or other forms of assistance at the moment, a lot of this is still coming together. Up until yesterday the exercises were still hardcoded into the application, but I already have some ideas on offering helpful tips (which I think would fit #1 and 3).

I like the idea behind 4 and agree the explanation box is a little limiting, but at the moment that's also forcing me to keep these simpler. For those of us that have been doing SQL for a little while, this set of SELECT exercises will move slow, when I get some more advanced stuff in there that constraint may stop being helpful.

Great start!

This could turn into a very useful noob* tool.

When the more advanced topics are created, this site will also be a refresher for the experienced amateurs like me (I still need to look up INNER/OUTER LEFT/RIGHT join behavior when constructing an advanced query).

My suggestions.

1) Add a prequel to the lessons already there. Just simple, cut and paste, working queries to show results. That will give the low-confidence, "I have never seen SQL before" types immediate feedback.

2) In lesson S4.0, you jumped right into the full column name (table.column) syntax without warning. If you don't already intend to, you really should have some single-table lessons that gently introduce the col name variations.

3) Along the lines suggested above by others, maybe a "Give me a hint" button can activate after several failed attempts. And maybe an "Okay, I give up" button after several more?

4) How about a "learn more" button/tab that displays a much more detailed discussion of the lessons topic? As the topics get more complex, I don't think the little "instructions" box will work very well.

<sarcasm>
5) To pacify all the "SQL is easy" haters above, maybe you should re-brand this to "www.nontrival-sqlishard.com"
</sarcasm>

All in all, a great tool.

PS - Are you are interested in outside (free) help on this project. I might have some time to contribute. (If so, I assume you can get my email through this posting).

* I say noob with all respect. We are/were all "noobs" on some topic!

3 days more to go, can't wait for this event and learn new things regarding data visualization and more.

Thanks for the additional feedback everyone, I've been out of town so I apologize for not getting back to everyone in a more timely fashion.

I'm going to make some tweaks to the application and my priority list based on the feedback. I also need to see if I can duplicate wazz's issue with needing to scroll, so far I haven't run into this issue int he resolutions I have tried, once I can duplicate it I'll tweak the CSS to scale better to that size.

Nice!

The SQL that is readily accessible via a query-by-design interface is also the SQL that is fairly easy to learn. For more complicated queries while you may be able to use a GUI interface to build one it is much faster to do so explicitly in text.

I don't really have much to comment generally on this tool since I am not really its target audience and I haven't given much thought to trying to teach others SQL.

Given a database and a desired result figuring out the needed code (SQL or otherwise) is going to be fairly doable - performance requirements not withstanding.

Instead of simply trying to teach people SQL it would seem to me that trying to teach people to solve data problems in a specific domain would provide a better approach. Think "word problems" in math instead of solving simple formulas. Creating a list of formulas to solve may help teach the mechanics but story problem solving is (I say this without proof) better for actually learning and remembering those skills in a meaningful way.

This is a great idea and I applaud you for it. When I've trained juniors over the years (usually helpdesk people who showed an interest) I've sent them an introductory ebook I've had for years. I will still do that but then send them to your project. Great work!

To those commenting that SQL is not hard, I would say that simple SQL is not hard, but real world applications of SQL are rarely simple. Most people try to do things with loops or "row by agonizing row" as Jeff Moden calls it. That is easy but it's the wrong way to do it. Being good at SQL requires truly understanding set theory. It's a serious branch of mathematics that underpins relational database theory, and if you think that's easy as a beginner then you're either a genius or an arrogant fool who has barely scratched the surface.

Beyond the language, when you start really working with SQL you will find that every database you come across is completely different and you'll often have to reverse engineer the tables to create your own entity relationship diagrams and wade through stored procedures, triggers, functions, ORM code and sometimes front-end application code with inline SQL etc etc just to understand why it was designed that way and how you can work with it. You'll also end up having to work with different vendor databases such as Oracle, postgres, MySQL, sybase, pervasive, SQL Server, DB2 and now things like HIVE and they all have their quirks and non-standard implementations of the ANSI standard SQL. Then you'll be using procedural extensions to the SQL dialects like T-SQL and PL/SQL and it goes on.

There are no easy languages, they all take learning, practice and experience to master and any interactive learning tool like this is a blessing to those just starting out.

Nice!
Btw, to those buffoons who write that SQL is not that hard (and can be learned in a day! LOL), please list your websites or who you work for...so that I may never, ever entrust my credit card or private information to you or those you work for; like Dan Sutton ;-)

Keep up the good work.

Not sure, Denis. But I did go to the right URL. ;-)

I'll try to check again later. I have had my account for a while but didn't start actively using it until a couple of years ago. So maybe it's for more active users?

Yes, and yes again, Eli. I would gladly recommend this to friends who are interested - I get asked fairly often for good resources like this. I would also gladly pay a nominal fee for this kind of resource.

Updated this post with the Followers screenshot

I'm afraid I have to disagree with Jerome. Sure, you can use Access to generate simple queries and even basic joins but it is very limited for doing anything more complex than that. There are other visual designer tools that do a better job but even they have serious limitations. Some things you just have to code by hand. I have never found a visual query generator that can even approach things like CTEs or even subqueries.

SQL isn't hard because it is bad. SQL is hard for some people because the underlying concepts of the relational database force them to change their thought process from procedural to set based. The SQL language is a (mostly) elegant solution to handling that paradigm.

I applaud this project - what a great idea!

Ummm... how is SQL hard? It's just about the single simplest not-even-a-language out there...

I used to use a lot of SQL in Visual Basic. What I discovered was that you should never write SQL. Instead, go into Access and use the Query By Example (QBE) tool to drag and drop links between columns in tables. Then copy the generated SQL as text. It is very easy to understand selects and joins if you use the visual interface.

What this says to me, is that SQL is hard because SQL is bad. The language obscures what it is intended to convey.

I love this idea, thank you! Keep it up!

[no need to publish this comment.]

excellent. i would point people there.

couple design things:
- "let's get started" on home page MUST be bigger and more obvious. giant button.
- remove some gray-space between exercise and query designer and/or make designer a tiny bit shorter.
- move the "excellent, let's continue" result to the top of the designer. it might wreck my previous comment about saving space, but because i had scrolled down a bit to see the entire designer, i didn't see "let's continue" come up when i finished the exercise.
- possibly add some css to the 'execute' btn. i didn't see it at first. no biggie.

> i think i have a pretty average-sized monitor and i have to scroll about 1 inch to see everything; annoying to have to constantly scroll a tiny distance just to be sure i'm not missing something at the top or bottom. just a few tweaks here and there (slightly narrower left column could help) and scrolling would be gone. (i know there will prob be scrolling sometimes for longer exercises, etc.. i would keep the designer visible at all times.)

Interesting, a co-worker doesn't see the analytics tab either. I wonder if they are doing a staggered rollout. Maybe the people who signed up earliest for twitter get it first, or maybe it goes my number of followers or tweets?

This is a great idea that I would definitely point cow-orkers to. If this expands with more complex examples it would be great for everyone. Is there a chance that we could submit lessons or does the method to create the examples not allow for easy submission?

(it's not "along road," it's "a *long* road.") ;)

Koen, maybe it is US only for now?

Has Twitter started allowing ads like on Facebook. Thanks for the info on the analytics tho

Nice job! I could definitely see this being useful.

One thing that might be nice is a sign-up form for people who want to know when you get a chance to add more content.

JJ, Chris H, Elias: Thanks, it's comments liek that that will give me the energy to open this up on work on it on a Saturday morning :)

Earlier Chris: Good idea, that format for dates is how I write them by default now, so I didn't even recognize the potential for confusion, I like the solution of using the 30th. On the other exercise, I've seen a couple people have similar issues with order by statements I wasn't expecting. Currently I verify records with the assumption that they are going to be in the order I get when executing the solution statement, but maybe i can add an order-less comparison. You would still be able to get a false positive success if you managed to do an incorrect query that returned the right number of records and the 1st 100 were in the answer key, but that seems like i would be hard to do accidentally.

"no" and "lol": There is a lot more to SQL than just a few commands, being able to use CROSS and LEFT joins well can significantly simplify a lot of logic, windowing functions like ROW_NUMBER can collapse statements that used to require a lot more work (or application processing), query tuning (which I hope to get to at some point) is not at all simple or something you would learn in a day. After learning some basics it is easy to fall for the trap that we know everything there is to know about a subject, hopefully you'll come back in the future and be open to trying some of the more advanced exercises (provided, of course, that I actually get to those :P).

What about international characters - solving this problem for 7 bit ASCII is easy.
How about this:
DECLARE @S_INPUT varchar(20) = 'þÆ.er8tY99öx#mÍx'
DECLARE @S_LOWER varchar(20) = LOWER(@S_INPUT)
DECLARE @S_UPPER varchar(20) = UPPER(@S_INPUT)
DECLARE @S_I varchar(20)
DECLARE @S_L varchar(20)
DECLARE @S_U varchar(20)
DECLARE @I int = 0
DECLARE @L int = 0
DECLARE @U int = 0
SELECT @S_INPUT
UNION ALL SELECT @S_LOWER
UNION ALL SELECT @S_UPPER
DECLARE @S_LEN int = LEN(@S_INPUT)
DECLARE @COUNT_OTHERS int = 0
DECLARE @COUNT_LOWER int = 0
DECLARE @COUNT_UPPER int = 0
DECLARE @IDX int = 1
WHILE @IDX <= @S_LEN
BEGIN
SET @S_I = SUBSTRING(@S_INPUT, @IDX, 1)
SET @S_L = SUBSTRING(@S_LOWER, @IDX, 1)
SET @S_U = SUBSTRING(@S_UPPER, @IDX, 1)
SET @I = ASCII(@S_I)
SET @L = ASCII(@S_L)
SET @U = ASCII(@S_U)
IF @L = @U
BEGIN
SET @COUNT_OTHERS = @COUNT_OTHERS + 1
END
ELSE IF @L = @I
BEGIN
SET @COUNT_LOWER = @COUNT_LOWER + 1
END
ELSE
BEGIN
SET @COUNT_UPPER = @COUNT_UPPER + 1
END
SET @IDX = @IDX + 1
END
SELECT @COUNT_LOWER AS 'Lower count', @COUNT_UPPER AS 'Upper count', @COUNT_OTHERS AS 'Count others'

So far, two people that left comments without reading the post.

It's not that hard...
One has to master 1 command ...the SELECT, the other 3 are trivial along with the rest of the syntax.
first learn SELECT...WHERE , then how to JOIN, then GROUP BY and then OVER and maybe some kinky advanced stuff.
Overall it can all be learned in a day i'd say.
Then it comes the database management and administration which is another stuff, but if you do not design or administrate dbs, just knowing SQL to work with data is not all that hard imo.

It keeps insisting I should remove adblock and won't work.

Or disable it.

A great idea and the start you've made is well executed.
I look forward to seeing it develop more - and then learning more SQL from it, myself!

A really worthwhile project!
Thanks

Signed into ads as well, but also no analytics for me. Sad trombone :(

If you need compressed SQL Server backup you can use SQLgzip. It supports all SQL Server versions starting from 2000.
Free fully functional version (32-bit) supports compression and TSM. (download: http://sqlgzip.com)
Additionally you can specify compression rate as standard gzip or zip protocol are used.

You singed into https://ads.twitter.com/ not just twitter right?

Interesting. But it's worth noting that I do not get an option for Analytics once I sign in.

Great idea for beginners.