Time
7 hours 36 minutes
Difficulty
Beginner
CEU/CPE
3

Video Transcription

00:00
I will come back to Module three sequel programming. This is less than one sequel statements, lesson or sub lesson 1.3 This Lex statement and the previous lesson. We installed the acumen axiomatic a framework database with test data, and that was a large schema, and it'll be good practice.
00:18
So let's go ahead and hit the ground running and get started.
00:21
So I'm gonna go ahead and click on my database, my axiomatic, a database. I'm gonna set it as active so that my intelligence works correctly, would expand the tables,
00:32
and we have a lot of tables to deal with here. But that's don't get intimidated by that. When dealing with a large database, what you want to do is focus on the tables that you're interested in,
00:45
and in this lesson, we're going to be interested in the user's table
00:50
so we can type users up here and the search box, and that will quickly filter down to the table
00:56
we need. In fact, if you find yourself using some kind of software that doesn't have a filter,
01:02
you're probably going to want to dump that software and get some software that has a filter
01:07
I have seen some examples of people using that and having to scroll through
01:11
a ton of tables, and I just waste too much time. So
01:17
make sure to use filters when available or find software that has filters available,
01:22
and we are again interested in the user's table. So I'm gonna go ahead and give that a double click.
01:27
It's not showing any relationships,
01:32
which does happen. Sometimes I know there is a another table that this ties to.
01:38
In fact ah, we'll discuss that later. But right now let's take a look at the user's table.
01:44
So that's all the data, but we're going to select specific data. So we're gonna say New sequel editor
01:49
It was a select
01:52
and select again is just almost self explanatory. It saying Select and then you're gonna list what you want to view.
02:00
It's sending a command to the database. I'm selecting these items to view, as in the results set.
02:06
So I don't know what I want yet, so I'm gonna jump down to the next sequel statement and safe from the from statement is saying
02:14
Okay, you want to view this data from where and where is a table so I'm gonna list the table. We're interested in
02:22
users,
02:23
and I'm not gonna put any constraints yet. I'm gonna go ahead and go up here and select the columns that I want, So I want company I D.
02:31
Now Company idea is a common I concept in a lot of large database software,
02:38
essentially what it is. The software can be broken into companies. For example, a company might have two branches or two companies. Maybe there's one in
02:47
Virginia and another one in Florida, and they each need to have their own data sets. And then the software will permit them to assign company numbers to specific data sets so that those companies could keep their data separate from each other if that's desired. And you see that in a lot of large software, even though
03:07
there's probably a lot of companies that don't use that future.
03:08
But you need to be aware of it because accidentally pulling from all companies can quickly skew your data sets. So be sure to check for that company I d field in whatever database you're working in, and take note of it if you find it because you will need to be sure to be specific about what company you're querying whenever you're ready. Query.
03:29
So we're gonna put company idea in there. We're gonna go ahead and put the P K I D, which is the primary key identifier. We're gonna go ahead and put full name in there.
03:37
We're gonna put is approved now is approved. Means
03:39
are they enabled? Are they allowed to use this application? So
03:46
let's see what we get with just this.
03:49
All right, So I see a bunch of Company two's.
03:52
I do see one company one,
03:54
but it would seem that company to is our primary company.
03:58
So I'm gonna go ahead and put in a constraint to say where company I d is equal to two
04:05
and that'll get rid of our ones.
04:09
And sure enough, now it's just too.
04:11
And let's do this, Let's say
04:13
and
04:14
let's see if there's any disabled users is approved equal zero.
04:23
Okay, it looks like everyone is approved.
04:26
Was one
04:28
Okay, so
04:30
let's go into this application and see if we can change the data.
04:42
So
04:43
in this framework, we can see and this is just given example how similar sequel is between different database engines.
04:48
This was like almost the same query with a couple differences in field
04:54
and conditions. I believe, let's see
04:58
pretty
05:00
pretty much the same. And this is running on Microsoft's equal. So this is a different database engine
05:06
that, with a simple query, is pretty much identical. So that's the neat thing about getting really good at sequel. You'll pretty much be able to go into any database environment and almost start working.
05:17
Now. When I say almost are working is because there are differences. Sometimes sometimes there's function calls that are different, and sometimes it's features that you can't use. So you do have to, ah, learn what those are and then react appropriately.
05:30
So we see that we haven't is approved of zero.
05:35
And ah, this guy is the same guy that's there.
05:43
Anonymous, anonymous, So let's enable him and see what happens. We will enable that user
05:47
and I have to use Microsoft's equal because that's the database. This is connected to you right now,
05:51
but it's the same quarry. So let's go ahead and rerun this query we changed them to enabled. Let's see what happens.
05:59
Oh, he went away. He's not there anymore. Let's go back into the application
06:03
if I can find it. Okay, here we about Here we are again. What's going to disable him again
06:11
and let's see what happens now.
06:15
And he's back. So what's going on? Well, applications use databases for data storage. That makes sense so
06:23
and the application. When we change the setting, the framework sends a data base command but then changes the data to the appropriate setting.
06:33
Eso
06:34
What happened, was, is when we click the disable or enable button, the axiomatic a framework sent a sequel command
06:42
to the database to update that data to reflect its setting on the front end.
06:49
So that's how the database in the application interact.
06:55
And if we go back to our primary learning area, which is the my sequel,
07:00
we won't be able to do that in this
07:03
case with this my civil database, because we're not connected to an actual application so we can't go into the application to change it.
07:10
That's the basics of how an application interacts with this database and why reporting from a database is so powerful because you're getting the data that the application is saving to the database so that it itself can ruffians that later to show users that of this relevant and remember key things like disabled users.
07:30
So that was the selects statement. Okay, that brings this lesson to a close. And just to recap, we learned about select statement and how to interact with the database selected view data.
07:42
And that is basically
07:45
finding the table you're interested in that holds the data that you're interested in selecting what you want to see from the table. You want to get it from where the conditions are true.
07:56
And that brings this lesson too close. I hope you enjoyed it. Hope you got your queries organ. And I will see you in the next lesson. Thank you.

Up Next

Introduction to SQL

This introductory SQL training teaches SQL core concepts that can be applied in professional environments. Once students complete this course, they will be able to query and interact with an SQL database, and know how to design database schemas.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor