 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 you basically say grant, read on a particular object name to a particular user name. 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 you want to create a product you want to create a sale and you also want to update the inventory. 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 then your data becomes inconsistent your data becomes inconsistent. 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 let's say I say insert into product values and then I give all the list of values that I want to pass and you know insert into the table say 1 comma fruits 2 comma you know household 3 comma automobiles 4 comma stationary where these 1 2 3 and 4 are all your IDs. 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 rollback and commit are the most commonly used transaction statements because a lot of the times you know you are making some changes and you want to commit them to the database until you do a commit unless the auto commit is enabled unless you commit the changes are not persisted. 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. Then you also have data query language which is nothing but your select you know clause and read their database and produce output for the business then you also have transactions 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