SQL: New Features and Changes

Doug Deb introduces new SQL features like group by all, order by all, function chaining, and more, enhancing SQL flexibility and ease of use.

### SQL U: Exploring DuckDB's Unique SQL Features with Alex from Modex
* 🦆 DuckDB is a **lightweight, in-memory analytical database** that is easy to install and widely used for **learning SQL**.
* 🐘 A key feature of DuckDB is the `GROUP BY ALL` clause, which **automatically groups all rows into a single group** without specifying individual columns.
* 🐍 The `duckdb-engine` Python package is a SQL Alchemy dialect that allows DuckDB to be used with SQL Alchemy, a popular Python **ORM**.
* 📊 Using the `pandas` library with Jupyter SQL magic commands can provide **faster data transmission** due to both DuckDB and pandas being written in **C/C++**.
* 📝 Google Colab is a convenient platform for **sharing and running SQL code**, making it ideal for **educational and collaborative purposes**.
* 🔒 Modex offers a shared DuckDB database in the cloud, allowing users to access open datasets by creating a free Modex account and using a **service token**.
* 📅 The SQL standard is maintained by ANSI/ISO, with the latest major version released in 2016, leading to **challenges and fragmentation in SQL implementations**.
* 🎓 SQL learners starting with DuckDB may find it natural to use its unique features, such as **`GROUP BY ALL`**, which are **not part of the standard SQL**.

### DougDB: 10 SQL Features to Make Your Life Easier
* 📦 DougDB extensions provide additional functionality like **connecting to HTTP, HTTPS**, and **managing credentials**.
* 🔮 "Everything is happening automatically, so you don't have to **install or load any Mod Deck specific extensions**."
* 🧠 DougDB's sample database contains multiple datasets available upon creating an account, making it **easy to play with data**.
* 🔢 "Order by all" orders by **every column in a row**, ascending by default, simplifying **query maintenance**.
* 🅰 Aliases in the select statement are not available in other clauses like **group by, having, or where**.
* 🔠 String slicing allows slicing strings based on position, making it **easier to manipulate text**.
* 🛠 Trailing commas are allowed anywhere you can put commas, **reducing errors and making queries more flexible**.
* 📈 Pivoting columns is useful for visually interpreting data, especially when each column represents a **time period**.

hello welcome everybody uh for our first uh quacken code uh live stream. Something new that we are trying, and the goal of uh the session is obviously to quack and to on a specific technical topic and then to code a little bit to have something uh pragmatic. And it's just before the Christmas holidays, so do pretty light uh today and talk a bit about SQL U and how the new features that Doug Deb bring to uh SQL also. So if you're new to uh Doug Deb, it's also a good session for you. Uh we'll be uh sharing directly uh some code that you can follow along uh during the live stream. Uh so please uh let us know uh where you are from if you're joining us live. Uh I'm based in Berlin so it's pretty late already 9 uh but let me know if you're taking breakfast or lunch. I mean you don't necessarily need to have a specific time zone but usually we take the breakfast in the morning and the lunch in the noon um so yeah Greece oh

so I see we have Belgium, uh, Finland. Do we have someone from Berlin? So nice to see a lot of right. I'm Bas Boulder, yeah, right, uh. So, uh, during the session, I always, uh, invite some, uh, guests. And would it be external guests or internal guests? And today, uh, we have an internal guest. So, someone also, uh, from mother deck. Uh, Alex, uh, Alex, welcome. How you doing? Hi, everybody, doing great, doing great. Uh, so Alex, what EXA can you do a bit, a quick intro, like what actually do you do at mother deck? Oh, sure. So, I'm a forward-deployed software engineer at mother deck, which is a made-up title. It's, uh, kind of ridiculous, but what does it mean? It's a little bit of everything. It's, uh, I'm kind of the first person to talk with our customers in a lot of cases. So, first time we're meeting them, learning about their requirements, their use cases, and kind brainstorming, dreaming with them. But then I'm also kind of the first line of defense on the Tactical troubleshooting in terms of

Maybe here's a code snippet to solve what you need. Maybe there's an integration that you need some help with. You know, working with a different ecosystem tool, or you know, bug hunting on our own platform. So, a lot of fun. Yeah, that's a lot. So, it's a bit more advanced than sales. It's something between sales engineer and a solution architect, but you're still doing sometimes the integration yourself. It's definitely a mix, and that's, I think, part of it. Is you kind of get to choose what you like, and I happen to like the ecosystem side, so I kind of picked up a few extra tasks there, but you know, the core of it is, yeah, the learning what the customers need, helping make sure we build what they need, and then helping them tactically as they, as they use. My and what's your initial story with, with Doug DB? Oh, sure. So, um, my background is industrial and systems engineering, and I spent nine years at Intel, and while I was there, was basically a part

of uh I'll call it Shadow it. So we complied with all the it regulations, but we did all the stuff that we did all the stuff that it didn't want to build, you know, like the kind of intermediate solution, the prototyping stuff that it's not the three-year plan, it's the, you know, one week plan. And as a part of that, we built a self-service analytics platform, and it was designed to connect all the hundreds of on-premise databases that Intel still runs on in all their factories, um, and the best way to combine all that data together, um, ended up being ducky B. So I found it way back in 2020, and I hammered it with a million crazy SQL queries, and it didn't make a sweat, you know, no problem. And then, um, it has since gone on a hockey stick curve, and I've been along for the ride, um, so found it just with the Google search, and then just started using it, really liked it, tweeted about it a lot, and um, then I got tied in, yeah.

