CS50x 2024 - Lecture 7

CS50x 2024 - Lecture 7 - SQL

Рет қаралды 369,246

Күн бұрын

***
This is CS50, Harvard University's introduction to the intellectual enterprises of computer science and the art of programming.
***
TABLE OF CONTENTS
00:00:00 - Introduction
00:01:01 - Flat-file Databases
00:33:00 - Relational Databases
00:57:02 - Halloween Costume Contest
00:58:31 - Shows
00:59:30 - Schema
01:13:29 - Data Types and Constraints
01:15:04 - Primary and Foreign Keys
01:17:43 - Querying
01:47:24 - Indexes
01:55:44 - Python and SQL
02:00:30 - Race Conditions
02:06:47 - SQL injection attacks
***
HOW TO SUBSCRIBE
kzfaq.info_c...
HOW TO TAKE CS50
edX: cs50.edx.org/
Harvard Extension School: cs50.harvard.edu/extension
Harvard Summer School: cs50.harvard.edu/summer
OpenCourseWare: cs50.harvard.edu/x
HOW TO JOIN CS50 COMMUNITIES
Discord: / discord
Ed: cs50.harvard.edu/x/ed
Facebook Group: / cs50
Faceboook Page: / cs50
GitHub: github.com/cs50
Gitter: gitter.im/cs50/x
Instagram: / cs50
LinkedIn Group: / 7437240
LinkedIn Page: / cs50
Medium: / cs50
Quora: www.quora.com/topic/CS50
Reddit: / cs50
Slack: cs50.edx.org/slack
Snapchat: / cs50
SoundCloud: / cs50
Stack Exchange: cs50.stackexchange.com/
TikTok: / cs50
Twitter: / cs50
KZfaq: / cs50
HOW TO FOLLOW DAVID J. MALAN
Facebook: / dmalan
GitHub: github.com/dmalan
Instagram: / davidjmalan
LinkedIn: / malan
Quora: www.quora.com/profile/David-J...
TikTok: / davidjmalan
Twitter: / davidjmalan
***
CS50 SHOP
cs50.harvardshop.com/
***
LICENSE
CC BY-NC-SA 4.0
Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International Public License
creativecommons.org/licenses/...
David J. Malan
cs.harvard.edu/malan
malan@harvard.edu

Пікірлер: 164

@jaanifilmwala 5 ай бұрын

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

@branthebroken2323 3 ай бұрын

Where can i get the Google forms data bro?

@edumartim 3 ай бұрын

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

@enisten 5 ай бұрын

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

@bassycounter 2 ай бұрын

Equal equals equal in sequel is my new favorite tongue twister

@mr_riyajath 3 ай бұрын

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

@asheaven1st 4 ай бұрын

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

@vikingthedude 4 ай бұрын

Like when i was learning to drive 😔

@skblink 15 күн бұрын

This lecture is worth the same as an entire semester of two subjects in my university course

@denysolleik9896 14 күн бұрын

Actually it's completely worthless. You can just have a chatgpt with things you don't understand. I've seen this guy before. He's using way too many words to explain the basics. He's just wasting everyones time trying to promote how prestegious universities are, but everyone is capable of learning. Some people, those who go to a university, need a little pushing. As if it's a mental illness.

@bonadoni762 11 күн бұрын

@@denysolleik9896 Not everyone is as smart as you, brother. I myself am not a fan of learning through ChatGPT or any other AI because it easier for me to learn things through a person's explanation.

@Swegrid 2 күн бұрын

@@denysolleik9896 Get laid dude

@0001ssss 4 ай бұрын

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

@tingdongliao7036 Ай бұрын

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

@user-yt7je4mi6l 3 ай бұрын

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!

@yavor05 2 ай бұрын

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.

@activationfunction 5 ай бұрын

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).

@youMatterItDoesGetBetter 4 ай бұрын

Especially when you get real-world experience with legacy SQL systems. Databases are a f*cking nightmare to work through. It's all archaic non-sense.

@Blackfeet 2 ай бұрын

Yeet.

@youMatterItDoesGetBetter 2 ай бұрын

You should see the DB2 Database at my work. It's a foreign language with SQL sprinkled in.

@zeeshan.safdar 4 ай бұрын

