Hello guys welcome to this course on SQL Mastery. In this course the focus is actually going to be on my SQL as you can see on the screen here but with this course you will also be very proficient and confident in handling tasks in other databases such as Microsoft SQL Server and also Oracle. So let's begin. Before we actually start the course itself I'd also like to highlight some of the key goals or objectives of this this particular course. So let's see what are our goals and objectives here. Okay so let's start you know listing our goals. The first goal that I would like to achieve or objective that I want to achieve with this course is that I want to make sure that we all understand what is data model how do we design a data model. A data model is something that is very important in design databases. You really need to understand your business model the different entities that are involved in it their relationships etc. So that's one of the earlier goals of our course. The second or the next goal that we want to achieve is be able to create a database. So database is basically a storage of information or storage of data. So with my SQL as a focus and as our hands-on tool we want to understand how to create a database. The next goal would be to also understand how to create user accounts you know for our database. So these are all the different people that will actually access the data and query the database and then also these users you know these users will have different privileges or different you know permissions on different objects. The next goal would be to create tables. So when I say tables I just using tables here but we're also going to be able to create views which actually sit on tables and views can combine data from multiple tables. So that's also what we're going to understand and learn how to do. The other thing is you know when once you create your tables you know they're pretty much empty right. So we need to start populating those tables populating those tables with our business data right. So we will be able to do that also in this this particular course we're going to create those tables and also populate them. Then once we populate the tables we have pretty much you know half the work is done I would say. The reason I say half the work is done is that you have all the business data you've created your database or designed your database and you have stored the information also. Now the question is why did we do all this thing all these things right. Why did we take all the trouble of you know designing a database and you know loading with data. It's simply because we want to answer some questions. The questions would be business related and to help your businesses take better decisions. So initially we're going to answer some simple questions. Simple questions such as what are the different products that we're selling right. What is the total sales let's say for the year of 19 sorry 2019. You know things like that. Fairly simple and straightforward questions which actually are some of the basic things that you know almost every analyst you know every data analyst in this world should know. So the next thing after answering simple questions we're going to move on to answering complex questions. So when I say complex questions what do I mean by that. Complex questions can be as complex you know complex questions can be as easy as show me the total sales for each product category right by different region. So which means that you know for each region I want to show the total sales for each product category. So I need to be group I need to be grouping the total sales by different parameters that I pass. So that's one of the simpler you know complex questions if I may put it that way right. Now there are more difficult questions which are really really difficult in the sense that it is not only difficult to write the query or the SQL syntax for that it's also difficult to process that you know these are called correlated queries where for every record that you get from one table you need to run a separate SQL on on fly you know to get certain other related information. So we'll come to that these correlated queries and other things that we were to maybe I just mentioned right. So answering more complex questions and one of the important things to remember is that you know these complex questions are important when we start talking about analytics right. It's not just about you know show me a list of products and another show me sales etc. You need to be analyzing the data you need to do a slice and dice thing you need to compare one with the other you need to compare current versus previous you need to compare you know category by category or region by region and things like that. You also want to be able to see subtletals at different levels. So a lot of those things will be you know covered in answering this in an answer complex questions section. So next we also want to implement certain checks and controls. So when I say checks and controls let's say you have you have database table where you're actually storing sales information. Now sales itself cannot be negative. So you want to be able to make sure that you know the system does not accept negative sales. There could be a business rule that you know your returns cannot be more than your sales you know for a particular customer or for a particular order ID or whatever right. So all those kinds of business logic can be you know embedded into the database itself and whenever the data is modified either by creation of data or by modification of data these checks and controls will kick in and then prevent any kind of data corruption. So just so you know these checks and controls are actually done in the form of constraints and triggers. Alright so next the other thing that I wanted to mention in terms of checks and controls is also about permissions which will come to in a few moments. So the next thing I wanted to talk about is reusable code. When I say reusable code let's say you actually want to run a certain query to process certain information and display an output and you do that on a daily basis. So if you want to do it on a daily basis why would you want to write it again and again right or why would you want to let's say you want to save that syntax or code in a notepad or text pad and copy paste and run it everyday that's also not good right. So my SQL and any other database system a modern database system provides you to create stored routines. I mean I say stored routines these are stored reusable programs such as functions and stored procedures. Once these are defined and created and saved in the database itself you can invoke them as many number of times as you want. So that's the reusable reusable code that I'm talking about. The next thing is managed permissions. So managed permissions is also a part of your checks and controls right. The only reason I kind of split them up into two different things is that managed permissions. Managed permissions is something that is more of an administration activity that by an administrator whereas checks and controls with respect to business data is something that even SQL developer can also do by creating triggers and functions and procedures and also while designing the database itself in terms of constraints. So coming to this managed permissions let's say you want to you want to have certain users access a database table in a read-only mode or this. You don't want certain users to be able to delete certain things or modify certain things and things like that. So all those things are actually handled by permissions in any database system and the next thing we will target is designing a date and time table or an entity end to end. When I say end to end what it really means that in this exercise we're going to go through the entire life cycle of designing the table you know right from logical to physical design to populate in the data okay and even modifying the table and querying that table and everything. So this is going to be a very interesting exercise for us. The reason is date and time is pretty much a standard across the industry because everybody has the same calendar right of course you can have different kinds of calendars different fiscal years you know but the essence of date and time is pretty much the same across the world. It's just that in different time zones etc etc but you'll have the same number of days you know same number of hours in a day etc etc. So yeah so this is going to be very interesting exercise for us and in this exercise I'm also going to use Excel to show you how to visualize the data and then how to reproduce you know how to create the data in MySQL. So then what else okay then we're going to do some more you know in this tutorial we want to answer some more complex questions I've just listed on some questions here you know as you can see and some of them may not really be very complex for you or even for you know anyone else also but that doesn't matter these are just indicative questions we're going to actually we're going to come to the real questions when we come to that section. So that's it you know when we do all these things right when you when you when you know how to create a data model you know design database you know create your users create objects such as tables views etc. reusable code checks and controls populating the tables answering simple questions and complex questions etc etc. So you're actually going to become you know an SQL master and that's our real goal here. So once you get familiar with all those things and once you get confident with all the hands-on exercises this is what our ultimate goal is to become an SQL master and I say SQL master you don't you are not restricted to my SQL itself which is our tool for this tutorial you will also be able to you know show proficiency in other database tools because the the gist and the concepts are pretty much same across of course there are differences in the syntax and in some of the features that are supported by different vendors but all those things will become pretty easy for you once you complete this course. So that's it you know okay guys so let's start understanding why we need SQL right. So one of the first reasons that I've highlighted here is creating a database. So let's say you have just purchased a database tool right like MS access or you downloaded the free MySQL database. Now if you want to create your own database and start storing information in it the first thing you need to do is you need to create your you know your database so that's what you will do and you'll need to write an SQL statement for that. So create database and database name right. So that's the first reason. Now the second reason or the second use case is you want to be able to define you know define your data that's going to be stored in the database. When I say define DDL actually stands for data definition language what I what is meant by defining your data is that you need to define the name names of the different structures that are going to be stored what is the structure format what are the attributes of the object what are the data types for example let's take product as an example so I want to store product information right. I want to create a table called product product and it should have let's say four columns right product ID, name, file description and create a create date column right. So these are the definitions that you have to you know write down in in the SQL specific format and then feed it into your database. So that's another reason you need DDL. So DDL also includes creating your functions you know your users and a lot of other things which we are going to explore in this in this particular course okay. So what's the next use case? So the next use case is actually modifying so when you want to modify so let's say you want to modify your data right. Modification also includes although it's not so obvious it also includes creating data or inserting data right. So you start inserting your product information you know you are selling automobiles you know paper, electronics, apparel, whatever right. All those product information you are actually entering into the system which is a part of your DML and then you can also update it right. You can also change the definitions you can change the values or you can also delete those records. So all those things come under you know DML or modification process. So DML stands for data modification language. Now the next use case is querying it right. Obviously if you don't you know like query it if you don't ask questions to your database system you're not going to get an answer and if you're if you never intended to query the database there's no point in storing it and unless it's an archive which is just kept for you know just in case you know contingency kind of thing right. So an active database is always queried right. So here DQL means data query language. So you ask questions to your database give me a list of our products, give me all the total sales for this year or give me all the sales where a certain product was not sold you know or sold the max things like that. Simple questions and complex questions. So all those things can be done in SQL. Now the control DCL as I'm mentioning here DCL means data control language. This is used to manage permissions. So let's say I want I have a certain table or a view or basically any object in the database that I've created and I want to restrict access to it to certain number of users and I want to enable access to certain number of users. So all this is done using DCL or control DCL.  So that's pretty much what the syntax looks like across different you know database tools. We're going to explore some of those also in my SQL later in this course. The next use case is triggers. Now triggers are basically programs that are tied to a table or a database table and to an event you know and basically triggered by an event. So they can be triggered before the event or after the event. I think we also touched upon this triggers in one of our earlier lectures. So for example let's say you are inserting email IDs into a table right into a table column. So and I create a trigger that validates your email ID. So because we don't want any junk garbage values in that field right. So what this trigger might do is before insert or before update you validate the input value right and check if it confirms to a specific email ID format. For example x at y.com right and there's a whole string pattern recognition thing that we need to do to ensure that you know to confirm or affirm that it's a valid email ID. As a use case as an example I'm just talking about email IDs but you could also it's enforce business rules with this and a lot of other things. So that's what you know triggers are and you can write triggers using SQL basically write triggers using SQL syntax. Okay so now here we have transactions right. So transactions by transactions what it actually means it's not about you know the money transactions that we normally do right like give and take right that's not what this is about. So here the let's say you have five different steps.  So for example if five items are sold then those five items must be removed from the inventory table. So as an example right. Now what if you update your sales table and add three three sales or sales of three products but your update to the inventory where you need to delete the inventory by three right that fails.  So it's like if you're balancing the debits and credits if you increase the credit and you must change the debit also if that doesn't happen then your whole equation goes for a toss right. So that's what this transaction control is all about. So in this what you do is you specify different steps let's say there are one two three and four steps right. So in transactions control what we can do is that we can instruct the database to complete this entire set of instructions either fully or you know absolutely none. So it cannot be you know partially done you know it cannot be partially done. It should be done in full or in none. So that's what is your transactions. So the other thing is that you know let's say you updated certain data right. Now you made a mistake and you want to roll it back you want to undo it. So roll back and commit. Commit is when you make a change and you want to persist you know those changes so you do a commit. So all these things come under transactions and those are also you know done using SQL statements or SQL contracts right. So now I think I just do something here. So here what I'm mentioning here is actually administration it's not clear because I just used that space to demonstrate the transaction thing. So administration so when you talk about administration you talk about user account management password reset in a table space management a lot of other things that you know the DBA does all of that is also done using SQL syntax. So that's it. Then the other thing that we should remember is that you know it's very important that SQL is not case sensitive. So your SQL commands or SQL syntax or SQL code is not case sensitive. It doesn't really matter if you write it in uppercase or lowcase or mix them. So they're going to be effective as they are. So let's just remember that right. So this is about your use cases of SQL and why we need SQL. So I hope you understood the purpose of having SQL it can do so many different things that you know in the beginning it might look a little bit if not intimidating you know definitely confusing because you have something like you know DML, DDL, DQL, DCL and you have triggers and this and that and all. But as and when you start getting your feet wet with hands on exercises you will be able to appreciate these concepts better. Alright so thank you. Well friends welcome back. In this tutorial or in this lecture we're going to explore what are the various different types of SQLs you know. It's basically understanding the SQL concepts and if you remember we talked about why do we need SQL at all in some of our in one of our earlier videos and we talked about how to define our data, how to modify data, how to control access to our data etc. So here we're going to take one step we're going to take it one step further and understand what are the various options of our various features available. But the execution part of it or the implementation part of it is something that we're going to reserve for upcoming videos and I'm sorry and then we reserve for next videos. So let's start what are these SQL concepts that we should be aware of. In fact before we start let's also understand that this forms the decor or the gist of this entire tutorial. If we understand these concepts well and then we implement them properly in the following videos and lectures and working sessions and all then we should be good. Alright so let's start. So one of the first things that we talked about was DDL or Data Definition Language. So what this helps us with is how do we define our data right. So DDL helps us define our data. So before we actually start using data we need to define it. So what is sales, what is your product, what is the customer right. So we have to come up with the structure, we have to come up with you know the different attributes or fields or columns, what are the data types also involved in it and all those things are handled in DDL. So how was it done? So take a look at this screen. In order to define your data you have multiple you know SQL concepts or statements that are available with MySQL and also with other database vendors. I keep repeating that because SQL has become so standard that you know if you learn one tool you're pretty much good with other tools also almost by 80 or 70 percent. So you have create you know you have create clause this helps us create a table, create a view, create stored routine such as a function or trigger or anything else right. Then you also have an alter option. So once you create your objects you may want to make certain changes to it or make some additions to it. So for example if you have a product table which has just two columns and if you want to add a third column then you come back and alter it or if you want if you have a product column and you want to make product name unique which was not done before again come back and alter it. So alter is like modifying your definition. Now renaming. So renaming is a pretty straightforward fairly straightforward syntax or you know usage of it. You just rename the object you know or do something else. Now let's say you have created certain tables and your entire business requirements has changed or you just made so many mistakes you know so many so many things have changed that now the objects are no longer usable. So what you do is you drop those objects. So there's a similar command for data modification. Let's say you want to delete a particular row in a table then you do a delete. However if you want to drop the object itself which contains that data for example a table or if you want to drop a view or a function or whatever you use drop. Now take a look at this truncate. Now truncate is a very special kind of you know data definition language syntax. What it actually does is that it removes all the data from your table. Okay. So let's say you have a table which has like 5 million rows and you need to scrap that data right. If you go through the standard way of deleting every row one by one it's going to take a lot of time and also a lot of resources okay because it will hold up your resources and your other queries will suffer because of that. So to overcome that or the you know that situation the database system gives you this command called as truncate. So when you do a truncate it deletes all the data in the table okay and it also commits it automatically which means that once you truncate it you cannot undo it. So you have to be very careful when you use a truncate statement. Now the last one in DDL is your comment clause. So what this comment clause does is actually it's like adding metadata to your data definitions. So you have let's say a product table which has a product ID for a description and things like that and you want to add certain comments to it which is not your business data right. So what does product ID hold? It's you can add a comment that you know it is a unique identifier for each product okay and product description. So this is a digital description of the product and it can be up to 100 characters long something like that. So these kind of commenting features are available. So this is pretty much what you will be working with as far as DDL is concerned most of your time okay. So let's move on now. The next one is DML. So what is DML right? If you take a look at this DML thing here it's as we have discussed earlier also it's called data modification language okay and how it helps us is that it helps us to create data okay. It helps us to modify what we created. It also helps us to delete or basically you know clean up our data. So we by cleaning up for cleaning up we just talked about truncate you know up here right up here right. That is that is something that comes under the definition language because it has different implementation and it is automatically auto commit transaction where you cannot undo it and it doesn't do it doesn't follow the standard steps that are delete statement would follow. So delete is strictly data modification language and it goes through all those you know internal steps that it has to delete the data from the table okay. So now let's see what are various constructs or clauses for data modification. So take a look at this on the screen. You have so many right. So you have insert to insert a row to a table okay. You have update to update the existing data. You can update the entire table or you can update only a select few rows but for that you have to add a where cross. So update product in a set product description is equal to something where product ID is equal to let's say one or two right. So that's how you can limit your operations. Now again delete delete also has the same kind of effect and you can delete all the rows in the table which is extremely dangerous and no one should ever do it okay. If you really want to delete all the table in the all the records in the table better go for truncate you know because one is it is super efficient and second is that you know that you know you're taking a dangerous you know action and you'll be careful with truncate. With delete you can delete one or two rows also or you can delete the entire you know table by mistake if you do not specify a filter condition okay. So now let's next is merge or absurd. So take a look at this okay. What is absurd? Absurd is basically your update and your insert. So when you issue this kind of a statement with certain data what the database does is if it already has a record it will do an update on it. If it doesn't have the trigger then it doesn't insert. So let's say you have a product ID one and you say product right product okay. So when you do that if you have this kind of thing and second is let's say you have another product okay which is let's say I don't know like what fruits something okay. So when you do an absurd when you issue that command called absurd then it will check and if you specify let's say if you specify this row okay absurd with this values it will see that you know the ID the one ID is ID one product is already there. So it will do an update okay or it will do an update here or if you if you pass variables as you know let's say three and let's say water right then it will because it's not there in the assuming that it is not there in the table it will do an insert okay. So that's your absurd. Now you have similarly we have other commands here we have do import table replace table values with CTE etc. I will go through some of this in this video and in the coming videos we want to see a demonstration an actual hands-on session on each of these command tables or each of these commands okay. So let's take a look at this what is CTE right. CTE basically means common table expression. So when do we need this CTE? So let's say you're writing a query and it's a very complex query and in order to run that query you need some extra information or you need to bring in more you know data and then query on that. So what you can do is you can use this with CTE and it actually creates a temporary table with that particular data and then queries on that okay. So that is your CTE. So when we come to the implementation you'll be able to easily forward. So let's take a look at this now you have values right. So what is values? So values is a statement that is used to pass input values to the data table rules okay.  So something like that okay. So that's your values. Now explain plan. Explain plan is a very important concept if you want to become an SQL master or you know if you want to be more than the regular SQL developer. Explain plan actually tells you how your SQL is going to be executed. The reason we need to understand explain plan is that if you have a complex query running against a very large table then you need to understand what kind of indexes are actually being used in the query and what is the query path okay. It actually helps us optimize the query for performance. So let's say your query is taking like 15 minutes to run right. For many cases which is okay this is not a bad idea this is not a bad thing actually but however in many cases 15 minutes is not a lot where the user is expecting results in let's say few seconds or at most in a couple of moments right. So when you do an explain plan it will tell you about it will talk about how the table is being used if there are any indexes and if they are being used or not and things like that. So just remember that explain plan is used for performance analysis and performance improvement. So then you'll be able to you know use it further okay. Now let's say let's come to this statement called use. So let's say you are in a database called CS database okay and you want to query or you want to switch toward different database okay. So there's inventory database and CS database. So within CS database if you want to switch you say in you you switch to inventory by using use space inventory database okay. So that's how you can switch. Similarly there are others you know there's replace what replace actually does is when you're creating a different new object normally what we do is create or replace okay. This is what is normally done. So if an object by that name already exists it will replace it and a new object is created. So that's about all these data modification languages sorry data modification language constructs. I hope you didn't get over one by this but the names that you see on you know in this table are pretty self-explanatory you know they're not intimidating at all. You have insert update delete very common absurd is basically absurd or insert then you have do import table replace is pretty straightforward no do and import table will come to come to this you know in the future videos. Then we have with common table expression that is your CTE common table expression and a couple of more. So not very intimidating and not very confusing also so we'll come to the practical sessions very soon and then you'll be able to appreciate this further. So let's move on now. So what is this DCL? So DCL is as we have discussed in our previous video is basically your data control language. How can you control access to your data? How can you avoid unwanted people from accessing your data? And how can you enable you know eligible people to access the data rights? There are two concepts one is called grant the other one is called revoke. Grant is basically your granting permissions and revoke is your taking away the permissions. So this is this is the syntax for that. I'm not sorry I'm sorry not the syntax but the construct. We're going to look at the syntax when we actually start working our sessions. So now the next thing is data query language for DQL. So we did all this stuff you know we designed our tables so we designed our data we modified we granted permissions but if we don't use it what's the point of you know doing all the stuff right? So DQL is where what helps us fetch data from the database and the most common on the universal construct is your select construct okay? So you do a select whatever information you want from whatever objects you want and you know with the specific conditions right? So after this video we'll look at some of the common SQL statements and then you'll be able to understand how it is used but just remember that you know select is not a very limited or a simple construct. It can have a lot of different variations in it you know and a lot of complications and a lot of complex queries can also be written. We're going to go through them in the next few videos. Okay so now coming to transactions okay? So transaction is also another SQL statement or SQL concept as I said in the previous video. If you want to make sure that your data updates are consistent and you know without breaking any data integrity you have to be able to manage transactions. So let's say a transaction is complete only five steps are completely done or it is absolutely incomplete if any one of them is also not done right? So either you want to say let's say you have a block of code here okay? You have a block of code here. Here you're deleting some data okay? Let's say you're deleting some data then you're inserting some data then you're updating some data then again you're you know inserting some data right? Okay and then you're updating some data. So you want to make sure that you know this entire code is done in complete or man at all. So that's where you use your transactions or you also want to check you know that I just deleted my record by mistake and I want to undo it okay? So that's where your transaction statements come into picture. So what are they? Take a look at this. Okay so you have start transaction you have commit. I'm sorry this is actually commit okay? Then you have rollback and then you have save point. You also have rollback release lock and transaction.  So let's say you update certain data you have to do a commit to make sure that it is you know it is saved permanently in the database or you do something wrong and you want to do or you want to undo it. So the way to undo it is you do a rollback. So that's why as a best practice auto commit you know I'm sorry auto commit should actually be disabled is that according to me you know because it might lead to a lot of you know unwanted results. Okay so that's your transaction and we are going to go through them you know with hands on also. All right so what else do we have right? What else can I use to help me with my shop? So that's the question. There are a lot of others you know the syntax, the SQL constructs, a lot of the commands that are available in my SQL just for this particular tutorial and this discussion I'm going to show you the three different the three other categories of statements that we can use. The first one I'm mentioning here is called utility statements. So utility statements are basically you know you'll describe and explain explain is already something that got covered here and describe and explain are actually your synonyms. So in fact all the three here that you see here explain, plan, use and help they are all actually your utility. I'm sorry I'm just doing this again because the time was not correct. Okay so this is basically your so this is basically your you know utility statements all these three. Okay so this explain, plan, use and describe and help. Now the other one that I want to mention here is called prepared statements. So a prepared statement is like a pre-compiled SQL statement. Okay so you prepare let's say you write a complex SQL and that SQL or that statement can also take parameters as inputs. So for example let's say show me the sales for all the products sold after a certain date. Now that date can be parameterized which means a user can enter the you know date value and the statement will take it as an input prepare the query and then you know send you back the data. So these prepared statements are actually stored on the server and reusable and they can also take parameters. So that's also a very useful feature because you don't want to you know prepare or write your SQLs again and again that's one thing and you also don't want the server to parse these statements again and again and you know consume resources. So you write once and use it many times but just remember that you know prepared statements are alive as for as long as your session is alive. Okay let me just try that here clearly. Okay because as long as your session is alive. Okay all right now the last one in this discussion is your compound statements. So compound statement as the name itself suggests contains multiple statements. Each statement is complete in itself and then terminated by a semicolon. But you want a whole bunch of code you know to be executed right now. Compound statements can also be nested. So you can have one large compound statement within that you can have multiple different compound statements okay like a group of statements and in each component statement you can also declare your local variables. So think of this you know a component statement is basically like a small program snippet which contains multiple you know statements or multiple commands or whatever right and it also has its own local variables. So that's it. You will come to see that you know compound statements are heavily used in stored routines where you want to create reusable code right. So in one of my earlier discussion in this course I had also talked about reusable code. So compound statements and even the prepared statements both of them can be used as reusable code in many occasions. Okay so this was all about SQL concepts just to recap you know you have your DDL that is basically your data definition language to create and define and order and basically manage your data definitions. Then you have data modification language. It is basically to manage your actual business data then you have data control language which is basically to manage your permissions okay or control who can access who cannot access okay.  Transactions is basically to ensure that your data is I know modified or updated or deleted or whatever consistently according to your business rules. So you cannot have half baked or half done activities and leave your system in a status of confusion right. So for example if I am removing if I am withdrawing money from my account then both my account you know data's need to be updated and your ledger or whichever your master book right that also has to be updated according to accordingly. So if I just delete my if I remove the you know if I basically reduce the account balance on my account but your total balance in your ledger or whichever write your big book is not adjusted then there's a state of imbalance of yeah imbalance actually yeah. So that's for transactions and then the last one we talked about is what else and we have utility statements, we have prepared statements and we have a compound statements. The last two prepared in compound or for reuse of code all right. Okay so when we come to the practical exercises you'll you know appreciate this better and I hope that with this tutorial combined with the with this course and with the last course on why we need SQL you would have gained better understanding with little more you know details on SQL concepts. Thank you for watching. Hey everyone. This lecture I wanted to cover some of the basic things or basic concepts of you know getting data from different databases. The reason for having this introduction to a select introduction to select is that most of us in the beginning do a lot of you know database queries. We actually try to get read data from the database from single tables single view multiple tables. We want to you know answer a lot of business questions such as what's the sales what's the sales by you know different products and things like that. So I just want to make sure that in everybody and anybody and everybody who's taking this course gets gets to see what kind of questions can be answered and these are all basic questions actually business questions so that it gives gives us an idea of what to expect going forward and in the future we're going to have some advanced concepts also and complex joins complex queries and all but this particular discussion or this particular session is just to pick that interest you know just to make sure that you you get a just you get an idea of what is coming up and how you can benefit and just to show you some value of this course okay. So introduction to SQL or introduction to select statements. So how do you fetch data from databases okay and how do you combine data from multiple tables and multiple sources now this is one of the this is by far the you know biggest question that all of us have especially if you're endless or if you're just getting started with databases and all. So what does it really look like right so let's take a look at it okay now let's assume that this is one of our tables called sales table which has four columns in it sales ID product ID amount and channel ID and it has got 12 I think about yeah 12 rows so this is a table that has rows and columns now there's another table called product okay the product table has two columns one is product ID and the other one is called product name so if you take a look at the first table that that also has a column called product ID now in the database world this is actually a foreign key so let me just yeah this is actually a foreign key in the sales table f o r e i g n okay this is a foreign key and that references the primary key in product table as the arrow goes okay here as I just did it so so now what are the different questions that we can ask and we can answer right now I just showed another small data sheet here what I have done is actually I have calculated the total sales for each product ID from the sales table okay so the sales table if I do a you know select of product ID and sum of sales sum of amount you know as per the syntax I should be getting this output so now let's start asking questions okay or let's start looking at different simple SQL statements that can be run so the first statement is how do I get all rows and all columns from a table basically I want to fetch the entire data set right in the table so I just do a select star from products here products is the table select is a keyword star is a wildcard character from is another keyword and product is the name of the object in our case it is product so I just do a select star from products and it will give me this output as you can see here on the left side so select star from product gives me all the rows and all the columns in the product table so that's how we get all the data now the next thing is I want to instead of doing a select of star or basically you know basically selecting all the columns I want to specify what columns I want so in this query the second query as you can see here this is the first one and this is the second one I'm specifying the column names okay sales id and amount okay sales id and amount from sales now I've just missed typing a here so with the space I should be typing a and it's actually an alias you could have a b c d you just write whatever name you want that's basically an alias for this particular table so the the query says that you know give me these two columns from sales table and what are those two those two columns those are sales id and amount it's when I run that I get this data so if you take a look at this the sales table has multiple rows and columns and I'm only picking up these two columns and all the rows all the rows okay so that's your second SQL statement now let's come to the third SQL statement where I'm actually selecting few rows but all columns okay here I'm not limiting the number of columns in the third query I'm limiting the number of rows now how can I do it I can do it by applying a where condition so again select star from products give me all the columns from products however the the the condition is that product name should be bananas so the SQL says or the select statement says select star from products where product name equals bananas product name is the column on which I want to apply the filter on and equal to bananas so bananas is the values that I'm actually searching for right so when I do that it just gives me one row because in the entire product table there's only one row that has bananas in it so that's the row I get so that's how you apply a where condition on a column okay now the next you know select statement take a look at this I'm actually combining data from multiple tables now what I'm doing here is I am combining data from sales and product table so my query is select b.product name comma a.amount okay now what are these a and b is right as I said a is an alias that I used previously I'm using it again here and b is another alias so every table or every or every view in the select statement can be given an alias name or you don't have to give it if you're just using one table or if you're not using similarly named you know objects so it's better to give because it's a good practice so here I'm giving an alias to sales as a and an alias to product table as b so I'm selecting select b.product name comma a.amount from sales a and products b okay I'm combining the data and I'm also applying a where condition the reason I'm applying this where condition is when I'm combining data from multiple tables we need to identify the common columns and give a condition otherwise what happens is it will pick up all the possible combinations from both the tables okay which is wrong right because sales of let's say bananas can't be mixed with sales of apples so if I don't specify this particular condition where a.productid equals b.productid it will just do a m into n you know kind of matrix and it'll give me the entire set of combinations and that the total number of combinations if you do a simple math there are 12 rows and sales and six rows in products so what are the combinations right 12 into 6 is actually 72 okay so it'll give me 72 rows and each of the sales will be repeated for each of the products which is actually wrong right which is very very wrong because sale id one is against product id one so it should actually be reflected against product id one and product names apples only and nothing else that's what this that's what this joint condition does okay so when you do that and of course I'm also doing an order by order by product name order by is basically your sorting by default it is ascending you can also change it to descending so let's see what the output looks like so as you can see here each product id is now being replaced by your product name and sales as usual okay and one two three four five six seven eight okay so I've just copied so if you take a look at this now on the sales table there are 12 records and in the product table there are six records okay and when I combine this product id equals product id I mean when I come by sales and product table I got fewer rows I got less number of rows than the actual number of rows in sales table and what is the reason the reason is because I have given this joint condition and it find it found certain records in the sales table with product id seven and eight which are actually not present in the product table so these these rows that I'm marking now in red actually fail the joint conditions and that's why they are excluded so and that's why they are excluded you know they did not show up in the list so just to remember you know you when you combine data from multiple tables you should specify joint condition all right so the next query now okay now again I'm going to select product id and I'm going to do a sum of amount now now if your table has multiple rows and you want to aggregate them you want to combine data from multiple rows you actually use what what is called as a called as an aggregating function or aggregate function or summarizing data summarizing function right summary functions so this could be your sum min max average right any of the statistical functions on multiple values sets of data right so when I do this I do a select a dot product id comma a dot sum of a dot amount from sales a again a is the alias whenever I use an aggregate function I should also use a group by function okay that's how the SQL engine knows that when the aggregation is happening what the columns that you want to group by okay and here I'm actually grouping by product id because I want to combine all the sales for each product id so if you take a look at the sales table product id one has 100 it also has 101 and what I want to be able to do is I want to be able to combine these two rows into a single row with product id one and total amount is 101 because I'm summing right and this is the SQL so you do a sum of amount from sales and then I do a group by the columns the remaining columns the non-aggregate columns okay so when I do that this is the output I'm getting and which is correct also so for each product it is doing a sum of its sales and it's displaying by doing a group by so this is another kind of SQL and very very common scenario these are all very common scenarios okay now what's the next one that we can think of we want to look at right now if you remember we filtered for bananas in this in the third query here okay we did a sales start from products and we said where product name is equal to bananas now similarly when you do an aggregate you know query when you do when you aggregate data and you want to filter based on certain minimum value or maximum value right or equal to when you do that when you want to do that you do not do a where okay you do not use a where clause you actually use a having clause as I'm highlighting here so select a dot product id comma sum of amount from sales having having is the condition it's similar to where but it is applicable only to aggregate functions having sum of amount greater than 500 so this is the filter on aggregate values so this is a complete way so when you do that I'm sorry the group by is still it's bad okay so with this group I also so because we I'm doing an aggregation I'm doing I'm using a group by and because I want to do a filter on the sum value or the aggregate value I'm using a having clause okay so when I do that this is what I get so all the sales all the sum of sales where the sum of sales is greater than 500 is listed here and if you take a look at the previous query output 201 and 41 for product id 1 and 2 are not listed because their sum of sales or sum of amount is less than 500 okay and not even equal to 500 actually okay so the last one is let's take a look at this I'm selecting product id I'm selecting the sum of amount from sales and product and I'm also doing a join on the product id column and I'm also specifying a where condition okay now this where condition here the first line is nothing but your join condition okay it's not really a where condition although it is actually it's not really a filter condition but that's what it is doing but it is ideally known as a join condition and the second line is your filter condition okay where I'm saying product name like apple okay I'm only looking for apples now but I'm also looking for apples that sold more than 500 okay I'm looking for you know product id's with names like apple now like is is a pattern matching you know construct so when you say like you can give a pattern and the values are matched if they you know match this pattern that you mentioned then the results are included so where product name like percent apple percent percent is another wild card so basically what I'm saying is the product name contains apple within it it could be at the beginning it could be at the end of it or it could be in the middle of it anywhere so as long as the name contains apple in it I want the results right so if you take a look at the data set product data set the only product id that really has apples in it is product id 1 okay product id 1 but we know that product id 1 for product id 1 the total amount is only 201 right only 201 now having some amount greater than 500 is another condition that's here now obviously 201 is not greater than 500 so this sql should give us no results okay this sql should give us no results and that's what it does if you you know when you actually run the query in the against the database so this was a very quick and very brief introduction with simple sqls that actually tell us how you know we can get data from the database tables using different different methods okay you can get data from the same table you can combine data from multiple tables you can filter on the row values you can filter on the aggregate values you can specify a pattern to look for when you filter you don't have to give the exact values right you can fetch all the information in the table or you can fetch just part of it you can you know fetch limited number of columns etc now combining data from multiple tables is not as easy as you you know see in this particular example as i told you this was just an introduction we have a separate discussion on joints where we talk about how data is combined from multiple tables and things can actually get very complicated also but nothing to be intimidated you know nothing to be worried about because when you go step by step and you understand the concepts it doesn't matter whether you have like five tables to you know combine or you have 15 tables you will be able to do it when you break it down into smaller pieces you know small problems so with that i'd be ending this lecture i hope you got some good idea of what sql is what select is and why do we need to learn sql what can we do with that when we have access to a database and we know how to know fetch data what kind of reports we can run etc etc all right so that's it thank you thanks for watching welcome let's start this course i'm going to start this course with an introduction to the database the concepts and i'm going to cover this from the perspective of a manager or a layman by that what i mean is we will not be specific we'll not talk about any specific you know syntax or code or any kind of specific tool but in general let's understand what is the concept concept behind a database and you know how they are designed etc etc so so what is the database and what are the basic features of a dbms system dbms stands for database management system so let's check out so if you see on the screen you know i've kind of defined it as a database system is a software system that organizes and manages data when i say organize and manage it captures data it stores it it defines it you know it also provides his tools to retrieve data and also to you know add data modify data etc especially focused on business data and that is what the end users would do either automatically in terms of automated scripts or through interaction with the users as in a user would type in certain questions or queries in the specific format or specified format and you will get the results so that's pretty much it you know a database system it actually it's a software system that holds your data and lets you manage it and also query it now database system as a terminology it refers to both the systems as well as the technology adopted to fulfill all these business needs now there are multiple different stages of designing a database all right you know in order to design a database we have to go step by step through a sequence of different stages or lifecycle stages until you reach a mature and reliable output or target state let's let's let's also think about the file systems that we have been using traditionally right your laptop your computer they all have a file system where you could you know just save your files in terms of text files or image files or work documents and so on so forth right so a file system can also be used to store data however there's a huge difference between a file system and a database system the database system is specifically designed to store huge amounts of data and then query it right whereas the file system is pretty much flat and it is it is not as efficient and as optimized as a database system is and there are a whole lot of benefits of using a database system so what are those benefits or what are some of these features of a database system is something that let's you know we're going to look at right now i've just highlighted if you look at the screen i've just highlighted some of the benefits here first the first benefit that you see is called no redundancy when i see no redundancy what it means is database system is carefully designed to ensure minimum storage requirements you know so let's see have a database field or a business field called name you know a customer name or a product name now that customer name and product name could be used at multiple different places in the system but that doesn't mean that you have to you have to store the the complete username you know at all the places right you could create a specific storage for those names assign an id to it and then use that id in all the other places where you need to reference or refer to that other product name now that id would take less storage less storage space than you know the whole product name which could be like hundreds of characters in length so that's one of the things the second thing is abstraction so abstraction of details what this actually means is that the end user who is actually going to be using your system he doesn't really need to bother about the integrities of how the data is stored in your database he doesn't care about how we are partitioning the data how we are indexing it as an end user all he cares about is if he has the right questions with the in the specified format he should be getting the right results so that's you know abstraction of details from the end users now the third benefit is suitable for complex systems you know with the database system well designed you can really implement it for extremely large and complex business corporations and then huge huge amounts of data can be stored and you can fetch the results also in a very finite amount of time without really incurring huge cpu costs so that's one of the you know significant benefits of a database system in fact a database system is also used for better manageability of your data let's say you want to cut out certain portions of your data chunks it's easier to do it in a database system rather than doing it in a file-based system the next one is a query in simple and understandable language of syntax right so when I say simple and understandable language it's pretty much like your natural language of course there are rules on what you can type in or what you can feed or what you cannot feed in and all it sounds like let's say select product name from product table right so this is English pretty much for all of us and that's how the SQL or the structured query language is designed now you don't have to write complex and cryptic code to get the data whereas if it was a file system and you have to extract let's say only those rules that match certain in a pattern in a name you'll have to write a whole lot of cryptic code to get the data with database system you have to write a very simple SQL query which sounds like English and then it's also very easy to remember and also write back however with all these benefits there's also a price to pay which is the you know the experience of creating designing and maintaining a database system it's significantly higher than a file-based system but of course you know the benefits outweigh the costs of it so large corporations and even small corporations or corporations also prefer a database system the last one that i wanted to highlight here is the access control a file-based system also has a certain level of access control where for example a file can be designed for read only or read and write both however with the database system you can also implement a finer or granular level of access control so for example if i am storing customer information i can restrict certain users from you know updating or modifying certain rows only in that table so if i have 10 rows in the table i can make sure that a user is able to update only a certain number of rows but not all the other rows so that kind of granularity in terms of security is also possible in database systems so that's you know pretty much an introduction to a database system and what are the benefits and this makes more sense to a business user whether you are a business head in a large corporation or running your own business you know small business whatever right how you store your data and how you manage it and how you use it for your day-to-day operations and also for your strategic decisions matters a lot and this course is also going to you know expose you to all those possibilities of you know picking up and you know going ahead with the database system now here at the bottom i have given certain you know examples of database systems that are available in the market and that is pretty popular okay the first one of course that i have highlighted here is my sql which is what we are going to use in this tutorial as our hands-on tool and just so you know my sql is also absolutely free this is absolutely free there's no cost in all in it the second one i have given up given here is ms sql server so this is basically by microsoft you know corporation and they have sql server database that is also very popular in the industry then of course you have oracle oracle is a giant and they have you know a lot of different products in the market but oracle is preferably is generally chosen by large corporations with huge amounts of data then we also have postgres which is another you know great database system then microsoft also ships ms access with its ms office you know package right now this is used by a lot of different people in across the industry including people in huge large corporations where they have a bigger databases you know such as oracle or even db2 and all a lot of business analysts and business users have their own copy of ms access you know they they actually create their own data they they create the data from excel sheets and you know even manual entry or importing from files from other extracts and all but they maintain and they actually maintain a copy of a database a subset of the universe right in their on their desktop or on their machines and do their own reporting and analysis and all so that's why ms or microsoft access is very very popular among business analysts now the last one i wanted to mention is ibm db2 so ibm db2 is also another popular database given by ibm so this was the basic introduction to what a database system is and what are the different products available in the market and if you're an individual user or individual buyer a small business owner then what are your choices for you know picking a database so obviously the number one choice that i'm going to highlight is microsoft sorry my sql um hold on let me just um yeah so my sql is one thing that i'm going to highlight for you uh if you're an individual uh and the second one that i'm going to highlight is also ms access microsoft access um that