you do like twer right. I I seen that. I do I enjoy it. it's it's fun. it's I feel like I don't know. I I wish it was man a little differently but I I like the people I get to talk with on there cool. um and um yeah. that's like the shadow it thing. I think we could talk for for an hour about this but I'm really. I'm really curious. do you think there like are there other companies that like have dedicate team come like that. you know it's. we never called ourselves that. I kind of learned that's what we were after I left. so uh. we weren't exactly up front about where the shadow it team. but uh. yeah. makes sense right. I've learned since then that there are a lot of places where you need a spectrum of solutions. you you know. if you're running. you know your your customer orders. you know the system that takes an order and submits an order is going to be built by your corporate it. and it's got to work 99.99% of the time.

uh your reporting tools um frequently are built by other teams. It can be a real spectrum of people building it themselves, people having a team that uh uses commercial software, or maybe an internal platform team that builds an internal platform. And we were kind of that internal platform team um model, yeah, now. But that makes sense. I mean, I've seen like some of them kind of like bootstrapping thing, especially where like with large corporate company, right, you need to kind of take some shortcuts. But okay, you were not like advertised as a shadow, shadow team, you know. And we follow the rules. We had a couple people that used to work in it on our team, so we had a couple ringers where we, you know, we weren't your typical Shadow it hacking stuff together. We were, I think, building something pretty neat. No, that's great. Yeah, I mean, I guess um and so we have um I have a small uh SQL quiz and Alex is not freaking out because he doesn't know any of those.

answer but it's also for you. For the audience, for a people live stream, um, it's I think there is interesting things to know, uh, about SQL. At least I'll learn it when I, when I did the quiz. Um, so first, do you know about which year when SQL was developed? Oh, I should know better than I do. Um, I'm go with roughly. I don't know. How about 77, 1977? Oh, that's, that's really close. Yeah, 1974. Yeah, okay. So 1974. And if you want to try, by the way, I, I have multiple question around SQL. So if you want to test your knowledge, please don't use chat DPT. We all know the answer at the end, right? Just to test your culture around SQL. Um, so yeah, uh, SQL was developed at first in 1974. And do you know which popular song was released that year? I'm GNA give you a hint. Uh, Sweden, H, Swedish band. I do not, oh, oh, the most popular song in that year. Oh man. Well, I mean, ABBA is a pretty good hint. Uh, they're

from the Swedish band yeah we're gonna go close what what what do I know from ABBA songs. I only know a few so we'll go with Dancing Queen by Abba. oh no that's W water water was was releas so now whenever you're going to hear that song you're going to say that's the year sequel was released. I'm not sure it's going to be a bang at you know a cocktail party but you know that's uh that's how to remember anyway uh and so do you know where would the L actually sequel oh I think I think I might be a big enough data personer to know that so what was this was this was this IBM system R doing this yeah yeah exactly yeah so IBM and the first database uh that they were using was uh cstr and do you know the first name of SQL so that's a first a fun one so before it was named squel before it was sequel at one point it was the word squel spelled out like s e q e l my

God, you're a mysterious book. I thought I could trap you. So yeah, SQL. So SQL, like spelled out as you said, like SQL movie, right? Um, so and it was standing for Structured English query language. And then they shorted out to SQL. SQL. And do you know what's the name of the standard version of SQL and who is maintaining it? Um, I know it's ANSI SQL. ANSI does like every three years or so, they put one out. Um, but I have no idea what ANSI stands for. Yeah, ANSI is a private nonprofit organization. Um, and you have also ISO. So which is, uh, they basically work together for those standards. So regarding this standard, do you know when was the last one released? The SQL standard. They did one in 23. I think it's got the new graph syntax and stuff. Yeah, yeah. And so actually before that, um, I mean, there was one in 19 as I can see, but mostly like the last big one.

was in 2016 so between 2016 and this year, we don't have that much. So it's pretty crazy, right? Because if you think in terms of uh of word uh timeline, right? Changed a lot, that's a lot, yeah. So just to give uh you an idea, uh Snowflake was created in was publicly launched in 2014. So yeah, they basically been, you know, living on certain standard for for quite some time. And that explained why also we have quite some divergence, maybe with the different uh data warehouse, because the current stand there hasn't moved a lot. What do you think about that? Yeah, my my quip about this is that it's it stands for structured query language, not standard query language. We definitely can't get everyone to agree and uh it's kind of been that way all along. Um, it's I think improving over time, people are starting to realize that this is this is not super fun. There's some new tools coming out to translate back and forth, but yeah, it's tough, yeah. And uh, so so if people want

read about I just put the in the comment section the blog about the new SQL standard for this here and as you mentioned there is some stuff around Jon which is interesting too right that's true I think they did finally formalize some of the Json stuff yeah yeah and it's funny because I guess like a lot of companies saw the need to be able to have more power in the SQL and you know for people ingesting unstructured or gent kind of data into their relational database but it's also interesting to see how you know does it how much it lag with like standards coming true right because a lot of databases have been supporting G for a couple of years so that's pretty neat yeah think the way that the standard folks do it is they wait for a couple people to build it and then they see how similar they are and then they kind of build a standard after the fact a little bit so

