CS50 2021 in HDR - Lecture 7 - SQL

TABLE OF CONTENTS

00:00:00 - Introduction
00:01:17 - SQL
00:03:21 - Data Collection
00:07:46 - Flat File Databases
00:09:24 - Reading from a CSV
00:14:26 - Filtering Duplicates
00:21:21 - Counting Titles
00:28:53 - Sorting Data
00:30:26 - Lambda Functions
00:36:07 - Cleaning Data
00:39:48 - Regular Expressions
00:45:50 - Search
00:48:17 - Relational Databases
00:51:20 - Create, Read, Update, Delete
00:53:32 - sqlite3
00:57:09 - SELECT
01:01:58 - WHERE
01:04:37 - DELETE
01:05:59 - UPDATE
01:11:31 - Redundancies
01:18:00 - Table Relationships
01:27:49 - INSERT
01:30:10 - Python and SQL
01:41:49 - SQL Types
01:43:11 - Many-to-Many Relationships
01:48:27 - Indexes
01:59:14 - JOIN
02:03:38 - SQL Injection Attacks
02:11:15 - Race Conditions
02:20:10 - Transactions
02:21:26 - This was CS50

***

This is CS50, Harvard University's introduction to the intellectual enterprises of computer science and the art of programming.

***

HOW TO SUBSCRIBE

HOW TO TAKE CS50

HOW TO JOIN CS50 COMMUNITIES

HOW TO FOLLOW DAVID J. MALAN

***

CS50 SHOP

***

LICENSE

CC BY-NC-SA 4.0
Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International Public License

David J. Malan

[abrahamtellez]

An excellent teacher makes all the difference in the world. Your ability to explain complex ideas in easy to understand ways is remarkable. Thank you for being a person in the world.

[LittleRedx]

If these videos were around in 2003/04 I might have actually finished my college programming David is able to explain everything in terms that everyone could understand - even older adults who haven't been in high school for many years. 😺👏👏👏 (from Canada)

[MrX-nccm]

We didn't get a "This was CS50" at the end :(

[andybecker]

Been using sql and oracle tables for years for data analysis. This is a fantastic introductory explanation

[Akerfeldt]

My life changed for the better when I found this and a host of other Harvard courses were actually open to all...free. Good on you! And thank you!

[ricardocorrea]

Can someone tell me if this was cs50? They didn't do it at the end this time so I'm not sure :(

[mobai]

I have finally done all the Pset and Lab for Python! Finishing DNA feels like beating a Dark Souls boss lol. it's 2 A.M already but I'm so excited I'm gonna watch some more lecture 7 until I'm too tired. This is the first time in my life that I'm actually excited to study lol.

[dluca]

Every lesson is a mind bender but so interesting!

[JokhioAppsReal]

This SERIES is so underrated. It should get a lot more views.

[ubermensch]

13:21 David Malan looks so epic in that frame

[basic]

In math, a set is represented with {}. In Python, the initialization of set is done by set() and the initialization of dictionary is done by {}. Also, a dictionary has more structure than a set. It's amazing how the creator of this lecture might have thought this through for the beginners and also naturally progressed from the use of the set to the use of a dictionary. Thank you!

[wmblemania]

A suggestion for next year: make the selections visual.
i.e. show graphics to explain the relationships between tables, and how the queries are formed.
Otherwise, it's a lot like trying to work it by reading a book, which isn't easy.

[johiny]

amazing introduction to databases, when david explain primary keys and foreing keys was WOW

[jbiemans]

From first hand experience do not ever use delete while also using '%' wildcards without a really strict input validation to avoid just deleting where x = '%%'. It doesn't really end well. :)

[laughing-out-loudhd]

Can you please make a lecture about only relational and non-relation databases like you did in python? 😩😩😩😣

[azzyyy]

We're getting underway with the long way to go.

[danivanon]

2:07:45 - why would one do '{username}' and not just {username} ? I'm confused why the quotes are even necessary for this situation.

[aracari]

The editing up until this point has been phenomenal, which makes it particularly jarring several times in this video when we are viewing the stage instead of properly seeing his screen. E.g. at 1:42:10 he is pointing at stuff on the screen but I can't read it

[laughing-out-loudhd]

Hello David. You said we don't have such powerful regex in sql as we do in python. Yes we don't have such regex in sqlite3, but we do have in MySQL. we have REGEXP clause..

[HB-qqjx]

Awesome video as always! Thank you for protecting my innocence by blurring out that naughty tv show 😏