PowerBI Self-Study Course & SQL Deep Dive: Truncate vs Delete

Hello. Hello. What's going on? >> Hello.

>> All right. All right. How you doing? >> Well, and yourself? >> I'm doing pretty good. Pretty good.

Pretty good. So today, Esa Noic. We're going to

do a few things. Um, I want to show you guys something. It's

not done, but I'm going to show it to you anyway because I'm super excited. And this is going to be something that

you guys are going to see. So, I'm going to just bring this over here

and go ahead and start talking about it. So, what you're looking at

is a self-study course. I'm revamping the whole self-study course portion of class, updating my skills. I'm about to

This is going to be massive undertaking. And by the time we get to say January, this probably gonna

have 30 different horses in here. Um, I got a block get this one, but my brother developed

this to where um, you can come in here video.

>> Welcome to the lifting the veil PowerBI course. I'm glad you're here. In this first lesson, we answer the most basic

of all. What exactly is PowerBI and why do companies everywhere pay good money for people who know it? PowerBI is

Microsoft's business analytics platform. Its job is to turn raw data, spreadsheets, databases, cloud services

into interactive reports, and dashboards that people actually use to make decisions. It isn't one program. It's

three parts working together. PowerBI desktop, a free Windows application where you build reports, the PowerBI

service, the cloud where you publish and share them, and PowerBI mobile so the people you share with can read those

reports on a phone or tablet. Build on desktop, publish to the service, consume anywhere. That flow is the heart of

everything in this course. PowerBI Desktop is where the real work happens and it costs nothing. You download it,

install it on Windows, and you have the same tool the professionals use. There is no paid version of desktop with more

buttons. Everything you'll build in this course you'll build here first. Here's the workspace you'll live in. Across the

top, a ribbon just like Excel. In the middle, the report canvas where visuals go. On the right, three panes, filters,

visualizations, and your data fields. Down the left edge, you can switch between the report, the raw data tables,

and the model view. Don't memorize it. Now, >> now this is the thing. The reason I

wanted to show y'all this, look at all this. So, it's going to give you

everything. You're going to have this actual um documentation down here. So, this

isn't like Udemy where you just watch a video and that's it, right? You're going to have links that you download. You

gonna have screenshots. As you can see, it's going This is a little bit more in depth than what's in

the video. Then by the time you get down here, you going to have labs, which right now

you don't have any labs in here, but by the time we get down Oh, and by the way, you have a quiz.

So, this is going to be very thorough. And by the time you get done, now we got 93

different I should 93 different ones. That's just PowerBI. Um, when I'm done,

I'm going probably do Tableau next. I might take this blockchain down or I might put it back up later. I don't

know. I just looked at I was just testing something out. Blockchain is a little bit different. Blockchain is

snippets from the blockchain course that we offer. So,

>> digital signature is a mathematical technique >> and it's doing everything. It's telling

you, it's taking screenshots. It's showing you everything. Obviously, you you you know, you have to kind of Well,

I got it kind of small right now. So, let me make it bigger. But, as you can see, you can go through

here, look at everything, you know, and as you can see, I'm I'm very very excited about this. My brother

created this. Nobody can do That's why I ain't saying nobody, but nobody in the market is

offering this. He's actually able to create this stuff and it with my voice and likeness, but

it's it's all AI based. Now, I gave you guys Python. I developed this myself. I've developed the syllabus. It's just

20 courses. I mean, 20 uh videos to get you a introductory, you know, uh set set up with with pandas, I mean

with um Python. I don't know Python, but it would be good to go through this as a as

a class. I don't want to stretch the class out because this now we know probably March or something. But a

lot of this stuff will help you guys. And if you want to do it on your own, you can. But this stuff will really take

you guys to the next level because now you have Python. And when you got Python, so you might have Python, you

have PowerBI. I'mma add Tableau. We got Azure Data Factory, right? But remember guys, now you already have over here. I

already gave y'all Well, right here. Yeah, right here. I already y'all SSIS, SSRS.

And I could probably what I'mma probably do is take these videos at these

do and so I'm I'mma see if he'll make this sort of make videos explaining how to build all this stuff. Man, look, it's

it's it's going down. Like the level of knowledge and information that I'm gonna be able to offer you guys now is is

it's just whatever it really is. like if I want to put uh SQL server data engineer, not SQL server

um engineer, I already been looking at it. So I've been looking at Azure

data engineer certification. So they got 700 and 750. 750 is dealing with fabric data engineer and 700 I'm just probably

start with 700. But this is, I'm telling you, this is really gonna make a difference as far as

putting us in a position where you guys are really in a good space, man, where y'all can start really winning. You know

what I'm saying? As far as what when you hit the market. So, that's what I tell you guys. I always tell y'all, man, like

you got to be willing to put the time in. If you guys are willing to put the time in, I'm I I told y'all I'm give

y'all my heart and soul. I told y'all that the very first class. You know what I'm saying? So, if you guys are really