it's it doesn't really prevent the fragmentation, but at least you kind of learn what's the best way. So once it's finally standardized, it's really good. But it does mean that people do their own thing for a little while. Yeah, yeah, yeah, indeed. Um, and so coming back to my quiz, what else do I have? Yeah, I think, uh, I think we cover, um, a bit that around the intro. Uh, I hope, uh, everybody learned something new apart from, uh, waterl from ABA, right? Uh, but yeah, um, moving on, um, let's talk a bit about, uh, tooling and and set up, uh, around, uh, tdbn SQL. So what's, what's eily you're you're go to, uh, fing when you use, uh, dgb? Yeah, when I'm, when I'm implementing like a system with duct Tob, I love the, the python client. So that's my, my go-to if I want to have like a long running thing or or a really complicated workflow, that's my favorite. A lot of what I'm doing these days when I'm

helping folks out is trying to do the simplest thing possible. So these days, I use the CLI a lot because that's kind of the most core part of duct TB. It is the command line tool. So I usually run that. Either just I'll run it in a basic terminal, but I'll also run it in VS code so I can have a SQL file where I edit all my commands, and then I can just kick it off with a one-liner in the CLI. So at least I get some, you know, both of those. At least I get some syntax highlighting. But yeah, that's what I use. What when did you start to use like the VS code thing because I kind of like discovered this workflow when I started to work with duck DB in February. But I think you were using it already before. Oh, maybe not probably later than that. I think I was not a huge terminal user. You know, my previous company, I was a Windows person.

know command prompt is not as nice as the terminal, so I used it when it was required. But nowadays I'm on Mac, and so getting more and more used to that. You know, VS Code I have used for a couple of years, but I hadn't done as much SQL in there. I was mostly just using it for Python stuff, JavaScript stuff. Yeah, and let me share to the audience quickly what we are talking about with VS Code. This is something we showed in one of the videos. But you see my screen, right? Because I don't see you anymore. Yep, yep. So what we talk about is that basically when you... It can work with any terminal or any editor, but the goal is basically you have an SQL file, and then you open the VS Code CLI down here. And when it's there, then you can basically just L. What's cool with that is that you have formatting and syntax highlighting, and also GitHub Copilot. I mean, here I don't need my kid.

up copilot because I have my human copilot, right? I like it there. Uh, the goal is that you basically have all the to complet all the feature of of the of the terminal. And what I do, you see, I send commands uh down, and it's just a shortcut so you can setting uh do custom shortcuts in uh PS code, and then you can just uh send it out. And here, as you see, I'm just reading a public CSV, and that's it. I can I can work like that. So I I do like also work like that when it's a something that's want to easily repeat, uh, you know, a SQL file because I found like we're g to talk about notebook, but notebook has their Pro and cons for Learning and sharing. But yeah, I do like pure SQL, and it's also embraced the simple setup of like, you know, um, Doug DB, uh, Doug DB itself, right? So yeah, and I didn't even have that advance of a setup for me. I just I run one command of like read than the name of my SQL file.

it'll just kick them in one shot, but uh, but I'll be using, uh, can yeah you can select, you, you can select multi line and so on, everything, uh, works, yeah, yeah. Works, uh, pretty great, um, yeah. So, um, yeah, so that's, that's the one possibility we have. Also, people using D Behavior, I, I think you, you're using a bit D Behavior or not, yes, yep. I like d Beaver for if I'm doing a lot of work with like tables and I really want to do, you know, creating tables, removing them, and all that. It's much nicer to have that exploratory pain on the left hand side, um. I also do some work with postgress and dctb can read postgress, so it's nice to have like a, a postgress client right next to my, my dctb. So I do use D Beaver for that. It's kind of my, um, if, if I'm doing more with, with physical tables, I'll, I'll use DB, yeah. Oh, that's cool, uh, and then we have other stuff like Harin har. I'm not sure.

how to pronounce it. Uh, you know, the terminal editor for DDB. Um, probably should cover that in a specific video or live stream. We're pretty cool. So, and then we have notebooks, uh, which works also, you know, uh, great. And today we're gonna use to talk more about the specific SQL features of DDB, uh, Google Collab, uh, simply because it's super easy to share and to set up. You basically don't need, uh, uh, anything. Uh, have you been using for are there use case done sharing and teaching a little bit, um, I work part-time for Dub Labs as well in addition to Mother Duck. So I do, um, documentation for them, and Collab is a nice way to have, um, you know, live documentation where you'll actually write it out in a markdown file, but then, hey, by the way, if you want to go run it, here's a Collab link. So that's, yeah, probably how I got started with it. Um, we didn't use it internally until, um, there were, you know, Microsoft shop so.

so no, no Google Collab there, but uh I've been enjoying it on the open-source side. It's just great to share that link. Just hey, by the way, check it out. Yeah, yeah, no, definitely. And uh, so I'm going to share uh with people, folks, the Google uh Collab if you want to join uh directly. And then I'm going to share uh my screen, and we're going to dive into that. So, Alex, you wrote actually two blog posts around uh SQL feature from Doug B, right on the yeah, Doug B website. Um, and um, and I've seen so I went to those two blogs, and I've seen a lot of things that I wasn't aware I wasn't not like using it. So it's uh really interesting. We're GNA see that basically there is. So first, there is stuff that is was happening that I didn't realize there were happening because they were not basically not part of a standard, right. Um, and then uh, there is part, uh, there is just function that I was not aware uh, but it's kind of like

