Pro tip for anyone who needs to do any database administration: Start every destructive command with BEGIN TRANSACTION, and a ROLLBACK, then type your command in between. Change ROLLBACK to COMMIT once you're sure that your command is ok. Saved my butt a number of times at my job. Happy to be going back to the basics of CS.
Salam, everyone wishes someone would pick up the trash but they're not that person. Be that person when there isn't that person don't expect that person to show up, so you should be that person
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.
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).
Hata ahora he aprendido todas estas cosas en cursos de 6 meses, realmente fueron útiles para profundizar, pero estar viendo esto ahora sintetiza todo lo que he aprendido y le da una razón de ser, un algo real en lo que utilizarse.
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.
@@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.
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 ?
@@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. ```
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 🎉
regarding sql injection attacks, i imagine some enterprising young staff member deleting those extra characters, as it looks untidy, unknowing that it actually protects the database.
2:06:00 unfortunately there is a mistake here. placing the two queries in one transaction does not solve the race condition issue. there are several solutions, one of them is to just add (or delete) an entry to the Likes table and each time this happens trigger a function that updates the likes counter, which can be stored as a column of the Post table.
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
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.
Would it be better if IMDB had a table "genres" with a unique genre id and a genre (text) and then have a relationship table with foreign show ids and foreign genre ids?
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
at 32:08, doesn't the count contains "problems", so how david could get an answer for "Scratch"? if that was your question, well scratch was also a problem set..check out here 30:50, just after Hello worlds and Filter
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.
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 !
@@ItzBusinessbhaiOr 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.
@@ItzBusinessbhai 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.
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
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.
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
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!
Pro tip for anyone who needs to do any database administration: Start every destructive command with BEGIN TRANSACTION, and a ROLLBACK, then type your command in between. Change ROLLBACK to COMMIT once you're sure that your command is ok. Saved my butt a number of times at my job. Happy to be going back to the basics of CS.
Awesome tip! Thanks!
this professor is a teaching machine, way to go!!!!
Just beautiful. This was a significant improvement over last year's lecture and I really liked it. Thank you Prof. Malan!
I love how he teach..
I love how he explain..
Man.. Wish the world have a people like him on every life-aspect
Like when i was learning to drive 😔
Salam, everyone wishes someone would pick up the trash but they're not that person. Be that person when there isn't that person don't expect that person to show up, so you should be that person
Great lecture as always. Thanks CS50 and Prof. David Malan for offering this course to everyone free of cost.
Equal equals equal in sequel is my new favorite tongue twister
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.
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!
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.
I don't think SQL and Python are even comparable.
Python evidently has larger parameters for its usage,. However, in the context of data analysis, they can be comparable.
@@charliebitmyfinger7124
@@charliebitmyfinger7124 bros out here comparing general purpose full fledged programming language to a query language lmao
@@SnPnibba I think HTML is the best programming language!
@@user-ex5pg3oy9c😂😂😂
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).
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.
Yeet.
You should see the DB2 Database at my work. It's a foreign language with SQL sprinkled in.
Thank you, dr. Malan
People like you are really inspirational for CS world.
Great lecture Dr. Malan! Shout out to the supporting team behind too for breaking the educational barriers
I started learning to code with the CS50P series and now I am continuing with CS50X. I watch and learn a lot about coding. Thank you for all of it.
I am just like you! We have achieved a lot so far. Congratulations, my friend. See you in CS50W!
I finished school but it feels like Ive just started the real one.school of life.
Cs50 is ranked right at the top classes.
Honestly wish the first part of the lecture opening csv files was included in the last video. Really struggled with this in pset DNA
true
@@sefonefinpsefnpefn4191 just drag the csv file from your local computer and drop it in the web terminal
Same lol
For DNA I just skipped the CSV module and did it with string methods. In particular .split(",")
@@martinepstein9826 have you seen the TV show guy 'Smart Guy'
best tutorial ever, he is too good at teaching
the best intro of CS50
Hata ahora he aprendido todas estas cosas en cursos de 6 meses, realmente fueron útiles para profundizar, pero estar viendo esto ahora sintetiza todo lo que he aprendido y le da una razón de ser, un algo real en lo que utilizarse.
This lecture is worth the same as an entire semester of two subjects in my university course
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.
@@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.
@@denysolleik9896 Get laid dude
Then why you are here.
@@denysolleik9896 Why u here ?
getting the information from the first part of the lecture would be super useful for the DNA task 😄
fr
you mean the problem from week 6?
@@farhanarahman9375yes
Exactly what I said 😭😭
Carter covers that on week's 6 section. Don't skip them! The sections from each week are super useful.
i wish i'd like David Malan's content 100000000x🙃
Guys, if your terminal left aligns the output (unlike in the video), type in sqlite3 prompt: .mode table
This lecture was just INSANE; I mean, got lot of things to learn
An operating systems course by CS50 would be awesome
One of the best teachers EVER!!
I'm grateful that I can understand English, as it allows me to learn from some of the world's leading professors.
我好喜歡他在講解 join 語法時，從長到短來介紹三種語法篇幅
1:42:17 Executing joins with multiple tables in SQL
1:44:36 Different ways to join tables in SQL
Thank You, David for everything. I really liked the Milk analogy.
As always best lecture 💚
This wonderful vide has made me appreciate the pandas library.
Lol this stuff was complex
This is so nice for beginners
fantastic lecture as always, thank you!
❤❤ Great lecture as always.
Amazing professor! I was looking for vídeo tutorial to recommend to a friend. This one is perfect.
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 ?
@@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.
```
Learning from a Harvard Teacher from a brazilian favela for free.
Otimo irmao! Boa sorte pelo futuro! Segue assim :-)
Niiiice! 🙌
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 🎉
lol same!
Vamo que vamo irmão 🤙
Learning from a country that helped make your country a favela for free 🤔
regarding sql injection attacks, i imagine some enterprising young staff member deleting those extra characters, as it looks untidy,
unknowing that it actually protects the database.
The opening music is awesome! Came for the class but stayed to listen to the opening music on loop!
Your priority = zero
Haha so true! You go become a tech tycoon!
reminded me of the start of a James Bond film
There's a whole new intro now!!
First, i thought SQL is dry and boring. But these problems sets are so much fun. My favorites so far!
Incredible eloquence at such talking speed ?
great lecture ;
2:06:00 unfortunately there is a mistake here. placing the two queries in one transaction does not solve the race condition issue. there are several solutions, one of them is to just add (or delete) an entry to the Likes table and each time this happens trigger a function that updates the likes counter, which can be stored as a column of the Post table.
1:16:33 I think he meant to say "primary key"
Yes I guess
1:16:34 I think he meant to say "Primary key is the column that uniquely identifies your data", not "foreign key..."
Been a backend dev for about 2yrs now. My best advice is to pronounce SQL as `ES QUEUE ELLE` and NOT `SQEAL` or `SEA QUILL`
So thorough!
"Tough crowd" 💀
Thank you sir
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
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.
Would it be better if IMDB had a table "genres" with a unique genre id and a genre (text) and then have a relationship table with foreign show ids and foreign genre ids?
47:53 "Equal equals equality in the world of 'Sequal'." 😂
GOAT ACTIVITIES
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
Best to use cs50.dev ! Or on Windows, you can run `sqlite3 -table` !
@@davidjmalan OMG didn't expect Dr. Malan to reply to this! I thought that maybe others watching would reply. Thank you David! It works 🥰🥰🥰
A simple solution is to set your mode to table by using the following command `.mode table`
seems like first part of this lecture would have been useful last week…
The first 30 minutes would have been helpful for the last problem set dna
NGL, the intro reminds me of GOT intro. EPIC.
1:03:19 All of these sheets
at 32:08, doesn't the count contains "problems", so how david could get an answer for "Scratch"?
if that was your question, well scratch was also a problem set..check out here 30:50, just after Hello worlds and Filter
Without data there is no need for CS!
1:51:14 "So when you run create *INDEX*? (maybe?)
9:04 is wrong. You close a file by writing `file.close()`, not `close(file)`
my favorite joke related to SQL and database
XKCD 327
Little Bobby TABLE
21:50 something completely unrelated: the spelling of "favourite" is different from the US version "favorite"
thank you so much for this lecture, it was useful 🎯
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.
i wonder wh y there are only 2 comments in such a informative video?
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 !
@@ItzBusinessbhaiOr 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.
maybe because it was uploaded today?
@@ItzBusinessbhai 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.
DAVID
Hello, would love to know the music composer's name
can id use varchar? i merge two tables using varchar id but there is always no table content
If they all teached like that I could get behind 100k degrees
100k degree schools tend to have professors of this caliber yes.
how can i get the shows database so i can work on the same problem set?
we need some videos for networking
5-6 months SQL course in college < CS50 2 hours SQL Lecture
genre to show should be many to many as a genre can have multiple shows
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
ctrl + l if im not wrong
ctrl + L
oh david mentioned the bobby table :) at the end
I will be there.
what is the logic behind row 1, why all the entries has come in answer?, this is column value right
Hi Team, Can anyone teach about ETL concepts and how it works in real time project.
That great!
You are an argentinian and watching a lecture on computer science... suddenly a Messi picture shows up holding the world cup 🤩🤩🤩
Where can i find the data you use in the starting of the video?
first 30 minutes would of be nice to have it before past week 6 problem set...XDD
It would be great, if you could upload the favorites.csv file to the Lecture Notes!
Done!
im trying to find this file
50:26 "baby table, not a technical term" :-DD
Show 62614:Zeg 'ns Aaa 😂 That's an old Dutch serie which my parents always watches
That's great!
Where can i get Show.db file?
Sorry, where can I find those databases ?
Ummm this .csv + .DictReader explanation would have been a lot more useful BEFORE the Week 6 DNA problem set🙄
how can I get the shows.db file?
I'm getting this error: cs50 has no SQL attribute. Can someone help me
Are you using cs50.dev or some other programming environment? Do email a screenshot, too, to sysadmins@cs50.harvard.edu!
@davidjmalan already fixed, thanks for being in touch. The error was because I named the file as cs50.py I renamed it and worked 👍
50:30 "my little baby table" not a technical term 🤣😂 I wonder who is this genius that would think it's a technical term?!
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
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.
Wait everything getting uploaded all at once? Nice
education can be fun....haven't seen the home assignments yet.....let's do some web scraping😀
Intro music is 🔥🔥🔥 - ouch
what does code means in terminal when $ code favourites.csv is given in command line??
Code opens your file in the code editor
Porfavor Inclui disponibilize Legendas em português Brasil
Pode fazer voce mesmo e manda pra eles :-)
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
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!
1:44:24 stopped, indexes, rare condn., sql injections