If you're going to be working with databases,
it is highly likely you'll be using some sort of structured query language, or SQL,
SQL. While there are variants of SQL, they all follow the same basic rules and definitions.
In this video, you'll learn what SQL is and how it works with relational databases. We'll go through
some use cases and investigate what a table is and what a SQL statement is, along with the basic
syntax. If you're wanting to start using SQL with databases, you've come to the right place.
SQL is a stronger type declarative language. This means it's easier to get it right, as the
syntax is more stringent and you only declare what you want, instead of having to program
it yourself. For example, you use SQL to define what parts of a dataset you want,
not how you get it. All right, let me show you. So talking about declarative language, let me
just show you a really quick example here. So I'm in SQL Server Management Studio, and I'm looking
at the AdventureWorks database. And we'll talk more about that later in this episode, as well.
I'm gonna click on New Query here, and I'm just going to copy, paste a bit of SQL so you don't
have to watch me type. So here we have a SELECT statement, and I'm just saying I want the Title,
FirstName, and LastName from the table called Person.Person where the first name is Mary.
I am not programming anything. I'm just declaring what I would like. So we're gonna execute this.
I am getting all of the records that has a first name with Mary. Behind the scenes,
there's a bunch of code that takes my declarative statement and obviously interprets it so that the
database can understand it. But that's all that declarative is. It's very easy to approach,
and it's very easy to learn because it kinda makes sense from the start because you're just declaring
what you need. A relational database management system, or RDBMS, is the system that your SQL
statement talks to, which then returns the data requested from the database. Microsoft SQL,
Oracle Database, and MySQL are all examples of RDBMS implementations that you could use SQL with.
Now we know what SQL is and where it lives. What can you do with it then?
SQL allows you to retrieve, insert, and update data in a database, given you have the right
permissions. You can create regular reports from the data, you can automate tasks that
manipulate the data and schema, and you can update data based on rules and other triggers.
SQL is easy to learn, easy to start using, and easy to create very powerful data operations
with. It is a language that keeps on giving as you learn about the intricacies and details of it.
Let's get into the more tangible aspects of SQL and how it works. In order for us to work with
any kind of data, that data has to be in a table. A table is a defined part of the entire dataset
being stored in the database. A table consists of columns, which each account for a single piece
of data within a row. For example, you can have a database called OnlineStoreDB.
Inside that database are a number of tables, one of which is Customer. You might've guessed that
it holds customer data. Each row in the Customer table represents a customer record. The record
is split up into multiple columns, such as UserID, CustomerNo, FirstName, LastName, and EmailAddress.
A SQL statement is then used to get that data out of, insert data into, and update
data in that Customer table. And that brings us to the syntax of SQL statements. Let me show you
how it all works by using a practical example. Now, if you want to join in, I'm using SQL Server,
the AdventureWorks database, and SQL Server Management Studio to type my queries into. They're
all free and linked in the resources. So I'm back in SQL Server Management Studio where we left off
just a little bit ago in the episode. And I've got my statement here from before. So
this is this SELECT statement. So SELECT means I just want to view data. I want to get data
out. Not manipulate it in any way. I just want to look at the data that matches my declarative
statement here. So that's the SELECT. In this particular case, I'm selecting three columns.
Title, FirstName, and LastName FROM. So that's the table from the table,
Person.Person. And then there's a condition, which is WHERE, is the conditional FirstName = 'Mary'.
So that's the SELECT statement and the syntax for that. Now, if I want to update a record,
I'm just going to copy, paste this again so you don't have to watch me type.
Here's an UPDATE statement. So if I were to run this, which I will in just a second,
I'm gonna update the table, Person.Person, and I'm gonna set the FirstName of the record to Lars, and
I'm gonna give it a condition on what to update. Now, this is critical. Don't ever have an UPDATE
statement without a WHERE statement. Because if I took out the WHERE statement in this case,
I would update every single record in the Person.Person database, table rather, to
have FirstName = 'Lars'. The WHERE here is super important, as it is with the DELETE statement,
which we'll get to in just a minute. So if I run this, execute this statement rather,
there we go. One row affected. That's good. You wanna see one row affected, ideally,
or just a very small number, right? Otherwise, you know something's wrong. Then I can,
in SQL Server Management Studio, I can right-click Person.Person. I'm just gonna look at the top
1,000 rows, just so we can see, here you can see down here, the row with BusinessEntityID 1 has
been updated to FirstName = 'Lars'. If I just go back here. So that's an UPDATE statement, right?
You're updating a record, one or more records, as few as you can get away with. You've gotta be very
specific with your UPDATE statements. So in that vein, let's have a look at the DELETE statement.
Looks like this. DELETE FROM Person.Person. Again, there must be a WHERE statement. There
must be a conditional. Otherwise, you would delete the entire table,
right? So WHERE BusinessEntityID is equal to 1. So this is obviously just gonna delete it.
I could also say WHERE FirstName is 'Lars', or whatever, but be as specific as you can. An ID
is always good 'cause you know you're targeting a single row. So I'm going to click Execute.
Oh, and we have a constraint here. So this is a good thing. You can see here
that there is a foreign key. FK is foreign key. That means another table is dependent
on this row, so I can't delete it. Now, this is really good because it's a safeguard that says,
hey, you can't delete a row that another row in another table is dependent on. So you can see
here that now, SQL Server saved me from making a mess of it. But that's the DELETE statement,
and this is how it would look. Now, if I wanted to delete this particular row, I would have to go and
first delete all of the other rows in other tables that are referencing this particular person.
So I hope that makes sense. But that's a DELETE statement. And then finally,
we're gonna have an INSERT statement. Like so. And you can see, this is one of the more verbose
commands because I'm saying INSERT INTO and then a table name. I now have to define
every single field or column that I want to insert data into. So we have BusinessEntityID,
PersonType, NameStyle, Title, Firstname, LastName, EmailPromotion, rowguid, and ModifiedDate.
Now, you might be wondering why didn't I choose all of the columns, 'cause this is not all of the
ones that are in this particular table. You can see here, if I expand Person.Person under Columns,
some of these fields have a null. That means that you are allowed to insert nothing. A null value
is nothing. That means I don't have to define them when I do an INSERT statement. All of the
others that say not null, I do. This case, I've defined a BusinessEntityID. That's the next
one in incremental order for this particular row. I have a PersonType. Guessing that's employee,
I'm not sure. NameStyle is 0, whatever that is. I need to give it a title. Lord, 'cause I always
wanted to be a lord. Why not? FirstName, LastName. Then we have the 0, which is the EmailPromotion.
So that's saying, I don't want an email promotion. And then it needs a rowguid. So that's a unique
identifier for the row, and you can just have this. This is a function
in SQL, NEWID. That will return a guid. And the same with CURRENT_TIMESTAMP. It's a function
that will return a date timestamp. So I can use that. So if I run that,
you can see I get an error again. These are good. We wanna keep the errors because we
learn from those. The BusinessEntityID, I can't actually define that because it's a primary key
that has a foreign key constraint. You can see here, the foreign key,
Person_BusinessEntity_BusinessEntityID, is depending on it.
And you see the conflict occurred in table, Person.BusinessEntity. So I can't actually
use that. So in this case, I would have to go to the table, which is defined near the end,
Person.BusinessEntity, and insert in a row that would then match this particular
value, 20778. So this is part of the safety net, I guess, with SQL, is that you set up these foreign
keys and primary keys so that you don't get into trouble. That is the basic syntax of the commands,
SELECT, UPDATE, DELETE, and INSERT in SQL. And now back to, well, me. SQL queries is a
very approachable way to select, update, delete, and sort data from any dataset.
This episode showed you what SQL is, how it relates to an RDBMS, what you can use SQL for,
and finally, how to use it in a variety of statements, such as SELECT, UPDATE, DELETE,
INSERT, and WHERE. Of course, there's a lot more to SQL queries, but you can find that information
on the A Cloud Guru platform. If you enjoyed this video, give it a thumbs up and consider
subscribing to the channel for everything cloud computing. Keep being awesome, Cloud Gurus.