also challenging to kind of incorporate that in your cache no this one um sure yes yes those those function uh do do you have like in highlight like what's your your favorite function uh first going before going diving into the The Notebook specifically oh my my favorite um kind of duck Tob specific sequel thing is a um a group by all so instead of having to group by individual columns you can Group by just have it just tell it handle my group by automatically so nowadays I don't even worry about my group ey it's it's really nice that's yeah and so I think it's like it's really interesting because there is uh you could say for example someone starting there is a lot of people starting their SQL Journey with uh dgdb now you know even at University and so on because super easy to install um but what is fun is that you could say someone that learns you know SQL with Doug DB oh you know of course there is Gile that's totally normal you where you get where I'm going it's like no this

it's like there there is actually stuff um done for you, which is great um, but I think we we should really recognize the um the A4 that is sp there, because yeah, that me just seems uh so so natural, yeah, that one in particular. There is at least one other dat database company that has picked that up and implemented it, uh, they're named after a type of precipitation, um, but uh, they uh, they they have group a as well, because we want to make it a standard, so you know, you can't necessarily make it a standard with just one database doing it, but if there's a couple, I think we can get there, yeah, yeah, indeed, um. So we going to start, uh, basically to just install a couple of package, um. Can you tell me the difference between the dark DP inine? I'm I'm going to play dma so that physically everything is clear as for the audience, oh sure, oh yeah, actually yeah. Can you tell me the difference between the ddb engine python package and the dgdb python package you

bet uh duck DB engine is our SQL Alchemy dialect. So, SQL Alchemy is a Python ORM, or object-relational mapper. It's really good for, um, you know, using Python to determine your schemas instead of SQL if you prefer that. It's also good for migrations and it integrates well with a lot of other tools. So, we're using it here because it powers Jupy SQL, and that's how Jupy SQL connects, is with the SQL Alchemy driver. Uh, because SQL Alchemy supports so many databases, it makes it easier for, you know, things built on top to connect all different things. Um, so that's the advantage we're using it for here. Um, the creator of DuckDB, Elana, has said, Oh, I wish I could rename it, because it is a tough name that's not exactly the standard SQL Alchemy. But, um, we use it, we love it, we really appreciate the work that she put into building it. Nice. Um, so yeah, let's, let's just run that. Um, and after basically we do the import, uh.

have 3B. Uh, why do we need actually pandas here? In this case, the jupy SQL can return things directly as pandas, uh, to you and that ends up being nicer in a jupyter notebook, mostly for looking at the table output. It's formatted nicer, okay. Um, but it's actually faster as well. So, because pandas is written in C, uh, C++, and so is duct Deb, it can actually transmit the data all at the C++ layer and kind of turn it into python at the last second. Whereas if you use the python DB API like fetch all, it has to convert kind of every individual object into a python object. And I love python, it's flexible, but the objects have an extra layer of abstraction over like a c type or something like that. So, um, it's faster if you use pandas, okay. So, that, yeah, that actually I didn't know, but that's, uh, that makes, uh, total sense. And so, um, yeah, here we have just, uh, a couple of setup of, uh, configuration and what we are going to

use is uh SQL magic command so that basically we can use uh pure SQL uh command and ah wait. I did something really bad uh apparently people doesn't have access to must have link to access it. Yeah, see anyone with the link all right should be solved. Sorry for that and thank you Nick for mentioning that out um so you can actually check back the link I sent on if you're on YouTube or in in and uh follow along uh the the collab here um so yeah I was talking about uh Magic command in SQL. It's just that instead of uh having because here we using a python runtime basically uh but instead of wrapping this into you know an ugly text drink uh we can just have a pure SQL directly uh uh over there so yeah so that's basically it and um then we're GNA do uh one thing yeah we we still going to use a mod deck but we're not going to use the mod deck inine we're basically using uh the shared database so mod deck has a

feature for share database. So it's basically a Doug DB database in the cloud, and we have a couple of open data sets over there. So you can actually create an account on DDB on Mother Deck. It's free launch for the moment. So just and super fast. So if you want to have access to those shared data sets, just create your account, and then you can get your service token on your profile. And that's what we are going to fit here. So basically just putting, passing this. So I passed the my series token from Mod Deck account, and now I'm just connecting to Mod Deck using this role. And yeah, here it's basically the same way if you would use a local Doug, right Alex? But we just have a the MD2 MD column. Can you a bit explain what's happening behind the scene with the extension? Sure. So in this case, we've partnered really closely with the founders creators of DCTB over at DCTB.

labs, and they've added a special hook into duct TB itself for us where, um, typically you'd be connecting to duct TB with a path of, uh, where your file is and your local file system. You know, here's where my file is. Um, if you actually start with MD colon, it's just a special if statement. It says, Hey, if you start like that, what you actually mean is you want to connect to mother duck. And what we're going to do is we're going to automatically install a mother duck extension for you in an extensions folder, um, same place that the other dctb extensions go when you install those. And that extension is going to open up a connection to the mother duck server and, uh, when you, when we make that connection, we'll go ahead and send you the information about kind of your, your tables, your views, and what's in your database. And then you can go ahead and hit it with queries, um, the nice thing about that is some of your queries can run on our server in the cloud and other ones can run

