Programming Throwdown - Query Languages

Episode Date: October 28, 2012

This show covers Query Languages, programming languages designed at accessing data from databases. Tools of the biweek: Pixen and Downcast ★ Support this podcast on Patreon ★

Hosting provided by Host Tornado. They offer website hosting packages, dedicated servers, and VPS solutions. HostT.net. Programming Throwdown, Episode 20, Query Languages. Take it away, Jason. Hey, everybody. take it away jason hey everybody so i uh i have a decidedly lack of you know knowledge in economics right i mean when it comes to like real uh economics like the equations that go macroeconomics microeconomics i think this would be called uh microeconomics so it's like the strategy behind

why people do things like a little bit of game theory, like on the border there. Right. And by the way, by the fact that you said that, I think you just contradicted your first statement. Really? I think that now you've already shown like you were able to determine like what parts and talk about game theory. Oh, I don't know anything about this. All right, go ahead. Well, I may, well, let's just see where the story's going. But basically I, you know, they were talking about at work, they were talking about probability distribution functions and how, like, the distribution can change and now priors and all these things that, like, I had no experience with. Yeah. Okay. So, but statistics in terms of economics, which is, you know, the study of rational people making decisions, right, which is ultimately.

In theory. Yeah, exactly. Which is ultimately... In theory. Yeah, exactly. Which is ultimately what everyone's talking about. And I really felt like I was lacking. So I looked up probability distribution function, probability mass function into book search, Google book search. And one of the first results was economics for dummies. And so I was like, well...

And I read the like few pages that it gives you of quota or whatever. Before you had to hit the paywall? Yeah, exactly. And I was like, oh, this is, you know, it's a dummies book, but it actually is exactly what I want. It covers everything. And being an economics dummy,

I seem like the perfect audience for this book. But then I was like, you know, I want to read the book, but I need it like immediately. And so I was going to order it on amazon things like that but i really needed it like right now i needed it basically in an hour and i was at home so i decided you're really wanting to show show off to your friends at work huh i must learn this overnight i i legitimately i'm gonna go to a bookstore and look for this. Sorry, go ahead. So I went on, you know, our good friends in Sweden who sail the pirate ship. And... The story's getting worse.

I did buy the book, everybody. So the book is on order. But in the meantime... And then I thought, you know... So I looked for a dummies book or whatever. And the first was 547 dummies books. And it was,47 dummies books. And it was like 7 gig.

And I was like, I got to get this. I mean, like anytime you see like a giant corpus or something. I'm face palming. Just for people who can't see. People who can't see. Patrick is just completely buried in the machine. I am not condoning this behavior at all, Jason. No, do not do this. You have a friend who did this.

You did not start this story correctly. That's right. You know this horrible, immoral friend my mate dave don't use names so um so so among the books these are real books that are printed i think addison wesley publishes them for dummies there is pimping for dummies a real book no i i'm not kidding a real book pimping for Dummies, a real book. No. I'm not kidding. A real book, Pimping for Dummies. But there's also like France for Dummies, you know, et cetera. There's tons of these dummies books, you know, Electronics for Dummies,

Quantum Mechanics for Dummies, Pregnancy for Dummies. Like that one's probably not necessary, you know. Pregnancy for Dummies should be an empty book, right? But I found one that really resonated yeah so i'm pretty sure this is not true i mean this is a fake book pimping for dummies this is not real oh well they did it in the style of the dummy so somebody must have somebody somebody faked it somebody inject i can actually send you this pdf no i don't want to be a part of this no no don't do that i'm stating now for the record

let the record show the record clearly states patrick does not want the pimp i'm gonna look for it on amazon if it's not amazon it's not real okay someone must have injected this book but um yeah basically uh i found there's hacking for dummies oh nice which is apparently related to pimping for dummies a little bit of everything for dummies oh the little bit of everything is pretty awesome

it's actually chapters taken from other dummies books and seriously like click on that the first chapter is like DOS commands and the second one

is dating or something check this out do you have it anyways I'll keep going look inside so the one that here let's take a look just keep talking or something. Check this out. Okay. I'll keep going. Look inside. The one that... Here, let's take a look.

Let's keep talking. The one that really resonated for me among all 500 of them was negotiating for dummies. For some reason, I really... I guess it's because we had actually been talking about negotiating just coincidentally. I mean, some friends the day before.

I started flipping through this book and i honestly had very low expectations i mean first of all i i you know didn't really expect to get a whole like economics for dummies like for you to be interested in economics you have to be a little bit of a geek right so the dummies is sort of like a paradox but in the case of you know negotiating for dummies i really thought there would be just really dumb low level like like like you know luddite negotiating for luddites right but but it actually was phenomenal um it starts off the very first section is you know for you to be able to negotiate you have to have like a sense of direction in your life and you have to

sort of like know where you're going and that like kind of made sense it's like if you're just getting sort of carried along by life and you don't really have like you don't have like a like a direction or something you want to go to and it's hard for you to really negotiate and so it didn't start with anchoring or don't be the first one to say your goal so yeah it is exactly right it didn't start with any that started with like evaluate your life and that that kind of threw me for a loop is this another fake book no this is totally then it got into pimp now okay but it uh but then no it got into and so it explained cases where you so for example um the you know don't make the first move right

it said oh you know a lot of people know know, don't make the first move, right? It said, oh, you know, a lot of people know this, but they accidentally make the first move anyways. And they gave an example. Like, let's say you interview for a job or you're applying for a job. They'll say, you know, put your current pay at your current job. Just leave a blank. Yeah, you don't have to put that. Just put nothing. But then they'll ask you.

But you just say, oh, no, thank you. Yeah, you just say, to put that. Just put nothing private. But then they'll ask you, but you just say, oh, no, thank you. Yeah, you just say, look, that information is private. Because by saying what you made your current job, you're effectively making the first move. Like they can guess at like a percentage increase of your current salary. But not to be off track, that's already somewhat. Not making a first move is hard because like if you go to a car dealership, you've made the first move. You showed up at the car dealership. You've already tipped your hand that you're interested in buying that car.

As much as you want to feign disinterest, ah, I might buy this car or I might not. You spent your time on time you're not working to go to this place and talk to this person. You've tipped your hand. Or interviewing for a job. If you've bothered to fill out an application form where they had these things, you're already showing that you're somewhat interested. So you've already tipped your hand at some point but what i guess the point is don't tip it more than than you have to like be careful about your information and you might you know you might be willing to work at a place but only for salary x right and

then they have no way of knowing that that's there's that part of it too right so if you give your current pay let's say you give your current pay, they figure like 99.99% of people would take double their pay. So like that's already information that they know that they wouldn't have known if you didn't put that pay, right? So this is really interesting.

They also covered two things which I thought were really important. One is set limits before you start anything. So before you start negotiating, set a lower and an upper bound, if appropriate. So, you know, clearly you don't want to set an upper bound on your pay. It doesn't make any sense, right? But, you know, set a lower bound, right, or an upper bound on the price of a car, et cetera. Sometimes you need to send a lower and upper bound.

Or it could be multidimensional negotiation. You should set bounds on all dimensions. And then stick to those the whole way through and remind yourself why you set those. You know, don't let yourself get caught up in the context of the negotiation that, like, you slip on your bounds, right? The other thing is based on the gravity of the decision at every step take a certain amount of time like regardless of any external pressure so for example if you're buying a car yeah they don't like that by the way no no so these are things that the people you're negotiating with will hate you for but these are these these really matter so if you're buying a

car a car might be let's say like several, maybe a year of your salary, right? Or, like, six months of your salary, some X amount, right? So at least take three days or two days. Every time there's, you know, some shift in the negotiation, take three days and think about it. That seems long. I mean, that's basically it. So, like, when I bought my last car, what happened was we negotiated we got to like a good price or whatever i said i'm gonna go have dinner yeah i did not like that answer like no what i'm gonna put you in it now

like the car's gonna sell like all this stuff right i'm like maybe it will yeah i'm like i'm gonna go buy dinner and i'm gonna eat dinner and i'm gonna come back and he's like no no no and i'm like fine like whatever you say like i'm not and then you try to come back. And he's like, no, no, no. And I'm like, fine. Like, whatever you say, like, I'm not. And then he tried to lower the price, right? Like, oh, it was great. Like, I'm writing this down. Like, okay, this is a lower price. Yeah, yeah.

But then I did. I, like, went to dinner and ate about it, talk about it, think about it, clear your head, be away from the high pressure, and then go back. You know, and then just. And just a real-world example. So, obviously, this was before I read the books. This was years ago. But when I went to buy my wife's engagement ring, you know i looked at a bunch of three years salary is that no it's like

three months or something anyways oh yeah yeah you're right yeah if if my wife is listening several years of salary so so um infinite if you're making no money because you're in school. Very easy. Oh, that's true. Good point. So, you know, I looked at a bunch of rings and I said, you know, I did the right thing, which is to think, you know, spend a couple of days and think about it. And over the course of a couple of days, I realized that I wasn't making a good purchase. Like I had like buyer's remorse, but without buying, right? And so then I went back in sort of with a clear head all over again. And I up saving I think it was like two or three thousand dollars it's like significant you know so I yeah yeah it's pretty

