Querying Data I mentioned in the last module that I'll be using the Chinook sample database, and if you haven't set that up please go do so now. You can head over to codeplex. com and look for Chinook or you could just go to chinookdatabase. codeplex. com. Once there hit that download button, and wait for a few minutes, and down it comes. What you get when you download this is a zip file and that zip file contains all kinds of information, XML even, but you have Db2, SQLite, Postgres, Oracle, MySQL, blah, blah, blah, blah, blah. You could run this yourself in your query window if you like, however, I found the simplest possible thing is to just double-click CreateSqlServer. bat. If you double-click this up pops PowerShell and it gives you a choice here, Run Chinook_SqlServer. sql or AutoIncrementPKs. We want to do number two. You don't have to of course, but inserting and updating without AutoIncrementPKs is kind of a pain in the butt, whichever one you choose is up to you, so if you hit two it's going to run, run, run, run, run, and it'll take just a second, and boom. It is finished, so let's go over to the MMC, and as you can see, I removed the Chinook database that I had in here before just to show you this example, but if I hit refresh, boom, we have Chinook, there it is.
Filtering, Sifting, and Sorting Data Once you start working with SQL you become the data person pretty quickly, which is okay because it's fun, it's fun giving people answers, and, speaking of, my boss is sending me emails right now, she wants to know some sales information, specifically about Brazil. She wants to know what the top sales for Brazil are, and I can do that because I know SQL. Because this is a report I have put together a query for her that is going to be readable with some customer information. I've had to join two tables together, specifically Invoice and Customer, so to query only on Brazil, you've seen me do this before, I can say where Country = and 'Brazil', this is a string, so it needs to be treated as such, and if I execute this we have all sales for Brazil, lots of them. Alright, but she wants to know what is the top sale in Brazil and we can do one better, we can show her all of the sales in Brazil sorted, and I do that by using order by, and then I just have to put in Total. I've executed this. You can see, whoops, hmm, that doesn't make much sense. She wants to see the top sales, but this is sorted the wrong way, it's sorted from least to most. Let's turn that around. What I think I'll do here is I will sort this in descending order and to do that I use the d-e-s-c or desc keyword, stands for descending, and if I do that, ah ha, she can now see the sales information for brazil sorted appropriately.
Many to Many and Self-referencing Relationships Let's write some queries and insert some data into our many to many tables here, and let's just start off with what you've seen already, insert into Users. I guess I shouldn't need that User to keep with the way everything is being singular in this database, oh well, old habits, and we'll insert into Email, values('[email protected] com'). Good. Then we're going to insert into Role, and in here we're going to insert into the Name field the values and then let's just say Administrator. Alright, and then finally what we're going to do is we're going to insert into our joining table. Now since we set this up to be auto incrementing I will know the Ids, they will be 1, so what I can do is I can relate these two by saying insert into UserRole, and then we're going to say UserId, RoleId, values(1, 1). Anyway, this is how we relate two records, so if I execute this you can see everything goes in just fine. Now we have a relationship, and we still have some red squigglies, which is annoying, but anyway, now we have a relationship between users and roles.