CS50x 2024 - Lecture 7 - SQL 

this professor is a teaching machine, way to go!!!!

01:02 Introducing the need for different tools in software programming and the introduction of SQL. 03:11 Google Forms data integrates with Google Sheets for analysis. 07:16 CSV files use quotes for strings with commas to avoid confusion 09:28 Using 'with' in Python to automatically close files. 13:36 Using dictionary to access data instead of numeric indices 15:43 Iterating over a file row by row and incrementing counters based on the content 19:37 Using dictionaries to count occurrences of values in a loop. 21:44 Using dictionaries to track and print values 25:44 Python makes sorting easier with built-in functions and rich libraries 27:46 Python's Counter class simplifies counting and sorting 31:54 SQL allows for interactive and efficient data analysis 33:54 SQL is a declarative language for describing data retrieval and analysis. 37:33 Loading data into SQLite using the command line 39:27 Overview of 'schema' command and selecting columns from a database 43:33 SQL provides various functions like count and distinct to retrieve specific data from tables. 45:29 SQLite distills things into the essence and is widely used. 49:29 Changing the query to get a top list and using aliases 51:29 Adding data to a table using SQL. 55:25 Updating and modifying data in SQL tables. 57:19 Dealing with real-world data sets from large databases 1:00:56 Discussing the design of the spreadsheet implementation. 1:02:39 Eliminating redundancy in data storage 1:06:43 Exploring SQL database normalization 1:08:43 Working with IMDb data and converting to SQLite format 1:12:38 Understanding data types and primary/foreign keys in SQL. 1:14:26 Database design allows for null constraints, unique values, and relationships between tables. 1:18:34 Using SQL to filter and select specific data from multiple tables. 1:20:36 Using SQL to retrieve shows with ratings 6.0 and above. 1:24:21 Joining tables and filtering data based on conditions. 1:26:25 Using SQL and joins to retrieve specific data from tables. 1:30:25 Using SQL to query and reference data in tables. 1:32:23 Using SQL to compose smaller ideas into larger queries for data retrieval. 1:36:11 Using a third table in a many-to-many relationship requires additional steps to retrieve data. 1:38:20 Retrieving person IDs associated with a specific show ID using nested queries 1:42:17 Executing joins with multiple tables in SQL 1:44:36 Different ways to join tables in SQL 1:48:16 Creating indexes in SQL can significantly improve query performance. 1:50:12 Optimizing database performance using indexing 1:54:09 Indexes drastically improve query performance 1:56:19 Resetting and importing data in SQL using SQLite3 and Python 2:00:26 Managing large databases in SQL 2:02:29 Race conditions in large systems 2:06:05 Use transactions to avoid race conditions in SQL. 2:08:09 Single quotes are important in SQLite for strings and comments 2:11:49 Using a library like CS50 handles escaping of user input

Equal equals equal in sequel is my new favorite tongue twister

Just beautiful. This was a significant improvement over last year's lecture and I really liked it. Thank you Prof. Malan!

Great lecture as always. Thanks CS50 and Prof. David Malan for offering this course to everyone free of cost.

Thank you, dr. Malan People like you are really inspirational for CS world.

Awesome video! I'm picking up SQL for the first time, and using it as part of my PhD in humanities, and this was perfect to get started, thank you!

Thank you Dr. Malan! A great lecture! It is very noble of you to make such knowledge readily accessible and explained in a captivating way.

the best intro of CS50

Great lecture Dr. Malan! Shout out to the supporting team behind too for breaking the educational barriers

I love how he teach.. I love how he explain.. Man.. Wish the world have a people like him on every life-aspect

Learning SQL is like learning PLC programming. The syntax, theory, workflow, framework... are pretty straightforward. The clusterfeck comes when you start working and realizing that other people's system and setup are ridiculous (and now you are tasked with maintaining or scaling it).

fantastic lecture as always, thank you!

Learning from a Harvard Teacher from a brazilian favela for free.

As always best lecture 💚

An operating systems course by CS50 would be awesome

i went to an SQL course at Oracle 25 years ago... the basics have not changed but this is clearly and well explained and once you master it, it is so much better than python for large data queries . CS50 is really the best.

Thank You, David for everything. I really liked the Milk analogy.

❤❤ Great lecture as always.

One of the best teachers EVER!!