when you're buying a million dollar ring Patrick you don't see me sliding money over to Patrick right now. I'm putting 20s in his pocket consistently. But yeah, the book was Negotiating for Dummies. I wouldn't say it's the best negotiating book because it's only one I've read. But in general, knowing how to negotiate is very important. That's good advice. So did you learn about economics? No, of course not.

I was reading about negotiation. No, I did no i did a lot of good economics books it used to be i have like a i like learning new things so i'm constantly changing what i'm interested in so i was interested in economics for a while so i did do some amount of like like online coursework type stuff you know i took micro economics in college and read some books there's like freakonomics was popular that's kind of like short story it's really easy to read it won't teach you about all the economics but some of the kind of that rational thinking how that doesn't always lead you where you think um yeah there's other other books as well so there's a lot of good books out there about that yeah so i mean to answer the question i i you know had to study like bayesian

probabilities and things like that you keep keep saying statistical things, though. Yeah, but I think that there's a high correlation, right? Yeah, I mean, there's a lot of overlap. I guess when you say economics, I think more of like even microeconomics, right? The supply and demand and, you know, pricing and elasticity or inelasticity. So, yeah. I mean, those are like, I don't mean like the statistics are important important as well and i'm sure economists have to be very strong mathematicians for the most part you know um but i i just tend to think more of those or like you said behavioral aspects versus you know it sounds like you approach it for much more of a quantitative yeah exactly yeah

so i needed it for like for like some very specific things like with respect to like you know equations i was using at work and things like that. And so I think that statistics for dummies probably would have been even better, but I don't think that exists. No, no, no. Not down to your choice. Economics is interesting. But yeah, yeah.

So I mean, I am interested in the other. Actually, one thing which I happened to skim across was a Dutch book. Have you heard of this? Like the language? No. So a Dutch book is when you can set up a set of bets in such a way where you make money regardless of the outcome. So, for example, let's say like you're willing to pay me three dollars to one three to one odds if obama wins the election and i find someone else who's willing to pay three to one if if uh

mit romney mit romney i didn't know what you were doing okay you forgot the name romney wins the show so much i'm interested in politics right but uh so so if i have two people who are each willing to this is a bookie this is i mean the whole goal of like a las vegas casino in a way well not no no that's much of las vegas because they don't have sports books so like a bookie there's a whole idea right like he tries well sometimes he'll front the money but a lot of times i don't know i don't know that much about it never having gone to one yeah i mean i think the idea is they try to pair up people right like they try to essentially we're betting against each other but they get a cut gotcha for kind of like matching us or whatever but but yeah if you do

have this dutch book so you have these two people as i mentioned you can you you're it's in your interest to bet infinity money to both people right because like let's say you bet a thousand dollars um you're gonna lose right because there's two choices and you're betting a thousand dollars on both choices so you're betting two thousand total one because there's two choices, and you're betting $1,000 on both choices. So you're betting $2,000 total. One of those $1,000 you'll lose, and you'll get nothing. The other $1,000 you'll win, and you'll get $3,000. So you'll go from having $2,000 to $3,000 in your pocket. I guess as long as you don't have the whole problem of not being able to pay the one guy

before you get your other rewards. No, assume that you have the $2,000 up front. And, you know, you're just... It would be infinite money, and then you owe infinite money. Yeah, so... You get infinite knees broken because you can't pay your infinite money. And then it doesn't matter that you have infinite new money because you're laying dead on a street.

Maybe you're one of those, like, carrion beetles which have, like, a million legs or whatever. And by the time they break them all, you already have double infinite i say infinite money oh okay uh but this sounds like arbitrage yeah that's the whole idea of arbitrage right a dutch book is this is a situation is a betting or a situation where you could do betting arbitrage and come out ahead okay um with 100 chance of success there's a whole like race to do high frequency trading and underground. New ways of running the cables and fiber optics and microwave towers to communicate between exchanges.

This is essentially exactly what you said. I know the price in one place, and I know that price is different in another place. And I know in two seconds those prices are going to line back up. But in the meantime, in those two seconds, I can buy a whole bunch from one and sell to the other. And essentially as much stuff as I can transfer between the two before they catch up, I make money. But I, in that process, actually make them catch up.

And so I make them catch up faster than they would have before. Because somebody else would have been arbitraging them before and they're slower and I'm faster. So I get that money instead of them. Totally, totally. And then we can, did you hear about this guy? I was a real estate trader. Okay, sorry. He worked for Goldman Sachs.

He was doing exactly this. So they work on this. Actually, they do this high frequency trading, but it's a lot of like heuristics. They use decision trees and machine learning and stuff to figure out like what to buy and sell and everything. He took his algorithms and he tried to like spin off his own company,

you know, using these algorithms he developed. I think we talked about this, dude. Oh yeah, we did. did on the show he went to jail and everything yeah yeah wow so that guy so we uh so we should probably insert what episode number that was but i don't remember what it was so yeah so if you're watching all the episodes back to back to back then you've just heard this conversation but anyways uh for us it's been years but uh for years but yeah so uh yeah so the economics thing was was interesting the negotiation was very interesting um and in general dummies books are better than i thought i mean i you know they have such a negative connotation right dummies yeah it's like it's like i guess

there's some people who like the idea of being a dummy so that was episode two oh wow i thought it has been a really long time former goldman sacks employee jailed eight years for stealing code yeah that was that's been is that two years ago or one year ago here anyways this was march 18th and i didn't write the year it was before years 11 no it's like five billion years um so that was a year and six months ago gotcha gotcha cool but yeah so um dummies books pretty cool especially negotiating for them economics pretty cool economics pretty cool yeah going jail not cool no don't go to jail don't go to jail not hot either okay um on to our news of the week the first article is one I put.

So this is a research paper that I thought was pretty cool, mostly just because it's a cool video, I'm not going to lie. It's the equivalent of a cat video, but for programmers, I guess. Nice. And this was somebody, the title was The Floating Point Game of Life, the first headline I saw. But I guess the actual article was something like Continuous Conway's Game of Life or something much more impressive sounding research paper. Just to give you a little bit of hype a little moment both of us looked at

this article independently and we immediately wanted to talk about it's amazing right i was first yeah so i arbitraged yeah i win there's like a dutch book where we're both trying to find articles we were trying to find other people to bet against that we would have this and this. No, okay. So what this was is in Conway's Game of Life, now if you don't recall it by name, is a square grid of squares. And each square can either be occupied or unoccupied. Right.

So alive or dead, essentially. And by a simple set of rules about how many neighbors a cell that neighbor cell up down left right up left down left so yeah nine connectivity or whatever it's called diagonal neighbors or whatever so depending on how many of those cells are occupied means that the cell will either uh become dead from overcrowding will stay alive if it's in the middle numbers or if the cell is already dead and there's enough it will become dead from overcrowding, will stay alive if it's in the middle numbers, or if the cell is already dead and there's enough, it will become alive. Right.

And so just this simple set of rules, and I forget the exact numbers for each, but having this simple set of rules, you can kind of start with a few, three or four little squares that are alive, and they can just go on for very, very long amounts of time of having this behavior, things growing and exploding and colliding into each other. I mean, we've all, I think, seen these before at one point or another. The gliders, right? There's like gliders, there's blinkers. Takes advantage of the fact that like when you set a cell on, it's not really on in the current state.

Because you basically have like a current state and a future state and the future state is independent of each cell is independent of one another right except in their reliance on the previous state right and then so you calculate the future state for all the cells and then you flip the future state becomes the current state right and because of that whole process because of it's that two-stage process you end up yeah with these gliders that are just sort of like well there's all sorts of people made like adding machines. People have made like self-replicating ones. I think someone proved it as Turing Complete or something. I don't know.

Or you probably designed a Turing Complete machine in it is my guess. So there's really a lot of research goes on in there. And it says cellular automata. That's the name. So somebody came up with a research paper where instead of treating it as a discrete square based grid for these things, I think time is still discrete, just like we described, but space is continuous. So each cell, each thing that's alive or dead is an infinitesimally small point. And then it has a neighborhood, which is some radius of circle

around it. And then essentially created a computer to approximate that by using anti-aliasing and floating point numbers and various things to try to come up with that. And so there's a video that we posted the article, which seemed very technical and complete and only semi-interesting because I'm not that much into research. But the YouTube video

is really cool. Yeah, you guys gotta see. And even the song on the YouTube video is awesome. Jason was rocking out to the song. I was totally jammed was totally i listened to it without the music on so i didn't even know it had music i was doing the numa numa dance it was pretty epic hands were waving i don't even know what that was that's that my it's that guy he's like okay no still don't know what it is and for those of you not not watching jason attempt to dance it was pretty embarrassing it was pretty bad yeah there's a lot of empty chairs in this room

uh so yeah pretty interesting to check it out but there are a lot of people found the equivalent of gliders but instead of only like i think four directions of the traditional gliders this glider can go in like any direction yeah it's like pairs of gliders that orbit around each other and like these weird like snake support pillar things. Yeah, that was crazy. Yeah. That was interesting. You should check it out. Yeah, it's totally, totally awesome. I think it's a phenomenal idea. It's one of these like, I can't believe I didn't think of it kind of

things. It's like such a awesome idea. And to think that the Game of Life has been around for what, 50 years or something like that? A long time. Yeah. We recently had the 50 year anniversary. Oh no, it was 1970. So, actually it is close to 50 years or something like that. Yeah. It recently had the 50-year anniversary. Oh, no, it was 1970. Actually, it is close to 50 years.

It's not quite 50 years. You're a little early. Yeah, a little early. But, yeah, totally awesome. Oh, here's in Minecraft somebody built Conway's Game of Life. Do people do everything in Minecraft? No, I have to watch this video. Did you see that the game of life

was actually in the search did you see that or is that just me do the search again okay hang on i'm watching the video oh yeah yeah here we see you made an amazing thing oh it is wow somebody made conway's game of life in minecraft no when you had exactly what you expected to look like when you had the search results it actually looked like there was Game of Life going. Oh, in the background? In the background. But I might have just been looking that up. Oh, yeah.

Yeah, yeah. That's pretty cool. So my article is not as cool, unfortunately. I kind of, you know, I wish I could do, you know, crazy continuous integration, Riemann sum, game of life thing with floating point. But I think it's still pretty cool. So Twitter, they bought a company called Clutch.io. And Clutch.io does A-B testing for mobile.

And I'll get into A-B testing in a minute. But Twitter was fortuitous, or we are fortuitous, or something. I don't know where you're. But Twitter was fortuitous, or we are fortuitous, or something. I don't know where you're going. It is fortuitous that Twitter open-sourced the Clutch.io library for everyone to use.

So what is A-B testing? Patrick. What is A-B testing? Thank you. Jason, tell me. I've heard this term before, and I just can't figure out what it means. Could you help me?

A-B testing for dummies. Let me look. So A-B testing comes from alpha-beta testing, and alpha-beta is a type of distribution. First two letters of the Greek alphabet? That's true, too. But alpha-beta is a type of... Oh, ab-improving muscle training for dummies?

Alpha beta is a type of probability distribution. And it might be that if you're at economics for dummies, it would go into what alpha beta filters are. But anyways, so alpha beta testing is super important if really, I mean, you're in any type of industry and the idea is you have a you know your alpha which is sort of your control group then you have beta which is you know some new feature so for example you might have let's say your website is a cars that you're trying to sell or something like that so you might have an alpha group which has your website as it exists now. Then you might have a beta group where you randomly

throw in high-priced cars. So it's like, oh, I searched for Volvo. I searched for whatever. But I'm going to randomly throw in something which kind of matches the search result, but also more expensive. And the idea is maybe you make a commission off expensive cars. so even like you know so your alpha version like I said gives the gives the users exactly what they want the beta vision version gives them what

they want but also throws in some these expensive cars then you can look at the results of both of these independently and you can see oh you know when I when when people got to the beta version of the website, they did spend more money and they bought more expensive cars, but they never came back or something like that. And so it's like, well, long term, this is going to hurt my business. So I need to stick with my alpha version. Even though it says alpha beta, in reality, you can split many different ways. So you could have, you know, eight changes. And now you have two to the eight different versions of your website there's a 256

different versions of your website and you can you know sort of isolate which version is the best like which features helped which features hurt so this sounds easy it's actually really hard I mean one of the things that makes it hard is being consistent So you have to find some way to know okay Patrick visits my site He's getting the B version of the site a week later when he comes back He needs to get the same B version like if every time you refresh the page you get either one version or the other That's gonna be really confusing and it's gonna pollute. You know your experiment

so the other that's going to be really confusing and it's going to pollute you know your experiment so ab testing frameworks for the web or in this case for mobile sort of deal with that and they hash users in such a way where they you know they keep it consistent they give good reporting and all that good stuff so if you do any development you want to be using an ab testing and if you're doing mobile you should check out this one open source by Twitter. That's cool. Yeah, I mean, it's a lot of people doing, especially e-commerce, like if you sell something, right? A-B testing is really important.

Yeah, definitely. Is the red buy button or the green buy button better? Well, exactly. You know, you could guess, but in reality, it's better to try to test. Yeah, totally. Actually, I heard from a friend of mine who works at a startup

who was telling me of another startup that makes Facebook games. And they had this one Facebook game that was like a fish. It was like an aquarium. And you pay real money to get food for your aquarium or whatever. And they weren't doing alpha beta testing. They added this feature where basically you could buy something that you couldn't buy before. So there was something before that you could only get through experience in the game right it was like maybe like your fish could grow up or something all of a sudden they added this thing where you could pay like a dollar

and have adult fish immediately i don't know i'm making this up but you get the idea like they added some monetization strategy that wasn't there and everybody left. Like it compromised everything. And I think for some reason it's like, people felt like having an adult fish was like a rite of passage almost. And by making it to where you could pay at like essentially trivial amount of money for something that took hundreds of hours

of real like effort to do before, it like ruined the entire game. And actually the company literally went out of business people say that about the new diablo because you can play the game and get a really good artifact a sword or whatever i don't remember the name and then sell it on the internal auction house and make money that like it becomes like almost like a job like you want to play because you want to you know get this thing so you can make more money by selling it. And some people have said that's ruined. Other people have said it's great and you make money.

But it's true. I think it's hard to do A-B testing, though, with something like that because when you're rolling it out to things that are naturally shared or social or community-driven, if you A-B test, I don't know that that works. So you could A-B test the sign-up page or something. But the actual thing, if I give you free fish and i don't get free fish like or you know sally doesn't get free fish like so this is confusing so like you're not only interesting topic actually so i know that the way i have so i know the way that they do this right now is actually through country so i have

another friend who works at a uh social startup i have like a lot of social startup friends um this one they actually test everything in canada so they do for them to do out the way they do ab testing is they do the b and b stuff in canada and of course there's skew there because canadians probably have different yeah well this is how like restaurants do like if you ever i don't know if you've ever been to a restaurant when they're testing a new product or something and they'll just like serve you a special product just there. Only that store has that feature. And then, you know, they see how people like it or not. That's a little different than A-B testing. Because A-B is supposed to be kind of like completely independent. Like I'm picking random people across my whole entire population. That's true. And I'm giving them some feature and seeing if they react differently.

Yeah, I mean ideally... Because you get all these other bugs. Like if you do it in Canada, like, oh, I'm randomly selecting everybody in Canada. Oh, okay, but you're introducing all sorts of biases. Yeah, totally. I mean, something that would be amazing would be if you like took your users,

you know, let's say you're making some social app, whatever, looked at the social structure like look at the the structure of the relationships between those users and broke them up into like connected components to do a b testing um that would be pretty awesome i mean so you can't be perfect because it'll be like ultimately they'll always be these two people who are friends and are on different versions right unless you literally have two disjoint communities. But chances are you won't, right? Because people refer their friends and things like that. But maybe you could find these pockets

where there's very few connections out and you could A-B test. That could be the B pocket. Yeah, I think A-B testing is typically or maybe even fundamentally supposed to be small changes. Like minor things, not like large features or the color of a button or the placement of something on a page or small stuff like that. So we've talked multiple times about just stating the obvious. That's my new thing, state obvious things.

All right, cool. You got that for dummies, the state obvious things for dummies? Talking for dummies? So Coursera is one of the online education platforms. And I actually happened to be involved in this. Oh, that sounds bad. I knew that this had happened before the news broke.

So basically, the inventor of the Scala language, which Scala we haven't talked about before, but is a functional programming language built on top of the Java virtual machine. He is offering a functional programming class for free on Coursera. So I'd signed up for this class, you know, in the high-hoping dreams that I would have time to watch the videos and do the exercises. And pretty much like every other class I've taken online for free, I just have no motivation to actually do it. I get busy. But I got this email one day saying that basically they had detected cheating

in this online course. Wow. So this is a couple of interesting things. One, it's a fully optional and free online course with no college credit, just like some email saying you complete this course as the only output. Why do need to cheat just be like me just don't do anything like it isn't it doesn't really like i don't resume padding or something right but i don't like it doesn't have a lot of value for a resume at this point like maybe one day it will i guess but then just don't take it now like i don't you know and there's no association like it's just your email right so you could just

change use a different email next time or whatever. I don't think it's a super big deal. But people were still cheating. They had basically posted solutions to some of the programming problems online. And this is kind of confusing slash also disheartening. And so they said that, oh, we've tried to identify the people who did this. We're going to basically block them. We're going to kind of give zeros to the people.

Or I forget exactly the action they were going to, you know, basically block them. We're going to kind of like give zeros to the people. I forget exactly the action they were going to take. But people like submitted assignments that were exact copies of the online known ones. And then like, oh, they're going to have the TAs basically looking online for, you know, solutions to pop up. Because arguably most people just find this by Google searching. Like, oh, folks are performing class. Oh, here's a form. Oh, look, here's the answers.

How nice. But it does bring up an interesting thing that, again this is a free class like no i'll be but people still like feel compelled to cheat and like try to get around doing real work which is kind of sad did the article go into why people no i mean nobody knows right like you can't you can make guesses or psychologists or whatever but i mean the other thing is like if my question was just like i guess maybe different maybe because the stakes are higher i don't know but like if you go to university so nobody ever talks about like cheating at university sometimes they do talk about cheating at university level there was somebody in my lab so many phd students they are also tas right for classes um that's sort of what pays the bills while you're in grad school um he actually wrote

a program to do like like semantic analysis of your code, like to generate the tree, the call tree and all that. And he somehow was able to write a program to detect copies. And then he just ran it on all N squared pairs of, you know, students assignments and found out groups of them, like, you know, these nine people all have exactly the same answer. And so I think it's possible. So, okay, so I'm not saying it's not possible to detect the cheating. I'm just saying, like, there's a lot of high stakes. Like, if people are cheating in online free classes with, like, basically low stakes,

and now the stakes are higher. You've paid money to take this class. It is going on your resume. Are you going to graduate in time? Like, the bar to, to like fail is so much higher so arguably maybe people don't want to cheat because if you get caught cheating like it's much more much more damaging but it's an interesting question and there have been cases like recently of kids cheating in high school like taking sat tests for each other oh yeah and people doing

electronic cheating i oh i could have put this article in here i was reading this article the other day about somebody cheating in chess and just like, oh. Did we talk about that last time? I don't remember. I don't think so. Yeah, so like there was a kid basically using like a PDA. So they have some specific model of PDA,

which I'm not very familiar with chess competitively. Whatever competitive chess associate has like a specific model of PDA that runs a specific app that's allowed. It's to keep notes. It's very traditional when you play chess that you keep notes about all the moves of the game so you can analyze it later. Or analyze famous chess masters

games. These kinds of things. They said, we'll just let these people use this app on this specific PDA, not like an iPhone or something general. That app's developer has promised like, oh, it locks everything out. It's the only app that can be running. We don't allow multitasking. We turn off all the Wi-Fi, everything, right?

Like it's all turned off. But there's now the ability for even these small devices to run very high quality chess engines, right? So they don't know exactly what happened, but all of a sudden this kid who was really bad started doing really good and like somebody came over and noticed him doing something

fishy with his with his device but they didn't confiscate it they didn't look at it they didn't like it just kind of let it go so they don't have any like clue about kind of what to do but it kind of brought into this like oh people cheating at chess you know but like brings in this general like electronic cheating like other people have been found with like little tiny like micro speakers in their ears like really hidden ones down in their ears getting told chess moves with somebody else basically doing it on a laptop right and like playing the opponent's moves and then letting the you know computer like all these things like all these you know high-tech cheating now is gonna be a real problem like how to detect it

to deal with it to stop it that's really interesting so i yeah i don't know so i mean like you said one thing is like you can compare so they do this for research paper or not research but like oh yeah turn it in right yeah there's like various services where basically you upload or all your students upload their research papers right but not like not like PhD research papers. For people who don't know, turnitin.com is a website. Like many TAs and teachers use it. Basically, it's this huge repository of, you know, research papers. I think it just scans like tons of things,

like mine's Google Scholar and like all these things. And basically it looks for, so then when you submit an assignment, it looks for plagiarism. So it looks for plagiarism between you and other research papers. Right. And also you from like it has repositories of books and articles and web pages and like all this stuff.

And so it'll show like, oh, this whole section is matched. And, you know, whether or not like it's annotated or footnoted or quoted or whatever. So basically give you a score of how likely it is this person cheated or how much they cheated. Right. But then for like code, it's actually fairly straightforward. So for words, it's pretty hard. Like you'd have to restate it. But if you're going to take time to restate a whole paper and like change all the words

around so it still makes good English sense, but you know, it's different enough. Like you might as well just write the research paper from scratch. It's kind of a lot of work because you have to understand the material, which is arguably the point anyways. But for a programming assignment, you have to understand the material, which is arguably the point anyways. But for a programming assignment, you can just, for instance, like rename all the variables. Yeah. And then it would be very hard to detect that in this. So you say like, you know, you knew this person who wrote like, analyze the tree.

So we're going to have to go to stuff like that. But then people are going to know that's the case. So then they'll have a, they'll add an extra program that, yeah, add an extra function, add a program that permutes, you know, it's like alters the function the function calling or you know adds extra extraneous ones that don't matter right and then it'll just be this cat and mouse game yeah totally it always ends up like an arm race like all this stuff like spam finding spam you know botnets like stopping now but it always ends up like some arm arms race or like you figure out a way to filter it and they figure out what to get around the filter and then it keeps going yeah this will be another one of those

yeah actually i saw an interesting ted talk on cheating this was a while ago like three or four years ago but one of the cool things was everybody has sort of like uh an internal like i don't know what to call it but like basically like like so the example he gave was in one case if you cheated there was a way for you to cheat but there wasn't a large gain in the other case there's a way for you to cheat and the gain was larger and it turns out like a lot more people cheated oh and the thing I didn't explain in both cases it was trivial to cheat and you knew you weren't gonna get caught so you know a robot

you know just a naive like you know math model of this would just say there's no chance of getting caught there's no implications i should cheat regardless of you know of of the game but the reality is people have this like internal inertia against cheating or like an internal karma system but if the reward is big enough they'll cheat and i thought that was really interesting would have been more interesting well i is the experiment if they had actually cheated when the stakes were less like somehow being worried that when the stakes are higher that the penalty would be somehow implicitly higher oh yeah but the way the experiment was structured it was obvious that there was no penalty yeah like completely obvious

but yeah that's interesting too like because what you described seems rational like okay so like if there's no chance of getting caught right and people will cheat but if the stakes aren't that big like what does it matter like i'm just not gonna cheat it's true i mean it's interesting but yeah yeah that's a bash on your video no no i just it's it's it's interesting it's sort of like cheating has like a weight to it it's sort of like cheating has like a weight to it it's sort of like if there's not a lot of gain then i don't want like the bad karma of cheating is basically that's what i took away from it you know but the same the same guy had this interesting thing where uh if you're given three choices and um all three are of roughly

like the same quality or maybe you don't even know their quality but or no sorry I got it wrong you have two choices they're both of the same quality now you introduce a third choice that is Pareto dominated which means it's worse in every way to one of the two choices people will pick the choice that's better than that third choice so for example if i have like like if if there's like girls trying because you know we do programming throw down there's constantly girls asking us for our numbers so if me and patrick are at a bar dude i just made all these nice things to you right now you're totally gonna lose it all if me if me and pat Patrick are at a bar. Dude, I just made all these nice things to you, right? So, like, now you're totally going to lose it all.

If me and Patrick are at a bar, and let's say, like, just if two of us were at a bar, we'd have an equal chance of getting picked up. If you introduce a slightly, like, ugly. I'm not condoning this conversation. Like, I don't even know if I want to let you go forward. No, no. If you introduce, like, a slightly uglier, like, slightly, like, I don't even know if I want to let you go forward. No, no. If you introduce a slightly uglier, slightly more disheveled version of Patrick,

the chances of girls picking Patrick over me goes up dramatically. And even though Patrick hasn't changed. Yes. Oh, yeah, yeah. I've heard it before. And he actually... It's because it gives you an anchor.

You say, oh, at least I know this choice is better than that choice. Yeah, and it has something to do with the brain cannot, like, evaluate things in, like, high-dimensional spaces. But, like, it can see that, like, this thing is better than that. It's ranking. At least you can rank those.

Like, it's very difficult to rank an apple versus an orange. Right. But if I put in another apple that's, you know, like, decayed, it's like, oh, at least I could take the good-looking apple. Yeah, exactly. It looks better than the decayed apple. Yeah. But I have no idea how to compare the apple and the orange. Right. thing with houses you just there's a really big problem with houses this is the whole comparable

thing when you buy a house sorry we're getting off track but yeah so like when you go to buy a house it's very difficult to compare houses in different neighborhoods so people tend to really quickly focus in on one neighborhood and try to rank houses because then they can somehow rationalize the price. Even though that price has no, it's just whatever you'll pay for it, right? Like whenever the seller is willing to sell it to you. But it's very difficult for people to keep the search

very large and open because it's very difficult to compare something that's a far commute, but very cheap and a very close commute, but very expensive. Like that comparison is very difficult. If you have to factor in like, oh, if I live here, the schools are bad. I'll have to pay for private school

and like factor in like the, like 20 or 30 different variables at each house. And like, you can't, your brain just cannot do that. And so, so yeah, what'll end up happening is you'll look at two houses. One will be better than the other in some like obvious way, like completely dominate

the other one and then your brain will make the mental leap as to say that house is better than average because it's better than this one something like that yeah yeah yeah it's all right wild we gotta move on we are very talkative tonight tool of the bi-week tool of the bi-week my tool the bi-week is pixen uh i don't know if i'm i think i mentioned this earlier how i was getting into doing art as a hobby like pixel art or maybe i just told you in private but but yeah so i've been doing pixel art i got one of those wake-on tablets and it's been pretty fun just drawing different things um and i've been using pixen and it's really a pretty cool tool it's only on mac which is a total bummer. I think there's a Windows equivalent software that you can find.

So this open source software doesn't run on Linux? This is not open source. I know. Wait, that's... Dagger in the heart. I'm normally the one who's the horrible person for not... I'm a terrible person. Wait, I've got to go change my noun to something open source.

Let's capitalize. Let's capitalize. Go, go. So, yeah, people won't know which one of us is better unless you find an open source software because there's just too many dimensions otherwise. No, I'm presenting closed source as well, so now they can compare us.