serious about trying to do this, I'mma develop this. This is going to be probably embedded in the site. I'm

probably not going to reveal it until January because I'm still working on it. But, as you can see, I don't know if I'm

going to do all this. Now, this one, I don't even need that no more. So, I can get rid of that. But, these are classes

we've offered. We've offered all This was my first class. This is my brother's class. He offered he got like eight

months of videos. Uh we got Salesforce administration. I I done about four of those. Data engineering was done. Data

science. Um Salesforce architect. So we got all these different skill sets. Now

obviously I don't believe in getting 30 different skill sets. What I'm probably going to do and it's just me. It it's on

it's on you guys as far as what you want to learn, how far y'all want to go. Because if you go through this PowerBI,

you go through my SQL course, I'm going to give you SSIS, SSRS. So, you going to have all that and we might as well going

to drop a little Python in there. You ready? You ready? You can be a data analyst anywhere you want to go. And and

if you you know, long as y'all subscribe, you can always come back and get the uh PowerB I mean the Tableau.

And it's probably going to be 30 40 different disciplines down here. It's going to be all types of stuff.

So, it's just it's going to be a a smogus board of stuff and you guys can just come in here, click videos like the

data factory. Like, it's literally, you know, coming in here. It's really

going in on data. >> Acronyms, three letters each describing the exact same three jobs. Getting data

out of a source, changing its shape, and putting it somewhere useful. The only real difference between them is the

order those three jobs happen in. And that order changes more than you'd expect. ETL stands for extract,

transform, load. Data gets pulled from the source. Transform in a separate processing engine.

>> Man, we we gonna have some fun, man. I mean, it's on y'all what y'all want to learn. But look, I mean, it's it's

giving you information. Now, there are no screenshots yet on this one. I might add I'mma see. I think it's early. Let

me see. Did I put screenshots on data factory? Let me see. I'm Did I'm sure I did.

Yep. It's got screenshots. That's what I'm saying, man.

Look here. If you willing to put the time in. So, we got videos as well as I got screenshots. I got everything

explained. And obviously if you don't know something, you can you got Chad GBT to

kind of explain it to you. But at least you guys will be able to see everything and start really working with some of

this stuff, man. See here, it's even giving you a lab and it's giving you some questions

to make sure you know what you're doing. You can't you can't beat this. if I do say so myself. My brother built

this um B basically has the functionality. He built some stuff. You know, my brother's, you know, I'm Did I

let him? I don't think he talked to y'all yet. I don't think he came to the class yet. I don't think. No. So, I'mma

have him come talk to y'all one day, but he he built he built this, man. So, he's kind of helping me with it. But, this is

crazy. So, by the time I'm done, we'll have we'll have a bunch of stuff. Oracle

Financials. I mean, I don't even know if that doesn't necessarily go with this. And

this will be a long this will probably be two 300 videos, truth be told. Maybe more. So, I don't know. That's going to

probably be a massive undertaking. Probably take me a week or two to do this one. But stuff like data science,

data engineering, these go hand in hand. You know what I'm saying? So, this is really going to change what

you guys are capable of. And and like I said, you know, you're going to have my skill set, but then I'm

I'm not the best data factory guy. I am I know SSIS like the back of my hand, but now we'll be able to start going

through here. I can go through this with y'all. We can do these together. So, I go through SSIS and then we jump over

here, start messing with data factory. So, then we'll be able to go and open up create our own, you know, account in

Azure and start, you know, extracting, transforming, and loading data. And then, you know, we got these here. This

is just something I was playing with. I'm probably not going to touch this, but this basically what it did was it

went and looked at videos. Let me show you what it did. That's why I'm like, man, my brother a

trip, if I say so myself, cuz uh I mean, he got this thing to go and grab everything. I was shocked. And

so he uh let me see. He went and got this playlist, all these hours of content. and it went

through, looked at all this content and made that course based on what was being taught.

That's what it did, which I thought was crazy, but it did it. And so that's what you're looking at right here. So, it'll

be able to go through this course and I can make a course based on this course, which I'm not going to do. I don't have

to. But what I'm saying is it's crazy and it's subject based. So it's, you know, and of course this is

the class, but then you can go back and he got it set up where, and like I said, the PowerBI, this one took like a few,

this was the first one was PowerBI. Remember guys, I was filming all those videos myself

and I had them over here. Where they at? See, I was going through here making them, but

I I kind of got right here and then he started telling me he gonna make some stuff for me. So, I slacked off and he

did it. I mean, he really did it. I'm I'm like, God. And he did. He actually built it, you know. So, here we are. So,

we're able to really go through. This is really meticulous what you're looking at. 93 videos. It's going to cover

everything. It's got a big section on DAX, too. See here? DAX formulas, fundamentals,

date, time, visualizations. I mean, he really, you know, based on the syllabus I gave

