SQL (Structured Query Language) is a standardized language used for querying, inserting, updating, and deleting data in relational database management systems like MySQL and PostgreSQL. SQL operates on tables with columns (fields) and rows (records), where data is organized in a normalized form across multiple related tables. Key SQL operations include: creating databases and tables with defined data types (VARCHAR, INT, TIMESTAMP, DATE), applying constraints (NOT NULL, CHECK, PRIMARY KEY, FOREIGN KEY), inserting data with INSERT INTO, querying with SELECT and WHERE clauses, updating with UPDATE, deleting with DELETE, and joining tables with INNER JOIN and LEFT JOIN. Advanced features include aggregate functions (COUNT, SUM, AVG, MIN, MAX) with GROUP BY for data summarization, and the HAVING clause for filtering aggregated results. SQL supports text pattern matching with LIKE and enables complex data relationships through foreign keys and linking tables for many-to-many relationships.
SQL Crash Course: MySQL & PostgreSQL Fundamentals
Added:this course has quite a lot of content it's a big course because we dive deeply into all these core sql concepts now this course section here is a course section where we summarize and also practice what we learned but maybe you also reached this course section because you skipped the majority of the course because we told you in section one of the course that this is an option and you just want to get that quick overview of the key concepts and the key sql language and syntax and that's also fine because that's also what this section is there for as an alternative for going through the entire course if you just want a general overview without diving into all the details or as a summary and practice section if you did go through the entire course up till now because in this course section we are going to dive into a complete example project which we're going to use to learn about all these key features and summarize all these key sql features and also of course to practice them and we're going to have a look at how we can create tables structure data for tables split data across multiple tables and then of course we're also going to see how we can insert and manipulate data and most importantly how we can query and select and get and filter data so this section is packed with content it's a great summary and overview so let's dive right in and let's start by again having a look at the most important question first what exactly is sql or sql so what is sql sql is a language that's used for querying data for working with data to be more precise for working with data that typically has a clearly defined structure and that is split across different tables which then might be related and that's also important tables sql is used for working with data in a tabular form so stored in tables where every table has multiple columns and different rows and for example here on this slide you see two tables the employees and the departments table and the idea here is that we split those different data entities into different tables which then still in the end are of course kind of related since we have a logical relation between employees that might be part of different departments and sql is a language that can be used for selecting data from those tables for merging data from different tables into one result set that's not all we can do with sql though even though that is probably the most prominent use case for sql querying data and filtering and merging data and so on but sql can also be used for inserting updating and deleting data so you have all these crud operations in there create read update and delete and sql can also be used for creating the databases and those tables in those databases in the first place and then in those tables we have those columns and rows as mentioned and sql when used for creating a table is also used to describe that table to clearly define which columns go into a table and which kinds of data can be stored in those different columns because this clear structure this this structure to which all the data that goes into a table has to adhere is a key feature of sql or to be more precise of the relational database management systems that are the tools that use sql internally so the software which you would install and which we will install in this course is such relational database management system software and examples here would be mysql or mysql as it's also called postgresql microsoft sql server and others these are the tools that use sql internally because these are the tools that do actually store the data that do actually organize the data into tables sql is then just a language for interacting with those tools and the data they hold and by the way because i already said it you can say both sql or sql sql is the more official way of referring to it since originally in one of the first drafts for the sql standard it's a standardized language sql was an acronym for structured query language but this was dropped at some point and they just kept sql as a name for the language as a whole but it's also often called sql and indeed we will call it sequel for the majority of this course simply because that flows a bit nicer it's a bit easier to incorporate into sentences and out there in the wild you will hear both sql and sql and it means the same thing it means this language now core characteristics of this language or of the tools those relational database management systems that use the language are that we store data in this table format that we have multiple tables typically and that data is stored in a normalized form as it's called which simply means that individual data entities are extracted and instead of putting everything into one big table we split data into different tables which are then connected through logical relations which can be picked up and queried in the sql language as you will see throughout this course sql gives you all the tools you need for connecting data that's stored in different tables and that is related now when we come back to those tables we have two main things that make up these tables that make up any tables in the world we have columns also called fields and we have rows also called records or entries now the idea really is when you work with relational database management systems that those columns are clearly defined when you create a new table in a relational database management system with sql you have to define which columns that table will have and which data types will be used for those different columns so whether a certain column holds a number a text or any other kind of value and then any data that's inserted and that would be the different rows then multiple employees take up different rows any data that's inserted has to adhere to this column structure so the columns define the features of the rows so to say salary and first name and so on would go into different columns and the different employees then so the different people that work for us would go into different rows that's how data is organized and sql is the language that helps you query that data and work with that data in general so now we know what sql is and that we typically use it when working with relational database management systems now i do want to mention that sql is not limited to those relational database management systems it's the most prominent use case but you can also use it with other software there are other tools out there for example some data science frameworks that also leverage sql as a query language under the hood but the most prominent use case are those relational database management systems but it also doesn't matter too much because sql generally is standardized i'm saying generally because those different database systems and tools bring their own slight variations but it generally is standardized and therefore in this course you will learn sql in general and you will be able to apply your knowledge no matter in which environment you then have to use it we will focus on mysql and postgresql as example database management systems in this course and we will highlight differences between the sql commands when working with these two systems in this course but in general as you will see in this course the language is the same no matter which environment you're working in and therefore you will be able to apply the knowledge you gain from this course in all those environments now even though sql is not limited to just being used in relational database management systems it is the most prominent scenario the most prominent use case as i mentioned and hence an important question that might come up and that has to be answered is why exactly you would be using such a database management system and the answer here is that there are two main reasons for doing that a you might have some applications some transactional data that needs to be stored and handled or b you're doing some data analytics on some data that should be stored and i will also say that there isn't a hard border between these two application areas instead of course you could be analyzing data that is generated by an application but these are the two general categories the two general use cases we might be able to differentiate now when i say transactional or application data i simply mean data that is generated by users using your mobile app or website so they are interacting with your site and behind the scenes data is generated and stored for example if users are buying a product in an online shop then that order has to be saved in a database so that you can process and fulfill it and that's just one example and of course most websites or at least most big websites do generate some kind of data that should probably be stored and handled and then you might also want to analyze that data or analyze data from a different data source you might want to do that and there are all kinds of sources you could think of you could be analyzing the sales of an online shop but maybe you also have some weather data that's generated by a weather station and since most businesses do gather data and want to analyze data that's another very popular application area for relational database management systems and therefore for sql now there are alternatives to those relational database management systems and to sql like mongodb but that's not the focus of this course we got a separate course on that if that is what you would be interested in but since you took that course you probably aren't now that's why you might be using database management systems because you have data that should be stored and managed now there are a couple of terms that are also worth clarifying to avoid confusion we do have those database management systems for example and as the name suggests these are systems software that helps us work with databases or that manage databases for us and indeed if you install such software as we will do in a couple of minutes you get software that gives you a database server also sometimes called a database cluster so that's typically what you start when you use such database management system software when you installed my sql or postgresql and you start it you typically start such a server on such a server you then can have multiple databases for example if you had different applications every application might get its own database and the data in those different databases can be related to each other but often won't be related at all now what is related however is the data that's stored in different tables and that is what goes into those databases every database can hold one or more tables up to dozens or hundreds of tables of course and then in those tables we have our columns and rows that make up the table so that's then the actual data that is stored inside of the table and you can keep this big picture in mind to avoid confusion now throughout this course we will sometimes say database when we actually refer to the database management system simply because it's a bit shorter but it will always be clear what we mean simply from the context in which we use those different terms but that's the big picture that's why we would use database management systems and sql simply is the language used by those database management systems to help us interact with the data manipulate the data set up the database and its tables and most importantly query filter structure and merge that data and that's what we will learn in detail in this course so now that we know what sql or sql is and what databases are or database management systems before we start writing some actual sql commands on our own let's have a look at the general sql syntax first so that we understand how a sql command looks like and how it works and for this let's have a look at an example sql statement or command so this overall thing here is typically called a command or statement and this example statement here for example would give us all the employees so all the rows from the employees table to be precise that have a salary greater than eight thousand so we would have a look at the salary column and filter out all the employees that have a value greater than 8 000 in their salary cell so in their column row combination for that salary column and then we retrieve all those rows all those employee records that meet that condition and we then get the name and salary column to show that in the final result set that is what this command would do and now let's have a look at the general syntax that we have here for example this command ends with a semicolon and all sql statements slash commands do end with semicolons you can often omit them if you only execute one command but including the semicolon is never wrong and will save you some trouble if you do execute multiple commands in one go which is something you can do you can for example insert an update in one go insert data in one table update in another table you can execute both commands together and having that semicolon then would be mandatory so always including it is never a bad idea now in front of that semicolon we then have multiple words that make up that overall command and we call those words tokens which is just a fancy way of saying words so these different tokens make up the overall command but not all words have the same meaning for example there are certain keywords that are part of the sql language keep in mind that sql sql is standardized even though different database management systems bring their own variations but it generally is standardized and there are certain keywords that are locked in so certain keywords which do special things for example the select keyword tells the database management system that you want to select fetch some data you want to get data from a database table you want a query data as it's also called the from keyword would then specify from which table you want to get data and the where keyword would specify which filter you want to apply having a filter is optional by the way but often you will use one but we'll see more practical examples in a couple of minutes now we also have tokens here that are not keywords for example we have a couple of so-called identifiers here for example we would have name salary and employees as identifiers where employees in this context identifies a table we want to get something from the employees table and name and salary in the where part here and in the select part in the beginning do identify columns from which we want to get data or which we do want to use in our where condition so these are so called identifiers now that's still not all we see here though we also have greater than eight thousand but before we come to that there is another term another thing you should know about and that's the term clauses in this overall sql command the sql statement that is made up of those tokens and identifiers and keywords we have different clauses for example the select keyword combined with the identifiers that belong to it makes up the select clause from combined with the identifier that identifies a table makes up the from clause and where combined with the tokens that make up the condition forms the overall where clause so whenever you hear the term clause for example from clause it refers to the from keyword being used with a table identifier that points at a specific table by the way in case this is overwhelming right now you will hear these terms over and over again throughout the course and the more you work with sql the more you will get familiar with them you don't need to memorize this by heart right now you just should maybe keep this slide in mind so that you can come back to it whenever you hear the word clause and you're not sure anymore what it meant now that being said we're not done though because we also have the greater than sign and the eight thousand now the number eight thousand here is a so-called value or expression because we don't point at a specific table or column or anything like this instead we're saying hey i want to have this fixed value in this case as part of this where condition you can also use values or expressions in other parts and we'll dive deeper into that later in this course and of course in the different sections that make up the whole course but you use such values for example when filtering for certain records that should meet a certain condition well and when we talk about filtering there we also need operators like this greater than sign that allow us to define what our condition is for example that we're looking for salaries greater than eight thousand you also have other operators you can look for equality and inequality and so on but again we will see examples for that throughout the course and that's the general sql syntax you should be aware of now of course as mentioned we're going to write many sql commands together over the next minutes but this is how sql will work and before we write any sql commands together let's now set up an example database management system so that we have a database with which we can work and against which we can run our sql commands if you want to follow along with this course and with the commands you will learn about over the next minutes you should of course have some database some database server to be precise against which you can run these commands so where you can create tables and store data of course you can also just watch the videos without following along but if you do want to follow along here's the setup we will use over the next minutes we will install a database system server postgresql and mysql are the example database systems we use for this course so that's what we will install and then these database systems this software which we install gives us that database server which we will be able to start and which needs to be up and running for us to talk to it and to interact with it and store data and so on and we will also have a client software which we will also install that allows us to interact with that server so the actual software where we will insert and enter our sql commands which will then be sent to the server for the server to really execute them and do something based on them so we will have this client server communication now as mentioned for the server we will use mysql and postgresql for this course when it comes to the client mysql and postgresql actually come with their own clients which we could use so there's a broad variety of clients we could use but in this course we will actually use a third-party software and extension for visual studio code to run commands against the database management servers and the reason for using this extension in visual studio code which is a coding editor which you would use for programming in other languages as well is simply that this makes recording the course way easier because we can see these commands with some highlighting keywords are highlighted for example and we can also store these commands in files which then makes it possible for us to make these files available to you so that you can also download the commands we executed and try them on your own compare them to your commands in case you're facing any errors and so on so you could use different clients but we will use this visual studio code extension and therefore we got a couple of things to install we have to install the database systems and we have to install visual studio code with that extension now if you want to follow along we do have a separate video that shows you this exact setup so my sequel and postgres sql with this visual studio code extension through which you can go to have the setup we will use in this video here we didn't include it in this video to not unnecessarily bloat the video and therefore below the video in the video description you do find a link to this extra video in case you want to go through that setup and follow along with the exact same setup we have here alternatively you could of course also just google for mysql installation and then go through the installation instructions you find here on the official web page for my sequel however you want to make sure that you're using the the community edition which you find on the downloads page down here so you want to use that community edition because that's free and you want to download that server and maybe also the mysql workbench which is a graphical user interface for interacting with that server and that shell which is also a client you could use instead of visual studio code in the editor and you can also of course install postgres sql on your own without any extra instructions by simply googling for postgres sql installation and then as for mysql you will find some official site on the postgresql site where you can download it for your operating system and go through the installation steps mentioned there so that's of course an alternative to going through that process together with us if you also want to use visual studio code you can also download that here on code.visualstudio.com install this editor it's totally free and then inside of visual studio code go to the extensions area and install the sql tools extension which is the extension we will be using and also install the mysql and postgresql drivers for this extension so that you will be able to connect to these database systems with this extension and then you will have the same setup as we have it here for discourse and with that out of the way now assuming that we are all set up let's finally dive into the example for this project here and let's start writing some sql commands now as mentioned before for this section we will have an example project simply so that we have some dummy project for which we can create our dummy data and with which we can work and the example project i came up with could be this fictional app where we for example allow users to find events and register for events and other users might be able to organize events now this app doesn't really exist it's fictional there is no code for it because this course is not about programming such an app we do have different courses on that like the flutter course and so on but instead here we will focus on the behind the scenes database operations that would be needed to bring such a app to life so that would be caused by such a app behind the scenes now for this app here we could have a starting screen where we for example have a list of events and we can also search events we can add filters let's say and when we tap an event we could be forwarded to a detail page where we have more data related to the event like the exact date and time when it's going to occur a maximum number of participants a minimum age that might be required the exact address of the event a description text and then a button for registering that could be that fictional app and which core features it offers so therefore what we will need to take into account from a database perspective is that we need to be able to search events and that we need to be able to place various filters more on that in a second that we need to kind of get a list of events or a list of filtered events that we also need to be able to filter events by organizer because if you organize an event you will have certain events attached to you so we will need to model this relation between event organizer and event and of course we should be able to then also find all the events that belong to an organizer having organizers of events also in the end means that we need users so we need to be able to store and create users we also need to be able to fetch various details for a given event so instead of for all the events for a specific event which we for example identify by id and of course since in this fictional app users should be able to register for events we of course also need to come back to that idea of having users and differentiate between regular users who signed up for an event and organizers who are all the users but users who also organized an event so these are all the aspects we should take into account when we dive into creating tables in our database and when we then later of course also store data in those tables and when we then query that data so to sum up this project and its capabilities and the database features that we would need for the database of such an application we want to support browsing for events we want to support viewing event details and we have users registering for events as well as organizers and then we have different pieces of data that belong to these different capabilities for example when we browse list of events we might want to support searching by name we might want to support filtering so that we for example are able to find all the events that will occur within the next seven days and so on for the event details we might want to store the image of an event the name the date and time the location and so on and for registering and for organizing we might want to store the full name and birth dates of our users their emails and for organizers maybe also a password because for registering you maybe don't need an account just registering as a guest might be enough but if you organize events you should probably also have an account with that application and therefore then we might want to store both the email and a password so these are some capabilities and features and data pieces that we might need for a database that could belong to this fictional application and of course that is just what i came up with if you think about that if you plan your own database for your own application you might come up with more capabilities or different capabilities and you might come up with different pieces of data and the different features that you want to support and these are definitely also things you can explore then as a practice definitely play around with that and come up with your own application but that's what i will focus on throughout this course and therefore as a next step i want to think about the tables we will need for this application so the tables we would need to create and the structure of these tables before we then we'll of course also insert some dummy data and also learn about how we could manipulate that data so how we could update and delete data and before we then of course also have a look at how we could query data so how we could select data and how we could also write more complex query statements where we also merge data from different tables and where we also might have complex filters these are all the things we're going to tackle next now let's start with planning our data entities so the different tables that we need and relationships between those tables that we might require and for this our first goal should be to identify the main data entities that we need for this kind of application so for the database that belongs to this application because every data entity then should go into its own separate table this is a process that in the end can be summarized as data normalization and data normalization is super important when working with sql and relational database management systems the idea behind data and normalization is that you keep your data organized and maintainable by doing two main things first you want to avoid having compound column values which simply means that instead of having a single full name column that contains both the first and last name in one cell you would prefer to have two columns one for the first and one for the last name this ensures that data is always stored in exactly the same way if you had just one full name column then you could accidentally sometimes store a value like max schwartz so first name first and last name second and sometimes schwarz comma max where you have a reverse order and maybe even a comma after the last name and to avoid this inconsistency where you express the same value differently in the same column you would try to avoid such compound values and instead go for multiple columns where every column holds exactly one value per row that is a core idea one value per column per row and the other key aspect of data normalization is that you split your data across multiple tables instead of having all the data in one at the same table so you are looking for a granular split amongst your data entities and you're trying to identify the different data entities that form your overall data so that you can store them in separate tables so for example instead of having an events table that contains both the name and then different columns for different aspects of the address you would instead go for two tables one for the events one for the addresses and you would then link these tables together and of course you're also going to see how that linking would work over the next minutes now in our complete sql course we of course also have a way closer look at data normalization and we dive deeper into that as we dive deeper into all the key features of course but this here is in a nutshell what you should know about data normalization and why you do split your data across multiple columns and then also across multiple tables so with that out of the way we can now start to identify the data entities that make up our application that i showed you before this events application so that we can then plan the tables that we will need for these entities so which data entities do we have and which tables do we therefore need well we will probably have some events with which we deal and therefore an events table probably makes sense we could then assign a unique id to every event because having unique identifiers in tables so that every row in a table has its own unique identifier is a great idea since it will often help with querying for example it would make it easy for us to find a specific event by its id if we need the detailed data for a specific event and unique identifiers like ids also allow us to later link records from different tables together as you will see so therefore we might have an id column in the events table so that every row every event has its own id and we then also might want to store the name then the date and time when an event occurs the image that maybe was uploaded for a specific event and also a description the maximum number of participants and then maybe also the minimum age and you could store more data but i think that's a good chunk of data that could make up an event now what's not part of the events table is the address the location of an event because that's what i meant we would typically store something like this in a separate table in a locations table so a location is a separate data entity you can think of a house being a separate thing it's detached from what happens in the house an event is one thing a house is another thing if an event takes place in a house that still doesn't make the house a part of the event once the event is over the house still is there so that's how you can think about that and why you can consider locations as standalone data entities but now of course a location should be related to an event so we need some kind of relation and indeed when working with data especially in the context of relational database management systems relationships are everywhere and there are three main kinds of relations we can differentiate there are one-to-many relations many-to-many relations and one-to-one relations now we have a one-to-many relation if one row one record in table a can be related to multiple records in another table but those other records in that other table are only related to one record in table a at most an example here could be an organizer of an event that may organize multiple events but every event only has one organizer so that's one kind of relationship that we could have between different data entities and it's a very common one but another very common kind of relationship is the many-to-many relationship there one record in table a could be related to multiple records in table b and the other way around it's the same one record in table b could be related to multiple records in table a for example we could have an event that has many participants but every participant every user could of course participate in multiple events now a bit rarer but still also common is the one-to-one relationship there one record in table a has exactly one related record in table b and vice versa here an example could be an organizer that isn't just a regular user and a user can of course also just be one organizer because it's one of the same person but we still might store the data in separate tables to have a separation between regular users and users that are organizers and with those relations in mind if we're back here on this slide where we are planning our tables it's fair to say that between locations and events we would have a one-to-many relationship because one event has exactly one location but of course one location could then be used for multiple events and with that in the locations table we then again might add an id so that every location has a unique id that can then help us with setting up these relations later and we might have a title of the location if it has a specific name and then most importantly the street the house number of that location in a given street maybe a postal code and then of course probably also a city maybe even a country but for simplicity reasons we'll ignore the country here and just focus on the city now we could add the city as a column in the locations table but we could also treat that as a separate table we could manage our cities in a separate table why might we want to do that well for one it's probably fair to say that a city is a separate data entity if you think about it and b and that's also an important reason we might want to standardize our cities so that it's always munich and never muc which is the shortcut for the airport in munich or anything like this and therefore we might want to manage our cities in a separate table and then link our locations to that table again with a one-to-many relationship because one location belongs to one city but one city of course can host multiple locations and every city then could simply be a name and we might not even have a separate id column here we could add one but we might not even use one instead the name will be a unique identifier but still we have that simple separate table for the same reason you could also add a separate streets table but for simplicity here and to not make this too complex here i'll keep the street in locations we also might not worry too much about different ways of riding the same street but i will outsource cities into a separate table now another table we might need could be a tags table so that we can assign tags to our events of course we could have added a tags column to the events table but of course one event then often will have multiple tags and we might want to standardize those tags just as we wanted to standardize the cities and in addition we want to avoid compound values in our column cells so if we had a tags column in the events table and one event had multiple tags of course that would have been multiple values for a single column in a single row and that is what we typically want to avoid therefore we might add a separate tags table and the that tags table would be related to events through a many-to-many relationship many too many because one event can have multiple tags and one tag can be assigned to multiple events and then we could simply again store the name of a tag in that tags table but having that separate table gives us that normalized data so as you can tell by now we already got a couple of tables for storing the events now of course we also have users because users can sign up for events and therefore we have a relation between events and users because one event can have multiple participants multiple users that's signed up for it and one user can of course also sign up for multiple events and for every user we again might have a unique id then maybe the first and the last name the birth date and the email address let's say of course some users also are organizers and to also reflect this fact in our database we could add a separate organizers table which is related to users through a one-to-one relation because if a user is an organizer there is only one entry in the organizers table for that user and on the other hand an organizer is only related to exactly one user and therefore we just store some extra data in the organizers table which is not stored for regular users and that would be the password so since we have the relation an organizer in the end gets all the user data because an organizer is related to a simple user but we also store the password in addition and we could store more organizer specific data if we wanted to but i'd say for this example this is already enough and gives us plenty of aspects to dive deeply into sql last but not least it is of course important to recognize that organizers are also related to events through a one-to-many relationship because one event has exactly one organizer but one organizer could be organizing multiple events and therefore that's now the database structure i want to implement together with you these are the tables we will set up and for that we will of course create the tables create the columns pick appropriate data types for the different columns and then insert some dummy data and play around with that data and run a couple of queries including some more complex ones to learn how we can use sql so it's finally time to get our hands dirty and for this here i'm in visual studio code i got that sql tools extension installed and installed the my sql in postgres sql drivers and that's always important i got my mysql server up and running and my postgres sql server up and running and then you can add a new database connection here to connect to these databases i showed how this works in the setup process and once you did that we can start writing some commands and the first command i want to execute is the command for creating a new database because you can do that with sql as well you can not just run queries to insert data you can also administrate the database with sql though that's not the focus of this course but creating a database is still something you should know how it works for this i'll add a new file here and i'm storing the different steps in different files simply so that i can make these commands available to you and i'll name this first file zero one create dash database dot sql the extension is kind of important because this will then be recognized automatically by the sql tools extension and we can then click on this button here to run the command which we are about to write here and the command which i do want to write here is the create data base command and this does what the name implies it creates a new database and now you just need to add the name of the database so the identifier of that database that you want to create here after create and i'll simply name it my events you could use your app name here if you had one just like that and then a semicolon now regarding the naming here the convention is to use lowercase names and if you have a name or a word that's made up of multiple words you don't add a white space this would cause an error because this would then be identified as two tokens as two identifiers instead you use an underscore to combine these separate words into one word again so that's how you name your databases but later also your tables and your columns that's the naming convention and then we can run this against the active connection which in my case hero's postgres but if i quickly switch to my sql by clicking on it here and i run it here that also works as you can see so now that database was created now the connections which we have here are actually connected to a specific database and that's not the database we just created therefore now that i did add a new database here in this extension i have to edit my connection and actually switch to a different database in this case the my underscore events database i do that here for mysql save that and then connect again and i do the same for postgres sql i edit the connection swap the database name for the name of the database i just created and then of course i also save this and connect to that we only need to do this once now that we created the new database we of course don't have to do this after the other commands which will execute and with that we already did the first important thing we created a new database next i want to create these tables and that's then also where we will dive into the different types of values we could be storing and where we will also explore how we do set up relations between tables so i will add a new file create tables.sql and in there i'll do what i just said now we did plan our database structure and the tables that we want to have before and i'm now simply going to translate that into some sql commands so that we do create all these tables and for that we can create a table with the create table command just like this now just as create database this command then wants an identifier that identifies the name of the table that we want to create and why don't we start with the events table here therefore i could use events as the name here now again a name about the wording here it should be all lowercase and one word only and then when it comes to tables there are two main conventions which you will see out there you sometimes see table names that just pick up the data entity name so the name of the data entity that will be stored in that table and in this case this would be event because we're storing single events so just event without the s alternatively you also often see the approach here where you use the plural form so where you add an s at the end for example that's the approach i personally prefer but you will see both out there and both are fine now if you should come up with a name that also is a keyword for example if for some reason you would want to name your table create then you can't use it like this as a side note sql is not case sensitive so if you write create like this all lowercase or like this or like this all doesn't make a difference it's three times the same command it's just a convention that we write those keywords all uppercase to make them more visible but you would still have a naming clash even if you write it lowercase because there technically is no differentiation and because that's the case if you would want to use such a reserved name as a table name or also as a column name you can wrap it between double quotes this is something you can do to tell sql that that's the name of the identifier you want to use but that it's not to be confused with a built-in keyword so double quotes can then be used when working with mysql you can also use backticks like this this then also identifies a token as an identifier instead of a keyword if you however have a name that isn't a keyword in the first place like events you of course don't need to do that and there was a lot of talking about this name alone but that is important to know of course so that's the next step when creating a table giving it a name it's not all though when creating a table we also need to define the structure of the data that should be stored in the table because that's a key aspect of sql and relational database management systems data is organized and has clearly defined structures it's not up to you to throw any random data into a table instead you define a table structure once and you can of course edit that structure thereafter if you need to but if a structure is set all the data that is inserted has to adhere to that structure and therefore it's this structure that has to be defined next for this you add a pair of parentheses and then between those parentheses we define all the columns that should make up that table now we can split this across multiple lines for better readability or if we write it all in one line just like the casing this doesn't matter here because sql will treat this all as one command no matter if it's all written in one line or if it spans multiple lines it's the semicolon that ends a command not a line break so therefore here i'll actually split this across multiple lines to improve the readability and i now do want to write down the columns that make up the events table and for that we could start with that id column which i also outlined on the slide before this name is totally up to you all the column names are up to you but it's kind of a convention to add an id column that's called id and that stores a unique id for every record not every table must have such a id column you could also have a never column that acts as a unique identifier but every table should have some unique identifier and very often that will be this id column so that's the first column we can add and we can add a comma thereafter to then specify the next column though i will say we're not entirely done with that column setup at this point but let's still move on to the next column that could be the name of an event and therefore we could name it name now here it's actually highlighted in blue as a keyword but it isn't a keyword here and we can actually use name without issues as a column name here now next we might want to store the date for which the event is planned and hence i'll use date planned here as a name and thereafter i want to store the image that was uploaded we also have a description for the event so some detail text and image and description also aren't keywords here so we can use them without issues and we might want to store the max number of participants and maybe a min age and for the moment that's it now you should make sure that you don't have a trailing comma here because that would cause errors if you then try to execute this later and hence you should remove this but at this point we won't be able to execute this anyways because the column names alone aren't enough we also need to define which value types should go into these columns because it's not just the number and names of the columns that are set up in advance it's also the types of data that can be stored in these columns that have to be set up in advance and therefore that's what we'll do next now when it comes to data types sql has a lot of supported data types though the exact names and the exact number of supported value types always depend on the relational database management system or the software in general that you are using for example postgres sql and mysql don't have exactly the same supported value types that being said there are some value types like var chart text ind numeric that are supported in basically all sql environments the same is true for date and time stamp we also see boolean quite often now here is an overview we're not going to use all these types right now and if you want to find out which value types are supported by a specific database management system it's always the best idea to dive into the documentation of that database management system if you search for postgresql data types for example you will find a link to the official docs and you might want to select the latest version then the current version and there you find a list of all the supported data types and here it's a pretty long list because there also are a lot of niche data types which you will probably rarely use but there for example are the numeric data types which are useful for storing different kinds of numbers the difference between these types is the range of numbers that can be stored and then for numbers with decimal places there also are differences regarding the precision we dive way deeper into that in the full course for the moment we just need to know that there are different types and we'll see some types in a couple of minutes but the official docs are always the best place to go to learn about all the types that are supported by different sql environments and with that in mind when we come back to our table that we're creating here let's start with the id column what's the type of that column well it should be a unique identifier and there are different kinds of unique identifiers you could come up with for example we could simply have incrementing numbers so that the first record has an id of one the second record has an id of 2 and so on we could also have strings here or anything like this for sql it is quite common to use incrementing integers here integers are numbers without decimal places like 1 2 3 4 and so on and we would turn this into a ind by simply adding int after the column name so column name white space value type that's how you configure the value type for a column when creating a table you could also write integer both is supported by most database environments for postgresql and mysql they both support int and there also are different kinds of ins for example there is a big end which simply supports a bigger range of integers so if you know that you're going to store millions of events in there you might want to go for a bigint instead of a regular ind for example here for this demo a ind is enough though now that's not all we'll do with this id column but for the moment it's enough because we do set up the correct data type with that and therefore we can next move on to the name the name of course is some text it's the name of an event so it's some text that was entered by some user now there is a text data type available but we typically use that for longer texts and the name of an event is probably only a couple of dozens of characters long for that we instead have the var char which stands for character varying which is the data type you use if you have only a limited number of characters that should be stored you add parentheses after the type name and between those parentheses you enter the size of this column in bytes so for example 300 and in most cases one byte represents one character it's not always the case there are some characters which take up multiple bytes so you should also consider some threshold here but in general you can consider one character to equal one byte so varchar 300 would be able to store text that's up to 300 characters long and that sounds like a suitable data type for this name column here now date planned should be a date and indeed there is a date value type which is supported in basically all sql environments but here i don't just want to have the pure date but also the time on a day and for this we instead can use the timestamp data type which is also supported by mysql postgresql and most sql environments timestamp stores both the date and the time of the day and that's what i want here now the next part is interesting though that's the image how do you store a file in a database when it comes to storing files in databases the simple rule is that you don't store files in databases for example when you have a website a web application that's running on a server and that's talking to your database server so for example when you're building a social network application you have a social network website and the database that belongs to it then you might have a users table there and a post table and other tables and users of that website might be uploading files for example profile images or images that belong to posts and you might then wonder how these files should be stored and as mentioned the simple rule is that you don't store the files in the database because a database is a system a tool for storing pretty simple primitive data text numbers and so on now files which are chunks of bits and bytes would not fit into a database you can't query them they take up a lot of space and they might slow down your database that's why you don't store files in the database but instead on a file system on a hard drive a hard drive that might be part of that web server and you then only store a path to that file which therefore then is just some text in the database so that's the only thing you store about a file in a database the path to the file and therefore here for the image what we would store in the database again is a var char so that's the data type we need here because we'll just store some text here the path to the file and now the number of characters we want to reserve here and we want to allow here simply depends on the length of that path we would expect so maybe 300 is enough maybe you need 1 000 maybe 50 is enough that comes down to your use case here i'll go for 300 but it's up to you now let's continue with the other columns here for example the description the description again is some text but unlike the name or the image path this could be longer text we don't know in advance how long it will be and we might not want to restrict our users for this we have the text data type which we can use that's the perfect data type if you have long text where you can't predict the length now when it comes to the maximum number of participants that again is a number now we have various numeric data types as you saw before but here it could again be an integer simply because we're not going to have 5.89 participants but we have 5 or 6 or 20.
so we have a whole number as a maximum number of participants and hence ind is the right choice bigint is probably incorrect it would just reserve some extra space in our database and hence make our data bigger than it actually is even though we don't need it we're probably not going to have events with multiple millions of participants and if we do we probably didn't have a max number of participants in the first place now for a min age it's the same that's an integer because we have a minimum age like 18 or 19 or 15 but probably not 17.8 and therefore that could be the value types we need here now in the full course we also have a more detailed look at the other value types and how they work but for this example in this section this already is a good mixture of important data types and it shows us how we use them still we're not done with the configuration of that events table having the column names and the data types is super important but it's still not all in addition we often need to add certain constraints as it's called constraints allow us to control which values might be stored in a given column the data types already give us some kind of control but maybe not enough for example regarding the age we know that an integer should be stored but at the moment we could store an integer like -1 by the way that is how you add a comment here in your sql commands you use two dashes and anything thereafter will then be ignored if you execute this so -1 could be stored here and at the moment that would absolutely be possible because -1 is also an integer now to avoid this we could add a so-called check constraint by adding the check keyword here after the data type but still when defining this column check allows us to define a simple check that should be executed for every bit of data that's inserted into this column for the check constraint you add parentheses after the check keyword and between the parentheses you have your checking logic for example here we could say min age greater 0 which means that every value that's inserted into the minh column is checked to be bigger than 0 and if it's smaller than 0 or equal to 0 it would not be allowed to be inserted and an insert operation that would try to insert such an invalid value would be blocked and would throw an error so that's a check we might want to add here of course we can also add a similar check here for the max participants where we might want to control that max participants is also greater than zero so that we can't set the max participants to a negative number or equal to zero so that would be an example for a constraint the check constraint is not the only kind of constraint we can add though another constraint that we can add is the not null constraint for example here for the name we added by adding not null as keywords after the data type for this column definition now not null simply ensures that whenever a new record is inserted into events a value for name has to be provided by default out of the box all these columns that don't have the not null constraint could be left empty so we could insert a record without specifying a value for date planned image description and even max participants and min age because the check constraint doesn't control whether we have a value at all it only checks the value if we do have one but it does not check if we have one in the first place now null is a special kind of value in the sql world which simply represents the absence of data so if no data is inserted into a specific column it holds the value null which means there is no data and by adding the not null constraint we make sure that this can't happen now whenever a record is inserted into events a value for name has to be provided that value could still be an empty string so that would be okay because that is some value even though it's an empty string just providing no value would not be allowed and therefore here i actually want to add not null for most columns i want to have an id so i want to add not null here as well and i also want to have a timestamp so i will add not null here as well and then we also might want to enforce an image though we might also be fine without an image that comes down to your preferences and here to mix things up i will actually not set this to not null so that not providing an image would be allowed the description however must be provided so i will set this to not null and then for max participants and min age it's again up to you as mentioned check alone does not do the trick here when it comes to null value so two missing values and therefore here we would have to add not null in addition if we would want to avoid that no value is provided but we could argue that we are fine with no max participants and no min age value being provided because the absence of these values could simply mean that it is an event that has no max number of participants and that has no min age and therefore here i won't add the not null constraint to these two columns but that's what you should know about not null now as mentioned before not null is one thing but for the name for example we could still insert an empty string like this it's not null but it's still technically not that useful therefore here we might want to combine this with an additional check constraint where we check the length of the inserted value however unlike before with a number we can't say name grader 5 for example if we want to say that the length should be greater than 5. this would not be evaluated correctly because it would try to convert name to a number and compare it to the number five and that of course would probably fail so what we want to do instead here is we want to call a so-called function sql has many built-in functions though it always depends on which database management system you are using which functions are available for example again if we google for postgresql text functions then we find all the functions that are supported on text or on strings as the official name is and here you find a list of operations you can perform on text and a list of built-in functions that you can use for postgres sql and of course you have similar functions for mysql in case you're interested in that here a function we could use is the length function and i'm again writing it all uppercase to make it clear that this is a built-in thing it technically could be written as all lowercase as well but i'll use all caps as i did for all the other keywords as well now a function takes parentheses and between parentheses you put the value that should be handled by that function in this case the name length executed on the name will give us the length of that text that is inserted here and then this length can be compared to this number because the length is a number and now we would have a valid check here that ensures that only sufficiently long text is allowed into this field into this column so in this case we would only allow event names that have a name greater than five characters long so at least six characters long and of course we also cover functions in greater depth in the full course okay so we're still not done with the events table but of course we already learned a lot about sql at this point about columns data types functions and constraints now to finally come a bit closer to the final state of this table let's have a look at the that id column again this id column should hold integer values which should not be null now i also said something else before which is very important the id here should be unique it should be the unique identifier of this table every row that's stored in this table should be uniquely identifiable by having a look at this id here and at the moment we have no guarantee that the values that will be inserted into this id column will be unique we could insert two events which both have the id one and that of course would be very bad because then we wouldn't have any unique identifier anymore to ensure that this is unique we can add the unique constraint here the unique keyword it isn't highlighted as a keyword here unfortunately but it is a keyword i can tell you that this will ensure that values stored in this column must be unique so two rows with the same id would be blocked it would not be possible to insert a row that has an id that already exists in that table and because this is such a common combination that you have a unique identifier which of course should not be null because you must have a value and that should be unique there is an alternative to writing it like this you can also write primary key though primary key is not just a replacement for the combination of not null and unique but it's also a special thing because every table should have one exactly one primary key so if you have multiple columns that are not null and unique you can still only convert one column into a primary key column because this primary key column will also become important later again when we talk about relations between database tables so primary key is another special keyword or two keywords which we can use to mark this as the primary key column and under the hood this will ensure that it's not null and unique so that's a huge step forward and of course you can't turn any column into the primary key column it doesn't have to be the id column it's just very common that you have such id column now when using an id column like this we have a problem though this would work and it would be unique but we still would have to insert a unique id manually for every event that's inserted and that also means that we need to keep track of the ids that already have been inserted before which can quickly become super annoying now therefore we typically want to auto-generate that id so that whenever a new event is inserted a new id is assigned automatically a new unique id i should say and because that's such a common use case we indeed do have a way for ensuring this automatic creation when working with mysql you can add the auto increment attribute here it's also not highlighted as a keyword but it is one however only when working with mysql in postgresql this is not supported and would give you an error but if you run this against mysql this would do the trick now if you are using with postgres sql or many other sql environments then you would use a slightly different syntax you would omit auto increment and instead of int you would use the special serial data type that's a special data type which also creates an int under the hood but an int that will be automatically incremented for you so this here is the postgres sql alternative to this approach actually my sequel also supports this but their serial is translated into a big hint instead of a int so the exact equivalent to this approach indeed is this approach in postgres sql and with that we now did finish our events table at least if we ignore relations for now because now we did set up all the core columns that we need and the different data types and constraints that define these columns now before we dive into relations and on really working on this example application i first of all want to work with that events table as we have it here and then dive into some first sql commands and queries that we can execute for inserting and fetching data before we come back to more table definitions so therefore what you should do now is you should make sure that you're connected to one of the databases here i have the postgres sql syntax for the id so i will connect to postgres and then run this on the active connection and this should succeed if you're getting an error make sure you closely compare your create table statement to mine here which you see here and which you also find in the attached code then we can connect to mysql if you're also following along on both platforms and comment in that mysql id version and comment out the postgresql one so that it won't be executed and then we can run this against the activeconnection to also create that table in mysql with that we got the first table now we'll insert some dummy data and manipulate that dummy data and also start diving into some data fetching tasks so now that we did create this events table let's start inserting some data for this i'll add a new file manipulate data dot sql and the goal is to insert data into the events table and also to change and delete it so that you see those commands before we thereafter also have a look at running query commands so selecting data now that of course brings us to a very important question how do you insert data into a table and for this let's assume that in this app that we might have built a user created a new event an organizer created a new event and they offered that app in the end talked to our servers our computers and basically triggered an action that should lead to a new event being inserted into our events table and the command that would have to be triggered for that is the insert into command which is followed by the name of the table into which you want to insert data so therefore of course you have to refer to a table that has been created already in this case events now after this table name you can add a pair of parentheses and between those parentheses you can list the columns into which you want to insert data so you are about to insert a new row and now you have to define which values should go into which column of that row and therefore you can now list those columns here of course you might need to insert data into all columns anyways but for example here for the events table that's not the case because for example image does not have the not null constraint and therefore the image column is actually not a column into which you do have to insert data the id column is also not a column into which you have to insert data because it will be managed automatically thanks to auto increment or the serial type so the columns into which we have to insert data here in this case are the name column the date planned column and the description column because these are the three columns that do have the not null constraint so that must not be left empty and therefore here when i insert a new event i can target these columns the name column then separated by a comma the date planned column and then separated by another comma the description column you simply list the column names here in any order of your choice separated by commas now we also might want to insert data into max participants and min age even though we don't have to and therefore here we can also target max participants and min age and just as before you can split this across multiple lines you don't have to but you can to increase readability which is what i will do here so now i'm targeting all these columns with that insert into command however that's not all targeting columns is nice but we also need to provide the values that should be inserted and for this you add the values clause here the values keyword values is also followed by a pair of parentheses and now you provide the values for the columns you specified here in the same order as specified here and of course you need to provide appropriate values that match the value types expected by those columns so for the name column for example we need to provide a var char so text or a string as it's also called and you do create such a string in sql with single quotes opening and closing single quotes and then between those quotes you can have your text like a first event that's how you set up some text and in this case it can be any text of your choice it just should be less than 300 characters long now for date planned we need a timestamp and how does a timestamp look like well you can simply also insert text here for dates and time stamps and if that text has a certain format it will be treated as a date or as a timestamp by the database system and the format you should adhere to is this one you first specified a year then separated by a dash the month and then separated by a dash the day then separated by a white space you can specify the time in the 24 hour format so for example here we could say 16 30 and this would mean 4 30 pm you can also add another colon for seconds here if you want to but that's not mandatory and this would now insert this timestamp for this event now with that we also need to provide a description and that's again some text it's not a var chart but text but that still is no difference here when we insert it we again use single quotes opening and closing and then any text of our choice and again for readability reasons i will actually split this across multiple lines here again this is not needed but then here we can have our text and the text here could be this is the description of this first event like that that could be the text which you want to insert here now the last two columns which i'm targeting are the max participants in min age columns both are int columns if you have a look at the column definitions again and therefore we have to provide whole numbers without decimal places and we could say here we want to have a maximum number of 20 participants and the min age maybe should be 16 or 20 or whatever you want i'll go for 18 here maybe and that's how we could insert an event and you can also insert multiple records in one go by simply providing multiple such values here separated by commas so i can add a comma here after the first value and then add another value still between parentheses and that could be my second event which takes place in may let's say and then at 12 30 and then here this is the description of the second event and the max number of participants here could be 10 and maybe here we want a min age of 22.
if you are inserting multiple values in one go they all have to insert the same amount of columns though because you define your column list for this insert into command once and then all the values have to adhere to this column list here that's something worth noting if you want to insert two events with different columns because for example the second event doesn't need a max number of participants then you would need multiple insert into commands which is also something you can do here i'm using one command though and with that we can make sure that we are connected for example to mysql and then run this and this succeeds and it will also succeed if i connect to postgresql and run it against that and now we inserted some data now that the data was inserted we might want to retrieve and view it and for this i'll add a new file the query data sql file where i now want to get my data and you get data with the select command the select clause which then is combined with other clauses to form the overall query the overall command for getting data now with select you first have to specify which columns you want to get we will also be able to control the amount of rows so we will be able to filter our records but you can also control which columns you want to get in the result set that should be generated by the select command and if you want to get all the columns so all the data that is available for all the rows then you just add a star here an asterisk this means all columns alternatively you can specify columns here separated by comma like id name and maybe also the description but that's up to you here i'll go for all with an asterisk and then thereafter you add the from clause you need to specify that to inform sql from which table you want to select these columns or these values to be precise and here of course that's the events table so i add the name of that table after the from keyword and with that we can add a semicolon and execute this here against postgres sql but it would be the same for mysql as you can see here this extension which we use here in visual studio code gives us this nicely formatted table where we can then see all the columns that make up the table from which we fetch the data so here if i zoom out we can see that we got the id column name date planned we didn't insert any image hence this is null that's this special null type i mentioned then a description and then the max participants and the min age that's the data which we stored now we can also filter data and so on but before we do that let's go back to manipulating data because i first want to also update and delete data before we thereafter have a look at filtering let's assume that our inserted data is wrong unfortunately i inserted a minimum age of 18 for the first event and the actual age should maybe be 16.
what can you do to fix this well there are two ways of fixing this though one of them isn't that great the first way which isn't that great is that you delete this event and you then re-insert it the problem is that the new event will receive a new id and if you have other code that relies on the id of the first event that code might be fetching the wrong event or no event at all anymore that's why deleting plus reinserting could be problematic a better way of fixing this would be to just update this first event and for this we got another command which we can execute for that i'll actually select all that insert into code and comment it out with a shortcut here or with help of edit and then toggle line comment this means that we can still see it but it won't be executed anymore if i run this overall file because in this file i will now also add a new statement that will actually update an entry a record in a table and that is the update command so the name should be quite easy to memorize with the update command just as with insert into you then specify the name of the table where you want to update rows in this case events next thereafter in the same line or in a new line doesn't matter as you learned you add the set keyword which allows you to control which columns should be set to which new values and here i could then set the min age column equal to 16 so that i switch from 18 to 16.
if i wanted to update multiple columns i could do that you simply separate them with commas and then you could also update the max participants for example if you wanted to do that as well but here i don't need to do that hence i'll revert it and that's the update which i want to perform instead but there is one problem with that how would sql know which event to update at this point we're not saying anything about that and indeed you can update multiple rows in a table in one go but often you want to narrow down which rows should be updated either to a few rows or to even just one row and you can do this with the where clause which we saw before already on some slides and which we will see again in a couple of minutes with the select command to also select limited data here with update you control which rows will be updated and after where you specify a condition for updating here for example we could be looking for an event with a specific id for this if i quickly query all my events again we can see that the first event has an id of one and the second event has an id of two so if you want to update the first event we could simply say where id equals one now what's important here if you have some background with other programming languages is that in sql you check for equality with one equal sign two or three as you have it in some other programming languages it's one equal sign you can of course also check for other things like greater or smaller or greater or equal or smaller or equal or for inequality with this operator smaller and greater combined that means not equal but here i want to check for equality and with that i'll update that first event so if i query again before updating we can see that the first event if we scroll to the right has a min age of 18 and if i then run this file here to execute this update operation and the succeeds and i then query again i can see that now here we have that updated min age of 16.
now it's the second row here simply because the order was swapped for whatever reasons still the ids haven't changed the first event still has an id of one and it's this event which now had is age changed so that's how you can update tables of course you sometimes also just want to get rid of some data you want to delete something and you can do this with the delete from command just as update you then specify the table name thereafter and then you add a where clause controlling which events which rows should be deleted so if i would want to delete the event with id1 i could write it like this this would delete all the events that have an id of one which of course is only one event here but we could also have a condition that is met by multiple rows and in that case multiple rows would be deleted now i'm not going to execute this here i'll still leave it here for reference but that shows you how you can manipulate data how you can insert data how you can update data and how you can delete data and with that let's get back to querying and let's take a closer look at that before we then thereafter dive back into our project and set up the remaining tables now we already did execute this query command multiple times and you see it gives us both events here the order now is a different one than what we saw before but it's the correct data and that's what matters now sometimes of course you want to filter your data here we only have two events but you could have hundreds or thousands of events and then let's say for example that we only want to get the events that take place after june in the year 2022 so we would only want to get this event therefore since the other event takes place in may which is before june to achieve this we can add that where clause and then just as we saw it before for update and delete we can add our condition here in exactly the same way now here i'm not looking for some id though i could do that as well but instead i'll have a look at my date planned column and i want to make sure that i only get rows where the values stored in that date planned column are greater than some other date and thankfully that's just as simple as it is for working with plain numbers if you want to compare dates you can use greater than and so on as well and if you want to get all the events that take place after june 2022 well then you could just specify your date here like this and you would be good or maybe even go for may 31st so that you start at june 1st and you would include that since it's greater and not greater equal alternatively we use greater equal and go for june first we don't even need to add a timestamp here we can just specify a date and if i run this it works and i therefore get only one event which is the event that takes place after june so that's how you can filter in this case for a certain date you can of course also combine conditions for example we could be looking for all the events that take place after june and that have a min age requirement of 20.
so then i could check for this here and you can combine multiple conditions with the and keyword so now this and this has to be true for each record to be included in this overall result set hence if i run this i get no results i just get the success message but no results because here i simply have no event that would be met by both conditions you can of course also have or here if it's okay for you if one of the two conditions is met and in that case i would get my event again it doesn't have the min age requirement it's not 20 but 16 but it does meet that date condition and since i have a or only one of the two has to be true for a given row to be included in the result set so that's how you can filter with where now what you can also do in combination with where or also without wear if i comment this out is ordering sometimes you want to get a specific order for the result set for example here if i query again in my case without a where condition i get the event with id 2 first that might not be the same for you but it is the case for me here because the order is not guaranteed if you just execute a select statement like this but if you want a certain order then you can achieve it with help of another clause which has to come after where if you do have a where clause but which doesn't require a where clause and that's the order by clause this allows you to then order by a column or even multiple columns if you want to so if i would want to order by id i could just write it like this and then if we execute this i again have this order by default it sorts in ascending order if you would want to go for a descending order you can achieve this by simply adding desk thereafter now it will order by this column in descending order so if i do that i again have the event with id2 first so that's how you can order and that's of course also nice to know just as filtering is okay so by now we created this events table and we learned how we can insert and update and delete data and how we can query data how we can filter data with the where clause and how we can order data these are all crucial fundamental sql operations that you have to know and now you do know them but of course if we come back to our big picture here to our target application or our target database system so to say then we're not really there yet we added an events table but of course this events table is missing crucial relations to locations or tags because we haven't added those tables yet and therefore that will be the next step we'll add a locations table first and then probably also a cities table since we need that for our locations as well and then in a second step we're going to make these relations a bit more official and you're going to learn how you can query these relations so how you can merge data that is related but stored in different tables now step one the creation of these tables is definitely something you can also try on your own here as a first step write down the commands for creating a locations and cities table don't create the tables yet because we will need to add more data more information to them to set up these relations but write down the commands that you would use for creating a locations and a cities table and then in the next step we're going to wire all these free tables events locations and cities up so for that i'm going to add a new file create locations tables dot sql obviously the file name is up to you though and in there i now do want to create the two new tables which we need for now locations and cities and i'm going to start with the bigger one the locations table of course we still do create the table as we learned it before with the create table command and then we choose a name for the table and here i'll still go for locations so i'm going to use the plural form of the data entity that's going to be stored in there and then it's up to us to decide which fields which columns we want to have in that table but here i will add another id column so just as before for the events table i will add such an id column and i'll copy the code from the events table actually since i want to have the code for both my sql and postgres sql here so i'm going to copy this in and then besides the id column we want to have a title for a location which is optional not every location will have one but if a location has its own title we will be able to store it and such a title would be some text so varchar sounds like a fitting data type since varchar is the type you use for text for short text to be precise and i think 300 bytes so generally around 300 characters should be enough here i'm not going to add not null because actually i want the title to be nullable it should be okay if the title is null so if no value was provided for it now besides the title we also need to street name and that will also be a varchar because of course that should be some text so the street is of our char and here i'm going to add the not null constraint because the street should not be null every location should have a street then i need a house number and here the question is what the data type should be since it is a number you could go for an integer so that values like 5 or 10 or 18 could be stored in there but at least in some countries for example germany it's also fine to have house numbers like 15 a and that of course would not be a number therefore hence i'll actually also use a var chart here so that we can store the house number as text so that we also do allow for special house numbers like 15a and i think here 10 bytes so around 10 characters should be more than enough for that house number i'll also add not null here because just like a street every location should have a house number next i want to have a postal code which you might also not have in every country of the world but at least here in germany it's the case so i'm going to add one and this is also a text i'd say um up to five characters long here and five bytes should be enough therefore since the postal code typically will just be something like this we could store it as a number but since we're not going to do any math with it storing it as a text is probably a bit more flexible and not an error here i'll also add not null here because this field should also be mandatory we could also add a check constraint where we check the length of the entered postal code if we have some restrictions there like a minimum length of 4 characters but i won't do that here and last but not least we want to store the city name and for that i actually want to use a standalone table because that allows me to standardize the supported cities and ensure that every city is only stored once and then i'm going to set up a relation to that cities table in a second so therefore i'll create that second table with create table and we can create multiple tables in one file here and execute them all together with that vs code extension that will work and that second table which i create will be called cities and in there i actually want to have just one column one field and that's the name of the city of course you could name this column differently but i'll choose name as a name now the name of a city will be a var char because it will be some text and maybe we want to support city names up to 200 bytes so up to 200 characters it is up to you but that's what i'll choose here and here i will actually turn that name column into the primary key because whilst the primary key often will be such a auto incrementing id it doesn't have to be in the end the primary key just says that this is a not null unique field and it will also be important for setting up relationships later but of course this here can be a not null unique field even though it's not an auto incrementing id even though we'll store city names in here we will store every city only once and therefore it will be unique and of course we will also provide a city name for every row that we insert so it is not null and unique and we can't treat it as the primary key and that's really important to know that it isn't always such an id you can of course always add such an auto incrementing id field and we could do this here as well but the more real key which we already have here is the city name since that will already be unique so it doesn't make too much sense to add this artificial extra id we could do it but it's not required here and therefore now we got these two tables set up they weren't created yet but we wrote down the general commands now as a next step i want to set up these relationships and make these relationships a bit more official if you want to call it like that so we need to set up relationships and for this it's best if we take a brief pause and think about what a relationship between different data entities actually is it in the end just means that we as a human know that some row in the locations table will be related to some city row in the cities table because we'll have a city that belongs to a certain location and of course multiple locations can be related to the same city so actually multiple rows in the locations table can point if you want to call it like this add some single row in the cities table ensuring that that city is related to them and it will be the same for events an event of course will be related to a location so in the end we as a human know that some rows in the events table will point at some location in the locations table now that's the logical part we as a human know that some rows are related to our rows but of course in order to query related data later and to merge data from different tables together into one result set we need to make this relationship a bit more official we need to store some information in our tables that allows us to identify the related rows and for this we're going to add a new field and i'll start here with the locations table i'll add a new column in the list of column definitions here and in that column i want to store an identifier of the related city in this case here hence i'll name this column city name but this is up to you generally the convention just is that you pick up the data entity name of the related data entity and then the name of the column that stores the unique identification criteria in that related table so in this case we're going to set up a relation to the cities table and there to the name column so city name is a fitting name here and then you repeat the data type of the related column because we're going to store related values here in the locations table and that's generally it now we can store city names here in locations and later we'll be able to run queries where we look for related city names here in cities now of course since for cities we only stored a name in that related table actually merging this table with this table for a certain queries doesn't make too much sense because we already do store the city name here and actually right now we could still insert any kind of city name here in the locations table and if we don't have a matching row in the cities table that would be our problem that's why it's a good first step to have such a column that allows us to store related values but with that we don't really set the relationship in stone we still could insert invalid values here and therefore to make this relationship a bit more official and to force the database server and system to ensure that we insert valid related values into this city name column we can add another constraint here a so-called foreign key constraint we set up this constraint by using the references keyword and then we specify the table to which rows in this table the locations table should be related to and you can have multiple foreign keys for a single table but here we're going to start with one the city name the related table of course is the cities table and then between parentheses you can specify the column in that related table so in that cities table here that stores the related values so in this case name however if you plan to set up a relation to the primary key column of the related table you can actually also omit the column name here in parentheses you can also still specify it but it's not mandatory and very often you will use the primary key but it's not a must do foreign keys can point at any column in the related table however often it will be the primary key column so with that we set up this relation and now with that the sql database system which we're using mysql or postgresql in this case will actually ensure that we don't insert city names here in locations that don't exist in cities so now we get this extra enforcement if we would try to insert a city name here which is not stored as a city in the cities table this insert operation would be blocked and that's of course very useful and that's how we set up such foreign keys however there is now also one important change we need to make here if we plan to create these two tables at the same time because then we should actually move the creation of the cities table up front because we do refer to the cities table here when we create the locations table so for this to succeed we should make sure that the cities table does exist before we create locations and that's what we're doing here now and now with that we can also go back to the creation of the events table and copy this code this command and add this in the create locations tables sql file as well because i want to recreate this table and therefore we will have to drop it first of course but i want to recreate it such that events are now also related to locations and that's of course another great practice and challenge for you because you can also try writing the code for this on your own after a short pause which i give you to pause the video and try this on your own we will set up the relation between events and locations with such a foreign key together were you successful let's do this together and for this it's important to remember that we have a one-to-many relationship here a single event will only point at a single location but of course a single location can be related to multiple events just as one location pointed at just one city but a single city can of course be used by multiple locations it's now the same for events and locations and therefore we want to set up our foreign key here in the events table you always add the foreign key in the table that is only related to one other row so one event is related to just one location therefore we add the foreign key here in the events table and now we add it by simply adding a new column and there again you can name it whatever you want but the convention kind of is to pick up the name of the related data entity so location in this case and then an underscore and then the name of the column that's used for the relation in that related table and in that case that will be the id column because i'm going to use that primary key of locations now with that we got this now we need to use the same data type as we have in the related column and that will be an int no matter if we set it up like this or with serial serial in postgres sql also uses an int under the hood so i use int here as well and then just as before i will add the references constraint to make this relationship a bit more official we could query related data and merge it into a single result set without the references constrained as well but the references constraint will ensure that when we insert a value for location id in the events table it has to be a value that does actually exist as an id in the locations table and we typically want that extra security that ensures that we set up correct relations so therefore here i'll add references and then i'll reference the locations table and there the id column though since idea again is the primary key column this is optional and we can remove this so now this was also configured i will still not execute these commands yet though for one because we'll need to drop the events table first before we can recreate it but also because i want to do something else as well the something else i want to do as well is i want to make sure that we also set up some logic that should be executed if a related rows should be deleted now with that i mean that for example a location could be deleted if a location is deleted and an event depends on a given location then something should happen to this event because of course it's all of a sudden losing its related row and you can set this up by adding the on delete action here after configuring the foreign key constraint with references so on delete is related to references and with on delete you can specify what should happen if a related row is deleted now there are various actions and i dive deeper into those in the full course for example you have restrict to block this so that if you would delete a related row the operation is actually blocked if there are rows depending on the to be deleted row but you could also use cascade as an alternative which means that the deletion should cascade so that the related event that is related to the location that is deleted should be deleted as well so the event would then also be deleted you also have set null for example which will just set the location id column to null if the related location was deleted now here i'll go for cascade to make sure that events are also deleted if locations that belong to them should be deleted but you can also go for different implementations here you also have on update which allows you to specify what should happen if the related column value changes but for the id here that typically shouldn't occur now of course we can also set up on delete and on update here for the city name in the locations table because we got a relation here as well and if a city should be deleted which sounds unlikely i don't want to allow that because it sounds like an error if a city disappears but of course you can also come up with a different solution but here i'll set on the lead to restrict to not allow this and i do want to add on update because a city name could theoretically change even though it's also unlikely in this case i'll set up cascade so that if our related city name should change we also update the city name here in the locations table so that this updated city name is propagated to this table as well and now with that we configured the relations for these tables we could now create them and start inserting some data but i actually don't want to do this right now instead we'll move right on and also implement all the other tables and relations that we see on this slide here again as a practice you can try getting started with this on your own the many to many relationships will be tricky though so you might want to ignore those but you can set up the one-to-one and one-to-many relationships here if you want to and in general write down the code the commands that would be used for creating those remaining tables we're going to do this together thereafter and then thereafter we're going to insert more dummy data play around with that and also dive into more queries now for all of that to set up all the tables i'll now add a new file create all tables dot sql and i'm going to copy the create commands from create locations which we never executed into this table into this file and now there besides the tables which we already have here i want to add more and i will start with users and organizers before i then add tags and set up all the relations now adding the tables for users and organizers should be rather straightforward and since events depend on users and organizers i will create those tables before we create the events table so that we can set up proper relations i'll start with users and add my users table here and then again it's up to you which columns you want to add here i will add an id as i did it before here for mysql and postgresql because every user probably should have a unique id and then we could say a user has a first name probably a varchar maybe for 300 characters and that should not be null because we want a first name and then i'm going to duplicate this and also add a last name here and then thereafter we also might want a birth date for a user now for a date we saw before that we could use the timestamp if we want both the date and the time of the day but for a birth date it's probably enough to just store the date and the day for here we can use the date data type which is what it sounds like the date without the time of the day this should also not be null because every user should specify his or her birth date i would say and then last but not least i will also store an email and here for the email that's again a varchar it's some text maybe up to 300 characters long and not null so not optional that could be the users table now we also have users that are organizers as well and there would be different ways of modeling this but one way is to add another table organizers where we store organizer specific data now here to keep things simple the only organizer specific data that i want to store is an extra password but of course it would be easy to imagine that there is more organizer specific data that should be stored now for the password i'll choose a varchar of let's say 300 or 500 whatever you want to allow and then also set this to not null because for organizers it's not optional but then of course an organizer is also just a user in the end and therefore we might want to set up a relation to our users and we can do this just as we learned add a user id column data entity name column name use the same data type as we have it here in the users table for the related column and here i'll use the id as a related column so the data type is int and then here we use references point at users id this however is optional since id is the primary key there and then we can also make it clear that if a user should be deleted we also want to delete the organizer that belonged to the user because without a user there also won't be an organizer anymore because an organizer is just a user with more data attached now here in this scenario a special thing that we have here at least when modeling it like this is that user id is actually not just a foreign key pointing at the id in the users table but also the primary key of the organizers table and you can configure it like this a column can be the primary key of this table the organizer table and at the same time be a foreign key pointing at a related row in another table in this case the users table this is possible and something which makes sense here if you model the data like this i also want to clarify that for a users and organizers we have a one-to-one relationship that actually means instead of storing the user id here in the organizers table we could also add an organizer id here in the users table so instead of doing that or in addition to doing that if you wanted to with one-to-one relationships you can set up your foreign keys in both tables for one-to-many you didn't have that choice now here i will set it up in organizers though since it makes sense to have that user id as a primary key there anyways and i think that's an elegant way of modeling this relation and this overall user and organizer data entity so that's that of course that's not all though events are also related to users and organizers now regarding the events users relation we have a many-to-many relation just as we have it for tags and events so we'll focus on this next because we haven't learned about this relation yet and we don't know yet how we would reflect it in our commands but at least regarding organizers events organizers we have a one-to-many relationship and we do know how to reflect this in our commands what we need to do there is add another column in the events table because it's a one-to-many relationship and one event has exactly one organizer but one organizer can organize multiple events therefore the foreign key goes into the events table and there we can have an organizer id and then use int and references and we point at the organizers table and they are the user id column since that is our primary key and therefore we could also name this organizer user id if we wanted to but i'll just name the column organizer id since that should be clear as well but again since user id is the primary key of organizers we can also omit this here now if organizer should be deleted we might also want to do something with the event to which he or she belongs so which was organized by that organizer and here i want to cascade if an organizer disappears the event should maybe also be cancelled so that's what i'll do here and that's therefore the relation between events and organizers now it's time to have a look at those many to many relationships now here in our example we have two many to many relationships events users and events tags there is one problem that we'll have with those many many-to-many relationships when working with many-to-many relationships you need intermediate tables also called linking tables and here's a simplified example we have events users and one event can have multiple registered users and a single user can register for multiple events hence it's many too many the problem is that here we can't add a foreign key in either of the two tables since we would need multiple values because again one event can have multiple users and one user can belong to multiple events so therefore here we actually have no way of adding a single foreign key column unless that column would store multiple values which goes against data normalization there we don't want to have multiple values in one column instead it should be one value per column so therefore no direct connection is possible at least if we adhere to data normalization which we should therefore what we need here is a intermediate a linking table an extra table that is created that is only there to store relations between events and users so this table could be named events users and it stores event ids and user ids and maybe an extra id column though that's not mandatory and then we can store relations where we have one row per relation between the two main tables that's the idea and that's how we can reflect many to many relationships and that is how you will reflect many to many relationships when working with relational database systems as we're doing it here with sql and therefore back here in our code so to say we now want to create a tags table because that was missing but for the relation between events and users and events and tags we also want to create those linking tables and therefore these are a couple of things we should do now i'll start with the tags table because that will be a very simple table i'll name it tags and in there i just want to have a name so the name of a given tag and that name should be a varchar up to 100 bytes long and it should not be null or to be precise it should actually be the primary key here and that's all so just like cities that can be a very simple table but now we need those linking tables and there we could start with events users so that we're able to actually register users to our events so to say that below the events table here because we will use both the events and the users table in that linking table hence it has to come after the creation of the two tables i will add another new table and that will be the events users table now it's up to you how you name that table but since it's a linking table you typically include the names of the two related data entities or the two tables that will be connected in this case events users and then in there in this table i want to have an event id column which stores the well event ids and then also a user id column which stores the ids of the related users and then again we'll have one row per connection in addition we could also add a registration date if we wanted to but i'll not store it here but i just want to make it clear that you of course can store more data as well this linking table is not restricted to just storing the two related ids or related primary keys but that you could store extra data that belongs to a registration which this is in the end as well but here i'll keep it simple and just store event id and user id now you do set up those columns just as we did it before a since we'll point at ids i'll use int here and then the reference is constrained and then here for event id i of course reference the events table with the id column which is optional since that is the primary key of course and then thereafter we can also specify what should happen if an event is deleted and in that case i want to cascade here and also delete this registration and it can be exactly the same for the user id it's an int and of course here we point at users that's an important difference but then if a user is deleted i also delete the registration this is how you can set up such a linking table now there are more things you can do with linking tables that allow you to ensure that you don't have duplicate rows for the same kind of relation but that is something we'll have a look at in the full course for a start this is more than enough because this will allow you to store many-to-many relationships now one last thing i do want to add here though is a primary key and for that we could add an extra id column that would be fine but we could also say that in the end it's the combination of a specific event id and a specific user id that makes up the primary identification criteria of a row in this event's users table and you can indeed set up primary keys that comprise multiple columns a so-called composite primary key for this you add a new column definition row so to say but instead of defining a new column you add primary key here and then in parentheses thereafter you specify the columns that should make up the primary key and it's the combined value that will then act as a primary key so you still have only one primary key here but it's the combination of column values that is something you can do and that is often done when working with sql and here it's event id user id the combination of these two columns that's my primary key and that's therefore also something you can do alternatively we could have added this extra id column as a primary key that would have been fine as well but that's now how we will be able to store many-to-many relationships and therefore now we can do exactly the same for tags hence i'm going to copy this events users table creation command here and create my events tags table where i also point at events and have an event id but then i also have a tag id and a point at tags and if a tag is deleted i also delete this relation between events and tags and the primary key then can be the combination of event id and tag id and that could be all though there's one important thing to note tags don't have an id column instead we have the name column which is the primary key but that's no problem we can use this as well i'll just rename this to tag name to reflect this and not use int here as a data type but instead the same data type as i used it up here varchar 100 so that's what i'll use here and then i can store this correctly as well and therefore in the primary key it's of course tag name as well and with that we should have all the table creation commands that we need quite a lot as you can tell now it's time to execute them and then insert some dummy data and play around with that dummy data and for that of course we'll first of all have to drop our events table because that table already does exist and i now do want to recreate it so that will be the next steps so let's start by dropping events for this i'll add a quick drop sql file because here i'll just add a single command drop table events that's all that's how we can rid of this table now we just need to make sure that we're connected to our databases and then once we are i will run this against mysql and this succeeded and also against postgresql now with that all dropped we can go back to the more important create all tables sql file and try running this against postgres and mysql now you want to make sure that you have the correct id column commented in and at the moment i have the mysql version commented in so i will connect to mysql first and then execute this here and here i'm actually getting an error which isn't really an error this is actually a bug from that vs code extension which i'm using here and even though it looks like the command failed it will have succeeded actually you can tell that it did succeed by having a look at your mysql database and there are the my events tables and you should see all those tables there so they were created and you see they do have the appropriate columns so they have been created here all these tables are there and therefore this did succeed now we can connect to postgres and also run this against postgres for this of course we just should make sure that we comment out the mysql id here and comment in the postgres sql id so that this postgres sql id will be created like this and once we did that we can run this and here we don't even get an error instead it just succeeds so now all these tables were created and with those tables being created now we can now also populate them with some data now to insert some dummy data i prepared a file which you find attached this insert dummy data sql file in there i prepared some insert statements which are commented out but which we can now execute step by step for some commands we will have to fill in some placeholders once other commands have been executed and here i want to start by creating or by inserting some data for tags and cities i'll start with those tables because of course a lot of our tables depend on tags and cities for example we have relations between events and tags and cities are required by locations and locations are then required for events so it makes sense to start with inserting some basic data like this therefore i'll comment this in and then i'll run this against postgres here and insert that data please note that for tags and cities i do point at this name column which is the only column the tags and cities tables have and then i do insert some strings here some text so let's run this and this worked for postgres and if i run this against my sql it also works but i do get the same bug error as before but you typically get this error if you have multiple commands in one sql file and therefore in the end this actually did also work and we can verify that it worked by also querying our data for this i'll add a new file basic queries dot sql where i for example want to select everything from cities to see whether some data is in there and if i run this i do get my list of cities here and of course we can do the same for tags if i run this for tags i also get my tags here so that worked now that was cities and tags i'm now going to comment this out so that we don't try to insert these tags and cities again if we would run this file again and we can then move on to locations though actually let me comment this in real quick again if i would try to run this again it would now fail for a different reason because now i would try to enter an already existing value again and since the name in our tags table is the primary key and therefore guaranteed to be unique entering a tag with the same name as an existing tag is not allowed so that's probably good to know that's what you learned now you saw it in action now i'm going to comment this out though and i'm going to comment this in so that we can create some locations and here i'm using all the table and column names i created over the last minutes if you use the different table or column names you of course will have to adjust this here but with that we're going to add some locations and please note that some locations do have no titles at the moment i then enter an empty string this would be okay but since we actually do allow for null values in the title column because we did not add the not null constraint we can actually also change this and instead add null as a value which means we want no value we could also omit the column altogether but since i'm adding multiple entries multiple rows in one step and some do have titles and i'm therefore referring to the title column here i can't just specify it in some values and omitted in hours then if i do enter multiple values in one step i'll need to target the same columns hence then if some values should leave a column blank empty we have to use the special null type to make that clear so therefore here with that i can now run this and create some locations we get the mysql bug error here for mysql but if i then do connect to postgres and we run it against that there it works and now we got some locations as well and we could query them but they will be there hence i'll comment this out and continue with users now i want to insert some users and therefore it's the same as before i'm specifying a date here that's noteworthy it works like the timestamp just without the time portion so you have some text using a special format this format here where you have year month and then the day in the month and then this will be stored as a date since birth date is of type date in our column definition so now if i run this against postgres this works and we can also run that against mysql and there we got the same bug error as before but it will also have worked now we got this and now before we continue with organizers and events i want to get some ids because for organizers i for example need to know my user ids and therefore i should first of all query the users to find those ids hence i will select everything from users here and then run this and then here i get the ids of these users in this case one two three four that's typically what you should expect but it's always worth taking a closer look if you plan on inserting related data where you want to use ids and if you had a real application that would be doing that instead of us doing it manually the application might run a query like this first to find the id of a certain user and then run another command where that id is then used to insert some related row in another table now here i do know some ideas and now it's of course up to us which users should be related to a given organizer so which users should be organizers here i'll simply use the first two users so the users with ids 1 and 2 but of course you can use any ids here i also added a comment that of course here i have a dummy password like this in reality your application that's talking to your sql database would actually encrypt the password and store the encrypted password but the encryption is something that would be done by the application not by the sql command so here i just have this dummy value and now the users with the ids 1 and 2 will become organizers so let's execute this as well here against mysql with that well-known error and then thereafter against postgres sql where it works so that's that now we can comment this out and then also store some events so for this i'll comment this in and now here we also need some concrete ids to be precise when i insert an event i have a location id and an organizer id in that order so here we need the location id here we need the organizer id and therefore we should query that again so therefore here i can query everything from locations to get my id here and it's up to you which locations you want to pick for this event here it's a new in town event so maybe it makes sense to host it in the beer hall so i will use this id 1 here but again that is up to you so one is my location id now for the organizer id i want to query from organizers and there of course i have these user ids to which i'm referring and i can use one or two here and in my case here i will use one here as well so that's the organizer id if we now try to run this against postgresql it works and if we connect to mysql there it should also work we just get this well-known bug error but with that we're getting closer to the end we inserted a lot of data now we have to insert data into our many to many linking tables now inserting data into such linking tables works in exactly the same way as it did for other tables though there is nothing special about it now here for events tags we need to target the event id and then the tag name and for events users we have to target the event id and then the user id if you're not sure what your column names were you can simply have a look at your column creation commands here or your table creation commands to find out how you labeled your columns here so that's that now we need to find out which event ids we have so that we can connect some events and we also need them down here as well as the user ids so again if we run some queries and we get our events of course at the moment i have only one event and that has an id of one so that's the event which i do want to connect hence here i can use one for all those tags so these tags all belong to the same event and that's the idea one event can be connected to multiple tags and one tag on the other end could also be connected to multiple events if we had multiple events here and then it's the same for events and users here i'll also use the event id of one which is the only event we have and then we need to find out some user ids by querying users and now let's say actually julie and michael are registered for this event so that would be the users with ids 3 and 4 and therefore here we have 3 and 4 and we don't need the last row actually and that's how we now could insert that many-to-many relationship data into those linking tables and the more events and so on you have the more rows you might have in those linking tables of course so we now run this here for mysql it works with that well-known error if i run this against postgres sql it just works and now we inserted all that dummy data for that single event and those different locations and users into our tables and that's a great first step now we did add all these tables now and we did set up the relations between those different tables so between the rows in these tables now a typical use case a typical scenario would be that we also want to query related data we might want to for example get all our events even though we have only one here but not just get that event data with those cryptic ids but actually get a result set where for example the id of the location is replaced with the actual location data you could have an application that needs to fetch events plus their addresses and you typically would want to fetch this in one single command in one single query and of course you would want all the address data then not just the location id that would be a very common use case and requirement and of course that is so common that sql supports this and actually it's a key feature of sql of the sql language for this i'll add a new file queries with relations.sql and in there we'll dive into such queries that will actually merge different tables and the related data so for that let's start simple let's say we want to get all events here we only have one but of course we could have multiple events and therefore of course we could run a query like this this is what we did before in the course already if we execute this here against postgres i get all my event data and here we can see what i meant for location id and organizer id we only get the ids we don't get any detailed location data of course because that detailed data is not stored in the events table therefore what you would do in such cases where you want to merge data across tables which again is a very common requirement is you would perform a so-called join with the join clause to be precise typically with the inner join clause though we will also soon learn about the left join clause as an alternative but let's start with inner join inner join does what the name kind of suggests it joins data from multiple tables into one single result set and that's also important it does not change the original data or the original tables data doesn't get merged into a new table it's just about generating this result set for this select statement and for that with inner join we can combine data from multiple tables to be precise related data because combining unrelated data and merging any kind of addresses with any kinds of events would be possible with other queries you'll learn about them in the full course as well but it's really helpful so here we would want to join our events table with the locations table and you do that by typing inner join and then after the inner join keywords you add the name of the table in this case locations that should be merged with your other table by the way you can have multiple inner joins in one of the same statement in one of the same query but we'll see this later for the moment let's just join one table with our events table here now just a table alone isn't enough though because out of the box sql doesn't automatically know how the data should be joined how related rows in the locations table should be identified for the events that's why with inner join you also add the on keyword and after on you specify the joining condition so to say you specify which columns from the two tables have to be equal for data to be joined for example if we want to get the location data related to events we would typically want to join our data on the location id column in the events table keep in mind that there in the events table we do have this location id column so we want to look for this column and then find equal values in the locations table and they are in the id column because that is the related table to that location id column here for a location id we point at the locations table we don't specify a column so the primary key of that table gets used and the primary key of locations is id so therefore that is the check we can use here for combining data so that will find us the locations that are related to events here however we would have a problem because id is a column name that's not just used in locations but also in events sql wouldn't know to which id column we're referring here the one in events and the one in locations it might be obvious to us humans but it isn't to sql so to make it clear to sql what's commonly done with join queries so with queries where you do use inner join is that you assign aliases which is something you can do without joins as well you can use the as keyword after a table name for example you can also add aliases to columns but here we're talking about tables and then you can give this a new name for example e and you can do the same here for locations and give this l or lock or whatever you want as an alias and then you can use this here to clarify which column is coming from which table by using a special dot notation here location id is in the events table so we can use e dot location id and here we are talking about the id column from the locations table so we can use log.id actually you don't need aliases you can also use the full table names here so then this would work without aliases as well but with aliases we simply save some typing because we don't need to write down the full table name that's why we typically use them or why we often use them but you don't need them what is required though is this dot notation to be clear about which table and then which column in that table should be used here so the alias part is optional but you need to clarify from which table a certain column is coming if that column with that exact same name exists in multiple tables that are all involved in this query as it is the case here for events and locations which both have an id column therefore of course here for location id it's actually optional and you don't need to add or use the dot notation here but it's also never wrong to be more explicit by using the table name and then the column name or an alias and then the column name and with that we're joining the data and as a result if i now run this what you will see is that here we still get all the event data but if i scroll further to the right besides location id and organizer id which is still there we now also got the data from the locations table we got the exact location data that belongs to the related location of this event and only that location there is no other location data here the other locations are not included it's only this one location that was configured as a related location with help of its id this here is the id column of the locations table and we used its id to set it up with help of that foreign key column in the events table and that's how we then merge this data together and of course here when we merge and when we join multiple tables we typically get a lot of columns because by default when we use the asterisk we get all the columns of all the involved tables and often that's too much so here we then might want to clarify which columns we do want to get we can do this by adding a comma separated list here instead of the asterisk so for example we could be interested in the id of an event so we can type id here but again that would exist in multiple tables so for that we should use the dot notation and use e dot id if we're referring to the id in the events table which has this e alias then we might also be interested in getting let's say the name of an event and the date planned but maybe not the other data but then we're interested in more data about the location so therefore here from the event i also get the name and date planned and here the dot notation is actually optional because name and date planned doesn't exist like this in the locations table but still using the alias here isn't a bad idea because it makes it extra clear where these columns are coming from which can make it a bit easier to understand this query here and then i also want to get some location data like for example the title let's say and the street and the house number maybe and also the city name like this so now i'm getting all these columns and to improve readability i'll split this across multiple lines which of course is not required but also no problem and it simply helps with reading this query and with making it a bit easier to digest you can also assign aliases to columns by the way for example here if you would want to clarify in the result set that this is the id from the event you could give this an alias of event id this is the only part of the result set just as with the aliases here for the table names this does not change the table or the data in the table it only changes how this data is used and labeled in this query and in its result set if i now run this for example i therefore get a result set where i have event id as a name here for this id column so that's my alias and i get all the other data but only the columns which i specified in my query not the other columns and still we got the same merge of an event and a location as we saw it before so that's how you use aliases but even more important that's how you use inner join to merge related data so we learned about inner join as mentioned before you can also have multiple joins in one query so for that i'll actually copy this and then comment it out so that we still have it for reference but then i want to repeat it but i want to also join another table we could also join the cities table but since the city name actually already is the full city name since we used that as a primary key in the cities table that wouldn't make too much sense here probably what might make more sense here is that we also join in all the registered users and we can easily do that by adding another inner join of course we could also just join users and events but we can also perform such a join with locations and users and events there are no limits you can join whatever you need to join to get the results that your application needs or your use case needs so here if i want to join in my users as well we first of all have to remember that we got a many-to-many relationship here so we got this events users linking table and therefore if we want to join events with user data we actually need to go through that linking table so to say we can't directly join the users table because there is no direct relation we got no foreign key in events or users that could be used for establishing the relation between events and users instead that's only the case here for events users there we do have our connection to events through that event id column and hence it's this events users table that needs to be joined in first so here i'll again give this an alias e u maybe and then just as before we add the on keyword you always need that when configuring a join and then here we again have to clarify how our columns from these different tables are matching and of course here we know that in the events users table we got this event id column which stores the ids of related events and therefore we want to identify fitting events by looking at the e dot id column so the id column in the events table which stores the related event ids that are also stored in the events users table with that we get entries from that linking table but of course we're ultimately interested in getting the concrete users and therefore we need to join in another table we also need to join in the users table now that we first joined events users we can do that because events users which is now part of the result set and connected to events through the event id also has a user id column which we then will be able to use to combine users into the result set that's the idea so therefore here we can join users as you maybe or a user or whatever you want and then we do this on eu user id so the user id column in our linking table which has to be equal to u so the users table dot id so the value is stored in the id column of the users table where we have equality between those and the user id column in the events users table we have a match and then this will give us this data now we probably also want to see some users data in the result set so here we can then get let's say the first name from the users table using the dot notation to make it really clear where this is coming from and the last name and of course we could get any data we want and with that with this now if we run this command we also get a result set but now with multiple rows now we get multiple rows even though it's the exact same event as you can tell by looking at that data we also got the exact same event id but we have multiple rows here because we got multiple users that are registered for this event we see different users here and of course therefore we get multiple rows because every combination between an event and a user is a brand new row in the result set so in the events table it's still only one event but the result set gives us one row per relation which makes a lot of sense because how else would this be reflected and therefore now if you had an application that needs all the user event combinations it would now get a list of all these combinations for example that this first event is actually combined with two different users and that's how we can also create such a result set which is arguably a bit more complex because now we use multiple join statements to combine data from a lot of different tables and that's of course absolutely also something you can do when working with sql so we did explore the inner join clause before there also is another join clause that works a bit differently and that would be the left join for this let me comment out this select statement and let's now perform a left join for this we still add a select statement it's still a select command we still run a regular query but now we'll join multiple tables with a left join for this let's maybe say we actually get all our locations so i get everything from locations and of course if i would run it just like this i therefore get a list of all my locations with all the data that shouldn't be too surprising but now let's say we want to merge this with some events data so kind of what we did before but i'm starting with the locations table as a base table here if you want to call it like this not because you always need to do that for left join but because it helps with understanding the difference between left and inner join in this scenario here because now i can join this with my event data with help of an inner join as i did before just to show you that difference for this we add inner join then the name of the related table events in this case and we can assign aliases here for the same reasons as before and then just as before we need the on keyword and here if we want to merge locations with events we can use the same condition as before even though the order of tables is different now but it doesn't matter i'm merging my data which i get from locations with my data that's stored in events and therefore this condition can still be used because i'm still working with the locations and the events table and i'm still using the same aliases even so this will still work and as a result if i run this i get a similar result set to what i had before the only difference is that now the location columns come first if i just use the asterisk for querying as i'm doing it here because i'm fetching from the locations table first but then i still have the related event data so that didn't change and please note that we get back one row because we have exactly one event that has a related location and we're getting exactly that data or put in other words we're getting all the locations with all their related events and in this case only one location has a related event one single related event but now watch what happens if i swap inner for left and that's all that changed it's the same kind of query besides that we still have on with the same condition we got the same tables and the same aliases but i replaced inner with left and therefore instead of performing an inner join we perform a left join now watch what happens if we now run this now in the result set we get way more rows to be precise we get all our locations here all locations but if we scroll to the right only one location has a related event for the other locations the event data is null because no related event was found and that's indeed the difference with a left join you always get all the data all the rows from the left table from the left sub result set so to say so in this case the left part of left join is this part here so we get all the rows from locations and then all those rows are merged with all the data from the table that you specify after left join and if there are rows if there are locations in this case which don't have a matching event then we still get those rows but for the event data we simply see null as we see it here for these rows so that's the difference and both joints have their place both joints can be exactly the right tool depending on what your goal is if you're looking for all the locations that have events attached to them inner join is the correct clause if you on the other hand want to get a list of all locations no matter if they're related to any events or not but in addition you do want to get any related events data then left join would be the place to go and of course we can also have multiple left joins in one statement and we can even mix and match left and inner join in one and the same query if we want to let me show you an example here let's say we want to get a list of all our cities and then find all the locations that belong to these cities for this keep in mind that when i inserted data i inserted a bunch of cities here and some of these cities aren't used down there to be precise frankfurt is a city which i never use down there still it is a city stored in the cities table and therefore if i select all my cities here with this simple query then of course i get a list of all my cities and that list includes frankfurt but i could for the same reasons before want to join this with all the locations and if i still want to get all the cities no matter if they do have an attached location or not i can use a left join if i left join my cities with my locations on locations dot city name being equal to the name of a city and again we can use aliases to make this a bit easier to digest and be a bit clearer about which column is coming from where so i have my city name in the locations table and then i have c dot name referring to the name column in the cities table then if i run it like this what you will see is that if we run that we got a bunch of rows and we got all the cities including frankfurt which simply doesn't have any location data attached on the other hand for some cities like munich where we have multiple locations that are part of that city we get multiple rows because we have different locations thereafter as you can tell here these are different locations different streets different house numbers that's why we have multiple rows here because we have one row per relation combination and that's a left join it includes frankfurt i can of course also use an inner join to not include it anymore so if i do that if i get my inner join here then you will see that frankfurt is not part of the list anymore but my main point was actually that you can have multiple left and inner joins and that's indeed the case let's go back to our left join here and we saw that we can use multiple inner joins before already let's now say that now that we joined in the cities we also want to join in events that take place in those cities so for that we first of all reach out to our locations and then once we got that list of cities and locations merged into one result set we can add another left join so that we still keep the complete list of cities and we don't narrow it down to cities that have events and we could left join this with events again i'll give this an alias and then add on and then here we would say e dot location id should be equal to log.id that's how we merge events with locations that's what we did before as well now however i'm using multiple left joins here so that i still have my full unfiltered list of cities and locations if i now would run this i still get my list that includes frankfurt which still doesn't have any location data but if we scroll to the right we also see that it of course doesn't have any event data that however is the case for more locations and cities as you can tell only one city only one row here does have an event and that's munich for this exact location the other locations in munich do exist but they also have no events which is why we also have null here for the event columns that's the result we get here when using left join twice if i would use inner join here for joining this with events then we would get a different result there we would only get one row because we only have one overall relation where we have a city with an event we only have one event in total so we get back only row in total here and what's really important is that we get that even though we have a left join here because both inner and left join always join the part that comes in front of the clause so in case of this inner join statement the result that comes from this query up to this point with that table so behind the scenes when this overall query is executed sql builds this up step by step and it first gets our cities joins it with locations with a left join and then the intermediate result of that query up to this point will be inner joined with events that's why it's then still narrowed down to one row overall and why it's not the case if i use a left join here because then we keep our result set from here which includes all the rows for all the cities because of this left join and merges that with any extra event data that we might have so that's how a left join works and how it's different from an inner join and why both can be useful because sometimes you want to get all cities for example plus any event data that exists and sometimes you only want to get the real combinations that do exist in your database only the cities that do have events you might not be interested in the other ones so that's the difference between left and inner join now before we leave the world of joints both inner and left join i also want to dive into another aspect of joining or another aspect of queries that's also important when working with joins you can still filter and order as you learned it before so for example here where i'm getting my cities and i'm then joining this with locations and events we can still filter here for example i could still filter for only the cities that are called munich which of course is only one city but i could do this i could add this filter this where clause and if i do that and i run this i therefore of course get only the combinations that include munich as a city name i still get multiple rows because i have multiple combinations but it's only munich and this also shows us something really important which is important about sql in general the order of how queries are being executed the filtering here clearly is only applied after we went through all these joining steps up front because if that would not be the case we would only get one row since we get three rows clearly the joining has been done already before the filtering kicks in and that is of course important because that allows us to filter on the joint data instead of the data from one table only this might matter because we're interested in all the combinations for munich but it could also matter because we're maybe not filtering for some field that's part of the original table but instead we might filter for some field that's part of another table here for example i could also take something from locations or events and add this here in the where clause or mix and match columns from different tables with and or or that's all possible there are no restrictions regarding that so here if i'm looking for events where the date planned is greater than let's say 2025 january the 1st then i would actually not get any results at all because we have no event that satisfies this condition if i on the other hand change this to 1 january 2020 then i would find my event that does satisfy this condition now i only get one row here instead of three or more rows because even though i'm using a left join i am filtering the final results thereafter so don't confuse inner join with the filtering mechanism for joint data instead you still filter with where of course inner join also kind of filters out some results since it only gives you real combinations unlike left join which gives you all the data from the left combined with some data from the right so to say but it's then still where which can be used on the final result set which is derived from all these joints and so on so that you can really select the data from that final result set that you want to have in the real final result set so to say so definitely feel free to also play around with that i just wanted to mention it here because it is super important to be aware of the fact that you can of course add where and order by and whatever else you can use for regular queries without joins even if you are using joins because it still is a regular query in the end now just actually combining multiple tables into one result set which then can be used for ordering or filtering as you just saw it so that's it for relations and for merging related data super important and you now learned in depth how it works with inner join and left join and what the differences are let's now come back to more common queries again and let's dive into the interesting world of filtering again but also mainly into the exciting world of aggregate functions aggregate operations or grouping as you could also say for this i'll add a new file more queries dot sql and in there the goal is simply to play around with more query statements and i want to start straightforward again i want to select something but i now actually do want to select some users so i'll select data from users but i don't want to get all the users instead i want to filter and you learned before that you can restrict the amount of rows that will be fetched and that will be part of the result set with help of the where clause now my goal is to narrow down the number of users that i get in this result set by their name if we have a look at the dummy data that was inserted here we can see that various users were inserted and let's say we want to find all the users who have a first name that starts with m a for whatever reason just so that i can show you how you could achieve a goal like this of course we could also be searching for events by name here i'm showing it at the example of users now of course we can find users where the first name is equal to max with a query like this you can check for equality with that single equal sign and now i'm selecting all the rows from users where the first name value so the value stored in the first name column is equal to max if i run this i find that this is exactly one user but of course we could have multiple users called max so this is a query that could make sense but what if i want to find all users that start with ma this doesn't work now you could search for users where the first name is greater than m a but this would give you some strange results it gives you max and manual but also michael because if you use comparison operators like greater than or smaller than with characters so with text then it doesn't look for the rest of the word and find all the words that start with m a but instead it simply compares all the values in first name and finds if those are treated to be bigger than m a and michael is greater than m a because the m here is equal but then i comes later in the alphabet then a does and therefore anything that starts with m i is treated as bigger than m a so it is not looking at the rest of the word it's just checking which text is considered bigger than m a and a lot of text will be for example everything starting with a character bigger than m so coming after m in the alphabet or if the first character is the same as is the case for michael it will do the same for the second character and here a a is smaller than a i because the a comes earlier in the alphabet that's how string comparisons work when using greater than smaller than and so on and that's not a bug but the intended behavior so what do you do if you do instead want to find the users whose first name starts with ma this clearly doesn't work well for this you have a special operator the like operator which is written like this the like keyword can be used as an operator in your where clause and it finds you all the users where the first name column holds values that are like this now if i execute that i don't get any results though instead now here we need a so-called regular expression which is a string with some placeholder characters for example if you would want to say that some text should start with ma then you would use m a and then percentage sign the percentage sign is treated as a placeholder which means any other characters they're after and any amount of characters they're after so this gives us all the names that start with m a and then end with any characters or any combination of characters and therefore if i run this with the like operator and this regular expression here then i indeed get max and manual because these are the two users that do start with m a and i don't get michael anymore so the like operator can also be very useful besides the percentage sign you can for example also use the underscore here which matches a single character the difference can be seen if i run this again because now i only get max because max starts with ma and then has exactly one additional character manual has multiple characters and whilst this was met by the percentage sign the underscore looks for a single additional character so this so this matches any free character first name that starts with m a this however matches any first name in general no matter how long it is that starts with ma and that's therefore also something you should know the like operator and percentage sign and underscore in combination with it now with the important like operator out of the way let's comment out this last query and let's dive into aggregate functions aggregate functions are functions that are built into database systems that you can use in your queries so in your select statements to condense multiple rows into one single number and it certainly becomes easier to understand if i just give you an example let's say we want to find out how many locations we have in total we could be interested in answering this question because we're analyzing our application data so now we could be the application owner and we might want to find out how many locations are currently available how many locations maybe were created by organizers of events and we could easily determine this by selecting from locations and instead of selecting all columns or specific columns as we did it before we now run such a aggregation function for example the count function which is built in which is supported by basically all database systems which counts the number of rows now you use the count function like this by using its name and then parentheses and then between the parentheses you pass the so-called function arguments and different functions typically want different numbers of arguments the count function wants one argument and that would be the name of the column for which it should count the values so basically the rows though it is worth noting that count will ignore null values so if you pass in a column that contains null values those would be ignored for accounting which can sometimes be the desired result therefore here i'll pass in the id column because of course the id will always be set it will never be null here for this table and hence this in the end will count all the rows of that table now as another side note just as mentioned earlier already i wrote it all uppercase i wrote count all uppercase but this is optional i'm doing this because i'm writing all these keywords and built in functions uppercase to make it clear that these are not names i came up with but that these are reserved names made available by sql or the database system but you could also write it lowercase because the casing as mentioned before doesn't matter so if i now run this i get 7 as a result here because i have seven locations here now besides count there also are other aggregate functions like min or max to get the smallest or biggest number in a given column or a sum to get the sum of all the values stored in a column we could do this here as well for the id column since we do store numbers in the id column but of course that sum isn't too meaningful it's just the sum of all our ids which yeah isn't that useful of an information i would say now as always you should always check out the official documentation of the database system or of the sql environment you are working with because there you can learn more about how it supports aggregate functions in general and you can also find out which aggregate functions are supported now max min count sum and also afg for average are typically supported by all database systems but it's still worth having a look at the official documentation now using aggregate functions as we just did it here to count all locations is one way of using them but you can also do more powerful things with those aggregate functions for example what if we actually want to get a list of all cities but then for every city we want to know how many locations exist in that city well we can do this for this we can start by selecting everything from cities and then since we're interested in locations we'll have to join our cities with locations since i want to get a number for all cities including the cities that don't have any locations i will perform a left join so that all cities are included in the result set and then i join my cities with my locations on locations.city name being equal to cities.name alternatively as before we use aliases like c and lock here so that we have less to type so that i use log city name here and c dot name now like this i'm not using any aggregate functions i'm just doing something which we already did before i'm getting a list of my cities with the related locations data but now instead of getting three columns for munich for example for the three different locations i would prefer to get one column for munich with a number of locations that exists in this city and that is something we can do with aggregate functions we can for example get our city name here in the result set and then actually count lock dot id as well so that we get the city names but then the number of locations so the number of location ids which is the number of locations for a given city however if i execute this i would get an error because now i actually must group my data now the reason for this is that now unlike before i'm not just using count but i'm mixing an aggregate function or the result of such an aggregate function with a regular column which is not aggregated now as i mentioned before aggregate functions do return single values they condense multiple rows into single values so of course that doesn't go well with a regular column which tries to reflect all rows that have values for that column so that's why we have to add an extra clause if we want to mix our regular columns with aggregate functions like this we have to use the groov by clause because this allows us to actually do aggregations for certain groups and we can group by c dot name now and we have to group by this to tell sql that we actually want to create groups here in our result set we won't aggregate down to a single value but instead a single value per group and a group is defined by the city name so every city forms its own group every city name forms its own group and then we'll just aggregate for each group so if i run it like this for example then i get my list of cities and then next to them the count the number of locations that we have for every city and then for example for frankfurt we see that we have no locations for berlin we have two for munich free and for cologne and hamburg we have one so that's how we can then aggregate four groups and if you think about it this group by clause makes a lot of sense because with aggregate functions you narrow down rows to single values and if you still want to have multiple rows in the result set you of course need to kind of create groups where for every group you then have a single value a single count in this case and just as before you can also assign an alias to this like num locations to make this more meaningful in the result set so that now in the results that we see num locations up here and then the number down there so that's how we can do that and of course we could have also performed an inner join here the difference would have been that in the result set frankfurt would be missing because it doesn't have a single location because of inner join it's not part of the result set anymore that's not what i wanted here so i will go back to a left join but of course it is important to be aware of the fact that an inner join could have been used as well so that's now group by now as a last note i also want to mention that you can also group by multiple conditions for example here we could also group by location street and include that here in our result set in the list of columns then so in that case if i run this i would have more granular groups i would have multiple rows for munich now but then i have a group per street and we for example see that we have two locations in the beer street and one location in garden street so group by can also be more granular you're not limited to a single group you can also have sub groups if you want to call them like this as you see it here now group by is very important when working with aggregate functions another important clause that you can use with aggregate functions is the having clause and for this i'll copy this and then comment it out so that we still have it for reference and i'll repeat it but without the grouping for the street i'll get rid of that by the way we have to get rid of it here in the column list that should be fetched if we got rid from it here in group by because if we would keep lock street here but not use it in group by we would get an error because again we would try to use some column here in the column list for which we didn't create a group so how should this be displayed then it would be impossible to build valid combinations out of these three columns and results if we only group by name that's why you have to add or remove both but that wasn't the main thing i wanted to get into here instead what if we don't just want to get a list like this but we want to only include cities that have a number of locations greater than one so cologne and hamburg shouldn't be included well one solution you might come up with is that you add a where clause and you look for count lock id being greater than one maybe if you try this however you will fail you get an error because you can't use aggregate functions here in the where clause instead the where clause is there to filter out rows in the raw result set before any aggregations were performed so we could do this before we group to for example a filter for log city name being equal to munich that doesn't make too much sense but this is a filter we could add here running this would succeed but of course now we only have munich in the result set because i'm filtering for that here but that's how you could use where you can use it to narrow down the number of rows that will then be considered for aggregation but if the aggregation itself is part of the condition that you want to have you need a different clause and that's the having clause having then allows you to do what i just tried to do you can use your aggregate function it doesn't have to be the same one as here by the way it could be a different one and it can also be multiple functions combined and there we can now look for our cities where the number of ids is greater than one so with having you can then pick up these results of your aggregation where the where clause is evaluated and used before aggregation happens having is used after aggregation happens so therefore now with having and this condition we now get only the cities that have more than one location so at least two and hence only berlin and munich are part of the result set now and therefore having is another important clause you can think of it as the equivalent to where where you use having to run after any aggregations that have been performed and that's it we learned a lot about sql and about the core features and commands that you have to know when working with sql you learned how to create databases and tables you learned about column definitions different data types constraints like not null check and primary key you learned about insert update and delete statements and you learned how you can query and also filter data you learned about the importance of relationships and how you can create tables that are related you learned about foreign keys with references and on delete and we then of course also learned how we can insert related data how we can run queries that merge related data with inner join and left join and you learned about the difference between these two different joins and that you can also combine them and have more than one join clause per command and last but not least you also learned about like which is very important for finding text that is like some other text and you learned about aggregate functions how they work how you can use them with grouping and how you can also use them to filter aggregated data and with that you can of course dive deeper into sql for example by going through our full course by exploring our resources and of course also just by playing around with it by coming up with example projects and demos and by then building your own tables inserting your own dummy data and running your own queries that's how you will learn the most
Up Next
Build a CRUD API with FastAPI and PostgreSQL: A Complete Tutorial
@witty.coder1
25.5K views•2024-05-25
IFS Therapy Demonstration: Complete Session with Unburdening
@IFSCA
95.9K views•2021-01-13
FastAPI vs Flask vs Django: Choosing the Right Python Web Framework
@TechWithTim
302.5K views•2024-05-26
Game of Thrones Opening Credits: A Cinematic Analysis
@gameofthrones
46.3M views•2011-04-18
Related Study Plans & Knowledge Roadmaps
Structured learning paths in General & Interdisciplinary Studies