Benefits of Learning SQL

So welcome, my name is Dan Rodney and welcome to SQL. So for everybody online, a couple things you need to do to be ready. There is a workbook to download, a PDF workbook that you can download from Classes Portal.

For anybody online, you want to download that PDF ebook. And then in there, there are instructions for installing DBeaver. So actually, there's two things you want to download for those people who are online.

But for anybody online, download the PDF ebook and also download the class file. Also for everybody online, you want to make sure you install DBeaver. The instructions are in that ebook.

Thing to know, when you go to download DBeaver, we are downloading the free community edition of DBeaver. It is from DBeaver.io as the workbook instructs, not DBeaver.com. DBeaver.com is the commercial paid version. DBeaver.io is the free community edition, not the pro, not the commercial version, the community edition.

You want to download that. It says that there's an update. You can install the update, but you don't have to install the update.

If it needs updating here, don't worry. They put out updates every two weeks. Every two weeks, like clockwork, they put out updates.

So don't feel the need to update every single two weeks. Databases aren't changing that much. The app is not changing that much.

So it's, I think in years, I think maybe there was kind of one material kind of impactful change that I saw that needed. Other than that, like, don't feel like you have to update every single two weeks. So I just want to cover just a couple of fundamental things here before we dive in and get our hands on the program and start to code.

We have a little bit of understanding, a base understanding here before we get started here. This is the better microphone here. So, so first of all, what are we learning here? Why are we learning it? We're learning SQL, which is a structured query language.

So what is this all about? Basically, when you have your information in a database, lots of companies have their information in databases. We want to be able to go in and extract that. We want to be able to query it or retrieve that data from a database.

And so if your data is just sitting there, not telling you anything, you know, you could be missing out on insights. We want to be able to go in and retrieve stuff. And so today we're assuming that you only have the ability to question or to query, to, to retrieve information.

There is another side to SQL, which is managing databases, where you can actually create databases. You can insert new records or information. You can update ones that are there.

You can delete ones that are there. Now, imagine if you can go in and change and delete information in a database. Do you want just anybody in your company being able to do that? Messing up your database, bringing your application or your website down? No, you don't want just anybody doing that.

So because of that, most people do not have the privilege of being able to change the database. You can look, but you can't touch. You can't change things in most situations.

We're teaching it, assuming that like most people, you will not have the ability. You will not have the permissions to change the database. When we're doing data science, data analytics, we're going in and looking at the data.

It's enough to look at the data. You don't have to change the data that's there typically. And only people at a higher level that have more experience that we understand, we can trust with our data.

Only then would we trust with the ability to change the database itself. Now I could be wrong. It's possible that somebody will trust you to change the database.

But when you're first getting started with SQL, you don't really want to get started with that side of things. You want to get started with just the querying the stuff. And then later you could upskill and learn about how to manage the database or create databases.

So normally only database administrators or DBAs have permission to do that. We're not going to be getting into that side. We're just all going to be looking at data and trying to retrieve it and find useful things and find specific types of information in our database.

The good news about learning SQL is that it is just focused on databases. That's all it is. It's trying to question or retrieve or query the data, pull it out so you can see just what you're looking for.

So it's just working with database data. That's it. So because it only does kind of one thing, it's a lot smaller than a lot of other languages.

And you can learn this, I think faster than you can learn most other computer programming languages or computer languages, just because it's smaller and more focused. It's also been around for a long time. What other technologies are you using today that were created in the seventies? Not a whole lot of technologies.

So for the most part, they kind of figured out databases, figured out how they worked. And it really hasn't changed very much over the years. Because you store data, you put data in, you take data out, you look at it.

It's really, it's not changing year to year. So that means that once you learn it, you can basically know it for a long time and use it. I can't imagine this going anywhere anytime soon.

I don't think that they're going to change this. It's been the way that databases work because it's been around for like 50, over 50 years now. I don't think they're going to be changing it.

So good news. You can use this and probably you could use it for the rest of your life. That's can be said by about very little technology these days.

You compare that to something like JavaScript or Python, that's changing all the time, new libraries coming out, new ways of working. So it's kind of cool that there's one thing that you can learn and use for a very long time. It's pretty rare in technology.

There is no specific standard SQL though. So people talk about SQL as a language. It's not really SQL as a generic language.

There are flavors or kinds of SQL. So kind of like with English, there is no specific one main English, no perfect English. In the UK, they do a little bit of a different flavor of English.

For example, color is spelled with a U. We don't do that here in the US. Down in the South, they'll say, hey y'all. But we don't say that here in New York.

So there are different flavors of English. Same thing. There are different flavors of SQL.

Are they all English? Yes. Are they all SQL? Yes. But there can be minor differences here and there.