entirely locally. Um, if you've already copied the data locally, so um, you get that nice flexibility. Yeah, thank you. Um, so yeah, and and just to give uh context for those not super familiar with Doug DB extension. So uh, Doug DB has this notion of extension is clearly like package that you can install to Doug DB, and you have supported extension from DDB Labs that you can just do install, you know, for example. They have uh extension to connect to HTTP, uh, FS. So HTTPS file that hosted on history or Google Cloud Storage, or they have also an extension to manage adbs, uh, credential, for example. So you can just do install, uh, ads, and you have this extension loaded. Um, but here what's the beauty of it is that U everything is happening automatically. So you don't have to install or load any, uh, Mod Deck specific extension. And now we are uh, connected to Mod Deck. So basically here what we're doing is just uh, querying uh, sample of data, which is uh, if you look at Mod De take that uh.

quickly, uh, our documentation website. You would see where, what do we have actually by, uh, by data set, public data set. So if that's a so mod, do documentation website, you go to sample data set and queries, and you have information about the, uh, the different website, uh, the different simple data that we have, and, uh, the simple database actually contain multiple, uh, data set and actually, uh, available as soon as you create a mod de account. You can play with those and, uh, you have information about, uh, those data sets and a couple of example of queries, um, so just to, uh, to give you a rapid context about where those data set is coming from coming back to our notebook. So if we run this, so that's the first, first, uh, Magic of theb is the FR statement right, um, I think I'm not sure why this hasn't been done before or at least from like the, the database I know there isn't, uh, that supports, do you have any hint on that. I think there may have been one that did it previously. I think it might

have been a dialect that was part of a bi tool or something so it it may have existed before so credit to them for being ahead of at the time but it's not widespread and there's no other popular one that I know of that has it um part of this goes back to that that fun fact that you had about SQL using to be spelled out seq L it was designed to be english-like and it was more like a sentence to say I want to get these columns from this table and so it was just meant to be more more like spoken word uh but it's always been backwards if you actually think about it the you know every intro tutorial you get for SQL is going to have here's the order statements and here's their execution order and here's how they don't match uh but now they can match how about that yeah so it's it's for me it's just crazy the number of time I do a select star the table and then a limit then just to you know get the grasp on

everything because even if you have the metadata, you want to have a quick view, like just like how the actual data looks like, you know, first um and um and so yeah. I think that's like one of the biggest uh things I like about uh Doug be is really simple. And then the next thing is that we can actually Nest directly select, and that's actually still pretty new. It was released this year, no, at the same time as that from only one, so it might be two years old by now, but it uh, okay, okay, the basically benefit is you don't have to, you can just reorder the Clauses if you want, so you don't have to to skip what columns you're grabbing can still put it there, but you can have it in a more natural order. Um, the other really nice thing about this is it uh, it helps us with those AI overlords that are coming right as soon as you actually give the table information. Autocomplete is going to have a much easier job of figuring out what you're probably going

to do so um this this uh even helps out our auto complete yeah and that's a really interesting thing is that there is like certain pattern I think like for documentation that's also to write documentation that is B friendly that can help you know the AI to parse and understand thing and I think in the same sense it's like right you know programming language that is also uh I mean in that context for example in that specific use case where uh the the AI basically can tell from uh from the table all the metadata directly information and send you out so this also uh it going to work basically um not so so much surprise here um and then we have uh the thing you were mentioning uh just earlier the group order by all um so typically here we have uh a group by and instead of specifying each goon right we just specify Group by all and is that the same behavior with the order by it's similar so the um order by all is going to order by every column in a row and uh ascending by default just

kind of sort sort of like how an Excel pivot table sorts. It's always going to sort ascending column by column. Um, you can adjust that. You can say descending after it if you want to flip it around. You can even say NS first, NS last if you want to get picky about where your nulls end up in that ordering. Um, but it's really handy, you know. A lot of people get around this by saying, Oh, order by the numbers one, two, and three. But when I'm playing with a query for a long period of time, I always end up deleting columns and adding columns. And I always have to go back and change that number. I've seen a query like some of it's autogenerated, but I've seen a query that probably had like the numbers one through like 20 listed in like a group buy and in an order buy and but like number 16 would be removed. It's like that is the least comprehensible thing ever, right? Just like group by everything except column 16. Like, Oh, what a mess. So I much prefer

this way, yeah, and it's true that like here, we're playing with like a really really simple example, right, but typically when you, like in practice in production, you probably often have more than three colon on your final data set, so the problem is basically bigger, right, um, than here. So yeah, so this is something the group by all I was actually using it, but the order by all, actually I wasn't, I wasn't aware, uh, that was also existing. So, oh, awesome. So yeah, um, so and then we have, uh, I put a 2B, uh, here, colon, Alia, and I think this is, uh, a bit the same thing than, um, that, that I was saying earlier, those thing that's happening behind the scene, and, uh, and I, I completely forgot, actually, that basically if you're using an alias here in the, in a select statement, they're not available, um, in other clauses, in group by or having or where, so you have to to repeat those, uh, which can be tedious and just had, you know, more, uh, human error prone, right, because you define a different place, um.