him, he really put it put it together. He done showed me how to do it, but so I know how to make them myself, but it's

his thing. I don't want to because I was going to say, man, you can take this on the road start trying to

help people, you know. Um, so if y'all want something like this built for yourself, he this whole website he

built, too. So, I'm not going to use this website. I'm probably just stick with mine. But still, but as you can

see, this is So, I'm going to embed all this on my website where you'll be able to get to it. But this is crazy, man.

To me, this is crazy. You know what I'm [clears throat] saying? Cuz I I know you know how long it took me, man. It took

me a while. And that's supposed to be my voice. Obviously, it doesn't sound like me as

much as I would like it to, but still, I don't really care about that. You know what I'm saying? Man, I could have I

could have used anybody's voice, but as you can see, man, it it's really going through blowby-blow and doing

everything. Merging queries. It's going to show how to merge. Look at there.

This is crazy, man. It's telling you where to click, what to do. It's showing you the little wizard. It's talking

about all the joins. Y'all know that. Y'all already know what this is. See what I'm saying? It's It's

breaking it down thoroughly. Then you got a lab. Now, I think I had a I told it to use um

I think it was Yeah, it's right here. So, I think I started saying use Adventure

Works and data warehouses. I think it's data warehouse 2014. Let me see.

Yep. So, it's telling you where to go. This the data. So, y'all got this back up.

So, it's telling y'all to mess with the dim fact table. So, it's explaining all that, man. It's explaining everything.

So, I was going to probably provide this as, you know, monthly for lifting the veil for other people as well, but I do

think it's better if you go to the class live. Obviously, it always is. But at the same time, this is something, man.

I'm telling you, by now, by the time January get here, I'm probably have 40 different skill sets in here. I'm going

just tell y'all which ones we going through and I'm going to take that and put Yeah, it's going to be crazy. It's

going to be crazy. So, man, if you want to put Now, you got something where you can learn directly

in here. Now, it's not like, well, I'm just giving you a book and and you got to go watch the self study, which the

self-study book is decent. It'll get you where you got to go. But this is like even as far as taking notes now. You

don't really have to like I mean, look at this. Ain't I mean it's everything's right

there in your face. This is better than uh to me better than anything I've seen that, you know, as

far as being able to have actual stuff that you can go and get. This is crazy. So you can go get everything

and look at it and really thoroughly look at it. Then guess what? If it don't work, you can go

jump in the chat GPT. You can go open up uh PowerBI and start doing stuff. You can't beat that.

Cannot beat that. That's just my personal opinion. I don't think you can, but I ain't going to say you can't beat

it. Like I'm I'm being a little whatever. But still, this is, you know, a great thing, great tool. I'm

super excited about it. I've been I was up last night to like 1:30 messing with it. I'm just going to keep going. I'm

not going to put it on the on the website yet. I'mma probably put it on the website, but I'm not going to make

it live until we get through because I know what y'all will do. Y'all start jumping around and I don't want that. I

want y'all to make sure y'all focus on sequel right now. I'm gonna probably put a sequel piece. That's probably what I'm

gonna do next. I'mma put SQL in here, transact SQL, and I'mma tell it to cover all the constructs that I cover. I'm

going look at I'm going to give it the note cards. Everything that we cover is going to be in here, and it's going to

go through it. It's going to do screenshots, and it's really going to break it down. But the beautiful thing

about it is it's going to go to the MSDN. Like if I type this here, it's going to go.

Where is it? Where's What's the name? Just don't have Let me hold on. It's going to go here.

It's going to go here and get basic. It's going to get everything directly from the horse's mouth.

And it's going to take those scripts because guess what? If I take this script here, like let me look at this.

These scripts work with Adventure Works 2012 usually. Let me see. So like the scripts,

they used to have scripts unless they done changed it. Let me see. They done probably Let me

see. No, here they go. Well, this is 22, which I can go get a backup of that if you want to, right?

But look, I mean, you got the actual scripts right here that you can actually start taking code. So, it's going to

have a code just sitting there. So, everything you guys need to learn is going to be right there in your hands.

And I'm going just give it to you so you don't have to worry about. It's going to all be accessible from here.

All of it. It's going to be accessible from here. That way you guys don't have to worry about anything. This is This is

really going to take on a whole new meaning. Self-study quizzes. I can just about get rid of that. I'm paying every

month for that really. And a lot of people don't even use it. But that's what I'm talking about. These are prior

classes. I'm going just leave them in here or I might just put them in a whole separate tab and just make all these one

big page. And it's just, you know, I'mma keep this. I'm going keep how to install SQL Server, but the self-study is going

to be everything. Or I might just put embed it under here. So when you Yeah, I might just embed everything down here

where you guys come in and you just click it and go to it and it's going to take you wherever you got to go. I ain't

mean to go off on a tangent, but I want to I'm excited because I see this as a improving a massive improvement for for

Lifting the Veil as far as really getting you guys because my thing is turnurning out competent sound

developers that understand what they're doing, understand the constructs, and know how to code. So, yeah, my brother

put me on with just I I called him about four or five times like, "Bro, I don't think you understand what you did." You

know, so I've been telling him like, "Bro, you need to really start marketing this because he could do this for any

subject, not just tech, any subject. You want you going to do a a course on how to cut hair. He he'll

have it he probably spit it out in like an hour. Like he he didn't got like with it. So anyway,

now let's have class. Let's go ahead and finish up these scripts.

All right. We was down here Saturday. We came in

here and we stopped right here. I talked about all the joins and I I specifically

told you guys about all the different joins and I said this is something that we need to be able to understand because

this is what's going to make a difference as far as when they ask you about the different types of joins.

There are eight of them, right? Eight different types of joins. The other two have to do with joining

valued function. I gave you guys this the actual um explan of that inside the Facebook group.

Well, that's data modeling. I know I put it in here. Set operators. Where did I put

At any rate, I know I put it in here Saturday, but Saturday I answered I I gave you

guys all these different questions and we talked about table valued functions. We talked about how you have a left join

for the outer apply and an inner join represents the cross apply. And then you got the self join where you

join the table itself. Full join, left and right join, inner join, cross join. That's eight. That's how you do.

We're going to talk about views. Now you guys have seen mess with views in SQL server. You got tables,

views. Now views. If right click and select view. So what

this is when it's ran in the background what we are actually running in the background

you're really is all this and I hate this one cuz it's

doing too much. I didn't want to do all this. That's what built this. But I don't want

that. I don't want to do that. Let me see. Let me just do this one there.

Look at what it's doing. It's running all this in the background. But when I select from it,

this is what it does. If I turn on the execution plan and then execute,

look at what it did. It did all this. So, it didn't just do a regular select from it. It did the execution plan for

this because if I run the execution plan for this, it's going to be identical. See there? So that's basically what's

happening in the background when you select from a view. So it's a named select state in so many

words. Everybody understand that? >> Gotcha.

Now given that it's a named select statement, a virtual table that doesn't exist and when it is used a select

statement runs in the background. Select star from view for example. When you add an index to a view it

materializes in the database like a regular table. So if I go over here and type up the

word materialized view a database the premputed results. Let me go down

here. Materialized view pattern. So what is a materialized view? Go back.

So the query the view materialize view store

procedure. It should be when it has an index but let me see F index

anyway. I guess that's not it. But anyway, so let me forget that. Let me forget. Anyway, a view is a name select

statement that can that's stored in the database. It does is not actually physically stored in a database.

Views are not actually stored in a database. A view

only becomes stored in a database like on a physical disk of a database when you add an index to it. When you add an

index to a view, then it's like a table. is stored in the database on the disk like a table. Other than that, every

time I select from this in the background, it's running this. If I make this into a a table or if I put an index

on it, it will become it'll be it'll be just a select with a table scan more than likely for the uh execution plan.

Everybody understand that? All right. Now, we're going to talk about a primary key constraint, which

really isn't much to talk about right now because we ought to know what a primary key constraint is. A primary key

constraint uniquely identifies each row and won't allow nulls, right, in a column.

When you create it, it creates a clustered index automatically on the table. A clustered index

has three levels. Roof, I mean root, branch, and leaf level. At the leaf level are what? What's at the leaf level

of a clustered index? >> The actual data pages. >> That's correct. physical data pages

where the data is stored. That is correct. Right? Then you have unique key. Well, hold on. I ain't gonna go

there yet. Now, this is pretty much what we, you know,

gives a t a table it unique. It says uniquely identifies each row, right? So, you know, when you see a list

of the employees at a company and you use the last four of their social for their primary key, that's your employee

ID. What's your last four of your social, right? You we've all been there with what's your last four your social?

That could be a primary key that they use to uniquely identify who you are as a customer or as an as a employee.

Right? So, if you want a list of all the employees, you take the last four of their social,

right? If you want to if you're looking at a customer

and you want that customer to you be unique, you give them a primary key. You got a primary got a customer ID, you got

employee ID and even each each one is unique. You cannot have a null value in a primary key column. If you got a null

value, you got to use something else and it's called a unique key constraint. What is a unique key constraint? It

uniquely identifies each row in a column. Unlike the primary key constraint, it allows one null.

So you can have one null value in a unique key con. Once you have a unique key constraint, it'll it'll let you have

one null. Everybody with me? One null value. What that allows for is it enforces uniqueness, but it allows

for one null. Now when you create a unique key constraint it it generates an index automatically

as well but it it generates the other index. So if it's not a clustered index it's a

nclustered index. Everybody with me? >> Yeah. here.

>> Now on the nclustered index at the leaf level you have what? >> Pointers.

>> Pointers. >> That's right. Sisters >> automatic. That's right.

So these two are unique. They enforce uniqueness. But this one unique key

