Placeholder Image

字幕表 動画を再生する

  • [MUSIC PLAYING]

  • COLTON OGDEN: All right, and we're live.

  • Hello, world.

  • This is CS50 on Twitch.

  • And I'm joined by CS50's own David Malan.

  • DAVID MALAN: Nice to see everyone again.

  • Hello to the viewers in the chat room right now.

  • We've got a few people in there.

  • We got to see [? Bavick ?] [? Knights. ?]

  • Scotty529 says, I don't have SQL experience but I'm excited to learn.

  • Excited to teach some SQL today.

  • Or I'm going to be learning some SQL today.

  • Yeah, Colton is going to be pretending not to know much about SQL.

  • So--

  • COLTON OGDEN: Pretend.

  • DAVID MALAN: --you can experience vicariously

  • what it's like to learn a little bit of structured query language today.

  • COLTON OGDEN: Sure.

  • Andre's here in the chat, says hello.

  • Hello, Andre.

  • Good to see you.

  • Good to see [? Bavick ?] [? Knight, ?] Zodiac.

  • And [? Nuwanda, ?] that's [INAUDIBLE] from last when

  • we did the concentration game.

  • DAVID MALAN: Yes, good to see some familiar usernames here.

  • Some of which are a little hard to pronounce,

  • since they're not exactly words.

  • But I hope you'll forgive just a little wave from us here.

  • COLTON OGDEN: All right, and so what are we going to be talking about today?

  • DAVID MALAN: So why don't we just talk about databases?

  • We actually, in CS50, of course every semester talk about SQL

  • and databases more generally.

  • But we tend to move through it really quickly.

  • We don't necessarily get too much into the weeds of doing things hands on.

  • And in fact, even when we do, we tend to use CS50 IDE.

  • But for the more comfortable audience that might be tuning in via Twitch,

  • thought we could play a little more hands on the terminal window

  • on my own Mac, which you can certainly do on your own PC

  • as well, so that it feels a little more real-world representative and less

  • classroom like.

  • COLTON OGDEN: Cool.

  • Yeah, sounds great to me.

  • Do you want me to move on into your laptop on the screen?

  • DAVID MALAN: Yeah, if you don't mind.

  • I pulled up a little throwback from 1999.

  • COLTON OGDEN: Oh, are we--

  • DAVID MALAN: Let's see if we're plugged in here.

  • Give us just one moment.

  • COLTON OGDEN: Should be plugged in.

  • DAVID MALAN: So what you're about to see is a screenshot from a website

  • that I think I first created back in, like, 1997.

  • I was a sophomore or junior in college at the time.

  • And I was part of the freshman intramural sports program,

  • or Frosh IMS program.

  • This--

  • COLTON OGDEN: I think you might need to-- are you on--

  • [INTERPOSING VOICES]?

  • COLTON OGDEN: Are you on 720p?

  • DAVID MALAN: Ah, let's take a look.

  • Pardon me.

  • We're just going to go into my system preferences here on Mac OS

  • and make sure I'm in the right resolution.

  • SPEAKER 1: Or you can just mirror it.

  • DAVID MALAN: Let me go ahead and mirror my display here.

  • All right.

  • COLTON OGDEN: Appreciate it.

  • DAVID MALAN: Now that we have that, let's go ahead and give you 720p.

  • COLTON OGDEN: Awesome.

  • DAVID MALAN: There we go.

  • Cool.

  • COLTON OGDEN: There we go.

  • DAVID MALAN: No movie magic here.

  • Everything is happening incorrectly here in Cambridge, Massachusetts,

  • as you see it.

  • No post-production.

  • So what you're seeing on the screen now behind me and Colton is Frosh IMS,

  • the freshman intramural website.

  • COLTON OGDEN: Who designed that great background?

  • DAVID MALAN: Well, obviously I did.

  • Though to be fair, it was probably something we found on Google images

  • before there was even Google images back in the day.

  • COLTON OGDEN: How did you find images back then, if it--

  • were there like user groups, or?

  • [LAUGHTER]

  • DAVID MALAN: I think I had to call someone on the phone

  • to get an image back then.

  • COLTON OGDEN: Send a floppy over the--

  • DAVID MALAN: Yeah, pretty much.

  • The [INAUDIBLE] too.

  • Someone would deliver it to you in the mail.

  • Yeah, no, I don't remember where the image came from.

  • But clearly it's all in its 1990s glory here.

  • And at the time, like, so Harvard for the students

  • had an internal sports program, which just

  • means sports for those of us like myself who

  • couldn't compete at the varsity or junior varsity or really any level.

  • And was really just for fun.

  • But at the time, if you wanted to sign up for sports,

  • you would literally fill out a piece of paper, walk across campus,

  • and then hand it to someone physically or slide it under their door.

  • And then you were registered for the sport.

  • So nowadays, you would clearly think that you could at least use a Google

  • form for this.

  • But back in the day, there was no Google.

  • There were no Google forms.

  • And even then, there was no website even for the intramural program.

  • So my first foray into web programming was just to learn it on my own.

  • I mean, I did not learn how to do web programming in CS50 or another class

  • here called CS51.

  • So I was completely self-taught.

  • And frankly, to this day I think the best way to learn something new

  • is just force yourself to do it and pick it up--

  • COLTON OGDEN: Almost feels like the--

  • DAVID MALAN: --along the way.

  • COLTON OGDEN: --Wild West of the web in a way.

  • DAVID MALAN: Yeah.

  • You were alive at the time, I think.

  • It wasn't all that long ago.

  • COLTON OGDEN: Ashley says thanks for remembering my name, Colton.

  • Thanks, Ashley, for joining us today.

  • DAVID MALAN: Yeah, indeed.

  • Good to see everyone.

  • And interject at any point over the chat with questions

  • about this or really any other topic.

  • So we always talk about Frosh IMS, not just so

  • that I can relive my glory days, but so that we

  • can use it as a point of departure for talking about where the data actually

  • goes.

  • So the whole point of the Frosh IMS website,

  • besides being informational and telling people about the website,

  • about the sports that were upcoming, was to allow

  • students to click a link, type in their name, their dorm, maybe their phone

  • number, although no one really had cell phones back then, their email address,

  • and then click submit so as to register.

  • And at the time, I had no idea what to do with the data

  • once someone actually clicked submit.

  • So I pretty much found a library, which did exist at the time,

  • and I just sent an email to the person who was running the sports program so

  • that he or she could just have in their inbox

  • a list of all the kids who had registered.

  • So this is clearly an opportunity for a database.

  • And like, what would you have done maybe back in the day

  • if you needed to store something elsewhere than someone's inbox?

  • COLTON OGDEN: Probably written it down on a piece of notebook paper.

  • But that's probably not a sustainable solution.

  • DAVID MALAN: No, that's essentially what people did there.

  • And let's see, actually, Andre's noting back

  • in the 1990s we used smoke signals for internet transfer.

  • The big breakthrough happened when Morse code was invented.

  • Wow, that's very, very, very well--

  • very appreciated comment, Andre.

  • I'm pretty sure that's not entirely true, for those of you who

  • were born in the 90s or after.

  • COLTON OGDEN: JP Guy's rating with a party of five.

  • So I believe that means, and I'm somewhat new to Twitch myself,

  • but JP has actually brought his channel's chat into our chat.

  • DAVID MALAN: Oh, nice.

  • COLTON OGDEN: That's what that-- the connotation of that.

  • So thanks so much, JP, for that.

  • Dr. [? Profedo ?] says raid, raid.

  • Heard you had cool hair, says [? Tangora. ?] Thanks.

  • Appreciate that.

  • JP Guy has his emoji there.

  • DAVID MALAN: No.

  • In fact, we'll be having an upcoming Livestream about Colton's hair.

  • So be sure to tune in later this week on that.

  • COLTON OGDEN: Oh man, oh man.

  • [INAUDIBLE] you want to read-- maybe read some of the other ones

  • below Andre's there too?

  • DAVID MALAN: Yup.

  • Whip Streak, nice to see you as well.

  • So what of course might you do with something like this?

  • So back in the day, what I would do is actually store

  • the data, not just in someone's email inbox, but in a CSV file,

  • or comma separated values.

  • Which is kind of a poor man's database, so to speak, whereby all you need to do

  • is to be able to do F open and then F write,

  • and then actually write the data to a file.

  • And that's it.

  • And it's kind of a lightweight database.

  • Wouldn't you agree?

  • Yeah, that's right.

  • So he's not quite on screen, so it just seems like I'm talking to no one there.

  • So but of course, the problem with CSV files might be what?

  • Like where does the limitation come to mind?

  • COLTON OGDEN: A little bit less than human readable, I would probably say.

  • DAVID MALAN: Yeah, it gets a little annoying,

  • especially when you've got more and more words and lines.

  • COLTON OGDEN: I was going to say if I were to do it back in the day,

  • probably would have opened something like a text file

  • and just written them out line by line.

  • But that's effectively what a CSV is.

  • DAVID MALAN: Yeah.

  • You're just using commas to sort of imply that there's a column there.

  • COLTON OGDEN: Sure.

  • DAVID MALAN: Right?

  • COLTON OGDEN: Yeah, that's the only thing I guess I could think of,

  • would be that it's a little bit hard.

  • Hard to do.

  • Hard to maybe find things you're looking for, I guess, in a programmatic way.

  • But I guess we'll get into a little bit of that.

  • DAVID MALAN: Yeah, no, I mean, Colton's right.

  • Because if you think about CS50, or if you have any other prior programming

  • experience, you might have thought about running time or performance.

  • And a CSV file is a file.

  • And files are slow, partly because you can only read them top to bottom.

  • Like you have to start at the first byte and read all the way to the end.

  • And then you have to do sort of big o of n or linear search

  • on anything, which can grow-- it can become tedious, certainly,

  • once the data set gets large.

  • So Andre, you're about the same age as me, I believe.

  • Just pointing out that talking about the olden days of the internet

  • makes me feel, well, you are old.

  • I think you're much older than me, if I may say.

  • So I'm just reminiscing about things I've heard about, of course, Andre.

  • But thank you.

  • COLTON OGDEN: We missed [? Gossen's ?] note there. [? Gossen119 ?] says,

  • is this a part of the Harvard CS50 course?

  • DAVID MALAN: It is.

  • Well, it's sort of ancillary.

  • So Colton has been taking the charge on leading this and other sessions

  • with Kareem, Bryant.

  • Nick you'll meet soon too.

  • Really doing something beyond the scope of the course

  • to have a more casual chat with folks interactively, albeit with us

  • on video and you on chat, so we can explore more topics hands

  • on especially.

  • COLTON OGDEN: Yeah, sort of shift a little bit of maybe the load on lecture

  • from coding to doing that here, given time limitations.

  • DAVID MALAN: Yeah.

  • So David asks, what's the difference between SQL and NoSQL?

  • So in a moment we'll dive into more detail on what SQL itself is.

  • But SQL, you'll find, is a language for manipulating relational databases.

  • And the simplest explanation of a relational database

  • is something that's based in rows and columns.

  • Really it can do spreadsheets, but running into computers memory

  • not just visually laid out for the human.

  • NoSQL is not that.

  • It instead tends to store things as documents, so to speak,

  • or as objects where there is actually more hierarchy and even nested

  • structure.

  • [? Gossen119, ?] I wouldn't say SQL lacks security.

  • I'm not quite sure what you mean by that.

  • In fact, you're welcome to elaborate here so that we can respond.

  • They are really different data models, so to speak, and different mindsets.

  • And NoSQL has come into vogue in recent years,

  • but SQL still very much has a lot of momentum still.

  • And in fact, it allows you to model your data a little more, let's say,

  • with a little more rigor, perhaps, even than a lot of object storers do.

  • But more on that in a bit.

  • COLTON OGDEN: So NoSQL would be a little bit more flexible, but maybe not

  • as efficient?

  • DAVID MALAN: It's definitely more flexible.

  • It eliminates some of the pain involved in actually thinking

  • through your schema and factoring everything out.

  • It can be more performant, in that you don't necessarily

  • have to rejoin data in ways that we'll talk about in a bit.

  • But really to each his or her own.

  • They can both be used for similar problems.

  • So in MySQL is a very specific implementation of SQL,

  • also called Maria DB these days.

  • MySQL was just one of the most popular versions of software

  • via which you can host a SQL database.

  • COLTON OGDEN: I didn't realize those were the same.

  • DAVID MALAN: Which one?

  • COLTON OGDEN: Maria DB and MySQL.

  • DAVID MALAN: I think Maria DB is like the open source spinoff or fork of what

  • has been historically MySQL.

  • But I'd have to dig back into the details in Wikipedia or the like.

  • It coexists though alongside things like Postgres,

  • which you might have heard of.

  • Heroku uses that.

  • Oracle, certainly one of the bigger fish in the space, Microsoft Access SQL

  • Server.

  • So it's just an alternative, but a free and open source alternative.

  • COLTON OGDEN: Is it the case that CS50 is potentially

  • going to look more into Postgres for future stuff?

  • Is that something you were mentioning in lecture?

  • DAVID MALAN: A little bit.

  • Only in so-- so back in the day, we used MySQL in CS50.

  • And honestly, it was just a pain in the neck

  • for students to run not only their own web server on their Mac or PC

  • or in the virtual machine or now in CS50 ID, but also a database server as well.

  • It's another process.

  • It's another port.

  • It's just another thing to manage.

  • So we actually transitioned to SQL Lite instead, which we'll use today as well.

  • Postgres is more analogous to MySQL, competitor to it if you will.

  • And we have introduced students to the data types used by Postgres this year.

  • Because Heroku, frankly, which is this wonderfully free, very easy to use,

  • cloud-based service for hosting a web app, it's what they offer.

  • And it was a nice way to sort of prepare students to graduate from CS50

  • and go off into the real world with one such popular service.

  • COLTON OGDEN: OK, cool.

  • Sounds good.

  • Makes sense.

  • DAVID MALAN: Indeed, yeah.

  • So let's see.

  • Perhaps this has been asked before, but how

  • do you handle big data in terms of SQL or other database structures?

  • OK, so hang in there for a look at what SQL actually is.

  • But for those a little bit familiar, you can use SQL as well as NoSQL

  • for big data.

  • It really depends on what your definition of big data is.

  • But you'll see that within a SQL database

  • there's a lot of design decisions that you can make along

  • the way to improve performance.

  • Indexing certain fields, deciding how to or not to normalize your database

  • can be a big question there.

  • NoSQL can certainly be used as well.

  • It kind of depends on your data set.

  • It depends on your comfort.

  • And honestly, it depends on if and when you bump up

  • against some actual limitations.

  • Starting with what you know, starting with what

  • you're comfortable with is always a reasonable design decision, I think.

  • And when you actually have a problem to solve,

  • does it make perfect sense too to rethink that whole architect?

  • COLTON OGDEN: And big data--

  • DAVID MALAN: Architecture.

  • COLTON OGDEN: --normally you hear like Python,

  • and are big languages and big data.

  • So I'm assuming you can use MySQL with other programming languages too, right?

  • DAVID MALAN: Oh yeah.

  • I mean, those languages are fairly agnostic to big or little data.

  • But the database can certainly be finetuned to the size of the data

  • that you actually care about.

  • And this is a can of worms of topics too.

  • You can introduce discussions of replication of databases,

  • caching of databases, views that you can impose on databases.

  • There's so many ways to make more efficient sense of the data

  • that you have.

  • And we'll only scratch the surface surely today.

  • But keep the questions coming.

  • COLTON OGDEN: Cool.

  • Makes sense.

  • DAVID MALAN: All right.

  • So shall we talk about what SQL, is for those less familiar, perhaps?

  • COLTON OGDEN: Let's do it.

  • Let's take a look.

  • DAVID MALAN: All right.

  • So let's go ahead and formulate things like this.

  • Let me go ahead and open up a Google spreadsheet here, untitled spreadsheet.

  • It's got nothing in it by default.

  • But what's nice about a spreadsheet, whether it's Google Spreadsheets

  • or Excel or Apple Numbers if you're more familiar,

  • is that it lays out your data in rows and columns.

  • And of course whenever you're using a spreadsheet,

  • like, how do you use these rows and columns personally?

  • COLTON OGDEN: I usually categorize my--

  • I mean, I guess you could do it both ways.

  • But I'll usually have a category of information along the top.

  • DAVID MALAN: OK.

  • COLTON OGDEN: So that first row, maybe it's there would be a name field,

  • and like an address field.

  • And then each-- I guess it would be shifted over,

  • and then each on the left, that column there,

  • will be the individual people or the individual entries in the database pf

  • my document.

  • DAVID MALAN: OK.

  • COLTON OGDEN: So David on the first one, Colton on the second one.

  • And probably the name is duplicated in this instance,

  • if we were to just put their name there, but the same idea applies.

  • DAVID MALAN: Yeah.

  • So the columns each represent something like an entity, so to speak,

  • or a field.

  • And then the rows represent a piece of data broken out

  • into these individual cells within those columns.

  • So you could do it completely the opposite.

  • Like what Colton just said could be completely flipped with rows

  • and columns and columns and rows.

  • But frankly, this just happens to be a little more user friendly in

  • that you can scroll down, which is just more pleasant generally, when

  • you want to look through your data.

  • And you only scroll left to right when you

  • want to see additional fields if they don't all fit on the screen.

  • Yeah, so this is exactly the mental model

  • you should have when it comes to a database.

  • But the problem when it comes to a spreadsheet

  • is when it gets large, like thousands of rows or tens of thousands of rows.

  • Like Google Spreadsheets and Excel and Numbers,

  • they're not meant to handle tens of thousands of rows.

  • Like you could still hit control F or command F to search for something,

  • but you might get the stupid spinning beach ball or hourglass

  • because they're just not designed to search over huge data sets efficiently.

  • And you can certainly edit them by moving your cursor up,

  • down, left, right and typing, but there's

  • no easy way other than find and replace to do things programmatically.

  • And so very quickly, or eventually rather, especially with big

  • data to your point earlier, does it become the wrong tool for the job.

  • COLTON OGDEN: Sure.

  • DAVID MALAN: So SQL Lite, or SQL more generally,

  • is a language via which you can read and write and update and delete data.

  • But using actual commands, not just using your keyboard or your mouse,

  • but actually writing code in a language called SQL.

  • So let's go ahead and do this.

  • Let's actually flesh out what we might want to store in this database.

  • You proposed name and address.

  • What else might a human have?

  • COLTON OGDEN: Sure.

  • So maybe a phone number, because now people do have cell phone numbers.

  • DAVID MALAN: OK, thank you.

  • Maybe an email address like we said earlier.

  • COLTON OGDEN: Yeah, email address.

  • And I guess it depends on what this would be used for,

  • but we probably could suffice with four or five fields for now.

  • DAVID MALAN: OK, yeah.

  • Let me just boldface these in the spreadsheet,

  • even though this has no semantic meaning.

  • Just to make clear that those are our columns or our fields.

  • And then a few of-- a couple of us will be like the data set here.

  • So I might live at, say, 33 Oxford Street in Cambridge, Massachusetts,

  • 02138.

  • And the number here might be 617-495-9000, and Malan@Harvard.edu.

  • Whoops.

  • E-D-U.

  • Let's go ahead and make a little more room here by blowing these out.

  • And then you, meanwhile, might live down the street at 1 Oxford Street,

  • maybe Cambridge, Massachusetts, 02138.

  • 617, we can all go through the same switchboard, 495-9000.

  • And then Cogden@CS50.Harvard.edu.

  • All right, so that might be two representative pieces of data.

  • And those of you following along online might

  • notice that there's already a little bit of redundancy in here.

  • And odds are you can spot this too.

  • Whip Streak chimes in here.

  • I know this is completely unrelevant, but Colton said to make suggestions.

  • And I would suggest the tic-tac-toe in [INAUDIBLE] and love.

  • COLTON OGDEN: Duly noted, Whip Streak.

  • Thank you.

  • That sounds like something we could pretty easily do, so yeah.

  • We'll take a look at that.

  • DAVID MALAN: Yeah.

  • Let's do it.

  • Tic-tac-toe in [INAUDIBLE] and love.

  • Go for it.

  • I'll do that maybe in another stream.

  • I will prepare for a little bit of time there.

  • So, OK-- so [? bobak, ?] you propose an ID number.

  • So yeah, that's actually a good idea, too.

  • The spreadsheet kind of gives us that notion--

  • with the column all the way on the left, the one, two, three-- but of course,

  • those numbers are going to change if we sort things.

  • So absolutely, I like this idea.

  • Let me go ahead and insert another column

  • and just arbitrarily assign each of us a number-- generally,

  • by convention, starting at one-- and then just auto

  • incrementing it's way on up to 2 billion, 4 billion-- however high

  • you want to go.

  • And this is going to be advantageous because SQL is indeed

  • a relational language whereby the whole purpose is to relate data in one sheet,

  • if you will, to data in another.

  • And so we're going to find that this number is actually

  • a nice way of relating data in this sheet to another data set all together.

  • COLTON OGDEN: Maybe another database where we have courses that we teach,

  • for example.

  • And then you teach a SQL course, I'll teach a games course--

  • and maybe the other database can map the IDs to those courses?

  • DAVID MALAN: Yeah, so let's actually do that.

  • Let me go ahead and rename this spreadsheet

  • for the moment to say Instructors.

  • And that kind of implies that we'll have some other tables

  • here in just a moment, too.

  • There is some redundancy though, here.

  • What would you point out as being redundant?

  • COLTON OGDEN: Probably-- well, the phone number, for one thing.

  • It's duplicated amongst both of our--

  • DAVID MALAN: --OK, that's fair--

  • COLTON OGDEN: --tables.

  • And the addresses both contain Cambridge, Massachusetts,

  • and the 02138.

  • DAVID MALAN: Yeah.

  • COLTON OGDEN: So there's probably an ability for us

  • to factor that out somehow.

  • DAVID MALAN: Yeah, and so there's a buzz word in database design called

  • normalization, where as soon as there's an opportunity to factor out

  • common text that appears again and again and again, odds

  • are you'll benefit space wise by just removing it, putting it in one place

  • once, and then somehow linking the data.

  • Now, this is a bit of a mislead, because it turns out

  • in the US that zip codes do not necessarily uniquely identify cities.

  • But for the sake of discussion, let's assume as much.

  • And let me go ahead and create another sheet here called, let's say-- cities.

  • And maybe that city is going to have a city name, a state, and a zip

  • code associated with it.

  • But because zip code is going to be a little more important here,

  • I'm going to go ahead and--

  • well, what you want to put in this table, exactly?

  • What do you think?

  • COLTON OGDEN: In the zip code?

  • DAVID MALAN: Yeah.

  • And what do you want to factor out here, exactly?

  • COLTON OGDEN: Probably this--

  • well, I'm trying to think how it would be best to store--

  • because the table itself wouldn't semantically

  • know that a state belongs to it--

  • I guess we could have a states table--

  • DAVID MALAN: --hello from Cambridge--

  • COLTON OGDEN: --so we could have states that maybe get an ID.

  • DAVID MALAN: OK.

  • COLTON OGDEN: And then cities that--

  • I guess they could also get an ID.

  • DAVID MALAN: Yeah, we could kind of continue this pretty deep.

  • So let's keep it simple, maybe.

  • And let me propose that why don't we call this not cities, but zips--

  • or postal codes, for those more familiar with that term.

  • Let's just move this column over here, just because it kind of

  • is most important.

  • And to your point earlier, let's go ahead

  • and create an ID column as well, just so that we can number these things

  • arbitrarily, but with simple numbers.

  • And let's go ahead and put in one of these zips--

  • 02138.

  • And you already see a potential problem with databases.

  • COLTON OGDEN: You had this issue a long time ago doing something, didn't you?

  • DAVID MALAN: I did.

  • So-- stupid, stupid feature, sometimes.

  • Right?

  • The leading 0 disappeared.

  • But here in Cambridge, Massachusetts, we need to have that zero.

  • The fix, of course, is to reformat your data--

  • which is an issue we'll come back to in a minute-- as plain text,

  • and now tell Google not to ignore it.

  • And indeed, yeah.

  • When I used to use Microsoft Outlook for email years ago,

  • at some point I transitioned to Gmail and I exported all of my contacts

  • from Outlook--

  • some of whom lived in Cambridge, Massachusetts, 02138.

  • And damn it if I didn't make the mistake of opening

  • the export, which was a CSV file-- so comma separated value file.

  • Accidentally must have hit Command-S or Control-S to save the file,

  • and stupid Excel got rid of all of those leading zeros on all

  • of my friends' and contacts' zip codes.

  • And so to this day-- years later--

  • I'm living with friends who still have 2138 as their zip code,

  • because I haven't fixed them all manually.

  • COLTON OGDEN: It's an unfortunate consequence

  • of technology trying to work in your favor and screwing it up.

  • DAVID MALAN: Indeed.

  • So, [? Bavek, ?] if we are indeed moving right now.

  • Maybe try a reload if you're only seeing the chat and not the video.

  • We are doing a bit of typing in this spreadsheet,

  • and I think our human bodies are moving a little bit, as well.

  • So the database automatically generates--

  • OK, so David, you're mixing technologies here.

  • So MongoDB is an example of a NoSQL database,

  • and indeed it can create unique identifiers for you,

  • but frankly, so can SQL.

  • And right now-- we've not even looked at SQL, per se.

  • We're literally just using Google Spreadsheets

  • and manually adding these IDs.

  • But most database engines in SQL can also

  • add these unique identifiers for you.

  • So you do not need to create one manually--

  • as I'm currently doing for the sake of discussion.

  • COLTON OGDEN: And JP, in solidarity, says "thanks Excel.

  • Face palm."

  • DAVID MALAN: Yes.

  • Indeed, indeed.

  • So let's just finish this up.

  • So if you know that 02138--

  • for the sake of discussion-- always maps to Cambridge, Massachusetts,

  • we can go ahead and create a table-- a row-- just like that.

  • And let's do another one I know from childhood--

  • Andre, maybe you're in on this with me, even though different country.

  • 90210 is one of the few other zip codes I know, and that's Beverly--

  • Beverly Hills, California.

  • So we might have another row in this table, there.

  • And let's just give these unique IDs of 1 and 2 as well.

  • So what you can do now is notice that Cambridge, Mass,

  • 02138 has a unique identifier of 1.

  • So what we could do is go over to the instructors

  • and actually change the address to be something like this--

  • not just be an address, but we could have it have a city column.

  • And we can clarify this as just the street address,

  • so that now this is 33 Oxford Street, and then city ID equals 1.

  • And then meanwhile, you--

  • Colton-- live at 1 Oxford Street in this story,

  • so your city ID is also going to be 1.

  • Let me go ahead and just left align everything for consistency.

  • And so now you can see that we're storing the same information

  • but across multiple tables.

  • And so we've made a relationship between the instructors' table and the zips'

  • table so that, if you do somehow combine these two,

  • you can reconstruct the original data set.

  • COLTON OGDEN: And it's not worth necessarily storing all of the street

  • addresses because there are just so many of them,

  • but there is a smaller but still sizable number of cities and-- well,

  • I guess countries, as well, you could have--

  • but zips.

  • DAVID MALAN: Exactly.

  • We could certainly continue this, and you could certainly factor out

  • Massachusetts and have a state ID.

  • You could factor out country, if we'd included that, and have a country ID.

  • But at some point you're just creating work for yourself,

  • and you have to decide for yourself-- design-wise--

  • where that inflection point is.

  • So you mentioned before that we teach some courses.

  • So let's add one more table.

  • Let me go ahead and create a sheet called Courses.

  • And one will be called, for instance, CS50,

  • and that's Introduction to Computer Science.

  • The other one is nicknamed GD50, which we

  • call Introduction to Game Development.

  • And now these things, of course, should have some columns.

  • So what would you title the columns here, would you say?

  • COLTON OGDEN: So, course abbreviation is not the greatest one,

  • but maybe course handle?

  • DAVID MALAN: Handle?

  • OK.

  • COLTON OGDEN: And then, course title.

  • DAVID MALAN: Title.

  • OK.

  • I'll boldface these just to make clear that those are the field names.

  • Let me go ahead and-- again, just for consistency-- create an ID column.

  • And it's OK that I'm reusing 1 and 2 and 3 and 4 and 5

  • again and again, because they're in different tables in this case.

  • So now we need to somehow relate who teaches a course to the course itself.

  • So let me propose, as a straw man, this.

  • Let me go ahead and say the instructor ID for this course is-- well let's see.

  • If I go back to the instructors table, so David is ID one.

  • Colton is ID two.

  • So if I teach CS50, we'll put a one here.

  • And if you teach GD50, I'll put a one here.

  • Let me line everything on the left again.

  • Oh, sorry.

  • I took over the course.

  • Two.

  • So now we've created another relation but this time between the courses

  • table and the instructors table.

  • But it's kind of a leading question.

  • I've kind of messed something up here.

  • Like, who actually teaches GD?

  • Well who technically teaches GD50?

  • COLTON OGDEN: Technically, both of us do.

  • You're the leading instructor.

  • I'm the secondary instructor.

  • So there should be two instructors, in that case.

  • DAVID MALAN: Yeah.

  • And whoever is who, it feels like we need two instructors.

  • So let's assume that the columns need to be uniquely named.

  • So maybe this is, like, instructor 1 ID, and this is instructor 2 ID.

  • And that's OK because then I could come in as a co-instructor.

  • And maybe no one teaches CS50 with me, officially.

  • So that can just be null, and that's OK.

  • But suppose that we bring on a third colleague.

  • You know, him or her would need to go in--

  • well, OK, instructor three ID.

  • And if, God forbid, we have a fourth person, we'd need another column.

  • And so this tends to be a bit of a slippery slope.

  • If you know only that there's going to be one instructor for any course,

  • super clean.

  • If you know there's only going to be two instructors, OK, fine.

  • Just have two columns.

  • But if you have a variable number of instructors,

  • you could just be defensive and be like, all right, well let's just

  • go all the way up to instructor 10 ID.

  • But for most courses, most of those columns are going to be blank.

  • So at some point, it just gets a little stupid and inefficient.

  • You're just wasting space and adding messiness to the table.

  • So you would actually then use a join table instead.

  • COLTON OGDEN: OK.

  • DAVID MALAN: So what I'm actually going to do is get rid of all of this.

  • Let's not actually put the instructor in the table because of this variability.

  • Let's give ourselves a little more flexibility

  • and create another table here that, by convention, is typically

  • called something like course underscore instructors

  • where you have one word from each of the tables with an underscore

  • by convention.

  • But you can call it anything you want.

  • And then, you could call it teaching assignments or whatever.

  • So in here, I'm going to have a couple of columns.

  • One is going to be called, say, course ID.

  • The other can be instructor ID.

  • Let's go ahead and boldface those to make clear that there are columns.

  • And now the courses have IDs 1 and 2 for CS50 and GD50.

  • So let's just put that in here.

  • Course 1.

  • Course 2.

  • The instructors are, again, David is 1.

  • Colton is 2.

  • So we can 1 and 2.

  • But if I'm also going to be a co-instructor, technically, of GD50,

  • that's another row for course 2 with another instructor

  • ID, which happens to be mine.

  • And now we have unlimited number of potential colleagues.

  • COLTON OGDEN: So we don't put the burden of actually storing

  • those relations in the data.

  • It's more like we have a separate database that keeps

  • track of all the possible relations.

  • DAVID MALAN: Exactly.

  • And this now allows for a many to many relationship as opposed

  • to a many to one relationship.

  • Like, multiple courses could be taught by the same human.

  • Or one human could teach multiple courses or both.

  • And that's the reality here.

  • Each of us is, potentially, involved in multiple courses.

  • So by having this intermediate join table, so to speak,

  • that literally joins the two tables conceptually

  • can you avoid this problem altogether.

  • And so just to be clear because a lot of CS50 students

  • make this mistake on campus, you should never

  • be in the habit of adding columns to your database dynamically.

  • Once you've decided on your database tables and schema, so to speak,

  • that's it.

  • You should only be changing them when you want to add features.

  • You should not be changing your schema when you want to add data.

  • COLTON OGDEN: Sure.

  • Makes sense.

  • DAVID MALAN: So we've had a few comments here.

  • So yes, this stream will be available later today on YouTube.

  • COLTON OGDEN: Yep.

  • DAVID MALAN: Take a look at.

  • COLTON OGDEN: [? One Bad Panda ?] is hosting us

  • on his channel, which means that people looking at his channel

  • are seeing our video.

  • DAVID MALAN:

  • COLTON OGDEN: So thanks, [? One ?] [? Bad ?] [? Panda, ?] very much

  • for hosting us on your channel.

  • Very much appreciate it.

  • DAVID MALAN: Not a sentence most people have probably said before.

  • COLTON OGDEN: Yeah, that's true.

  • Let's see.

  • DAVID MALAN: [INAUDIBLE] said, hey David,

  • I've sent you an invitation on Facebook.

  • Except it.

  • I will do my best.

  • I'm afraid we get lots of kind invitations and notes.

  • And we try our best to keep up.

  • So I'll try to catch up soon.

  • COLTON OGDEN: You're the friend cap, aren't you?

  • You have 5,000 friends?

  • DAVID MALAN: That's also the thing too.

  • Yeah, you're limited in the number of requests you can actually get.

  • So I have to wait till a few of my friends

  • unfriend me sometimes before I can say hi to new people.

  • COLTON OGDEN: Cool.

  • DAVID MALAN: All right.

  • So none of this thus far has actually been about databases.

  • But what we have done is create relations among data.

  • The problem is with something like Google Spreadsheets,

  • or Excel, or [? Numbers, ?] it's kind of hard now to answer simple questions,

  • like, Colton, what courses do you teach?

  • Or what courses are taught by David and Colton?

  • Or what courses don't have an instructor?

  • Like, for that, you really want a query language.

  • Like, some way of typing a command in the form of a question

  • and getting back an answer.

  • And yes, you could write macros in these spreadsheets.

  • You could do Find, and Search, and of Copy Paste to find the data.

  • But at some point, it just becomes the wrong tool for the job.

  • And thus, were born actual databases.

  • COLTON OGDEN: And you can't integrate Excel necessarily easily

  • with a web app.

  • DAVID MALAN: No, exactly, especially when

  • you're writing code in some actual programming language.

  • Python, Java, PHP, [? Roby, ?] whatever.

  • It is indeed hard to then interface it with a spreadsheet,

  • unless you just dump the data as a CSV, which is totally fine.

  • But then too, you lack some of the dynamism,

  • and it devolves back into linear search and linear time operations.

  • COLTON OGDEN: [? One ?] [? Bad ?] [? Panda. ?] You guys helped me write

  • a game mod that helped my stream.

  • I appreciate you guys.

  • That's awesome.

  • Out of curiosity, which mod?

  • I'd like to know.

  • DAVID MALAN: Yeah, and I'm sure all thanks go to Colton on the gaming front

  • there.

  • All right.

  • So let's actually transition the conversation to an actual database.

  • So what do you need to run a database?

  • Well you either need money to pay for something like Oracle,

  • or SQL Server, or Microsoft Access.

  • Or there's so many open source and free options these days.

  • My SQL.

  • I DB.

  • Postgres and also SQLite.

  • And SQLite is, perhaps, the easiest, lightest weight way to get

  • started because it's light.

  • It's nice and simple.

  • Has fewer features, but it's just really easy to get started.

  • And it also happens to be what iOS and Android are using on phones.

  • So it's also wonderfully applicable.

  • It's not even just a little toy environment.

  • COLTON OGDEN: Cool.

  • DAVID MALAN: So how to do this?

  • So typically, you can install a command on your own Mac, or PC,

  • or Linux box called SQLite or SQLite 3, which is the latest version.

  • And if I go ahead and do that, I can specify the name of a database

  • that I want to create.

  • So this one might be called, say, Harvard dot DB

  • if we're making a database for Harvard courses,

  • and instructors, and all of that.

  • So now you just got a simple prompt to like

  • this where you have SQL Light as your command

  • prompt where you can actually start typing commands.

  • Now if we wanted to actually create a table,

  • we're going to have to create a command with which to create that table.

  • And I'm just bringing up my little notes because, frankly, I never remember

  • the precise syntax for creating tables.

  • After that, it gets a little easier.

  • But I do recall that it's create-- whoops.

  • Strike one already.

  • Create table, literally.

  • I can type in uppercase or lowercase.

  • But I would say convention tends to be to use uppercase for the SQL commands.

  • And then, lowercase for your own choice of words.

  • So if I want to create a table that has, for instance, an ID column, and a name

  • column and, say, an email column--

  • and let's keep it simple with just those initially--

  • called instructors, I can actually do exactly that.

  • So let's create a table called--

  • what did we call it before?

  • Instructors that has an ID, name, and email.

  • But with SQL and SQLite in particular, you have to be a little more specific.

  • You can't just specify the column names.

  • You have to specify the types.

  • So turns out SQLite has a few well-defined types.

  • One of which is integer, which I'm going to write as here.

  • Another of which is--

  • actually, let's do it in all caps for a moment-- integer.

  • Another of which is text.

  • And then, we'll use text again for these, as well.

  • COLTON OGDEN: To clarify, is everything being uppercase or lowercase optional?

  • Is it case insensitive?

  • DAVID MALAN: It is optional for the SQL keywords.

  • What you type inside single quotes, or double quotes, or even back ticks

  • is going to be case sensitive.

  • COLTON OGDEN: OK.

  • DAVID MALAN: Good question.

  • So it turns out this is where SQLite does

  • differ from fancier databases like MySQL on Oracle in that SQLite only

  • has a few data types.

  • It only has integers, it has reals, which are like floating point values,

  • text which are, of course, strings.

  • It has numeric, which is a bit of a catch all for other data types,

  • like dates or times.

  • And then, it has blob for binary large objects,

  • which is when you want to store just raw zeros and ones in the database.

  • But I think for the sake of discussion, let's actually

  • use some more conventional data types that are more useful in the real world.

  • One of which is integer, say in Postgres.

  • By convention, its lower case.

  • But it does not matter in this case.

  • Text is a little ill-defined.

  • So I'm actually going to say something called varchar.

  • A variable number of chars.

  • And this is where you need to make your first database decision,

  • like how many characters maximally might the person's name be.

  • Like, how long.

  • C-O-L-T-O-N. So we could go with six, maybe.

  • COLTON OGDEN: Right.

  • For our first name.

  • DAVID MALAN: First name.

  • So last name, Ogden.

  • You know, at some point, you have to decide.

  • And we could argue all day long what the possible longest name of a human

  • is going to be.

  • But I Googled in advance.

  • COLTON OGDEN: You know the actual [INAUDIBLE] in lecture, didn't you?

  • DAVID MALAN: Yeah.

  • According to Google, it's 225 for a crazy, long name

  • that this guy named Nick had.

  • But let's just round up to 255, which was just a historical convention.

  • Eight byte value starting zero indexed.

  • COLTON OGDEN: One Bad Panda says, Terraria connected my StreamElements

  • points system to reward in-game items.

  • That's really cool.

  • It's C#, right, if I'm not mistaken?

  • We'll be covering C# on Friday, actually, this week with Unity,

  • so if you're interested in that, and if it is indeed C#,

  • definitely come check that out.

  • DAVID MALAN: Nice.

  • An awesome panda.

  • I think that is an awesome username too.

  • Someone should scoop that one up too--

  • One Bad Panda.

  • How can we do varchar if we use phpLiteAdmin,

  • as in mashup pset requirements?

  • So Bob, it's funny you ask.

  • So we are actually in the process now of transitioning to these newer data

  • types, so students who are working on last year's CS50 materials

  • will now see in phpLiteAdmin these new data types.

  • Not just text, you will now see varchar as of today.

  • You might need to do an update 50, which is

  • the command in CS50's IDE for updating itself,

  • but you should now see these more precise types.

  • Yeah, really nicely done on the C# side.

  • That's a pretty cool combination there.

  • So for email too, I don't really know what the longest

  • email might be in the world.

  • Something tells me that no one really has email addresses longer than 255.

  • COLTON OGDEN: Except Nick.

  • DAVID MALAN: Because no one's going to email you.

  • So let's just assume 255, but this is a real decision you have to make.

  • And if you've ever been to a website where

  • you try typing in your address, your name, or whatever,

  • and you suddenly can't type anymore, that's

  • quite likely because the database designer has decided

  • that's enough characters for you.

  • You often see this on customer support forums.

  • If a company wants to be able to have customer service,

  • but they don't want you to be too wordy in your complaints or whatnot,

  • you can just tap the number of chars altogether.

  • COLTON OGDEN: Sounds like you might have some personal experience with this.

  • DAVID MALAN: Yeah, I'm a little annoyed because I've been trying

  • to do this recently, and someone--

  • I'd like to provide a lot of detail, but they didn't want to hear it.

  • Why does the SQL implementation differ between platforms and servers?

  • I think this kind of boils down to reasonable people disagree.

  • There have been different computer scientists, different developers,

  • different companies who've decided we can do that better,

  • or we would like to do that differently, or we have different problems

  • that we want to solve.

  • And while there is this common subset of SQL

  • that tends to persist across all of these various dialects,

  • as they're called, different vendors and different humans

  • have just different implementations.

  • COLTON OGDEN: Kind of why Ruby and Python exist.

  • And why C# and Java exist.

  • DAVID MALAN: Yeah, exactly.

  • Why are there so many languages?

  • Not everyone agrees on what a language should do and how it should do it.

  • So same thing here.

  • It is a little annoying with SQL, though, in that it's all called SQL,

  • but it is platform specific.

  • And this can create problems if you actually

  • want to transition from one type of database to another.

  • Now, frankly, that's probably not that commonly done,

  • but it's certainly one implication of that reality.

  • So let me cross my fingers, and hit Enter, and see

  • if we have actually created a table.

  • Here we go.

  • Nothing seems to have happened, which, generally,

  • in a Linux environment or Mac environment is a good thing.

  • Let me go ahead now and type schema with a dot in front of it, which means this

  • is a SQL-like command and not a SQL command,

  • and you'll see just a regurgitation of the command

  • with which to create that table.

  • Now, how do I go about adding data to the database?

  • Let me go ahead and do that.

  • Insert into instructors the following fields--

  • an ID, a name, and an email, and the values I want to insert will be one,

  • say, David and Malan at Harvard.edu.

  • You'll notice I got a little lazy here, and I did not

  • put quotes around absolutely everything, and sometimes that's OK.

  • If the words you have chosen for your columns or whatever

  • don't coincide with reserved SQL words, you don't need to bother quoting them,

  • so you can save a few keystrokes.

  • Enter-- nothing seems to have happened, but let's do this once more.

  • Insert into instructors-- ID, name, email, values two--

  • Colton and then Cogden@cs50.harvard.edu--

  • semicolon, which is important--

  • Enter.

  • And now-- and someone proposed this year, select from where?

  • Indeed, spoiler, so if we want to actually select

  • the users in the database, select star, which

  • means select everything from instructors should select all of the instructors

  • from this table.

  • And indeed, you see it's kind of like a CSV, Comma Separated Value,

  • but it's using a vertical bar or pipe, and you can kind of sort of see

  • a graphical version of that same table.

  • COLTON OGDEN: OK, so values is like a function that has multiple parameters,

  • and that unpacks those to put them into the table?

  • DAVID MALAN: Yeah, I don't know if it's technically a function,

  • but it's definitely a keyword that needs to be present in order

  • to specify here come the values to distinguish them from the key names

  • there.

  • A bit of chatter here, so let's rewind here.

  • Why is JavaScript still a thing?

  • OK, so a little off topic.

  • That's kind of a can of worms.

  • Certainly useful on the client side, and lots of people-- and increasingly,

  • people are finding it useful on the server side via Node.js.

  • Let's see, what else here.

  • COLTON OGDEN: I think Web Streak and Swarm

  • Logic were making a joke about a SQL injection attack, possibly.

  • DAVID MALAN: Yeah, you could certainly do that.

  • COLTON OGDEN: So you can send a message in Twitch chat.

  • DAVID MALAN: Let's see.

  • You completed mashup, [? Bave, ?] but I had to make tables locally in my PC.

  • Ah, so yes, that was by design. phpLiteAdmin

  • was configured to support only SQLite, and therefore,

  • only its five or so data types.

  • And we have just recently, as of a week ago,

  • enhanced it to actually support Postgres data types as well,

  • but a subset of them only.

  • How can you disable HTML?

  • Now I think you're hopefully trolling us,

  • because that's not really going to happen if you're using the web.

  • What's an integer in the context of SQLite--

  • a 32-bit, or 64-bit, or is it dynamic signed or unsigned?

  • Good question, Andre.

  • I actually don't know offhand.

  • I know that in Postgres, this is well-defined behavior.

  • A small int is two byte, an integer is four bytes,

  • and a big int is eight bytes, but when in doubt, let's

  • consult the documentation.

  • So SQLite, integer-- you'll find that the official documentation

  • is at SQLite.org.

  • Version three is the latest.

  • Let's go ahead in here.

  • Yeah, look at this.

  • So Andre, in answer to your question, SQLite's a little dynamic.

  • It'll use one, two, three, four, six, or eight bytes,

  • depending on how big the number actually is, thereby, dynamically

  • figuring itself out for efficiency.

  • COLTON OGDEN: That's cool.

  • DAVID MALAN: I believe it is only signed by default,

  • but let me search for signed.

  • Signed, signed, oops-- let's go back up.

  • I saw one hit there.

  • Yeah, it looks like unsigned big int is mapped to integer two,

  • so something tells me that it's only signed integers from negative something

  • to positive something if it's using a byte value, which

  • I don't know how to pronounce offhand.

  • What else do we have here?

  • Can we do a table name like insert into table name?

  • Table name-- so, [? Bavik, ?] do you mean can you name your table,

  • quote, unquote, insert into table name?

  • In brackets-- oh, I see what you mean.

  • Yes, SQLite does support bracket notation

  • where you can put the table names in brackets instead of using quotes.

  • I think that's what you mean.

  • Yes, that is indeed the case too.

  • Quotes tend to be a little more conventional,

  • but for SQLite, you'll see that as well.

  • All right, so why is this all actually useful?

  • And honestly, this is getting a little tedious.

  • I don't really want to type out all these commands in black and white.

  • So [? Bavik ?] mentions phpLiteAdmin, which is this free web-based tool.

  • It's written in PHP, but that's an implementation detail

  • that just makes it more pleasant to interact

  • with the database on Macs and PCs.

  • Let me show everyone a really helpful free tool.

  • If you search for DB Browser for SQLite, the first hit

  • is hopefully SQLitebrowser.org.

  • If you go and visit that, you'll see a website that looks like this,

  • and you will see a Windows version, a Mac version, and even the tar ball

  • if you want to install it on Linux for yourself.

  • I've already done this in advance and installed it on my Mac,

  • and once I've done that, you'll see that I have this program--

  • DB Browser for SQLite.

  • And it's actually pretty nice, pretty simple GUI, Graphical User Interface,

  • for manipulating databases.

  • So let me do this.

  • Let me exit out of SQLite 3, which is the black and white command line

  • version.

  • And let's go ahead and open that same database, which we called Harvard.db.

  • Enter-- and in here, now you'll see exactly the same information,

  • but formatted into GUI, and so notice, if we actually

  • look at the instructors table and browse the data, we'll see a couple of columns

  • here.

  • I can zoom in here.

  • COLTON OGDEN: Looks awfully familiar.

  • DAVID MALAN: Yeah, we've used it-- what's familiar about it?

  • COLTON OGDEN: It looks like Excel all over again.

  • DAVID MALAN: It is.

  • So it's a GUI that actually looks like Excel, but we're going to have,

  • in just a little bit with Python, the ability programmatically

  • to talk to this data.

  • So let's go ahead and actually start over with this tool,

  • so folks have seen how to do it here.

  • So let's see.

  • COLTON OGDEN: It changed the colors of the windows, says One Bad Panda.

  • Is that a reference to-- oh, that's--

  • DAVID MALAN: Yes, I know it doesn't have to be black and white.

  • It's just meant to convey the simplicity of the environment.

  • Green and black is very popular as well.

  • So let me go ahead and just blow this away and delete this table as unneeded.

  • And let's start over, so we can actually see how you can create the same thing.

  • And notice-- zero, zero, zero, zero-- there's nothing in this database now.

  • So let's create a table.

  • Let's call it instructors, and what's really cool about DB Browser is that

  • it's sort of a teaching tool. %%%

  • It will show you what code you could type out manually

  • to execute the same functionality.

  • COLTON OGDEN: Does phpLiteAdmin also do that?

  • DAVID MALAN: It does after the fact.

  • It doesn't do it in real time.

  • When you click Submit.

  • So let's go ahead and click Add Field, and let's go ahead and call this ID.

  • We're going to keep it an integer, and you'll

  • see that these are the finite number of types we talked about before,

  • but you can override it.

  • If you want to proactively prepare to migrate to Postgres,

  • you could type in something like big int here,

  • and it would accept it, but let's go ahead and go with the defaults.

  • Notice that there's these columns here--

  • PK for Primary Key, AI for Auto Increment,

  • U for Unique, and Not for not null.

  • So primary key is, by definition, the value

  • that uniquely identifies each row, which hopefully is indeed

  • these unique numbers.

  • Auto increment is a fancy feature, because now I

  • don't have to think about whether you're one and I'm two or you're two

  • and I'm one.

  • We just let the database give us IDs.

  • COLTON OGDEN: If you don't do auto increment,

  • will it just or store whatever data you give it?

  • It won't perform any automatic--

  • DAVID MALAN: Correct, and you can still manually provide a value.

  • And what SQLite will do is take the next highest value the next time

  • you do an insert without specifying.

  • COLTON OGDEN: So if there's two people and we give them the ID one,

  • and if we try to grab those, it'll just give us both those rows in that case?

  • DAVID MALAN: Exactly, yeah.

  • That's what we want to avoid.

  • So we see a lot of sarcasm going on here in the chat window.

  • Thank you so much.

  • Hello, Sergio from Spain.

  • What code editor?

  • I tend to use Atom these days.

  • Colton uses--

  • COLTON OGDEN: VS Code, typically.

  • DAVID MALAN: Those are two of the more popular ones these days.

  • COLTON OGDEN: They're pretty much identical.

  • DAVID MALAN: Every phrase has a meaning--

  • OK, so there's a whole conversation going on.

  • We're going to talk about SQL here on our end.

  • So we've got ID.

  • Let's go ahead and create an instructor's name now.

  • COLTON OGDEN: [INAUDIBLE] did ask one more question at the very bottom.

  • DAVID MALAN: Let's see, what have we got here.

  • What's the difference between unique and primary key?

  • Good question.

  • Primary key is a superset of unique.

  • And by that, I mean primary key means the value has to be unique,

  • but it's also a commitment by you to use that field as the unique identifier

  • for that specific table, and it is a value that can therefore

  • appear in other tables as a foreign key, which is just what we were doing

  • earlier with our table that we called--

  • let's go over to that one--

  • course instructors-- where a number from another table

  • was appearing in that table.

  • Good question.

  • Keep them coming.

  • So let's just bang out the rest of this table.

  • Name is going to be text, although we could type in var

  • chart to be more precise with a number, like 255,

  • but we'll go with SQLite's types now.

  • Do I want to make this a primary key?

  • No, because we already have one in our number.

  • COLTON OGDEN: You should only usually have one primary key

  • throughout your entire database, right?

  • DAVID MALAN: Yeah, and it can span multiple columns.

  • You can say that together these two columns define my primary key,

  • but you shouldn't have one primary key, another primary key, and another.

  • That violates the definition.

  • What about unique?

  • Should we make name unique in this table?

  • COLTON OGDEN: I don't think so, because like John Smith,

  • for example, very common name.

  • Those might exist, right?

  • DAVID MALAN: Exactly.

  • And god forbid we--

  • we have two Coltons.

  • We should at least allow for it.

  • So name might be in there, and then let's do one more, email.

  • We'll keep it simple as text as well.

  • That could maybe be unique if you assume people can't share email addresses.

  • So sure, we'll make that unique, but not the primary key,

  • but that invites a good question.

  • You could use email as your primary key if it's already unique,

  • but if your email address is like cogden@cs--

  • it's going to be 10 or more characters, whereas an int is

  • going to be two bytes or four bytes, so it's a lot more efficient, typically,

  • to use integers.

  • So that's another reason to make the primary key an integer.

  • So primary key is how we tell SQL what we intend to use the ID for?

  • Yes.

  • That's a good way of thinking about it.

  • Are primary keys auto indexed on SQLite?

  • Yes.

  • Primary keys and unique keys are, by definition,

  • indexed on SQLite, and more generally on SQL databases.

  • What's the difference-- OK, I'm going to pluck those off as well.

  • Keep them coming.

  • Let me go ahead and before I click OK just draw our attention

  • to the SQL that was generated for me dynamically.

  • Notice it's using back ticks, which is technically

  • how you should escape table names and field

  • names to distinguish them from keywords, but sometimes single quotes

  • are fine as well.

  • So a bit more syntax than before.

  • Partly because I don't remember it when I type it out manually,

  • which is why tools like this are nice.

  • So let's go ahead and click OK there.

  • So we could continue this logic and actually

  • create more and more tables here just by walking through those steps.

  • If you want to play around with DB Browser,

  • I would encourage you to do so, but why don't we actually transition now

  • to maybe some actual code.

  • See how you can talk to this?

  • COLTON OGDEN: Lets take a look at it.

  • DAVID MALAN: Hey, love the haircut there.

  • COLTON OGDEN: David says, love your haircut,

  • Col. Thanks, David, appreciate it.

  • DAVID MALAN: Keep the hair compliments coming for Colton.

  • All right, so those of you who are familiar with a bit of Python

  • might know about Flask, which is a popular framework for web programming.

  • It's similar in spirit to Express on [? NOJS, ?]

  • and it's similar to Laravel and PHP.

  • It's similar to Rails for Ruby, although there are certainly

  • differences among those.

  • But we can go about implementing a program in Python,

  • and with it, Flask, that actually uses a database.

  • But let's start by making, not a web app first, but just a little command line

  • application.

  • COLTON OGDEN: Let's do it.

  • DAVID MALAN: All right, so I'm going to go ahead and open up VIM here

  • in my black and white terminal window.

  • COLTON OGDEN: The other editor that you primarily use, right?

  • DAVID MALAN: Ah uh, yes, no indeed.

  • When I'm in a Linux command line environment I just pull up VIM.

  • Let me go ahead and open up harvard.py as a script.

  • And now let's go ahead and write a little script that maybe just iterates

  • over the instructors and print them out on the command line,

  • just because so that we can show how to do it.

  • So we need a way of talking to the SQL database.

  • And there's a few, there's any number of ways to do this.

  • In CS50, the undergraduate class, we actually

  • have our own fancy library that creates a nice little abstraction that

  • does this for us.

  • In the Python world, SQLAlchemy is, frankly, quite popular, perhaps

  • too popular for its own good.

  • I find it to be a pain in the neck to actually use.

  • It tends to provide not just an abstraction layer over SQL, also

  • a whole ORM, or Object Relational Mapper, which

  • is convenient in some cases.

  • But it's kind of a whole technology stack unto itself to learn.

  • So in CS50 we use a simpler API that just has one function, called execute,

  • which I'll use today just so we can keep the focus on SQL and not

  • on a rabbit hole of Python frameworks.

  • COLTON OGDEN: Does the CS50 library wrap SQLAlchemy at all?

  • DAVID MALAN: It does.

  • So we figured out how to use SQLAlchemy.

  • And we just abstract that away for students.

  • But we're not abstracting any of the SQL stuff.

  • This is just a Python thing.

  • So let me go ahead and import the CS50 library as follows.

  • So from CS50, import SQL.

  • And after that, let me go ahead and connect to the database.

  • So I'm going to declare a variable called db.

  • But I could call it anything I want.

  • I'm going to use this SQL class that I've just

  • imported from the CS50 library.

  • And I'm going to go ahead and import the SQL file that we called Harvard.db.

  • Not a typo, I'm indeed using three slashes there

  • specifying a protocol of SQL [? flights. ?]

  • Now let's go ahead and execute a query.

  • To execute a query we can literally type db.execute.

  • And then let me go ahead and select all of the instructors.

  • So SELECT star FROM instructors, and let's just leave it

  • at that, so just the instructors.

  • And actually I don't really care about all of our information.

  • Let's go ahead and select the name of the instructors from instructors.

  • So again I'm capitalizing SQL keywords and lower-casing field names

  • that we defined ourself.

  • COLTON OGDEN: So if you select a keyword, or I guess a column,

  • it will default to all, the basically the same thing as star?

  • DAVID MALAN: Say that again?

  • COLTON OGDEN: So this select name from instructors

  • will give you all of the rows and all of their names, just like a star,

  • basically but more specific?

  • DAVID MALAN: Star selects all of the columns.

  • Name selects one of the columns.

  • But you will always get back all of the matching rows.

  • COLTON OGDEN: OK.

  • DAVID MALAN: This function will always return a Python list,

  • even if it's empty.

  • You will get back zero or more rows by definition.

  • COLTON OGDEN: OK.

  • DAVID MALAN: I'd really like to see some good Vim RC file and plugins for VIM

  • someday.

  • That is a good, actually, topic for Jordan [? Hiyoshi, ?]

  • who teaches our mobile app development class using React native.

  • If you look at that course online you'll actually

  • find that he uses VIM quite a bit and has his own plugins.

  • If you send me or Colton an email we can point you at that after too.

  • We just have to dig up his URL.

  • Is it better to start with Flask or Django, regarding the learning curve?

  • So for my part, Flask, hands down.

  • Django is a much bigger beast than Flask.

  • Flask solves a few problems very well.

  • Django solves a lot of problems all at once.

  • And so frankly I would hands down start with Flask

  • and then work your way up to Django.

  • COLTON OGDEN: And then Brian's course on CS video [INAUDIBLE] [? .org/web ?]

  • goes over both, right?

  • DAVID MALAN: Indeed, yeah, Brian's class,

  • we start with Flask and then transition to Django.

  • So you can go to that URL that Colton just rattled off

  • if you'd like to see that as well.

  • Semicolon missing, this is Python.

  • So we-- oh, good question.

  • Within the SQL commands, the way the execute function

  • works, and this tends to be true of a lot of SQL APIs in Python

  • and any language-- you don't need the semicolon there.

  • The library will take care of that for us.

  • What do you think about Postgres SQL versus regular SQL MySQL?

  • So there isn't really--

  • you can't really compare Postgres against regular SQL

  • because SQL is the language, whereas Postgres and MySQL are

  • implementations of the language, or specifically of a server.

  • Frankly, I find MySQL a little easier to use.

  • It's a little simpler in parts.

  • But frankly Postgres has been gaining steam, I would say.

  • MySQL has probably been losing some steam in recent years.

  • We tend to encourage our own students to use [? Heroku ?] just

  • because it's so simple.

  • They've got a free tier of service.

  • They've got great documentation.

  • And so on those bases alone, the fact that they support Postgres

  • is why we encourage students to pick up a bit of Postgres.

  • But if they had used something else, we would probably

  • encourage something else.

  • Oh sure, we can repeat the URL.

  • You're about to type in the chat room, perhaps?

  • And we'll type in the URL of Brian's web class here.

  • All right so if we turn our attention back to the code here,

  • this line of SQL, SELECT name from instructor

  • should give us back a list of all of the instructors.

  • So let's actually store that list in a variable that I'll call rows.

  • And then the purpose of this program is just to print out these names.

  • So let's go ahead and do this.

  • So for row in rows, let me just go ahead and print out row itself.

  • And that's it.

  • And, whoops, no semicolon, wrong language.

  • But what do I want to print about the row?

  • Well it turns out that each of these rows in CS50's library,

  • and commonly in these SQL libraries is to return a dictionary, or Python dict,

  • as each of the rows.

  • So I'm specifically going to print out someone's name in parentheses there.

  • All right, so I think I've made no mistakes here.

  • Let me go ahead and save this here.

  • And now let me do a few things.

  • This is using, of course, the CS50 library.

  • If you've never done this before on your Mac or PC, your Linux box,

  • you're going to want to do PIP 3 install CS50, which will install CS50's

  • library, if you have PIP installed.

  • If not, Google how to install PIP.

  • It varies based on platform.

  • PIP is just a package manager that will install that on your machine.

  • I happened to install it already so it's already there.

  • So I'm just going to wave my hands at the text there.

  • And now I'm going to go ahead and run Python 3

  • to make clear we're using the latest version of Python of harvard.py, Enter.

  • And voila, you see on that put all of our names.

  • Now you also see a debug line that's built

  • into the library for pedagogical purposes,

  • just to show you what command in green was sent to the database.

  • And all indeed seems well.

  • COLTON OGDEN: That's handy.

  • I like that.

  • DAVID MALAN: So what we can do now is make this a little more programmatic.

  • If I also, for instance, import the sys library, thereby

  • giving myself access to like [INAUDIBLE] [? arg ?] v,

  • I could even do something like this.

  • I could add a WHERE clause, which someone alluded to earlier,

  • WHERE name equals, and then I can put a placeholder.

  • Now normally in Python you'd put a placeholder

  • with like curly braces or whatnot.

  • But in SQL a convention tends to be to use named place holders like this.

  • And then the library is going to have me do this, pass

  • in a value for that named place holder.

  • You don't repeat the colon here.

  • And let me go ahead and say sys.rgv 1 where

  • I'm going to presume that the user has typed in a word.

  • And then hopefully that's going to filter down

  • the number of results I get.

  • So if I save that and do Python 3 of harvard.py.

  • And now let's go ahead and search for just David instructors.

  • Hopefully I'll get back just David.

  • And if I do this again I can just get back Colton.

  • But of course I don't support prefix matching,

  • like Dav, I'm going to get no one because there's no Dav in there.

  • So we can actually show another command.

  • Instead of using equal sign, we can actually say, like, and then go ahead

  • and plug-in a value here.

  • So instead of just doing this here we can go ahead

  • and concatenate onto this, for instance, using a plus, a percent sign, thereby

  • saying the name should start with [? whatever ?] the human typed in,

  • but it can end with any number of characters as well.

  • COLTON OGDEN: I think somebody even mentioned that in the chat.

  • DAVID MALAN: Yeah, I think we saw a percent sign a moment ago.

  • COLTON OGDEN: JP guy, fourth one down, third one down.

  • DAVID MALAN: Indeed, yeah, no, very good addition there.

  • So let's try this.

  • So now let me run, let me go ahead and run this again, searching for David.

  • OK, that's still works.

  • Searching for Dav, and voila.

  • That now works as well.

  • So we the ability to search on a field too.

  • COLTON OGDEN: It's kind of like the primitive version of the Google,

  • what's it called?

  • What's their technology called where it knows what you're typing?

  • DAVID MALAN: Autocomplete.

  • COLTON OGDEN: Autocomplete.

  • Didn't it have a different name to it, I thought?

  • DAVID MALAN: Autocomplete, I don't know.

  • COLTON OGDEN: I thought Google had like a specific--

  • DAVID MALAN: Oh, like intelli-sense or something?

  • COLTON OGDEN: Something like that.

  • DAVID MALAN: I don't know.

  • All right, let's catch up here.

  • So SQL [INAUDIBLE],, you can use the percent sign.

  • Yep, OK thank you JP guy.

  • Semicolon, yep, not necessary.

  • OK, can I have a job in Silicon Valley although I'm in Tunisia?

  • I think by definition no.

  • You would need to be in Silicon Valley.

  • But if you mean can you apply, absolutely.

  • I certainly think you should be able to find

  • contact information on any number of tech companies websites in California.

  • See if there's a link to internships or jobs, and by all means,

  • reach out to it with an email to someone you see online.

  • Or if you have access to LinkedIn.com and have a profile,

  • look for, search for Facebook recruiter, or other such search phrases

  • and see if you can connect with someone, by all means.

  • The world's a increasingly small place.

  • And talent is everywhere.

  • So I wouldn't hesitate.

  • What is SQL best used for?

  • So it's a good question.

  • I think it tends to be best used when you have lots of data to store,

  • or data that you want to be able to search conveniently or efficiently.

  • And that, [? may be ?] a relatively small data set.

  • Frankly, even we, for purposes on campus,

  • find it much more useful to be able to type

  • SQL queries to answer questions for ourselves

  • about the students, about grades, and any number of administrative things,

  • than it is to write a program and a script to analyze data,

  • or to poke around Excel, or the like.

  • So I think it really depends.

  • But the fact that you get the ability to create data sets, select from them,

  • delete from them, update, and delete, is a pretty powerful tool kit.

  • Can you use SQL with other languages?

  • Absolutely, pretty much every language comes with the--

  • certainly any popular language these days comes with support for SQL

  • in the form of a library, including C. So you can use it from old languages

  • to new.

  • COLTON OGDEN: CS50 [INAUDIBLE] the docs at iO, got a plug there.

  • DAVID MALAN: Well, thank you.

  • Thank you.

  • So what happens behind the scenes when you search--

  • so is this what happens behind the scenes when you search a website?

  • Possibly, yes, if that website is using SQL--

  • which is very possible because it's very popular technique these days--

  • then yes, there is one or more queries going on probably using the verb SELECT

  • to find that data.

  • You could, as someone noted earlier, by using a NoSQL database,

  • but the spirit is the same.

  • The data is stored differently.

  • But you can actually search it in the same way.

  • So both of these paradigms are actually quite popular.

  • COLTON OGDEN: We'll be covering actually a little bit of that, aren't we?

  • You said possibly a mini example, like a [INAUDIBLE] example?

  • DAVID MALAN: Well possibly.

  • We actually ended up talking about names and addresses, which is fine too.

  • We started the conversation there.

  • But I don't think we need to import it just to [? sports ?] next.

  • COLTON OGDEN: [INAUDIBLE] oh, co-completion, right.

  • That's right, yeah.

  • DAVID MALAN: Is that what you were thinking about?

  • COLTON OGDEN: No, it's got a different word.

  • I forget.

  • There was a specific phrase I thought Google

  • used for their autocomplete feature.

  • Autocomplete would be like the general term for it.

  • But Google had a specific--

  • I'm gonna look it up.

  • We'll look it up.

  • DAVID MALAN: In the meantime, does regular expressions in SQL

  • helped me to find data?

  • Yes, but they're not exactly regular expressions.

  • The expressiveness of SQL tends to be a little less.

  • You can use a percent sign, as I think the JP guy mentioned earlier,

  • which represents 0 or more characters.

  • You can use a question mark, which is 1, or 1 or 0 characters, I believe,

  • or possibly just 1.

  • I forget offhand.

  • And that's mostly it.

  • You can do other little trickery but it's not

  • nearly as expressive as using something like Python or JavaScript

  • or Ruby to actually use regular expressions on the language.

  • Good question.

  • Keep them coming.

  • COLTON OGDEN: It might be Autocomplete.

  • But I swore that it had a different name to it.

  • DAVID MALAN: OK, I believe you.

  • So in any case, where does that leave us?

  • So we've only just scratched the surface of this.

  • Let's put together a simple web application just

  • to tie it together to some generation of html.

  • But it's really not a hard leap.

  • Because notice that after all, what we've just done with harvard.py

  • is generate text dynamically by using a for loop in print.

  • But you could certainly imagine using the same kind of code

  • to generate like an unordered list, or an ordered list, or a table tag, again

  • and again and again.

  • So let's actually do that with a bit of Flask

  • code, which is Python code that just happens to use a micro framework, so

  • to speak, called Flask.

  • And what have we got here?

  • Aren't regular expressions mostly--

  • I'm not sure I'd say that.

  • They're used in bunches of languages.

  • I learned them back in the day in Perl, one of the earliest incarnations.

  • They're in PHP.

  • They're in Python.

  • They're in Ruby.

  • They're in, did I say Python?

  • They're in all modern languages nowadays, certainly

  • higher level languages as well.

  • 0 or 1, thank you, [? Bavek, ?] for looking that up as well.

  • Yeah, regular expressions are super popular and definitely something

  • worth picking up.

  • I can say it's really easy at first to like not quite

  • understand how to use them.

  • The syntax can get a little scary.

  • And honestly just as we preach in CS50, the best way

  • to start building up regular expressions is baby steps,

  • like just write the simplest one you can, then enhance it,

  • enhance it, enhance it until you have something crazy complicated

  • that you might not even recognize anymore.

  • But if you document it and you remember those steps,

  • odds are it'll solve your problem.

  • Google suggest?

  • Is that super effects?

  • COLTON OGDEN: Yeah, maybe.

  • I honestly don't remember.

  • I might be trippin.

  • But I'm pretty sure that it was--

  • it had a specific name.

  • DAVID MALAN: All right, let's keep belaboring that point.

  • OK so let's go ahead and make a relatively simple web app just

  • to demonstrate how all this works.

  • I'm going to go ahead now and do this, PIP 3 install Flask, which will install

  • this framework on your own Mac or PC.

  • Nothing really happens because I did this earlier as well.

  • Then let me go ahead and open up a file called application.py,

  • which initially is blank.

  • And I'm going to go ahead and do a few imports.

  • So I'm going to import from the Flask module,

  • something called FLASK in all uppercase, a function called render template,

  • in lowercase, and request variable as well.

  • And from the CS50 library, I'm going to import our SQL library again.

  • But you can use SQLAlchemy or the like.

  • We just want to use SQL today.

  • We don't really care what the library is.

  • And then we go ahead and do the following.

  • Let me create a web app using Flask by saying,

  • OK, Python, this file with this name should

  • be initialized with the Flask app, the Flask micro framework, capital F.

  • And I'm going to store that in a variable called app.

  • And let's do the same thing as before with our database.

  • Let's go ahead and use the SQL constructor to say go ahead and connect

  • to [? sqlite///harvard.db. ?]

  • All right so we got two global variables.

  • Now in Flask, let's do the simplest thing possible, define a route.

  • So for those unfamiliar, a web-based framework typically models incoming

  • requests by way of routes, like slash, or /login, or /register,

  • some path with slashes in it that represents what URL the user might

  • visit.

  • And the way to express this in flask is to say, hey application, go ahead

  • and define a route like slash, which is typically

  • the default. And then associate a function with any name--

  • I'll choose index because that tends to mean the index of a site below it.

  • And that function now should do something.

  • I'm going to do something super simple.

  • I'm just going to return hello world.

  • So this is not html yet.

  • It's literally just a string.

  • But I think it's enough to get the job done.

  • Because what I'm going to do next is this.

  • I'm going to go ahead and type python3-m and run the Flask module--

  • the software for which I installed a moment ago.

  • And I'm going to specify a global environment variable called FLASK_APP,

  • that the name of the file I want to use for my Flask application

  • is called application.py.

  • I'm going to go ahead and hit Enter.

  • And you'll see that I screwed up because I forgot the key word run.

  • So I need to actually type in Flask run.

  • So let me bring this up to a line of its own and go ahead and hit Enter.

  • So you'll see a bunch of diagnostic output, the most useful of which

  • is this URL at the bottom.

  • I'm going to highlight and copy that, open up a new tab, paste it, and voila,

  • there's hello world.

  • COLTON OGDEN: Beautiful.

  • DAVID MALAN: If I view the source of this it's not technically a web page.

  • It's just a string.

  • But we're one step closer.

  • COLTON OGDEN: Cool.

  • DAVID MALAN: Now for those a little less familiar with command lines,

  • this is a little tedious, honestly.

  • To run your web server it's a little annoying

  • to have to type this whole thing again and again.

  • So there's a few changes we can make here.

  • One, you can export an environment variable semi-permanently.

  • And I can say go ahead and export a variable called Flask app,

  • setting it equal to application.py, Enter.

  • And now until I close this window I never need to type that again.

  • And I'm also going to set an alias in Mac OS or Linux

  • called Flask, and set that equal to python 3-m flask,

  • so that I never again need to remember that crazy incantation.

  • So now if I want to do the exact same thing as before,

  • I can just very simply say Flask run and voila, I'm good and going.

  • COLTON OGDEN: CLI magic.

  • DAVID MALAN: Indeed, now depending on the platform,

  • like Linux sets all this up for you.

  • Mac OS, I think have to do a little bit more manually.

  • With Windows, open through sub [? shell, ?] you might--

  • subsystem.

  • You might need to do it manually too.

  • I'm not sure.

  • But these are just little command line tricks.

  • COLTON OGDEN: XKCD 208 for on point [? rejects ?]

  • truth, are you gonna pull that up for [INAUDIBLE]??

  • DAVID MALAN: Oh yeah, sure, let's see.

  • COLTON OGDEN: XKCD 208

  • DAVID MALAN: XKCD 208, here we go.

  • COLTON OGDEN: Regular expressions, let's see.

  • Whenever I learn a new skill, I concoct elaborate fantasy scenarios

  • where it lets me save the day.

  • Oh no, the killer must've followed her on vacation.

  • But to find them, we'd have to search through 200 megabytes of emails

  • looking for something formatted like an address.

  • It's hopeless.

  • Everybody stand back, I know regular expressions.

  • Perl, boom boom, and he's done.

  • Nice.

  • DAVID MALAN: Thank you for that.

  • There's an XKCD for almost anything in CS these days.

  • COLTON OGDEN: There is some truth there, though.

  • DAVID MALAN: Another question, what is the -m for?

  • So -m means run a module in Python speak.

  • So somewhere in the file system now is installed a module called Python.

  • And that got installed when I ran PIP 3, the package manager.

  • So Python 3-m Flask means run the module called Flask.

  • And when I then created an alias, I was able to just remove that prefix

  • because an alias sort of secretly types the same command for you

  • magically behind the scenes.

  • Which language, asks [? amano, ?] would you

  • recommend for getting data out of machines, mostly PLCs and REST,

  • in addition to SQL?

  • I started to pick up Python, want to get the data into a database

  • and work with them in a statistical way.

  • So I'm a little less familiar with that particular use case.

  • But to be honest, if you've started to pick up Python, that is certainly fine.

  • You can write a command line script that does kind of the opposite of what

  • we're been doing.

  • Instead of writing SELECT statements, you

  • can instead run INSERT statements like I did earlier for Colton's and my data,

  • and do that script once, import all your data into a database,

  • whether it's SQLite, or Postgres, or MySQL and be on your way.

  • And then you can use R, or any number of other environments

  • to actually analyze that data the way you'd like.

  • Is there a visualizer to cut and paste, like Excel, data around in SQL?

  • Kind of sort of, db browser light might let you copy and paste some data.

  • Let's see, if I go ahead and let's go into browse data.

  • Let's go ahead and create a new record.

  • So David, and then here [? Malan@harvard.edu. ?] Whoops,

  • I didn't apply it before, one sec.

  • David, apply, let's see, can I copy this?

  • Copy-- so short answer, no.

  • You can't really copy paste easily it seems in this GUI tool.

  • I'm sure there is.

  • And frankly the only reason-- well, the primary reason

  • we use DB browser in the courses we teach here is one, it's free.

  • Two, it's very easy to use.

  • Three, it exists for Macs and PCs and Linux machines as well.

  • There's also far fancier versions, like SQLpro, SQL [? lies, ?]

  • I think, or something like that for Mac OS.

  • And I'm sure there are others for PCs.

  • So I'm sure there are.

  • I just don't know any, honestly.

  • Visualizer will let you run SQL.

  • So you can get all this by downloading Docker.

  • OK, so true, you can get all this by downloading Docker.

  • We're not using Docker here.

  • I'm just using a little old Mac OS.

  • And you can use the Ubuntu system on Windows these days, or Windows proper.

  • You can certainly use Linux or Unix.

  • But Docker, yes, would allow you to do this as well.

  • But there's no need, strictly speaking.

  • All right, so let's pick up where we left off.

  • I've got a server running.

  • It doesn't actually render any html.

  • But for that we need one other feature Flask.

  • So Flask supports templating.

  • It uses a language called Jinja, J-I-N-J-A,

  • which can be used not just with Flask but with other frameworks.

  • And it just assumes that you have a templates directory.

  • And in that directory it assumes you have one or more files.

  • Let's go ahead and create an index.html file that's just

  • a super simple web page, like HTML.

  • Let's close that tag.

  • Let's have a head of the web page, close the head of the page.

  • Let's have the title, like let's just call this my first app title.

  • Let's have a body of the page here, and close body.

  • And just, let's go ahead and say for now,

  • hello world, but surrounding it with html.

  • If now in my application.py I want to output

  • that file and not just a hardcoded string, I can use that function

  • we imported earlier, render template, and render index.html.

  • Let me go ahead and save that, go back to my browser,

  • hit reload, and of course, can't be reached, refused to connect.

  • But that's because I'm not running Flask.

  • So sometimes two windows are helpful.

  • So I'm going to go ahead and close this, do Flask run to kick off the web server

  • again.

  • Let me go ahead though and go open another tab.

  • So we have access to two windows here, application.py.

  • And now let me go ahead and reload.

  • And now it's working.

  • And now if I view the source you'll see that I have a full-fledged web page.

  • But the cool stuff you can do pretty easily with Flask is like this.

  • You might recall from the internet more generally,

  • you can often have URLs that have question marks in them to provide user

  • input, like name equals David, Enter.

  • Now that doesn't change the behavior of my app yet.

  • But it could if I do this.

  • If I go in here and I use my request variable that I imported earlier,

  • I can do something like this.

  • Name equals requests.orgs get, quote unquote name.

  • And if the user gave me his or her name, I can now do something like this,

  • pass in a name equal name, where name is a key and name is a value here

  • that came from the variable.

  • If I go into my template now I can do something like this, not hello world,

  • but I can do curly curly brace, or mustaches, and say name,

  • and have a placeholder holder in this template.

  • Now if I go and reload it's not going to quite work as I expect yet.

  • See nothing seems to have happened.

  • But that's because Flask, like a lot of frameworks,

  • is caching my output from before for performance.

  • So I can solve this in a couple of ways.

  • The simplest is just to stop the server with control C, rerun it

  • with Flask run.

  • Now go ahead and reload, and indeed you see David.

  • If we now change this to Colton up here, you can see Colton and any other name

  • too.

  • COLTON OGDEN: So I might see where you're going with this.

  • I think we have another iteration potentially.

  • DAVID MALAN: Oh, where are we going with this?

  • COLTON OGDEN: Maybe some SQL integration?

  • DAVID MALAN: Oh, a SQL integration, yeah let's do that.

  • So let's go ahead now and go into application.py,

  • which is the controller in my application,

  • and just add a bit of SQL logic.

  • But let's take a look here at a few questions first.

  • So scrolling back into town, we I think we left off with the lol

  • before, and then the Docker question.

  • Where can I download Docker for Windows?

  • So honestly, I would just Google Docker for Windows

  • and the first hit should give you there, should come up on Docker.com.

  • Where can you find contacts on the internet

  • for having a direct job interview for like Skype?

  • Honestly, check, as we mentioned earlier, LinkedIn, looking

  • for contacts, recruiters especially.

  • I'm sure we'd welcome reach-outs, as they're

  • trying to identify talent as well.

  • Thank you, [? swarm ?] logic, for the Docker reference there.

  • [? LinkedIn ?] stuff for everything, thank you.

  • COLTON OGDEN: Oh right, yeah.

  • DAVID MALAN: Why do we use Python and not PHP?

  • You want to take that one?

  • COLTON OGDEN: Well, it's a bit of an opinionated answer, I think.

  • I mean PHP is a bit of an older language now,

  • though it's not safe to say that it's not used.

  • Because I think Facebook does still use PHP.

  • DAVID MALAN: Yeah, they technically use Hack I think,

  • which is their own variation of PHP.

  • COLTON OGDEN: But I think a while back we just

  • altogether agreed that Python was just a bit more elegant, better pedagogically.

  • PHP kind of has verbose sort of function names.

  • And it's kind of, it feels a little bit more of a hackish language than Python.

  • Python's a bit more I think engineered, I would say.

  • DAVID MALAN: It's true.

  • PHP has some messiness.

  • But by the time they got up to version 5 point

  • whatever, a lot of that had gotten cleaned up I think.

  • There were remnants, but it was teaching better design principles,

  • or more modern ones.

  • COLTON OGDEN: I think you expressed that you found it more or less pleasurable

  • using frameworks like Laravel, or I think Symphony was another?

  • DAVID MALAN: No, I never liked Symphony.

  • But Laravel I liked, even though we never actually rolled it out.

  • Yeah, I mean, honestly the way I summed this up in a Quora answer was,

  • it was time.

  • Like the reality was there's just increasing amount of momentum

  • these days around Python and also Node.js.

  • But that I think is a little too complex for the pedagogical purposes we have,

  • especially given the asynchronicity of it all.

  • Python is useful for not just web applications but also command line

  • scripts.

  • And yes, you could use PHP for command line scripts.

  • And indeed, I did myself for years.

  • It was never really the right language for that, even though you could.

  • It was designed for the web.

  • And I think too that there's just a practicality of Python.

  • It's just kind of increasingly omnipresent.

  • And it's not a bad thing for students to have some programming chops in Python.

  • So I do think that it's documentation is inferior.

  • I think PHP's documentation is fantastic, especially for newbies.

  • And so we did give that up, which I think is an unfortunate price to pay.

  • Python's documentation is not nearly as accessible or thorough, I think.

  • But I don't regret the decision, I would say.

  • COLTON OGDEN: I think you made the right call.

  • DAVID MALAN: Thanks.

  • All right, let's see.

  • Let's see.

  • Hello to [? Sirez, ?] if I'm pronouncing that right.

  • Welcome to Working On Music.

  • Having some issues with the isRest function.

  • Probably a little tricky for us to answer that in real time here.

  • But by all means, reach out on--

  • This course's subreddit-- r/cs50.

  • Or Facebook, or Twitter--

  • If you go to cs50.edx.org/communities, if you don't mind maybe typing that

  • out.

  • On the chat, cs50.edx.org/commuities--

  • That should give you a link to any number of places

  • you can asynchronously ask questions.

  • Let's see.

  • Julius, Thank you, appreciate you providing valuable content

  • for everyone, national treasure.

  • Oh, thank you, Julius.

  • COLTON OGDEN: I just said, we saw it here.

  • DAVID MALAN: Just for the web and data analysis--

  • OK.

  • Come on to asynchronous by a--

  • OK.

  • Swarm logic.

  • I don't disagree, but I think for students

  • who have only two months prior learned what a for loop is, and a while loop,

  • and the like--

  • I do think it's a non-trivial step.

  • And so while we do teach it actually in a follow-on class on campus here

  • at least, called CS100, Software Engineering in the Arts

  • and Humanities--

  • I'm comfortable saying it's a little too much too

  • soon for most of CS50 students.

  • Who, again, have just learned a few weeks

  • prior, procedural programming itself.

  • Python is cool for non-CS majors also.

  • It's quite popular in STEM, I suppose.

  • Yep.

  • Absolutely, agree there Andre.

  • Sure, Ann, as well.

  • Suggested something-- How did you actually

  • get into computer science and programming, both of you?

  • COLTON OGDEN: You first.

  • DAVID MALAN: Slowly, I would say.

  • The story I always tell in CS50 that as a freshman in college,

  • I was a little too intimidated by CS50 because I went here when

  • the course was already offered here.

  • And I didn't take it freshman year because it seemed only

  • for kids who'd been programming since they were 6 years old.

  • And I was scared away.

  • Finally got up the nerve to shop the class or sit-in on it, sophomore year.

  • And honestly, I just fell in love with it.

  • And legitimately, it was the first time ever

  • in 18-plus years of going to school that homework was fun.

  • I used to look forward to going home on Friday nights

  • and writing computer programs for CS50's homework.

  • And that just felt like a sign for me.

  • COLTON OGDEN: For me, I was interested in game development.

  • I actually had no idea what programming was.

  • And I bought a bunch of books and saw this weird syntax

  • for all these home-brewed programming languages that various game

  • engines in the mid-2000s used.

  • And was turned off by it, but eventually gave it another chance.

  • And then I found that I actually really enjoyed C and C++,

  • and then other languages in years thereafter.

  • It's been an interesting ride.

  • DAVID MALAN: Feel free to share your stories too,

  • especially if you're just starting off in programming-- how you actually

  • came to find your way here.

  • Minter27, currently during the final project for CS50

  • and having a bit of a problem.

  • Can I iterate over two arrays that have the same length, I presume you mean.

  • If so, how?

  • So, short answer, yes.

  • Depends on the language, of course.

  • But if they are the same length, you can just find the length of one of them.

  • Then use a for loop, for instance, over that specific length and index

  • into both of them.

  • But if I'm inferring here that you have two arrays that by design have

  • the same length, and you're just trying to line up elements in each of them--

  • odds are, that's not a good design.

  • You should generally never have parallel arrays, so to speak, each of which

  • represents something else--

  • but between which there is a relationship.

  • You should probably, instead, have one array, each of whose members

  • is a struct in C, or an object in JavaScript,

  • or a dictionary or a tuple in Python so that you're actually

  • keeping similar data together in just one bigger array, if you will.

  • How do you get into Harvard, asks [? Gassen. ?] Love

  • to be there for just one day.

  • OK.

  • So getting into Harvard for one day is very easy.

  • You just come to Cambridge, Massachusetts, 02138.

  • In fact, folks online are welcome to come

  • to see CS50's lectures, which are on Friday mornings.

  • It might be a little too short notice because we only

  • have two left this semester.

  • And those are coming up the next couple of weeks.

  • But by all means, next fall-- next September--

  • if you'd like to join us on campus in an actual CS50 lecture,

  • you're welcome to join.

  • Keep an eye on our social media channels for the invitations

  • and the free tickets for that.

  • You can also take free tours of Harvard, walk around Harvard Square.

  • You're welcome to come through and just see the university itself.

  • And if you go to Harvard.edu, there should

  • be a link somewhere to visitors or guests

  • that should provide you with all information.

  • Everyone I met told you that I looked like a programmer.

  • Don't know what that means, so when life gives you lemons, eh?

  • OK, so that's fair.

  • COLTON OGDEN: Fitting the mold, so to speak.

  • Oh that's great.

  • I forgot about indexing.

  • Thanks so much.

  • DAVID MALAN: Swarmlogic-- OK I don't know if this is going to be sarcastic

  • or not but here we go.

  • I knew I was getting out of the Army soon-- a few years ago-- took

  • CS50x, kept going and hacking.

  • Now a software engineer.

  • Wow.

  • That's awesome on multiple fronts.

  • COLTON OGDEN: He was here the last couple streams I did.

  • And he was providing suggestions to the implementation.

  • DAVID MALAN: That's awesome.

  • Glad that went so well.

  • And thank you, of course, for everything you did there.

  • And of all the things to find your way to--

  • computer science.

  • That's fantastic.

  • COLTON OGDEN: And we know I needed all the help I could get.

  • So my story-- I'm a translator and had no idea

  • how programming worked whatsoever.

  • I wanted to learn a bit and get out of my comfort zone.

  • And a friend suggested Khan Academy.

  • It was so easy that I could actually get into it.

  • And much like you, I fell in love with it from the beginning.

  • For Free Code Camp at CS50 afterwards.

  • Thank you both so much for the wonderful work.

  • DAVID MALAN: Now I feel a little bad.

  • We're not actually fishing for CS50 stories here.

  • You're welcome to have your own independent discoveries.

  • But that's really sweet to hear.

  • COLTON OGDEN: And let's [INAUDIBLE] who provided the concentration game

  • suggestion from last week.

  • DAVID MALAN: Yeah, keep the suggestions coming.

  • Colton loves to prepare large projects for these streams.

  • So I think we should just possibly finish this thought

  • because we're running low on time.

  • And we can go ahead and get this little app working.

  • So I have access here to my DB variable after having

  • connected to the SQLite database.

  • And so really, I can select anything I want from this.

  • And so rather than get the argument just directly from the URL,

  • why don't we go ahead and generalize this-- maybe

  • Q for query, just like Google does.

  • And then let's use this variable Q in a SQL query.

  • So let's go ahead and say, for instance, rows gets db.execute.

  • Let's say, select name FROM instructors WHERE name LIKE colon q.

  • And then we can go ahead and plug in the value like this.

  • We can go ahead and say q should equal request.args.get.

  • Its wrapping because the response is a little big here.

  • q, and then I can go ahead and just append to that a percent sign.

  • And I could clean this up and actually use kind of a templating approach

  • to make this a little cleaner.

  • But for now, this gets the job done.

  • But what's most important and we go into more detail in this

  • in CS50's lectures--

  • Always, always, always use some kind of placeholder

  • that's built into the SQL library itself.

  • Do not just use Python's f strings or format strings.

  • Do not just concatenate a value onto that first argument.

  • You will make yourselves vulnerable to a SQL injection attack, which means--

  • as someone alluded to earlier--

  • you can accidentally let a bad guy slip some of their own code

  • into your database, potentially compromising,

  • deleting, updating your data in ways you do not attend.

  • So now that I've done this, let me go ahead and save this.

  • And now pass in, not the name, but let me go ahead

  • and pass in all of these rows.

  • Let me then go into my template, index.html.

  • And let's go ahead and get ready for a list of 0 or more results--

  • this time creating an unordered list in HTML.

  • And then in there, let's go ahead and do this-- for row in rows.

  • And now I'm using this templating language

  • I alluded to earlier called Jinja--

  • syntax of which is almost identical to Python, but a little different.

  • Like endfor is not a Python thing, it's a Jinja thing.

  • And now let me go ahead and say hello and then curly brace

  • row, name, plugging in the 0, the first, the second, and so name that's

  • coming back from the database.

  • So now you see the power.

  • Even though it's just scratching the surface of what

  • you can do with a templating language using

  • Python to get the data from a SQL database using Jinja-- a templating

  • language to render the data.

  • So we have all these technologies now talking together.

  • So if you've ever heard the term technology stack,

  • that's kind of what we're talking about.

  • All this stuff stacked together to produce one final result.

  • So let's go ahead and search for just Colton first, reload.

  • And that does look as we expect, but let's go ahead and restart the server.

  • But you can also do this.

  • With flask, if you pass in reload, it will reload it on every request.

  • But that won't make a difference on your templates.

  • You might have to still restart then or add some code to make that work.

  • Let's reload.

  • Uh-oh, dammit, I thought we almost made it through the stream perfectly.

  • But internal server error means I screwed up somewhere.

  • It's kind of overwhelming at first type.

  • But it looks like unsupported operands type for plus.

  • So I seem to be getting back none from the database.

  • And I'm trying to concatenate a stir onto that.

  • So let's see if we can diagnose this.

  • So that's definitely on my DB line.

  • So let's go into application.py.

  • So let me go ahead and do where name like q [? equals request.rxget. ?] Oh,

  • you know what I did?

  • COLTON OGDEN: I do know what you did.

  • DAVID MALAN: I know what I did too.

  • I never changed my variable.

  • So now in a better application where we're not just whipping it up

  • on the fly, we are going to have some more actual error checking.

  • But recall that I changed the parameter to q.

  • So let's change that in my browser to q.

  • Now, I get Colton.

  • It's formatted a little differently because even

  • though you don't see the bullet here--

  • I actually forgot the tag.

  • That's why you don't see the bullet.

  • So let me actually fix this.

  • Let me go back to my template.

  • And let me give you your very own list item tag.

  • Let me restart flask.

  • And now let me reload, and voila.

  • Now we see the li tag.

  • Now we see the bullet.

  • And if we go ahead and search for, say, nothing here, now we get everyone.

  • Because the empty string matches everyone.

  • And so if we reload here, we see that we indeed now

  • have a bulleted list of two people.

  • COLTON OGDEN: Did you end up using the like thing

  • in the lecture demo for the dictionary?

  • DAVID MALAN: Most recently, I think, very briefly.

  • Someone in the class asked about it.

  • And I changed my equal sign to a like.

  • So let's see.

  • I think we've got to scroll up a little bit.

  • We got a little distracted here.

  • COLTON OGDEN: I think this was the last where we left off on.

  • DAVID MALAN: So either of you participate in Hacktoberfest 2018?

  • No, I did not, I'm afraid.

  • Helped build a boot camp.

  • I spent some time in college, but I couldn't find what I liked.

  • And eventually realized that the answer was right in front of my face

  • on my computer.

  • So I decided I wanted to contribute to the world of programs and apps

  • that made my life easier.

  • That's awesome, very compelling.

  • And the lives of others too.

  • Nice to slip that in there too.

  • [INAUDIBLE],, OK, thank you for the qualification there.

  • I think that's on you.

  • COLTON OGDEN: This is called [INAUDIBLE]

  • DAVID MALAN: Swarm logic.

  • OK.

  • Nice little connection here.

  • It is live, right?

  • Yes.

  • [INAUDIBLE] It is live, because you're hearing this live right now,

  • probably a few seconds after you type that.

  • All right, Colton, things coming up on this Friday?

  • COLTON OGDEN: Yeah, on Friday.

  • And we'll be doing a 3D [INAUDIBLE] example in Unity and C#.

  • DAVID MALAN: [INAUDIBLE],, why not q equals q?

  • That's because I'm stupid and I forgot that.

  • So yes, that would in fact make this line a little shorter.

  • Thank you.

  • Very well said.

  • I missed that myself.

  • Let's see.

  • Live on Friday.

  • Yep.

  • Everything's live and we also post it on demand after.

  • And what else have we got here?

  • Uh-oh.

  • [INAUDIBLE] just wanted to say this.

  • CS50 is my most favorite course, especially because of David and Colton.

  • You put in so much work and can actually see you sweating in the lectures.

  • Yes, unfortunately there's not very good air conditioning in there.

  • I want to watch lectures when I don't have to and I love you

  • guys because of that.

  • Your not so secret admirer, Nate.

  • Thanks so much, Nate.

  • That's really quite sweet.

  • I guess keep the comments coming today.

  • We do appreciate it.

  • But you're all too kind.

  • COLTON OGDEN: Seems to be a good temperature in here, though.

  • You're not sweating too much.

  • DAVID MALAN: Yeah, no sweating here.

  • It's when we're moving around more and it's

  • under the hot lights of the theater that it gets a little warm.

  • Here we have all LED lights.

  • So I think we're winding down.

  • Feel free to chime in with some final questions here.

  • But just to recap what some of the goals were--

  • For those unfamiliar with SQL, hopefully you

  • have a little bit of a sense of what's possible with it.

  • Actually, do you mind going ahead and pasting

  • a link to this past week's lecture for folks who

  • would like an even more in-depth look?

  • Go to CS50.harvard.edu.

  • If you go to CS50.harvard.edu and click on--

  • do slash weeks.

  • Actually 2018/fall/weeks/8 enter.

  • That should lead you to this past week's lecture video,

  • which offers more in-depth discussion of SQL and what it's for.

  • It will recap a little bit of what we did, but using different examples.

  • By all means, tune in there.

  • Those of you who are following along with CS50 on edX, the final one or two

  • problem sets will introduce you to Flask, and Python, and SQL, as well.

  • So feel free to tune in there if you want some hands-on experience.

  • And then of course in the coming days, do

  • you want to recap the upcoming Livestreams that we've got going on?

  • COLTON OGDEN: Yes.

  • So tomorrow we'll be joined by Nick Wong, who'll

  • be giving us a tour of a basic binary classifier from Scratch

  • using Keras and TensorFlow--

  • a couple of open source machine learning libraries in Python.

  • On Wednesday, we'll be joined by Brian Yu, who

  • taught the web course with CS50.

  • And he's also a head teaching fellow.

  • And he will be talking about some React basics.

  • And then on Friday, it'll be just me and we'll be talking about Unity in C#

  • making 3D Pong.

  • DAVID MALAN: So all this is thanks to Colton.

  • He's been spearheading this whole initiative,

  • building the audience on Twitch, getting everyone lined up

  • to chat about various fun technologies.

  • So props to Colton for that.

  • COLTON OGDEN: And my guests, CS50's own David Malan.

  • DAVID MALAN: Thank you.

  • [? Bavik Knight-- ?] any examples of left inner join?

  • If you don't mind, let me defer to the lecture

  • that Colton just pasted the URL of where we go into a little

  • more detail on joining data.

  • We kind of set the stage today when talking about instructors and courses.

  • And in that lecture, we go into more detail

  • on how you can join those data sets back together in a way

  • that SQL really empowers you to.

  • And thank you, [? Gassen, ?] for tuning in today.

  • We're indeed here live from Cambridge, Massachusetts.

  • You're very welcome, [? WebStreak23. ?] Thank you

  • for the kind shout-outs, literally.

  • Any final questions?

  • COLTON OGDEN: Some people just lost their hearing on stream.

  • DAVID MALAN: Give folks a few more seconds here.

  • COLTON OGDEN: [INAUDIBLE] Colton, thank you.

  • Studying to understand Friday's session better.

  • Thanks [INAUDIBLE],, looking forward to having you there on Friday.

  • DAVID MALAN: Can we do a shout-out actually to this [INAUDIBLE]..

  • You want to paste the URL @CS50.edx.org/games?

  • So if you're interested in learning more about gaming from Colton specifically,

  • Instructor ID 2, you can go to that URL there-- @CS50.edx.org/games.

  • It's freely available.

  • Just click the audit option and you can follow along with all the projects

  • there.

  • Nice to see you, David, from Tel Aviv.

  • Hello from Cambridge here.

  • No children here.

  • Thanks for this very much.

  • Is it a major game-changer for web?

  • Is it a major web--

  • What is a major game changer?

  • I'm not sure I follow, [? JPGuy. ?]

  • COLTON OGDEN: Thanks, Bella, for tuning in today.

  • Appreciated.

  • DAVID MALAN: Very welcome [? Vahib. ?]

  • COLTON OGDEN: First course, wow.

  • DAVID MALAN: Nice.

  • Let's see what other final comments come in here.

  • Can't quite see the blue text there from far away here.

  • But you're very welcome, London.

  • Oh, web assembly.

  • I guess we missed that there.

  • Yes so web assembly-- maybe.

  • So we in CS50 are actually pretty excited about the potential

  • of web assembly.

  • One of CS50's former TFs-- or Teaching Fellows--

  • is actually at Google now working wonderfully on that team.

  • What we are hoping it enables ultimately is

  • to do much more browser-based development in languages

  • besides JavaScript and Python.

  • In fact, we're hoping before long, we'll be

  • able to have students coding C in the browser

  • without even needing a server-side IDE or cloud environment.

  • Hopefully, it'll be possible even offline.

  • It's probably a few years away, but we shall see.

  • So yes, [? JPGuy, ?] I think that's all very, very promising.

  • And also for performance, I think, of future applications

  • where as single-page applications get more popular,

  • they can be theoretically even more performant if they can be compiled

  • down to something more native, locally.

  • COLTON OGDEN: I can't read this name on here.

  • Oh, gymnasium.

  • DAVID MALAN: Gymnasium88.

  • Thank you, London, as well.

  • [? Bavik, ?] we'll tell Jordan that you've requested his presence.

  • He works nearby at a startup.

  • So we'll see if he can join us here some time.

  • Thank you Liberianwarlord.

  • Also a sentence I've not had to say very often Very welcome,

  • [? Munich. ?] Thank you, [? Gassen. ?]

  • When will David be back?

  • I don't know.

  • I don't think I've been booked yet.

  • So Colton has the schedule packed, but hopefully in a week or two.

  • COLTON OGDEN: We don't want to spoil what

  • we're going to do in a couple of weeks.

  • DAVID MALAN: I don't know what are we going to do in a couple weeks?

  • COLTON OGDEN: Thanksgiving week.

  • DAVID MALAN: Oh no, let's keep that to ourselves for now.

  • Stay tuned for an exciting day of relaxation

  • once it's Thanksgiving here in the United States, which is the holiday.

  • Anything else?

  • COLTON OGDEN: We'll see you soon.

  • DAVID MALAN: Nope, no spoilers.

  • COLTON OGDEN: We'll keep that one on the DL.

  • DAVID MALAN: All right, I think we're going to have

  • to log out here in a few seconds.

  • No spoilers, there you go.

  • Apparently Unity noticed something like 30% performance increase

  • by compiling to Web Assembly rather than the JS Web Assembly Emulator.

  • I'm not surprised.

  • I think that's the kind of potential that lies ahead.

  • COLTON OGDEN: Yeah, I've heard similar.

  • Spoiler, spoiler.

  • DAVID MALAN: How about this?

  • Do you know with what button can we stop the stream?

  • Don't hit it just yet.

  • COLTON OGDEN: I have to go over here and click this.

  • DAVID MALAN: OK.

  • So how about this?

  • In just a couple of weeks time, what Colton and I are going to be doing is--

[MUSIC PLAYING]

字幕と単語

ワンタップで英和辞典検索 単語をクリックすると、意味が表示されます

B1 中級

SQL BASICS - CS50 on Twitch, EP.6 (SQL BASICS - CS50 on Twitch, EP. 6)

  • 2 0
    林宜悉 に公開 2021 年 01 月 14 日
動画の中の単語