Good morning. It's a little bit after 5 a.m. We're looking at single digits this morning. We got eight degrees lightly snowing out there been snowing for Little bit over an hour. I think I've been here in a hit in the roof. It's nothing too hard Still got a little bit of a breeze blowing Today is pack up camp day. I Got a feeling there's gonna be a big Big snowstorm coming over the next 24 hours or so so there's no sense getting stuck in here And I get some stuff I got to do downstate been an awesome trip so far I mean I caught a musky and then I ended up catching I think 11 musky topside Broke off a couple saw a couple more and learned quite a bit about him You know, it's it's interesting really really unique fish. That's pretty amazing We're lucky to have him in the state of Maine. Thank you, Canada got the coffee perk And she's just about ready to roll gonna have us a lot smaller breakfast this morning had a monster of a breakfast last night for dinner Kind of ease my way into the day here still got another hour hour and a half where the Sun comes up So I think I'll slowly start packing up bedding and Clothing bag and the kitchen stuff over here get all that pack So when the Sun comes up, I could start loading up the sled for the trip on out of here Gonna have some porridge for breakfast this morning. Just a little bowl forage Kind of coat the stomach just in case I have a long day ahead of me Never know how how that day's gonna wind up when you're traveling pretty good snow line Yeah, she's knowing pretty good now fellas That's a pretty good size flakes nothing serious yet. I Got almost all my stuff out of here loaded up For the most part I left some room for the floor The tent on top This is what she looks like pretty much empty That was under my bed all week, so it shows you stays pretty cold in here nice ice layer there and Isla layer on the floor Yeah, a couple more things to do and then we are out of here I'm gonna see if I could drive this Pull load right on and go out the front door and unhook because that tote sled is it's freighted It's heavy.  I'd have to unload it So save some time. Let's see if I could just drive her on be kind of cool If I can get her up in here, I know I can rearrange it a little bit. That should fit pretty easy All right, you fit like a glove The Jim Carrey kind not the OJ Simpson kind she fit good Everything's in there. We're loaded up ready to go or we're sitting on about 98 hours without seeing another human or hearing another human or any thought of any Existence at all from anyone else. We could have a pandemic out there for all I know and I wouldn't even know it So I'm gonna hit the road. I'm on the login roads now It's no one pretty good. It's not too bad We got like an inch of fluff inch and a half of fluff built up and let's see. Hopefully the ride home goes smooth Let's go I have no idea what I'm listening to I'm closer to Canada than I am to any town in the U.S. But it sounds pretty funny. They're actually pretty good. Yeah, they're pretty good. Yeah, you can get a good sleaze off that That's what I caught them on.  Well, I only caught one so No, I got I got 11 Did you Chuck Norris them right? Yeah, exactly Sweet so I'll eat that for sure. This is my buddy Jeff. How's it going? Jeff's the one who told me that this is his favorite fish. Yeah, that's one of them for sure. Yeah, like a good white clean meat How are you gonna cook it? Probably gonna chowder. Nice. Yeah, let me know what you find in there. Yeah, I pulled a smelt out of his going Oh, really? I'm surprised. Is anything? Well, I've got one of those.  Let's take a look How's it look? Everybody's got a wall she That didn't last long did it? No, it didn't last long. So there's supposed to be a piece here And this piece is supposed to connect there And it don't no more no I broke it and then This is where the clutch is right here, right? Yeah, yeah, so there was snow going up into the clutch and they say that bad. Yeah, that'll cause a belt to slip Snow going into the belt too, but I stopped on my way home. I haven't even made a home yet And josh and the guys here chase fixed me up. I don't even know if it's been a half hour Maybe we got you on the fly today. You might be missing a little acoustic foam, but that doesn't get a matter much So minor that'll get you back fishing as long as I'm back fishing. That's all that counts But I appreciate it guys. I appreciate you guys. Thank you. Get me right in. This is customer service Had its best right here these guys fixed me right up got me right back out so I can get back out there and Just really really good customer service here as always. Thank you. Thank you So is this going your wall or my wall? This is going to go on your wall. Okay Hopefully that's the only piece I hang out this year. This is the best part of this here. There's a little heart here Oh, do I need to break it? I was gonna say you got a broken heart now. Yeah, I'm gonna have to break it What is that all about we're assuming there's a girl at the on the printer label just made that just for Oh man, was that done in Canada you think? Yes it was. Yeah, I love Canadians Absolutely love them. Awesome. Awesome. Thanks guys