constraint allows nulls. Allows one null because guess what? One null still is unique.

So it's still unique. It just enforces uniqueness. You can place this on other columns.

Now it can't be a primary key because it got a null, but it can be unique. Now

what's the difference between a clustered and nclustered index? We already know.

Somebody tell me what's the difference. I want to know everything exhaustively. What is the difference between a

clustered and unclustered index? Somebody tell me. Come on, guys. Clustered and none. We

just talked about it. What's the difference? A clustered index has a root, branches,

and leaves. And uh they have pages at the leaf level where it contains data and then non-clustered index has

pointers that point to the data >> data memory location. Correct. >> Yeah.

>> What else? There's another difference. >> One more piece. I want all the juice out the question.

Oh. Uh, >> clustered clustered index can um man,

>> it's actually two differences. I I'm just I'm just I'm just kind of want y'all to squeeze y'all just kind of

learn one of them. >> A clustered index with a view can be materialized,

right? >> No. No. Okay. I'm not sure. >> So, I'm gonna go ahead and tell you.

>> Okay. >> So, at the leaf level, they both have a root, branch, and leaf level. At the

leaf level, you are correct. You have the physical data pages where the data stored on disk. For a clustered index,

you can only have one clustered index and 999 nclustered indexes. That's the difference.

>> Yeah. Yeah. >> Okay. Now also if somebody ask you you say and also when you create a unique

key constraint it automatically generates a nonclustered index and when you create a primary key constraint it

automatically generates a clustered index. I I gave them every answer.

You do not leave anything on the table when they ask questions. That's why I gave y'all these note cards. But now you

ain't gonna have to worry about the note cards because I'm going to make this into a complete course and when you guys

are reviewing you're going to be straight. Don't worry about it. Let me go back.

That being said, clustered indexes uh actually stores data in the data

pages at the leaf level of a bit tree whereas nclustered indexes use pointers at the leaf level. These pointers point

to the location in the memory and actually store the data. Now, that's not all. I wanted you guys to squeeze out

two more answers. You got the unique key constraint and the primary key constraint. Primary key constraint

automatically generates a clustered index. Think about it. You can only have one

primary key. You can only have one clustered index. You can have multiple unique key

constraints on a table on different columns. But you can't have a one primary key.

Think about you got a table with a 100 columns and each column you got seven 75 of those columns got unique key

constraints on them just hypothetically saying. So you got 75 clustered indexes on that table but you got one nclustered

index for the primary key. So because you can have up to 999 nclustered indexes and one clustered

index. When they ask you these questions, this is definitive. You got to give them the definitive answer to

where they there are no if hands or may about your skill level. All right.

What is the difference between a delete and truncate? I'm going have to really zoom this in.

Now, I want to show y'all something. truncate

when we ran that truncate because we actually tested this out. When we ran truncate,

truncate is first what type of language is it? Somebody tell me. Anybody tell me what truncate is?

Data manipulative. >> Nope. Data definition. Correct. DDL.

data definition language. That's what truncate is. Just like insert, update, delete is

what? Data manipulation. >> Correct. So you got DML and you got DDL.

Data definition. What does that mean? When you execute it, what's the difference? So when you run a data

manipulation language, it's going to tell you the amount of rows affected. Whereas when you run a data definition

language statement, it says command completed successfully. Right? So when we run like a create a

table, command completed successfully. If I drop a table, if I alter a table, command completed successfully because

those are data definition languages. But if I do an update and I update five rows in there, it's going to say five rows

affected based on the wear clause that I gave it. Or if I update the whole table, it's

gonna say if it's got a thousand rows in it, it's gonna say, "Okay, you got a thousand rows in there." And it up the

command completely successfully. I mean, not I'm sorry, it won't say that. It'll say 1,000 rows affected.

That's that's one difference. I told y'all data definition, data manipulation,

right? as it pertains to the log the the the log got so you got a log file for a database which tracks what's being done

in the database. This is what database administrators use. By the way, I'm going add database administration to the

courses. I'm going add that too. But anyway, we have a database admin administration.

I mean, I'm sorry. When you have a DBA, my mind gone. Sorry. we have a DBA and they're working and something happens

and they need to reverse an event that took place then if it's a data manipulation they can literally go

back and reverse rows being taken out or update or deleted but they cannot reverse with the you know they can't

reverse truncating so if you truncate it can't be rolled back when I say roll back I mean roll back in the sense of

I'm not talking about transaction control language not talking about TCL it can't be rolled back with the restore

or something like that so you got to be careful with transaction control I mean not sorry you have to be careful with

the truncate cuz you dump the records out the table those records are gone it's going to say command completed

successfully in the log file that's what it's going to have just that one entry whereas it's going to have a hundred

different entries if you delete aundred rows out of a table it's going to 100 entries in that log file. So which one

is faster? The one that just have one entry. So truncate is faster than data

definition. So I'm giving you a bunch of different answers. I love when I go on an interview and they ask me this

