CMU Database Group · 1:18:44 · https://www.youtube.com/watch?v=MzigBKf84aY
0:05 official members bounce bust off the Chrome realize it's real hell what we call Home official members bounce bust off the Chrome realize it's real hell where we from the inside one I Vision fat licks and M Clips the Chrome will make the average imitator do back flips you like a pistol put the best around the ISS Miss coming that's offici okay so uh today we're going to talk about SQL this will be the only time in the semester we really discuss like at the application Level um but again it'd be
0:40 I'd be feel like I'd be um doing you a disservice if you graduate from car melon taking a de class and never touched squel so we're going to cover SQL today but again as I said last class I'm assuming everyone already knows the basics of SQL uh and so we're going to focus on the more modern things you can do with it and that'll be related to homework one that's going to go out today so last class we talked about
1:00 the relational model on how great it is and why it's the it's the superior choice for any any database system and then we showed how relation algebra was the building blocks we would use to run queries and manipulate the database and then later on the semester we'll see how we design a system the basically the query engine the execution engine to execute those those relational operators
1:21 relational algebra and produce results for our for for applications okay so today's class we're not really going to touch on relational algebra but you'll see how the things we talked about last class like a projection and uh and and select operators wear cloes you know you'll see how they they could be the underpinning for relation algebra we'll go go more details to how you implement the the execution engine later in the semester
1:44 okay all right so some quick background history of SQL goes back into the 1970s and as I mentioned last class when Ted Cod wrote that seal paper on the relational model he did it was all mathematical he didn't find a programming language uh and so other people at IBM took that paper said hey this might be a good idea let's see if we can Implement A system that could run you know the relational model and run queries on top of it but then they had to design their own query language and as I mentioned last class the first version of a a relational query language that IBM invented was this thing called Square um and this came out of this work done in in the UK before system R which is a very uh influential early database system at IBM relational database system
2:27 there was another project in the UK called the the Peter Lee relational test vehicle which sounds like it's a prag rock band but it was an early implementation of the relational system so they implemented this thing called Square but as I said last class you couldn't actually write it you can't can't even write it today right it's like this vertical language you have to write things up and down top of each other right so this is the paper that defin square and again it's like weird notation so this didn't go anywhere
2:53 nobody actually implemented this but the guys that invented this uh Chamberlain and Boyce they went back said okay let's do let's make one actually for real now and that ended up being what we now know as SQL the first version of SQL was not SQL as we understand it today the first version was spelled out SQ L right and then when IBM stand uh when this IBM came out with db2 in the ' 80s we'll talk about later uh they said hey now we support SQL with SQ L somebody already had to copyright for that and they sued them so then they just shorten it down to SQL
3:27 right so the this first version uh will be more true to the relational model in that it didn't support Json because that didn't exist then didn't support a raise right all those things we'll see in the next slide came in later updates to to the to the spec so IBM built a system called system R again we'll cover that throughout the semester they that's where they invented two phase locking a lot of early stuff that that we're going to care about in this class but then they never commercialized system R because they are making so much money on IMS even until today IBM makes money on IMS because there's all these Banks and insurance companies the Social Security Administration for the federal government all these systems run on IMS
4:06 which they set up in the 70s and it's too expensive to get off of it so they just they still run it and IBM milks the maintenance fees um but then in in the early 80s late 70s they realized okay relational model is going to be going somewhere so IB put out a bunch of different systems system 38 didn't go anywhere SQL DS is still around but the big one is going to be db2 that came out 1983 and that's still around today and that's still a juggernaut uh in industry
4:31 and we'll see db2 stuff L later in the semester and as we go along also two throughout semester you'll you'll be able to see what makes ENT what I'll call an Enterprise database system like an oracle or db2 or a seal server what makes them better so to speak against like a postgress or my SQL because they're going to have way more advanced features um p is go but it doesn't do everything that like db2 can
4:53 do all right so again the the the first paper on SQL came out I think in 72 73 um and then in 1986 this was designated as an anti standard right the American national standards Institute and then it became a international standard in 1987
5:09 and this is where they they decided had they had to call it SQL as in structured query language and so SQL is not a dead language right even though it standardized in ' 86 there's been a bunch of new updates to the spec that have come out every so often and the last one actually came out last March in 2023 right and you kind of see here over the years the the various features that they've added and because this is their adding the changes in how people write applications and what features they would want in in in in a query language or databases right XML was the hot thing in the late 90s ear 2000s uh so they added support for XML Json was the hot thing you know with manga DB and 2010
5:43 early and so forth so they added Json support in 2023 they've added uh support for property graph queries so now you can you can do sort of if you know neo4j or Cipher like there's there's you now Traverse a graph in uh based on bu on top of tables all through SQL they also add support for multi-dimensional rays as far as they know at least as of 2024 the only system that supports property graph queries is Oracle like Oracle got their version of what they wanted and they worked with the standards body but they're the only system today that still Sports uh right now that supports property G queries so if you're building a new system today and you say you want to support SQL the bare minimum you need to support what we'll call sql92 that
6:24 has all your favorite things the selects the froms the the group eyes aggregations right the bare minimum you need to say my databas system support SQL it's it's pretty simplistic and so as I said before seagull is not a dead language although every year or every 10 years or so people keep proclaiming it's going to be dead it's going to get replaced Google just put out a paper this week I think yeah on they have a rewrite of of uh of SQL that you know that they're going to run internally that makes it look more like pandas right there's always always somebody saying they're going to replace eagle and it hasn't happened so you see a lot of like this like this guy saying
7:00 uh that you know natural query languages are going to replace SQL right this guy is saying he saw a demo someone using chat TBT to rewrite SQL right and that's all all going to go away or SQL is all going to die I assure you that SQL is not going to die in your lifetime I've made public statements about this it's it printed um so it's it was here before you're born it was here here when you die now this may not mean that everything you're going to always do is going to be writing in Sequel in fact I think using something like chat BT to get a first approximation of what a query should be is a good way to get started but you know you still have to
7:35 put the right controls in and understand you know is it actually producing the answer you want so in the same way that I think in the long term in the same way that you wouldn't write assembly now but there's people that still do it you need to know it SQL might be something like that but in the short term it's it's super important unless you think I'm also being HP or you know that I'm not practicing what I preach uh I've already started teaching my four-year-old bi biological daughter uh
8:00 Sequel and so this is this is her first sequel query she's only four her first SE query that she wrote uh earli this year now I realized me showing you like my child is annoying so I'm not going to do that but it it she needed some help and
8:17 uh there you go that's your first sequel query what do you think that's cool that's pretty exciting yeah yep good job your father loves you okay so all right so in the seal spec what do you get it's not just the select statements and insert update deletes you get sort of roughly categorizes three uh three groups of D types of queries there's the data manip manipulation language so these are going to be the selects the updates the inserts deletes all those
8:52 the the data definition language the ddls this would be like the create tables create indexes create views the things that allow you to create cont constructs or create entities within your database um and then there's something I'm not focus on too much with the data control language this is like AAL for seq so some systems allow you to specify who's allowed to even read read what table read a column even read an individual record in a row you think of they're like wear Clauses like can this person read anything from that from this date or something and then it'll it'll prevent you from reading stuff one thing we talked a little bit last class we want we'll see more of this today is that although the Rel the relational model is Bas on sets meaning there wasn't going to be duplicates because it's all math and it's easy to compute that SQL is going to be based on bags
9:36 the bag is an unordered Set uh where there could be duplicates right lists can have duplicates but they have defined order sets cannot have duplicates but they don't Define an order and the bags can have duplicates and and they have no order and so we'll see somea cases where like uh we could run the same query multiple times uh on the same database just maybe at different times a day when or when the system has reorganized things and we'll get back different results and From sql's perspective the DAV systems perspective it's still the correct
10:07 answer right if we cared about the order something we're have to add order by Clauses or other things to enforce the uh remove duplicates and enforce the order that we're going to want so we'll see that in some cases as we go
10:17 along all right so today's class again this is just be sort of Crash Course on the on on what I call Modern SQL uh this is like the SQL Plus+ the things you really need to do get SQL do manipulate the data and use data in the way uh to answer really complex questions so let's first talk about talk about aggregations with group eyes we'll talk about how deal with straight and ding uh straight string date and time operations and one of the things we're going to see with this is that although there is a SQL standard and the basics of SQL queries will be the same across different systems once we get into like dates and times it all falls apart right across all these different systems uh we look at Alpa control and redirection window functions nested queries lateral joins and comment table Expressions so again these bottom four here this is what I would call you know super important for
11:03 understanding how modern SQL works so for today's class we're going to use a really simple database modeling a university there's a table for students uh and there's there's a table of courses and then students take courses and they get grades right and we have really simple uh data with um a small number of rows and we'll use this as as our examples as we go
11:23 along all right so aggregation functions are a way to basically compute a single value that's derived from a bag of tupal or multiple tupal right so this is things you would expect like averages Min ma uh sums and counts right sometimes you can do standard deviations
11:41 uh uh geometric means and so forth like different systems will have different uh aggregation functions but these are considered the the core five some systems also let you define user defin Aggregates udas so there's some computation that you want to perform across Ts that isn't available in the SQL standard these basic uh Aggregates you can extend the databas system to support those things that was actually one of the main ideas of postgress from the 1980s what made post different other systems is this extensibility and we'll see how people have leveraged that over the years as we go along so here's a Rel
12:14 example right so we want to uh get the number of students that uh we have in our in our table where they have the uh their login ends with at CS so it's trying to find all the computer science students that are enrolled in classes so we see we have account function account aggregate here and and we're putting it over login this is actually meaningless
12:32 uh because you think what does it mean to count a login you're just you're just counting tupos right so you could replace this with a star or one it doesn't matter right it's still all the same and then you see here in the wear Clause we have login and then this like operator is is how we do wild cards in SQL you can do more complicated things like regular Expressions but the basic SQL syntax you say like and then for whatever reason the percent sign is is what is what a wild card is and if you want to match it'll match
12:59 uh I think it's one or more characters including none uh and an underscore will match only one right you can put other crazy things in the count function you know one plus one plus one again it doesn't mean anything the database Sy will just ignore that computation if they do it correctly and all these will produce the same
13:19 answer so say now we want to get the the number of students and their average GPA that have an at CS login right so you can put multiple Aggregates in here so we can do get the average GPA and then just count the number of students right and we'll get we'll get an answer like this right all pretty simple but now we do something more complicated say we want to get the the average GPA for all the students that are enrolled in each
13:44 course right so what do we do we we we we add the the course ID to our select output projection Clause is this correct no right what does this mean to say right what I'm trying to do is get the average GPA per course but then I'm having the the course ID along with it but again the average is being calculated across the entire all the records the datab doesn't know what you mean like that that you're trying to get a course ID out of this right so some systems uh will allow you to do this
14:26 yes the question is what is what is that like actually doing is looking for substrings it's looking for any any string that'll match uh a w card followed by the suffix at CS so if I just do like at CS I think it wants to do a whole
14:43 match operator sign makes it do a wild card all right so going back here right so this example here if I'm trying to get the the average GPA of a student in each course if I just put the course ID here it doesn't mean anything from sql's perspective
15:04 because it's it's collapsing all the the average function is collapsing all the tupal down to a single value then I'm asking for the course ID from all any any of those tupal right so you could do it uh the right way to do this if well this is the the course idea here is meaningless if you don't care what the actual value is SQL now allows you to put this any value in here but in without that this is technically going to be an error right because again it doesn't know what course ID you want and we can just pop
15:38 open the different database systems and see what happens so um so I'm going to have on on machine running back my office I have uh postgress my SQL SQL light duct DB SQL Server Oracle and click house all running and again these are all what you would call relational database systems that all support SQL if we take that query we had showed before
16:00 uh and we run it run it in postgress right postgress is going to complain because again I have that course ID in in in the in the the the projection Clause the output and it doesn't like it doesn't because it doesn't know how to compute the aggre on it doesn't know which you're going to give you but if I add that any value on it um key operator then I it'll produce some result right but again what does that mean to have
16:26 15445 in there like across all the the the the average GPA across all all the courses that's sort of meaningless um so if I can go now to my sequel right do the same thing my SQL gives me an answer right without the any
16:43 value is that correct I mean my SQL seems to think so um but so in my SQL my SQL is going to be one of the biggest offenders of like doing weird stuff or incorrect things and so they have different SQL modes so if I say I want follow the the an standard right then it'll actually throw by an air throw an error or I can tell that I want to be strict to follow the the SQL
17:07 spec uh and I can't be it's ansy I think that's one you want right it's basically saying follow the SQL spec and then it'll throw the same kind of errors that postgress does we can go to uh SQL
17:21 light we think SQL light's going to do it get he says yes gives an answer go to duct DB dub throws the same eror that Prest does and dub is actually super helpful so drb says hey if you want to do this add any value that's great output I like that uh let's go to
17:44 Oracle Oracle doesn't like that SQL command not properly ended that doesn't help and that's because Oracle doesn't like as right what does as do as is basically an alias so I'm saying enrolled but treat it as the name as as a the table with tables names e but if I get rid of that as right then it allows to work again oh the other student the other as right then it complains about the the the aggregation so let's go now to uh
18:16 click house or SQL Server SQL Server complains that's correct and then the click house click house uh also complaints right so a pretty basic thing and they're all producing uh pretty different results but it'll get worse all right so for this career what we really want is this group ey Clause because what we want is the what is the average GPA per per course based on the course ID so we add this group ey Clause down here then we can say group them by the the course ID now I can reference the course ID in my select output and then now I'll get the result that I want
18:57 right so basically what is it doing it's along our table of all all the the grades and it's just going to bucket them up based on this course ID and then for each of those is then then going to compute the average and produce the output
19:10 right so group I you can actually have multiple columns right you can do you do uh multi-dimensional aggregations um but basically anything that's not going to be uh any anything that's going to be aggregated sorry produced in the output has to be in the group ey Clause otherwise you have to add the any any value right
19:29 right so this won't work because I'm grouping by on course ID but I'm also now trying to reference name right it doesn't like that so I'd have to put the name name down at the bottom right you can also now do uh additional filtering on your uh on your aggregations through the having Clause it's like a wear clause for group eyes so say I want to say I want to get all the all the students enrolled in the class and where and I only care about the classes where the average GP is greater than 3.9 I can't can't reference
20:02 my output up here where it say take the average Al is to average GPA I can't reference it down here to addition additional filtering right because when the data system is actually running this it doesn't know what average GPA is when it's evaluated tuples because that's think of like the aggregation is is computed later in the query plan so it doesn't know how to filter tup at this point right so you can't do this all you do is just adding a having clause
20:28 but this doesn't always work either because some systems won't let you even reference the Alias output uh in the projection output in the having Clause like mys will allow this postr will not allow this what you really need to do which I don't care for but you basically have to say here's the computation I really want and then do the filtering based on that and again in theory the data system should be smart enough to say okay the average GPA you're Computing down here is the same as the average GPA up above I don't need to compute that multiple times
21:01 right uh in the sake of time we can skip that but like again it's it's it's it's different idioms for these different database systems and you basically it'll be think of this as like without the having Clause they get this this output and then the having Clause will do a
21:15 filtering so aggregation clear it's a way again to compute it's a way to derive new information from the data that you already have everyone people say I'm I'm a data scientist I'm a data analys uh analyst this is more or less what they're doing at a high level right you're deriving new new facts from the data you've already collected and allows you to get a better view of what's actually going on for larger
21:42 Trends all right so of course uh SQL is going to have strings um there'll be basic operations on strings you can do like uh quality checks less than greater than there's much other functions you can do to to manipulate your strings or varchars as they're called so in the SQL standard as defined in SQL 92 the string cases are sensitive me that the values of the string we actually care about whether they're lowercase or uppercase like capital A is not the same as lowercase a and then the way we're going to reference or to uh wrap or a constant string is through single quotation marks
22:17 but you can see from the list here everybody does something slightly different postest is pretty good it's following the standard so they going be case sensitive and only allow for single quotes my SE be case insensitive and allow for single and double and you SQL light allows for both as well right so what does that mean so if say I want to check to see whether the the name Tupac is equivalent to you know mixed case Tupac I can cast both of them to Upper
22:41 then do my comparison in the case of my SQL it doesn't care right so let's go back here right so say in my Prest table right select star from student where name equals Tac that'll do an exact match right but if I change it to be Tupac like
23:06 that right doesn't match that's expected right that sounds good um but if we go over to my SQL it finds it right do you even lowercase like or whatever you want like that right because it ignores cases and again this is what their strict mode turned on um I can also use double
23:32 quotes oh ah did that correctly so if I turn it back to uh traditional by default yeah it it'll handle that but again you put it you put it in the strict anti mode uh complaints so with the double quotes it's basically saying it's a way to to escape the names of columns like I don't recommend this if your if your column name has a space in it you can use double quotes to then to then reference it um and then let's go over to SQL
24:06 light right it is K sensitive but I can still do double quotes oh that's new can't do that anymore either all right hey every year I upgrade fantastic good job Richard okay um see light is a phenomenal system sqlite is written by one dude down in North Carolina right uh he puts in the public domain there is a SQL light company but he put everything in his wife's name like his whole all this company is is owned by his wife uh he's he's brilliant uh and he's super duper nice okay so uh for string operators uh or do matching she she asked questions
24:51 right for whatever reason in SQL they're going to use the percent sign to match any substring including empty strings and then the underscore is going to be matching one character yes yeah because question is if you're using my SQL turn anti mode yes yes and uh as far as I know uh as far as I know in my SQL is the only one where you can change that SQL mode just because they're kind they're trying to write the
25:20 the the wrongs they've done in the past right uh which is good right uh in in Enterprise systems like an oracle I don't know you can do this in SQL server but you can actually specify what version of of the SQL you want from like system so say you're running on Oracle 22 you can tell it I'm going to give you Oracle 11 queries so the idea is you can still upgrade your system get newer versions of of Oracle but then still not not to Rite your applications as that their SQL spec changes right um and then post just updates every
25:52 year all right so as I said before there's also going be a bunch of string functions to do anything you can imagine again think of like string functions in Python to lower uh uppercase lowercase uh substring uh replacing strings all those things
26:06 um all all that is available you canat concatenating strings should be like a simple thing but this is where again where everyone does something different the SQL spec says you would use the the double bars like an or uh that's how you concatenate strings uh in SQL Server you use the plus right like con concatenate strings in Python um and then in my again they don't have they don't support either of these you have to use a explicit conat
26:37 function why is this way you know someone decided to do that way long ago and I we we have to live with it all right strings are pretty pretty pretty straightforward pretty easy to understand other than again when the concatenation stuff comes along but let's
26:54 now let's let's see how the problems we get when you do days and times should be a pretty simple thing to do right seems like kind of important databases want to keep track of dates and times of things
27:03 uh you know you would think the squel the SQL spanner says how to do it but everybody's going to do something wildly different so let's try to do something really simple let's just try to count the number of days from today which is August 28th to the beginning of the year
27:20 right seems like a pretty simple thing let's see let's see what happens so let's start with postgress so the first thing we say how do we get what today is right so the SQL spard specifies a Now function right that I can get back you know the a date and time right so let's now see if everybody has that so if I go to uh my
27:48 SQL they have it make sure I'm in an mode right they have Now sqlite does not have now uh Oracle does not have now this is uh SQL server for Microsoft uh does not have it or has something wait wait
28:22 wait oh oh all right we'll come back to that uh and then click Al has it okay well let's go back to my uh who is it who didn't have it or duct B Dr B should have it too I think doesn't have
28:40 it okay so the another way to get time is or the the current time is through the keyword or through this thing called current timestamp all right except in uh in postest it's not a function it's a keyword
28:59 right so let's go now to make sure I'm doing the right one my SQL they have the function they have the keyword SQL light doesn't have the function has the keyword duct DB oh sorry this is Oracle duct
29:24 DB doesn't have the function has the keyword or doesn't have the function or gives a different error date time interval Precision out of range we'll come back to that one let's try it with the keyword now we get something else another error yes
29:41 sorry question oh I don't even remember time zones uh this question is what time zone are they using the same yeah so there there is there is a date time type and then a date time with time zone type so you can say like in postc you can say I want to store a date time and I'm explicitly going to tell you what the time zone is do that always do that or you can say whatever the default is like on the system that's what's happening here all right so Oracle has given me two errors right it doesn't have select current time stamp with the uh with the function and then I got another error where it doesn't like the keyword because it's missing a from Clause so I think this is fixed in in Oracle 24 this I think I'm running Oracle 21 2 Oracle does not let you run queries without from Clauses so like going back to post
30:35 I can do select plus one plus one like that and it's like a calculate I can get any answer I want and I can do this in all the other systems in Oracle you can't run a query without a from Clause without a table so they give you this thing called The Duel um and it's like this built-in magic table that has nothing in it so that you can write
30:55 queries against nothing right so if I do select star from duel right I get some X thing back right I don't I don't know what that is so it's basically it's a way to like to to have one tupal to do a query without without a from Clause so again so now I can go select current time stamp from duel and I get my result but if I try the function
31:21 now right I get this date time interval Precision out of range what's that well when I ask chat TBT it says oh you actually can specify the Precision of the time stamp that you want right so just giv me a Precision by one one decimal place okay that's cool I didn't know you could do that let's go back to postgress and try that because uh they don't have the function um who had the function my SQL did right right can I put one there let me do it because they try to copy what uh or does but can I do it like
32:01 this I can right so I couldn't do the function without the uh without the argument but if I add a one there then I get my result but everyone's doing something different so keep going uh this one had the function right oh no this didn't like the function if I had one then it doesn't let me do it that's duct DB SQL Server alive nope all
32:25 right don't take this as kn secret server is a very good system don't I don't want to give any wrong impression um so click house click house has it the function does not have the keyword and it has the function does not have the function with arguments right okay so this just getting the time stamp getting the current day was was a big ordeal across all these different systems we're still trying to get the number of days since the beginning of the year so uh in
32:53 postgress um they have a way to get out the day of year um so so there's these things called extract functions so I can give given a date so here here I'm taking the string uh today's date I'm I'm casting it to to Casting the string date of a date into a date and this extract function says give me out some part of this date so I can get out for example like the day and I'll get today's the 28th so gets out the date but I can also get out the day of the
33:21 year right and to get 241 which is not exactly what we want because it's it's it's counting January 1st we're trying to count from the number of dates January 1st so what you can do is um just cast the beginning the current
33:38 date and the beginning of the year as dates and to subtract them and you get 240 that's correct right so here's an ADM post that I do like where they they they bu of systems this cast function this you calling this date function could be a bit cumbersome they have this nice syntax where you can put a colon after anything and then it'll cast it for you uh only postcast can do this and duck DB do can do it Dr B does it because they took pogress grammar right so then I I do that and get get the right
34:11 result all right so let's try to do this now in um in uh my SQL I get this what is that so uh I for for several years I do this demo I'm like I say I have no idea what this number is one year somebody in the in the YouTube comments I like hey this is what it is the first number seven is the current month subtracted by January one so 8 - 1 is 7 and then it's 28 - 1 is 28 - 1 is
34:51 27 right so that's weird um turns out uh uh so the first way I I figured I tried doing this and you can ask chat to do it now but you can convert the the current the dates to to Unix Tim stamps so that's the number of seconds since the Unix Epoch January 1st 1970 and then you subtract them multiply by 60 Minutes by 6 60 seconds 60 Minutes in 24 hours uh and you get 240 um
35:17 there's an easier way to do this there's a date diff function in SQL server or sorry in my SQL just to get that if you tried in postgress proc doesn't have it um we'll come back to SQL light uh this doesn't have a date diff so what are we in this is duct DB so duct DB I should be able to do the same thing I could do in postgress right I can cast it and subtract it that's cool um if I hop over to
35:47 Oracle right again I I complains because uh doesn't doesn't have this this doesn't know how to cast the string to a date uh Well turns out you have to specify the format that you want right so I I have you know four four year digits five five two month digits for two days then I get the answer that way
36:08 um chat gbd did that one for me SQL Server is let's see if it comes back yeah we might be back uh so SEO server the way you cast is through this explicit cast function right that looks cool so we get that um they also have a way to you can call convert but
36:34 again PR can't you know nobody else can do it that's a SQL Server thing um so they actually have the date di function right but you get negatives so we got to do the I got to put the what the current year sorry I got to put the first day
36:55 first the beginning of the year first and then subtract it do that so hope you're getting the impression that like as great a SQL is this is kind of annoying um and then click house we right we get today which is just it's an Alis for now subtracted by T casting the strings through two date to a date then we get
37:30 240 so again something should be really simple everybody's doing something widely different um and even the semantics some cases that of of these calculations are are different in other cases yes my SQL one the one using uni time step in which Step is the converted to dat i me shouldn't it be like seconds or
37:52 M his question is is this un is this Unix time stamp function what is that actually converting it to yeah I think it's seconds let's find out I guess we how we know right um that looks like seconds right and it's integer so it's not milliseconds so you there might be a way to get the Unix time St in milliseconds I don't know this feels like unified way to compter this feels what sorry feels like a uni way that can unify all the databases convert to un times he said said said this the way I'm doing this in uh first
38:37 conver that this this be the right way to do this across all different systems well let's find out right because they don't have uh what's it missing here may doesn't like round it's missing hold let's get rid of the round that might be fing up yeah to his point like this seems like something that should work everywhere
39:00 right uh I'm missing them dat y anyway uh but let's let's so let's see how we do this in SQL light so sqlite did not have a uh doesn't have I don't think it had the Unix time sand function or I couldn't get the work right um so the way I came up with it and then if you ask chat PT chat PT came up with the same thing you actually want to convert the the timestamps into the Julian calendar you know what the Julian calendar is it's the number of days since uh Julius Caesar's birthday right
39:46 you laugh but hold up this is how a lot of the banks in the 60s 70s and 80s calculated interest right because I think there's always 30 days in a month in the Julian calendar it's like it's always a fixed number whereas the gorian calendar it varies per month so they would compute interest based on the Julian calendar this is actually one of the things that postgress uh again going back to postgress being extensible uh as I said Stonebreaker was the guy that that that sort of built postgress but he built the first system Ingress and when when they went to go sell Ingress to the banks they were like great you have date types we we we need that to compute interest but you know but it was all Goring calendar and the banks are like where's the julan calendar and they're like we don't have that so they had to go back and modify the Ingress source code to add support for the Julian calendar as EXP explicit type in
40:29 postgress uh sorry to go back modify the Ingress source code in postgress they have these user defined types so now if you if you implement according to their API in C you can load in your own custom types and then then specify what the operator should be like plus minus and so forth in division based on that on that type right and that user defined types are widely used in in in systems today so the Julian calendar is you know
40:54 is important it was super important in Banks maybe less so today right so if you go to se a light we can take the current time stamp uh and get again the number of days since since Caesar's birthday and you get back the number you want um and then you just cast it
41:11 to to integer to make it round when I asked chat gbt it gave me syntax I've never I've never seen before uh where I can go say Jullian day but rather giving a time stamp I give it the string now
41:24 right and then I can then also convert a date from now uh based on the start of the year to get the beginning right and it computes the same result again I I want to harp on this too much but like this is a pretty basic thing but everyone's uh widely
41:41 different okay all right so um we only have half an hour left so I'm going to skip output Direction redirection because you don't need this for uh for the homework but basically you don't always have to have the output of a query come back to you to the client the thing that invoked the query like either through the terminal I'm showing here or your application code you can actually have the data system write it into uh another table
42:10 right so the into Clause you can say take whatever the output the select query is and put into this table here um in my SQL you can actually create the table and have it be populated uh with whatever the scheme is so it infers
42:22 through the the the the select query since it's declarative it knows what the types of the data you're accessing it knows how to populate or set up that that table on the other side um post you can actually create temporary tables like this by adding this into Clause right before the
42:36 from um I skip all this this basically you can do distincts order buys uh which should be pretty obvious by column names multiple ones um and then I I can get uh you do limits or get the first number of Records you want uh you know and do jumps and all Stu you ever see like pagination and web pages like you know the search results show you the first 10 you click next show you the next 10
42:59 they're doing it through uh techniques like this and then you can handle ties and other things okay so let's get now to the good stuff window functions so I don't I forget when window functions were added they're probably in the last 20 years or so um but it's like an aggregation but where instead of an aggregation where it collapses all your tupal down to a single scalar value the idea here of a window function is that you can compute an aggregation in a rolling manner still retain the output of the tul that you're scanning or in in in your query result
43:34 but then you can update that aggregate result as you go along right you want to compute like a ranked a ranking of of of Records or a uh a run a moving average or running total uh even a row number uh you could use a window function so the way to think about is like the the function name for the window function will be like an aggregation or a special one like rank a row number that's specific to window functions and then you can specify how you want to slice up the data or group it or even sort it to produce the result right you can think of the Over clause as as as like a group ey and this is super common when you're doing time Z analysis when you want to like again computer moving average of a stock price or something like
44:13 that so you can do all the aggregation functions that we talked about before mid Max uh average and some some count but then the special ones are going to be row number and rank now row number is a weird one because as I said sqls based on bags there isn't an order but obviously sometimes you want an order right you want to know the position of a record of a tupal in your output and row number will give you that rank is similar but it's like the ordering it's the position within the sort order so in this case here I can do a select star from the roll table uh where I'm going to get all all the values or all the attributes for for each record but then I'm going have this window function that says what's the row number like where does it appear right and you you would get output like this where they have this column at the end that gives you the row number just 1 2 3 4
44:59 5 so this Over clause it's going to specify how we want to group these TOS together um and instead of calling it Group by they call it Partition by but the idea is basically the same so now here I can say give me all the the records in the roll table um and I want the course ID and the student ID but I want to partition them by the course ID and I want the row number to the idea is like what what what position are you in in each
45:25 course right and you would get a result like this and now you can see the row number will basically resets uh every time it switches into a new new Partition or new bucket you can put order by Clauses in in the over uh over output right and this basically it's it's way to sort the results then compute the the the window function you want over the over the sorted
45:53 data so an example like this we want to find the student with the second highest grade for each course you couldn't do this without a window function because you don't know what it means there there's no notion of like what my position is in the course and how to get the second one because there is no like there's no concept of a row number within an output I'll get the output you know if I just do an order by cluse I can get back to my client to my terminal
46:18 things in an order but within the SQL itself I have no way to say I'm the second one or the third one or the fourth one unless you do a window function so this in the Over clause here we're going to group by the course ID
46:30 then we're going to sort sort by grade and then now we're going to have our rank position it's going to be again where where do we where does a record exist in that uh within that uh that that partition or group and then now since ranking is going to give us our position now we can reference it in the wear Clause of the outer query these are nested queries I'll see that in a few more slides but in this Outer Outer query here I can reference the the rank
46:55 that I that I comp to give me the second entry right the rank the offset of the rank is start at one yes sorry but theing from low to high question is uh the keyword ascending here uh is that low to high yes then ranking
47:14 number uh so you're going by it it should be descending yes yes you should be descending grade uh I forget whether it's a or b i the grades might be ABC here yeah so that might be why that's the case um but yes ascending or descending would change the order of
47:34 that let's check I'm pretty sure it was ABC so we'll do this in postgress to start with again because it's always the right way to it's always the best thing to start with so let's see the grades enrolled right they're they're letter
47:57 all right so I put my um I put my uh my query in here and again so I'm going to get the in the inner query here I'm going to get the the select star get all the records and then compute the rank of them within the course order um so if I maybe if I do this I'll remove the outer query so I won't sort
48:16 by um I won't sort by the or I won't filter by the ranking and I'll just run the query so here's the Here's the the yep like that so here this this is the raw output without that filtering so I'm going to I'm grouping by the course ID
48:40 and the Partition by and then uh then they're they're sorted by their grades and the rank is going to be their position in the grade and you see in this case here two people got A's in 721 so they both have the rank of one and then the next the next record starts at three instead of two right there is no two in this case
48:58 here so let's try that in all our different database systems see what happens this is so in in the early days of this course when I give this demo um a lot of these didn't support it so this doesn't have
49:15 it yep my SQL doesn't like it SQL light should do it yep s light can do it duck DB should do it yep or probably not nope um oh cuz the a another as yep yep still doesn't like it all right uh SQL Server should do
49:51 it yep and then click house should do it yep click house can do it good so all of them but my SQL can do this and Oracle all right so this this this example here was of a nested query so you have this inner query here and the outer query so nested queries are another uh super important aspect of in SQL and it's going to allow us to do really complicated things and so a nested query can basically appear anywhere inside of of a select statement
50:22 right so you can put it in the from Clause so so we refer to is the the calling query the the outermost quer is considered the outer query and then whatever's inside of that would be called the inner query but again an inner query can have its own inner queries you can Nest these things arbitrarily deep but you can put it in the uh in the the select output you can put it be in the from calls as if it's like a temp table you're referencing you can even do weird stuff like put it in the order by Clause right this is meaningless right this is like getting the max student ID as a number and then you're sorting things by that number
50:54 right you can do sort by one plus one it it doesn't mean anything because they all they all GNA have the same value right but again you can you can put it anywhere and we'll see later on when we talk about CR optimization uh that this is nested queries especially correlate Nest queries are going be the things that that breaks chokes most database systems uh because the dumbest way to execute these queries is just for every single tup in the outer quer outer query
51:19 rerun the inner query right and what you really want to do is rewrite it to be a join if I if I look at I'm doing here like I say this one here so I'm doing for every single student in the role table I'm doing another query to go get their name well I could just rewrite that to be a join on the enroll table and the student table and then I can do joins efficiently and that'll run fast
51:39 the dumbest thing to do would be for every single tupo in the enroll table rerun this this thing one by one my SQL used to do that um and there's some cases where the the different datab systems will end up doing that too because they can't uh they can't reason about what the query wants to do with n queries and ends up falling back to the slowest thing yes from
52:02 optim F doing like a temp table with the key wi then selecting from that temp table the question is is doing a nested query this being as fast as using a temp table with being a CTE we'll get that a few more slides is this going to be faster than a
52:19 CTE I don't know right because depends on what the query plan is right at this point here can we just we're just trying to Define the SQL to produce the answer that we want the how good the query plan is going be left up to the the the optimizer in the DAT system so what I was saying is like uh the for not for any arbitrary subquery or n query most systems cannot handle them correctly there's only two systems can Umbra which is now commercialized as Cedar DB from the Germans um and then duck DB but the duct B copied what the Germans did right
52:54 I don't mean that disparately like they it's all in the papers they did it right and actually dctb couldn't do everything then our students send them patches to handle lateral joints correctly with NE query it's like so dctb is probably the best open source one that can do do handle this now and it's always going to try to rewrite it as a CTE or sorry as a as a join with CTE you're also going to try to rewrite them as joins as well but for today we don't care we don't
53:17 know all right so uh let's try to get the names of the students in 15445 and again this is going to be stupid because like you just rewrite this to join that the next query yeah yes this one don't
53:44 know partition or no no like the thing that you put the function into the query um like the my SQL oh the terminal yeah the terminal teral it looked like it was giving different uh answers for the ones that it ran
54:04 for why all right so statement is uh when we ran this one query we were getting different answers so let's double check that so uhin it and another one another one had like five and it only had ones and twos
54:27 yeah uh let's make sure we have all the right data so select star [Music] from so what's that uh one two three four five yeah I I may have an extra Tuple from earlier demo like Taylor Swift might have taken a class
54:49 um yeah I have an extra tual um yeah uh there's I can show demo next time there's with Nest at queries you can get weird evaluation orders of things like the if you have a nest query that references like an aggregation when does it compute the aggregation before they get the tupos get processed or not like I I'll show demo next time
55:14 like you can get wrong different answers um let me come back to that I I think I'm one where you show there's definitely rounding issues as well uh we can see those um yeah so let me come back to that and I I'll show other
55:31 examples okay sorry okay so uh right we covered this covered this n queries so again this example is kind of stupid like we're trying to get the the the name of students that are that are taking 15455 we could rewrite this as a join but say for Simplicity we're going to write this as as a nest query and then we could use this in Clause to basically say this in operator says give me and match any two from the outer from the outer query from the student table
56:13 if it exists in the enroll table for the uh you know for the um you know for 154 4 445 and so what's happening here is that now the scope of this of the student ID the Sid field or attribute is going to depend where we are in the nesting of of the query so in this inner query here the student ID is going to be from the enroll table and then in the outer outer query the student ID is obviously from the uh from the outer table yes speaking of efficiency is same as join this question is is it the same as join let's find
56:49 out so in SQL you can actually ask the database server to give you the query plan so let's see what query was this it was the in Clause right so here's post grass right I can do equals any or that's equivalent basically to in so let's do
57:22 in right and put some inut so if I put the the keyword explain in front of a SLE query in post you'll get back the query plan right so this these are basically almost like the relation operators we talked about before this is how the D system is going to execute this query so it tells you visualize this as a tree it's going to do a squential scan on the student table a squential scan on the uh on the enroll table and here's the filter looking for 15445 and then it's going to do a nest Loop join to to compute the result think of that is like a two four Loops for every two on the student table check to see whether there's a match in the uh in the the
58:03 enroll table so we can see what other datab systems do for this as well so go to my SQL right and you get some something like this that's not really useful right and it has a warning so let's see what that warning
58:18 is uh complains about something uh it look looks like it told me that it rewrote it as a join uh so to get the full plan you got to do explain format equals tree y there we go um so in this here G here it tells you is doing Nesta four just before but actually now it's going to do a uh index look up on the primary key on on student ID so for every single tupal in the roll table it's then going to probe the student table on its index to get a result so it's a nested nested
58:59 index nested index or index nested Nest nested Loop join um so let's go to seite let's do the same thing let's SE light can actually run this query um sorry these different terminals like sometimes like like you can move faster with with control key sometimes you can't so s light will execute it
59:29 um I can't put put explain I get this what's this right so way seal light is actually implemented is they convert the query plan into their own op codes if you know like the jvm right and they have basically a virtual machine inside of the database system then interprets their own programs right it's genius
59:57 right it's now he didn't invent this idea right actually I we'll say this throughout through the enti semester a lot of things that seem new IBM did in system art in the 1970s IBM used to take your query plan and convert it into assembly and then they would run instead of like interpreting the tree and walking the tree they just run that that assembly code so fig almost like it it it it would generate a code that be hard coded for your query and to run that super efficiently so s is basically doing the same thing and this allows him
1:00:26 when I say him Richard it allows the sqlite guy to ensure that like no matter what weird Hardware sqlite is running on because it runs on almost everything right it runs on all the airplanes uh it runs on satellites in space right uh it's on everyone's phone that like all he needs to do is change the the make sure that the VM works and that no matter what what you know what query program he gets it can run anywhere so if you need to get the full plan uh I think we got to go
1:00:54 explain query [Music] plan yeah then then we get the tree right I don't know how to do this in Oracle we could ask J WBT um but in in in duct DB I I'll explain and it'll actually give me a
1:01:15 nice every year you guys go oh amazing right it's it's just Unicode right it's not it's not not anything that it's not mind-blowing um Oracle I don't think you can do this yeah I we have to ask jbt
1:01:37 um and then this is Click house right the merge tree like this is again a good example of the the the data Independence trees can be organized in different data structures so this is telling me I'm reading from one the merge tree data structure for my table representation but no matter what the SQL is it it's it's I don't have to reite the SQL if I change change the syntax and then for SQL Server I don't think it's going to work
1:02:06 either yeah um so here let's go ask chat WBT I mean this is the way of the world right why wouldn't you do this um how do I run wait what's that all right hey what oh CU my search all right look
1:02:34 um all right uh all right explain plan oh this oh I hate this yeah I yeah so if I call explain plan it's going to this is super annoying so in all the other servers I call explain plan um and it it would give me the
1:02:59 output was it four right explained so [Laughter] then yep so then I got to go back and I go select star from plane plan table yeah so it's it's in there trust me right all right uh I forget what was the point
1:03:34 um what was the question is same as join yeah it's it's gonna get Rewritten as a join yes yes if they do it correctly not always um all right uh so again these are different ways to to access tupal uh in N queries so you can say match all where I don't care all the T have to set my sub satisfy my subquery any means at least one n is equivalent to equals any like I I can do like equals this less than that any and exist just saying give me back any to but I don't care what it actually
1:04:08 is so if now we get the names of the students in 1545 I could do in I can do equals any right and and they're all it's all equivalent so let's now do something more complicated let's try to say find the student record with the highest ID that is enrolled at least one course
1:04:24 again you wouldn't be able to do do this easily um in just regular SE without necess queries because you you need to know that something at least exist in in in another set of set of uh tupal right so this query won't work in SQL 92 uh with strict mode because I can't reference
1:04:43 um I can't have this aggregate here and reference name uh without doing a group by if I now if I do a group I on Name That would be sort of meaningless it doesn't give me what I want right so the way to sort of these n queries is you sort of at least conceptually how I do it is you start with sort of the outer thing of what I want like I want the the student ID and the name for the student table and then I have some wear Clause that says that's going to I just write in English what I actually want the answer to be so I want to know that the student is enrolled uh and has the highest student ID and then now I could write that what that inner query is going to be and then now I just need to figure out how do I match the from the the record from the outer table from what the inner table is going to compute for me right so in this case here oh
1:05:26 sorry uh it's missing the equals any here um but again it's just a way to to take T from the output for the outer table outer query and match it to things on the inner query uh in the sake of time to skip this but this is basically like just you know doing further examples of this I want to get to lateral joints because you need that for the um need to know this for the homework so the when you have nested queries
1:05:55 you C the inner query can reference the outer query tupal but the outer query can't reference the inner query tupal right because it's running on the inside until it's materialized the outside uh you can't do anything with it and if I have now multiple nested queries I can't have them sort of look into each other to figure out what what they actually have right I have have this sort of strict tree tree nesting so where lateral joints come or lateral joints come in is allow me to have one query reference data or two or attributes from the previous nested queries that appear before it in my query plan so say a really simple example here so I just want to have a I have a nest query
1:06:37 select one as X so just it's a single tubal with one attribute with the value one uh in some temp table T1 and then now I want to just add one to it in a next uh Nest query so if I add this lateral keyword now in my inner query here I can reference things that were computed from the queries that that that appeared
1:06:57 before and this seems kind of bizarre in the case of SQL as we talked about because you're not really supposed to have this ordering of how things should be executed but lateral is imposing upon that allows us to say we can we can this thing has to be computed for this other thing can can be computed and whether or not the data decides to run actually these in parallel if they can rewrite them or executes them in serial order it doesn't matter it's just from the the
1:07:21 from the computation of the answer that we want we know it has to be done in that order so we open up uh let's open up post again and see what it looks like so right so what am I doing here so I have select one as
1:07:45 X again this is going to make a a synthetic table a virtual table that has one t with one attribute X and then now I want to add one to it in in my as part of my output so if I had this lateral keyword
1:07:58 here right after the commum I say lateral and then I can then specify what computation I want but now I can reference T1 which is computed over here within this nested query and and get the answer that I want if I remove that lateral
1:08:15 keyword right it's going to complain because I'm trying to reference T1 in this other query here and it's not there and he actually nicely tells me I should be using lateral so I think I can do this in most of these
1:08:33 systems my SQL can do it SQL light can't do it Dr B should be able to do it yep Oracle nope oh the AES well we'll skip that um in in SQL Server they don't like it because they don't have the leral keyword they have cross apply which is basically the same thing and then I get the answer that I want but also what's sort of confusing
1:09:03 is I say as T1 then cross apply and then with my n query but I don't have a comma when I Define the lateral join in postest there was a comma and I think the SQL standard says there is a comma
1:09:16 so for whatever reason s uh SQL Server does it differently I think SQL Server came out with this first and then the SQL standard deviated from them but I might be wrong and then click house uh can't do
1:09:29 it okay so let's see more complicated example so say now I want I want to compute the the the number of students enrolled in each course and the average GPA for them then I want to sort them by the enrollment count in ascending order so I want to get all the the all the students in these course get their aage GPA and then rank those that listing by the the
1:09:53 by the number of students that enrolled in that class right to know like if the class has a lot of people that I'm ranked first or not so there's basically two SE subqueries we want then for each course we want to compute the number students and then also for each course you want to compute the average GPA of of the enrolled students from a b so you could write this as two laterals here and this first part right we're we can reference uh we're producing the output that we can then you know we're referencing up here in in the C so the outer inner query here is referencing what's coming out of the the outer queries from the slum calls here
1:10:25 and then the bottom query down here it's now going to reference the same thing but it also can reference the anything that was computed in the one before it right and then then we get our answer like this it's a way think a way to have like four Loops within uh within SQL that are executed in in order yes do we need you don't need it just showing how to do it
1:10:51 yeah all right so last thing is commentable expressions with the things she she brought up sometimes called CTE and think of this as like a way to specify a temp table within your query whose scope is only within that query that's that's invoking it uh that allow you to then reference it in other parts of the query so a really stupid example would be I can find a CTE where all it does is call Select one produces a single tupal with no name uh with no column names just the value of one and then now down below it in this select CLA here I can reference uh reference my CTE
1:11:26 as if it was a table and then get get any result I want out of it yes create table from the suby join them together this question is for lateral joints is it like creating the table Yeah like we l join like two sub cures
1:11:44 can I say it's kind of like I create a temporary table from the subur and then join so it's it's crazy statement is should I think of L joint is like creating a temp table and then I can then reference it and do joins in other other other parts of the query with lateral joins at a high level conceptual yes okay but the lateral joint is specifying that the the first you know this first part here has to get executed before this one does at least conceptually to produce my result okay but it could again the D syst could just rewrite it to be joins okay but you don't know you don't you don't care they doing the same thing when you say do like it depends
1:12:25 again like I don't want to get too much the weed of like how these Syms are impl this that comes later like does it produce the answer you want is what you really care about right now how it's actually BE Implement under the covers we'll cover later okay because like I said it's going to try to rewrite this joins but not always it can't always do
1:12:44 that and if you're curious to know what it is actually doing you just just call explain and see what it actually wants to do yes seem tables are doing things that are really similar toest statement is are temp tables doing the same things are very similar to nested tables yes but a temp table will will persist for the session so if I if I open up the terminal and I call create temp table I can put stuff in it and then I can run multiple queries and still reference it then when I close the session it gets blown away but like a a a CTE or a nest query it's a lifetime is only for that
1:13:17 query it code also cleaner why would we not temp his statement is it seems like temp tables make code actually cleaner why not it always use them why they why do you think they're always why do you think it's
1:13:34 cleaner uh his statement is because it's I think you saying like you define the you define the query or the temptable outside of the thing you're trying to invoke on it like a hel function it's like a helper function
1:13:48 um it depends on what you want to do right uh in some cases if it sad is is in some cases it may be better to you define that temp table you're right and then have multiple Cs and voke against against it because you're not materializing over and over again in other cases they maybe just more efficient to put everything tell the all at once what you want to do and you can try to figure out the best plan for it
1:14:14 depends all right quickly CTE which is a garly concept but we'll get through it quickly but it's not that difficult think it's like a temp table again that you can reference within the query so you can bind an alias of things so I can say the name of my CTE is is CTE name and then I can say the output of whatever is in my my inner inner queries here is going be column one column two
1:14:36 and I can reference that down below uh in postest I think you can actually give them the exact same name and it'll it'll still work uh my SQL doesn't let you do this but postest when I tried before let you do this don't do it
1:14:51 um right that that's a post like thing um so you go back to say find the student with the highest ID that's enrolled at least one course right I could say for in my CTE get the max student ID from U from the enroll table and join against
1:15:06 that and then it's going to get Rewritten as a join but conceptu I can think of like it's like the The Nest query think about instead of stting starting from the outer query and say this is the answer that I want then figure out how to do the inner query you're starting with the inner query first I want the max student ID from the roll table and then I write the outer ption of it it's just a different way of thinking about how how to solve solve the
1:15:30 problem okay so let me finish up uh we talk about a lot of these different differences there is again the SQL standard you can go buy it it's a lot of money uh if you're working at a company that's building a database they will buy it for you uh but we'll take it offline there's ways to get it for free um all right so SQL is so important SQL is the most important uh programming language right I think right now and again it's going to be here it was here before you're born it's be here after you die
1:16:00 this is a from a a survey from 20123 from i e basically saying what's the most what do people they ask programmers what is the most important query language or sorry programming language need to know sql's always at the top they always have that SCS Jeopardy I think when they have like the talent day and they pull the students what's the most important you know programming language and every year I say SQL and every year it doesn't rank uh trust me the SQL is way more important the standard ml or o camel
1:16:27 okay yeah I said that and then sort of what we saw today is a bunch of different tricks to try to do as much as the computation within a single SLE query rather than having to go back and forth between the application code of the terminal and the data server homework one is coming out today uh you're going to have to implement the uh I think five SQL queries in both sqlite and duct DB right the syntax will be mostly the same uh and what what'll happen is you'll you'll run SQL light you run in the duct DB one of them's going to be
1:16:55 faster I guess which one's be faster Dr DB why can we take a guess nobody knows great this is what this class will be about so we'll come back starting on what today Wednesday on Wednesday next week we'll start talking about what the internals by data system looks like and we'll understand why duct TB is going to be so much faster than sealite for those queries yes so are we allowed to have only one version for two of those can we write different verions
1:17:25 his question is uh for seite and duct DB do you have to write uh exact no does it not have to be exact same because the syntax like we saw syntax is not going to be the same but what I'm saying is that the variation to go from seite to duct DB like whether or not you start from duct DB to go to seite or seite to duct DB I recommend starting with duct DB um like it doesn't you know they're not going to be the same okay any questions all right so next class again we start with the internals of the system and we're not talk about the SQL ever again see you M I need something refreshing when I get finished manifesting to cold a whole B like Smith and Weston one court and my THS hip hop related write a rhyme in my pants
1:18:05 intoxicated lyrics are quicker with a simple Mo licker since on mer City Slicker play Waves a picker rhes I create rotate at a r to quick to duplicate feel a breeze at a skate mik at fah when I holding real tight then I'm in flight then we ignite but starts to boil I heat up the party you let my girl R me my mic down with oil Rec still turn with thir de burn for one man I heat up your brain give it a sun so just cool let the temperature rise to cool it all with s eyes
1:18:40 [Music]