and so this is like actually something that felt natural, and I don't know why at some point, uh, when writing seal with their DB, you know, it got me. And then recently I went to, uh, other cloud data we have because I do, um, I like shopping, you know, wrong, and, uh, and, and then it was not there, and I was like, but yeah, but that's, that's obvious, I knew that, um. And so you know, like, like the magic kind of disappear, so, so yeah, that's serting, but this one's a funny one. In a lot of cases, this is kind of going back to like, in general, we database folks love High precision, and in theory, there's no way to know for sure if the column you're using is the Alias or the underlying one. So for to be sure, they made you not use the aliases at all, but really if there's no conflict, like if there isn't a column named year or month or keyword FS, the database should know. So duct DB is going to guess, you know, it's going to guess.

only when there's maximum certainty if there is an ambiguity it's going to tell you an error out and say hey please be picky about it but when there's no ambiguity you know the database should know that you just rename something year and it should it should let you use that column name wherever you want yeah yeah yeah no so that's that's uh definitely so this is like really a one I was using that I kind of realized when I jumped out of SPC DB for for a while yeah and then once you jump out of it you have to do like a whole nested CTE up above it or like a subquery to rename it and do it or you have to just use these ugly functions in the rest of your query so it's it's there's not a real good workaround yeah it can be really verbi right if you if you don't have those yeah um and so this one um I didn't run this one U and by the way this is uh a data set from uh acar

news. I believe you have quite a lot of data up to 2023 May and is counting the number of appearances of tgb in title of I new P so you can see this was in October 2022 was mentioned 10 times already. I think that was, you know, start of the hockey stick that we were mentioning earlier, probably this time. So yeah, the low part of the hockey stick is probably mid-2020 timeframe. Everyone decided, you know, I'm stuck at home locked inside this whole Global pandemic thing. Let's check out database. I like the way people think. This is true, that's definitely true. Reasonable, colon, alas. So it's kind of the in the same Vibe or maybe it's the no what am I missing here. Yeah, it's very similar. The main difference is the one above is like I can use something in my select in my wear Clause here. It's I can use it in the next part of my select class, which is really nice. Yeah, so here

nested your nesting operation on one alast, and then another, then another, yeah. So, yeah, that's even a bit more advanced. I think pushing a thing maybe a bit too far. I think you can create like a lot of complexity, uh, to that. You can, you can, uh, this one, I think Red shift, I think, supports this. So, it's been around a little bit. It's really convenient. It's one of those where I saw someone post, you know, like I jumped from Red shift to somewhere else, and now this isn't there, and like I hate writing squel now, you know, like it was, it was a very extreme. They didn't say exactly that, but it was a very extreme reaction because like it's another case where the database should know what you're asking for with no ambiguity and should just figure it out. And so we're, we're all about that, yeah, indeed. Um, this one, I, I like it because actually I think I didn't say that, but like, uh, I think deck DB kind of make peace with me with SQL, uh, I was.

more a data frame person. I think there is two tool actually that has made pie for me. SQL Deb defly because the the before the the the time I was using SQL was with cloud data warehouse and so on and just the interaction is not like yeah for a software engineer it's a bit complicated to test uh you know to create unit test to test it locally um and so ddb provide actually that right by by being able simple to run. So now I can not only for you know data pipeline but as just a typical task or I want to inspect a CSV or a par or something locally. Yes I start to use you know SQL more as a as a toolkit than you know a language to to program. And the other tool for me was definitely what do you think is this the second tool that made P me on SQL. I don't know not sure uh maybe Ibis doing some data frame SQL hybrid. I don't know uh no no that could be but uh no DBT or I mean any framework.

that basically helps you to kind of again solve the gap of you know SQL for unit testing. Create variable reusing codes. Um, and I think like the two together is really nice. I, at least, it feels like back in the days when I was developing Spark Scala Pipeline and functional programming, right? And so coming back to SQL with those environment, um, yeah, feels nice. Anyway, that was like a little parenthesis. All of that to say regarding function chaining, this reminds me of typical Scala, you know, nesting data frame, which is why I like this one, yeah. Um, yeah. So what we do here is basically we have the string, we do uppercase, then we split it into a list, right? That's correct, yep. And then we basically recombine it with a dot for each word, and then the concat basically makes it back as a string. Is that correct? Yeah, I think the concat is at the very end. I think we just had a period at the end with that one. Ah, okay, sorry. Yeah, that makes sense. It's a

very contrived example so I, I, I, I uh. It took some work to come up with one on the fly here with no for this one but um, yeah, it's pretty neat. Basically using the dot. It's just like in other languages that allow it, you know where the first argument of that function is what you have right before the dot, and it keeps you from having to nest everything super deeply. And it's super, in my opinion, super convenient. Um, this was actually probably the most controversial of all of these when I talked about Twitter. People were like, Oh my gosh, you know, like how am I going to parse this in my SQL tool that parses SQL? It's like, yes, it is a new trick for SQL, this is true. Um, I haven't heard any pushback from people using it though. It's very handy for using on the system builder side. I could see this is more complex, you know, this is tougher to support duct DB syntax if you have to parse it now. Um, but man, just