So thorough!

Honestly wish the first part of the lecture opening csv files was included in the last video. Really struggled with this in pset DNA

great lecture ;

1:44:24 stopped, indexes, rare condn., sql injections

There's a whole new intro now!!

getting the information from the first part of the lecture would be super useful for the DNA task 😄

What would be the easiest way to view a relational view (as per in the video with the connected boxes) of the schema for shows.db? Could I export .schema's output into some sort of previewer? edit: The term I was looking for is 'entity-relationship diagram'. As a manual workaround and some help from docs, I converted the sql to postgresSQL and used the dbizzy extention for vscode. It is a bit basic and doesn't show the one-to-one, one-to-many etc symbols, but it's a start.

Amazing professor! I was looking for vídeo tutorial to recommend to a friend. This one is perfect.

The opening music is awesome! Came for the class but stayed to listen to the opening music on loop!

thank you so much for this lecture, it was useful 🎯

GOAT ACTIVITIES

That great!

47:53 "Equal equals equality in the world of 'Sequal'." 😂

Describing joins as glue should result in the wrong mental model though. The use case Joins have been invented for is a glue-use-case, but it's a quite specific edge case. In actuality, joins are combinatoric operations, so you'd start with explaining cross joins first. All other joins are essentially cross joins within a domain (specified by keys with ON/USING) where you chuck out NULLs selectively depending on the kind of join (e.g. right-side NULLs in LEFT JOIN). Understanding the combinatorics nature of joins is so essential, especially when debugging and a key we thought was unique is not actually unique.

what is the logic behind row 1, why all the entries has come in answer?, this is column value right

I'm grateful that I can understand English, as it allows me to learn from some of the world's leading professors.

Incredible eloquence at such talking speed ?

1:16:33 I think he meant to say "primary key"

Hi Team, Can anyone teach about ETL concepts and how it works in real time project.

Where can i find the data you use in the starting of the video?

NGL, the intro reminds me of GOT intro. EPIC.

oh david mentioned the bobby table :) at the end

DAVID

how did he make his terminal so clean when he run his programs. I have vs code but I have literally failed to setup such an environment. Please be kind I am old

Wait everything getting uploaded all at once? Nice

my favorite joke related to SQL and database XKCD 327 Little Bobby TABLE

I'm a noob in SQL but why isn't my sqlite3 interface as neat as this interface with ASCII art? I downloaded it from the original website and I'm using it from Windows cmd. Is this a different version or from a different source? Mine doesn't even display the headers

we need some videos for networking

Without data there is no need for CS!

hy atvedx how ro i choose language

1:38:44 Batman swoops in

Where can i get Show.db file?

It would be great, if you could upload the favorites.csv file to the Lecture Notes!

what does code means in terminal when $ code favourites.csv is given in command line??

Hello, would love to know the music composer's name

how can I get the shows.db file?

One question: at 2:06:24 Does python automatically insert the missing semi-colons at the end of sql commands? and also, I guess there shouldn't be semi-colon after db.execute() command as python doesn't need ( ; )

i wonder wh y there are only 2 comments in such a informative video?

Show 62614:Zeg 'ns Aaa 😂 That's an old Dutch serie which my parents always watches

This year's SQL lecture is a lot easier to follow, which is convenient since I was halfway through the old one when it uploaded. I'm not sure about the new introduction though, the music choice is a little strange, I miss the old one

1:31:29

5-6 months SQL course in college < CS50 2 hours SQL Lecture

I'm getting this error: cs50 has no SQL attribute. Can someone help me

Porfavor Inclui disponibilize Legendas em português Brasil

which year's recording is it ?

Tough crowd

Still waiting for the updates on EdX😭

I know this already. But I still feel that I don't known anything. I am under valuing myself...please help sql interview seems very daunting

first 30 minutes would of be nice to have it before past week 6 problem set...XDD

18:26

Hi

아따 이집 맛집이네.... 잘가르치네

1 like = one minute of cs50

why didnt anybody laugh at the ending comic. i thought it was pretty funny😭🤣

Hey

sequel

Aguante Messi y Argentina!

33:02

1like = 5pushups + 45min workout in 2024

50:00

Who else is watching Catweazle?

23:12

English?

1 like = 100 pushups in 2099😂

worst way to learn SQL