the best intro of CS50

@Fyuture 13 күн бұрын

I am not even programming, but I like to get inspired by his energy and knowledge. Thanks for the upload and thanks to David J. Malan.

@nicholaslydon7089 Ай бұрын

fantastic lecture as always, thank you!

@user-qm2lc7fr9w 4 ай бұрын

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

@sefonefinpsefnpefn4191 3 ай бұрын

true

@user-sk6cn6sp6w Ай бұрын

@@sefonefinpsefnpefn4191 just drag the csv file from your local computer and drop it in the web terminal

@aniiko4138 Ай бұрын

Same lol

@martinepstein9826 23 күн бұрын

For DNA I just skipped the CSV module and did it with string methods. In particular .split(",")

@tanvirhasanmonir1627 3 ай бұрын

As always best lecture 💚

@user-gx6vg5qs8c 4 ай бұрын

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.

@charliebitmyfinger7124 3 ай бұрын

I don't think SQL and Python are even comparable.

@skugga7 3 ай бұрын

Python evidently has larger parameters for its usage,. However, in the context of data analysis, they can be comparable. @@charliebitmyfinger7124

@SnPnibba 3 ай бұрын

@@charliebitmyfinger7124 bros out here comparing general purpose full fledged programming language to a query language lmao

@user-ex5pg3oy9c Ай бұрын

@@SnPnibba I think HTML is the best programming language!

@SothearithKONGMrMuyKhmer 3 ай бұрын

❤❤ Great lecture as always.

@Aman-fz1ew 4 ай бұрын

great lecture ;

@user-mo9bc8lo6b 4 ай бұрын

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

@jamespawson6045 3 ай бұрын

Otimo irmao! Boa sorte pelo futuro! Segue assim :-)

@dannyisrael 2 ай бұрын

Niiiice! 🙌

@RashadPrince 2 ай бұрын

This is so awesome to hear. I've been listening to him for free on Apple podcast since 2009 🙏 This is even before he started doing free lectures for everyone and even you could download the videos off the Apple podcast because it was only for Harvard students at that time Now it's for everyone 🎉

@RuanSantos25 Ай бұрын

lol same! Vamo que vamo irmão 🤙

@MasterOfYourself Ай бұрын

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

@Polaris_Babylon 4 ай бұрын

An operating systems course by CS50 would be awesome

@k16e 3 ай бұрын

So thorough!

@ninedaniel246 4 ай бұрын

One of the best teachers EVER!!

@Mo-ip4mm 10 күн бұрын

This is so nice for beginners

@alyonkabenya 5 ай бұрын

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

@_kage_ 5 ай бұрын

@farhanarahman9375 4 ай бұрын

you mean the problem from week 6?

@sefonefinpsefnpefn4191 3 ай бұрын

@@farhanarahman9375yes

@jjfam777 2 ай бұрын

Exactly what I said 😭😭

@juli.72 2 ай бұрын

Carter covers that on week's 6 section. Don't skip them! The sections from each week are super useful.

@winnieroze 2 ай бұрын

There's a whole new intro now!!

@AungThiha-xj2bb 19 күн бұрын

Thank you sir

@chamaocharles Ай бұрын

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

@ad5772 Ай бұрын

I got a question, I wonder if you can help, I notice his terminal is so clean when running his programs, do you have an idea how he set it up like that ?

@chamaocharles Ай бұрын