Sometimes bigger differences. But fundamentally, if you can kind of speak or write one flavor of SQL, learning another flavor is pretty easy. You just have to learn the differences.

There are oftentimes when things are exactly the same in all flavors. But then there's sometimes when some things are different. Today and for this class, we're going to be learning Postgres.

There is Microsoft SQL Server. Normally, they just call it SQL Server. And all of our class materials are written for both Postgres and SQL Server.

There are other flavors as well out there. But all of our class materials talk about the two kind of most popular ones, which are Postgres and SQL Server. When we're going through, if there's slides, for example, I'll typically just skip past the SQL Server things because we're not learning SQL Server.

But if you ever do need to learn SQL Server, you can always look back at the slides. And in the coding files, when we work with those, if it doesn't say anything about a flavor, it just means that they're both the same. But if it says Postgres and SQL Server, if there's two things today in class, we'll always look at the Postgres side and we'll ignore the SQL Server side.

And that means that there is some difference between them, whether that's a minor difference or a bit more of a major difference between them. But just know that whichever one you learn first, you're learning stuff that is mostly applicable to the other flavor. But there might be some differences if you ever do need to switch.

Essentially, the companies that you work for, they will have decided what kind of database they're going to use. Are they going to use a Postgres database, a Microsoft SQL Server database? And that will determine the flavor that you have to use because it's determined by the database itself. Is it a Microsoft SQL Server database? Is it a Postgres database? And so when you walk into a company, it's not like you can say, well, I only know Postgres, so can you use Postgres? They will say what their database is, and then you have to use that flavor of SQL.

But they're not so different from each other. Once you learn one, it's pretty easy to learn the other one. You can always Google what's the difference between this flavor and this flavor.

So the one that you know and the other one. Also, speaking of if you're ever Googling or using ChatGPT or anything like that, don't ever just say SQL. Say your specific flavor of SQL.

If you're looking for how to do something, you're looking for how to do it in this class in Postgres because that could be different than SQL Server. Also, when you get search engine results and you're clicking on a link, always make sure that you see, is that for, for example, Postgres, which is what we're learning? Because if it's for SQL Server, that might be different than Postgres, which is what we're learning. T-SQL is also the Transact SQL name shortcut.

That's also what officially SQL Server is called is Transact or T-SQL. If you see any of those labels, those are all for Microsoft's SQL Server. But we're going to focus on Postgres.

Okay, so what we're going to learn. First, we're going to see how we connect into our database. Basically, there's a server on the internet storing our data, and we're going to connect into that server, and that server stores a database.

We're going to connect into that. Once we connect into that, we're going to browse through the database, look through it. If you move to a new city, do you know any of the streets or the restaurants or anything? Do you know what's there? No.

Does it help you to kind of drive around the streets and kind of see what's there and start to get to know things? I say the same thing about a database. You don't know what data is there if you've never been in a database before. You don't know what kinds of information.

You don't know what kind of user information, what kind of product information. So the first thing before you ever start writing a query is, what can I even ask questions about? You need to explore that database, look through it, get to know it a little bit, and see what's there so that you can then start to write queries about the information that's there. So for that, we need to use an application.

And the app that we're going to use is the free dBeaver Community Edition. Now, dBeaver can connect basically to any kind of database. It can connect to Postgres, SQL Server.

It works on Mac and PC. So it's pretty much the kind of Swiss army knife of, I can connect to anything on any platform. It's free.

It's wonderful. I like it. There is also on the SQL Server side of things, Microsoft SQL Server Management Studio, which is like the world's longest application name.

And people that are using SQL Server can use that. But I actually typically like to use dBeaver because once I get used to dBeaver, I can connect into any kind of database. So that is what we're going to be using is dBeaver.

We'll learn a little bit about that application, but really it's just what do we need to know to be able to browse our database and start to get to know it so that we can start retrieving data using our SQL code. There's some things you can do within the interface, but for the most part, we're going to be writing code to retrieve this stuff. We're writing our queries to question that data and extract it from the database.

But first we need to connect into that database, which is sitting on a computer somewhere. So kind of like when you browse to a website, you type in the address, same thing. We're going to do that, but we also need the privileges to log into the database.

We need a username and a password to be able to log in, kind of like logging into your Amazon account to see purchases that you made. What we're not going to be doing, as I said earlier, is talking about database management, database architecture, building tables, building databases. We're going to assume that you're given a database by a company and you want to be able to go into that database and pull out information and see what's there.

Also, there's a lot of things just to start learning SQL. We're not going to get into high-level performance optimization and the differences of the speed of this query versus the speed of this query. We're just getting started with our queries.

So it's enough to just start learning how this stuff works versus worrying about high-level performance optimization as you get to a much higher level.