Well, there's too many other things now. So, yeah, so Pixin does a lot of cool drawing. It will do, like, dithering and things like that. It'll let you look at the same object at multiple zoom levels simultaneously. It'll open 10 windows where the object is zoomed in, zoomed out, regular. And for doing pixel art, that's really important.

Really cool app, highly recommended. Nice. My app is Downcast. Okay. Which is a fun play on words. So we are a podcast podcast and this is a podcast app so if you're not on ios you can stop listening for the next few few minutes but i happen to use an iphone and they updated to the new ios ios 6 and they moved podcast used to be part of like

where all the music and stuff was too it was just like kind of like a separate tab or separate screen but now they moved it up out into a separate app that app is horrible oh horrible that's the built-in like it basically like I almost thought like I listen to a lot of podcasts like every day on my commute I listen to podcasts I almost got podcast app wrong I mean looking at well so like just the all the like functionality that it used to have like i can't even begin to describe it like i need to i've like so blocked it from my memory i don't even remember what was so bad about it that i didn't like and this wasn't like oh i read online and like uh people are outraged no i'm outraged no this was like literally like i didn't

know it was coming i went to the normal app to pull up my podcast and it's like oh your podcasts are now in this other app and i'm like okay so you know you have to go download that app if you don't have it but i already did have it from earlier playing with it when it was optional feature um and it looks like i actually deleted it so i don't think you even have it so i can recall um so it's like oh yeah you know you need to go delete it or you need to go download it if you don't have it and so i download it and then it just like, Oh no, I remember like,

so one thing was if you start playing a podcast, right? So I'm playing a podcast. I've downloaded to the phone over USB cable or on wifi while I'm at the house. And so like I'm driving in the car, right? And all of a sudden, so that episode finishes and it starts playing the next episode.

I'm like, I didn't know I had that one downloaded. Like, I guess I did. No, guess what was streaming over the internet the whole time i was driving to work the next episode guess what my battery was like when i got to work really low so that was a nice feature and i sure i probably could have figured out how to disable it and thankfully i don't have so i'm not still on the

unlimited plan because oh no it's too long so it doesn't really matter to me and even then like the podcast wasn't huge so it wouldn't have been a big deal but the fact that like killed my battery just because of streaming yeah yeah and like i mean i guess it's a nice feature but i really would like confirmation that that was what was going to happen and just managing the subscriptions and it like uh i just really didn't like it i was having trouble figuring figuring out what things I had downloaded to the phone that I could just use. It downloaded the whole next episode? Like, it didn't try to stream? It didn't download it.

It streamed it, like, over time. But that's actually worse. Like, I would have rather to just download the whole thing at once. Oh, yeah, you're right. So now it's, like, keeps hitting it. Like, I don't know which penalty is worse. But I didn't end up having it downloaded when I got to work. Like, I only still had wherever I was in the stream.

Yeah, because if you had downloaded it all at home, then you would use Wi-Fi. Yeah, so that wouldn't have been a big deal if they had, like, downloaded the Wi-Fi. It's versus I'm in the car and it's just on 3G. Exactly. So anyways, Downcast is another app which does basically the same thing, only it seems much nicer. And maybe it's just because I got that one experience burned me so bad. I don't know.

But looking later on the Internet, it seems like a lot of people really don't like this app as well, this podcast app by Apple. So if you're looking for one, I recommend podcasts. We're on there. And a nice bonus feature is that when I look for, when I do a search for podcasts, I'm doing it right now to make sure this is still true, and I search programming, the

very first result. Here we go. Check it out. I'm searching programming. Searching for podcasts. Whoa! Number one! Holy crap. Number one. It's amazing. Check it out. Searching programming. Searching for podcasts. Oh! Whoa! Number one! Holy crap. Number one! It's amazing. So number two is music for programming. We're number one!

That's totally... Music for programming? You guys have nothing on us. So better than the iTunes store, which ranks programming, programming, because it says programming twice. I don't... Guys, if you listen to programming, programming people, I'm sorry. I don't know anything about you.

No. I've never listened to your podcast. No, not at all. But you get ranked ahead of us, and I think it's because you just have programming twice in a row. So programming throwdown is number one. I have no idea what ranking it's using. It could be completely random.

I don't care. It's amazing. Programming throwdown, which we'll, for now, forevermore call programming programming programming. No, we shouldn't be mean. Those guys might be really nice. No, I'm sure they have a great... Actually, the last episode they uploaded was in 2007. Oh oh all the more reason for us to be ahead of them so maybe it's just freshness although music for programming has a even more recent episode than

us so oh wow but anyways we're ranked really well in this which is just a nice side benefit but this one has things like it'll do the geofencing so like when i get home it'll automatically refresh my podcast list and start downloading anything new. Oh, dude. Like, once I get home. Dude, that's a brilliant – I've never heard of any app doing this. Really? I mean, it's such a good idea, right?

Okay, yeah. I guess I don't know. Oh, wait, no. So, like, the reminders from Apple does this. Why wouldn't it be geo, though? Why wouldn't it just wait for Wi-Fi or something? Well, so, for instance, like, if I'm on the road, like, I don't have access to charging. But once I'm home, like, it's actually fairly easy for me to charge.

And you can set where it is. Like, I can set home. I can set work. So, at work, I have Wi-Fi as well, so I can do it at work. But I just don't want to do it in my car. And you can set it, like, leaving an area, which I don't exactly. But, like, arriving at an area. Wow.

Between certain times, like, it can check daily how many episodes to keep. You know, what to do with new episodes if you already have old episodes it just seems very highly you can set it to sleep after a certain amount of time you can create playlists of podcasts you can tell it like i do want you to stream ones i don't have downloaded or never do that anyway it's much more configurable but the good side note is we're number one amazing amazing that's all thanks to you guys really i mean assuming that they're using you know voting and like stars we're gonna find out it's completely random and we're gonna be completely deflated but yes until then until then it's all thanks to you guys out there you know giving us good feedback making the show

better i mean we take the feedback really seriously read all the email we read all the feedback on itunes yeah we're not always good about replying, but we do read it all. We're getting better, though. I actually have the programming throwdown at Gmail on my phone now. Oh, nice. So we have 65 ratings now. Yeah, amazing. That's pretty good. And I think this is only in the United States. I think it shows you localized results.

Yeah, it says U.S. So I don't know if people in other countries want to look, but I know we had an email from a listener in Germany. Yeah, yeah. So I think we had one from brazil a couple couple weeks ago yeah so we've got listeners all over the place although you're saying beta earlier people know we're in the u.s right because this is beta and beta yeah yeah so is alpha still alpha yeah i think so i don't know you know what was that one word but thank you all. To interrupt your thinking of our listeners. Thank you, guys.

When I went to London, I noticed that I say process, and the guys in London say process. That was the big one that, you know, because we're all nerds. We're clearly saying process a lot more than beta. I guess because we are computer science nerds and not math nerds. Maybe. Anyway, 65 reviews, that's pretty good. There's a lot of well like, well-known podcasts

that have, like, only, like, 65 reviews or 50 reviews. Yeah, totally, totally. They have the same as us. No, I mean, but, like, we're doing pretty good. This is good, man. Yeah, I mean. We haven't checked our download count recently, I don't think. Have you?

I haven't checked recently, but I can do that. But a lot of our, you know, more recent episodes have been based on user feedback. Like, someone suggested that we do that. Oh, that's true, yeah. Yeah, someone keeps suggesting we do like Ruby or JavaScript or query languages, et cetera, so databases. So I appreciate you guys' feedback.

Yep, and on that note about databases, we're on to the topic of the week, query languages. Yes, query. Query or query? Oh, man, I'm going to think about how I'm saying it. Query. Query. Query. Query or query Oh man I'm going to think About how I'm saying it Query Query Query

Query Query eye for the straight Dude I I You're going to make me Like have to edit you Out of the whole podcast It's going to be

Patrick only this time Yeah We're going to have to Change the queen rating Oh I was going to mention That actually Because I was listening To another podcast

And they were talking About this So a little behind the scenes What do they call it Inside baseball Okay So we do the whole show in one take yeah totally like we i like so i'd edit the show and we'd do one exception when our internet went out but yes okay yeah that's true

we've had one where internet went out but every other time we do it straight through once and i hardly ever delete anything like i think just once or twice like i've deleted a section where one of us like coughed or something yeah but yeah for the most part, we slap on the beginning part, the end part, listen to make sure good, and that's it. So, it's like, we do, like, this is totally, like, we don't edit to make it, like, us sound better. We're this terrible in real life. Like, we don't have to be edited to sound worse. We could come to your school and talk, and we'd be this bad. That's, like, we could do this in front like oh oh we'll

talk about that in a minute yeah all right so hidden fun stuff so query languages stay till the end everybody so this is cut cut to the bone the meat the meat of the situation what is the what is the one everybody knows about sequel sql that's right it's so people know about it so well they've turned the acronym into like a word like they've tried saying sql so on that when in c++ when i write c-o-u-t how do you say that c out okay that's how i say it as well but some people say count those people i know people who say cow i don't want to generalize like i don't want to sound okay what about what about but what about p-r-i-n-t-f print f okay this is the same as me so so so the people i know who say i in it's because they the people i know who say count it's because they they didn't grow up thinking of out as a word

