The video explores the foundational elements of SQL (Structured Query Language) and its critical role in managing relational databases. SQL is a standard language used across various databases such as Oracle, MySQL, SQL Server, and PostgreSQL, allowing for the storage and manipulation of data in table formats with rows and columns. Learning SQL once enables its portability across different database systems, making it a versatile skill.

The video discusses the anatomy of an SQL query, breaking down components such as the action types like 'insert', 'update', or 'select', specifying columns, and using conditions for data retrieval. When queries become more complex, tools like "views" can simplify SQL queries by abstracting complexity for better readability and execution. The enterprise feature of stored procedures is also highlighted, as they allow executing multiple operations and incorporating variables for dynamic interactions.

Main takeaways from the video:

Please remember to turn on the CC button to view the subtitles.

Key Vocabularies and Common Phrases:

1. embed [ɪmˈbɛd] - (verb) - To fix something firmly within a surrounding mass. - Synonyms: (integrate, incorporate, implant)

Enterprise databases like Oracle and IBM DB2 and Microsoft SQL server developer databases like MySQL and Postgres, and even embedded databases like SQLite all have in common?

2. relational database [rɪˈleɪʃənəl ˈdeɪtəbeɪs] - (noun) - A type of database that stores and provides access to data that are related to one another. - Synonyms: (table-based database, SQL database, structured database)

SQL stands for structured query language. Now a couple of quick facts about this particular language which is used to process and deal with storing of data inside of relational databases.

3. portable [ˈpɔːrtəbl] - (adjective) - Easily transferable from one place to another. - Synonyms: (transferable, mobile, movable)

So because it's so portable, once you learn this language one time, you can use it across almost every relational database that actually functions off SQL.

4. alias [ˈeɪliəs] - (noun) - An assumed name used to simplify complex actual names, mainly in queries. - Synonyms: (pseudonym, pen name, assumed name)

Now a view is a way to kind of abstract away the complexity of your particular query where you can kind of alias it or simplify it so it's easier to read.

5. serialize [ˈsɪriəˌlaɪz] - (verb) - To convert into a format that can be easily stored and transmitted. - Synonyms: (convert, encode, format)

It even has the capability to be able to pass in certain variables and information that you want to serialize or hydrate.

6. obfuscate [ˈɒbfʌskeɪt] - (verb) - To deliberately make something unclear or difficult to understand. - Synonyms: (confuse, blur, complicate)

And so it makes it very, very portable for me to go and really obfuscates away the ability of writing queries.

7. hydrate [ˈhaɪdreɪt] - (verb) - To supply with ample fluid or bring to life data structure. - Synonyms: (activate, bring to life, energize)

It even has the capability to be able to pass in certain variables and information that you want to serialize or hydrate.

8. framework [ˈfreɪmˌwɜːrk] - (noun) - An essential supporting structure of a building or a system. - Synonyms: (structure, system, architecture)

And there'll be a series of frameworks out there.

9. condition [kənˈdɪʃən] - (noun) - A specific requirement or rule in a program or process. - Synonyms: (requirement, stipulation, provision)

Now, the last part of that is the, where you have some condition that occurs and that can be a filter based upon like I'll do a simple one, like where id equals one.

10. transaction [trænˈzækʃən] - (noun) - A completed agreement between a buyer and a seller to exchange goods, services, or financial assets. - Synonyms: (exchange, deal, operation)

You'll find other things of doing transactions, and these are very good because they're able to process a series of actual queries.

What is SQL?

It's time for senior expiring a pop quiz. What do the enterprise databases like Oracle and IBM DB2 and Microsoft SQL server developer databases like MySQL and Postgres, and even embedded databases like SQLite all have in common? Well, you're probably starting to Google right now, but let me just kill the suspense here. They have what's known as SQL in common. SQL stands for structured query language.

Now a couple of quick facts about this particular language which is used to process and deal with storing of data inside of relational databases. The first, it was invented do a little I there invented in 1970, that is almost 54 years ago, many, many moons before even I was even born. And then roughly around 14 years later, it was made as a standard by the ANSI organization in 1986. Now the big part about that is that it is a standard that everybody learns as a technology here. The cool thing about this is they made a way for all these databases to function by being able to store data in a table format. So you're going to have a series of rows and columns, and that is how you store your data.