question because I sit there and just start talking like I'm talking to y'all. That's what I do. This is how I get

jobs. Truncate is faster and uses fewer systems of uses fewer system and

transaction log resources than delete. See there truncate is faster. It removes

data by deallocating the data pages used to store the data on the table basically and only the page deallocations are

recorded in the transaction log. See there. So when you truncate something, you can empty the table, but that's a

problem. Now you got to be careful because it just records that, right? It doesn't. Whereas delete does something

different. We'll get to that. Truncate removes all rows from the table, but the table structure and its columns,

columns, constraints, indexes, and so on remain. So the structure of the table's intact, right? says the counter

by an, you know, used by an identity column for new rows is reset, aka the seed column. Now, what is an identity

column? I done told y'all. Y'all don't probably forgot, but I'm going to show you.

Remember identity columns. Just grab this here and I'mma script

this out. Ah, this ain't got no identity column. Me close it.

Me go here. Let me go up here. Look at there. What does that mean?

That means when I do an insert, you're not going to see a insert for the ID. You know why? Cuz ID is a ticker.

If a row gets insert inserted in there, it's just going to add one to the identity column. So we got 1004 records

in this table right now. Right. So let me show you. I'm going just run it.

We got 1004 records. If I insert a new row, it's going to be 1005. If you go down here

to the identity column, let me slide this out. As you can see, the very last one is 1004.

It's a ticker. And if I go back over here, if I Where is it? Right here. If I go

Okay, I'mma insert. Watch this. When I insert into and I did it, it doesn't even notate

that I need to update the ID. See there? It automatically knows that the ID is going to be 1005

because it's identity column. And another thing I want you guys to look at, if I go right here and I say edit

top 200 rows, it's going to be grayed out. See there? The identity columns are grayed out.

So it doesn't when I'm whenever I'm putting a new row in there, I don't have to update or insert a new identity

because it already knows. Now, if I go in here and I delete all the records out this table, let me go

ahead and show you. Hold on. So, I'mma script out a table first. Actually, I'mma do this.

I had to get crazy with it. Boom. So, we got our table. I'm going just create it.

And it's got the identity column. Y'all see that? So, now staff has the identity column.

I'mma comment this out. Oh, man. I got to go over to lab. Lord have mercy. I forgot.

All right. So, I'mma comment this out and then I'mma insert. Hold on. Let me go. Oh yeah. Do this here.

Insert into hashtag staff select. I'mma run these values in. I'mma get rid of this ID because I don't need it.

I don't need that. Now I'mma run it. Now I'mma select everything from staff.

Now you saw I just didn't insert no ID because it's the identity column. So what's that mean? It's going to have 104

records in there. Watch this. So if I run this, there you go. That's the ID. If I go all

the way down is 1004. Y'all see that? Now we finn to have some fun. So, I'm going to come here

and I'm gonna say delete and I'm going just put the table name. That's all you got to do.

So, I'm and look now delete is data manipulation. So, what did it do? It says 104 rows affected. The the log file

tracked all 104 rows that I took out. Right? So when I come up here now, ain't nothing here, right? Let's rerun this up

here and see what happens. All right. So it says 104 rows, right? I'mma commit this out

now. I'mma select it. Well, I'll be it started at 105. It did not reset the identity column.

So when you run a delete statement, it doesn't reset the identity column. But we was reading and it said truncate

does. So if I run this select ain't nothing there. Now I come

back up here and I run it again. Then I come down here. It's starting back at one again. So the

delete is skipped and went to the next row. But whereas with truncate it reset the identity column back to zero. If you

look at the way the table oops if you look at the way the table works it has an identity and it's saying hey

start with one and increment by one. That's it. Everybody understand that?

Now, >> hey, when you say start by one and increment by one, is that why

>> the first row is going to be one and then it's going every time you insert a row is going to go up by one.

>> Okay, I got you. Okay. Whereas >> I could put five and increment by 10, then it'll be five,

>> 15, >> 25, 35, 45 like that. Or it could have been five increment by five. 5 10 15 20

25 30. Right? So that's what it means. You can set it how you want to set it. Would it be a what would be the reason

why you would set it uh otherwise if if you had to? >> I mean, usually when I see people set

it, it's just one, two, three, four. I mean, it it it doesn't Nobody goes and does 5, 10, 15, 20. I hadn't seen nobody

do that, but you can. >> Yeah, >> I got you.

>> All right. So, like it says, truncate removes all rows from the table, but the structure, like it says, stays intact.

It resets that aka the high watermark, also known as the identity column. says you cannot truncate a table uh

referenced by a foreign key. So if it's got children pointing to it, that primary key, it can't be deleted

because it's got children. Remember, you can't kill nobody with kids. That's the that's the rule right

here. says using SQL truncate cannot be rolled back unless it's in a transaction or truncate can be rolled back when you