like they know it's a word now but like english isn't their first language and so they just look at the whole thing phonetically whereas we're trained because we knew English from the beginning. We're trained to see the out in the sea. But I kind of like people who that's not true. So I don't mean to generalize. There's nothing wrong if you say just these aren't actual real words. So you know like people were like English-speaking since birth who say cow. Yeah, and they're very defensive of it.

Really? Yeah, like and sin instead of C-in. But, I mean, clearly, you know, so just to refresh people's memory, C-out is when you want to stream things out to the console. In C. So it's actually short for console out. That's why it's C-out.

Oh, I didn't know that. I actually thought it was C because it's a programming language. This is C-out as in C++. But also it's a pair because it This is C out as in C++. But also it's a pair because it's always C out and C in. Yeah. So you see those right next to each other. Right. So, I mean, the C part is common.

Yeah. You just blew my mind, dude. So you can go back to that guy who says cout and you can be like, listen, buddy. Yeah, that's what I'm going to do. I don't get beat up, dude. It's your director. Okay, anyways, back on the topic, SQL.

And now we pronounce it SQL. JOHN MCCUTCHAN- Yeah. So SQL is used to query databases, primarily. So the canonical SQL is select star from and then the database name. So if I have a SQL database, which means a database that can be queried in SQL, actually, I guess there are some other...

Yeah, so, I mean, we should talk about that. But SQL is as a language, right? So, first as a caveat, we love caveats here. Totally. We don't condone this behavior, you know, whatever. We like caveats. It's like caviar, but cheaper. There's like four face palms.

So the language of SQL, or query languages in general, can be distinct from their data that's underneath. So sometimes a translation is done. You can write a query in SQL, but a translation is done to some native query when it's executed, or whatever. But SQL is just kind of like, it is very much like a programming translation is done to some native query when it's executed or whatever. But SQL is just kind of like, it is very much like a programming language in that it can

get compiled, it can get optimized, it can get, not in the same way, but in a way. And it is distinct from the data that's underneath. Yeah. So for example, I did some work in C-sharp a long time ago. So this might not even be true anymore. But C-sharp uses, we used TSQL, like the Microsoft Transact SQL, and there was this thing called a stored procedure.

And what this was is you wrote this SQL code, but then it compiled it down into something that was like at a very low level, like in some low level TSQL instructions that could be executed really quickly. So for example, if you wanted to get all the users in the database, you know, in pagination of 10 at a time. So, like, the whole, you know, sending that command over the wire, you know,

the text, and then, like, receiving it, you know, in the database and then processing it, that could take time. So this stored procedure was a way of doing all that compilation beforehand. Oh, and I actually didn't get good to my caveat before, but that we're not really talking about the backend system so much. We will, and how they relate. So the whole database administration is a very ripe field, and we are not, I don't know much about that at all.

Me neither. So querying, the SQL, the query part, I mean, I'm not great at it, but I understand the querying part. But the actual administration and schema creation, so schema being we talked before about things that define what the data is stored like. And so this is another, you know, the schema kind of says how are we storing the data. And then query is written against the schema. So you query the data that's stored in the way that the schema specifies.

Yeah. So we're not going to talk a lot about that stuff. Yeah, we won't get into B-trees or, like, how databases work. I mean, that stuff gets really ugly. So, yeah. But, yeah, so, like, the queries are written against language. And I think it's an interesting point. So kind of, like, when people go through learning about programming and not necessarily like at college, but like in an early stage, a lot of times your first thing is like you write very code that just kind of runs.

It doesn't store anything. And then you need to store something. Like, oh, I want to remember something or somebody chooses something and I want to remember that or I want to write some output. So you typically write to a file on disk or whatever, you know, and you just learn to read and write files. But then when you move up from that and you want to have multiple users and store lots of data, right, so you begin to look like, how am I going to do that? Well, you can write, like, structured data, like XML,

and write that to the disk. But I think the first kind of thing people encounter these databases, like the first thing that people interact with that's not on their computer. Right. So you can, and we'll talk about it later, there are ways to run databases that are on your computer. But to be honest, if you're doing that, you don't need to know if it's on your computer or not. It doesn't matter.

Right. But at least in my case, and I think in many cases, the first time you're doing something between computers, multiple systems being involved, is talking to a database. Yeah, definitely. Because a database can run wherever. And it can be serving 100 people at once. And invariably, you see people hit this wall. Like, they start off with just one text file with all their data. And then that becomes really slow.

You either have to read the whole file into memory or something. So then you do something like, oh, each row in the file will mean something. So I can go straight to a specific row. Or maybe I'll have a file for each user. And I'll let the directory structure work. But then what really kills you and where you really understand oh I need to get a database and SQL is when you run into needing secondary keys so in other words let's say I have like so say what a key is oh yeah so so you know data in a database

is stored in some type of aggregate structure so in the case of like my sequel which is a database they store things in tables right and so a key would be the element that you almost that you typically want to use to look up a row in a database so it's usually unique so for example if I have a database of people's names and how much money they've spent on my company, I could use someone's name as the key, but there might be two John Smiths, right? And so that wouldn't work because they would collide. So I might just have every time a new user shows up, I give them a new number. So the first user gets one, the second user ever to the system gets two, so on and so forth.

And that becomes my key. So as long as like Patrick's in the system I sort of keep around that oh Patrick is two one five four three and I can go and say get me the payments for two one four five three get me the you know expenditures get me like what emails I've sent them and you use this this key to sort of get all that data that's a primary key but then you'll end up running into situations where you need like get me everybody who's bought this product so I need to go through all the data and I need to look at like a column that isn't the key so I need to go

through all the users all the primary keys look up their information pull out the secondary key or pull out this other column, see if it's equal and then you know that would involve going through the entire database, right? So if you have secondary keys you could think of that as sort of little mini databases. It doesn't have all the data, it just has sort of pointers to the main database. So for example the primary key might be the user ID. And so I have this, this map, which maps like two, four or five, whatever to Patrick. But then I'll have the secondary key that maps like bought economics for dummies to Patrick and Jason and

these other five people, right. And so as whenever you update one row, like if a new person, if Jim buys Economics for Dummies, now you have to update the row connected to that primary key where your real data is, and you have to update that secondary key. You have to add Jim to the list of the people who bought that. And if you have to do that, that's annoying. That's terrible. Yeah, and so doing all that is a nightmare. So that's what, yeah, exactly.

The database will do all that good stuff for you and uh you just write sql and so you almost got there but not quite but introduced the relational part of database so sql is a relational database and we've heard a lot of thing we're going to talk about a little bit about what relational database is and moving away from that but that wasn't originally originally the case it wasn't relational. Oh, really? Yeah. So before relational databases, they just weren't. They just were flat.

So then we introduced relational databases. Oh, okay. Do you want to talk about what the feature of that is? No, you go for it. All right. My throat's sore. So, yeah. So we introduced the concept of relational databases. This is what we know today.

And it seems like, in hindsight, like, oh, this seems intuitive or whatever. But Jason introduced the notion of a key so a key can identify uniquely identify a row in your table right so you think of rows and columns so rows being different entities and columns you know attributes of that entity or there's various ways of talking about it and we're not experts we're probably not using the right words, but that's okay. So relational says, oh, okay, I have that key. So Jason talked about, let's call it an identifier, an ID.

So my user ID is whatever that number you said. And so that can identify a table that lists my name. But why do I also have to have the table that says what I bought there? Because I might have bought multiple things. And how do I, that gets confusing now because the database runs most efficiently if, you know, there is a key that identifies everything, like uniquely identifies each row so that, you know, it's very fast and efficient to look it up when you provide that key

because it knows exactly the one place where it is. Yeah, yeah. And so multiple things could be combined to form a key, but in the case where I bought multiple things you might have to use like a date but I don't it's not really convenient for me to look up things that Patrick has bought by date so I have his unique identifier I can look up his name oh that's Patrick okay now we'll look at the things he bought well I can list one thing there but when he buys a new one do I just overwrite

this and this is the problem before relational databases this becomes inefficient you end up with like a column that has to store a list of things in it. Right, or multiple rows. And then that's inefficient because it's essentially collisions in your map, if you think about it. So the relational database was introduced, and that was this table relates to this other table. So now I have the unique identifier that points to the entry that is names and addresses

right because people only let's assume for simplicity's sake initially only have one name and address but then i have another one that is things that that user has bought right and then that's you know some other so it uses the same key user id but then maybe it has like user id and something else like just another id like purchase id or whatever and something else, like just another ID, like purchase ID or whatever, right? Something else. And then I can link those things together in what's called a, so that's a relationship. So I define the relationship, you know, like, oh, this thing relates to this thing in this way. Oh, that's too many things.

And I'm probably doing a terrible job of describing this, but that's okay. No, no, it's good. So they relate. And then the other concept that you get to immediately once you do that is joining data from one table to another. Right. So you have the secondary keys like JSON, you have the primary keys, you have what's called a foreign key. So like this key also references this other table. So it's a foreign key to that table.

Ah, I see. You also have another thing that we haven't touched on now is you have indexes, indices. Right, which is how you do these efficient lookups. lookup yeah yeah which is all sort of things you do behind the scenes but in SQL you can actually if you're writing SQL to select from a particular column if that column is indexed it'll go much faster yes yeah yeah or if you're adding restrictions so like I know that I want only the column that has Jason's user a you're a like So I say where the user ID equals Jason's number. That's really fast if that is the key or at least in an index.