@@ad5772 Hi! I never used, but I believe he is executing commands on the command line window on VS Code (press CTRL + J). I usually use Microsoft Studio Managment Studio to access usually SQL Server. He is using SQLite. Ask ChatGPT: "How to execute sql commands on sqlite through vs code terminal window". Here is the answer that it respond it: ``` You can use an extension to open, view, and query SQLite database files directly in **Visual Studio Code**. Here's how: 1. **Install the Extension**: - Click on **Extensions** in the left sidebar (you can also open the Extensions menu by pressing **Ctrl + Shift + X** on Windows or Linux, or **Command + Shift + X** on macOS). - Type **SQLite** and install the extension by **alexcvzz**. - Click on the **Install** button. 2. **Open an SQLite File**: - Once you've installed the extension, you can open an SQLite file by: - Right-clicking on the file in **Explorer**. - Clicking on **Open Database**. - The database will appear as a collapsed menu in the **Explorer** tab. - Click on **SQLite Explorer** to expand the database. - You can then click on a specific table and view its data or run a query. 3. **Running Queries**: - To run a query, create a **.sql** file (e.g., **001.sql**) and type your query. - For example, to select the first 10 rows from a table called **albums**, use: ```sql SELECT * FROM albums LIMIT 10; ``` - To run the query: - Press **Ctrl + Shift + P** on Windows and Linux (or **Command + Shift + P** on macOS). - Type **SQLite run query** and select **SQLite: Run Query**. - A new tab with the query results will open. 4. **Closing the Database**: - When you're done working with the database, you can close it by: - Pressing **Ctrl + Shift + P** on Windows and Linux (or **Command + Shift + P** on macOS). - Typing **SQLite run query** and clicking on **SQLite: Close Database**. Remember to expand the **SQLite Explorer** menu in the left sidebar after opening the database. Happy querying! 😊 For more details, you can refer to [this article](bobbyhadz.com/blog/vscode-view-query-sqlite).¹ Origem: conversa com o Bing, 09/05/2024 (1) How to open, view and query SQLite in VS Code | bobbyhadz. bobbyhadz.com/blog/vscode-view-query-sqlite. (2) Transact-SQL with Visual Studio Code. code.visualstudio.com/docs/languages/tsql. (3) How to run SQL query in visual Studio Code - Stack Overflow. stackoverflow.com/questions/72743136/how-to-run-sql-query-in-visual-studio-code. ```

@goagirl9767 5 ай бұрын

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

@Dante-fk4yi 5 ай бұрын

Your priority = zero

@goagirl9767 5 ай бұрын

Haha so true! You go become a tech tycoon!

@monkeysgone2heaven1 4 ай бұрын

reminded me of the start of a James Bond film

@cRaZy_CaTssss 3 ай бұрын

That great!

@bilalbeny4172 4 ай бұрын

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

@aleclippe6213 4 ай бұрын

GOAT ACTIVITIES

@meg.cabellera 3 ай бұрын

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

@meg.cabellera 3 ай бұрын

我好喜歡他在講解 join 語法時，從長到短來介紹三種語法篇幅 1:42:17 Executing joins with multiple tables in SQL 1:44:36 Different ways to join tables in SQL

@Nanagos 5 ай бұрын

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

@manifestasisanubari 5 ай бұрын

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

@davidjmalan 5 ай бұрын

Best to use cs50.dev ! Or on Windows, you can run `sqlite3 -table` !

@manifestasisanubari 5 ай бұрын

@@davidjmalan OMG didn't expect Dr. Malan to reply to this! I thought that maybe others watching would reply. Thank you David! It works 🥰🥰🥰

@MazineZ 4 ай бұрын

A simple solution is to set your mode to table by using the following command `.mode table`

@Russ_Paul 3 ай бұрын

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.

@tuanh9661 4 ай бұрын

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

@aniketbisht2823 2 ай бұрын

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

@sheldonj8204 11 күн бұрын

Guys, if your terminal left aligns the output (unlike in the video), type in sqlite3 prompt: .mode table

@antonioskokiantonis7051 4 ай бұрын

Without data there is no need for CS!

@muyanjassenyonga3398 2 ай бұрын

Incredible eloquence at such talking speed ?

@ankurkumar9757 4 ай бұрын

1:38:44 Batman swoops in

@aopen130 2 ай бұрын

oh david mentioned the bobby table :) at the end

@magicmaddox 2 ай бұрын

DAVID

@gathandi5531 5 ай бұрын

Where can i get Show.db file?

@branthebroken2323 3 ай бұрын

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

@ksport12 4 ай бұрын

we need some videos for networking

@pinakpaninath4321 4 ай бұрын

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

@ceal2137 9 сағат бұрын

I'm using Windows, installed sqlite3 on WSL using "sudo apt install sqlite3" some minor differences and I wonder why 1:56:26 when I typed sqlite3 favorites.db, it didn't ask for a confirmation if I want to create a new db 1:59:18 it didn't show alias n above the number 34

@shafialanower3820 5 ай бұрын