use the begin and end transaction like you know that's it. So if you put truncate inside of a begin transaction

it'll it'll it'll it'll roll back that way but it can't be rolled back if it's in the log file.

Truncate is data definition obviously and it says truncate resets the identity columns. That's it, right? We already

talked about that right here. Delete removes rows one at a time. That's why I just said 5,000 rows affected, 1,000

rows effect. That's what it does. And records each one as an entry in the transaction log,

right? It says delete does not reset the identity column. We just saw that I deleted the records and it started at

105. We just saw that. It says delete can be used with or without a wear clause. Y'all just saw me said delete

and table name. I didn't even say delete from. I just said delete table name and that that's going to delete every record

in that table. So you got to be extremely careful with a delete statement or you will remove all the

records from a table. And why do that unless you're trying to you might just want to remove one record but I knew I

was going to do that. Proper syntax would be delete from table name. But I just did that's proper syntax too.

Delete and table name. Delete can be rolled back. Obviously, we talk about roll back. We're not just

talking about the transaction. We're talking about from the log file. And it says delete is data manipulation

command. Delete does not reset the identity column on a table. Everybody got that?

Now, I'm going to tell y'all something. When you go on the interview, they're going to ask you this question. You got

to be able to go through it and kill it. All right. Now, I'mma stop right here because we got to do uh got to do the

lab. Let's do the lab right quick. Let's see. Where is it?

Uh that's lab one. I could have swole. I put it in here.

Let me see. Let's go. All right. All right. All right. All

right. Let's see. New query. All right, guys. So, let's see what we

got. We're going to create a table called Hold on. Let me comment this out. Ain't nothing to it. Let's knock it out

right quick. We had a table called hashtag movies. Hold on, hold on, hold on. I needed the

instructions. All right, got this. I ain't worrying about all this. We just gonna come in it

out right now. Let's see what we got. Okay, we got a table called hashtagmov.

Create table. Hashtagmov.

I'm assuming that's ID. Yo, here it is right here. I gave it to you.

Close this. Boom. And get rid of that. Run this. That creates our table.

Right now, we going to insert everything. So,

I'm copy this. Well, first, let me get rid of these. And I'm going C

that back. And we're going to do a insert. Insert into

hashtagmov values. Boom. Obviously, this is probably not

going to work because of this. Got that one. Bring that like that. And like that.

And we going to come down here. Do this one. Actually, I like to bring I like to line my stuff

up a little bit like that. See what we can do.

Let's get it popping. That's it right there. And this one

with the comma. Oh, got to get rid of this. Get rid of this.

It's tedious. Get rid of this. Bring this over here.

Let me put these up here. I'm tired of doing them. Don't get this out the way. Got that. Get this out of the way.

Get rid of these. Missing one. Got. Okay. What is this? Transformers. Transformers.

Just make something up. There we go. I think that's it. Is it? Let me see

what's going on. Am I missing something? GI Joe to something up. Let me see.

That should work. Am I missing something? Oh,

might need these. There we go. Got them in.

So, we got that. Now, we're going to do these. Got all this commented out. I'm going

just comment this all out. Bring this down here. See what we got. Says UCTE with ranking

functions. Gather the two house from each genre. Okay. Ah, select. Oh, wait a minute.

semicolon with movie_ct as

it says select. Okay. Top two. Wait. No. No. No. No. No, I don't

want to do it that way. It's um I can number it. Yeah, I'll number it. So, select

asterric from hashtag movies. And then we going

to bring this down and we going to say cuz we just want one and two. So, row number

is it underscore? I can't remember. Yep. I think over I'm doing this off the dome. I got

to remember. I think it's a over parenthesy. I think over

partition by genre.

Let me make sure that's the is that the name of it thing in a way genre. That's it.

Partition by genre. Order by uh what is that? Box office gross

descending. Maybe that's wrong. I think that's wrong. Let me see. Hold up. It's wrong.

Maybe this. Hold on. Let me see something. Nope. It's not that. Is it this

big? Oh, comma. Hold up.

Uh, still probably wrong. Let me see. Must have a over. I did a over row number over. Is it? Hold on. Let me see.

That can't That's not right. Syntax ain't right. Let me see. Is it over? Is it here?

Let's try it. Got it. Now, see, we got this over here. Y'all

see this? All we got to do is where it's in one and two. So, we come here

and obviously it's it's so it's grouped by genre and it's in descending order. Y'all see that?

Descending order. We want the top two. So we say select asterric from

movie CTE where

can't spell today where oops I didn't give it a name. Call it row number

just RN. RN less than or equal to two.

I'm going run it. There we go. We got the top two from every jungle. I got asked this on an

interview one time. It was really painful, but now you guys should be able to figure it out.

All right, let's keep going. All right, it said create. Oh man, get

this out of the way. Create transactions. Delete to everything in the western genre.

Select to show their deleted rows, then bring them back without reinserting them.