it's much cleaner to look at, yeah, and you basically make, uh, make friends with data frame people like me, um, so I think, I think that's a pro. I, I, Ed P this too, but yeah, I'm more of a sequel, uh, sequel to start, yeah, um, exclude, replace, uh, so yeah, if you want to, uh, select everything except the column, uh, some columns, so that this is also pretty, um, pretty Andy, um, Dynam colum selection. So here we just do a Lambda function on based on columns, right, um, and just to look any, uh, column that starts with pop. I think that's really useful. I've been, um, using U once or two. I, when I have a really large, uh, table that I'm not sure and when I do the describe, it's like you have a lot of things, like really when you're like doing some data data mining, you have no idea the data you're dealing with, uh, I think like, like just searching, uh, based on some keyword and returning, uh, some of the column, um, so here we see that we

have to PO sour simp population. Yeah, it's really great if you're generating columns automatically in some way. Like if you're doing one hot encoding or you're doing some of the machine learning stuff where I've got, you know, I'm trying to do a prediction so I've got three different columns that are very similar but name the same but I have a thousand columns. You know, this means that a lot of those workflows that where I used to be in a data frame and I used to be getting a list of my columns and doing a text search on that list and then doing some operation on that list of columns. You can do it all right sequ, yeah, yeah. All right, pivot and pivot. So this is more like a SQL thing, an Excel Excel thing. I think for the people I never like use that much. By and and buot, what about you? For me, I ended up using it a decent amount because in the group I was in at in I was in the supply chain and it's a we production.

scheduling, and yeah, if you have a schedule, it makes way more sense to look at it pivoted out where every column is a new week. Every column is a new month, and it's just so much more visually appealing to look at that way. Many many things were structured that way. Uh, but it's always been best to do that as the very final step. You know, working with pivoted out columns is always messier in SQL, and so this is a really great kind of final step of just just pivoted outwards. Um, but that that's the main use case for me, you know, here this an example with years, kind of the same idea, like it allows you to do a much more a very quick visual interpretation of what you're looking at. Yeah, not I think that makes the that makes uh total total sense. Um, it's just like yeah, it's true that I again like as I said like I'm I'm mostly a data frame person, so that's like a different different side, but I know like people

coming from Excel or a Google sheet, they use that a lot because it's kind of those people actually don't use SQL, right? When they and I think like byot or function in Excel is pretty popular, super popular. I call myself a reformed Excel Guru. That was my Gateway into programming was Excel VBA. So that was like the first thing where I was like wait, I can just wow write this and like I can just delete files on the computer like what? Like this was news to me at the time. So I thought it was really cool and I got really into it and that's what convinced me to actually go learn programming. I didn't do it as much in school. I just really that got me excited where I could just, Oh you mean I could do anything like okay, this is pretty sweet. So that being said, I respect the power of Excel. There's some stuff that is just it's just so good and pivoting is one of those where

um it just lets you use your visual pre precognitive ability to just look at something and see the problem and yeah that's uh you can underestimate that pretty easily if uh if you're not uh playing around with Excel all the time yeah. I think uh I think Excel still has its place. I'm curious about like what thing the people live with us uh are you fan of Excel or not. I think there is like really strong opinion depending on your profile if you're sourcing from an Excel Source or if you're actually an user from Excel. I think that's where I've got on both sides of that. Yes that is hard I will agree with that very much yeah. All right uh string slicing um yes that's that's fun that's again a bit like something I mean bonis I would say no what where where is it come from exactly yeah but you can slice string yeah you can SCE string um based on position here so I remove the two and I don't I I don't love ddb anymore I love

duck probably the same thing this one. This one is another. This one is another. Yeah, this one is another um thing that based on the I think, which was the one uh the group uh by all um yeah the trading comea was also some which just happened and then I hit my you know a wall on another system to say but yeah, of course, I shouldn't have trailing comma. How, how does that work, you know, yeah, absolutely and this is just the the folks building dect Tob uh really familiar with their parser and they were able to really kind of make it dance and I think this is one of those where you know there's a lot of places they had to change this, you know, your select cause, your wear cause, you're everywhere but basically anywhere you can put commas it it handles a trailing comma at the end and it won't be mad because we could the computer should be able to figure this out, right, you know, we know that you don't actually mean to put comma than nothingness so uh just.

another Common Sense let the computer do the work situation. So just to clarify for the audience, is that normally this would, uh, this should be better, um, or standard SQL, and this would an error. And actually, typically, uh, the problem is that when you, um, actually explore your query, and you typically, you know, command some colon, then you might end up, um, for example, not in that case, you might end up with a trailing comma, um, so that's where it's pretty handy to just not, not think about it. And that's again something that just, that I knew initially on SQL that was needed, and it just disappeared in my mind until I, I try another system, and I hit all, um, the other, yeah, oh, one last funny thing on that, that also to be, taps into another, you know, age-old fun argument in SQL land, which is, yeah, trailing commas or leading commas, you know, like people have very strong opinions. I think the main reason that leading commas are preferred is because you're usually editing the, F last couple of columns, and you want to