Aloha, welcome to Figments, The Power of Imagination. I'm Dan Leif, I go by Fig, thus the name. And I'm glad you're joining us today because I've got a really special guest. And I think you'll find a very intriguing conversation for you. Remember, if you're watching this and have the opportunity to click like, please do. Even if you don't like it, not for the algorithms that drive YouTube and other media, but it makes me feel better. That's a good thing, right? So my goal on Figments, The Power of Imagination is to entertain and inspire. I think this episode will inspire you to maybe think a little more deeply about leadership, not necessarily from a personal sense, but from an organizational sense. And my guest today is an amazing leader who I have known since birth. My daughter, Yateng, Ola, Yateng. Ola and Aloha. Yeah, how are things in Virginia? They're getting chilly. I'm jealous of your weather, as always. Yeah, it's a little toasty, but we'll tolerate that here in Hawaii. I should point out that Yateng is in a leadership role at Google and what she's going to say today does not reflect any official Google policy or anything else like that. So she's appearing as my daughter and one of the best leaders I've ever known. And I've known that you are a leader almost since birth. And that's not much of an overstatement. Once you came home from the hospital, we finally had a mature person in the room and got a few pictures of our life together. I think I skipped over our title slide, but that's okay, you don't need that. So the one on the left, you're already giving me guidance. And then in the middle, I commissioned you as a second lieutenant in the U.S. Air Force at UC Berkeley. And that was noteworthy because I stumbled on your commissioning. Oh, do you remember that? I do, because when I pulled that photo, there was a caption from my friend that said, her dad said he was more nervous than she was. Well, I was more nervous than you were, but as I stumbled in one part of it, instead of repeating the misordered and I don't remember what I got wrong, you corrected me quietly and I got it right and you gave the oath correctly. And that was leadership. And then the third picture was you and I at the end of the Honolulu marathon. And to lead your old man to participate in a marathon, we'll call it running. That's extraordinary leadership. I mean, I don't know how you did that, but I think you've always been a leader.  No, I suppose I took some cues from you. I'll give you some credit there. Who? But I've always enjoyed observing other people. And I suppose that in those observations, that's led me to look at how other people are influencing to drive an agenda forward or to make something happen. And I would say the Honolulu marathon was my psychological operations with you at its finest. Absolutely, yeah. And I wish we had time for the entire story, but it worked and I'm glad I did it once. I'm good, thanks, Dunn. I remember a couple other times where I saw your leadership and seriously, I often tell people you're the best leader I know.  The one when you were 10 or 11 and we shared this story on the phone the other day, you were in gymnastics in Korea and the coach did something that was just a bad way of leading young girls who were learning gymnastics. And you left the team and you didn't leave in a pouch or anything like that, but my sense as your old man was that, you didn't meet your standard, so you're out and you were out. You don't remember that though, do you? I don't, but it's interesting that you brought that to the forefront because when you asked me to do this segment with you, I was thinking about my own leadership journey and I think that my real interest in leadership actually started around that time and I didn't associate it with this gymnastics incident. I actually associated it more with picking up, I think it was 250 North Korean propaganda pamphlets around Busan and I was captivated by the fact that North Korea was pushing out these paper cartoons and trying to push a particular agenda. And it was at that point that you started showing me and teaching me a bit more about Kim Il-sung, Kim Jong-il and how they shaped an entire nation. And it seemed wild to me that one family could do that.  Absolutely not, but it gave me pause. It gave you pause, it made you reflect. It did, it was very powerful. I saw you lead with your friends quietly, you weren't a big rah rah leader and within the family through good times and not so good times. And then you went to the University of California, Berkeley, Go Bears and joined ROTC. That's another story that we won't share. And became the top cadets, the cadet wing commander. And that was probably your first formal designation as a leader. Definitely, yes. And I would say on my UC Berkeley time, that was my unrebellious rebellion against your efforts to send me to the Air Force Academy. But it was an interesting economy and laboratory to learn leadership. And an environment that had such a reputation for being anti-establishment. And I would say I have probably a very similar experience in ROTC at the University of Wisconsin. Similar environments, but you can't just lead by dictate or eat it, whatever the correct best word is. So, but this really isn't about being your leader, being a leader and how you lead and how you help others lead in a sort of a sense of leadership skills and principles. When we talked, I became more aware of your work because when we're together, we're doing family stuff, especially with your two beautiful kiddos. It's about the fact that you're really now, Google and word Deloitte, a leadership enabler working in the organizational environment that allows people to be the best leader they can. Did I describe that right? I think that's a pretty accurate description. I would say even broader and in addition to that, it's about helping organizations get their people to execute and elevate their strategy, whatever that strategy may be. Did this come to you in a dream, this part, this organizational structure, is it part of your Air Force experience or maybe while you were studying at UCLA Anderson School of Business? But how did this happen? How do you wind up doing this? It goes back to 2011, there I was in the, I mean the very air conditioned chaos of value deed. I was in a role where I was supporting the transition of assets from Iraq to Afghanistan. And in addition to that, I was watching and supporting a lot of quick reaction capability go into Afghanistan. And I saw some really cool technology being rolled out into the battle space. I felt like I had a decent understanding of the strategy. But where I saw a gap was in bringing people along on that journey, both on the ground and at different layers of leadership. Because even though the technology was really cool, people were grappling with, how do I even use this fancy new thing you just threw at me? And it was there that I realized you can have the best strategy, you can have the best technology, but if people aren't along for the journey and they don't know how to use the things or make the technology work for the strategy, then you're not going to accomplish your goals. You've wasted a lot of money, time, et cetera, et cetera.  And shortly after that deployment, I was dragged, if you will, to do a tour of Zappos, which is headquartered in Las Vegas by my friend who is visiting me and getting her MBA. And at Zappos, they had a few people. The shoe people, they had a very intentional culture where it was all about delivering happiness to the customer through their people. And they created systems that were so intentional that were aligned to this objective, like everybody, no matter what level you're at, sits in the call center for the first 30 days and interacts with customers. And that is how you learn the organization. Then at the end of that onboarding, you get paid a few thousand dollars to leave, so that if you don't want to be there, if you don't want to work in that type of environment, you get to opt out early on. And they designed a series of other reinforcing mechanisms to tell their story, to create this culture. And it was compelling to me because it anchored in the concept of happiness for employees and customers. And that's not something that I had been conditioned to anchor to as an objective. You're not talking about me and home, I'm sure, but you're talking about work. Yes. Yeah, that's interesting. And when I think back to my time as a commander in the Air Force at the squad, even at the flight level, I tried to create that culture, but it's pretty easy to do that in a military environment where you're the boss, you're the commander, and you can say, this is how we are, this is what we're doing. It isn't as draconian as it sounds, you have to encourage and nudge and all that, but you have a lot of leeway. But now you're trying to do that in business. You did that at Deloitte, you worked at Deloitte for five years, and I know I had a great experience there. Can you describe what you did there and then maybe compare and contrast it to what you're doing to take that enablement to business, please? Sure. So my first few years at Deloitte, I focused on supporting our clients for going through mergers acquisitions and divestitures from the standpoint of how do they guide their people through that major transformation journey so that they buy into the strategy and so that the organization is thinking about how systems need to change with the change in the business structure. So that looks like helping create talking points in the narrative for the leadership team, helping managers to tell a story and then guiding our clients through how do they think about the shape of the new organization in terms of organization design, in terms of performance management, et cetera. What are the things about the legacy organizations to bring forward to the new combined organization versus create net? Yeah, change is hard and that structure of the change is really important. You want to disrupt as little as possible and yet change, I think. Right, and it's important to keep in mind that humans are naturally pre-wired to resist change for good reason and a lot of my leadership philosophy and how I approach guiding clients through these types of changes is fully accepting that and trying to meet them where they are and acknowledge that skepticism that might be inherent with a major transformation or the fear of having to go through these steps that feel very unfamiliar. People don't like doing things in new ways when what has gotten them to the point of success before has just worked. There's a tendency to want to rinse and repeat and hope for the best that it takes to you carries you into the future, but in today's world, it doesn't. And then now at Google, are you doing the same thing, different, slightly different, really different? It's a spin, so one important piece that I missed from my Deloitte times is that I was part of a major Deloitte culture transformation project where we revamped how we incentivized our top 6,000 leaders of the firm. It was a very ambitious program, but it was in the service of shaping a culture of development and courage so that we could continue to be relevant and excellent players in our market and deliver excellent client service. And so that work I would see as the launching pad to my work at Google. So I am on a transformation team within Google Cloud and we are helping guide the organization through shifting the way people think, behave, and the systems are designed so that we can serve enterprise customers more effectively versus prior to our career. I'm gonna interrupt to say that I'm struck by the use of your word courage. That's something that you usually hear in or think about in the business world. I guess you're trying to give people the courage to not just accept the change in a passive sense, but embark on being participants in the change. Being leaders of the change. Leaders of the change. And also having the courage to give each other feedback that is meaningful and not simply superficial. Blah, blah, blah. That you did it. How do you actually help others guide others to perform in ways that are more effective? So one of the, you mentioned a performance assessment in the things you did at Deloitte. But one of my career long frustrations was the limitations of the assessment system, the rating and feedback process. And the fact that so few leaders had the courage to give real meaningful feedback, both directly to the person they were rating and to the system that was going to select whom to promote. Doesn't mean that horrible people were promoted. I think by and large the system was effective, but people do better with honesty, I think. They do and they appreciate it where I think people struggle with honesty is when it feels candid just for the sake of sticking it to them. And I think this is where it's important to coach leaders and I'm getting ahead of one of your questions, what can their force learn from the business world? One of the things that has come from Google that I think is quite powerful is the research around team effectiveness where the number one predictor of an effective team is psychological safety. And so if you don't create this environment where people are comfortable speaking the truth and feeling included and being able to challenge the status quo, then if you come out of left field with something that might be very honest feedback, you might be perceived as a jerk. But if you do that in the context of our norm, our ritual is to give each other feedback on an ongoing basis, people are in a better headspace to accept it. They're able to kind of counterbalance what might be an amygdala hijack of their brain saying warning, warning, I'm about to get something I don't like to, okay, you know what, I'm expecting that we have these types of conversations in this organization. Okay, because this is so intriguing to me, I'm going to quickly give your book recommendations to our viewers, and I'm sure we'll want to write this down. So here are books that Yateng recommends. Yateng, you don't have a chance to talk about them because we're gonna use up all the time talking. And let's go quickly through some of the other principles I asked you for, what are your leadership principles? But briefly, because I want to come back to this conversation on courage and safety. And compared to our lives, you served as a fighter squadron intel officer, and I served in fighter squadrons throughout my career, which is an interesting but safe environment. So regarding your philosophy, what principles are important to you here? Talk about this slide for a minute, if you would. So I will cut to the great leadership points. I put on here that great leadership is guiding people to get something done by enabling other humans to thrive. And this human's thriving piece is particularly important to me personally because it ties to one of the longest Harvard longitudinal studies where they tried to, I understand what predicts health and happiness in human beings and they followed people for 80 years or whatever it was and found that close relationships were the number one factor. So when I think about being a good leader and doing something that's meaningful, you are hopefully creating connections with your team, creating meaningful relationships with your team to contribute to their long-term happiness factor and then doing something, having a mission that enables other humans to thrive. And this is one of the reasons I love working at Google because our mission statement is so clear around helping others get information in ways that are accessible and our leadership constantly talks about being helpful. Helpful is probably the most commonly used term I hear within Google because it's what we aspire to do and how we aspire to treat each other. How do you balance all that with personal ambition because people do have ego and they are ambitious? I would say that that's why the underlying belief to be successful is that if you want to achieve your ambition, it should be through the help and by lifting each other up. So doing it with other people gets you farther than attempting to do it yourself. Yeah, I was being the devil's advocate there because I could relate at a certain time in my career where I became more concerned about the success of folks I led than my own success. Doesn't mean I was unconcerned about being successful maybe getting promoted, maybe getting the next job but the real reward and the real ambition shifted to those who enabled my success and I figured if they were successful, I'd be successful and it sounds silly but it felt better. Hopefully most people find that it feels better if they wear that mindset but I think it ties to one of my other leadership principles that I know you'll pop up here around being clear on what your end goal is. What is your ambition for? Hopefully it's not just for self advancement though that may be the case. We've known people like that. Yes, definitely. I think if you ask those people to peel the layers of their own onion back they could probably find something or they are searching for something that's more compelling just than the sake of playing their own game. Yeah, those who play their own game or are self-serving long thought don't have much courage and now that you've introduced that in the leadership conversation it does take courage to rely on others to enable others and let them bring organizational success and not look to take personal credit for it. So I gotta take a quick break, feel a quick plug. Next week, Monday, 10 a.m. of Hawaii Standard Time. Those of you who suffer under daylight savings time please figure it out. We don't here in Hawaii. I'm gonna talk about China, Taiwan and the U.S. what you should know. I think this is a very important issue in Taiwan and Tang and I both have interest in this for not so but maybe obvious reasons. So that will be my next non-political discussion of current events. Let's get back to leadership. And Tang, I'd like to go back to your three areas. Humble curiosity, I'm curious about that. How does humble curiosity fit into business leadership? Some leaders have the preconception that to be a great leader you have to know everything and you have to be able to show up with all the answers. What I've learned over time and what I've observed from the best leaders I've worked with and observed in the leaders where I've seen pitfalls is this ability to ask great questions so that you can guide people to a better answer. And I like the framing of humble curiosity which is a little bit of a play on Adam Grant's phrase from Think Again of Confident Humility to give humility a little bit of a moment to shine because many people misinterpret humility as being self-deprecating but the origin of the word is more of from the earth like of the earth being able to recognize that you don't actually have all the answers. And if you pair that word humility or humble with curiosity that leads you to go into conversations not assuming that you already have the answer and guiding you to ask for input that you need or for getting people to talk so that you might uncover risks that you might not have seen and get to a better solution. Yeah, a good leader doesn't have to know everything shouldn't because their brain would be full and they'd be confused. They don't have to do everything or be involved in the doing of everything but what a good leader has to do is make some key decisions at the right point and then those decisions are left only to him or her. That's kind of how I oversimplified but there's no, I don't think a leader should feel uncomfortable about not knowing everything but I can't. I was not an engine mechanic when I was no 16 pod I didn't have to beat the engine mechanics who worked in my wing. And then I was curious about what they did learned enough to make good decisions. I think it seemed to work out okay. And agility, I mentioned this in our warmup here and this was from a conversation that Alejandra and I had another key part of my show prep by the way for pigments, the power of imagination is do you see differences in the agility of different demographics? Are old people less agile? I can say that because I'm old or our organizations what do you do in an organization to give this agility to deal with change and to become better? The way I think about our agility doesn't quite lend itself to that level of stereotyping. Well, that might be true in some dimensions of adoption of technology. How I'm thinking about our principle standpoint is having the agility to dance across being a player on the field with your team and being the captain of the team leading while you're on the field and being a coach, being on the sidelines and letting the team shine and letting somebody else serve as a captain. So as a leader, you need to be able to figure out in these moments, when do you need to roll your sleeves up and create slides with your team or are you scrubbing the toilets with your team? When do you need to be the team captain rallying and giving the rah-rah? And when do you need to be the coach? And I don't see a significant enough difference across generations to say that yes, definitively, younger folks are more agile than older folks. I would say in this case, perhaps those with a bit more experience have a little more leadership agility because they understand and they've been around to see and experience the right times to do this. The other aspect of agility that, again, goes beyond technology adoption is knowing when to stay the course and knowing when to pivot. And this I don't think is a generational construct. This is something that perhaps somebody has been coached to or somebody has been an astute enough observer through multiple experiences that they do. And there are of course people who might be young who are intuitively very good at this for one reason or another. Yeah, I found that when I went to Aviano's Wing Commander, you remember that? The team was very slow to adapt a new automated mission planning system not automated but computerized. And once the old man dove in and did it because you know I'm a geek, that inspired a lot of people not to be less technologically savvy than the old man. So that's one of those roll up your sleeves and clean the toilet moments. And clarity isn't clear. We're almost at time. So you got to be clear on clarity really quickly. Clarity is what is your bigger purpose? The mission that you're rallying people to and guiding toward. It's knowing who is on point for what and creating that clarity for your team. So people are duplicating work, wasting time or toiling an oblivion. And it is having the clarity of do I have enough information in this moment to move forward or do I need to get more data? And being confident and taking a step forward because one of the perils I see with leaders especially today when we have more data than ever is the tendency to get into analysis paralysis and the desire to create a picture and over minimize risk. The perfection is still not possible. So I'm gonna go over on time just a couple of minutes probably. I'm giving that heads up to Eric, our erstwhile engineer because I really do want to hear about this part. What can the Air Force learn from business and your observation, whether it's both organizational design, assessment, with Dr. Co. What would you take, give back to the Air Force from your six years on active? I would give back to the Air Force adjusting the way we do performance management and career development. And it's been a while I'll admit since I was in... They change, they tweak it every year it seems. But it incentivizes checklist behavior and a lot of business performance management systems do that. So it's not that the grass is 100% greener. But one of the things I loved about that delayed project I referred to where we are tinkering with performance management overhauling it was that we took a very balanced approach. We said, there's one part that looks at what you deliver this year. And there's one part that looks at your two to three year contributions. And on that two to three year horizon, there was an element, a measurement for agility. And it is very challenging to define what good looks like for agility. But that was part of our work. And I think that shifted mindsets of our leadership to recognize and embrace and take more smart, like more risks that are smart and well measured because they knew it was something that was being looked at for them. And I know the Air Force is doing performance management at scale for hundreds that I don't even know how many people are there are these days but lots of people, hundreds of thousands is what I was going to say, but a lot of people. And so it's challenging to do that level of rigor and customization at the scale that the Air Force has. However, there's gotta be a better way to think about what is good for the future and how do we incentivize people to get in line for that versus do what has made us successful in the past. So let me shout get off my lawn and be a grumpy old man and say I couldn't agree more and I don't care how hard it is. It's most important work you do as a leader that it drives everything. Your performance assessment and feedback that you give is I always thought and said it's the most important work I did. And so spend less time on PowerPoint and more time on people. What could business learn from the military? I hope you're gonna say instant justice. I can say instant justice because. Okay, if you say that, that'll lead to my comment on an earlier comment of yours. The military and the Air Force in my experience had really great rituals and symbols that made it really clear what mattered and who was responsible for what, at what point. And so I like instant justice because it's instant and it's just but it's also a ritual that used appropriately. It creates a bit of psychological safety. And you alluded to this. Exactly. Earlier where in fire squadrons you do have an interestingly and perhaps ironic safe space to give very direct feedback but that culture for my observation experience didn't carry through the other parts of the Air Force. And so it was a microculture which many all organizations have. And it was something that I wish would be scaled and I still am a little nostalgic for instant justice. So I'm going to share with the audience very briefly, instant justice is a feedback methodology usually on Fridays in the heritage room which could look a lot like a squadron bar where any rank, any person gives direct feedback about things good and bad, mostly bad.  But it does become a same space because you know what you're graded on. How do you fly? And then there are the other silly rituals of do you have the right name tag or t-shirt or whatever on. But it is strangely comfortable. Hey, we are out of time, we're over time. So let me go through a few quick pictures and talk about a little bit more of your life. You did go to Afghanistan, got a great picture of you in Kandahar there. We please watch my episodes thing and everybody else on Afghanistan.  And the one question that remains is all of this thinking and doing about leadership, do you do it as a parent?  Well, one of the greatest leadership books I've read is actually The Joyful Toddler and Preschooler, which talks about how you wrangle these very raw human desires that these little people have. And it talks about how healthy relationships require mutual responsiveness. And that's not giving into every whim of the person you're interacting with. It's acknowledging the whim and telling them, yes, I can help you right now or no, you're going to have to wait, but you're at least letting them know you heard them. And that's actually a principle I've applied to my work in a lot of conversations with people. And then the other piece of what I learned from that book is what people at their core really want is connection, purpose and mastery. And so I use those principles to guide my children through tantrums and find ways to perhaps influence them beyond the hard power of parenting. But it also shows up in the workplace. What I found hilarious was after I read this book, there was a Harvard Business Review article that said, how do you motivate in place its connection, purpose and mastery? And I was like, oh, I learned that from my toddler book. And so there are certain applications and I think the kids show me human psychology at its finest, most unfiltered view. And it's not a humanity in perspective because our brains are not that different as adults. We've just been able to, we've been trained to hide what we're seeking but it's really connection, purpose, mastery. And if you go back to that and you help lead others to that or through that lens it actually can make a really big difference. Well, I wish you'd taught me all this before I got to lead because this has been very interesting and thought-provoking and having just spent time with Stella and Hannah. You haven't mastered it yet, but they'll master you. I'd like to close with a thought that will stay with me when I talk to folks about leadership in kind of a more nuts and bolts sense. I'm very careful not to use analogies about treating the folks who work for you like children because they're not children. That's not the point, but they are human. And that human element is often underbellied. So, Tanya, you remain my favorite, my best and my only daughter. And I've learned a lot from you once again. So thanks for joining me. I'm Big Miss The Power of Imagination. Thanks for the opportunity. I've learned a lot from you too. Hugs to the kiddos. Good luck with the kitchen. Thank you. Thank you. All right, well, that is it. I'd like to remind you that Figments, The Power of Imagination and Figments on Reality are both brought to you by Think Tech Hawaii, a fine nonprofit company that brings incredible community content, very broad from a number of citizen journalists. They need your support, so please support them. I will see you next week on Figments, The Power of Imag, or I'm sorry, Figments on Reality where we'll talk China, Taiwan and the US. And thanks for staying longer after school today, the next six minutes. Please click like and join me again next week. Aloha.