Understanding SQL: Key Concepts and Learning Pathways

SQL is the universal language for managing and querying relational databases effectively.

### Mastering SQL: A Comprehensive Guide
* 📅 SQL was invented in **1970** and standardized by ANSI in **1986**, making it a **widely-used language for relational databases**.
* 🔍 SQL queries consist of **actions (e.g., select, update)**, **column specifications**, **table references**, and **conditions for data retrieval**.
* 📊 SQL databases can scale vertically by adding more CPU, memory, and storage, but **complex queries may require additional optimization techniques**.
* 🔗 Views allow users to **abstract and simplify complex queries** for **easier execution and maintenance**.
* 💼 Stored procedures enable the execution of **multiple SQL statements in sequence**, often used in enterprise environments for **transaction management**.
* 🛠 Object-Relational Mapping (ORM) frameworks like Hibernate can automate database interactions, but it's recommended to **master SQL queries** before using ORM.
* 🔒 It's important to practice writing SQL queries and work with different databases to become **proficient in data manipulation**.
* 💬 Share your favorite databases and SQL adventures in the comments, and remember to **like and subscribe** for more technical content from Señor España.

All right, let's go. It's time for a Señor Españ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 SQLLite, 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.

And 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 this 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 of whatever you want to do and, depending on what you're kind of going for, or a select if you want to read data out.

And then you're going to have either a wild 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 col1, col2 ... 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 = 1, a particular number, or you can do where some other condition, price > 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 or very, very capable. And 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 of other ways that you can go. Now, this is a very simple query, but it can get very complicated and very complex. Now, you can read any kind of guide on SQL online, and it will take you from this example to where you can actually write two of these statements and join data together from different tables. It's gets very exciting to work with. But as your queries get more and 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 just 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. So in next, another enterprise, we start to get to that enterprise tier that I mentioned before. They'll have functionality called stored procedures. All right, so let's do the store procedures. So store procedures. We do a called stored proc and I've worked with this a lot, especially in 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, so multiple operations that you want to do. It even has the capability to be able to pass in certain your variables and information that you want to serialize or hydrate this particular role of where 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 data bases 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 ORM, 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 mapped 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 ORM 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 ORM 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 would definitely want you to learn, it's the way that I learned this, and become the expert that I am today. So, in conclusion, this is SQL. I want you to definitely put in the comments below your favorite database that you like to work with and some of your adventures around SQL. I want to see you hear about some of those crazy queries that you've written before. Until next time, technically yours, Señor España. If you like this video and want to see more like it, please like and subscribe. If you have any questions or want to share your thoughts about this topic, please leave a comment below.

What is SQL?

Whether it’s AI, automation, cybersecurity, data science, DevOps, quantum computing or anything in between, we provide educational content on the biggest topics in tech. Subscribe to build your skillset, learn about new trends, and gain insights from IBM experts. 

This channel follows the IBM Social Computing Guidelines.



IBM Technology

Enterprise, developer, and embedded databases all share SQL (Structured Query Language) as a common feature.

SQL was invented in 1970 and became a standard by ANSI in 1986, providing a consistent way to store data in a table format.

The language's portability allows users to apply their knowledge across various relational databases, making it a fundamental skill in data management.

SQL queries involve selecting specific columns and rows from a database, with the option to use wild cards or specify column names.

The query also requires a From statement to indicate the database and table, as well as a Where clause to filter the data based on specific conditions.

As queries become more complex, they can involve joining data from different tables and may require additional resources to scale the database's capabilities.

Key Database Features: Views and Stored Procedures

Views simplify complex queries by providing an alias or abstraction, making them easier to read and execute.

Stored procedures allow for the execution of multiple statements in a row, with the capability to pass variables and manage transactions.

The choice of database depends on the project's needs, with enterprise databases offering more advanced features.

Understanding Object Relational Mapping (ORM)

ORM is a framework that helps map application objects to database tables, making it easier to manage data relationships.

It offers portability by adapting to the syntax of different databases.

While ORM can simplify data handling and obfuscate query writing, it's recommended to learn and practice writing SQL queries first.

The Benefits of ORM Layers in Project Development

ORM layers accelerate project initiation by eliminating the need to write extensive code.

The speaker emphasizes the importance of learning SQL and invites viewers to share their favorite databases and SQL experiences in the comments.

The video encourages likes, subscriptions, and engagement through comments for further discussion.