02 - Advanced SQL (CMU Databases Systems / Fall 2019)

[@Lukas-wm8dy]

Hey Andy, awesome videos, really great that you upload your lectures to KZbin. 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

[@andypavlo]

This is super helpful. I will fix the slides for next year. Thanks! -- Andy

[@drevil7vs13]

@Jigao Luo I guess you should switch "=>" to ">="

[@azizmalik5224]

@@andypavlo Should the second argument to SUBSTRING function not start from 1? 26:46

[@marcoq7160]

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

[@luisponce3580]

Beast

[@lakshayasood2197]

Super 🙏

[@stabgan]

I love you

[@bernoulli9047]

To the top! Thank you very much.

[@hasan0770816268]

thanks

[@abhishes]

Thanks to CMU for posting a video of this course from every year.

[@stabgan]

I don't get why they don't put all latest videod

[@thesameidiot]

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

[@badscrew4023]

I'm working with databases since 26years and still managed to learn new things here :)

[@hasan0770816268]

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

[@rfhp1710]

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 ?

[@420_gunna]

Absolutely love this course, thank you Andy! Making COVID layoff much more exciting :)

[@markchen7905]

this is the biggessttt chadd ive ever had teaching a lecture

[@RobertBaskette]

UNION ALL is an optimization as UNION will check for duplicates and the query is structured such that no duplicates will ever be generated.

[@drevil7vs13]

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;

[@drevil7vs13]

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;

[@drevil7vs13]

PostgreSQL 9.3 - ERROR: column "avg_gpa" does not exist

[@marathiManus10]

@@drevil7vs13 You are right. Andy's logic works on MySQL and SQLite but errors out on PostgreSQL

[@AndersonSilva-dg4mg]

Thank you so much for your work. Very interesting and informative.

[@stabgan]

What is the answer to the duplicate question about recursion ? The pen ultimate question ?

[@gmanon1181]

Unbelievable, I never imagined sqlite was so popular.

[@navjotsingh2251]

It is used in many phones and embedded systems!

[@Mnkmnkmnk]

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?

[@Mnkmnkmnk]

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;

[@peijunwu7354]

24:00 String operations

[@abdelrhmanahmed1378]

any resource for how group by executed internally?

[@didi098710]

great thanks for posting these videos

[@安康-v9t]

what's the name of the music in end

[@alexnarayanstechandetc]

are the links failing for anyone else?

[@pfever]

27:35 small typo here SELECT * FROM student AS s WHERE UPPER(e.name) LIKE 'KAN%'; instead of e.name should use s.name :)

[@manibhushan8763]

Thank you Andy, awesome lecture.

[@wardenofthenorth-w5d]

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.

[@HarshKapadia]

According to me, the output will be unsorted without an order by clause. Haven't tried it out though.

[@AbhisarMohapatra]

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

[@ShadKhan]

projects and assignments are available here 15445.courses.cs.cmu.edu/fall2019/assignments.html

[@ar_rahman_90]

@@andypavlo Thanks Prof!

[@drawkcabesrever461]

did you implement it?

[@charles7483]

I want to become grandmaster of database system design

[@davidcmpeterson]

Am disappointed he didn't do this lecture from the bathtub 😞

[@againnotgood8980]

good lecture and Andy's appearance just remind me of Cameron Monaghan😆

[@technosoft2035]

thanks a lot that's amazing!

[@joshuabonet]

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

[@andypavlo]

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

[@joshuabonet]

@@andypavlo Excellent suggestion, thank you Professor.

[@khammassihoussemeddine1193]

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

[@galaxy_mosaic3587]

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. kzbin.info/www/bejne/pZa8c5edncZ8oMU

[@emilmouz2294]

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.

[@ke224-p7o]

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.