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

[@exoneuromancer1672]

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.

[@pirogafk1]

Best course, thank you to make it public

[@vm7240]

Thanks for listening to the comments and upgrading the mic 🙏

[@andypavlo]

It's the same mic. I just need to move the receiver away from the USB-C hub and closer to me. I just forgot to do that for the first lecture.

[@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.

[@YeYe-u4u]

Thank you for sharing professor! 😄

[@Sub0x-x40]

this is like the best audio ever

[@potapow8684]

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.

[@hugolatendresse7617]

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.

[@СергейЗинченко-т2к]

its a good show, ty

[@tylerrongione6696]

this is f*cking awesome

[@millouwmills367]

BEEP

[@olegpatraschku3736]

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

[@andypavlo]

Unnesting Arbitrary Queries (Neumann et al. 2015) cs.emis.de/LNI/Proceedings/Proceedings241/383.pdf

[@NostraDavid2]

​@@andypavloTL;DR Codd was right, and the one feature that was being (originally) touted on why SQL was superior to Relational Algebra was actually an awful idea. Is that accurate?

[@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.

[@Paul-fn2wb]

Hi, this course is not about sql or data modeling. I think from the next lecture Andy will start talking about the internals of relational databases, i.e. how you could code your own DBMS. If you need to know how to use existing database systems, look for other courses. But still watch this one, it's well worth it.

[@EmiliaKaida]

@@Paul-fn2wb Really? In this video he did talk about sql :D Anyways, thanks for the info.

[@Paul-fn2wb]

@@EmiliaKaida btw,, he will tell us how to implement joins!

[@mephistotel87]

​@@EmiliaKaidaIt's just introduction.

[@proudbox5091]

who is kraska and what did she do?

[@NikolaiKononov]

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

[@NostraDavid2]

For Postgres you use psql.

[@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

[@Onedeag-qw3yc]

Project #1 is a C++ primer to get you ready for the rest of the course

[@armsofundertow98]

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;

[@ritikjangir718]

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

[@andypavlo]

15445.courses.cs.cmu.edu/fall2023/faq.html#q7

[@bagaichi5750]

why the shade at tim kraska tho?

[@NostraDavid2]

I guess because he mistreats students and backstabs friends? 😅 I don't know anything about any actual drama though. Maybe it's just tough love for a friend, who knows.

[@md.rahimuddin1738]

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!!!!

[@NostraDavid2]

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

[@NostraDavid2]

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.

[@rogue_neuron]

kraska is not a good guy

[@mohameedsalah9518]

your accent is very quick and i dont understand your talk