CMU Database Systems - 02 Advanced SQL (Fall 2018)

[@Blure]

This channel is helping me a lot in my undergraduate final project research. Thank you for sharing your knowledge!

[@shangruyi5439]

I know it is CMU. Still best database course ever.

[@정동호-i1r]

Perfect Contents, Cool Intro Fabulous Outro

[@mantistoboggan537]

Welp, it seems that describing window/partition functions is just kind of hard to do in plain English. I'll try and hopefully save someone confusion: Say I have sales data by month from 2010 to 2014. So I have sales for Jan 2010, Feb 2010, ... , Jan 2011, ... etc. When you do GroupBy, you get unique values of your tuples. So if I group by month and for maximum sales, I get only one value of January, along with the maximum sale in any year of January. When you do a window, you're getting every tuple in your bag. So I'd get January, January, January, January, January because I have one for each year. So clearly, there's a very different use case between GroupBys and using windows. One common example where we might use windows is running totals. Suppose you want to make a chart that indicates the total sales over time. Notice that in this example, I obviously want my query to return a result for each month of each year (so that's my hint that GroupBy is not what I'm looking for). The select statement might look like this: SELECT Month, Year, SUM(Sales) OVER (ORDER BY Sales) Translating this to English might be something like: "I want the month, year, and total sales for each row, where for each value, look at the tuples in the window created by (Order By Sales)". In this context, we can think of the window as saying something like "Consider the tuples until this row". So for the Mar 2010 row, the "window" that the OVER clause looks at Jan | 2010 | 200 Feb | 2010 | 300 Mar | 2010 | 400 Then the select clause takes the sum of those to return (for that row) Mar | 2010 | 900

[@dionwang]

Thank you for this high quality course.

[@jianweizhang1020]

First time knowing that different database understand same SQL differently...

[@ankitsablok952]

I think for the query "Get the number of students and their average GPA that have @cs login" the output relation should have the value "3","3.25". I think the value for COUNT(sid) is wrong in the slide i.e 12. 14:46. Also what does it mean by "Like a Where clause for a Group By" at 22:18? What does "ORDER BY (1+1) mean though?" when you say that in 41:30?

[@Feronom]

is this mysql 5 or 8? 8 is released in april 2018 and this video is fall 2018. You are doing unjustice if showing mysql 5 version here

[@gihanchanuka]

How can I access the www.gradescope.com/courses/22771/ ? Is it possible for outside person to attempt for the home works?

[@赵家贝]

same question. How can I judge my submission?

[@abdelaziz2788]

SELECT sid, name FROM student WHERE => SELECT sid, name FROM student WHERE sid SELECT sid FROM enrolled is greater than every SELECT sid, name FROM student WHERE sid => ALL( SELECT sid FROM enrolled ) why this is right? that doesnt ensure the student is enrolled at any course.

[@vishnukirantaka]

Killer intro

[@dobroanjo]

The music was fly the class was dope

[@7s9n]

Thanks 💛

[@aditya.ishan27]

Thank you so much 🙌

[@VaLuE_FoRkED]

waaaait a second... you uploaded this during VLDB! you too cool for rio or something? :)

[@LetsBeHuman]

What is VLDB?

[@VaLuE_FoRkED]

@@LetsBeHuman It stands for Very Large Databases and is one of the two largest database conferences held every year (the other being SIGMOD)