SQL Server Crash course | Microsoft SQL Server Tutorial | From Absolute Beginners to Advanced

hey people welcome to this complete sql crash course using sql server who am i hello everyone my name is sandeep and i have more than 10 plus years of experience in sql and i have taught more than 25 000 students worldwide till this point of time by year 2020 and i have expertise in many fortune companies including fortune number one company and i have worked on various domains for example i have worked in retail sector healthcare sector insurance banking share market etc all right so i have given my introduction with this you have to also know that this is going to be one of the best and highest rated sql crash course which is available on youtube all right now what is the average salary of sql developers so the average salary of sql developer is around dollar ninety thousand and it is most demand skill even before java python javascript c plus plus c hash poll and so on that is the reason i'm saying that you should learn sql and with this first let's understand what exactly is sql so sql stands for structured query language so this is a query language which is used to query huge volume of data all right so it can be small volume of data as well as huge volume of data but mostly sql uses to query high volume of data because if you have less volume of data you can even use microsoft excel for example so why do we use sql because we have to query comparatively a better volume of data all right and coming back to this course we will be using microsoft sql server to learn sql and whatever you learn it will be compatible with oracle so you can use this sql in oracle database as well as ibm db2 or you can also use mysql so basically sql is a language which can be run on any databases for example in this particular course we will be taking microsoft sql server but you can also run sql in mysql ibm db2 oracle and a lot of other databases so whatever learnings you will get from this course you can apply on all these databases and please note that this is the crash course which is for absolute beginners so i'm going to assume that you do not have any sql knowledge and i have made sure that approximately it will take two hours of yours so please make sure that you take this course wisely and you give your full attention because i have given so much detailed information about sql so what we are going to do is we will be installing the microsoft sql server 2019 and then on top of that we will import a sample database and then we will work on it later on i'll also show you how you can create tables databases and so on with this first let me just show you how to install microsoft sql server and then we will import a database and then we will start working or rather we will start coding the actual sql statements all right so let's go straight to the board before we get started subscribe to our youtube channel and click on the notification bell icon now let us start by installing the free version of microsoft sql server so just go to your browser and search for sql server download and the very first link which you get is sql server downloads and once you click here you will get this link and as i am staying in india that is the reason i am getting en hyphen i n in the link if you are staying in u.s you will get en hyphen us and suppose if you're staying in any other country let's say great britain then you have en hyphen gb so you can click on any of these links so let's say i am into this link that is en hyphen iron and you get two types that is developer edition and express edition now both these editions are free editions and you can use it freely so for this particular course i will be using this developer edition so i'll just click on download now and you see that i have already clicked here and i got this file downloaded so what i'll do is i'll just copy this file here that is in g drive softwares and ms sql server so you can create your own folder and keep your downloadable file there now double click on this particular exe file now you get three installation type that is basic custom and download media i'm not going to talk about download media and most of the time you can either give basic or custom now i want to install the basic default configuration that is the reason i'll just check basic if you want more control on the download process you can choose this custom so at this moment i'll click on this basic right just read this agreement and just click on accept if you want you can change this install location i'll just keep it as it is click on install and it will take some time so just go and grab a cup of coffee because it is going to take some time to download all the packages now here you see that the first one is instance name and the instance name is a unique connection string of your instance of the sql server and you need an instance to connect to your database the next one we have is log so this log folder is the place where it will create the log files for all the operations which you will perform and this is the installation media folder and this is the resources folder so what i'll do is i'll just click on connect now just to test whether the connection is getting successful or not and if you see this particular message that is microsoft sql server 2019 so you see that if you get this message that means connection is successful so at this moment i'll just close this and i'll just click on this install ssms so this sms represents sql server management studio and you have to install this so just click on this and let me just open with google chrome itself click on ok you can open with any other browser as well and here if you just come down you see that you have this option that is download ssms just click on this link and you see that this particular download process is started right so just wait for some time so you see that this exe file is downloaded in my pc so what i'll do is i'll just copy this and i'll just keep in this particular location so i have kept this exe file here and just double click on this and if you want you can change this particular location but i don't want to change it so i'll just click on install and you see that microsoft sql server management studio is getting installed so please hold for some time until this installation is completed so you see that the setup is completed and you can also close this as well that is sql server developer edition yes and just go to a search in your computer and just search for sql server and you see that you get this particular link that is microsoft sql server management studio 18 so you just have to click here that is click on this so you see here you get this connect to server screen and here you have the server name and in my case the server name is desktop hyphen d5 aj viu and in case if you don't find any server name you can just click here and you can just browse for more and go to database engine and here you will find all the server names in my case i have only one server name that is the reason they're showing this particular server name so here just click on this connect button and in case if you want to confirm your server name what you can do is you can just go to files just go to new and then query with current connection you can just give select and then you can just give add the rate symbols two times and then server name and just click on this execute button or you can just press f5 in your keyboard so here you see that this is the server name in the output and if you just want to know the sql server version then you can simply code a sql query for example you can just give select add the rate at the rate version right press f5 in your keyboard and you see that this is the particular version right that is microsoft sql server 2019 so we have successfully installed the microsoft sql server 2019 along with microsoft sql server management studio in the next video lesson we will download and import a sample database which is adventure works sample database on top of this microsoft sql server 2019 and here towards the left you see that under databases we have system database as well as database snapshots right but we cannot use the system database for training purpose so it is important to either create a database or use an existing database and for this particular course at start we are going to download and import adventure works 2019 database and as we move towards the later part of the course we are going to create our own database as well so at this moment let us see how to download and import adventure works 2019 database in our microsoft sql server management studio and the reason why i'm going to choose this particular database is because this database contains the details of human resource employees person details sales and a lot of detailed data so this is a perfect database to import this particular sample database what you have to do is let us go to google or whatever browser you are using just search download adventure works sample database and the first link which you get is adventure works sample databases sql server and if you just come down you see that you have another link from github so if i just click on this first link you'll get this and if you just come down you see that you have oltp data you have data warehouse data and then we have lightweight data right so oltp database is online transaction processing workloads while data warehouse is for data warehousing and lightweight is a lightweight database as the name suggests so for this particular course we are going to download this oltp and if you just see here you have adventure works 2019.bak file now if you're using a very old version of microsoft sql server for example if you're using microsoft sql server 2016 then you can use this that is adventureworks 2017.bak and if you're using microsoft sql server 2012 then you can use adventure works 2012.bak so depending on the version of your microsoft sql server you can use the corresponding oltp even if you have installed microsoft sql server 2016 if you want to import adventure adventureworks 2017.bak then it is not a problem at all but for this particular course i am going to use this so once you click this you will get this adventure works 2019.bak5 you can see that i have already downloaded this and i have kept in this particular folder now what you have to do is you have to go to c drive so if i just go to my c drive and inside that you have program files click here and go to microsoft sql server right and then you have this particular folder that is this is the particular instance you have at this moment so click here and go to ms sql inside that you have backup folder so just click here and continue and what you have to do is you have to copy this adventureworks 2019.bak file and paste it here now just go to your sql server management studio and make sure that you are connected to it and then what you have to do is you have to just go to database and just right click and click on this restore database click here now here click on device and towards your right that is extreme right you see three dots just click here and here you see that you have backup media fi that is file and url now since you are going to import a file so you don't have to choose url so so file is okay and click on add and here you see that you have this particular backup now if you don't see your dot bak file here you can just click on this refresh button and you will be able to see that in case if you are not able to see it here or if it is not pointing to this particular backup file location then what you can do is you can just go to backup and you will be in this particular location all right i'll just click on ok and you see that it is already present here now right and click ok now make sure that this box is checked and again you click on ok this will take some time so what basically it is doing it is going to import this particular database so you see here that database adventure works 2019 is restored successfully click ok and here you see that you have this particular database hey people welcome back so i assume that all of you have installed microsoft sql server management studio so first make sure that you are connected and here you go to databases click here and you see here we have the database that is adventureworks 2019 and once you expand this you just click on this plus symbol and you see that here there are lists of tables all right so let me just show this so you see here here this is the schema name that is human resources is schema name and department is table name similarly here person is schema name and address is table name so it is like schema name dot table name all right so let's take one table for example let's take human resources dot imply table so to see all the columns of the table you can just click on this plus symbol and you see these are all the list of columns and you see for example business entity id so this is one column and this is also called a field similarly we have national id number which is another field and we have login id and so on and you see here this is pk so pk denotes primary key and fk denotes foreign key so we are going to talk about pk and fk later on in the course at this moment you see that the third one we have is int that is integer so this particular field is of integer data type that means it will consider only the integer values next one we have is national id number and you see it is and where care of 15 that means it is a string data type of 15 length so we are going to talk about all these data types later on in this course so at this moment let us try to focus on selecting the records from this particular table so for this what i'll do is let me just bring this towards left and this is my table so just click on this new query i'll just click on this new query and wait for it and you see now that we are into adventure works 2019 now it is very important that you should select adventure works 2019 and suppose you are in master so if you are in master project and if you want to go to adventure works 2019 then either you can just select here and choose this or you can just write here use adventure works 2019 and just click on this execute button so this is the execute button which will run all the sql queries so to run all the sql queries or any one sql queries you can just click on this execute button so you see if i just click it you see it says command completed successfully and now i'm into adventure works 2019 all right so let me just delete this and let's say i want to retrieve all the records from this table so for that the syntax is select star from table name so it is human resources dot employee so please note that here you have to give schema name dot table name where human resources is schema name while employee is table name and attend it is good to give semicolon even if you don't give semicolon it is not a problem but i would suggest you to give semicolon and if you just give select star that means you are trying to select all the columns of the table click on execute wait for some time and you see these are all the records from this particular table so you see we have business entity id we have national id number login id and so on and let's say if i want to only select few columns for example let's say i want to select only business entity id so i can just say business entity id and let's say i want to just get job title so i'll just say job title from this particular table name click on execute and this time it has only selected these two columns similarly let's say if i want to only select job title you see i'm able to only select job title now here you see that we are having duplicate records as well so if you want to select only distinct records then you can just give your distinct keyword and once you click on execute you see now it is showing all the distinct records so here you see it is showing 67 rows and previously if i just remove this distinct keyword click on execute now you see it is showing 290 rows that is the reason if you want to select only the distinct record that means only the unique records then click on execute and you will get only the unique records all right now let us try to filter the records based on a particular condition for that we have to use where clause so let's say i want to get all the records where job title is research and development manager so let's say if i just copy this and if i just give where job title equal to and i have to give this value in single quotes so if i just give like this you see this is a string and that is the reason if it is a string you have to give that value inside single quotes right so if i just execute this you see i have only two records where the job title is research and development manager in the same way you can also give some other operators as well so in this case we have used equal to operator and let's say if i just execute only this particular statement i can just select this and click on execute and you see i'm getting all these records and let me just comment out this particular line so for commenting out you can just give two hyphens or two dashes and you see this is commented out and if i execute this it will only execute this particular statement all right and let's say this time i want that i want to get all the records where business entity id is greater than let's say 50 so i can just give here where business entity id is greater than 50 and if i execute you see i'm getting all these records where business entity id is greater than 50. similarly you can just give greater than equal to also that means business entity id must be greater than 50 or it can be equal to 50 as well click on execute and you see this time it has also selected 50. similarly you can give less than equal to also so if i execute you see it is selecting all the records till 50 so either it should be equal to 50 or less than 50 similarly i can use less than 50 and this time it has selected till 49 all right so this is the way to filter all the records using where clause let's proceed further and suppose i want to add two numbers or let's say two columns for example let's say if i have subtotal and let's say i have tax amount and if i execute this you see i'm getting subtotal with tax amount and let's try to add these two fields together so for that i can just use plus operator for example i can just give subtotal plus tax amount and you see that both of these are numeric fields all right so if i click on execute you see it has added the value of subtotal with tax amount and you see here we have the new value now here you see it is not showing any column name that is it shows no column name so if you want to display a particular column name of your choice then you can use an alias for that you have to use as keyword so you can just give here as let's say if i want to give an alias that is a temporary column name so i can just say like let's say sub total with tax amount so i've given a meaningful name so if i execute this now you see this is the column name which is displaying here so this is just a temporary column name in the same way you can also give minus operator for example if i just give your minus and let's say if i just remove this and give something like let's say a and you see here if i click on execute now this is the difference between subtotal and tax amount similarly you can give into or you can also give division operator so for example if i just give like this let's execute this so in this case if the value of this tax amount is 0 then that will be a problem so if i just show you you see here in this case tax amount is not zero that is the reason we are not having any problem but let's say the value of tax amount is zero and if i execute this you see it says divide by zero error encountered so whenever you give division operator make sure that this particular value whatever it is having must not be equal to zero so it can be either greater than 0 or less than 0 and in this case i see that all the records are greater than 0 so i can just give here where this particular column is greater than 0 like this so it is better to write something like this that is where tax amount is greater than zero and if i execute this you see that it has executed successfully hey people welcome back another point is you can use all the arithmetic operators that is plus minus multiplication or division operators in where clause as well for example you can also give something like this that is you can just give where subtotal plus tax amount is greater than 500 so you see if i execute this now you see this particular column will be having values which is greater than 500 but please note you cannot give something like this that is where this particular temporary column name is greater than 500 and you see it gives a red underline and it says invalid column name if i execute you see it gives an error this is because the order of execution in sql server engine is different so what happens is sql server engine first considers from clause and then it reads the where clause and at end it reads the select clause so that is the reason first it goes to this particular table and then it goes to where statement and it tries to find this particular column but until now this particular column is not defined so that is the reason this particular query fails so in this particular case you can just give here subtotal plus tax amount is greater than 500 and this will perfectly work now another point to note is that let's say if you are into this particular table that is person table and you see here if i just delete all these and if i just say select start from person dot person table and you see here i have to just correct this and if i execute this now you see i'm getting all the columns and you see here let's say i want to get the first name middle name and last name so i can just give here first name and then middle name comma last name and if i just execute this you see i'm getting all these columns and let's say i want to combine all these columns for example i want to create a full name where it is combination of first name middle name and last name then i can just give plus operator as well so for that what i have to do is i have to give first name plus middle name plus i can give last name and let's give an alias for example i'll just say full name just execute this and you see to execute you can just click on this execute button or you can press f5 in your keyboard right both the options you have and now you see the full name is first name plus middle name plus last name so when you use plus operator with integers or numeric fields then it will add those numbers but if you use plus operator with strings then it behaves as concatenation that is combining multiple strings and just to make it more better what you can do is you can just give first name and then you can add a space so i can just give you see here i've just added a space inside two single quotes and here also i can just give single quote space and then i'll just give plus last name and if i execute this now you see it looks more better right so this is also another way to use plus operator let's talk about null values so you see here we have some null values in few columns right so anyways this is the resultant column name and you see here we have null values similarly here we have null values so what exactly is null value so null values are unknown missing values and you cannot define a particular value for null values and that is the reason null values are unknown and missing values and let's say if you want to know all the records which contains null values then you can give a condition something like this that is let's say i can just give here where in this case you see here middle name is containing null values so i can just say where middle name is null so you see if i just give where middle name is null and let me just remove all these this is not required and if i execute this now you see i'm getting the first name middle name and last name where middle name is always null similarly let's say if you want to remove all those records where middle name is null value then you can just give is not null operator for that you can just give where middle name is not null and if i execute this you see i'm getting all the records where middle name is not null all right so that's it in this video lesson take care bye hey people let's talk about add operator and or operator i am into employee table and i want to get all the records where marital status equals s and gender is equal to female that is f so for that i can just give where marital status equals s and i can just use and operator i have to give and keyword and i can just say gender equals f that is female so if i just execute this it will give me all the records where that person is single and the gender is female so you see it is s for all the records and you see for gender column you will be having only f and let's say i want to get all the job title so first let me only execute the selected condition without giving where clause so if i just comment out this let's say if i just comment out and in this case you see we have lot of job titles now if i want to know all the records where either job title is design engineer or it can be research and development manager then in that case i can just give where job title equals and let's say design engineer so i can just say design engineer or i can just say that job title equal to research and development manager and if i execute this if i just execute this now you see i'm getting five records and you see either the job title is design engineer or we have research and development manager hey people welcome back now you see that in previous example we have used this job title column multiple times using or operator so if you have a requirement where you want to test for a particular column multiple times then it is not good to use or operator in that case it is better to use in operator so in the same case if i just comment out you see the better way to write is where job title in and inside this you have to pass the list of values for example you have design engineer and then i can just give a comma and another value which is a research and development manager and so on so you see here we have only two values and if i execute i'm getting the same result and it says where job title in design engineer or research and development manager right so this looks more better and suppose if i just select all the records from table you see here i'm getting so many records and let's say i want to get all the records where business entity id is either 1 5 10 or 15 so in that case what i can do is i can just give here where business entity id in and here i have to give let's say 1 5 10 and 15. so let me just give a semicolon at end and i'll just comment out this line so if i just execute this now you see i'm getting all the records where business entity id are either 1 5 10 or 15. so it is same as business entity id equal to 1 or business entity id equal to 5 or business entity id equal to 10 or business and at id equal to 15 but this looks more clear and another operator which we can use is between operator so let's say if i just select this and get all the records now let's say i want to know all the records where business entity id is between 1 and let's say 50. so in that case what i can do is i can just give where business entity id between 1 and 50. so let me just comment out this line and you see if i just give between operator and you see here if i execute this now i'm getting all the records that is 50 rows where business entity id are between 1 to 50 right so this is all about between and in operator hey people welcome back let's talk about like operator so suppose you want to match a particular pattern then in that particular case you can use like operator for example let's say if i want to know all the records where name is starting with a and after that it can have 0 or more number of characters then i can just give here like where name like and i can just give a percent so please note percent means that it will match 0 or more number of characters so if i execute you see all these names are starting with a and after that it can have either 0 or more number of characters and let's say if you want to start all the names which is starting with a l or let's say ala then i can just give ala you see here it will give me all the records that is at this moment it is having two records similarly if i just select only this and in the same way you can also find all the records which is ending with a particular value for example let's say i want to know records like this that is it is ending with o so i can just give here o and this means that percent o that means before o it can have zero or more number of characters so if i execute this now you see all these names are ending with o right in the same way let's say if i just execute only this and let's say i want to know all the records where name is containing let's say six characters so you see here we have alberta then we have alaska and so on right now let's say i want to know all the records which is starting with a l and after that it can have four characters so overall it will be having six characters so for that i can use underscore so underscore will match only one character so if i just give one underscore for one character in the same way i have to give total of four underscore and you see if i execute this now i have two records where the length is 6 and it is starting with a l right so in this way you can use like operator all right let's talk about sorting of result so here let's say i want to get for example let's say i want to get city and let's say postal code and if i just execute this now you see i'm getting city and postal code but you see here these two columns are not sorted what i mean is let's say i want to sort city that is this particular column in descending order or ascending order then for that i can just give order by clause so if i just give order by city and if i don't give here d esc or asc that means by default it will be in ascending order so if i execute this now you see it is sorted and this result will give you everything in sorted order and by default it will be in ascending order and if you just come down you see at end it is starting with z or z right similarly if i just say let's say the esc that is for descending order that is for descending order then if i execute it now you see now it isn't decreasing order right in the same way i can just say order by let's say postal code in descending order so you see here now i'm getting all the records in descending order now let me just write another query for example if i just give select star from person dot address or let's take person dot person table and if i execute this now let's say i want to sort the result based on two columns that is first name and last name then i can just give here select first name and last name and if you want you can also give another column so it is not mandatory that whatever column you are giving in where clause it must be present in the select clause as well so it is optional and it is based on your particular requirement so let's say i can also give your middle name from person dot person table where if i want to add a particular condition let's say middle name is not null and if i want to order the result let's say if i want to sort the result based on a particular column or i can also give list of columns for example i can just say order by let's say first name in descending order comma last name in ascending order so either i can just give asc or even if i don't give so by default it will be ascending order but i would recommend to give asc for ascending order so that the query looks more meaningful and if i just execute this you see here we are getting first name last name and middle name which is sorted by first name in descending order so here you see we are getting zo zo and then here we are getting ballet and bell so for the last name you see it is sorted in ascending order so both the records are matching in that case you see it will be in ascending order that is increasing order so first b a i l ey has come and next we have got bill right so in this way you can use order by clause to sort the result all right let's talk about group by clause group by clause is used to group the records per category for example you see here for this particular table that is sales order detail table you see there are a lot of entries for sales order id five 43659 so you see here from here to here there are 12 records with different unit prices right similarly you see for sales order id 4360 we have two records and so on so if i want to group based on sales order id and i want to know the total of unit prices for each sales order id then in that case we can use group by clause so what i mean is let's say if i just copy this and if i just give group by sales order id and if i want to know the total of unit price per sales order id so for that i can just give an aggregate function called as sum so this is one of the aggregate function and if i just give an alias let's say total unit price per sales and if i just execute this you see for this particular sales order id that is 43659 the total of unit price is 143 23.7118 so what i mean is let's execute both these and you see for this particular sales order id we had so many entries and if you do the summation of all these till here for this particular sales order id you get this particular value that is one four three 14323.7118 similarly if you take this particular sales order id and you see there are two entries so for that the sum is one two nine four so you see if i just add this plus this i get this particular value that is 1294.2529 in the same way there are a lot of other aggregate function for example i can also give an aggregate function called avg which is used to give the average value per category similarly i can also give count and this is used to give the count of total number of records in the same way we have max and it is used to give the maximum value out of that particular column and we have min which is used to give the minimum value so let's try to give examples of all these so we have already seen how some of this particular unit price has given me the total sum in the same way if i just give average of unit price and if i just give here average of unit price per sales if i execute now this time it has given me the average of all the unit prices per sales order id so for this particular sales order id that is 43659 this is the average unit price similarly i can give the count also so if i just give count of unit price as let's say count of unit price per sales or i can just say count of unit price and if i just execute this you see here this particular sales order id there are total of 12 unit prices so if i just execute this let me just show you also so you see for this particular sales order id we have total of 12 records so from here to here we have total of 12 unit prices and that is the reason if you give count of unit price it will give the count of total number of unit prices per category that is per sales order id right because i am giving group by sales order id similarly if i just give max of unit price as let's say if i just give max unit price and if i execute both these sql queries so you see here for this particular sales order id 43659 the maximum is you see it is 2039.994 right and that is the reason it has displayed the maximum unit price as 2039 dot 994 similarly if i just come down let's say for sales order id 43660 we see that the maximum is 874.794 so here you see we have got 874.794 right so in this way you can use max and we also have min and let's execute both these queries and here you see that for this particular sales order id the minimum it is showing as 5.1865 so if i just come here you see here the minimum is 5.70 and i think this is not minimum yeah you see here this is the menu that is 5.1865 and that is the reason it is showing as five dot one at six five so in this way you can use all these aggregate functions along with group by clause let's talk about some other functions i have taken the person table because it has lot of string values so you see we have first name middle name last name and so on so these are all strings and i want to show some functions related to string so that is the reason i have taken this person table and if you remember we had coded this particular sql query where we had used the plus operator to combine multiple strings in the same way if you don't want to use plus operator to combine strings then in that case we have a function called concat function so what i mean is if i just give concat and here i have to give the first name middle name and last name and let me just give a comma and then single quote and let me just give a space between first name and middle name similarly i'll give a space between middle name and last name and you see here that if i just execute this i'm getting the same result that is sayed e abbas and so on and in this case if i just remove this where and if i just execute now you see here that if we have null values so here in middle name we have null values but if you use the concat function it will ignore the null values and you see here it is combining the kim with last name that is kim abercrombie right so in this way it is better to use concat function in place of plus operator if you are not sure that any particular record might contain null values similarly we can give the length function so let's say if i want to know the length of each record for example let's say for first name so what i'll do is let me just delete this last name and middle name and let me delete all these so if i'm just here let's say if i just get the first name first and i want to know the length of this particular string similarly if i want to know the length of the second record so what i can do is i can use the len function that is length function and here i have to pass this particular argument and if i just execute this you see that length of this string is 4 that is it has 4 characters so length is 4. for this particular string it has 9 characters and the length is 9. in the same way if i want to extract fixed number of characters from left then i can give left function so if i just give left or first name and let's say i want to extract two characters from left side of the first name and i'll just give an alias for example extract from left let's execute this and you see it gives sy because i have extracted the first two from left similarly here it gives ca and so on in the same way if you just give right then it will extract a fixed number of characters from right side and if i just give let's say three then let me just change this alias as well so that it looks more meaningful and if i execute this now you see it has extracted three characters from right side so it is y e d similarly here it is i n e and so on now if you want to extract a particular sub string from main string then you can use substring function for that i can just give sub string and let's say you want to extract let's say from third position you want to extract a length of 4 from this particular string that is first name and let me just change it to extract sub string and click on execute now you see here that from the first name starting from third position so you see starting from e i want to extract a length of four so here anyways it is just two characters left after e so that is e and d so that is the reason it has displayed ed for the second record you see the third position is t and from t it will extract a length of four so it is t h e r and you see it has given me t-h-a-r so these are five important string functions and i just wanted to convey this to you so that it will be easier for you to work with strings all right let's talk about some date related functions and the first one is day function so you see here we have order date for each particular sales order id and if i want to extract the day part from this order date then i can just use the day function so day of order date and i can just say an alias like extract day let me execute and you see this is 31 that is the day for this particular date is 31. similarly if i just come down you see here for this particular date that is 2011 0601 the day is one similarly you can also extract month so for that you have to give month function and let me just change it to month and here if i execute you see for this particular date the month is 0 5. similarly if i just come down you see for this particular date the month is 0 6 and so on in the same way you can extract the year from a particular date for that you have to use the year function and let me just change it here as well if i execute this now you see we have extracted the year so it is 2011 and if you want to extract the current system date with time and without any time zone then you can use current underscore timestamp so if i just give select current time stamp and if i just execute this you see if i just give f5 in keyboard it has given me the current system date with time so this is the time part that is 17 hour 20 minute 40 second and 210 is millisecond and you see here it is not giving any time zone for that i have used current timestamp in this video lesson i am going to talk about having clause in this particular table that is sales order detail table let's say i want to know the total unit price for sales which is greater than 10 000 so what i mean is let's say if i just give this that is sum of unit prices is greater than 10 000 so you might think that i can use where condition that is where clause for this and if i just give where sum of unit price is greater than 10 000 and you see if i just click on execute this will fail and i'll let you know why it will fail so you see that it says an aggregate may not appear in where clause so please note that you cannot give an aggregate in where clause until it is a sub query so i'll just show you about sub query later on but at this moment please note that you cannot define an aggregate function with where clause if it is not a sub query so this is wrong and if you want to know all the total unit prices per sales order id which is greater than 10 000 then you cannot use where clause for that you have to give having and please note that you have to give having clause after the group by clause that means you cannot give having clause before group by clause and if i just execute this now you see this time it has given all the sales order id with total unit prices for sales where sum of unit prices are greater than 10 000 right so you see here all the records are having sum of unit prices greater than 10 000 similarly i can give where condition for a particular column but not for aggregate function so here i can give something like this that is where sales order id is greater than let's say 50 000 so this is fine i can do like this but i cannot give here that where sum of unit price is greater than 50 000 so if i just execute you see here it is giving me all the records where sales order id is greater than 50 000 and sum of unit prices is greater than 10 000 and i can also sort the result so i can just give here that is order by let's say i can just give sales order id and i can also give descending that is in descending order and if i execute it you see i'm getting this result now please note that you have to follow this order that is first you should give select followed by from and where then followed by group by then having and order by so if you are using all these statements then you have to mandatorily you have to follow the same order and if you change the order for example let's say if i just give order by here let's say if i just give auto by here you see it is showing an error so if i just execute it you see it says incorrect syntax that means you have to follow the order similarly let's say if i just give group by clause before where clause you see again it will throw an error if i execute it it will give an error right so it is mandatory that you have to follow this particular order if you are using where group by having and order by clauses all right and please note that this is the order in which you have to code but sql server engine will not process in the same order so first it will consider the from clause then it will consider the where clause followed by group by clause and then it will process the having clause followed by select clause and attend it will process the order by clause so this is the order in which sql server will process this particular sql statement all right so please keep this in mind and this is the order in which you have to code right so if i just execute it now i'm getting all the proper result in sql server engine it is possible to code a query inside another query for example let's say i am into purchase order header table and i want to get for example purchase order id and employee id from purchase order header table where all the records from this table must be present in this particular table that is purchase order detail table so you see here the second table which we have taken is purchase order detail table so in that case you can use a sub query so what i mean is let's say if i just come here and if i just say select purchase order id comma employee id from purchase order header table where purchase order id in so here i'm using in operator so i can just say in and i can just pass a sub query so you see here i'm just creating a sub query and inside this you see this is my sub query so i'll just pass this as my sub query and let me just delete this and you see here what i'm trying to say is that i want to get the purchase order id and employee id from purchase order header table where purchase order id from this particular table is present in select star so in place of that i can just give select purchase order id from this particular table where purchase order detail id is greater than 5. so if i just run my inner query you see if i execute this i'm getting all these purchase order ids and you see i'm getting eight eight four zero rows and if i just execute my whole query you see i'm getting the purchase order id and employee id from this table where all the purchase order id from this table must be contained inside this particular query so you see here i'm getting 4008 rows and let's say if you're certain that your inner query will only give one result so if i just give for example let's say equal to 5 and if i just execute it now you see it is giving me only one result so in that case you can also give equal to operator because if you give equal to operator that means this particular column will match against the fixed value and in this case it has returned 4 so indirectly what you are saying is where purchase order id equals 4 so if you execute you see it is giving me one result that is 4 and 261 for purchase order id and employee id respectively so if i just remove this 4 and you see here if i just execute it it is giving me the same result that is 4 and 261 so this is how you can use a query inside another query and this is called as subquery is there a way to combine rows or records from multiple tables into a single select statement yes that is possible using union operator so for example let's say i have one column that is business entity id which i am going to select from employee table from person table i am selecting business entity id and from customer table if i select customer id you see i'm getting all these results and if i want to combine all the rows then in that case i can just give union operator so i can just give here union and here also let me just give union so union operator will be used to combine the records or rows from multiple table into a single select statement and please note that union will remove all the duplicate records so if i just execute now you see here it has given the list of all business entity id and total number of records you see here it says 29518 rows so please note that if i just give like this you see here the number of columns in all the select statement should be same so here i have given business entity id here also i have given business entity id and in this case i have given customer id so you see in all these select statement i have given only one column so the number of columns should match and also please note that the data type for each columns must be same or similar and in all three select statement you see here business entity id is numeric field here also business entity id is numeric field and here we have customer id which is also numeric field so please make sure that whatever columns you are considering must have the same data type or similar data type so you see here we are getting 29518 record and if i just give union all you see if i just give union all it does the same operation but it will not remove duplicates so if i just execute this time you see here it gives four zero zero eight two rows so union operator will remove the duplicates while union all will not remove the duplicates but both these operators will combine records or rows from multiple tables all right in the previous video lesson we understood that if you want to combine the records from multiple tables then you can use union operator and if you want to combine multiple columns then you can use joins so let's say i want to get purchase order id purchase order detail id and order detail from these two tables by combining these two tables then i can give inner join so that i can get the matching records from both the tables so what i mean is let's say i want to make sure that the purchase order id which is present in purchase order detail table should also match the purchase order id from purchase order header table and i want to combine the purchase order id purchase order detail id with order date then i can give join or inner join so inner join and join is same so what i mean is let's say if i just give select purchase order id comma purchase order detail id from purchasing dot purchase order detail table and if i just give here inner join and if i just give let's say if i just remove all these and if i just give inner join purchasing dot purchase order header on let's say i want to match purchase order id so purchase order id from purchase order detail table equal to purchase order id from purchase order header table and also i want to get the order date right now please note that purchase order id is common between these two tables so how the system will understand that which purchase order id you are talking about for that what i'll do is for this particular table name i'll just give a table alias so for example i'll just give here pod and for this particular table i'll just give table alias as poh now if i just give here let's say pod dot purchase order id that means i am trying to say that select the purchase order id from pod that is from purchase order detail table and here you see we have poh so i want to get the order date from poh so i'll just give you poh dot order date and this purchase order detail id i want to get from purchase order detail table so i'll just give here pod dot purchase order detail id and i'll make sure that i'll just give here pod dot purchase order id equals poh dot purchase order id so what i mean is you see here what i'm trying to do is i want to select the purchase order id purchase order detail id from purchase order detail table and i want to get the order date from purchase order header table where purchase order id from purchase order detail table is equal to purchase order id from purchase order header table all right so you see here i'm matching the column that is purchase order id and this is called inner join if you use the keyword inner join or you can also give join so both are same but to be more precise i used to prefer inner join and if i execute it you see here i am getting all the records which are common to both these tables that means all the matching records where purchase order id from one table is equal to purchase order id from another table so in this case i have combined columns you see here we have purchase order id purchase order detailed id and order date so conclusion is if you want to join multiple rows then you can use union and if you want to combine columns then you can use joins and in this case we have used inner join and there are some other joins as well which we are going to discuss in the next video lesson take care bye hey people welcome back so in the previous video lesson we learned about inner join and let's consider these two tables that is person table and business entity address table so if i want to get the matching records from both the tables in such a way that i should get business entity id from person table first name from person table i also want to get last name from person table and i want to get business entity id from business entity address table and i also want address id from business entity address table and you see here if i just execute this i'm getting this particular result using inner join and please note here that we are getting business entity id from both the tables so here we have business entity id from person table and again we are getting business entity id from business entity address table and i want to get all the matching values only so here you see we have the matching value that is business entity id from person table must be equal to business entity id from business entity address table now let's consider a scenario where i want to return all the records from the left table and only the matching record from the write table so here you see this will be my left table and this will be my write table so in this case if i want to return all the records from left table that is person table and only the matching records from the business entity address table then in that case i can use left join so here i just have to give left join so what it will do is it will give me all the records from the left table that is person table irrespective of whether it is matching that is whether this particular condition is satisfied or not but it will only give the matching records from the right table so if this particular condition is satisfied then only it will pull the values from the right table that is business entity address table and if there is no match between these two tables then what it will do is it will just give null values for the right table so if i just execute this now you see here the business entity id that is 285 is present in both the tables so that is the reason it has pulled out all the records but you see business entity id that is 293 is not present in the right table that is it is not present in business entity address table just to verify that i can just give here where business entity id equals 293 and if i just run this let me just execute this you see it gives an empty result that means 293 business entity id is not present in business entity address table so if you just see the output in this case it has pulled all the records or all the values from the left table that is person table so you see here 293 katherine and abel while it has not pulled anything from right table so it has just given null values that is unknown and missing values for business entity id and address id from business entity address table similarly you see business entity id 295 is not present in write table that is business entity address table and that is the reason it has pulled this record from left table while business entity id and address id from the right table is having null values so whenever there will be match you see it will be having complete list of values and if there is no match then it will pull all the values from the left table but it will not pull any value from the right table and in that case it will just denote as null so that's it about left join take care bye hello everyone as we have left join in the same way we also have right join in right join it will return all the records from the right table and only the matching records from the left table so in this particular example if i just change to right join you will notice that it will give all the records from business entity address table so this is my write table and this is left table so it will give all the records from the right table irrespective of whether it is matching or not and it will only pull the matching records from the left table that is person table so if i just execute this now you see business entity id 285 is matching so it has given the complete result and if i just come down you see these are all matching records and let me just come down until i just find not matching record so let me just come down and i can see all of the records are matching and if i just come down still you see all these are matching records and yes you see here for example business entity id 1394 it is only present in write table that is business entity address table and that is the reason it has pulled out the records from this table while it has displayed null for person table so just to verify this i'll just say where business entity id equal to 1394 and if i just execute you will notice that this record will not be present so if i execute you see this particular record is not present in person table but it is present in business entity address table and that is the reason you see it has pulled this particular record in the output with write table as correct values but left table is not having values similarly you see all these records you see all these records are only present in write table but they are not present in the left table so in this way you can use right join to pull all the records from the right table but only the matching records from left table the combination of left join and right join is called as full join so before i show you full join please note that for right join we can also say right outer join and for left join we can also say left outer join for example if i just give you right outer join you see it will give the same output as right join so at this moment it has 19 614 rows and if i just execute this you see it is giving me 19 614 rows so right join is same as right outer join while left join is same as left outer join and coming back to the full join if you want to return the records from both the tables that is left table and right table which are matching and if you want to return all the record from the left table while only the matching record from right table and another thing is let's say if you want to return all the records from the right table and only the matching records from the left table then in that case you can just give full join or full outer join so if i just give here full join and if i just execute this you see here it has full outer join and you can also give full join it is same let me execute it and you see these are all matching records that is 285 and 285 but if you see here 293 is not matching so it is present in left table so you see it has given the record from left table y null from right table in the same way you see if i just come down you see these are records that is 578 1852 1096 etc these are only present in write table so you see it has pulled the record from right table while displaying null for the left table so full outer join is the combination of left join and right join before you start creating tables you must know all the important available data types in sql server so these are all the important numeric data types so the first one is bit data type and this is used to store the whole numbers that is 0 or 1 and in few cases it can also store null value overall it will have one byte in memory the next one we have is small end which is used to store whole numbers which are small integers so it will store whole numbers between minus 32768 to 32767 and in memory it will occupy two bytes you can also use integer if you want to store whole numbers between this particular range and it will occupy 4 bytes in memory if you want to add auto increment field then you can use identity so you see here if you use int with identity and if you just give s comma i that means s is starting value while i is increment by so for example you see here if i just give product id int identity 1 comma 1 that means product id will be auto increment field starting from 1 and each time you add a record it will add 1 and create the next record for product id and if you just give for example product id int identity then it is same as int identity one comma one and if you just give let's say one comma three that means it will start from one and every time it will create a product id after adding 3 to the previous product id so it will be like 1 4 7 10 and so on the next one we have is money which is used to store larger currency field that is if you want to add monetary data between this particular range that is from here to here then you can use money field and it occupies 8 bytes in memory and for identity it occupies 4 bytes similarly if you want more precision over your data then you can use float so you see here it can store a precision number between this particular number to this particular number and depending on the value of n it can occupy either 4 or 8 bytes let's talk about string data type and the most important string data type is care and wear care so you see if i just give care of n then it can store a text of fixed length and maximum it can have 8000 characters and depending on the value of n it will occupy a memory in storage and if you want to add variable field record which will store text then you can use where care of n and maximum it can also store 8 000 but in this case it will occupy 2 bytes plus number of characters and please note that this is variable character so if you're not sure whether you will get a fixed character or variable character then it is better to define with where care of n now let's talk about n care and and where care so both of these are same as care and varca respectively but the only difference is that these are used for encode strings so encode strings are like encoding standard which is oftenly used for cases where multiple language and scripts are used so in this case it will store 2 times the fixed length that is 2 into n and in this case it will be 2n plus 2 bytes and if you are not sure whether you should use and care or n where care then it is better you use care and varico respectively so these two are more popular compared to and care and anwar care talking about date and time data types the first one we have is date time which is important and this is used to store date within the range from this particular year that is 1753 0101 to 1999 1231 and the accuracy of this particular date in millisecond is 3.33 which means that it will round the value like point zero zero three point zero zero six point zero zero nine and so on in memory it will occupy 8 bytes similarly you can only use date if you don't want time so in that case this data type is used to store a date between zero zero one zero one zero one two nine nine nine nine one two three one so this is the default format that is y y y hyphen d d hyphen mm and in memory it will occupy three bytes let's talk about timestamp so let's say you want to add a unique timestamp which will be from the internal clock and every time if you update a record it should automatically get updated from the internal timestamp while creating the row or while updating the row then in that case you can use timestamp and another data type which is not from numeric string or date data type but this is a common data type which is called as unique identifier and this is used to store a 16 byte guid that is globally unique identifier so you see here if i just take a column you see here for row guid it is having a unique identifier field so this is same as what i'm showing here so it will be like 16 byte guid and often it is used for row id so you see here for this particular row id it is unique identifier so keeping these numeric data types string data types and date data types along with this general unique identifier i'll start creating tables in next video lesson till then please go through all the data types which we have discussed in this video lesson take care bye now the time has come to start creating tables now we know that in year 2020 a pandemic came which was kovid 19 and by april or may it was spread in almost all of the countries of the world and even by 2019 and in few countries it started spreading so let us try to create a table and in that particular table we will just store few columns or few fields that is for example let's capture the date and i will also capture daily confirmed cases and daily deaths and based on particular country and we will also capture the total loss and we will add a flag so if the flag is 1 that means the covet case is true and if the flag is 0 that means there is no covid cases in that particular countries for example by the time i am recording this video lesson few countries for example palau or kiribati or tualu these countries does not have a single case of kovi 19 so let's try to create a table now before that let us try to create an empty database so to create a database we have to use create database and then database name so let's try to create a database so i'll just give here create database and name of the database so i'll just say for example let's say kovid 19 so this is the database i'm going to create or you can also give any other name as per your requirement so if i just execute so you see it says command successful and let me just click here or let me just go to database and refresh it so if i just refresh now you see here we have a database called kovit 19. so i'm going to create all the tables inside this particular so you see at this moment there are no tables that means there are no user tables so these are all graph tables external tables file tables and system tables and we are not going to learn anything from these tables much so we have to create our own table for that we will use create table statement for that the syntax is create table and table name and you have to give the list of column that is column one data type 1 again you have to give column 2 data type 2 and so on and at the end you have to close the parenthesis so this is the syntax now let us do one thing i'll create a table with the name kovid stats so i can just say code stats and let's try to add a column that is for example let me just give kovid id so i'll just give here covid id and let's try to give this as integer field and with identity so that means this is auto increment field so let me just delete all these and let's add another column that is let's say date and for this i'll just give data type as date time and the next field which i want to add is daily confirmed cases for this i will just say end and let's say i want to add daily debts so i can just say daily depths and let's say i can just make this as small end and you see here i can just make it as capital d and the next field which we can add here is let's say we can add country and we can also add for example kovid flag and before that for country i'll just say where care as 25 so i'm just trying to add a variable character right which is having a length of maximum 25 and let's try to add covered flag and this will be my bit field so this is like a flag which will be containing a value of either 0 or 1 and if you do not pass any value it can contain null value also and let's add another field that is total loss so i'll just say total loss and this will be my money field right so let me just format this properly and what i'll do is i'll just say time and then we have daily confirmed cases daily deaths then we have country code flag and then total loss right so let me just pull it down and you see i have used create table table name that is covid stats and then first column is kovid id integer with identity field that means it will be having auto increment so let's say i can also add something like this that is it can start with 1 and every time it will get incremented by 3 so i can add something like this as well and next i have date so let's say i'll just say like go with date so this looks more meaningful kovi date and i'm defining this as date time daily confirmed cases as integer daily deaths as small and country as where care of 25 coved flag and then total loss all right let me just give a semicolon and first i should go to this particular database so i can just select kovid19 database right and click on execute and you see it says command completed successfully so here let me just refresh it and you see here we have created a new table that is coveted stats right and please note that by default it has created the schema name as dbu because if you don't give a schema name it will create a table with default schema name which is dbu in case of this particular free version of sql server in the previous example you see we created this table that is covered stats in the same way i have already prepared a query to create another table that is item header table here you can see that we have item id which is integer field we have item name which is varchar of 50 we have item order date which is date time field we have item ship date which is also date time field and we have item amount which is money field now you see that at this moment all these columns are nullable in nature that means these columns can contain null values because we have not added any constraint that will make sure that any of the column cannot contain null values so what i mean is let's say you want to make sure that a particular column should not contain null values then in that case you have to add a constraint called not null so let's say if i just want to make sure that item order date and item ship date should not contain null values then i can add a constraint called not null in both the columns so here i can just give not null and i can also add the same constraint here so this will make sure that this particular column and this particular column will not have null values similarly you can also add a constraint that will make a particular column unique for that you have to give unique constraint so unique constraint make sure that each record in a particular column of a table is unique so the values in a column will always be different so let's say for item name if i want to make sure that it contains a unique values every time so i can just give here keyword called unique so here you see i have added unique constraint in the same way if you want to uniquely identify each record then you can use primary key and primary key will also make sure that it will not allow duplicate values in a column and it will also not allow null values now please note that let's say if i want to make item id as primary key then i can just give here primary key and another important point to note here is that do not get confused between primary key and a unique constraint a primary key will never have null values while a unique key can have one null value in a column also you can add any number of unique constraints for example you can also add unique consent here you can also add unique consent here based on your requirement but there will be only one primary key constraint in a table and the main idea to use primary key is to maintain the entity integrity and the entity integrity helps to relate multiple tables by relating them through some columns so in this case you see if i just execute this let me execute this and you see this is successful if i just come here and refresh it now you see we have another table that is items header and if i expand the column you see here it gives pk that is primary key and integer not null for item name you see it is wirecare of 50 and it is null and you see item order date will be date time not null so here we have added not null similarly for item ship date you see here it is not null so it is showing here as not null and item amount is showing as money field but it is showing as null because we have not added any not null constraint here now what i have done is i have created another query that will create another table so i have prepared this query as well and let's see if i just add this query you see if i just add this query and let me just pull it down and let me just reduce the font a bit and this time i am going to create a table called item detail which contains item id item detail id item due date item auto quantity received quantity ship date and item amount and please note that item id is integer detail id is integer due date is date time order quantity is small end and we have received quantity which is also small end item ship date is date time while item amount is money field now please note here that at this moment we are not having any consent and let us try to add consent on this particular table for few columns for example let's say for item due date if i just say not null then i can just give not null constraint here similarly let's say for any other column for example item auto quantity item received quantity and let's say item ship date i can add not null as well right and and you can also make a particular column as primary key so let's say i want to make item id and detail id as primary key so you see here if you want to keep multiple columns in a primary key then what you can do is you can just give a comma here and you can just give your constraint and you can just give the name of the constraint so let's say if i just give item detail pk that is name of the table and attend i am giving a suffix as pk so this is the constraint name for primary key and i'll just give primary key and then i will just pass the columns for primary key so let's say i want item id so i'll just give here item id comma item detail id and both of these columns should be part of primary key and please note there should be only one primary key in the table and in this case item id along with item detail id so this particular combination is primary key and please note in this case let's say if i want to reference the item id of this particular table that is item detail table to item id of item header table then i can just add a foreign key so in that case item header will be parent table and item detail will be child table so if i just add a comma here and if i just say constraint and if i just give the name of the table that is item detail and for constraint that is foreign key constraint normally i used to give f k as suffix and i will just say foreign key and let's say i want to relate item id so i'll just give here item id references so for that you see references means that it is linked to this particular table so i have to give the name of the table that is item header and i have to give the column to which it is linked so i'm giving here item id all right so if you see here we have already created this table right now this is my parent table and i'm going to create the child table for this particular parent so this is one of the child table like this there can be multiple child table for the same parent table so in this case if i just give create table item detail and all these columns and if i just give constraint constraint name primary key and i'm making item id and item detail id as my primary key and i am adding another constraint that has item detail fk as foreign key and item id of this particular table is linked to item header and in item header item id is linked so for linking i'm using references and in this case this will act like one of the child table of this particular parent table right so this is called as entity relationship that means you see here if you have a record in this particular table let's say parent table and if you want to insert a record in child table then you have to make sure that that particular record should already exist in the item header table that is parent table for item id column so let's create this table so if i just select this and if i click on execute now you see here we have created this table right and now if i just come here let me just refresh it and you see we have item header as well as item detail now if i just expand item detail go to columns now you see here we have item id and here it says pk comma fk for item detail it says pk comma end comma not null that means item id and item detail id is primary key that means both the combination is one primary key and we have foreign key as item id right and this foreign key is linked with item id of item header table so if you just go here to item header now you see here you are able to see that it is pk and if you see a key symbol which is pointing towards left side then it is primary key and if you just go to item detail you see here that in this case also we have keys and both these keys are pointing towards left side so this is primary key item id with item detail id and you can also go to keys and here you can see that we have two keys that is item detail pk so this is my primary key and for foreign key you see that it will also have a key button which will point towards the right side so we have item detail fk in the same way you can go to item header table go to keys and you see we have one primary key and here we did not add a particular primary key constraint name so by default sql server has created a name like this and for unique constraint it has created a particular unique constraint name as well so in this way you can create tables using particular constraints and here we have added not tell constraint unique constraint primary key constraint and foreign so that's it in this video lesson take care bye okay if you just give select star from table name that is go with stats you will notice that at this moment the output is empty so you see the output is empty because we have not inserted any records into table to insert record into table we have to use insert statement so you see this is the syntax of insert statement that is insert into table name values and then you have to give list of values so for example just to save time i have already created an insert statement you see here let's say i want to add this particular record to this table that is you will notice that kovid id is auto increment field so that is the reason it will automatically created by the system so you see if i just right click here go to properties and you see here in identity column it is showing as true and it is showing that it is getting incremented by three and starting value is one right so that is the reason you don't have to pass any value for covid id and it is automatically created by the system so i'll just pass value for first code date so this is my date and for daily confirmed cases i have added a value of 1000 for daily deaths i have added a value of 10 for country since it is string field i have given in single quotes and the name is usa let's suppose and code flag i have given as one so either it will accept 0 1 or null value and total loss i have given this particular value that is 1 million let's try to insert this so i've just selected this and i'll just click on execute button and you see that it says one row affected that means it has created one record in this particular table so let us do one thing let me just run this and now you see that sql server assigned kovid id1 and this is the date this is the daily confirmed cases and so on now please note if i just come down you see here that if you just want to insert records for few columns or let's say if you want to just pass the list of columns then you can just give insert into table name list of columns and then list of values and in this case either you can pass all the columns of the table or you can pass few columns of the table but please note you cannot pass kovid id here because it is auto increment field and sql server will automatically assign a value for that so let's take an example of this as well so i have already created a sql statement and you see if i just give insert into dbu dot kovid stats and i have just passed coe date and country so i'm passing only these two columns and i'm passing the value of first column that is kovi date and country i am passing as tuvalu right let me just select this and if i execute you see it says one row affected let me just come back here and give my select statement this time you see i have automatically covered id4 because previous coved id was one and it added three to this particular number and it became four kovi date is same which i have given and you see for country it is showing as tuvalu and please note that daily confirmed cases daily deaths ko with flag and total loss is showing as null because i did not pass any value for these particular columns and let's come down so we have already run these statements right and if i just show you again let's come down and suppose you want to add multiple records in a single time then what you can do is you can just give something like this that is insert into table name and list of columns so here i have given six columns that is covid date daily confirmed cases daily deaths country covered flag and i have given total loss and you see i am providing the kovi date then i am giving daily confirmed cases daily deaths country bit i mean the covet flag and then total loss after that you see i have just given a continuation that is i have given a comma and then i have given the value for second record similarly after the comma i am giving a value for the third record so you see in this case i am passing this particular date along with these information that is daily confirmed cases are 0 daily debts as 0 country as below code flag as 0 and total loss as 5 000 and if i just select this if i execute now you see it says three rows affected so at a single time i have added three records into my table right so let me just select star from dbo dot co with stats and now you see i have added a total of five records into this table so this is how you can insert record into table so how do you update or modify an existing record let's say i'm into record number two that is for kovid id4 we see that there are a few null values you see here for four columns we have null values and let's say i want to modify the null values to a particular value for covidie d4 and let's say for covid flag and total loss i will set the null values to different values for that i can give update statement so you see here the syntax of update statement is update table name list of columns with their values for which you want to modify to a new value and then there is optional where condition so if you don't give where condition it will update for all the rows so you see if i just skip this where clause what happens is you are trying to say update table name set column one for example let's say for column one you have let's say covered flag so you can just say kovid flag equal to let's say 0 and if you want to change or modify the value of total loss you can just say here total loss equal to a particular value let's say 100 and if i don't give a rare condition what happens is it will update all the records of these two columns and here of course i have to change the table name so it is kovid stats right so either you can just give here dbo dot co with stats or you can just give kovid stats because by default the schema name it will take is dbu so please make sure that when you give update statement you should be very cautious because if you don't give a rare condition it will update the records for all the rows so in this case let's say i want to only update for kovid id 4 then i can just give here where kovid id equal to 4 and if i execute this now you see it says one row affected let me just give select start from covet stats and let me just execute this now you see for kovid id4 the coveted flag is zero and then total loss is hundred previously these two columns were having null values right so this is how you can use update statement hey people welcome back once you have created a table and if you want to change the structure of the table then in that particular case you can use alter statement so auto statement can be used to add a column or list of column to a table using alt statement you can delete a column or list of columns from a table and you can also modify the property of a column let's say like data type of the column as well so let's try to code some examples of alter statement if i just take covered stats table and if i just remove this statement and let's say i want to add a new column to this particular table so you see here we have these columns at this point of time and let's say that i want to add a column with the name as continent and let's say that it should be character of length 12. so for that i can just write alter table and then table name that is covered stats and i have to give add column name that is continent and it is character of let's say 12 length so the statement here which i have written is altered table table name so if you want to change the structure of the existing table then you have to use alter table statement so here it is alter table code stats add column name data type so if i execute this you see it is successful and let's go to columns refresh it and now you see here we have added this particular column that is continent now let's assume that once you have added the column and you are not happy with this particular data type or for any reason you want to change this particular data type then using the same alt table statement you can do that as well so what i mean is copy this and i can just say alter table code stats and here i have to give alter column continent and now let's say i want to change the character to variable character so i can just give you where care of let's say 15 so what i'm trying to do here is i'm trying to change this table that is code stats table for which i have given alter table statement and i'm going to change this particular column that is continent for which i am giving order column column name and then data type so let me just execute this and it is successful so you see here at this moment it is character and once i refresh it now you see here it is where care of 15 right in the same way you can drop a particular column as well so for that i can just give alter table table name that is code stats and i have to give drop column and name of the column so let's say i want to drop this particular column i can give here and if you want to drop multiple columns then you have to give a comma and that particular column let's say column two again if i want to drop another column let's say column three i can just give something like this but at this moment i just want to delete continent column so i'll just select the statement and click on execute and now if i just refresh it now you see that this particular column is gone if you want to delete the records from table then you have to use delete statement so what i mean is let's say i want to remove a record from the table where country is brazil and kovid flag is one for that i have to give delete from table name so it is code stats and here i have to give where condition that is where country equals brazil and covid flag equals 1 and if i execute the statement now you see it says one row affected and if i give select star from code stats let me just execute this and you see that particular record is deleted similarly if you want to delete all the records from table then you can simply give something like this you can just give delete from kovit stats and if you just omit the where condition that means you are saying delete everything from this particular table so you should be very careful while using delete from table statement similarly let's say if you want to delete let's say only top two records or top three records from a table then you can just give your delete top to from table name and what you have to do is you have to surround this two within parenthesis so if you just run this statement you will delete the top two records from this particular table so if i just execute the statement now let me just give select and you see that the top two records are deleted similarly i can also give something in percent for example let's say i want to delete top 10 percent then i can just give delete top 10 percent from code stats and if you do that it will delete the top 10 records from this particular table let's say you want to delete your table itself that means you want to permanently delete your table then in that case you can use drop statement so if i want to delete kovid stats table i can just give here drop table and then table name that is code stats and if i execute this you see it says command completed successfully and now if i just come here and if i just refresh this now you see that we don't have the table that is covered stats all right with this we have successfully completed this course and we have learned a lot starting from installation and then importing the databases we have run select commands then we have learned so many functions and we created tables databases and so on so with this if you have any doubt please leave your comments or leave your questions on the comment section and i will try my best to answer you and if you like the course please make sure that you like the video share with other people and most importantly please subscribe to our youtube channel so at last i would like to have a big big big thank you to all of you to keeping your trust and taking this course take care bye

Channel: TutorialBrain

Views: 46,674

Keywords: Microsoft SQL Server Crash course, SQL Server crash course, SQL Crash course using SQL Server, microsoft SQL Server management studio tutorial, microsoft sql server tutorial, microsoft sql server installation, microsoft sql server 2019 tutorial, sql full course, sql tutorial for beginners, SQL Queries, SQL Server tutorial, SQL Server, MS SQL, MS SQL Server, MS SQL Server tutorial, SQL Basics for beginners, SQL Server from Beginner to expert, SQL from Beginner to advanced, sql

Id: lo80Q8C0nXk

Length: 127min 46sec (7666 seconds)

Published: Wed Jan 20 2021