Yeah, and some of these systems are even clever enough where they'll analyze your SQL over time. And if you're making many calls to a column that isn't indexed, they'll index it for you. Or at least they'll ping you as a potential optimization of your database. So SQL can actually be used to sort of optimize your database if it's used often enough. So anyway, so it gets into joins. And so this is the thing that gets into the meat of SQL

programming is doing these joins and then also sub-queries. So I want to run some query, get some results. And then I want to essentially query that query. So I restrict the data set to some set, and then I want to query that restricted set for further information. Yeah, one kind of interesting thing about SQL is SQL reads from tables, but it returns a table. So, for example, if you say select user ID and name from the users table,

what you will get out of that select is a table. And as Patrick said, that table sits somewhere in memory or in some kind of cache on disk or something. But you can then run these nested select statements where it's like, take this table that I just selected and select something else from it. So it's a pretty brilliant language. And then when you're doing join between multiple tables, you're essentially building up a new table that has added information in it.

So you're adding to the columns, essentially, of this returned table. Yep. And then that gets into, now we get into everybody has these complex queries that they build to do these really specific things that seem kind of like oh so frustrating why is it not working why is my query not returning what i what i need but if you had to do like what jason was describing in the beginning you'd be far worse off yeah totally yeah definitely so um you know there's so much crazy magic behind databases and um it's just it's a research topic actually like optimizing and how to run the back end,

the database engine, the thing that is storing these datas and executing these queries and optimizing them. Yeah, and I mean, you know, no matter how little data you think you have, I mean, if you have hundreds of thousands or millions of records, you've got to be using a database.

I mean, if you want to have any kind of reasonable turnaround time you know it doesn't take a lot of data for you to get to the point where it's like oh i have to use a database and then you're running into using sql or something so we hear that term today big data so i mean from your example like big is relative i guess you start with like small data just in a file and it's bigger data like you don't have a larger data set and sql really can handle that but's bigger data, like I want to have a larger data set. And SQL really can handle that. But then you get to big data, which is the buzzword recently.

But that's even, so if your data just grows and grows and grows, eventually it takes longer and longer and longer to run these queries over all the columns and look for things. Then you can't index everything because the indexes would get too big. And so you have to go through every row looking for you know things that match that query and that then that's when we begin to introduce other things that are used oh um so like you know for running these and like we talk about the like hadoops like map reduces and you know oh we want to to do these things because that data is too big the traditional sql databases can't handle it it would just like it would just be

really really hard yeah and a lot but a lot of time is spent like sql did scale way further than probably people thought it would yeah totally so you know so millions of people querying it per second you know millions of queries i guess that's a good man like queries per second i think that's a typical yeah you know parameter that they measure by so like you know just like how many people are creating the same time and that's another thing that's an advantage of not having a file, is that you and I can share this data out in the cloud. Yeah, no one's locked the file or anything.

Yeah, we can, and then, we'll talk about it later, but, you know, that, then what happens when we want to write, not just read? Like, how do you handle that? Well, if you have only one file, it becomes problematic, but if we share a database, it handles that for us. Yeah, totally, totally. And the other thing is you might want to instrument your program.

So in other words, while your server is running, giving data to your users, providing data, you might want to write another program that sort of looks at the server every now and then and says, hey, what's going on, right? So SQL is a lot of these databases have like web interfaces where you can write SQL in like for example my SQL database has PHP my admin which is this PHP server that you run on the same machine as the database and then actually it doesn't even have to be on the same machine but you run this PHP server you

go to this website that the server is running on and you can actually look at your data as it's changing so if this was on a file system like you'd have to like lock the file users wouldn't be able to do anything and then you look at some data and then you unlock it's just not practical right so SQL is not the only thing that people use our other things as well many of them yeah so there's some what is another one another one is sparkle so this is for semantic webs so this is a little bit different the idea here is um if you have a semantic web of information so the idea is you have a set of entities and then you have information on the connection between

the entities so for example um let's say you had uh let's say you had all of Facebook's data and so each node was like a person and then the connections between them also information like these two people are in a relationship or married or friends or not friends or they used to be friends and now they're not friends that could mean something right so whenever you have like this what's called semantic web but you have like information that's sort of stored in this way Um sparkle is sort of a query language. It's really good for dealing with this So there's plenty of other types of query languages

And there's other things that are different like each each kind of database engine tends to have like a slightly different version of SQL Yeah, that's true certain commands that you think are part of language But aren't and are custom and how to do these, you know, the management stuff can be very different. Yeah, even like sometimes getting the current time. Like you might want to do just programmatic things which aren't really related to your database. Like you might want to say, for example, get me all the users in the past month.

Well, you have to know what 30 days or what a month from from today well you need to know what today is yeah concept of it so a lot of those functions like either the names are a little different or you've tried to standardize it and it's a lot better now than it was you know a decade ago but but yeah there's still all sorts of nuances there so breezing through some of the rest of our traditional structure of language you know I covered it yeah we talked about interfacing from multiple systems. Transactional, I alluded to this

and didn't exactly talk about, but if Jason and I are both trying to write data, or Jason's trying to write and I'm trying to read, how is that handled? And that's a very complicated thing. Yeah, sorry, but it's being able to roll back. So the idea is like, if me and Patrick both, let's say there's one product left on Amazon's shelf.

Like, Amazon's selling economics for dum's say there's one product left at on Amazon's shelf like there's the Amazon selling economics for dummies and there's only one left and we both go to buy it at the same time so it uh it starts processing both of our transactions and then it charges Patrick's credit card and like I'm halfway through the transaction and so the problem is multiple queries need to be bundled together in this transaction right so it's not just one query that each of us are running. And it's just a race condition and one of us will get to it first. I mean, that is a problem.

But even the worst problem is we need to do multiple queries. I need to query your credit card. Then I need to write that I charged your credit card and tell something else to go charge your credit card. Then I need to go to the product once I've done that and deduct the amount by one or vice versa. But whatever, right? Like I need to do this set of steps. and we both need to execute that set of steps. But we need some way of, like you said, rolling back so that if one of us can't fully complete it, then, you know, we undo everything that happened, like all the other rights that happened during that need to get undone. Yeah,

exactly. You need to go back to like none of them worked. It's all or nothing. And that's what a transaction is. Totally. Yeah. And it's very needed, very needed. And it has to, like you need to go back to, like, none of them worked. It's all or nothing. And that's what a transaction is. Totally, yeah. And it's very needed, very needed. And it has to, like, you have to know it's right. Yeah, I mean, imagine how terrible this would be. I mean, like, you would end up with, your credit card would get charged, right, as something came out of stock. You know, I mean, like, this could be just a complete disaster if it wasn't implemented right. And we didn't talk about it yet, but the ACID transactions.

Oh, yeah. So, you know, this is a whole thing that is very popular to be talked about, and especially what we're going to talk about now, about the no SQL movement. So SQL, when you try to go too far in one parameter, try to apply it to every use case, which is kind of what happened. It was the tool, right? And, you know, I'd say maybe early to mid-2000s, right? Like that was everything's database.

Even before, people were using database. But around that time, we started to run into problems where people were kind of using it, and maybe it wasn't the best tool. And part of that is that a lot of these servers strive for ACID compliance, which is atomicity, which we talked about being able to execute one instance in time, all those things, and it all works and nothing works.

I think I'm getting it right. Consistency, so that things return the same for all of us. We don't get mixed results. And then that gets the problem when you have sharding and stuff. One server can't handle everybody, so I have to split it up. Isolation and durability. Durability has to do, I to do with surviving across reboots, right? So if you get randomly rebooted,

you know that you'll come back at the same time. Isolation. I'm not sure on that one. I'm not good. Actually, I don't know either isolation. Oh, here you go. There's a little article on it. Ensures that the concurrent execution of transactions. Oh, I i see so isolation means that

if there's like let's say there's i'm trying to buy a book and patrick's trying to buy a book and let's say there really are two in stock let's not worry about the crazy corner case the idea is if i buy and patrick buys or he buys first or we both buy at the same time, the end result should be the same. Like, it shouldn't be such that I buy the book. I mean, you can imagine this going wrong, right? Let's say there's two books, and let's say there's two in stock, and the code says take the number in stock and then subtract one and then set that to the new number. Well, if we both hit it at the same time, we'll both see two,

we'll both subtract one, and then we'll both store one in the same time, we'll both see two. We'll both subtract one. And then we'll both store one in the database. And we really should have been zero. Yeah. So this acid thing is very useful. And many systems really need this. Yeah. But if you can do without one of these, you can begin to design something that's slightly different.

It's not exactly the same as what you were used to. Yeah. But, like, consistency is a big one. So consistency is, like we talked about, if you have multiple things running, I can write to one, and consistency says if I write to instance zero, instance three, when Jason tries to read from it

right after I write, also reflects the same thing. But that's pretty hard to do. So it doesn't always have to be true. Like, look at Twitter, for example. Right. So you get the notion of eventual consistency. Yeah. So it doesn't matter in Twitter if Jason tweets.

