Hey Andy, awesome videos, really great that you upload your lectures to TH-cam. I'm a big fan. You do have one mistake in this lecture though: At 52:51 you use the >= ALL (SUB_QUERY) operator to get the student record with the highest ID that is enrolled in at least one course. This only works in your case because all students in the student table are also in the enrolled table. However, if the student with the highest ID would not be enrolled in any course (and hence not be in the enrolled table), the query would actually return 2 student records, one for the student with the highest ID (who is not enrolled) and one record for the student with the second highest ID (who is enrolled, so the one - given the description of the task - would be the correct answer). Greetings from Germany PS: Counterexample Students table id name 2 Havoc 1 Prodigy Enrolled table id course grade 1 ADS B
0:52 Lecture start 1:18 Relational languages 3:17 SQL history 8:48 Relational languages 10:53 Today's agenda 11:29 Example database (mock university) Basics: 11:59 Aggregates 14:43 Multiple aggregates 15:06 Distinct aggregates 15:50 Output of other columns outside of an aggregate is undefined 17:14 Test above statement in Postgres, MySQL and SQLIte 19:42 GROUP BY 21:15 HAVING 24:00 String operations 25:45 LIKE (% for any substring, _ for any one character) 26:35 String functions (SUBSTRING, UPPER, etc) 27:35 String concatenation 29:11 Date/time operations 29:46 Get the # of days since the beginning of the year 35:29 The most widely deployed database system in the world 37:13 Output redirection (store query results in another table) 40:03 Output control: ORDER BY 41:47 Output control: LIMIT Advanced SQL: 43:28 Nested queries: IN, ANY, ALL, EXISTS (difficult to optimize) 48:01 Nested queries can appear anywhere, not only in WHERE clause 49:22 Nested queries: a more complicated example 53:40 Nested queries: one more example 55:32 Window functions 56:42 Window functions: ROW_NUMBER, RANK 57:48 Window functions: PARTITION BY 58:32 Window functions: ORDER BY 59:03 Window functions: example using RANK (also an example of a nested query in FROM clause) 1:05:25 CTEs: common table expressions WITH ... AS 1:05:50 A student question: window functions vs GROUP BY 1:08:28 Another question 1:09:50 Back to CTEs: alternative to nested queries and views 1:10:46 CTEs: binding output columns to names 1:11:18 CTEs: alternative to the query from 49:22 1:12:04 CTEs: recursion WITH RECURSIVE ... AS 1:14:11 CTEs: beware of infinite loops 1:15:00 CTEs: a student asks to clarify the recursive query 1:16:12 CTEs: a student asks what happens if you remove the RECURSIVE clause 1:16:45 CTEs: what happens if we remove "ALL" from the UNION ALL 1:17:22 CTEs: a student asks why don't we see duplicates with UNION ALL, Andy doesn't know the answer? 1:18:02 CTEs are actually very common 1:18:12 Strive to compute your answer as a single SQL statement
41:40 order by does not require attribute to appear in output 1:07:00 window func (rank) vs group by 1:19:12 cte's are common since they enable more optimization
Hey Andy, these are great videos. Thank you. However I must ask if there are advantages in writing everything into a single query. In industry, I find SQL code easier to read if I store intermediate queries into temporary views. Does this have any downsides ? Why the obsession with the single query ? Who hurt you ?
Andy, amazing course, thank you for your work. 22:34 I guess HAVING works before SELECT, so HAVING would not know about avg_gpa column. Maybe it is like that not for all RDBMS, I'm not sure. In SQL Server the following select query throws an error, while changing "having avg_gpa" to "having avg(gpa)" works fine: create table student_course ( sid int, cid int, gpa int ); insert into student_course values (1, 1, 2), (1, 2, 3), (2, 1, 4), (2, 2, 5); -- Msg 207, Level 16, State 1, Line 13 -- Invalid column name 'avg_gpa'. select cid, avg(gpa) avg_gpa from student_course group by cid having avg_gpa >= 4;
Does a recursive CTE generate all rows before the main query? What if we have the infinite recursive CTE temp table, but LIMIT rows on the main query? Does that work?
Just tested. It doesn't materialize all the rows before the main query. The below query works- WITH RECURSIVE source (counter) AS ( (SELECT 1) UNION ALL (SELECT counter + 1 FROM source) ) SELECT * FROM SOURCE LIMIT 1000;
In Parition by section, will partition by without order by return unsorted result? in the example, it seems the result returned is always sorted by cid.
Thank you so much. I am following everything you explain and love it. I would like to do assignments as along the course want to implement the system from scratch as you mentioned in first lecture.Can you please post the tasks and assignments.that would be too good
Hi Professor Pavlo, thank you for your great videos. I just have one comment, the video of the class is on top of some of the lines of code which makes it unreadable. Everything else is amazing
Why not just look at the slides? The links to the slides + notes for each lecture are in the video description. 15445.courses.cs.cmu.edu/fall2019/slides/02-advancedsql.pdf
some feedbacks the instructor have a very good level which is so motivating but i feel like he moves fast through some points that need more explanation => feel like u r focusing on finishing the material more than making students understand so it would be better to take more time in explaining the content is fruitfu
I would imagine bc the course has a textbook associated. so the instructor covers the important concepts (needed for homework and projects) and expects the students to go through the book for details. I think the book is covered in lecture 1 of the course. th-cam.com/video/oeYBdghaIjc/w-d-xo.html
Dude, we don't need to watch you, just to hear your voice. You are covering the powerpoint presentation in some slides... Besides that, good job , thank you.
i think education in a class like this is like fishing in Sahara desert. i dont want to waste my time fishing a big fish during entire my life. i think cmu or whatever college class are useless more than a text book.
Hey Andy, awesome videos, really great that you upload your lectures to TH-cam. I'm a big fan. You do have one mistake in this lecture though: At 52:51 you use the >= ALL (SUB_QUERY) operator to get the student record with the highest ID that is enrolled in at least one course. This only works in your case because all students in the student table are also in the enrolled table. However, if the student with the highest ID would not be enrolled in any course (and hence not be in the enrolled table), the query would actually return 2 student records, one for the student with the highest ID (who is not enrolled) and one record for the student with the second highest ID (who is enrolled, so the one - given the description of the task - would be the correct answer). Greetings from Germany
PS:
Counterexample
Students table
id name
2 Havoc
1 Prodigy
Enrolled table
id course grade
1 ADS B
This is super helpful. I will fix the slides for next year. Thanks!
-- Andy
@Jigao Luo I guess you should switch "=>" to ">="
@@andypavlo Should the second argument to SUBSTRING function not start from 1? 26:46
0:52 Lecture start
1:18 Relational languages
3:17 SQL history
8:48 Relational languages
10:53 Today's agenda
11:29 Example database (mock university)
Basics:
11:59 Aggregates
14:43 Multiple aggregates
15:06 Distinct aggregates
15:50 Output of other columns outside of an aggregate is undefined
17:14 Test above statement in Postgres, MySQL and SQLIte
19:42 GROUP BY
21:15 HAVING
24:00 String operations
25:45 LIKE (% for any substring, _ for any one character)
26:35 String functions (SUBSTRING, UPPER, etc)
27:35 String concatenation
29:11 Date/time operations
29:46 Get the # of days since the beginning of the year
35:29 The most widely deployed database system in the world
37:13 Output redirection (store query results in another table)
40:03 Output control: ORDER BY
41:47 Output control: LIMIT
Advanced SQL:
43:28 Nested queries: IN, ANY, ALL, EXISTS (difficult to optimize)
48:01 Nested queries can appear anywhere, not only in WHERE clause
49:22 Nested queries: a more complicated example
53:40 Nested queries: one more example
55:32 Window functions
56:42 Window functions: ROW_NUMBER, RANK
57:48 Window functions: PARTITION BY
58:32 Window functions: ORDER BY
59:03 Window functions: example using RANK (also an example of a nested query in FROM clause)
1:05:25 CTEs: common table expressions WITH ... AS
1:05:50 A student question: window functions vs GROUP BY
1:08:28 Another question
1:09:50 Back to CTEs: alternative to nested queries and views
1:10:46 CTEs: binding output columns to names
1:11:18 CTEs: alternative to the query from 49:22
1:12:04 CTEs: recursion WITH RECURSIVE ... AS
1:14:11 CTEs: beware of infinite loops
1:15:00 CTEs: a student asks to clarify the recursive query
1:16:12 CTEs: a student asks what happens if you remove the RECURSIVE clause
1:16:45 CTEs: what happens if we remove "ALL" from the UNION ALL
1:17:22 CTEs: a student asks why don't we see duplicates with UNION ALL, Andy doesn't know the answer?
1:18:02 CTEs are actually very common
1:18:12 Strive to compute your answer as a single SQL statement
Beast
Super 🙏
I love you
To the top! Thank you very much.
thanks
I'm working with databases since 26years and still managed to learn new things here :)
Thanks to CMU for posting a video of this course from every year.
I don't get why they don't put all latest videod
728 you get by subtracting the corresponding values in the date -- year=2018-2018=0; month=8-1=7; day=29-1=28, so the answer concatenated is 728
41:40 order by does not require attribute to appear in output
1:07:00 window func (rank) vs group by
1:19:12 cte's are common since they enable more optimization
Hey Andy, these are great videos. Thank you. However I must ask if there are advantages in writing everything into a single query. In industry, I find SQL code easier to read if I store intermediate queries into temporary views. Does this have any downsides ? Why the obsession with the single query ? Who hurt you ?
this is the biggessttt chadd ive ever had teaching a lecture
Andy, amazing course, thank you for your work.
22:34 I guess HAVING works before SELECT, so HAVING would not know about avg_gpa column.
Maybe it is like that not for all RDBMS, I'm not sure.
In SQL Server the following select query throws an error, while changing "having avg_gpa" to "having avg(gpa)" works fine:
create table student_course (
sid int,
cid int,
gpa int
);
insert into student_course values (1, 1, 2), (1, 2, 3), (2, 1, 4), (2, 2, 5);
-- Msg 207, Level 16, State 1, Line 13
-- Invalid column name 'avg_gpa'.
select cid, avg(gpa) avg_gpa
from student_course
group by cid
having avg_gpa >= 4;
sqlite, MySQL - no error.
Oracle, livesql.oracle.com/ - error ORA-00904: "AVG_GPA": invalid identifier
-- drop table student_course;
create table student_course (
sid int,
cid int,
gpa int
);
insert into student_course values (1, 1, 2);
insert into student_course values (1, 2, 3);
insert into student_course values (2, 1, 4);
insert into student_course values (2, 2, 5);
select cid, avg(gpa) avg_gpa
from student_course
group by cid
having avg_gpa >= 4;
PostgreSQL 9.3 - ERROR: column "avg_gpa" does not exist
@@drevil7vs13 You are right. Andy's logic works on MySQL and SQLite but errors out on PostgreSQL
Absolutely love this course, thank you Andy! Making COVID layoff much more exciting :)
UNION ALL is an optimization as UNION will check for duplicates and the query is structured such that no duplicates will ever be generated.
What is the answer to the duplicate question about recursion ? The pen ultimate question ?
Thank you so much for your work. Very interesting and informative.
Does a recursive CTE generate all rows before the main query?
What if we have the infinite recursive CTE temp table, but LIMIT rows on the main query? Does that work?
Just tested. It doesn't materialize all the rows before the main query. The below query works-
WITH RECURSIVE source (counter) AS ( (SELECT 1) UNION ALL (SELECT counter + 1 FROM source) ) SELECT * FROM SOURCE LIMIT 1000;
24:00 String operations
Unbelievable, I never imagined sqlite was so popular.
It is used in many phones and embedded systems!
great thanks for posting these videos
any resource for how group by executed internally?
what's the name of the music in end
In Parition by section, will partition by without order by return unsorted result? in the example, it seems the result returned is always sorted by cid.
According to me, the output will be unsorted without an order by clause. Haven't tried it out though.
27:35 small typo here SELECT * FROM student AS s WHERE UPPER(e.name) LIKE 'KAN%';
instead of e.name should use s.name :)
are the links failing for anyone else?
Thank you so much. I am following everything you explain and love it. I would like to do assignments as along the course want to implement the system from scratch as you mentioned in first lecture.Can you please post the tasks and assignments.that would be too good
projects and assignments are available here 15445.courses.cs.cmu.edu/fall2019/assignments.html
@@andypavlo Thanks Prof!
did you implement it?
Thank you Andy, awesome lecture.
I want to become grandmaster of database system design
Hi Professor Pavlo, thank you for your great videos. I just have one comment, the video of the class is on top of some of the lines of code which makes it unreadable. Everything else is amazing
Why not just look at the slides? The links to the slides + notes for each lecture are in the video description.
15445.courses.cs.cmu.edu/fall2019/slides/02-advancedsql.pdf
@@andypavlo Excellent suggestion, thank you Professor.
good lecture and Andy's appearance just remind me of Cameron Monaghan😆
Am disappointed he didn't do this lecture from the bathtub 😞
some feedbacks
the instructor have a very good level which is so motivating but i feel like he moves fast through some points that need more explanation => feel like u r focusing on finishing the material more than making students understand
so it would be better to take more time in explaining
the content is fruitfu
I would imagine bc the course has a textbook associated. so the instructor covers the important concepts (needed for homework and projects) and expects the students to go through the book for details. I think the book is covered in lecture 1 of the course. th-cam.com/video/oeYBdghaIjc/w-d-xo.html
thanks a lot that's amazing!
Dude, we don't need to watch you, just to hear your voice. You are covering the powerpoint presentation in some slides...
Besides that, good job , thank you.
i think education in a class like this is like fishing in Sahara desert. i dont want to waste my time fishing a big fish during entire my life. i think cmu or whatever college class are useless more than a text book.