MySQL Tutorial for Beginners | SQL for Data Engineering | Community Webinar

Key Takeaways

Today we will we will talk about uh how to spin uh my SQL instance. uh what is like we we'll go over the basics of uh how to set up my SQL uh why SQL is important and for those like who are interested in going to data science what are what is the motivation behind learning setting a SQL database right so with that I handle to Sergey so he can introduce himself I kind of lost in my windows Okay guys, so welcome here. Nice to see you all. Thank you for joining. My name is Sergey Kmichov. I am a database engineer or whatever you want to call it, a DevOps database reliability engineer uh with more than 10 years of experience. I'm currently working as an infrastructure performance architect at investing.com. And uh uh while we are mostly concerned with I'm mostly concerned with how the databases run and making sure they stay that way. We al also get experience with like you know uh the BI departments and all that. So I'm also a co-author of the book learning MySQL which we wrote with Winnie. It is a second edition and uh we will show a couple of links to how to get it a bit later if you want. So uh this presentation is a kind of a condensed uh part of the part of the book. Okay. And off to reading. Thanks Sergey. So uh I'm I work at Perona. For those of uh we have like the MySQL the Precona MySQL server which is uh a different flavor uh for those who are in the community already. We have variab enterprise peron server and so on. I've been working with database for a quite long time already from 15 years to 20 at least. So yeah uh and my focus recently has been on uh open source basically. So it's mostly uh my SQL and MongoDB for those who doesn't know we can mention it briefly later. All right. Um so so we will talk in a bit what's an RDBMS and why is it different from a no scale database. uh we will talk about why the structured query language which is like the uh backbone of interacting with an SQL with an RDBMS database is so uh is useful and the the main part of this talk and the the the point of this talk is to showcase how easy it is to uh get your hands on a local instance of MySQL u how to use some of the I think one of the graphical tools available and uh how to get a couple of data sets that could really get you going with trying SQL uh understanding how to interact with MySQL. Now uh um let's start by defining what is a relational database uh management system. So uh it is uh any software that can organize data basically and uh we can manipulate using uh SQL statements u usually uh RDBMS like SQL server oracle my SQL all of them uh thanks George okay so and basically it is composed by tables and the relationship between these tables to define uh some constraints like u uh I am Vinnie who where is my address who each is my who are my my children my family where I work we we can construct this by using uh relationships between tables uh one important thing is uh SQL uh databases they they are they are strict in terms of schema. So uh let's say I'm I have like my my full name is Venios Gria but in my table I didn't consider including my social security number. Uh there there's no way for me to add that unless I add a new column. So like it really depends. When we talk about MongoDB for example, you can create schemas with like Venicios, without last name, with social security number, with dog name, whatever like uh the sche the schema is very flexible and well SQL is the the language that we use to extract, transform, manipulate data um and work on So this is an example uh I like to pick uh because well at least me I started working with Excel and those spreadsheets and I think we can call uh a spreadsheet uh also like not exactly a database because uh of security data consistency issues but it follows the same idea you know. So this is basically like a spreadsheet where you will have your ids where we call lit for example primary key again social security number is kind of primary key everyone has one it's different and we have the other columns defining what we want. Uh and of course for each line we call it rows uh or tuple and in each column you have the data value and how different uh is it is from NoSQL database. Um I know many like uh already heard about for example big data or something like this where people are storing like enormous volumes of data like from audios from from everything like in an unobstructed way. Um so uh this is an example like um uh it demonstrates how like different it is a traditional database uh SQL database compared to the most famous NoSQL database for example key value which is u Cassandra is a good example uh graph uh which is most used like by social uh companies for example the one of this database are L4J document uh the the most uh well know is MongoDB and colony store we have um I forgot a name now um but yeah we we have another database for colony store dedicated for that um and many Many ask can can ask like if SQL is useful for data science. Um Sergey will demonstrate later like uh the SQL language has a big potential in terms of extracting and manipulating data. So we can go from simple queries to very complex ones. Um I think u a good example of this potential is for example PowerBI. or maybe I can like translate um raw data to like something that business can use in real time. So yeah u this is an example like I was saying from tables and their relationships. For example you can see the bigger table in the center of the screen which is film. It is linked to film actor, film category for example and how you if you want to extract information like uh for examp which category avatar movies like you need to join two tables. This isn't a very simple example. So like the the classic ones. Okay, I want information from country. I want all country names that I have, but I also want all city names that I have. Like I have one query for each. But if I want to put like okay um I want to know that S. Paulo city is from Brazil and Bhutapast is from Hungary. Then we can use the following join where we merge uh the information from country and city in a single query. And now we are going to deploy my SQL instance. I will hand over to Sergey who will go over a live demo. I hope he doesn't have the issues that I had in the beginning. Good luck sir. Okay. Thank you V. So I will share my screen. uh should work, but it's always funny with the live demos. So, you should be seeing my Safari window open. So, deploying MySQL instance is relatively simple. It's actually pretty much easier than I expected to be honest. I have never before been preparing for this presentation actually deployed my scale on a local Mac. And for those of you who use Windows, uh the procedure we unfortunately cannot cover in the amount of time we have, but it's pretty much the same. Like you get an installer, you click next, next, next, it just runs. And the workbench is absolutely the same. So yeah. So to get my scale the simplest way is to navigate to the gosh I don't know if you see the bar here with all the zoom okay to the site called devmyscl.com you can also Google for like mysel download it's going to be there probably and here you go to download and uh you should scroll down the enterprise edition unless you want to pay some money to Oracle and go to the community GPL downloads. And here, if you are on Windows, you would pick this. On Mac OS, we go directly to community server. It knows I'm on Mac OS. So, you can see there are different options, but we only need Mac OS. Uh, the only choice you have pretty much is whether you run on our x86. I happen to run IRM. So I will save you downloading this image image. I have already done that. So I've pretty much prepared everything. So once it's there in your download folder or whatever, just you know the usual stuff. Open and we should get something. I hope. Yeah, it's unsigned, but who cares? Yes, I do allow that. All righty then. So, and like it's going to be really really simple. So, you might want to read the license if you'd like, but it's it's okay. We don't care where it's installed. And I guess it's my password now. Okay. Gosh. Okay. So the only customization I want to recommend is do use legacy password like probably don't doesn't matter for you unless you are running in production but just for the sake of safety of your mind on a local machine do use legacy password some software might not work with the strong password encryption and it's absolutely okay for some home use and this is exactly what we're showing like getting something to test getting something to play around with so some password just I might I need to understand that I remember it and absolutely start my SQL server. Okay, once again I do have Touch ID. I wonder why it doesn't show. So yeah, when you live stream when you show stuff like this it's always funny because it always breaks and that's it. like that's that's that's all there is. I now have a completely functional MySQL server running somewhere on my computer and it's even possible to check it nicely in the in the system preferences on Mac OS uh or in the task manager on Windows you would check it but on the system preferences on Mac OS you get this nice icon of MySQL which you can click and you can actually see the servers and you can modify it, you can stop it, you and uninstall it if you want and you can actually go ahead and configure configure it if you'd like. The other way to make sure it works is to go to a terminal and probably just go here. All right. And I should it should work without this thingy. It's okay. And the CLI is something a bit more advanced. Yeah, because you have to type the passwords correctly. But yeah, this is MySQL now. Yeah, I have a functionally functional MySQL instance running on my laptop like that. It's not even 30 minutes, but the presentation is still going. So I believe Venicious will show you something more. Uh you should probably unmute Vinnie. Yeah, sorry. Uh let me go back to our presentation and I hope you guys can see it. Uh again uh the next step is um one of the methods to deploy my SQL apart from doing locally uh as Sergey did by installing um basically Mac OS and Windows is next next finish. You just need to to your to put your password. We can use Docker. Uh Docker is is kind of a virtualization and it works a little bit different from for example from virtual box or even a EC2 instance from AWS because uh you don't have the west operating system uh beneath it. So Docker like runs directly in the container in the container engine and it is expected to be like faster at least faster to deploy. And let's see a couple of examples how to deploy my SQL with Docker and maybe I can address Harry's question uh while we are doing the deploy. Um I hope you guys can see my terminal and as we are going to see uh running docker is actually like a single command line to deploy a database. Uh the docker command is basically docker run uh where I specify the name of my container like specifying the the name of my my machine. uh we map the port because we want to access it not only from inside the container but but usually from outside we are defining the password and this is the image like I'm using on my SQL my SQL server the latest version and with one click it will tell me okay I don't have the image it will download And once it finishes downloading uh it will start uh the container. Uh while we are spinning um as we can see it is already running. uh Harry uh I maybe I will go over in details more to the end but uh my SQL and Perona server they are very similar actually like we we say that you can replace my SQL server by Perona server like just by replacing the binaries uh the idea is to be 100% compatible Mariab uh is a bit different because in in the latest years uh it really deviated from the myop path. They started introducing their engines. They have their enterprise products and all this stuff. So nowadays if you come to me and say look I want to go from Maria to my or vice versa. H it is it is not so simple as it used to be. You are going to have some some problems. Um here for example we have the container running. I can check with docker ps and we see uh it was created a minute ago. It is healthy the parts that I have mapped. I can login into my my SQL instance by using this command. Basically I'm telling log to my log into the container and once you are in the container run the MySQL client. Why I'm not accessing this from outside at this moment? because as you can see I'm using the root password and the root password by default it does not allow uh external access right it's it's a security that comes by default with my SQL um if you want to access from outside you create a user with proper privilege and and life goes on and like talking about different flavors uh it is quite simple uh docker has images for uh every flavor. So this is a perona server again like I'm just defining the root password. Uh in this case I'm not defining the port but it will map from default 3306 to my default 3306. Yeah I will comment on the difference between extra DB and NB engines. I do not believe that this difference exists so pronounced right now. So uh the perona server basically adds a few let's say enterprise features to the uh vanilla myioscale community server which usually are available in the enterprise server. There are some quality of life improvements but mainly geared towards the uh the database administrators like it is a bit nicer to maintain. It's a bit nicer to understand what happens inside the perona server than it is with the regular community server. But uh the community it's it's not 5.5 anymore. We are in 2023 I believe or not actually weird saying that but community server has you know kept up. So uh as of now the the main the main difference here is Mariab as Vicious mentioned like it's just a different database to be honest. Yeah. And while talking about Maria, uh this is the single command line you will notice that at least for my Maria DB, it didn't download because I was testing before. So I already have cached the docker English. So we can see our instance is running and in case you want to see Docker. So this is the interface. Installing Docker is like Sergey installed uh my SQL next next finish give administrative privileges to run the containers and and that's it. Uh for those who doesn't want to use the commands like I can stop my databases here I can delete them and like simple as this like you can run a quick test with a sample database Sergey will will show it and then you finish you drop and like You don't have to install the operating system or turn off the the cloud machine whatever like docker is more flexible in this way but do do note like that uh if you are really basic user and you have don't have experience with docker then docker itself has some learning curve like it is uh you should probably know docker before doing myql with docker I also want to answer a question from chuck with ask is it possible to schedule queries or create store procedures? Yes, it is. It is possible to schedule events and it is possible to create store procedures. So, both are possible to do although we will not talk about that uh during this presentation but it's definitely something you can do. [Music] All right. So, yeah, where are what's next? Uh I will hand over to Sergey but just to mention like uh if you guys are using Apple computers ARM architecture uh you will have to download the specific docker version for this and also for the database like some database for example perona server does not have a ARM version at the moment. So uh you cannot run uh docker images on uh apple ships right. Uh so and to answer Harry is my SQL heatwave open source no uh heatwave is uh particular for Oracle cloud or the OCI and it's proprietary uh there is no version available and with that I handle to Sergey and how to manipulate the my SQL interface. All righty then. So the first in the menu is CLI definitely not something I recommend doing unless you want to do that specifically but as I showed to you you can go to my scale. I do not remember on out of top of my head where you will have the MySQL exact X exa on Windows. uh had some trouble finding it on Mac OS even but it is doable like the binary is somewhere there. If you have installed the MySQL server you will have the MySQL client and this is the basic client uh we have. So you can go ahead and do like show databases and see that the bare MySQL instance is pretty bare. has nothing inside it like uh the only stuff you have is the system stuff within the mySQL schema or database as it's called and you probably should not mess around with this stuff. So, uh this is CLI. We're not going to pay a lot of attention to it. It exists. You can do that. It's super convenient. It's my preferred way to do things but it has some learning curve and unless it's something you want to do don't bother about it. So now to the more user friendly stuff. So MySQL provides uh a graphical interface to it uh which is called the workbench again exists for Windows exists for Mac OS. So with Workbench, yeah, you can see Windows, Ubuntu, whatever. For Mac OS, surprisingly, you only have the x86 package, but if you run an IRM processor, don't worry. It's just going to work slightly slower probably, but it works. So again, I will spare you the download. I have already downloaded it. So things should be relatively easy. Yeah, this one is even simpler. No installation other than putting it into application folder and waiting for it for a bit. Cool. So now look what I can do is I can find it and run it. Uh it will be asking whether you really want to run something you downloaded from the internet. And that's it. This is the MySQL workbench. I'm going to keep it windowed. I will expand it as we go. So you can see here we have a pretty bare interface. Uh the most important thing here right now is the MySQL connections. Uh if you are working locally with your like basic server and you don't need anything else much, you can always just click on the local instance. Uh if you want to access something different, you probably want to click new connection and then you can type a new name like PCB. And you need to know the host name which is either an IP address or like a DNS name that they would give you and you would also need to know the username and probably the password. So you can test connection as usual. We will not do that right now. So with the local instance, it should have asked me about the password and whether I want to store it in the keychain because I have already done that. I didn't want to reset my OS. It didn't ask that. I'm also not going to restore the workspace. So uh this is where you get when you open a bear instance. You don't have too much to explore. By default, Workbench doesn't show you the schemas that are kind of database specific. So, you cannot really snoop around much. There is nothing to look here. Like, that's pretty much it. Some tables, some views. That's it. Uh, we can make sure it works by using the same query I used. And uh, show databases probably. Yeah, we can see the very same databases, but this is the workbench. uh and I will show you it in slightly more detail. It has a lot of functionality and a bit difficult to cramp it all in in a short time but the basic functionality is you have a connection you pick a schema you write a query you execute a query you get a result set which you can export if you want. Now this is pretty much what we want from the database interface. There are a lot of database specific stuff here, administration specific stuff here which not everybody needs. Uh so today we're kind of focusing on the running queries part of it. And uh Vinnie I believe uh you can tell a bit about the demo databases or is it me? Uh if you want to proceed with Sakula that is fine. Cool. So before we kind of proceed with the workbench and from now on Vinnie if you want you can just interrupt and if you think we have a question that needs answering also let me know. So to actually get some data uh the easiest way to do it is the way for instance we did when we were writing the book is we need to get some data we need to uh actually write some queries that people could be executing while they're reading the book. And so the Oracle itself provides example databases. So again you can always Google or search however you want with chat GPT for example databases. And the two most like the two databases that I like and I guess Vinnie agrees with me is the employee database and the secular database. The employee and secil database are pretty basic. Secular is a bit more intertwined. Uh maybe I can pull a slide somewhere. Uh maybe not. Not sure if it will work. Um it should work. Should work. Should work. Yeah, this is Sakila database. Okay, just trust me. It's a lot like there are a few different tables which are all interconnected and which are all kind of live together. And the employee database is relatively simpler like it's just fewer tables all over. But the employee database is just a little bit bigger. Uh it has many more rows. Uh I think it's 135 megabytes of compressed. It's not a lot, not big data, but it's definitely something that you can like uh finesse your SQL skills upon. I think uh as a curiosity the employee database was created by the community not from Oracle. I think it's a probably like I'm not sure like every every one of them has like its own documentation. The Sakila has its own like uh actually like its own key documentation with like with history even like when it began and why. Uh there are many more databases out there including like the famous New York New York City taxi cab taxi rides which is like billions and billions of data points but for the your basic home setup this should be okay. Now the downloading is simple for Sakila. You just get the zip file. It's going to be natively openable both on Windows and on Linux. You just get a directory out of it. And there are two files which I will show you for the employees database. It links you to GitHub. On GitHub, all you need to do is go to releases and get the turbo ball uh the tar.gz file. It's slightly more difficult to install uh but not by much. So now I actually have both of them ready here. So I have secular DB and the test DB ready. I will go to the finder and I will start with secular DB. So just did I double click? Did I did I not double click? Yeah, now I double clicked. So I just double clicked and I get my schema and the data. The schema is the definition of the tables and relations between the tables and the data is the data and so it's separated if and if you want to explore the schema without touching the data or do some modification on your own. So how to load it in workbench? That's really super simple. We just need to open the the files schema execute. Oh, where is it? Yep. Should be all green. Uh, it couldn't drop the schema. So, it's a warning, but it's not an error. That's it. Basically I think it is worth mentioning Sergey that um for example you have the data um that are actually the values in of the rows but you cannot import data without having the schema before as we said yeah I guess that's that's a fair comment so the schema is like a scaffolding for your data so you define the table actor and you can have the actor ID the first name last name last update all of those are columns but there are data types associated with them and uh the data in the RDBMS data without the table definition is useless like it's just a bunch of text you cannot make sense of the data without this associated schema it's mostly true for all the data sets to be honest but it's just it's impossible to load data into MySQL without specifying the schema there are ways to load plain files out there we describe them in the book but you will also have you will have to specify the schema for the plane files. Okay. So once the schema is there we just need to go ahead and run the data probably going to run for some time maybe or not maybe maybe even not I don't know let's explore. So you can see what I did is I pressed this click this refresh button it refreshed my schemas and sakila appeared. I can navigate to the octur and I can do like double click on it to get the right mouse button action and get the limit 1000 and I should see something some data here. Okay. Yeah, I will expand the interface. Just let me know if it doesn't work for some reason or you don't see something. So this is the output. I have 200 I believe actors there and that's what I have. So again, Sakila is much more compact as far as workbench goes. The interesting thing you can do with the T database is you can I think do the schema inspector and you can do here. No, probably not. Probably not something that I wanted to show, but it is possible I think to generate like a uh the overview of the database here with like how the tables are related to each other. All righty then. So this is the Sakila. We will get to it. We'll run a couple of queries on it. But now the employees database, the employees database, as I said, is a bit more convoluted on both Mac OS and Windows by default. I will believe you will be able to extract it, but you will need to use terminal. Uh I believe there are applications for both of them that can do this, but yeah, terminal it is. So just RXVF and uh within the test DB there should be something like employees at scale which I believe is just what we need. And so uh I'm not sure if workbench would work with it. Uh the employees are slightly more advanced to install. So you actually have to use CLI here. But if I go back to my SQL here, I can pipe to it the employees SQL file. If you don't know what this does, just trust it. Just will read this file into the MySQL. It will give it as an input. And so yeah, it's going to take a bit of time. I hope not a lot. Uh but you can definitely see it doesn't take a moment. So it's it's a bit more data. Um we do have some time. If you want, we can answer a question or something. I was going to say maybe we can we can answer a question. So, um, how do you handle the CI/CD with my SQL? I've noticed GitHub doesn't like files greater than 50 MB. Should you just mock the DB for the test suite and link to the DB in an adjacent folder? um like the qu the size in the question is a bit of a like I have a question to the question but I will assume something okay and if I'm assuming my assumption is wrong just let us know okay but I assume what you ask is if you want to have like a sample data set sample database in your uh repository uh I guess that's okay uh the I think GitHub has like LFS large file support. It should be okay with really really large files. Uh other than that you can always in your CI/CD if we're talking about sample databases again you can just download it from somewhere and use a technique like I'm showing here to just populate the database because in your CI/CD pipeline you will probably be running a fresh instance of my scale which you will populate with the test data which you will then run tests upon. If we're talking about something like managing changes to the production data, oh boy, that's like a whole topic of a conversation for maybe a webinar or two. Uh there is no easy way to do that. There is no simple way to do that. All the software that tells you that it's going to do that magically probably doesn't do it magically and there is a lot of interhuman communication involved and it always breaks. So it's just a hell but it is how it is. It's an unfortunate side effect of having a schema. But if you are talking about the test data either store it on GitHub and whatever uh it has LFS it should be okay or download it unpack and deploy it like I do here with employees. Okay, that's my answer. Okay. Uh there is another question here. Can I use my SQL without without a container like docker from Joy? H yes Joy. uh Sergey demonstrated you can deploy it locally. U if you're using a cloud environment like AWS, you can use the famous RDS where you are going to have the database as a service. So you don't need to worry uh the service will be run you have backups uh high availability all this stuff. It depends how much you want to pay. But yeah there are many ways to deploy a simple database. doesn't need necessarily to be only on Docker. Yeah. And while you were talking, I went out of my uh nice place where I was having all my files here. So, sorry. But what I was showing to you is that actually like in the beginning of this presentation, I'm actually installed my scale locally on my machine like on my Mac OS. It's the same on Windows. I can actually see in system preferences there is my scale running right now on my computer and it's going to stay running forever. I can just never stop it. It doesn't use a lot of resources by default. It's pretty compact. So like it's not a problem. Not Slack or Chrome by any means. Uh okay. So now we have two data sets installed. We can go back to the workbench and again this is Sakila. I can do refresh. Now I have employees. And now in employees, if I go to employees, yeah, it's like the table is called the same schema. I'm going to clear this. We will not need this for a while now. You will see that I limited I think this is limited to,000 rows by default. Right? So, and there are going to be a thousand rows here. There actually going to be a lot of rows in this table. Uh let me just run the count. 300,000 rows. Yeah, right. Actually, haven't ever checked how many rows are there, but it's quite a lot. So, the sakila is more about how the tables are related. Not a lot of data, but you have all this inventories and film texts and stuff like that. You also have some views. I'm not sure. Yeah, and you actually have store procedures. I remember a question about store procedures in my scale. You do have those. So, are you consent SQL to editor? maybe uh create statement. Okay, so you this is the stored procedure that what does it do? Probably checks whether the film is in stock or not. So uh maybe used somewhere within the Sakula schema. Uh right. So with two t with two data sets installed what you could do now is you could actually go ahead find the guide for the SQL language and just explore test create destroy if you do something wrong with Sakila or with employees the both the data sets they include specific statements that will destroy the existing databases and will reinitiate them a freshly and so you will never run into a situation where you worked your setup completely and it's impossible to get out again because this is your local instance. Just go ahead new MySQL as I showed to you system preferences like just go ahead there is an uninstall button there do it reinstall it's okay like do not be afraid to experiment and crash it you will not crash it but like do not uh be afraid to mess it up this is exactly why you should learn and run it so now I have al also prepared a couple of uh queries I mean Vinnie do you want to jump in before I proceed to those because that's like the really really finish line of this. Yeah. No. Uh there is only one question maybe want to answer uh at the MySQL community downloads page. What is the difference between community server and my SQL installer for Windows? Yeah sure. Absolutely. So the windows you can actually if I go here if I go to the community downloads page if I go to community server the community server itself is actually available for Windows it's just available as a zip archive and within the that zip archive I will try to download it maybe also whenever you are doing the downloads don't get like you don't need to login or sign up you can always say thank here. I just want to download something. So, I will download it in the background. But as far as I remember the Windows, the zip file that you have for the community server. Oh, the interface I think. Yes. Oh, come on. Yeah. Do this and it will work. Okay. Yeah. Okay. Cool. So, I should submit it back to Oracle. So the zip file is just you know the server as contents doesn't give you any guidance of installation of whatever unlike for Mac OS I showed you the DMG the DMG for Mac OS you can also see here you have a tarball the tarball is just the server as files but you kind of have to uh set it up yourself the DMG is the installer for Windows I don't know why for legacy reasons or it's just so convenient the installation is a link. You can also see that there is an installer that is smaller in size than the full uh instance that's going to just download MySQL uh during the installation phase. So in the process that I showed you where I picked community server, uh it's just the peculiarity of Mac OS. To be honest, it should have been like MySQL installer for Mac OS here probably for consistency. If I was doing this, I would do it this way, but it is how it is. So, you definitely just pick installer for Windows. It's going to be the simpler way to do stuff. Um, maybe I will download the Windows [Music] 8 64. Why did it zip? Yeah, you can see that the server I'm actually just showing files in the terminal. I downloaded I downloaded my scale per windows and all I see is just a bunch of files. So you would actually need to run MySQL D your own to understand like it's possible. It's doable. It's completely fine. It's just not something you probably want to do unless you are a database administrator or like experimenting uh or reading our book. Just kidding. Okay, so um that answered. Uh let's go to the couple of queries I've written. So the data sets are fine, but can you do any anything interesting with it? And I firmly believe that you can actually do get a pretty good start with just two data sets like with all the setup that I show we showed to you. I prepared a couple of queries. one of them on the sakila and why doesn't it allow me to open a query just open SQL script okay it's a different it's a modificator okay so this query it will show me the number of times uh a person sila just a step back sakila is a database that models a rental shop think Netflix in I don't know 1997 or maybe whatever uh it's like a video rental company. There are films, there are actors, uh I believe there are directors and also there are people who actually rent discs from shops or VHS cassettes. And so this query for a customer for for a particular customer who we want to check with Wesley, it will give us the number of times uh they rented films broken down by categories with a particular emphasis on showing all the categories even those that they have never rented anything from. And so when you deal uh something about workbench before we run the query you have to it highlights the current schema and there is a bit of a concern with word schema. Schema means uh a few things in databases as usual but schema is a definition of a table kind of what are the columns in the table. schema also means a logical schema as a logical schema of the database as a container of tables. So it would usually I think it would be better to call this databases but okay schemas. So if I run this in employees I get an error. I have to double click Squakila and then it will execute and it will tell me that Wesley probably likes game movies a lot also some foreign documentaries but he doesn't like action and doesn't like sci-fi so as far as simple queries go this one is assembled from a really few concepts really simple basic concepts but it's like it's not as straightforward to come to as you can imagine as you might imagine so this is sakila query I also have one more for the employees which is a bit more advanced, a bit more interesting perhaps. This one uses MySQL 8 features of the common table expressions and the window functions. And what we do here is we use common table expressions to make our query nicer. Disregard performance for now. uh to make our queries nicer uh to just not have a select and then like a lot of text within it and we use the window functions to analyze data and to get some interesting data points here. So this is a really basic example but what we do is for all the current employees we want to see employee first name last name their title as a job title their department their salary and also make some analysis on their salary. In particularly, we want to see how their salary relates to all the other salaries in their department as in percentage of values where 100% is the top top salary and 0% is the lowest salary. And also we want to see the minimum and average salary per the department on every row so that we can pick a a we can pick an employee and we can immediately see uh what's his salary but not not only just the value of his salary of their salary but also the uh how it relates to other salaries in the department. You could also do a breakdown for all the companies but you don't even need window functions for that. So uh it's slightly more interesting for the department. So again I will run it. It again failed because I'm again on Sakila and this is for employees. I go to employees. I run it and maybe it takes a while. As I said employees it looks little but it's actually quite a lot of data. And this is my data. We can see that probably it's nice to be a senior staff in sales to be honest in this particular company but you can see that three people in sales earn quite a lot of money and they are like the 100% they are the bucket number one of the salaries and as you scroll down you will start finding other departments and the salaries will decrease and so on and so forth. So uh maybe we can do like sc to showcase it a bit differently. But again this is talking about SQL and we aim to talk about how to get dirty with it. And so salary PCT zero means the lowest salary in this particular department. And you can actually see that it's the minimal salary. All right. So as far as live demo goes, this is the last one that I had. Surprisingly, nothing broke much. Uh I will stop sharing. And Vinnie, do you want to close? I think we have a couple of questions. Um yeah, the Windows Installer MSI installer list applies. So let me just answer that real quick. Installer for Windows, you have Yeah. Uh I think I answered this live but yeah I will mention it again. So this one it says installer web community. What what this what will happen is one when you run this installer it will download MySQL for you while installing with this installer. The MySQL server is actually embedded like you cannot get MySQL magically from five megabytes. you will still have to get all those all those megabytes. It just it will happen later. This is useful if maybe you just want to pass installer somewhere but that somewhere has an internet but you don't want to remember where to get this. So uh other than that maybe a bootstrap like but yeah I don't even know why would you use it. The question is is there a web app? Is there a portable app uh for MySQL? Actually, a good question. There probably should be like a ton of web apps to interface with MySQL. There are a ton of web apps in general to interact with the RDBMS using SQL. Uh like on top of my head, I cannot remember a web app, but there should definitely be something. You can hit us up on our links and we can find you some examples. Perfect. And there was one question in the chat about your book. Um, uh, so they want to kind of have you guys give them a 30 second pitch on why should they buy your book versus, uh, I think there's there must be another similar one out there. Yeah. Well, there are a lot uh of books, but what I liked, not only because I wrote it, but uh, I had the whole years of experience how I suffered to learn uh, and to reach where I am today. So like the I wrote the book thinking about okay if I wanted to start my career today what kind of information I would like to have to make my path easier to get to the top. So I think that's how the book is structured. Uh there is much more content that what we spoke here today. It goes really deep. uh apart it is saying learning my SQL but uh there are some complex topics like database in production monitoring which Sergey can talk more about. Yeah like my 30 secondond pitch is that it's actually called learning my scale but while writing this book I had a feeling that it would probably benefit from being written learn databases with MySQL. So this book is uh it has kind of a uh multiple approaches. You do learn my scale as you go and you can use it as sort of a reference going back to relearning about my scale topics but also if you like just want to know about RDBMS in general. Uh this book by giving you example of MySQL gives you a nice foundation for any database out there. It doesn't go too deep into my skill specifically and where it goes it makes we make sure to be comparing it with something different like maybe posgress scale or Oracle. So yeah and you can get it from O'Reilly or from Amazon uh ebook and the paperback. So uh Vinnie is it you jumping around? Yeah. No, and just as as you said like if anyone is interested in this just one point Sergey said uh the book gives foundation for SQL database post Oracle SQL server we're not going to use this book to learn MongoDB or Cassandra like then it is not related again like we are hiding if someone is interested in this word uh we I mean peron because me and we work at different companies so I leave him to advertise eyes peace s you want to say a word or two yeah I wrote about our company here and like you so perona is a doesn't you don't know what it does you can learn about what we do here it's just it's a portal where you get financial markets overview where you get quotes news indicators technical analysis whatever what have you uh we do have open positions uh so if you want to explore look around uh come see. I think that's it. Sounds good. There is one question left. Uh it's actually a good one. I think my scale has the geospatial data. Uh I personally have never I have I've been lucky or unlucky as you wish to have never been working with geospatial data g whatever so like I haven't touched it but I think there is I at least I saw briefly that there are geospatial data types in my scale so maybe you can do something with GIS data but u yeah you will have to check about a a bit about it. Okay, sounds good. And as far as basic geometry goes, Sean, uh, it should Yeah, it should do that. Like more advanced stuff, I'm not sure. Just but it should work. Yeah, perfect. All right, so I am going to take the screen from you because I am gonna uh close us out here. Um, so thank you all for joining today. Big thanks to uh Sergey and Vinnie. Um,