" 15 天 SQL：完整的 SQL 大師班 2024 " 線上課程免費部分預覽

Hello and welcome to the world's best SQL course By the end of this course, you will be an expert in SQL No previous experience is needed All it takes is just one hour per day for 15 days And my promise, by the end of these 15 days You will be an expert in SQL And this can, of course, completely change your career Hi, my name is Nikolai I'm a senior BI consultant best-selling and top-rated instructor on data analytics and data science teaching in over 170 countries all around the world And my mission with this course to make you an expert in SQL And this can completely change your career As a student of this course you will get access to 14 hours step-by-step video tutorials as well as an incredible amount of real-life challenges For that, we are going to set up two different databases locally in your machine so that you can work on everything yourself hands-on That's what's actually going to make you an expert On your 15-day journey we are going to cover every single important aspect of SQL starting all the way from the basics your first lines of code going then to group by classes, functions, joining tables And after you've passed the intermediate level you're also going to learn about managing tables and databases using data definition and data manipulation statements After that, you are going to step things up even more and you're also learning about all the real pro topics like window functions, grouping sets, stored procedures rollbacks, user management, working with indexes and much more But of course, everything will be step-by-step with simple and clear explanations so that you can work on everything step-by-step and hands-on And the most important thing is that this course is designed for everyone whether you've worked with data and databases before or you are a complete beginner and have absolutely no idea where to start This is the most comprehensive course you can find on this topic And after you've completed that course my promise, you will be an expert in SQL Of course, at the end you will also get your certificate of completion to show your skills to other people and potential new employers And if you should ever get stuck in the course I'm always there to help you out This course comes with a no-questions-asked 30-days money-back guarantee if you should not be 100% satisfied So I'd love to start this journey together with you And for that, click on the Enroll Now button and I'm happy to see you inside the course Let's start with the question Why should we even bother learning SQL? And to understand the importance of that we first want to answer the question What actually is SQL and what do we use it for? So very quickly SQL is just short for Structured Query Language This means that SQL is the language we use to interact with a database That means, for example that we get data out of the database and we can, for example, also update or write data into the database So let's have a look at how this works in practice We all know that data is super important in all companies And oftentimes, this is the most common scenario Data is stored in a structured form And for that, we need to have databases This means that in a database we have a set of tables So a table in a database is just a structured form of data This means that in a table we have columns and rows So this is what we mean with structured data And now, if we want to interact with this database as we have mentioned we want to use SQL for that to, for example get the data out of this database But also as we've mentioned we can use SQL to analyze the data This can, for example, mean that we join it with different tables We'll learn later on what that means We can aggregate the data make some calculations And with that, get more insights out of our data But of course, we can also write data back into the database and update existing data So this is super important because a database is oftentimes behind many IT systems And on top of that we can also connect a database with different other applications For example, Power BI, Tableau or many other applications So now that we have understood what actually SQL is and what we are using it for Let's come back to our initial question of why should we even learn now SQL So we've learned data is everywhere and oftentimes it is stored in databases And now my opinion and my statement that I'm super convinced of that SQL is maybe the most important skill in a broad range of professions And this is especially true if you want to become a data analyst data scientist, business analyst and even more professions that are not so specialized in working with data So you can benefit from the knowledge and the skill in SQL in a broad range of professions And in many, if you are working with data it is in my opinion even the most important skill And luckily on top of that it is also very easy and intuitive to learn So this is a great combination because it's super important super beneficial But luckily it is not very complicated And it is very easy and intuitive to learn And with that Mastering SQL is a great career booster And this is why I recommend SQL The number one thing to learn If you are planning to become a data analyst, data scientist Or just in general Want to advance your career And use this to do that So with that in mind And hopefully it was a good motivation for you We now want to dive a little bit deeper And understand what is a database So we now want to dive deeper And after that We immediately want to get hands on And start learning SQL Now let's get started with installing the tools That we are going to need in this course So this will be one PostgreSQL Our database management system And then on the other side We also need pgadmin Our graphical interface And luckily we can install Both of them together in one package So let's go ahead And jump into our browser Where we are going to search for PostgreSQL download Then the first link That is not an ad of course Should be PostgreSQL downloads So this is the link that we are going to use And in here we can now Choose our operating system first So this video is now of course for Windows If you are on a Mac operating system Then you should of course just use the Mac video In our case we are therefore Just going to select Windows And in here we can now go ahead And download the installer This will just make it a bit easier for us To install both of these tools together So that's why we just click here on download And in here again we can now Just choose the PostgreSQL version For our operating system So since I assume that you will probably Also be on a 64 bit version Most of the Windows systems nowadays are 64 bit That's why we are going to select this top link in here And note that of course This version can be different in your case If there's a newer version available But you can just go ahead And download your latest version And if you should have some problems Then you can also try a lower version But usually you shouldn't have any problems If you just go ahead With the latest version that is available for you So in my case it's 14 And that's why I will just click here on this link And this will now download this installation file So once this has been downloaded We can just navigate to the folder And open up this installation file And this should now open up the installation wizard So in here we can just start the installation By clicking on next The directory where this will be installed We can just leave at the default Click on next And for all of the components that are here checked We can also just leave them at the default As I've mentioned We can install PostgreSQL Together with pgadmin in one package And those other two We don't really need But anyways we can just leave them at the default So once we have all of them checked So we don't need to change anything in here We can click on next Also the directory for the data storage We can leave at the default And just click on next Afterwards we have to set up a password For the database super user So the username will be Postgres And in here This is not really a security issue And that's why I recommend To just use a very simple password So this is very important That you remember this password So that's why I encourage you To use a very simple password And also to write it somewhere down So I will just use The very simple password called password And again this is not a security issue Because you are just installing this Locally on your machine And anyways we are not using any sensitive data So that's why I just use the password Called password And I click on next Afterwards you have to select the port number And also in here You don't need to change anything Just use the default setting in here So in my case This is probably the case also in your situation It is 5 4 3 2 But even if you have a different number Don't worry Just use the default number that you see So I'm just going to click again on next And also the locale We can just leave at the default settings And afterwards We just click again on next on next And then the installation will be started This installation might take a couple of minutes But then afterwards it should be completed And now we just need to uncheck this box And now once we have unchecked this box We can click on finish And we have successfully installed PG Admin together with PostgreSQL Now if we want to start up PG Admin We can do this by just going to the window search And in here we can just enter PG Admin So this is what we are going to select And then this will open up in a new window And we have to of course now Enter our password That we have chosen in the installation So in my case This was the password called password So that's what I'm going to enter in here Then I can just click on OK And then I will see at the left side Those servers So in here I can expand them And I will just expand also the latest version Because I've installed also some previous versions But if you don't see them Don't worry Just click on the latest version that you see And then once you expand this In my case PostgreSQL 14 It will show the available database objects So we don't need to be concerned With all of them right now But we see that we can expand the database That's already installed Which is called Postgres And in here we can now also expand the schemas So we have already talked about The fact that we have schemas in our database That we use to organize our tables And other database objects So they are basically like folders And the public schema is the default schema That is usually available always in the beginning So this is all that we need to know And now we of course want to also set up Our database that we are going to work with So that's why we want to set up this database In the next lecture And then get to know SQL And also get a quick overview of pgAdmin So that's what we are going to do in the next lecture Let's introduce the course project In this project You are in the shoes of a data analyst That just got hired by a company That has recently acquired a business Called Green Cycles And this is an online movie rental shop So this course will be super hands on And as realistic as possible So this is why you will play the role of a data analyst And you have to help this company To operate the database This means that you have to write queries And help the company to succeed Because this data in this database That we are going to work with Is super important for the success of the company And this is why your job is to work on this database Work on these tables Write queries Analyze the data And basically help the company To succeed with this new business This is why you need to gain insights And help the company to solve problems And of course step by step Your skills should also progress And get more and more advanced So the database that you are going to work on Is this database with many different tables So you see that it's fairly complex Which helps you to get a real life scenario And learn SQL for real life scenarios as well So step by step Your skills in this course should advance And we will do this in this project Where you are going to explore meaningful data Get insights To help the company to make the right decisions And basically just help the company to operate And as mentioned Step by step Your skills will become more and more advanced The queries will become more and more advanced And you will learn everything That you need to know about SQL And working as a data analyst on this database So this is now the course project And now that we have this in mind We can start immediately With the first SQL statement And this will be the SELECT statement That's what we are going to have a look at In the next lecture This is now your second day at your new job At the company Green Cycles as a data analyst And this day starts normally With you walking into the office But there at your desk already The marketing manager is waiting for you And he doesn't look very happy This is because yesterday we remember That we have used this command SELECT distinct amount from payment To get the list of all of the different payment amounts And if we scroll down We find that there's one entry With an amount of zero And this is why the marketing manager Is now so concerned And this is why he is asking you About a list of all of the payments With an amount of zero And this is of course What we want to help him with And that's why we today Want to learn about filtering our data This is where we want to introduce The WHERE clause So let's jump into first quickly The very short theory And then we want to solve this problem For the marketing manager So the WHERE clause Is the clause that we use To filter our data output It's very simple And it always also comes after And immediately after The FROM statement Let's quickly have a look At how the syntax looks like So we just select the columns And then after the FROM We use the WHERE clause With a certain condition And then all of the rows will be output Where the condition is true Let's have a look at an example For example We would like to filter the amount Of only this amount 10.99 This means we can just select everything From the payments table And then we can filter the amount To the value of 10.99 Very simple Also if we want to filter a name So this means a text string So if it's a text column We can again just use the same query Select from the customer table And then we filter WHERE First name is equal to Adam And now since this is a text We need to enclose it In these single quotes This is how we can filter also text columns So these are the very basics Of the WHERE clause Of course later on in the next lectures We learn a lot more variations And use cases Different operators And things that we can use In combination with WHERE But these are just the very basics That we now want to of course apply To give this list to the marketing manager That's why we now want to jump back Into pgAdmin and solve this problem So what we want to do We can first just query again Select star from payment Just to get again an overview of the table And in here we see now that We want to now filter this data So we want to filter that To the column amount Where amount is equal to zero And then if we execute that We now get a list of all of these values And now if we don't want to scroll down Because maybe it's just a lot of rows And we just want to know How many payments have there been Where the amount is zero We can also just again combine it For example with count star And then we can count How many payments have the amount zero So if we execute that We see this is now 24 payments Where the amount is zero Of course if we want to for example Filter our customers So if we want to filter some text We can just again use the name So first name from the customer table And now if we want to filter The first name And we want to search for Adam for example We can again use these single quotes Because it's a text that we want to filter by And then we can say Adam And we can execute that And see first name Of course if we now also want to see The last name We can also include the last name In our search So these were the basics of the WHERE clause And in the next lectures We learn a lot more variations And things that we can do in combination with WHERE Different operators And different use cases But now again it's time for you To apply this knowledge And practice this in the following challenge