Microsoft SQL Server 2022 Course for Beginners

1. Course Introduction: I'm amid the one 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 from. This SQL cause basically covers microsoft SQL Server and it contempt for beginners. If you want to learn a skill from the beginning itself, then this course is for you. We have used a free and open-source Microsoft SQL Server Express edition to learn SQL 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. 2. SQL - Introduction: In this video, we will learn what is a skill. It's introduction and overview. So let's begin. At first, we need to understand what is RDBMS, RDBMS relational database management system to understand RDBMS. Here. And you can see we have a table. So since childhood 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, our table has rows and columns, and a database has what up of tables. So that means a relation. A relation of rows and columns is called table. And this is what we call RDBMS, 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 or MySQL, Oracle and other related databases, RDBMS, I have told you before, it's towards the data in the form of tables. And tables is having rows and columns. We have four rows. That means the 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 are shown here. Now what is SQL? Sql is the Structured Query Language is an RDBMS is the basis for this that I've told before. That means it will store the data in RDBMS, RDBMS phone that is in the form of rows and columns. Okay. And it is governed under ANS say that is American National Standards 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 a screen. Okay, so this is the current version. It will reduce 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 UFO squared that will allow you to easily create database tables, work on operations clauses, operators, and other stuff in a screen. 3. SQL vs NoSQL: Hello guys. In this video, we will learn about the difference between SQL and 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. 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 sequel just came in late 2000. You can see examples. Sql is basically RDBMS. All the database 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. 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 Redis database using white columns, using Cassandra and HBase. So the usage azure 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 that SQL example we have shown my sequence here. As I told you before, SQL save 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 the rows and columns. So these are columns 12345, Employee ID, first name, last name, employee ID, employee zone. And these four 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 a skill. That is, as I told you, tables with fixed rows and columns is what we call a model for the screen. What 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 a document-based MongoDB database. We have four columns here, IT department name, department manager, and department rank. So you can see the database here under no sequel. Under MongoDB. Documents have fields and collections, is having documents like tables is having rows and columns in these collections have documents and fields. And database is a group of collection 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 in graph form in no sequence. So completely different from our old school. Conventional SQL schemas. It is rigid and it is flexible. Nodes, E coli cell flexible database. You can see, you can store the data in all these forms. Different firms document, Key Value Graph, Multi record, asset transactions. So it is supported by SQL. Erin, no sequel doesn't support it, but mongodb will still support it. So assertTrue injection at basically an acronym for Atomicity, consistency, reliability, and durability. These are basically database operations. And this assert SCID is known as acid properties or you can say acid 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 NoSQL MongoDB is our example. So guys, thank you for watching the video. 4. Install SQL Server 2022 on Windows: In this video, we will see how we can easily download and install Microsoft SQL Server 2022 on windows. Let's see, at fascia, go to the web browser. I'm using Chrome. You can use any web browser on Google type, Microsoft SQL Server, and placenta. And 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 that are visible for SQL Server 222, the developer and express. Okay, so I'll be going for the experience 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. No, not started. They started with here it is for emboli. Double-click on it to install. Minimize. Now the setup will begin. Now we will select the installation type, basic customer and online 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 big node installation. So I'll be going for the basic step. After clicking the lysosomes it visible. Click on Accept. Now the installation location is visible. It will get installed into Program Files, which is fine. You can change the location from here, but I'll keep the default to 78 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 as written here. The first one is the connection string. This is used to connect a skill with other languages. Programming languages. Here is the log folder, so I hope we know what is the log folder here in the report regarding starting off for SQL, as well as areas 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 rows affected, 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 easier for you to walk 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. It got downloaded the zip file, double-click on it to install, minimize. Guys a set of structured, I just minimize this. So the sector 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 not we'll load the packages and install. Guys, you can see we have successfully installed the madmen Studio. Also. Click on Close. 5. SQL - Create a database: In this video, we will see how we can easily create a new database using Microsoft SQL server. Go to Start type SQL Server Management. And here it is. The Management Studio 19 is visible. Click on Open. Now you can see the SQL Server Management Studio opened up. You can directly click on Connect here. 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, and now you can change it to, let's say, 200 per cent so that it's visible. Now I'll type a command. It's called command create database to create a database. And let say the database name it. I'm a 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. This. We'll create our first database commands completed successfully. Now I'll click on refresh. And under this you can see the database is visible. 6. SQL - Create a table: 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 Ahmed dB. In the object explorer, you can see under databases we have amid DB database. So guys, now we need to create a new table, will be creating an employee table for that guy is first we need to use ADB and enter the database. Retyped, use, use space database name, selected it, and now click on execute. You can see command completed successfully. That means we have entered the database db now we can easily create a new table. So guys, to create a new table in SQL Server, we use a create table command. So let's write create table, then the table names. 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. Id will be an int, right? So it's fine. Comma into EMP name. That would be employee name. And the same will be in their care. Let's add the value comma, then come city their care only to 55 comma. Then the last, we can add a salary for our employees, which will be in int 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, and other constants in it. That means NOT null, check constant default, but I'll explain all these in a new video. So right now I'm 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 I'm EDB unclicking, I'll click on Tables. Now you can see a 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. 7. SQL - Insert Records: In this video, we will see how we can insert records into a table in SQL Server. So herein, first we created amid DB database using the following command. Then we created a table employee. You can see the database. We created a medieval heritage. 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 n, that is in ID and salary is int integer. And employee name and city R4 are being given where archetypes day-to-day. Now let us insert a record. For inserting records will be using insert into command. Let's write here amniote 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 them, press enter, salary. Select them, press Enter. So it's easy. Now, right? Values and insert records one-by-one. At first we will insert only a single row. So for ID letters, right, one, let us enter in quotes employee name. Let us right, tom city. Let us write a sample ABC, salary letters, write any value in it. Okay, so now it's fine semicolon. Now we will select this and click on Execute to enter the first row in an 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 selected and execute. After executing in the results section, you can see we successfully added one record in the table. Now for another record, let us implement the command again. Let us add the second record. Emma. Okay, now execute it selected, click on execute, one row affected. Now let us mentioned the command again, select star from employee, that's it. Selected 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 Regards in your Microsoft SQL Server table using the insert into command. 8. SQL - SELECT Statement: 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 our database 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 army DB database using the use Command click on execute, query executed successfully. That means we have entered our PDB 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, employees or 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 all we faced all the records. Now let's say you need to fetch the records for specific columns. That means, let's say we need to only get the idea and employee name. For that. You select. Mentioned the name of the column, that is select id comma EMP name from table name employee. That's it. Selected and click on execute. So here you can see only id and EMP name we faced from the employee table. This is how you can use the select command. 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 fit specific column record. So guys, thank you for watching the video. 9. SQL - SELECT DISTINCT Statement: In this video, we will see how we can face the distinct records in MS SQL server. So right now we have a DB database. That's why we use them ADB command, so that we can enter the DB database or database. Within that we have created an employee table with some records. Now let's say first you will fetch all the records of the employee table using the IOP not select star from employee semicolon now selected, we saw the same in the previous video. You can execute below under results, you can see these are our records. But guys, what we want, we need to face a distinct records here and you can see the city of some of the employees are same. 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 duplicates. So it is city, select distinct city from, from employees 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 ABC and bigger. You can see it is repeating here, PQR is repeating here, and ABC's repeating here. I'll click on execute. Here you can see the distinct values of treaties 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. 10. SQL - WHERE Clause: In this video, we will see how we can filter records based on a condition using where clause. Right now we have our AMI DB database. We have entered it using the following command, jews 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, name, city, and salary. So let's say we need to fetched records on the basis of some condition. We want to fetch the employee name with salary 8,000. So let's see how hearing it 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 8,000. So here in only the employee name will be visible for salary at Southern, that means Emma will be displayed. Select this and click on Execute. Now you can see only an image displayed. Now let us modify the query. Now, select star from employee where salary is equal to 8,000. That means all the records of the employee name Emma will be visible because we have set the condition as salary 8,000. So I'll select it, execute. You can see only the record, only the row of the employee name Emma is visible. Complete row, completed record, because we have set the condition as salary 8,000. Now let us display this again. You can see now what we need to fit. Let's say we need to fetch a TXT record because right now we face the numeric record. Let's say I want the record of the employee with name as Ginni. So let's say their name is Jenny, that means AMP name is equal to Jenny. Okay, but we need to add the codes for extinct for string because the type was here. Before that, we went for salary which wasn't. So no quotes were required in that. So everything looks fine. Select, click on Execute. Now only Jenny record is really visible employee named Jenny execute here and you can see all the records of the employee name. Employee named Jane is visible because we use the where 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 7,000. So let's say what I need to write here, the field name salary is greater than what I said 7,000. I can also set equal to here, the greater than equal to operator. Okay? That means delta greater than equal to 7,000. So the output should be greater than equal to 700, means equal to seven, doesn't alter. The output should be Tom, MR. david, and Henry. Let's see, execute here. And you can see Tom, David Henry, because salaries of these employees or more than or greater than and equal to 7,000. So guys, thank you for watching the video. 11. SQL - ORDER BY Keyword: In this video, we will see how we can easily order records in SQL Server. So we use the order BY keyword to sort the records in ascending or descending order. The default for order BY keyword is ascending order. So ascending order means arranging from smallest to largest, like 12345. Or you can say for alphabet A-B-C-D-E-F, that is from smallest to largest. So herein we have our AMI DB database. As you can see here, I'm in dB. We have entities using the use command. Here it is, I'm a DB 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. Here. And you can see guys, the records of the employee table are visible below in output. So we have four fields, ID, 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 of the sorting here. And I'll type select star from table limb, that is employee, presenter, the command orderBy. 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 will 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 it, and you can see the least salaries on the top. Then comes the next one and the end. The last one will be that will be the employee with the highest salary. Okay, guys. So now let's say I need to, you need to do the reverse for that type DESC for descending order that is on Largest to Smallest. Now, it will be opposite henry will be on the top, just selected. Execute. Now herein you can see guys, Henry's on the top and we have arranged according to descending order. That is from largest to smallest, 9,000,508,000.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, CT is for to employees and ABCD is for two employees. So let's say we are arranging accordingly the city, according to a city. We can set multiple columns so that we're able to solve this multiple city orders issue according to salary. Let's see. So we didn't guys we have used city as well as the salary in order by let's see what will happen, selected and execute. Now hearing you can see we have ordered in ascending order because we haven't mentioned anything. So ready for the descending. So it has arranged it accordingly. City and salary in case of repeated values, city repeated. It has arranged according to salary, the issues, no resort. So guys, In this video we saw how we can easily walk with order BY keyword to sort the record. 12. SQL - AND Operator: In this video, we will see how we can easily work with the SQL, an operator, the operator used to filter records based on more than one condition so far. And you can say you can display record if all the conditions separated by the and operator or two. That means all the conditions fulfill, all the conditions satisfied, and all of them are true. So guys, hearing 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 here and 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 an operator. So hearing we will display employee records whose city is ABC and salary is 7,000. 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. The semicolon. Now we'll add the conditions using the where clause, where city I told you is ABC. And we're using the AND operator and salary is equal to specific salary, 7,000. That's it. Add semicolon, and let's execute and see the result execute here. And you can see we are able to face the salary with 7,000 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. Let's say I want to display the specific employing them will with the city abc. So the city is ABC only. But the second is condition also should satisfy. So here, let's say I want to display 67 double zero. So what I'll do, I'll just mentioned less than. And everything looks fine. Selected click on execute here and you can see the employee name is visible because the salary less than 7,000 or so. Whereas 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. 13. SQL - OR Operator: In this video, we will see how we can work with the OR operator. So the order 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 an operator and only a single condition should be true for OR operator. So let us see an example. We have amid 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 in order to display the records, use select star from table name. The table name is employee. Here it is selected and click on execute. We have executed the query here and you can see we have employee ID, employee name, city, and salary. So hearing will 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. I'll mentioned the command presenter. Use the where clause. Emp name is equal to, I said Ginny. Our EMP name is equal to Henry. So both the records we'll get we'll get fazed because both the records are true. Even if one was true, it would have return the records without any error handling 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's no employee with the name Steve. If I remember, I'll show the records again. Execute. You can see there is no employee with the name Steve. So this command will is good because we are having or operator. That means only one condition should satisfy, execute here and you can see only one condition satisfied. Jenny, second failed, but there's no error because we're using OR operator. This is the benefit of using an OR operator. So guys, In this video, we saw how we can work with OR operator in SQL Server. 14. SQL - NOT Operator: In this video, we will see how we can work with the not operator in SQL Server. So the NOT operator is used to display a record if the condition is not true, not true. Okay, So let us see an example. We have amid DB database here, as you can see here in SQL Server. Within AML DB 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. The 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 record. Erin, you can see query executed successfully and here are all the record. So I told you employee ID, employee name, employee safety, 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 1,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're not salary is equal 2000. Now I'll select it. Execute. You can see except 8,000 salary record, every record is visible with that guy is for numeric. You can remove this, the single quotes. And then I'll execute again. It won't show any era. This is the usage of NOT operator. We just removed the following record from the result. That is the employee with salary 1,000 erased all the employee records are visible. So guys, In this video we saw how we can work with the not operator in SQL Server. 15. SQL - IN Operator: In this video, we will see how we can work with the SQL interpreter. So in operator allows you to set multiple values in a where clause. So let's see what 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 Amit DB database and the database PDB database. Within that, we have tables. We have a single table only employ. Let us see the records of the table using select star. Before that guys, we have entered the Army DB database using use pais database name, that is AMI DB 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 as in salary. Now let's say you want all the records of employees whose city is z by w and FGH. You can easily set it using multiple conditions. That is a skill 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 here we will write the condition wherein the column name city. Now use the Esquiline and within that add the values, multiple values. That means it was Z and Y, W and F, G, H heritage. Only. These two will get printed, Jenny and David, semicolon and run it. You can see I told you is Jenny and David is visible because the city was led by Wn, F, G, H. 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 face the records of the employee table based on employee names while mentioned. So I'll mentioned eNB name under this. Now we need to mention the names. Let's say I want the record only for let's say Emma, Henry and will swell mentioned Emma Henry, and that's it. Okay, I'll select it and click on Execute. Now you can see only Emma, Henry and we'll employ them to quads are visible. We can also use its opposite, that is, after Enrique use nothin. So let's say I'll just copy this and paste. Noel just mentioned not in what will happen except MR. henry and we'll 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 we'll all we'll visible. That means Tom, Jenny, and David will be visible because we have used not in click on execute. You can see Tom genuine David is visible. So 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 where clause. We mentioned multiple values here you can see. So guys, thank you for watching the video. 16. SQL - BETWEEN Operator: In this video, we'll see how we can use the between operator. So between itself says that in orange. So you can set a range like between value one and value to. These values can be numbers like 123 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 an army DB database. Within that, I'm having my table employee table. So we have entered the database using the use command, use database name, that means US Army DB. 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 face the employee name record. That is the entire employee records with salary 7000-9 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 7000-9 thousand. So between let's say 7,000, 9,000. It will also include both the value that is 709,000 because it is inclusive. Okay? Semicolon and we will select it. After that, I'll click on Execute. And only those records. Salaries 7000-9 thousand, including both, will be visible here. You can see here is visible here. More guys. Let us also see not between. I'll just copy this and if you want let say these are our 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 7000-9 thousand that means except 7,000 to 9,000, everything will be visible. That means Jenny records, as well as Henry records will be visible. But that will also be visible because it's less than 7,000. Because we have used not between selected and click on execute. Here. And you can see I told you, Jenny Henry, and will be visible. If you will go for between again, then the rest of the records will be visible. So I hope you know, the different between between an node between is just the negation of between. What Between? Now move further here. And we can also use the text values. So we will use between. Now let's say I'll mentioned a text values. So these were our records. So herein, let's say, let's say between David and let's say we are displaying between David and Jenny. So dividend Jenny means alphabetically D and J. Between D and J, we have EMR at Henry. Okay. And hearing we've mentioned EMP name. So we need to also order it. So orderBy, let's say EMP name. So we'll see what will be visible. Select it and click on execute here. And you can see between debit and Jenny firstly, dividend Jenny will be visible because it is inclusive, I told you before. And between that alphabetically, M and Henry is also visible. So this is how you can use text values. It's so simple. So guys, in this video, so we can work with between a node, between operator in SQL. 17. SQL - LIKE Operator: In this video, we will learn about a SQL-like operator. So the lac operator is using 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. You want only the employee name records with names starting with T. So you can use like operator. In this way. You can also work around 01 or multiple characters or even a single character for that guys. Percentage sign is provided by SQL. This is 401 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 here and you can see guys, we have Amit DB database under databases within that we have employee table. So we have entered the DB database using the use command, use pais database name, that is, I'm ADB. Now let us see the records of the table so that we can operate on the like operator. Our table was employee. So using select star, we can get all the records. Select and click on execute. 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 and we have employee name, Tom image, any David Henry and we'll 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 write select star because we want all the records we're using star. We won't be using semicolon again because you want to add a condition where city, the column name is city, right? City. Like the like operator. And we weren't starting with a capital a, write. That is ABC cities will be visible eyelid percentage here because we just saw the usage of percentage so that we can easily represent 01 or multiple characters. Here we have mentioned a single character. It's fine. Now, semicolon and we will run it, execute it, and you can see only the cities 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, where city. So there was pretty column. Now we want EMP named columns, so I'll mentioned EMP name, that's it. Like we want it in the end. That means we want the employee name records ending with that set. So herein, we will just use the percentage before n mentioned. I know after executing it only the third record will be visible. Select it and click on execute. You can see only Jenny record is visible. In this figure. You can work with the percentage. Let us guide display the records again. Now let's say we want the employee records with employee name that are having E, D in the second position. So Jenny is having E in the second position and Henry is having in the second position. So we will use a lack of data. Use select star from employee because you want to display all the records. Now the condition part hearing, we'll be using both the percentage as well as underscore. We're 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 l mentioned E here. After that, there can be any number of characters, so I'll add presented, That's it. Now, both Jenny, because the second character is E, Jenny and Henry will get faced selected and click on execute here. And you can see Jenny and Henry got faced easily. Now guys, let us see the light condition again. Let us first display the records. Okay. So hearing 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 mentioned the condition again. Emp name, not like use, not like E I told E mentioned percentage because there can be any number of characters after that. But the first character is fixed, which we have mentioned as E semicolon. Now, except Emma, every record will be visible, execute it, and you can see except Emma, every record is visible. In this way. We can also work around they're not like operator. Okay guys. So we have worked around the percentage as well as underscore. You can work on 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. 18. SQL - MAX() Function: 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 amid DB database. You can see here our database under Database DB, under that we have employee table. So we have entered them DB database using US Army db use space database name. Now, what we want guys, we want to display all the records of the employee table for that type, the command select star from table name, that is select star from employee. Selected. Execute. Now here and you can see all the records or 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 mentioned the column for which you want the maximum output. Erin, we want maximum salary, So LDH salary. After that, press Enter and write the name of the table that is from tamer limb, that is from employee semicolon. And let us know, select this and click on execute here and you can see the maximum salary, 9,500. Now guys, you can see there's no column name, so we need to fix this. Use an alias for this that is as hearing, right, Let's say result. You can write anything. We're just giving the name to the column output result. Select it and click on execute. So here you can see we've got the maximum salary sins. You can see it is 9,500 and our employee table is having the maximum salary of 9,500. 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. 19. SQL - MIN() Function: In this video, we will see how we can easily work with the main 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 face the employee name specifically with the minimum salary, you can use the SQL main function. So let's see an example here. And we have a database. I'm ADB. Here it is on a database is I'm a 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 fit, I want to fit the records of an employee with the minimum salary. So it will return the following, Jenny, with 5,000 salary. So let's see. Use the Min function. I'll write select MIN under brackets mentioned the column name wherein we want to fix the minimum. So I'll mentioned salary. That's it. Now I mentioned the table name employee. And that's it. Let us select and click on execute. So you can see it has returned the minimum salary, 5,000, but there's no column name. If you want to add a column name, you can write an alias as, let's say, the deserted, and select it again, execute here and you can see it at 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. And we selected salary. 20. SQL - SUM() Function: 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 that we have employed salary as a column. So we want to calculate the total salary is given. You can use the sum function. So let's see here and we have our database and PDB database. And it didn't that we have employee table here and we have entered zombie DB database. Let us see all the records of the employee table using select star from table limb, that is, select star from employee. Semicolon, select it 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 a select again, select some and mentioned the column name within it. That is salary. Because you want to get the sum of salaries from David limb. Table is employee. That's it selected and click on Execute. Hidden. You can see the sum of all the salaries are visible here. Now, let us with records again. Now let's say you want the sum of salary's only for salaries above 7,000 for that guys, what do you need to do? So about 7,000, let's say it's greater than, equal to. So it will be Tom. That means 7,000 plus 8,000 plus 7,500 plus 9,500. Okay. So here in allied air condition you using we're wearing salary is greater than equal to 7,000. That's it. Semicolon, select it and click on Execute. Now you can see for all of those for salaries about 7,000, that is greater than equal to 7,000 is 32,000. So guys, in this way you can easily find the sum of any numeric column values of any numeric column. 21. SQL - AVG() Function: 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. Let's say we have products table that we have price and we want the average price. We can use the AVG function. So here didn't. We will see another example in which we have our employee table. Let's say right now we have Amit DB database. And in that we have our table, employee table. And at first we will display all the records of the employee table. Before that we entered the Army DB database. Here it is. Now I'll display all the records of the employee table using select star from table limb that is employee selected 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 mentioned the column name. In our case the column name is salary. Because you want every salary from table limb. That means employee. Select it and click on execute to get the average salary. The average salary, 7283. In this figure is you can get the average value of a numerical column. In this case it is salary. Okay guys. So he's hearing, we can also add a clause where let's say salary is greater than 6,000. No semicolon. When I'll select, click on execute, it will only consider the records, the employee salary above 6,000 and we'll find the average. That's it, guys. In this video, we saw how we can find the average of a numerical column. We found the average of salaries. 22. SQL - COUNT() Function: In this video, we will see how we can return the count of rows that match a 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 5,000, let's say. So you can easily do it using count function. So let's see how herring, Let's say first we have the DB database inside, we have employee table heading under tables, employee table. So let us switch the records of the employee table using select star from table limb, that is, select star from employee. Select it and click on Execute. Herein below you can see the output. We have six row Row Records and we have four columns, ID, 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. We need to add the column, it's returned 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 hearing. I'll remove the semicolon hearing. I'll set a condition wherein salary is greater than, let's say 7,000. So it will count the employees whose salary is greater than 7,000 and will display the number selected and click on Execute. Erin, you can see three verified using select star from employee again, select it and click on execute. So you can see above 7,000, there were three employees, that is Emma, David, and Henry. So three God 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 lame Manchester. That's it from employee semicolon, and that's it. Click on execute and it will display six, 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 brackets star. You can recheck again, execute, and you can see we've been having six employees, employee records. Whereas in this video we saw how we can work with the count function to count the number of records. 23. SQL - NOT NULL Constraint: 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 not null constraint. Not null 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'll add null values after setting it as not null constraint, it will show an arrow. But always remember our color. A column can always hold null values, but if you set the column IS NOT null. Adding a null value will show an arrow. Okay? So let us see an example here we have a multi-bit database. You can see let us go inside the DB 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 finished names with it, datatype and constants. Let's see, 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 named, that is employee name. I'll set it as varchar and I'll set it as not null again. Next is our city. I'll set it as wildcat again. Okay. And the last one, I'll set it as salary, which isn't salaries, right? 5,000, 50,001 lag. We have added a not null constraint. 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, 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 here, Tom, Emma, and all these. So when I'll execute this, let me execute this before. When I'll execute this here and you can see we have inserted it. Okay? So again, I'll just show all the records. Select star from employee execute. So we have two records. Now, remember that ID and employee name is not null. So we can't leave that record. Okay, So guys, in this video, so what is it not null constraint in a squid? 24. SQL - UNIQUE Constraint: In this video, we will see what is a unique constraint and Esquiline 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 constraint will be only a single portable. 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 our database, DB, but we didn't add, we don't, we don't have any tables. So we will create a table and show you an example for unique constraint. Let us first enter the database user medieval 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's salary fields columns. Id is unique. Every employee will have a unique ID obviously. So let's say there are two implies, Tom and Jack, they won't be having a similar idea, right? There'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. Let's say we added records for a single record for all the four columns. That is the following. At first night for let me create a table. Click on execute. We created successfully. You can see below. Here it is command completed successfully. Command completed successfully. Now let us add the record yeah, execute. Okay, so now I'll just use select star from employee 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 forth record. It is guys, we have added more records. I'll select All and click on execute. One row affected. Now. Now execute this again. And here it is, we have four records. Remember, ID is a 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 four 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 say here it is. So 3.3 is repeating. That means ID is unique, but it is repeating which is false. That won't happen to employees can never have a single ID, can never have a similar id. So when I run it, it will show an arrow execute. And here is the right to live before. Unique constraint cannot insert duplicate, duplicate key in object. The duplicate key values three, here it is. I told you this is the purpose of unique constraint. Now when I'll set it five and I run it again, it will show new era. 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 you fix the arrow. In this video, we saw what is a unique constraint in SQL, how we can work with it. 25. SQL - PRIMARY KEY Constraint: In this video, we will learn what is a primary key constraint 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 or lumber in the student table. So let us see an example here. We have a database here in SQL Server, which is Ahmed db. Let us first use it. Use pay somebody to be using the use command. Execute. Now we are inside diameter DB database. Now we will create a table and said one of the column as a primary key. So let's begin. So here it is, guys, we have an employee table. We have four columns, employee ID, employee name implies city and employee salary ID and employee name or set as not null 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, often not just straight primary key. This will set primary key constraint on id column that said, 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 herein guys we have inserted to record 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 a table, execute this, execute this query executed successfully. Now I'll just insert execute and executed successfully. You can see now let us display all the records using a select star from table name, employee, selected, Execute. And here, and you can see we haven't started to records and a1, id is a primary key. So guys, In this video we saw how we can work with primary key constraint. What is it? You said? 26. SQL - FOREIGN KEY Constraint: 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. Foreign key is basically a column in one table which is referring to the primary key in another table. So you can see 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. 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, which is having a primary key. Primary key basically uniquely identifies each record in a table. So here in our case it's employee ID. Every every employee will have a unique employee ID. No two employees can have a similarity ID. So this employee table is our parent table with a primary key here when we created the table using the following syntax. So herein you can see as I told you before, EMP ID is a primary key. Okay? So this is the 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 means it is a child table. So in department, you can see the primary key is department id for department table. Okay. And hearing, it is also having employee ID from the primary key table. So this is what we call a reference. Here you can see it's called a reference. Okay? So this EMP ID is a foreign key. Therefore we set a foreign key is a field in one table referring to the primary key in another 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 him for DBT, DBT IT 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 EMP ID, the datatype 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, EMP ID. 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 this parent table. Okay? So why foreign key is used? 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 uncertain. Okay? So this is all 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? 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 or foreign key. 27. SQL - CHECK Constraint: 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 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 see 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 constraint while creating the table and while defining the column salary. Let's see live example here. And you can see we have our SQL Server, databases are visible. We have Army DB database. Okay, So let us enter DB database. Click on execute, query executed 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 unemployed table here. And you can see we have employee ID, employee name, city and salary. Employee ID. We have said NOT null and primary key. So here is the salary. We want to set a check constraints so that only the records with salary less than 10,000 gets inserted. So for that, let us send the check. This will depict the check constraint. And herein, salary less than 10,000. Okay? That's it. So we have said that check constraint. Now what I'll do, I'll just create a 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. Here. And I've 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 era when I'll insert it. Let's say I inserted it, I will execute for selecting. You can see queries executed successfully. You can see here there was affected. Now let us print the records eye-opener to display all the records from a table. We use select star from employee. That is select star from table name, click on execute. You can see we have inserted to record salaries is less than 10,000, so there is no error. Now let's say I'll insert a record with Sally greater than 10,000. Then it will show an error. Okay, So let us add it here. Then I'll add three name. Let us add will our location. We can add any, any random location. Now That's it, 8,000. So I'll set 1011000 year, but the check constraint depicts that the salad should be less than 10,000. So there will be an error definitely. Let's add execute. You can see Curie completed with the arrows and the editor will depict the check constraint. You can see the insert statement conflicted with the check constraint. Definitely is. The conflict occurred in database DB and column salary, as I told you before. It's all about salary because we inserted a salary, which is over the check constraint Mark. Okay? So what I can do, I can just change it to 1,000, let's say 5,500 instead. Now vinyl, I'll run it. There wouldn't be any arrow execute. You can see one row affected. Okay. No, I can straight away. It's good. Yeah, I can straight away run it. And let's see, the third value will get inserted easily with 75,500. So all these salaries are less than object constant that is less than 10,000. In this way, you can use a check constraint in the 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. 28. SQL - ASC Command: In this video, we will see how we can work with that's ending keyword. So they SC command is used to sort the records and that too in ascending order. So let's see an example here. And you can see we have our database and make dB. Here it is. But then that we have employed table. So let us enter DynamoDB database using the use command selected execute. Okay, so now let us see the records that 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 AC command. How? Let's see. I'll write select star from employee again, then right, order BY keyword. So I hope you know, orderBy is used to sort the records. That's why we used it. But to sort of specific column records in ascending order will be using the AAC command. So right, just order BY salary because you want to order salary column. Order BY salary is C, That's it, semi-colon selected and execute here. And you can see it is unsorted, right? No salary. And after clicking Execute, it will be sorted in descending 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 in ascending order, I'll select this. It is unsorted right now you can see, so Brad should be on the top and whales should be in the bottom. Execute. Now you can see we have sorted employee name column. Brad is on the top and we'll isn't the bottom as I told you before, the rest all in dictionary format. So guys, In this video we saw how we can easily work with the ASC command, ASE keyword in SQL. 29. SQL - DESC Command: In this video, we will see how we can work with SQL DESC command to basically sort the records in descending order. So D 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. I'm ADB, Android table employee. Let us enter the army DB database using use command. Use pais database name selected and click on execute. So you can see commands successful. Now let us see the records of our employee table. Select star from employee selected and click on execute. So here are our records. Now let us start the salary in descending order using the DAC keyword DAC command. How, let's say. So write 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 DAC 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 employee's salary in descending order. Okay, Let's see another example. Now let's say the following 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 descending order 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 orderBy, EMP name, DAC that semicolon. Select it and click on Execute. Now you're in, you can see we have sorted employee name, employee name in descending order. So we'll add the top and the initial Brad in the bottom. So guys, In this video we saw how we can work with the DAC commodity in SQL. 30. SQL - ALTER TABLE Statement: 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 DB and employee table in it. So let us enter. So we have entered DynamoDB database using use Command, click on execute. We've entered, let us show the records of her employee table. Here it is, guys select star 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 say we are adding age column for that. Let's use the command alter table. Table name is Employee, Center, right? Add because we're adding a column, age, an 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 huge 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 right drop column and remove the datatype. Obviously, because you want to just remove it selected and click on execute. Okay, Now command successful. Now use this again to show all the records select star from employee ID and you can see the age column vanished. So we altered our table successfully. So whereas in this video we saw how we can easily altered our table. We altered our column, we added a new column, then we deleted it using alter table command. 31. SQL - UPDATE Statement: In this video, we will see how we can work with the SQL update statement. Soda update statement is used to modify the existing records in a table. We will see an example. Here. You can see we have databases and the databases we have Ahmed DB database, and within that we have employed table. So let us first enter the army DB 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 we know to display all the records of a table, we use select star from, select and execute. So now this is our 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, updates based table name which was our employee. Press Enter. Now use the set command. The whatever new recording you to set you need to mention here. So we need to set the salary. So write the column name, salary. Salary is equal to 7,500. Let's say I'll set it to 9,500. Salad is equal to 9,504. That means we're command where employee name is equal to David. Or we can mention the ideas for because it's for David. So let me mention it. Id is equal to four semicolon. Okay guys, now we will select it. Salary of David is 7,500 for ID for it will update to 9,500, okay, selected and execute one row affected. Now guys, we can select this and execute, or we can mention it again so that it's a step. Since we updated, executed here. And you can see the salary of David updated from semitone or 500 to 9,500. So this is how you can update. You can use update, okay? So you can also update multiple records by joining two of the values. How? So? Let's see, I'll just copy this again. So now I have updated the salary to 9,500. Now let's say I want to update. Let's say I want to change the records 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 l mentioned set EMP named Tom storm. Let's see. I'll set it to Brad comma. Since I'm updating multiple values, I'll set comma. I also wanted to change the city. So I will set city, city lead sales headed 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 row affected. Now let us execute this again. You can see for Id1 we have changed from Tom to Brad and from ABC to j, k, l. 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 SQL update statement to modify the existing records in a table. 32. SQL - Aliases: In this video, we will see what our SQL aliases. 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 exist for the duration of that particular query. The ascii, what is used to create an alias. So let's see how. So herein you can see we have a database Amity be within that we have employee table. So let us first enter the army DB database using the use command, select and execute. So now we have raised there. So now we have a table. As I told you before, employee table, let us see the records of the table. Semicolon, select, execute. 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 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, I'll use the soil, use the keyword. As I mentioned, my result and the command is same. So hearing instead of EMP name, my result text will be visible because it's an alias click on execute, hidden. You can see my dessert. But remember as I told you before, this temporary name remains for the duration of the query only. That means if now I'll use the, I'll execute the following command. That might result would 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 we're having a lot of columns and rows in your table. And if we 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 a squid? 33. SQL - STORED PROCEDURES: 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 the 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 want to 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 here and we have our ammeter DB database. You can see I'm in dB. We have our employee table. Let us see the records into the table using user may TB use Command Space database name, that is MDB is the 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 we will create a procedure, use the command create procedure. Let us give it a name. All the courts use the alias. And by the same command and type the same command, select star from employee, that's it. But I center, right go semicolon. Now just select it and click on execute. You can see Curie executed successfully to actually execute it, ensure that records use the command EXE that is executed, and then the name of and then the name of the procedure, all the courts semicolon and just execute this here. And you can see all the records are visible by just only typing EXE see 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 for 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 the records on the basis of a city name. So for Dart used are the red city. Okay. So we want it for a specific city. So I'll 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 a datatype also. Let's say it's their character when t, it's fine. No. Okay. Now what I'll do, I'll just use the execute command similar way. All records, but it is having a parameter now. The parameter is at rid of city is equal to, let's see, we want for city name ABC. Now let us change it to all records tools so that we have a new stored procedure here. And you can see guys, we created all the courts to procedure and we added the type also to remove data for the city and hearing. We will not execute it. Executed successfully. Let us see now. Know all the records for C, D, a, B, C 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. This is a 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're fetching records that will select employees from a specific city. Specific city with a specific name. Okay, So this is our third one, city we already added. So we will add multiple herein. Let's say EMP name is what we're adding to 55. So we added two parameters here in cities, city, and we are using the end EMP name is equal to the rate of EMP name. So this is, this is what we added herein. What I'll do alert in a similar way at the rate of similar way at the root of EMP name. Okay, is equal to I told you for a specific Eminem. So city abc is having two employees. Will, and you can see will end tom. Both are having city abc. So let's say I'll fetch the term one, 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 selected Execute. You can see we select that specific record. So we have created three stored procedure, e.g. here it is four select star from employee here in for a single parameter, and the third one for multiple parameters. 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 a single parameter and from wind, and then multiple parameters also. 34. SQL - CREATE INDEX Statement: In this video, we will see how we can easily work with a 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 here and we have a database DB. Within that we have a table employee. So let's see. Let's enter the database I'm with 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. Here you can see the following other records. 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 non mentioned the table name, that is our table name is employee and brackets. And the 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 lied. Emp name, okay, semicolon and let's run it. So I ran it 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 of 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 name. Second index by index two. And I'll add multiple columns in it. Let's say Saturday. 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 drop index then the table name that is employee dot the index name. So we're 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 goodie executed successfully that when 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 are indexes? In SQL. We can create indexes and how we can delete indexes. 35. SQL - SELECT INTO Statement: In this video, we will see how we can easily work with selecting two statement to copy data from one table into a new table. So this will indirectly create a backup copy of any of the table. So let's see how here we have our database. So we have our mid DB database. Within that, we have our employee table. So let us go inside the army DB database using the use command, select execute. So we have reached DM me DB database. You can see command successful. Now let us print the records of our employee table. I have selected. Click on Execute. Erin, you can see the records are visible with ID, employee name, employee safety, and price LA. 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 you want all the records, all the columns. So, but here in I'll use into selecting, to select, sorry, into the new table name. So let's say it would be EMP backup to 1,022. Okay. Mentioned that from employee. That means from the table name from employee and that's it. Now I'll just copy it. I'll just select and execute it. And you can see six rows affected. Now let's say I'll select this and execute. And you can see our new table or backup table is having all the records of the employee table and select it. I'll select it and show you again hidden. 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 see. I'll just mention it again. Or employee table so that it's easy, it's easier for everyone to understand. Now, I'll just mention, okay, So star we used, why? Because we wanted all the columns. Now let's say I want specific column for that. I'll just move the star. Let's say we want only MPI, only EMP name and the salary. So EMP 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 type select File Type, select star from my backup. Now let's say what will be visible. At a semicolon? 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 that 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. 36. SQL - SELECT TOP Clause: 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 a database is there under databases. These databases having a table, employee table. Let us face the records of the table using select star from table limb. Select star from table name employee, semicolon, and I'll select it and click on execute. You can see a table is visible. So let's say I want to face the first two records only. So guys, what I'll do, I'll use the select clause. Let us Fetch, use select top. We want to record. So I'll write two star because I want all the records from employee. So this will face the top two records. Older 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 slip top percent clause. So in that you can face the record on the basis of percentage, e.g. so here is our employee table. It is having six rows. So if we want to phase three rows only you can straight away write 50% there. That's it. Let us see, select top. Since we want 50 per cent records, I'll write 50% manually. Star from employee. That's it. It will face 50 per cent records. That means the first three records completely from the employee table. I'll select it and click on execute here. And you can see it has first three records. In the same way, let us write this statement again. I'll just copy it. Let's say, let's say 75 selected Execute. Now out of six Accord, it has shown us 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 50% records, or let's say tough 25% records wherein salary is greater than 7,000 are, let's see what will happen. There is an error. Why? Because we added a semicolon here. It's fine. Now, let us select this and click on Execute. Hidden. You can see it has shown us 25% record. That means a single record. Our, I'll modify this statement again to make it simpler for you. Let's say I'll just write select star. From employee salary greater than 7,000. I'll select a new click on execute. You can see two records are no visible. The top two records which are having salary greater than 7,000. So it is 7,500.8 thousand because these are above 7,000 and these are the first two. I hope you understood. So right in this video we saw how we can work with a select top close to return a specific number of records. 37. SQL - Backup a Database: In this video, we will see how we can back up our database on SQL Server. So at first we created a database, I'm in dB, then we created a, then even created an employee table in it using the following command. After that, we added records in a table. So here in you can see our DB database with employee table is having the following records. So we need to backup their entire database. So it's very easy. You need to execute any command that is backup, that is backup database. The name of the database I'm in 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 a member AD in a different directory always, because in case if your directory which is having MSA squealed crash, then at least you have the backup of your database. So let us see the path. So herein let's say we created a stroke COPD backup folder. You can create any folder. I'll just copy this. After that, I'll mentioned the path here. Paste. Noel mentioned the name of the file. My backup dot vk is the extension. Now everything looks fine. Now let us execute and see what will happen. Select and click on execute here and you can see processed backup database successfully processed. So it is also showing the time and the completion time. Now let us see here and 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 B2C extension is here because it will allow you to easily restore your bagged up database. So guys, In this video, we saw how we can easily backup our database on MS SQL server. 38. SQL - Views: In this video, we will see how we can work with SQL create view statement to create a view. So what is a view? A view in SQL is basically a virtual table, which is based on the results set of an SQL statement. So if you want to show our 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 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 a multi-bit database you can see and an employee table. Let us enter the database using the use Command US based database name, that is MDB 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 and you can see a record is having ID, employee ID, employee name, employee safety, and 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 into the 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 use the alias as. Now use the select command. We wanted employee name and city. So EMP name and city. From mentioned the table name, that is our employee. Weird. City is ABC. Okay, now we have created a view semicolon. Select it and click on execute commands completed. Now to display, use the same select star, okay, in that, in this case we'll be using select star from our view name because we want to get the view. I'll just copy this two 0s. 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 want it 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 star from employee. Let's say we're viewing records with salary greater than 7,000. Okay? So what we will do, Let's write salary above 7,000, 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 7,000 from employee table wherein so what condition we need to mention? We need to mention under view wherein salary is greater than 7,000. Okay. This looks fine. I'll select it. We have a new view, no execute command completed. Now to display it, I hope you remember its select star from your view name, that is the following. It will come on it's own. You can see salary above 7,000. It's semicolon select and that's it. So we have all the records of employee with salary greater than 7,000. Okay guys, this is what we display it using a view. So we created a view now, now we will see how we can remove. So now we have to use heroin. First, second, What? Okay. Now let's we to remove the employees. Abc view, how we can remove it for that guys. Use the drop view command, right, drop view and a union, that union was. Here it is, employability, select and execute. So you can see commands completed successfully. Now if you try to display it, nothing will be visible. Looking at, you can see, you can see invalid object name. Okay? So this is why I told you we have dropped this view. So guys, In this video we saw what are we using SQL. We also saw we can create a view as well as how we can drop a view. 39. SQL - Drop a Table: In this video, we will see how we can easily drop a table in SQL Server. So right now we have a database, DB. Let's open it. Commands completed successfully. Here's our database. So guys, in that I'm a DB database, we have a table. Let's see which one 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 ID, employee name, employee safety, and employee salary. Now guys, let us drop the table. So to drop the table means to delete all the records from the table, as well as to remove the existence of the table. So let us use the command drop table, base 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 you deleted it. We dropped means we deleted it. Now, I'll try to get the records from the table, but the table doesn't exist. Now, when I 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 a 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.