avoid the trailing comma error. So to me, this kind of, I used to be a leading comma guy. Like all my old queries, leading commas. I have changed, I'm a changed man, and I've, I've been won over to the trailing comma lifestyle. So, uh, try it, you might like. I'm curious, do you, are you, are you in the audience using the comma in front because of that, or at the end, you know, drop in the comment what kind of sequel style are you, uh, so that we solve the mystery. Uh, we are almost, uh, 10 out, so I'm going to accelerate, but anyway, we are running at the end. Um, there is actually that's a duplicate. Uh, we're running in circle, um, and we have two more, uh, column with regular expression. Uh, we actually do that, did that too. Um, with, I think we did more Lambda function here, but not with a regular expression. So that's also working. So the columns is pretty powerful, and this one has to be also. We, uh, did that too. I promise I didn't do, uh, The Notebook when I was drunk. Maybe I

was a bit tired. That's that's different. Okay, no problem. I just fix it. So we have 10. Uh, I think that which is, uh, actually a nice number. Uh, better than 12. So 10 things in SQL. Um, yeah. So that's, um, that's about it. Um, for a SQL Journey. Uh, about, uh, DGB, a little quiz also to run. When SQL was invented. Can I ask you again when the when the SQL was invented? Oh, it's a real quiz now. No, I think it was 74. Now I know water man. I got it. Yeah, exactly. Cool. Uh, at least you, you learned something also from me. Uh, but yeah, the bottom line, I think is like go over those, uh, those query, the, those, this, net book and those function because they're really there, uh, to help you out to actually minimize the code. Also, I think that's really important to mention is that some of them make the code just more readable because it simplifies some stuff, right? And other stuff is happening for you, know, you know, behind the SC kind.

of magic like we saw the trailing comma uh the group by all if you get used to it, and you learn for the first time uh SQL with dougb. So, it's important to know that it's uh thinks that the Doug DB team has been put in place to just make SQL friendlier, right? Anything you want to add, I would just say also, you know, this is something where if there's more you can think of, let us know. Uh, open a discussion on the duct DB open source repo. This is an area where we're happy to kind of lead the charge and do something that people haven't done before of the SQL should be able to do blank, but it doesn't today. Um, tell us your stories of pain and woe of, you know, I always get this dang error, and maybe we can fix it, maybe we can solve it for everybody.

Friendly SQL with DuckDB

Collaborative serverless analytics platform


MotherDuck

A new live stream session introduces a discussion on SQL and its new features.

The session features a quick intro to SQL and its use cases, as well as a guest speaker from Mother Deck, Alex, who shares his role and experience with Doug DB.

Alex discusses his background in industrial and systems engineering, his introduction to Doug DB, and his experience with the platform's growth.

The session also includes a SQL quiz to test the audience's knowledge, covering the development year of SQL and a popular song released in the same year.

The Evolution of SQL and Its Impact on Data Management

SQL, originally named "SQUEL," stands for Structured English Query Language, with the standard version last released in 2016.

The slow evolution of the SQL standard has led to divergence in data warehouses, with new tools emerging to translate back and forth.

The standardization process for SQL often lags behind the actual implementation in databases, leading to fragmentation and individual approaches before a standard is established.

Many people are now using VS Code for SQL in addition to Python and JavaScript.

VS Code offers formatting, syntax highlighting, and GitHub Copilot for SQL, making it a versatile tool for SQL development.

DBeaver is also popular for working with physical tables, while Google Colab is being used for sharing and teaching SQL due to its easy setup and collaboration features.

The Difference Between DuckDB Engine and DGDB Python Package

The DGDB Python package is named after a type of precipitation and has a group A, aiming to become a standard database.

On the other hand, the DuckDB Engine is an SQL Alchemy dialect, preferred for determining schemas in Python, supporting migrations, and easily connecting different things.

Pandas is needed for its faster data transmission and the ability to return data directly as a Python object.

The MD colon is a special hook into DuckDB, allowing a connection to Mother Duck server and the use of shared databases in the cloud.

DougDB extensions offer the flexibility of locally copied data, without the need to install specific extensions.

The DougDB extension allows for automatic connection to ModDeck, providing a seamless query of sample data sets.

The extension also introduces new features like the ability to nest selects, which is still a recent addition, and the convenience of using "group by all" and "order by all" for comprehensive data sorting.

The Challenge of Using Aliases in SQL Queries

Using aliases in SQL queries can lead to issues in different clauses like group by, having, or where, requiring repetitive definitions and increasing the risk of errors.

The use of aliases in SQL can be restrictive, as they are not always available in the following parts of the query, leading to complex workarounds such as nested CTEs or subqueries.

The experience of using aliases varies across different databases, with some being more restrictive than others, leading to frustration and the need for complex solutions.

Function chaining in SQL allows for the reusing of variables and codes, similar to the concept in Spark Scala Pipeline.

It provides a convenient way to perform function chaining, making the code much cleaner and more visually appealing.

Function chaining also allows for dynamic column selection, which is useful for large tables and data mining, as well as for generating columns automatically, making it a handy tool for machine learning workflows.

Pivoting in SQL, similar to Excel, is beneficial for visual interpretation, especially as a final step, and is commonly used in production scheduling for a more visually appealing display of data.

The Significance of Excel in Data Analysis

Excel still holds a significant place in data analysis, with strong opinions on its use depending on the user's profile.

Handling string slicing and trailing commas in SQL queries is a common challenge faced by users, with strong opinions on the use of leading commas versus trailing commas.

The Duct DB team has implemented features to make SQL more user-friendly and efficient, aiming to minimize code and improve readability.