F2023 #02 - Modern SQL (CMU Intro to Database Systems)

15-445/645 Intro to Database Systems (Fall 2023)
Carnegie Mellon University

[exoneuromancer]

Wow the audio is great!
Fixed in 1 day.
Officially best intro to database systems on youtube now.
Andy you're the best 🎉

[MRKRRNZ]

Can’t thank you enough for sharing all this information for free.

[vm]

Thanks for listening to the comments and upgrading the mic 🙏

[pirogafk]

Best course, thank you to make it public

[siddhantprakash.]

my notes:
38:49 - get total no of days from today to this year beginning
50:04 - Find the student with 2nd highest grade in each course.
1:11:42 - Find student record with the highest 'id' that is enrolled in at least one course.

[potapow]

57:49 - we (devs in the company i work in) do stuff like that in Clickhouse instead of using joins because we believe that CH will load the whole 2nd table into memory even if we don't need all of them.

[hugolatendresse]

So why does the same query runs faster in DuckDB than in SQLite?

Is it any of those answers given by ChatGPT?

Columnar Storage Format:
DuckDB uses a columnar storage format, which is more efficient for analytical queries that require scanning large amounts of data. This format allows for better data compression and faster data retrieval, especially for operations like aggregations and joins.
SQLite uses a row-oriented storage format, which can be less efficient for these types of queries as it retrieves entire rows even if only a few columns are needed.

Vectorized Execution:
DuckDB employs a vectorized execution engine, which processes data in chunks (vectors) rather than row-by-row. This approach takes advantage of modern CPU architectures and allows for better CPU cache utilization and SIMD (Single Instruction, Multiple Data) optimizations.
SQLite processes data row-by-row, which can be slower for large datasets.

Parallel Processing:
DuckDB supports parallel query execution, allowing it to utilize multiple CPU cores to perform operations concurrently.
SQLite is designed for simplicity and portability, and while it can handle concurrent reads, its support for parallel query execution is limited.

Optimized Query Planning:
DuckDB includes advanced query optimization techniques that can generate more efficient execution plans for complex queries.
SQLite has a simpler query optimizer, which might not produce as efficient plans for certain types of queries.

Built-In Indexing and Compression:
DuckDB automatically applies various indexing and compression techniques to improve query performance without requiring explicit indexing from the user.
SQLite requires manual indexing, and its compression techniques are not as advanced as those in DuckDB.

[scootathan]

How do I learn enough c++ to follow along with this course. The lectures are so easy to follow and then the homework feels completely impossible

[EmiliaKaida]

Thanks for the series.
But does this series not talk about normal joins (left, right, inner joins) too? I think it's not in the previous 01 video.
It's a well-known concept, but as a refresher, might be helpful :)
Plus normalization too.

[olegpatraschku]

anyone know umbra's paper that Andy might be referring to when he mentions flattening subqueries/nested queries ?

[NikolaiKononov]

What console or app do you use for connecting and querying the database?

[armsofundertow]

This is how you do it in Oracle btw

SELECT TO_DATE ('2023-08-30', 'YYYY-MM-DD')
- TO_DATE ('2023-01-01', 'YYYY-MM-DD') AS days
FROM DUAL;

[ritikjangir]

how can I submit my attempt for the questions? (not a cmu student)

[NostraDavid]

To answer the RANK query question: you can't do a GROUP BY instead, because SQL is inconsistent doodoo.

[md.rahimuddin]

1 class represent entire the basic to advanced query.
In class there have practice 3 languages(Oracle, MySQL, PostgreSQL) and give assignment 2 (SQPlite, DuckDB).
what's the quality of a class and its assignment!!!!

[NostraDavid]

Oh man, when I thought I couldn't dislike the inconsistencies about SQL any more, I find a new example why it's a shitty language.

[mohameedsalah]

your accent is very quick and i dont understand your talk