Okay, so that's a begin transaction. Begin trend. And we're just going to do this. Select

aster from hashtag. Right. Hold up. Let me get this out the way.

So, we going to select everything. I'm going copy this. We're going to come down here. It said delete.

And it said delete western. Put that right there. And then

bring that back. Then I'mma roll back. I'll come here. Select. There we go. So, let's do it.

Boom. There we go. So, we got all 10 of them here. And when I get here, it says six. See there? It took

the four out. Then when I come back down here at the bottom, the four are back. It's back to 10. So, that worked.

Update. Okay. So, what did it say? It says update every title in the table to null and then select to show then

change them back. Okay. So I'm going just copy this come here to the end. Hold on. Let me

you got a question Carter? Yeah, I do. So, uh I know this just in the exercise, but why would the title

get set to low? >> Huh? I just did. It was just something I just

made up. It was no specific. >> Oh, okay. >> Yeah.

>> Okay. Okay. >> Update hashtag movies.

Set title equal to no.

It's going to do all of them. So it really ain't no is it called? Yeah.

Ain't no wear clause. Let me see. So, as you can see, all the records was there. I grabbed

them, changed them to null, put them back. All right. So, we did that. Select all the titles and make them all

uppercase. Okay. #movies. Come here. We use the word upper because

this is a string function. Boom. Right. Copy this. Made everything uppercase as you can see. Then we're

going to come down here. This one is reverse as you can see. And we did it

right. This one. Oops. We're going to do this and we're going

to say sub string title

2, 9. Right. There we go. So, we got two through

nine. get the count of the records that contain the letter G in the title. So it's select

count uh asterisk from

hashmov where t i t l e like

l i k e and it says this G. There we go.

So, we got five records that have that in there. Now, let me see. Copy this. Then we'll be able to count

them. Just be on the safe side. So, it says five. That's one 2 3 4

five. That's right. Put this in there for everybody.

This is lab number two. So what we're going to do Thursday when we come back,

we're going to go ahead and jump on these last two right here. Close this one. We got these last two.

Once we do that, we'll probably hear interview Thursday. We should be able to hear interview

Thursday. So, you guys are finally f to hear a real live interview of uh um of one of the students actually

interviewing with a company. And I'm want you guys to notice something in these interviews. And it's basically

going to be that you guys are going to understand everything they talk about. Everything you see them talking about is

what I covered. I did not teach you guys no fluff. I didn't teach you nothing that you didn't

need. Everything I taught you was what they going to ask you on the interview. I did

not waste time teaching you something that was peripheral. Maybe some of the DBA stuff, but guess what? Base

knowledge of of understanding a database environment and stuff like that ain't a bad thing. So, some of the DBA stuff I

mentioned probably, but most of the DBA stuff we going and besides, I'm going to give you guys a DBA course. So, we

really fa to get it. It's about to get busy. Now, I don't know what you guys want to learn. I wouldn't advise coming

in here trying to learn everything I put in here because I'm I'm gonna give y'all everything.

So, I don't want y'all to come in here. Y'all going to see DBA. Y'all going to see all types of stuff.

I'mma tell you what you want to focus on. These three right here. And I'mma probably drop in um Tableau.

So, I'mma add Tableau. Those are the main ones. And of course the SSIS, SSSRS, which I'mma teach you

that. I might put those over here, too. I might go and put those over here. That way you guys are really kind of in

the mix. You got everything you need. Now I'm The only difference is you going to have Net and C stuff, which

I might add like maybe three or four of those as videos that for you guys to see how to edit stuff. Um, but that's about

it. I ain't going to really go too ind depth with that. The SSIS one is going to be a doozy. So,

I don't really know. I might just stick to kind of teaching y'all what I know and then jump over here. We jump around

in data factory a little bit. So, that's that, man. We about to really step up to the next level. Um,

and I've always been I didn't want to limit you guys with what I knew. You know what I'm saying? So, me being able

to kind of walk you guys through this stuff, it's going to help me to be able to expand me as a better instructor, but

it's also going to give you guys hands-on labs,

hands-on documentation. I mean, this is real documentation, screenshots. It It's going down. It's going down. I'm

excited. So, just be looking on on the lookout for this. This probably gonna be December when I start letting y'all mess

with it because I thought about it. By the time we get through with SQL, that's when y'all gonna start messing with

this. So, right now, I'mma just keep adding and adding and adding and adding and adding. It's probably be 70 dog on

different courses in here by the time y'all get here. But, I'll tell y'all which ones to get. That's going to

really make a difference in getting a job cuz that's what we trying to do. We trying to get y'all ready. Now, if you

want to be a data engineer, you can probably just keep studying until the summer. That's what I would do cuz

that's where that's where you're going to really cross over into some stuff. So anyway,

you know, all right guys, God bless and I will see y'all Thursday. Y'all have a good one.

All right. Thanks. All right. Take it easy, sir. Y'all have a good evening. All

right.