Transcript for:
Essential Topics in SQL Course

I'm Amit diwan the trainer of this SQL course I have taught more than 20 Technologies including programming languages and databases rdbms is the basis for SQL that is SQL stores data in rdbms now what is rdbms rdbms stands for relational database management system which stores data in a table form that is rows and columns form this SQL course basically covers Microsoft SQL server and its concept for beginners if you want to learn SQL from the beginning itself then this course is for you we have used the free and open source Microsoft SQL Server Express Edition tool on SQL and its Concepts all the database queries and examples are provided with the Hands-On live running experience in this course we have covered the following lessons with more than 100 plus live running examples so guys let us now begin with the first lesson that is SQL introduction and features let's start in this video we will learn what is SQL its introduction and overview so let's begin at first we need to understand what is rdbms so rdbms is relational database management system to understand rdpms here in you can see we have a table so in style root we are creating these tables with rows and columns right so this is what we call rdbms that is a relational model relational means relation between rows and columns okay these rows and columns so this is what we have shown here rdbms it's a form of a table that is a table has rows and columns and a database has group of tables so that means a relation a relation of rows and columns is called table and this is what we call rdbms so relational database management system okay this is the basis for SQL so now you would be wondering the difference between rdbms and dbms rdbms is just an advanced form of dbms okay so it is having relational dbms that means the relation between tables relation between rows and columns so rdbms is the basis for SQL MySQL or MySQL Oracle and other related databases so rdbms I have told you before it stores data in the form of tables and tables is having rows and columns herein we have four rows that means rows are always horizontal entry okay you can see and columns we are having five so columns are vertical entries okay so this is what we have shown here now what is SQL SQL is a structured query language and rdbms is the basis for SQL that I have told before that means it will store the data in rdbms in rdbm is form that is in the form of rows and columns okay and it is governed under ANSI that is a medical National Standard Institute as well as International Organization for standardization ISO so these two rdbms have been developed by Microsoft so we'll be covering these two in SQL okay so this is the current version it will it is used to create and maintain database to create tables database and maintain it within that Microsoft also provide ssms that is SQL Server management Studio here it is we will be using this to work on SQL you can say it is a UI for SQL that will allow you to easily create database tables work on operations Clauses operators and other stuff in SQL hello guys in this video we will learn about the difference between SQL and nosql so we will cover this difference with examples let's begin so guys SQL follows the relational model that is the rdbms model that old school way of creating tables with rows and columns so this is what SQL supports and nosql is completely different it supports various formats like document and storing data in the form of key values graph nodes and other stuff so SQL came in 1970 so you can say more than 50 years it's been around and no SQL just came in late 2000 you can say examples SQL is basically rdbms so all the databases related to rdbms will get covered under SQL MySQL Microsoft SQL Server Oracle all are part of rdbms and all these are based on rdbms recording nosql the biggest example one of the most trending databases right now is mongodb which is document based with that we can also store data in the form of key values using the radius database using white column using Cassandra and hbase so the usage here SQL is generally used to store data in the form of tables rows and columns let's see an example at first SQL you can see the SQL example we have shown MySQL here as I told you before SQL saves data in the form of rows and columns it's based on rdbms that means based on relational model relation between rows and columns in a table here it is you can see the relation between rows and columns so these are our columns one two three four five employee ID first name last name employee Edge Employee Zone and these four are rows okay so as I told you tables are having rows and columns and a database is having a collection of tables so this is what we call SQL that is as I told you tables with fixed rows and columns is what we call a model for SQL that is a relational model okay now talking about mongodb I told you before it saves data in the form of documents key value pair that is completely different from SQL so this is the document based mongodb database we have four columns here ID Department name department manager and Department rank so you can see the database here under nosql under mongodb documents have fields and collections is having documents like tables is having rows and columns in this collections I have documents and Fields and database is a group of collections in the same way in SQL database is a group of tables and this works for mongodb but it saves the data in the form of in document based form or key value form okay so this is what we have shown here document based key value form white column form and graph form in nosql so completely different from our old school conventional SQL schemas it is rigid and it is flexible no SQL is a flexible database you can say you can store the data in all these forms different forms document key value graph multi-record asset transactions so it is supported by SQL here in nosql doesn't support it but mongodb will still support it so asset transaction are basically an acronym for atomicity consistency reliability and durability these are basically database operations and this asset acid is known as asset Properties or you can say asset transactions so guys in this video we saw how we can differentiate SQL and nosql so for SQL the biggest example is Microsoft SQL and for nosql mongodb is our example so guys thank you for watching the video in this video we will see how we can easily download and install Microsoft SQL Server 2022 on Windows let's see at first go to the web browser I am using Chrome you can use any web browser on Google type Microsoft SQL Server and press enter on pressing enter you can see it is directly showing the Microsoft website because SQL Server is owned by Microsoft so click here now the official website is visible click on downloads on clicking downloads go below here in the two versions are visible for SQL Server 2022 the developer in Express okay so I'll be going for the express because it is a free edition okay it is basically for production environment this is for non-production environment so I'll be going for the following Express Edition click on download now download started the installer will download here it is 4mp only double click on it to install minimize now the setup will begin now we will select the installation type basic custom and download Media this will allow you to download the file so that you can install them later this is for custom that means what we are installing will be shown first and this is for basic since we are going for beginner installation so I will be going for the basic step after clicking the license terms is visible click on accept now the installation location is visible it will get installed in the program files which is fine you can change the location from here but I'll keep the default 278 MB it will take only click on install now it will download the package and install it now the installation started so guys now you can see the installation completed same is written here the first one is the connection string this is used to connect SQL with other languages with programming languages here is the log folder so I hope you know what is the log folder here in uh the report regarding starting of SQL as well as errors will get reported here this is the installation media folder this is the resource folder so we can directly click on connect now here if the following is visible one row is effective that means it is successfully installed minimize now it is allowing us to click on install ssms ssms is basically SQL Server management Studio okay so this is like the UI version of SQL which makes it uh easier for you to work around SQL Server so we will install this also what you need to do is just click on install ssms this is also owned by Microsoft you can see the link of the Microsoft so here is the download link visible download ssms the current one click on it so it will download 621 MB so it is downloading so it got downloaded the XE file double click on it to install minimize so guys the setup started I'll just minimize this so the setup for SQL Server management studio is visible it will get installed here you can change the location from here but keep the default which is fine click install now it will load the packages and install guys you can see we have successfully installed the madness studio also click on close in this video we will see how we can easily create a new database using Microsoft SQL Server go to start type SQL SQL Server management and here it is the management Studio 19 is visible click on open now you can see the SQL Server management should be opened up you can directly click on connect here click connect it opened up okay now the database section is visible let us quickly create a database go to new query and click on it okay now you can change it to let's say 200 percent so that it's visible now I'll type a command SQL command create database to create a database and let's say the database name is Amit DB so I added the same this is the command create database and I added the database name you can add any name okay I'll just select it and click on execute and this will create our first database commands completed successfully now I'll click on refresh and under this you can see the database is visible in this video we will see how we can easily create a new table in SQL Server so here in right now we created a new database using Create database database name here in our database name is Amit DB in the object Explorer you can see under databases we have amiddb database so guys now we need to create a new table we will be creating an employee table for that guys first we need to use Amit DB and enter the database we typed use space database name selected it and now click on execute you can see command completed successfully that means we have entered the database Ambit DB now we can easily create a new table so guys to create a new table in SQL Server we use the create table command so let's write create table then the table name so let's say the table name is employee and open the brackets close it and use semicolon inside this we need to add our columns that is which columns we need to add with its type so at first I'll be adding ID will be int right so it's fine comma press enter EMP name that would be employee name and the same will be in where care let's add the value comma then comes City where can only 255 comma then the last we can add a salary for our employees which will be in end for obvious reasons so everything looks fine guys now we have added four columns I will select it we could have also added primary key and other another constraints in it that means not null check constant default but I'll explain all these in a new video so right now I am just creating a basic table I'll select this and click on execute after clicking execute you can see commands completed successfully that means we have successfully created a table now guys on the left object Explorer we need to click on refresh so that the same new command we executed gets executed here itself now I'll just click on amidb on clicking I'll click on tables now you can see our table created successfully here are the columns the same four columns so guys in this video we saw how we can easily create a new database and table in SQL Server in this way you can create a new table using Create table command so guys thank you for watching the video in this video we will see how we can insert records into a table in SQL Server so here in first we created amidb database using the following command then we created a table employee you can see a database we created amid DB here it is under that we created employee table the employee table we created is having four Fields the first one isn't and the last one is in that is in ID and salary is int integer and employee name and CTR for are being given where cat types data type now let us insert a record for inserting records we'll be using insert into command let's write so here we wrote insert into employee insert into employee is a table name as you can see here now let us mention the fields one by one ID when you will type it will automatically show under autocomplete press enter City select and press enter salary Center so it's easy now write values and insert records one by one at first we will insert only a single row so for ID let us write 1 let us enter in quotes employee name let us write Tom City let us write a sample ABC salary let us write any value in it okay so now let's find semicolon now we will select this and click on execute to enter the first row in our employee table here you can see the messages drag it you can see one row affected completion time that means we have successfully added records in the table now let us see the record we have added using the select Star Command select star from table name table name is employee semicolon select it and execute after executing in the results section you can see we successfully added one record in the table okay now for another record let us implement the command again let us add the second record Emma okay now execute it select it click on execute Wonder reflected now let us mention the command again select star from employee that's it select it and execute that's it now you can see we have inserted two records ID employee name City and salary in this way guys you can insert records in your Microsoft SQL Server table using the insert into command in this video we will see how we can easily select data from a database that means if you want to fetch records from a table in a database you can easily do it using the select statement so right now we have a database Amit DB within that we created employee table now let us see how we can fetch all the records of the employee table it's very easy so at first we will enter the midb database using the use command click on execute query executed successfully that means we have entered our bdb database now we need to fetch all the records from the employee table so we will use select star from employee semicolon select is our statement so employees are table now I'll select it and click on execute on clicking execute you can see all the records from our employee table are visible now this is how we faced all the records now let's say you need to fetch the records for specific columns that means let's say you need to only get the ID and employee name for that you select mention the name of the columns that is Select ID comma EMP name from table name employee that's it select it and click on execute so herein you can see only ID and EMP name we faced from the employee table this is how you can use the select command so guys in this video we saw how we can use a select statement on Microsoft SQL Server we faced all the records and we also saw how we can fetch specific column records so guys thank you for watching the video in this video we will see how we can fetch the distinct records in Ms SQL Server so right now we have amiddb database that's why we have use Amit DB command so that we can enter the amidb database our database within that we have created an employee table with some records now let's say first we will fetch all the records of the employee table using the I hope you know select star from employee semicolon now selected we saw the same in the previous video click on execute Below on the results you can see these are our records but guys what we want we need to fetch the distinct records herein you can see the city of some of the employees are same so so if we want only distinct that means different records we need to use the distinct command let's say select distinct so enter the column name which is having duplicate records so that so that we can display the record without duplicacy so it is City Select distinct City from from employee semicolon now let us select this and click on execute after clicking execute only city will be visible City column but the repetitive values won't be visible here repetitive values are a b c and P Q R you can see it is repeating here pqr is repeating here and ABC is repeating here I'll click on execute here you can see the distinct values of city is visible because we use the distinct command so guys in this video we saw how we can use the select distinct to display only distinct different records in this video we will see how we can filter records based on a condition using where clause right now we have our amidb database we have entered it using the following command use space database name after clicking execute we entered it inside that we have a table employee table so let us first see all the records of the table using the select star from table name table name is employee that's it semicolon select this and click on execute on clicking execute you can see we have four columns ID EMP name CT and salary so let's say we need to fetch records on the basis of some condition we want to fetch the employee name with with salary 8000 so let's see how herein at first display only the EMP name right select EMP name from employee now let us write the condition or we can also write like this where what I said where the salary is is equal to where the salary is equal to 8000 so here in only the employee name will be visible for salary 8000 that means Mr will be displayed select this and click on execute now you can see only image displayed now let us modify the query now select star from employee where salary is equal to 8000 that means all the records of the employee name Emma will be visible because we have set the condition as salary 8000. so I'll select it execute here in you can see only the record only the row of the employee name Mi is visible complete row complete record because we have set the condition as salary 8000. now let us display this again you can see now what we need to fetch let's say we need to fetch a text record because right now we fetch the numeric record let's say I want the record of the employee with name as Jenny so let's say where name as Jenny that means EMP name is equal to Jenny okay but we need to add the codes for texting for string because the type was worker here before that we went for salary which wasn't so so no courts were required in that so everything looks fine select click on execute now only Jenny records really visible employee name Jenny execute and you can see all the records of the employee name jailing employee name Jenny is visible because we use the wear clause so guys now we can move further and change the condition before that let us display the records of the employee table again now let's say I want the records of employees whose salary is greater than 7000 so let's say what I need to write here the field name salary is greater than what I said 7000 I can also set equal to here the greater than equal to operator okay that means salary greater than equal to 7000 so the output should be greater than equal to 7000 means equal to 7000 also the output should be Tom Emma David and Henry let's see execute here and you can see Tom ever David Henry because salaries of these employees are more than are greater than and equal to 7000. so guys thank you for watching the video in this video we will see how we can easily order records in SQL Server so we use the order by keyword to to sort the records in ascending or descending order the default for order by keyword is sending order so ascending order means arranging from smallest to largest like 1 2 3 4 5 or you can say for alphabets A B C D E F that is from smallest to largest so herein we have our amidb database as you can see here amidb we have entered it using the use command here it is amidbit database inside that we have a table employee table so we have inserted some records in the employee table let us see the records first I hope you know to display all the records we use select star from table name semicolon and just select it and click on execute so herein you can see guys the records of the employee table are visible Below in output so we have four Fields ID employee name city as well as employee salary so let's say we need to order the records in ascending order on the basis of salary so which command we will use I told you order by command so let's say we need to display all the records after sorting so herein I'll type select star from table name that is employee press enter type the command order by and we need to arrange it in ascending order so that's the default so just keep as it is and we need to arrange it according to salary so I'll just mention salary on typing you can see the salary is visible here just select it and semicolon so the results all the results will be visible on the basis of salary arranged in ascending order that is ascending order means from smallest to largest select this execute herein you can see the lease salary is on the top then comes the next one and the end the last one will be the will be the employee with the highest salary okay guys so now let's say you need to you need to do the reverse for that type desc for descending order that is from largest to smallest now it will be opposite Henry will be on the top just select it execute now herein you can see guys Henry is on the top and we have arranged it according to descending order that is from largest to smallest 9500 then 8 000 then 7500 in the same so guys we can also order according to different columns several columns why why we need it because let's say city is repeating here pqr city is for two employees and ABC cities for two employees so let's say we are arranging accordingly the city according to City so we can set multiple columns so that we are able to solve this multiple City order issue according to salary let's see so again guys we have used city as well as the salary in order by let's see what will happen selected and execute now here you can see we have ordered in ascending order because we haven't mentioned anything so the default is ascending so it has arranged it accordingly City and salary in case of repeated Value City repeated it has arranged according to salary so the issue is no result so guys in this video we saw how we can easily work with order by keyword to sort the records in this video we will see how we can easily work with the SQL and operator the and operator we used to filter records based on more than one condition so for and you can say you can display record if all the conditions separated by the and operator are true that means all the conditions fulfill all the conditions satisfy and all of them are true so guys herein we have amid DB database within that we created a table employee table the following are the Columns of the employee table now let us see the records of the employee table first select star from employee from table name semicolon select execute on executing let us see the output herein you can see ID employee name City and salary columns so let us use the and operator and include more than one condition separated by and operator so herein we will display employee records whose city is ABC and salary is 7000 so this will allow us to fetch specific records so we'll be including both the conditions that will display the employee name Tom let's see select star from employee let me write it again now remove the semicolon now I'll add the conditions using the wear Clause where City I told you is ABC and we are using the end operator and salary is equal to specific salary seven thousand that's it add semicolon and let's execute and see the result execute here in you can see we are able to fetch the salary with 7000 and city with ABC for the employee name Tom so we could have also set the values for let's say I'll execute it again to display all the records so let's say I want to display the specific employee name Will with the city ABC so the city is ABC only but the seconds condition also should satisfy so herein let's say I want to display six seven double zero so what I will do I'll just mention less than and everything looks fine select it click on execute here in you can see the will employ name is visible because the salary is less than 7000 now so guys in this video we saw how we can easily execute the and operator in SQL Server it satisfies both the conditions separated by the and operator in this video we will see how we can work with our operator so the or operator is used to filter records based on more than one condition it displays the record if any of the conditions separated by the or operator is true any of the condition not both both the conditions are for and operator and only a single condition should be true for or operator so let us see an example we have a mid DB database we have entered in it within that guys we have our table employee table and within that we have four columns in the employee table let us see the records of the employee table we already inserted it so I hope you know to display the records use select star from table name our table name is employee so here it is selected and click on execute so we have executed the query here and you can see we have employee ID employee name City and salary so herein we'll be implementing the or operator so let's say we need to fetch all the records of the employee whose name is either Jenny or Henry let's say so so I'll mention the command press enter use the where clause EMP name is equal to I said Jenny or EMP name is equal to Henry so both the records will get will get fixed because both the records are true even if one was true it would have returned the records without any error Henry semicolon now I'll select it execute you can see records for Jenny and Henry and visible now guys I'll just copy this command again and let's say I'll mention it Steve so there is no employee with the name Steve if you remember I'll show the records again execute you can see there is no employee with the name Steve so still this command will execute because we are having or operator that means only one condition should satisfy execute herein you can see only one condition satisfied Jenny second failed but there is no error because we are using or operator this is the benefit of using an or operator so guys in this video we saw how we can work with our operator in SQL Server in this video we will see how we can work with the not operator in SQL Server so the not operator is used to to display a record if the condition is not true mind it not true okay so let us see an example we have Amit DB database here as you can see here in SQL Server database we have employee table with four Fields as you can see here we already inserted records in the employee table let us see them I hope you know to display the records from a table use select star star star is used to display all the records from table name our table is employee as you can see here employee now I'll select this and click on execute to display all the records herein you can see query executed successfully and here are all the records so I told you employee ID employee name employee City and employee salary now guys what we need to do we need to implement the not operator so to implement the not operator let's say we want to fetch all the records of the employee wherein salary is not 8 000 so except Emma all the records will get displayed because we are using not so let's see I'll copy this again select star from employee we are not salary is equal to eight thousand now I'll select it execute you can see except 8000 salary record every record is visible with that guys for numeric you can remove this these single quotes and when I'll execute again it won't show any error so this is the usage of not operator we just removed the following record from the result that is the employee with salary 8000 rest all the employee records are visible so guys in this video we saw how we can work with the not operator in SQL Server in this video we will see how we can work with the SQL in operator so in operator allows you to set multiple values in a where Clause so let's say you want specific values from a table so you can use the in operator we will see an example here so you can consider it as a shorthand for multiple or conditions so let us see an example here in guys we have amidb database under databases on midb database within that we have tables we have a single table only employee let us see the records of the table using select star before that guys we have entered the midb database using u-space database name that is amidb database let us quickly see the records of the employee table select it and click on execute herein you can see the output we have ID employee name City and salary now let's say you want all the records of the employees whose city is zyw and fgh you can easily set it using multiple conditions that is SQL in let's see how since we want all the records so we will use select star from employee based on a condition right so herein we will write the condition wherein the column name City now use the in escalin and within that add the values multiple values that means it was z y w and fgh here it is only these two will get printed Jenny and David semicolon and run it and you can see I told you it's Jenny and David is visible because the city was zyw and fgh let us move further and see another example so I'll just copy it to explain it easily now let's say this is our table we want to fetch the records of the employee table based on employee name so I'll mention so I'll mention EMP name under this now we need to mention the names so let's say I want the record only for let's say Emma Henry and will so I'll mention Emma Henry and will that's it okay I'll select it and click on execute now you can see only Emma Henry and will employment records are visible we can also use its opposite that is after n we can use not in so let's say I'll just copy this and paste now I'll just mention not in what will happen except Emma Henry and will all the records will be visible let us first display all the records here it is now you can see except Emma Henry Henry and will all will be visible that means Tom Jenny and David will be visible because we have used Note 10 click on execute you can see Tom Jenny and David is visible so guys this is how you can use the in and not in operator we have used the in operator to basically specify multiple values in a wear Clause we mentioned multiple values here you can see so guys thank you for watching the video in this video we will see how we can use the between operator so between itself says that in a Range so you can set a range like between value 1 and value 2 these values can be numbers like one two three text it can be any text and dates between dates you can get records so let us see an example quickly so here in guys we have databases under that we have our midb database within that I am having my table employee table so we have entered the database using the use command use database name that means use amidb now let us display the records of the employee table I hope you know select star is used to display all the records and from is the table name that means employee click on execute on clicking execute guys you can see below all the records of the employee table are visible okay now let's say we need to fetch the employee Name Records that is the entire employee records with salary between seven thousand and nine thousand okay so for that let's mention the following again because you want all the records okay now we will add the where Clause because you want to set a condition we will use between now wherein salary between I told you let's say between seven thousand to nine thousand so between let's say seven thousand nine thousand it will also include both the value that is seven thousand nine thousand because it is inclusive okay semicolon and we will select it after that I'll click on execute and only those records whose salary is between seven thousand nine thousand including both will be visible here you can see here is visible here now guys let us also see not between I'll just copy this and if you want let's say these are the records if you want rest of the records you can just mention not between so I'll just show you the records again okay so not between seven thousand and nine thousand that means except seven thousand to nine thousand everything will be visible that means Jenny records as well as Henry records will be visible with that will also will be visible because it's less than seven thousand because we have used not between selected and click on execute herein you can see I told you Jenny hendrin will be visible if you will go for between again then rest of the records will be visible so I hope you know the difference between between and not between is just the negation of between but between now move further here in we can also use the text values so we will use between now let's say I'll mention the text values so these were our records so herein let's say let's say between David and so let's say we are displaying between David and Jenny so David and Jenny means alphabetically D and J between D and J we have e Emma Henry okay and herein we will mention EMP name so we need to also order it so order by let's say EMP name so we'll see what will be visible select it and click on execute herein you can see between David and Jenny firstly David and Jenny will be visible because it is inclusive I told you before and between that alphabetically mine Henry is also visible so this is how you can use text values it's so simple so guys in this video so how we can work with between and not between operator in SQL in this video we will learn about SQL like operator so the like operator is used in a where clause for a specific pattern in a column so you can see specified pattern let's say you have employee name Tom Jack and Tim so you want only the employee Name Records with name starting with t so you can use like operator in this way you can also work around zero one or multiple characters or even a single character for that guys percentage sign is provided by SQL this is for 0 1 or multiple characters in that way you can also use underscore for let's say for let's say one or you can say Single Character so we will see all these examples in this video okay so let us see so here and you can see guys we have amid DB database under databases within that we have employee table so we have entered the amid DB database using the use command use space database name that is amidb now let's see the records of the table so that we can operate on the like operator our table was employed so using select star we can get all the records select and click on execute here in you can see we have all the records I told you before that using like operator we can easily fetch the records using a specified pattern so let's say here in we have employee name Tom Emma Jenny David Henry and will and cities with the following so let's say we want the employee records with City starting with a only okay for that guys let us right select star because we want all the records we are using star we won't be using semicolon again because you want to add a condition where City it the column name is city right City like the like operator and we want starting with a capital A right that is ABC cities will be visible I'll add percentage here because we just saw the usage of percentage so that we can easily represent 0 1 or multiple characters here we have mentioned a single character it's fine now semicolon and we will run it execute herein you can see only the city starting with a is visible now guys let us show the execute the command again and here it is now let's say you want employee name that ends with I so the volume will be visible let's say I'll copy this again herein what we want guys we are City so there was City column now we want PMP name column so I'll mention EMP name that's it like we want it in the end that means we want the employee Name Records ending with i that's it so herein we will just use the percentage before and mention I and after executing it only the third record will be visible selected and click on execute you can see only Jenny record is visible in this way guys we can work with the percentage now let us guide display the records again now let's say we want the employee records with employee name that are having e e in the second position so Jenny is having e in the second position and Henry is having e in the second position so we will use a like operator use select star from employee because you want to display all the records now the condition part here in we will be using both the percentage as well as underscore where EMP name like So within this we will add the condition first we will add underscore because the first character is fixed whatever it is and the second character should always be e so I'll mention e here after that there can be any number of characters so I'll add percentage that's it now both Jenny because the second character is e Jenny and Henry will get fetched select it and click on execute herein you can see Jenny and Henry got fetched easily now guys let us see the light condition again let us first display the records okay so herein we will face the employee names that does not start with E so herein only Emma won't be visible and rest all of them will be visible so let us see I'll mention the condition again EMP name not like use not like E I told e mentioned percentage because there can be any number of character after that but the first character is fixed which we have mentioned as e semicolon now I accept Emma every record will be visible execute here you can see except Emma every record is visible in this way we can also work around the not like operator okay guys so we have worked around the percentage as well as underscore you can work around more examples on your own so guys in this video we saw how we can work with the like and not like operator in SQL in this video we will see how we can work with the max function in SQL to return the largest value of the selected column so let's say you have a table employee table and you want to get the maximum salary from all the employees then you can use the max function in this way you can get the highest score from a number of players in a team let's say a cricket team so let's see an example herein we have amiddb database you can see here a database under database zombie DB under that we have employee table so we have entered the DB database using use amidb use space database name now what we want guys we want to display all the record of the employee table for that type the command select star from table lamp that is Select star from employee selected execute now here you can see all the records of the employee table we have ID employee name City and salary if you want to face the maximum salary you need to use the max function so let us see how type select Max within that mention the column for which you want the maximum output herein we want maximum salary so I'll write salary after that press enter and write the name of the table that is from table lamp that is from employee semicolon and let us now select this and click on execute here and you can see the maximum salary 9500 now guys you can see there is no column name so we need to fix this use an alias for this that is as so here in right let's say result you can write anything we are just giving the name to the column output result select it and click on execute so here you can see we got the maximum salary since you can see it is 9500 and our employee table is having the maximum salary of 9500 so this is how we got the maximum salary so guys in this video we saw how we can get the maximum value using the max function in SQL in this video we will see how we can easily work with the Min function in SQL so if you want to return the smallest value of a selected column you can use the Min function so let's say I want to return the minimum salary then you can use the Min function and you want to fetch the employee name specifically with the minimum salary you can use the SQL main function so let's see an example here in we have a database Amit DB here it is on a databases Amit DB under that we have employee table you can see so let us face the records of the employee table first using select star from employee here it is selected and execute here and you can see the records we have ID employee name City and salary in our employee table so what I want to fetch I want to fetch the records of an employee with the minimum salary so it will return the following Jenny with 5000 salary so let's see use the mean function I'll write select min under brackets mention the column name wherein we want to fetch the minimum so I'll mention salary that's it now mention the table name employee and that's it let us select and click on execute so again you can see it has returned the minimum salary five thousand but there is no column name if you want to add a column name you can write an alias as let's say result and select it again execute herein you can see it had displayed the column name as a result because you mentioned Alias so guys in this video we saw how we can return the minimum that is the smallest value of the selected column here in the selected salary in this video we will see how we can work with the sum function to return the total sum of a numeric column so let's say you have an employee table and in that you have employee salary as a column so we want to calculate the total salary is given you can use the sum function so let's see so guys here in we have a database mbtb database and within that we have employee table so herein we have entered zombie DB database let us see all the records of the employee table using select star from table name that is select star from employee semicolon selected and click on execute here is the result guys so you can see we have a salary column let us find the sum of these salaries it's very easy use the select again select sum and mention the column name within it that is salary because you want to get the sum of salaries from table name table is employee that's it select it and click on execute herein you can see the sum of all the salaries are visible here now let us view the records again now let's say you want the sum of salaries only for salaries above 7000. for that guys what you need to do so above 7000 let's say it's greater than equal to so it will be Tom that means 7000 plus 8000 plus 7500 plus 9500 okay so here in I'll add a condition using where wherein salary is greater than equal to 7000 that's it semicolon selected and click on execute now you can see for all those four salaries above 7000 that is greater than equal to seven thousand is thirty two thousand so guys in this way you can easily find the sum of any numeric column values of any numeric column in this video we will see how we can use the AVG function that is average function in SQL to get the average value of a numerical column so let's say we have products table in that we have price and we want the average price we can use the AVG function so herein we will see another example in which we have our employee table let's say right now we have amidb database and in that we have a table employee table and at first we will display all the records of the employee table before that we enter the mid DB database here it is now I'll display all the records of the employee table using select star from table name that is employee select it and click on execute you can see six record okay so let's say we want the average salary which is very easy to find using the AVG function let us find select AVG mention the column name in our case the column name is salary because you want average salary from table name that means employee select it and click on execute to get the average salary so the average salary is 7283 in this way guys you can get the average value of a numerical column in this case it is salary okay guys so guys hearing we can also add a clause where let's say salary is greater than six thousand now semicolon when I'll select click on execute it will only consider the records the employee salary above 6000 and will find the average that's it so guys in this video we saw how we can find the average of a numerical column we found the average of salaries in this video we will see how we can return the count of rows that matches the specified Criterion that means let's say you have an employee table and you want to get the number of employees who are having salary above 5000 let's say so you can easily do it using count function so let's see how here in let's say first we have amidb database inside we have employee table here in under tables employee table so let us fetch the records of the employee table using select star from table name that is select star from employee select it and click on execute here and Below you can see the output we have six row six Row Records and we have four columns ID employee name City and employee salary let us find the number of employees first you select the method name count under that in this you need to enter the column so this is the syntax here you need to add the column it's written count function requires one argument so I have mentioned employee name from table name I'll select it and click on execute here and you can see there were six employees let's modify this and herein I'll remove the semicolon and here in I'll set a condition wherein salary is greater than let's say uh 7000. so it will count the employees whose salary is greater than 7000 and will display the number selected and click on execute herein you can see three verified using select star from employee again select it and click on execute so you can see above 7000 there were three employees that is Emma David and Henry so three got displayed when we executed this command click on execute you can see three so guys let's say you want to display only the number of Records without mentioning any column for that right select count under column name mention star that's it from employee semicolon and that's it click on execute and it will display 6 because we were having six employees six records so herein you can see we were having six records and the same is visible because we entered count bracket star you can recheck again execute and you can see we were having six employees employee records so guys in this video we saw how we can work with the count function to count the number of Records in this video we will see how we can work with not null constraint in SQL so SQL constraints are basically used to specify rules for for the table data we are considering here the nautical constraint the nocturnal constraint enforces the column to never accept null values that is why it is not null never it will never accept null values if you will add null values after setting it as not null constraint it will show an error but always remember a color a column can always hold null values but if you will set the column as not null adding a null value will show an error okay so let us see an example here we have amid DB database you can see let us go inside the midb database and use it so I'll create a new table now so that I can easily explain you guys how to work with not null constraint how we can set it okay so let us create a table now let us create a new table create table using Create table command table name or table name is employee within that we will now add the field names with its data type and constraints let's say our first field is ID that is employee ID I'll set it as int and I'll set it as not null so ID is unique for every employee and it is a field which is a must that is an employees all employees always have ID or SSN comma let's say the next one we will add is EMP name that is employee name I'll set it as worker and I'll set it as not null again next is our City I'll set it as wirecare again okay and the last one I'll set it as salary which is end salary is in it right five thousand fifty thousand one lakh we have added the not null constant that means this won't accept any null values let me execute it it is successful okay guys here it is now now let us insert some values so for inserting we use insert into so let's say we inserted the following insert into employee that is the table name and the values for all the four Fields here it is okay for ID we have added one two here Tom Emma and all these so when I'll execute this let me execute this before so when I'll execute this here you can see we have inserted it okay so again I'll just show all the records let's start from employee execute so we have two records now okay remember that our ID and employee name is not null so we can't leave that record okay so guys in this video we saw what is a normal constraint in SQL in this video we will see what is the unique constraint in SQL and how we can work with it so the unique constraint basically ensures that all the values in a specific column is different for an example let's say the roll number of a student or the unique number appointed to an employee that means an SSN number which is unique for every employee you can have more than one unique constraints for a table but a primary key constant will be only a single per table that's the difference between primary key and unique key that is primary key and unique constant so let us now create a table and add the unique constraint in it okay so let's begin here and you can see an example we have a database Amit DB but within that we don't we don't have any table so we will create a table and Show an example for Unique constant let us first enter the database use Amit DB using the use command we have entered here now let us create a table so herein guys we have created employee table we have employee ID employee name employee City and employee salary Fields columns ID is unique every employee will have a unique ID obviously so let's say there are two employees Tom and Jack they won't be having a similar IDs right they'll be having different IDs so we need to set this as unique obviously for obvious reasons now after setting it unique that means all the values in the columns are different in the ID column is different okay now let us insert some records so let's say we added records for a single record for all the four columns that is the following at first now at first let me create the table click on execute we created it successfully you can see below here it is command completely successfully command completed successfully now let us add the record here execute okay so now I'll just use select star from employed to display all the records from the employee table we have a single record only single row so here it is a single row now guys what we can do we can add another record let's say I'll add the second record in the same way I'll add the third record and fourth record here it is guys we have added more records I'll select all and click on execute okay one row affected now now execute this again and here it is we have four records remember ID is are unique as I have told you before it is unique so it will be different for every row now let us try to add a similar ID what will happen it will show an error so we have 4 here let's say I'll add three again I'll change everything else let's say Gary we can add anything here and let's see here it is so 3 and 3 is repeating that means ID is unique but it is repeating which is false that won't happen two employees can never have a single ID can never have a similar ID so when I run it it will show an error execute and here is the error I told you before okay unique constraint cannot insert duplicate duplicate key in object the duplicate key value is 3 here it is I told you so this is the purpose of unique constraint now when I'll set it 5 and I run it again it will show no error you can see now let's say I'll just execute this here or here I should say now we have five records with no error because we fix the arrow so guys in this video we saw what is a unique constant in SQL how we can work with it in this video we will learn what is a primary key constant in SQL we will also see a live example so a primary key uniquely identifies each record in a table and the primary key must have unique values and it can never contain null values always remember that a table can have only a single primary key example of a primary key can be employee ID in an employee table it can be student ID or student roll number in the student table so let us see an example here we have a database here in our SQL Server which is amitb let us first use it use space Summit DB using the use command execute now we are inside the amitb database now we will create a table and and set one of the column as primary key so let's begin so here it is guys we have our employee table we have four columns employee ID employee name employee City and employee salary idn employee name are set as normal constraint because it can never have null values we need to set the primary key to ID as I told you before how we can set it in SQL Server after not melt just write primary key this will set primary key constraint on ID column that's it so guys in this way you can easily create a primary key you can easily set up primary key constraint now let us insert some records so here in guys we have inserted two records using insert into command okay insert into table name then the fields that is the column names and then the values for each column value I'll just execute this okay I just forgot to create table execute this execute this okay query executed successfully now I'll just insert execute and execute it successfully you can see now let us display all the records using the select star from table name employee okay select it and execute and herein you can see we have inserted two records and here in ID is a primary key so guys in this video we saw how we can work with primary key constraint what is its usage in this video we will learn what is a foreign key constraint and how we can create it in SQL so we saw primary key constraint in the previous video we created a primary key in a table so it is related to foreign key a foreign key is basically a column in one table which is referring to the primary key in another table so you can say the table with a foreign key is called child table and the parent table is what which is having a primary key just like the previous video we have an employee table okay this is our parent table this employee table is having EMP ID as primary key okay so this is the complete table is our parent table okay which is having a primary key so primary key basically uniquely identifies record in a table so here in in our case it's employee ID every every employee will have a unique employee ID no two employees can have a similar ID so this employee table is our herein we created the table using the following syntax so herein you can see as I told you before empid is our primary key okay so this is our parent table now let us go to below which is having a department table so Department table as I told you before is is having foreign key that that means it is a child table so in Department you can see the primary key is Department ID for the Department table okay and here in it is also having employee ID from the primary key table so this is what we call a reference here you can say it's called a reference okay so this EMP ID is a foreign key therefore we said a foreign key is a field in one table referring to the primary key in a data table that means a foreign key is a key in one table referring to a primary key in another table so in this case we are having EMP ID and we created the department table using the following as I told you for dapt dptid Department ID is a primary key and employee ID is a foreign key because we took it from the employee table which we have shown here using the syntax the following empid the data type foreign key reference foreign key I told you this is a foreign key here references to employee ID in the employee table that is references to this employee table empid okay so this is what is visible here you can see the employee ID the EMP ID column in the employee table is the primary key in the employee table as I told you before and the EMP ID column in the department table is a foreign key in the department table this is what we can say it's called foreign key so a table with foreign key is called child table so our department table is child table and this is the child table and our employee table above is parent table okay so why foreign Keys use it it is basically used to prevent invalid data from being inserted into the foreign key columns so invalid data won't be able to come in the foreign key column because it is related to the parent table it should be one of the values contained in the parent table that is contained in the employee table which is the parent table so these two are related that's why it will prevent invalid data from being inserted okay so this is how you can work with primary key and foreign key in SQL so guys in this video we saw what is a foreign key what is a primary key what is a foreign key constraint how we can set a foreign key constraint easily we can reference tables so we also saw what is a parent table and a child table in context of foreign key in this video we will learn about the check constant in SQL as the name suggests the check constraint is basically used to allow only a certain values for a column let's say you need to limit the value range which you place in a column let's say the value in case of age records you want to insert the record of students with a is less than 20 only so you will use the check constraint while creating the table with that let's say another example you want to insert records of employees with salaries less than 10 000 only in a table so you can set the check constant while creating the table and while defining the column salary let's see a live example here and you can see we have our SQL Server the databases are visible we have amidb database okay so let us enter amidb database click on execute query execute it successfully we are inside the Army DB database now let us create a table and set a check constraint for it I told you let's say I'll go for create table employee that is an employee table so here you can see we have employee ID employee name City and salary employee ID we have set not null and primary key so here is the salary we want to set a check constant so that only the records with salary less than 10 000 gets inserted so for that let us set the check this will depict the check constraint and here in salary less than 10 000 okay that's it so we have said that check constant now what I'll do I'll just create the table click on execute and table created successfully now what I'll do I'll just insert the records okay and we'll show you a live example again so here and I have inserted two records so these are two rows with ID employee name City and salary salary is less than 10 000 so there won't be any error when I'll insert it let's say I inserted it I will execute after selecting you can see queries executed successfully you can see here rows affected now let us print the records I hope you know to display all the records from a table we use select star from employee that is let's start from from table name click on execute you can see we have inserted two record salary is less than 10 000 so there is no error now let's say I'll insert a record with salil greater than 10 000 then it will show an error okay so let us add it here in Ireland 3 name let us add will location we can add any any random location now that's it eight thousand so I'll set 10 11 000 here but the check constant depicts that the salary should be less than 10 000 so there will be an error definitely let's add execute you can see query completed with errors and the error will depict the check constant you can see the insert statement conflicted with the check constraint definitely is the conflict occurred in database Army DB and column salary as I told you before okay it's all about salary because we inserted a salary which is over the check constant Mark okay so what I can do I can just change it to 1000 or let's say 5500 instead now when I'll I'll run it there won't be any error execute you can see one row affected okay now I can straight away it's good yeah I can straight away on it wrong and let's see the third value will get inserted easily with salary 5500 so all these salaries are less than or check constant that is less than ten thousand in this way you can use the check constant in screen so guys in this video we saw what is the usage of check constraint in SQL and how you can use it in your table in this video we will see how we can work with ascending keyboard so the ASC command is used to sort the records and that too in ascending order so let's see an example so herein you can see we have a database Amit DB here it is within that we have employee table so let us enter the midb database using the use command selected execute okay so now let us see the records of the employee table that is the following here it is guys select and execute here are all our records employee table ID employee name City and salary so now let's say we need to sort the salaries in ascending order so it's very easy using the ASC command how let's see I'll write select star from employee again then write order by keyword so I hope you know order by is used to sort the records that's why we used it but to sort of specific column records in ascending order we will be using the ASC command so right just order by salary because you want to order salary column order by salary ASC that's it semicolon selected and execute herein you can see it is unsorted right now salary and after clicking execute it will be sorted in the sending order here it is we have sorted it in ascending order okay guys now let us see another example I'll just copy this again now I'll just sort the employee name that is a string it is unsorted right now so let's say EMP name okay in the sending order I'll select this it is unsorted right now you can see so Brad should be on the top and will should be in the bottom execute now you can see we have sorted employee name column Brad is on the top and will is in the bottom as I told you before rest all in dictionary format so guys in this video we saw how we can easily work with the ASC command ASC keyword in SQL in this video we will see how we can work with SQL desc command to basically sort the records in descending order so Des stands for descending order so it's very easy we will also see an example here and you can see our SQL Server we have a database Amit DB and a table employee let us enter the mitp database using use command use space database name select it and click on execute so you can see command successful now let us see the records of our employee table select start from employee select it and click on execute so here are our records now let us sort the salary in descending order using the DSC keyword DSC command how let's say so right select star from employee order by so we want salary we want to update salary salary records in descending order so order by salary just write DSC and semicolon that's it this will sort the records in the salary in descending order select execute now you can see guys we have sorted our employee salary in descending order okay let's see another example now let's say the following RR records again the initial records now let's say we need to sort the employee name in descending order alphabetically in descending order it is right now unsorted so we will just sort it in in descending order how by using the same employee so in this case we need to order by employee name right because we need to sort employee name so I'll write order by EMP name DSC that's it semicolon select it and click on execute now here you can see we have sorted employee name employee name in descending order so we'll at the top and the initial Brad in the bottom so guys in this video we saw how we can work with the DSC command in SQL in this video we will see how we can easily work with SQL alter table statement so alter table is used to add or delete columns in an existing table so let us see an example here and you can see we have a database Amit DB and employee table in it so let us enter so we have entered the Army DB database using use command click on execute now we have entered let us show the records of our employee table here it is guys select start from employee select and click on execute now we have the following records in a table now let's say we need to add a new column that means we are altering the table so the name alter table so to add a new column we will use the alter table statement which is very easy so here in let's say we are adding age column for that let's use the command alter table name is employee press enter Right add because we are adding a column age and int okay semicolon let us select and execute commands completed successfully when I'll select this and execute to show all the records you can see we have a h column but it's null because we haven't added any records in each column okay but we added a new column now let's say we need to remove the age column now use the same command alter but what you need to use so to drop it just write drop column and remove the data type obviously because you want to just remove it select it and click on execute okay now command successful now use this again to show all the records let's start from employee here in you can see the age column vanished so we altered a table successfully so guys in this video we saw how we can easily alter our table we altered a column we added a new column then we deleted it using alter table command in this video we will see how we can work with the SQL update statement so what update statement is used to modify the existing records in a table we will see an example herein you can see we have databases under databases we have amitb database and within that we have employee table so let us first enter the midp database so that we can use it here is the command select and execute so we have entered now let us display the data offer employee table here it is so I hope you know to display all the records of a table we use select star from select and execute so now this is a table okay guys so now what we want we need to update the table so let's say we need to update the salary of employee David okay so we'll update it now write update statement update space table name which was our employee press enter now use the set command so whatever new record you need to set you need to mention here so we need to set the salary so write the column name salary set salary is equal to it's 7500 let's say I'll set it to 9500 salary is equal to 9500 for that means wear command where employee name is equal to David or we can mention the ID as for because it's for David so let me mention it ID is equal to 4 semicolon okay guys now we will select it the salary of David is 7500 for id4 it will update to 9500 okay selected and execute one reflected now guys we can select this and execute or we can mention it again so that it's a step since you updated executed here and you can see the salary of David updated from 7500 to 9500 so this is how you can update you can use update okay so you can also update multiple records by changing two of the values how so let's say I'll just copy this again okay so now I have updated the salary to 9500 now let's say I want to update let's say I want to change the recalls for id1 that means I want to change the name as well as City so how will I do it set salary since we want to change employee name and City so I'll mention set EMP name it's Tom it's Tom let's say I'll set it to Brad okay comma since I am updating multiple values I'll set comma I also wanted to change the city so I'll set city city let's say I'll set it to jkl for a demo okay for ID here should be one so for id1 that is this we will change the employee name from Tom to Brad and from City ABC to jkl okay now I'll select it and execute one reflected now let us execute this again now here you can see for id1 we have changed from Tom to Brad and from ABC to jkl so this is how you can work with the update statement to update records so guys in this video we saw how we can easily work with the SQL update statement to modify the existing record in a table in this video we will see what are sqls is these are basically used to give a temporary name to a table or a column in a table it makes the column names more readable as well as it only exists for the duration of that particular query the as keyword is used to create an alias so let's see how so herein you can see we have a database amidb within that we have employee table so let us first enter the midb database using the use command select and execute so now we have reached here so now we have a table as I told you before employee table let us see the records update table semicolon select execute so now you can see these are our records so let's say I need to display a specific column from the table for that I'll write select the column name I want is let's say employee name select EMP name from employee table right so I'll just execute this here it is the column name is EMP name now let's say I'll mention an alias for the column name for the result so I'll write so I'll use the so I'll use the as keyword as let's say I'll mention my result and rest of the command is same so here in instead of EMP name my result text will be visible because it's an alias click on execute here you can see my result but remember as I told you before this temporary name remains for the duration of that query only that means if now I'll use the I'll execute the following command that my result will vanish and only the exact EMP name column is visible so this way you can add an alias so if you are having a number of rows if you are having a lot of column and rows in your table and if you want to display the result you can always use the Alias and it makes the columns more readable the result more readable okay guys in this video we saw what is an alias in SQL in this video we will see how we can work with stored procedure in SQL server and what are these so a stored procedure in SQL is a prepared SQL code that you can easily create and save so that you can reuse your code again and again that means it's like a function okay let's say you have a query which you need to write again and again what you'll do you won't write it again and again you will basically create a procedure so that you can just call it and execute it whenever you need it so this is what we call a stored procedure okay for stored procedure we use the create procedure command okay so let us see an example herein we have our amid TB database you can see amidbi within that we have our employee table let us see the records enter the table using use amitb use command space database name that is our database name execute successfully now let us show the records of the employee table select star from employee semicolon and just show it execute okay guys now at first we will create a procedure for this command only because we need to use again and again so so we will create a procedure use the command create procedure let us give it a name all records use the alias and type the same command and type the same command select star from employee that's it press enter right go semicolon so now just select it and click on execute you can see query executed successfully to actually execute it and show the records use the command exec that is execute and then the name of and then the name of the procedure all records semicolon and just execute this here and you can see all the records are visible by just only typing exec keyword and the name of the procedure or the name of the function that's it okay now let's say we will create a stored procedure with a single parameter for that let us first display all the records here it is similar now what we will do we will create a stored procedure again similar way I'll just copy it with a parameter so right now we have no parameters here you can see we have no parameters here we will select records on the basis of a city name so for that use add the rate City okay so we want it for a specific City so I will mention the condition where city is equal to at the rate of City okay that is the following here and you can see it is showing an error I need to add the data type Also let's say it's where character 20 it's fine now okay now what I'll do I'll just use the execute command similar way all records but it is having a parameter now so the parameters at the rate of City is equal to let's say we want for city name ABC now let us change it to all records too so that we have a new stored procedure so here you can see guys we created all records to procedure and we added the type also to remove the error for the city and here in we will now execute it executed successfully let us see now all the records for CD ABC will be visible under our stored procedure with a single parameter okay guys now we can also add multiple parameters let's say I'll create this again okay this is our third one at first I'll just display the records here it is okay so now I'll add multiple parameters to our stored procedure and let us name it to three for our example okay so now we are fetching records that will select employees from a specific City okay specific City with a specific name okay so this is our third one City we already added so we will add multiple hidden let's say EMP name is what we are adding okay where care 255 so we added two parameters here in city city and we are using the and EMP name is equal to at the rate of EMP name so this is so this is what we added okay here in what I'll do I'll add in a similar way at the rate of similarly at the rate of EMP name okay is equal to I told you for a specific EMP name so City ABC is having two employees will and you can see Will and Tom both are having City ABC so let's say I'll fetch the Tom one a storm now we have a specific stored procedure for it with two parameters okay so now I'll just select it execute command completed now I'll just execute this let me add a semicolon here both select it execute you can see we selected that specific records so we have created three stored procedure for example here it is for select start from employee here in for single parameter and the third one for multiple parameters so guys in this video we saw what is a stored procedure how we can create it we also saw how we can create a simple stored procedure with the single parameter and film width and with multiple parameters also in this video we will see how we can easily work with the create index statement to create indexes in in SQL so basically indexes are used to retrieve data from a database faster than any other way let's see how we can create an index quickly we will see a live example herein we have a database a mid DB within that we have a table employee so let's see let's enter the database Amit DB using the use command here it is now let us see the records of the employee table using select star from employee select and execute so here you can see the following are records okay so we will create a next using it how using the create index statement let us use the command create index add the name of the index let's say I'll add my index the name on command on now mention the table name that is our table image employee and brackets under brackets you will be adding the column name that is this statement will create an index named my index on the column which we will add here let's say I'll add EMP name okay semicolon and let's run it so I ran it and and command completed successfully you need to remember that the users can never see the indexes but they are only used to speed up the searches that is speed up the queries now we can also create indexes with a list of column names that is using combination of columns how let me use this the name our second index my index to and I'll add multiple columns in it let's say salary okay now I'll execute it the second index completed successfully command completed successfully okay guys so we created two indexes so guys now let us delete an index using the drop index statement just right drop index then the table name that is employee dot the index name so we are deleting my index to let's say semicolon I'll just select it and I'll click on execute after clicking execute you can see query executed successfully that means we have successfully deleted the index so remember guys you can create indexes when a column contains a wide range of values so guys in this video we saw what our indexes in SQL how we can create indexes and how we can delete indexes in this video we will see how we can easily work with select into statement to copy data from one table into a new table so this will indirectly create a backup copy of any of a table so let's see how here we have our database so we have a midb database within that we have a employee table so let us go inside the midp database using the use command select execute so we have reached the mitp database you can see command successful now let us print the records of our employee table I have selected it click on execute here in you can see the records are visible with ID employee name employee City and employee salary so guys what I told you we will copy it into a new table we will copy The Columns into a new table so let's say I want to copy all the columns for that I'll use select star because obviously star is used when we want all the records all the columns so but herein I'll use into select into selects are into and the new table name so let's say it would be EMP backup 2022. Okay so mention the from employee that means from the table name from employee and that's it now I'll just copy it now I'll just select and execute here in you can see six rows affected now let's say I'll select this and execute and you can see our new table our backup table is having all the records of the employee table I selected I'll select it and show you again and you can see similar records okay in the same way let's say you want only specific columns so here is our employee table okay so now let's say I'll just mention it again our employee table so that it's easy it's easier for everyone to understand now I'll just mention okay so star we used y because we wanted all the columns now let's say I want specific column for that I'll just remove the star let's say we want only MP only EMP name and the salary so MP name comma salary into the same table into let's say we are creating a new table now my back my backup that's it okay so now I'll select it and click on execute here and you can see six rows affected if I'll type select now if I'll type select star from my backup now let's say what will be visible add a semicolon here and you can see only the employee name column and salary column is visible under my backup table so through this way you can easily create a backup copy or you can copy the data the records the specific columns or all the columns into a new table so guys in this video we saw how we can work with the SQL select into statement in this video we will see how we can use a select top clause in SQL to specify the count of Records to return that means let's say we have a table and we want to only fetch the top two rows records then we can easily do it using the select top clause so let's see so you can see your database is there under databases this database is having a table employee table let us face the records of the table using select star from table name so let's start from table name employee semicolon and I'll select it and click on execute herein you can see our table is visible so let's say I want to fetch the first two records only so guys what I'll do I'll use the select top clause let us fetch use select top we want two records so I'll write two star because I want all the records from employee so this will face the top two records all the two rows the top two rows only select it click on execute now you can see only the top two records are visible from the employee table now we will look at select top percent Clause so in that you can face the record on the basis of percentage for example so here is an employee table it is having six rows so if you want to face three rows only you can straight away right fifty percent there that's it so let us see select top since we want 50 records I'll write 50 percent manually star from employee that's it it will face 50 records that means the first three records completely from the employee table I'll select it and click on execute here in you can see it it has fetched three records in the same way let us write this statement again I'll just copy it I'll let's say let's say 75 select it execute now out of six record it has shown as five records okay so now let us see we can also add a where clause in it so I'll again use this for where Clause just right where let's say I want top 50 records or let's say top 25 records wherein salary is greater than seven thousand let's see what will happen so there is an error why because we added a semicolonia it's fine now let us select this and click on execute herein you can see it has shown us 25 record that means a single record or I'll modify this statement again to make it simpler for you let's say I'll just write select top 2 from employee salary greater than 7000. I'll select it and click on execute you can see two records are now visible the top two records which are having salary greater than seven thousand so it is 7 500 000. because these are above 7000 and these are the first two I hope you understood so guys in this video we saw how we can work with the select top Clause to return a specific number of Records in this video we will see how we can backup our database on SQL Server so at first we created a database Amit DB then we created a then we created an employee table in it using the following command after that we added records in the table so herein you can see our amiddb database with employee table is having the following records so we need to backup the entire database so it's very easy you need to execute in command that is backup that is backup database the name of the database Amit DB then mentioned to disk is equal to in this you need to add the path where in your database will get backed up always remember add in a different directory always because in case if your directory which is having Ms SQL crash then at least you have the backup of your database so let us see the path so here in let's say we created a studiopedia backup folder you can create any folder I'll just copy this after that I'll mention the path here paste now I'll mention the name of the file my backup Dot b a k is the extension now everything looks fine now let us execute and see what will happen select and click on execute herein you can see processed backup database successfully processed so it is also showing the time and the completion time now let us see here you can see it automatically created a backup file so in this way guys you can easily create a backup of your database so the bake extension is here because it will allow you to easily restore your backed up database so guys in this video we saw how we can easily backup our database on Ms SQL server in this video we will see how we can work with SQL create view statement to create a view so what is the view a view in SQL is basically a virtual table which is based on the result set of an SQL statement so if you want to show or present a specific data from your database or table you can use a view a view basically contains rows and columns that is part of your records okay the fields in a view are basically fields from one of your tables only as I told you before so you can present the data in a more readable form using View so to create a view we use the create view statement in SQL so let us see an example here in we have Amit DB database you can see and our employee table let us enter the database using the use command use space database name that is amitb is our database here execute and you can see successful now let us enter our table now let us see the records of our table select star from employee select it and click on execute now here you can see our record is having ID employee ID employee name employee City employee salary now let's say we need to create a view for employees with City ABC that is the employees that is employees living in CD ABC so that would be the first one and the last one or we can fetch a view for it let us create a view create views the command let us add the name of our view let's say employees ABC we can write use the Alias as now use the select command so we wanted employee name and City so EMP name and City from mention the table name that is our employee okay where city is ABC okay now we have created a view okay semicolon selected and click on execute commands completed now to display use the same select star okay in that in this case we will be using select star from our view name because you want to get the view I'll just copy this to ease okay so I'll just select a semicolon I just select I'll just select and click on execute here and you can see both the records are visible this is what we wanted for City ABC okay now let's say I'll just copy this again let's say we want let's say we want to create another view with salary let's view the records again select start from employee let's say we are viewing records with salary greater than 7000 okay so what we will do let's write salary above 7000 okay what we want to display we want to display let's say the employee name and City let's say it's fine we can also display salary also that are above 7000 from employee table wherein so what condition we need to mention we need to mention under view wherein salary is greater than 7000 okay this looks fine I'll select it we have a new view now execute command completed now to display it I hope you remember it's select star from your view name that is the following it will come on its own you can see salary above 7000. that's it semicolon select and that's it so we have all the records of employee with salary greater than 7000. okay guys this is what we displayed using a view so we created a view now now we will see how we can remove so now we have two views here in first second both okay now let's say we need to remove the employees ABC view how we can remove it for that guys use the drop view command right drop View and the view name that's it so our view name was here it is employee ABC select and execute so you can see commands completely successfully now if you'll try to display it nothing will be visible okay you can see you can see invalid object name okay so this is what I told you we have dropped this View so guys in this video we saw what our views in SQL we also saw how we can create a view as well as how we can drop a view in this video we will see how we can easily drop a table in SQL Server so right now we have a database Amit DB let's open it commands completed successfully here's our database so guys in that omit DB database we have a table let's see which one employee table let's see the records of the table using the select star from employee command selected execute here you can see the records of the table employee so herein we have ID employee name employee City and employee salary now guys let us drop the table so to drop the table means to delete all the records on the table as well as to remove the existence of the table so let us use the command drop table face table name that is employee semicolon now this will delete the entire table we will check select execute now you can see command completed successfully now let us refresh this now herein you can see the table is not visible because we deleted it we drop means we deleted it now I'll try to get the records from my table but the table doesn't exist now so when I'll select this and click on execute this will show an error because you can see invalid object name employee because we already deleted the table so there are no tables in our amid DB database because we dropped it so guys in this video we saw how we can easily drop a table and remove the entire table with a record in SQL Server