I don't mean to overstep that. No, no. Jason tweets, right? It doesn't matter if I go to check and I don't see Jason's tweet. It's the same millisecond he tweets. But I do want to see it within five seconds. Yep. But five seconds is a lot longer than zero.

Yep. Totally. So if I get to eventual eventual consistency or maybe you don't even need consistency you know if it's a game or something maybe if you don't get it right away or it doesn't really matter it's not a big deal possibly it so if you can begin to start to violate some of these rules or you can do without transactions or without you without the durability that, you know, like again, like if I'm running a game server, maybe some parts of it, like all the people currently in the world,

I don't need that to be durable. If it crashes and comes back out, everybody just starts back at their home base. Or if you're running like a chess server where you're hosting a bunch of chess games and the server dies and as a result of the server dying, all the people playing their games are lost. The reality is they'll just start new games, right?

It's not going to be the end of the world. But you would want to make, but you might have different needs. So like the player's rank, that might want to be durable. And you might want to use an SQL database for that. But then for just hosting the games, maybe it's really expensive to do all of those transactions against a database. So you might want to use something else. And these are the kinds of things that people began to deal with.

And out came some of these things. Out came the NoSQL. Yeah. So the NoSQL movement is kind of a, what do you call it, like a misnomer? Like it's not exactly. It has nothing to do. It's like counterculture. Yeah.

It's one of those terms that doesn't make a whole lot of sense. But what it's getting at is that in NoSQL, instead of having these relationships defined and doing these expensive joins and indices, it's really geared to doing point lookups. So I just need to look up some bit of data. Like I want to look up this specific information about JSON, and I want to just store, kind of going back to what you talked about, like a whole complex thing, maybe some JSON or maybe some XML,

you know, something that's very structured. And I want to store all of that in one place. And I just want to pull it, look it up, and then I'll do some sort of analysis. And so, oh, I guess I'm skipping ahead. But running queries on that kind of thing is something slightly different. So if you're running on, let's say, Hadoop,

you're going to run, let's say you're going to run across analysis, everything, every row in the database. Let's say you have, so there's like an object store database. And what this does is this stores like an entire, as Patrick said, entire JSON or XML string. And you can think of it as sort of key value pairs. So there still is a primary key somewhere, like there's a user ID or something like that. And then the value is just this huge chunk of data. And the idea here is, you know, with SQL, it's really hard to do consistency across machines.

I mean, you have to, like, take all your transactions, broadcast them. It's the N squared problem, right? Like, if you have five machines, every transaction, you know, that hits a machine then has to get spread to all the other five machines, right? So a different way of doing it would be oh let's have each of the machines just store a different amount of data like each of the machines store some part of the database and then I can query all five of them which you know that part is more expensive but the idea is I could have like a thousand machines or ten thousand machines they all are just storing bits and pieces of this

database and even though it's more expensive to do this whole query, you know, asking 10,000 machines to do something is always going to be way faster than having five, right? Wow. You hope it's way faster. Well, I mean, yeah. I mean, in terms of this database stuff, yeah. But I mean, especially, like we were talking about, for the point where you need to read every row.

Right. And then you're going to do something, some manipulation of everything. SQL is not exactly, I mean, it can do that, I guess. I wouldn't say it's not meant for it. I mean, but that's not really the purpose. Right. So if I can store it somehow else, or if I'm making updates and I only need to just update

one specific thing, you know, and then when I read it, it's going to be in this manner. Or when I read it, I'm going to go pull the very specific row I need and it's got everything I need to it. Yep. Then in some ways it's overkill to use SQL. And you're sacrificing stuff by doing that. Yeah, totally, totally. And so there are languages written with this in mind.

So you have a NoSQL database or even like you're running in Hadoop and something and then there are languages written to help you write those queries so you don't have to necessarily write Java code that has to run at each stage of the pipeline. You can write this query, and it'll generate the Java code for you. Yeah, and I mean, a lot of these people, they're not dummies. They know that a lot of the people who work with databases are used to SQL. And so they made theirs in sort of the form of SQL.

So one is HiveQL, and that's part of the Apache Hive project. And so the idea is it'll go through a Hadoop database, or rather not a Hadoop. Hadoop is sort of like a clustering platform. It'll go over a Hive database. So that's like the file system, like the data storage layer. Yeah, exactly. It'll go through a Hive database, and it will, you know you know do so you could say things like select all of the documents where there is a you know in the JSON there's a name key and that name keys equal to Patrick or something like that and so it feels a lot like SQL but it's querying a completely different data structure on the back end it is not really I mean those are really meant for a real-time though like you're not serving something on a website without something batch processing.

Like, I wonder about some report or analysis. And it's actually somewhat common for people to do a little bit of both. Like, I've heard of cases where people have an SQL database up front, which has a lot of the indices and the support for, like, pulling individual records. But then they end up piping it out to some like hadoop based system or some cluster for doing like the heavy lifting like look at everybody and calculate the average amount spent and all these other statistics standard deviation and things like that so yeah because you don't want to make your users suffer when you're trying to run some report yeah exactly that'd be bad yeah it used to be where you had two copies of your database you had the one you know that was latent

that was for doing your analysis. But as Patrick mentioned, SQL is not really designed for that. It's designed for serving. So if you're going to have two copies, it makes more sense for your second copy to be NoSQL. Yep, and so there are other tools related to all of this. So we talked about SQLite before.

It's a tool of the week. So this is one that's an SQL database that runs locally and does write to a file on the back end that tries to make it more efficient up front. And there's MySQL, Postgres SQL. What is it? Oracle bought MySQL, but they have another SQL as well. Yeah, Oracle also

has Berkeley DB. Okay. They own that. Microsoft has some... Kind of like SQL Lite. SQL Server. Yeah, yeah. So there's, I mean, there's all sorts of tools. And there's, I mean, there's all sorts of tools. And there's tools for optimizing, running stuff in memory, making, you know, caching of queries, this kind of thing. Yeah, SQL Lite is a good one to sort of start with

because you can always just delete the file and your database is gone. You don't have, like, some service running in the background. Like, you don't have to know SQL to, like, clean the database. And it's also easy to switch over from executing it against your local one to a remote one. So you're not wasting a lot of time. JOHN MCCUTCHAN- Yeah. DAN GALPIN- And pretty much everywhere on the web

uses a database in some manner. But I mean, even things like WordPress, all the articles that when you display on the back end, now there's some optimization that goes on, I'm sure. But all of those articles that text is stored as some blob of text in a row in a database somewhere. And when you go to the web page, in theory, it's querying that database and getting the stories by date and showing them to you on the front page.

When you go to the next page, it's saying, get me the next three articles and showing you those. And in fact, like Wikipedia, you know, DBia is that is that what it's called no media wiki sorry media wiki which what which is what wikipedia is based on is um that software uses sql and you can actually download the sql file for all of wikipedia it's like this gigantic for trivopedia yeah and so so for people who don't know, SQL, you can select things from the database, which we talked about. You can also insert, update, and delete items from the database. So you can actually think of storing things in SQL code. Like if you had SQL code that, you know, created a database and then inserted

Patrick, you know, user ID, blah, blah, blah, Patrick, and then inserted all the things Patrick bought, that source code really has data in it. And that is the data. So a lot of these programs like MySQL, when you go to like dump the database or back up the database you get a gigantic block of code which is really just instructions for recreating the database from scratch and so wikipedia does this frequently and you can download like the biggest source file you'll ever see in your life like a 30 gigabyte source file which has all of the data i'll pass yeah all right well i think that's pretty much it. Oh, okay.

So we alluded to it earlier, but I think you have grand plans for our next recording, which we have to figure out the date, but we'll post it on our Google Plus page. Yeah, keep on the Google Plus page. We want to do a Q&A episode. We'll see how it works. I don't know yet.

We haven't sort of fleshed out the details, if it'll be a Google Hangout. I think that'll be good. We have to figure out how to record it right so i don't want to have to just like read the like re-say what people are saying so we have to figure this out it'll be a good experiment for us to learn totally um maybe we can just record the hangout i think you can record a hangout we'll see we'll look into it and uh we'll figure out the exact format we'll let you know maybe we'll have a skype number and people can skype we'll have to do it somewhere crazy so we'll be okay we'll be somewhere crazy well we'll be somewhere we'll be

somewhere i don't know what to make crazy everywhere is crazy okay it's us you know so so uh we'll we'll do the show over hangouts uh you'll see our goofy faces should we if we record the hangout i guess we could just post the hangout as well in addition to the mp3 sure we'll figure out something but anyways we're gonna have you guys call in yeah we'll see if not we could just post the hangout as well in addition to the MP3. Sure, we'll figure out something. But anyways, we're going to have you guys call in. Yeah. We'll see. If not, we'll just talk about news, I guess. Yeah, yeah.

Until people call in general. Like 90 minutes of news if you don't call in. Might be a short episode. But yeah, it will be a Q&A. Definitely post if certain days are good for you or times are good. We're curious to see sort of like what people's interest level is. So yeah yeah definitely post on the plus page and let us

know what you think all right thank you all all right see you later the intro music is axo by biner pilot programming throwdown is distributed under a creative commons attribution share alike 2.0 license you're free to share copy, copy, distribute, transmit the work, to remix, adapt the work, but you must provide attribution to Patrick and I and share alike in kind.