Now the second part about that is the big part about SQL is how you compose these queries. And so because it's so portable, once you learn this language one time, you can use it across almost every relational database that actually functions off SQL, off the standard that's there. So let's break down the anatomy of a query, and then I'll give you some tips on how to kind of get started in your learning and really become an expert overnight.

All right, the first is you're going to have the actual action of what you want to do. So it's going to start out with the insert the update, okay, of whatever you want to do and depending on what you're kind of going from, or a select if you want to read data out. All right? And then you're going to have either a wow card, which is saying, give me everything from that particular column that I have, that row that I have, or you can specify the actual column names that you want. So we'll just say col one, col two, you get the gist there.

Next you're going to say the from statement. So you have to tell it. All right, I want data. I want to do this action. I want these particular columns. But where do I want to do it on? Well, it'll be the database name and the actual table that I want to do it from. Now, the last part of that is the, where you have some condition that occurs and that can be a filter based upon like I'll do a simple one, like where id equals one, a particular number, or you can do where some other condition, price is greater than 50. Whatever you're doing is really about being able to select the proper data out of your particular database now so that you could do all these queries.

They're very easy to do. And then you're going to have ways where you can actually escalate this because these databases do hold quite a large amount of information, are very, very capable, and in their nature they always scale kind of vertically. Naturally by adding more cpu, adding more memory, adding more storage to fulfill the needs that you need from that particular project that you're working on.

But let's talk about a couple other ways that you can go. Now this is a very simple query, but it can get very complicated and very complex. You can read any kind of guide on SQL online and it'll take you from this example to where you can actually write two of these statements and join data together from different tables. It gets very exciting to work with, but as your queries get more complex, you're going to want to go across two different things that I want to discuss.

That should be top of mind when you want to do the first is what we like to call a view. Now a view is a way to kind of abstract away the complexity of your particular query where you can kind of alias it or simplify it so it's easier to read, it's easier to execute, and it can be fine tuned from there to work. So in this particular one, if I was doing, let's say we had books and authors, books in a table, we could say select my favorite books as the name of the view, but it actually has this particular context underneath the actual query.

All right, so next on other enterprise, when you start to get to that enterprise tier that I mentioned before, they'll have functionality called store procedures. All right, so let's do the store procedures. So store procedures. We do it called stored Proc. And I've worked with this a lot, especially in MS, Microsoft SQL Server. So it is a way of having multiple statements that you want to execute in a row. It can get as complex, multiple operations that you want to do. It even has the capability to be able to pass in certain variables and information that you want to serialize or hydrate. This particular role of way they should execute.

You'll find other things of doing transactions, and these are very good because they're able to process a series of actual queries, and if something goes wrong, I want you to roll them back, all right, to do all the data there. So, and then lastly, what I love to mention is I do want you to, after you kind of conquer a bit of these, and these are all dependent upon the actual databases that you're using. I had my start in MySQL and postgres, and then I started to work with a lot of enterprise databases where you had more of these particular enterprise features that you could kind of do.

And you pick your database depending on the project that you want to do. But in today's world, you're going to kind of see this particular acronym a lot, which is object relational mapping. So arm, and there'll be a series of frameworks out there. Now, you're going to be confronted with that early in your education on SQL, but I highly recommend that you go through the basics first because what this is going to do is be able to say you have you map the context of your application, whatever kind of object that you're working with.

So say I was doing that building that bookstore database with books and authors. So I can just say books, create many or create one. All right? And depending on which database I use, it will actually, the aurum software framework will map to the syntax required for that particular destination database. And so it makes it very, very portable for me to go and really obfuscates away the ability of writing queries. But I do highly recommend that you get into the practice of writing queries first.

Work your way up, work with different databases, and then depending on your project needs, the arm layers do actually make the process of getting started with a project very, very fast because you don't have to spend time writing all those. But that's kind of the way I definitely want you to learn. It's the way that I learned this and become the expert that I am today.

Sql, Databases, Technology, Innovation, Education, Databases Tutorial, Ibm Technology