Wait everything getting uploaded all at once? Nice

@white-listochechki 21 күн бұрын

1:46:13

@aopen130 2 ай бұрын

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

@mrtn-py9zl 28 күн бұрын

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.

@sankar_vj_ 4 ай бұрын

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

@IRONMAN-to7yl 2 ай бұрын

1:31:29

@subhashisbhowmik7 4 ай бұрын

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

@kremznerg 5 ай бұрын

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

@davidjmalan 5 ай бұрын

Done!

@MatheusHenrique-hu1cx 5 ай бұрын

im trying to find this file

@bareerasoomro5723 5 ай бұрын

hy atvedx how ro i choose language

@muhammadnabeedhaider4926 17 күн бұрын

genre to show should be many to many as a genre can have multiple shows

@reanimator9 4 ай бұрын

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

@ad5772 Ай бұрын

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

@kennessu4642 5 күн бұрын

ctrl + l if im not wrong

@kennessu4642 5 күн бұрын

ctrl + L

@ramnaresh_8 4 ай бұрын

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

@jamespawson6045 3 ай бұрын

Code opens your file in the code editor

@johnbeal6592 3 ай бұрын

how can I get the shows.db file?

@bert6774 5 ай бұрын

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

@Polaris_Babylon 4 ай бұрын

That's great!

@angelfh3309 Ай бұрын

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

@robb557 3 ай бұрын

18:26

@abhisheknavgan6829 5 ай бұрын

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

@thestraightpath2249 5 ай бұрын

Because the matrix has attacked the minds of youth and made them into TIkTOK mind consuming dancing and shit videos and spending their life as a second class citizen as said by Top G !

@centipedekid9824 5 ай бұрын

@@thestraightpath2249Or most people just don't know about this because Harvard doesn't advertise these videos. What makes more sense a psyop like mk ultra or poor advertisement.

@kremznerg 5 ай бұрын

maybe because it was uploaded today?

@MooseGoWoof 5 ай бұрын

@@thestraightpath2249 Or maybe most people just watch through CS50 Video Player? That makes more sense then the matrix. I don't see much reason for people to go here anyways other than for timestamps.

@OswaldShoo 5 ай бұрын

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

@davidjmalan 5 ай бұрын

Are you using cs50.dev or some other programming environment? Do email a screenshot, too, to sysadmins@cs50.harvard.edu!

@OswaldShoo 5 ай бұрын

@davidjmalan already fixed, thanks for being in touch. The error was because I named the file as cs50.py I renamed it and worked 👍

@SaadAhmed-js5ew 5 ай бұрын

which year's recording is it ?

@PabitraPadhy 5 ай бұрын

It says in the title 2024

@bros1083 4 ай бұрын

Fall 2023

@bros1083 4 ай бұрын

@@PabitraPadhy Must've missed Halloween 2024

@domxgun 10 күн бұрын

If they all teached like that I could get behind 100k degrees

@konan91 5 ай бұрын

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

@enisten 5 ай бұрын

Yeah, the same thing happened to me. I was watching the old one on SQL when the new set of lectures was uploaded. And I agree with you on both of your points!

@k23raj2 3 ай бұрын

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

@rayedriasat8079 4 ай бұрын

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 ( ; )

@01juniorpen 10 күн бұрын

43:00

@01juniorpen 9 күн бұрын

ayyyye im done

@gilbertaguidi5998 3 ай бұрын

Porfavor Inclui disponibilize Legendas em português Brasil

@jamespawson6045 3 ай бұрын

Pode fazer voce mesmo e manda pra eles :-)

@xuenf 8 күн бұрын

The first 30 minutes would have been helpful for the last problem set dna

@Brittow 3 ай бұрын

Tough crowd

@CaliGhost 5 ай бұрын

@ahemenjehoshaphat452 5 ай бұрын

Still waiting for the updates on EdX😭

@vashisht1 4 ай бұрын

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

@jamespawson6045 3 ай бұрын

Yes, you are undervaluing yourself! Everyone feels like this with new material at some point. Just go back, watch it again, make some notes. Practice what he’a doing while you’re watching in the code editor they provide for free. You can also search out YT videos on the subject too. But don’t worry, we all feel like this at some point, it’s normal.