SQL Introduction: Introduction and Overview

Abstract

Advance your database management skills with a relational database management system (RDBMS) used in modern database systems such as Microsoft SQL Server, Oracle, MySQL, and Microsoft Access, and understand the concepts of SQL. About This Video: Understand the SQL query process and the role of databases in real-world applications Create new databases and tables and perform operations to manipulate records using SQL queries Master SQL querying and write complex query statements to enhance your understanding of MySQL. In Detail: SQL Server is a relational database management system (RDBMS) developed by Microsoft. SQL Server also allows user-defined composite types (UDTs) to be used. The SQL Server services operate within SQL server instances, allowing people to do machine learning and data analytics. In this course, you will understand the difference between SQL and NoSQL. You will query SQL and learn about built-in SQL functions. You will create and modify databases and tables using the CREATE DATABASE and CREATE TABLE statements. You will create, select, and modify records and create constraints in SQL. You will advance to create indexes, drop tables, and databases in SQL. You will learn about operators (AND, OR, NOT, LIKE, BETWEEN, IN, LIKE), functions, statements, and constraints in SQL. You will look at stored procedures and aliases, and learn to back up databases in SQL. You will use the WHERE clause to query and learn about the SELECT and DISTINCT functions. You will arrange data using the ORDER BY keyword and ASC and DESC keywords. After completing the course, you will thoroughly understand SQL’s features and components. You will create complex databases and maintain existing databases. Finally, you will acquire complex SQL query capabilities and write complex queries. All resources are available at: https://github.com/PacktPublishing/SQL-Server-Course-for-Beginners-with-100-examples

[00:00:00]In this video, we will learn what is Esque, it's introduction and overview. [00:00:05]So let's begin. At first, we need to understand what is RDBMS? Rdbms [00:00:10]relational database, Magma system to understand RDBMS [00:00:15]here. And you can see we have a table since childhood, we are creating these tables with rows and columns, right? [00:00:20]So this is what we call RDBMS. That is a relational model. Relational means relation [00:00:25]between rows and columns, okay? These rows and columns, this is what we have shown [00:00:30]here, DBMS. It's a form of a table. That is, a table has rows and columns, and [00:00:35]a database has group of tables. That means a relation of rows [00:00:40]and columns is called table. This is what we call DBMS, Relational Database Magnus system, [00:00:45]okay? This is the basis for SQL. So now you would be wondering the difference between I [00:00:50]DBMS and DBMS. Dbms is just an advanced form [00:00:55]of DBMS, okay? It is having relational DBMS. That means a relation between, [00:01:00]relation between rows and columns. Dbms is a basis for [00:01:05]SQL or Mysql, C and other related databases. [00:01:10]Dbms. I've told you before, it stores data in the form of tables. And tables is having rows and [00:01:15]columns here, and we have four rows. Rows are always horizontal [00:01:20]entry, okay? You can see columns we are having five [00:01:25]columns are vertical entries, okay? This is what we have shown here. [00:01:30]Now, what is SQL? Sql is a structured query language. Rdbms is the basis for [00:01:35]SQL that I've told before. That means it will store data in RDBMS. [00:01:40]In RDBMS found that is in the form of rose and columns. Okay? And it is governed [00:01:45]under ANSI, that is a Medical, National Standard Institute, as well as International Organization [00:01:50]for Standardization. These two DPM's have been developed [00:01:55]by Microsoft. We'll be covering these two in SQL. Okay. This [00:02:00]is the current version, Microsoft Qs 2019 developed by Microsoft. [00:02:05]It is used to create and maintain database, to create tables, database, and maintain it. Within [00:02:10]that, Microsoft also provide SSMS, That is SQL Server, Madam Studio, here it [00:02:15]is. We'll be using this to work on quel. You can say it is a UI for [00:02:20]Quel that will allow you to easily create database work on operations clauses, [00:02:25]operators, and other stuff in Quel. Hello guys. In this studio we will learn about a difference between [00:02:30]SQL and N. We will cover this difference with examples. [00:02:35]Let's begin. Quel follows the relational [00:02:40]model. That is the DBMS model. That old school way of creating tables with rows and columns. [00:02:45]This is what Quel supports. No SQL is completely different. It supports various [00:02:50]formats like document and storing data in the form of key values, [00:02:55]graph nodes, and other stuff. Sql came in 1970, so you can say [00:03:00]more than 50 years, it's been around and no CL just came in late 2000. [00:03:05]You can say examples. Sql is basically I DBMS. All the databases [00:03:10]related to DBMS will get covered under Quill, [00:03:15]Microsoft Oracle. All are part of DBMS, and all these are [00:03:20]based on DBMS. Q The biggest example, one of the most trending [00:03:25]databases right now is Mongodb, which is document based that [00:03:30]we can also store data in the form of key values using the Redis database, using white [00:03:35]columns, using Cassandra and H base. The usage SQL is generally used to [00:03:40]store data in the form of Rosen columns. Let's see an example. [00:03:45]At first Quel, you can see the kill example we have shown my here. As I told [00:03:50]you before, kill saves data in the form of rows and columns. It's based on DBMS. That [00:03:55]means based on relational model relation between Rosen columns in a table. Here you can [00:04:00]see the relation between Rosen columns, these are our columns. 12345, [00:04:05]employee ID, first name, last name, employee, employee on, and these four [00:04:10]rows, okay? As I told you, tables are having [00:04:15]Rosen columns and a database is having a collection of tables. This is what we call SQL. [00:04:20]That is, as I told you, tables with fixed rosen columns is what we call [00:04:25]a model for SQL that is a relational model. Okay. Now, talking about Mongo [00:04:30]V, I told you before, it saves data in the form of documents, key value pair that is [00:04:35]completely different from SQL. This is the document based Mongo database. [00:04:40]We have four columns here, ID, department, department manager, and department rank. You can see [00:04:45]the database here under No, CQL, under Mongodb, documents have fields [00:04:50]and collections is having documents like tables is having rows and [00:04:55]columns in this, collections have documents and fields and database is a group of collections in the same way [00:05:00]in Sql, database is a group of tables. This works for Mongodb, but it saves [00:05:05]the data in the form of in document based form or key value form. Okay? [00:05:10]This is what we have shown here. Document based key value form, white column form, and graph form. In no [00:05:15]qual, completely different form or old school, conventional qual [00:05:20]schemas. It is rigid and it is flexible. Flexible database, you can say [00:05:25]you can store a data in all these forms, different forms, Document key [00:05:30]value, graph, multi record, asset transactions. It is supported by Q [00:05:35]here, no, L doesn't support it, but Mongodb will still support it [00:05:40]at. Transactions are basically an acronym for atomicity, consistency, reliability, [00:05:45]and durability. These are basically database operations. This CID [00:05:50]is known as a properties, or you can say asset transactions. In this [00:05:55]video, we saw how we can differentiate SQL and Nosql. For [00:06:00]SQL, the biggest example is Microsoft SQL and No SQL. Mongo DB is [00:06:05]example guys. Thank you for watching the video. In this video, we will see how we can easily [00:06:10]download and install Microsoft SQL Server 2019 [00:06:15]server Madam Studio. For that you need to go to the browser. [00:06:20]Let's first download Kill Server on Google [00:06:25]Type Chill Server Presenter. [00:06:30]As you know that Esci server is owned by Microsoft. [00:06:35]This is the website you need to click on this, Microsoft.com [00:06:40]here. Let's click on Downloads.

[00:06:45]Now we have reached the download section. Here you can see [00:06:50]Developer and Express Edition is visible here. We will be downloading [00:06:55]2019 Express Edition. Here it is. Click on download. Now [00:07:00]it is downloading on the left. Now [00:07:05]we have downloaded it. You can double click here or you can just click on the arrow and click on Open. [00:07:10]Minimize this.

[00:07:15]Now you can see [00:07:20]the Express Edition installation type is visible. Basic [00:07:25]download media through this, you can download the files and you can install [00:07:30]later. Custom installation means you can select what you want to install or [00:07:35]what you want to leave. Here is the basic installation for Bigness. Click on it. [00:07:40]Now the license terms is visible. You need to [00:07:45]accept here.

Here you need [00:07:50]to specify the installation location. That is where Microsoft Esci server will get installed. [00:07:55]It will get installed on a drive program files and a new folder will get created. If you want [00:08:00]to change the location, click on Browse. From here, you can select the location where you need [00:08:05]to install Microsoft Esci server. I'll keep the same. Click on Cancel. [00:08:10]The download size is 266 M B. You just need to click on install [00:08:15]here. Now the installation packages are getting [00:08:20]downloaded.

[00:08:25]So guys, now [00:08:30]the download completed. It is now installing.

[00:08:35][00:08:40]Guys, [00:08:45]we have installed a skill server. The first is our instance [00:08:50]name, that is the address connection sting. The second one [00:08:55]is a log folder. I hope you know what is a log log. Whenever you will start [00:09:00]cull server or even if there is an error, everything will get reported under log [00:09:05]folder. The next one is wherein we installed [00:09:10]server Express Edition. This is the Resources folder. Now we can straight [00:09:15]away connect. Now from here, let's connect [00:09:20]here, It got connected. Now you can see the Cerver visible. [00:09:25]That means we have installed server correctly. Now going [00:09:30]further, here it is. Giving an option to install SSMS. That means kill [00:09:35]saver, Magma Studio. Kill Server. Made Studio is a software application [00:09:40]or a tool through which you can easily work on Kill Server. You can easily create databases, [00:09:45]tables manage, and you can also work as an administrator there itself. [00:09:50]Let's install. Click on install SMS.

[00:09:55]Yes, open with any browser.

[00:10:00]Now, here in need to download kills Mama [00:10:05]Studio. Here is the link. We will also add this link in the description. [00:10:10]Click on it, it will download. On the left, you can see it's around 635 [00:10:15]M B, so it will take ample amount of time [00:10:20]guys. Now you can see we have downloaded the setup [00:10:25]for Eskimo Studio. Click on the arrow open. [00:10:30]Let's minimize this. Now guys, the path [00:10:35]is visible to install kills Manu Studio. Here is the location. If you [00:10:40]want to change it, you can change it from here. By clicking Change, you can change [00:10:45]the location from here. And click on Okay. But I'll be clicking on cancel. [00:10:50]Esclava will get installed in this folder. Click [00:10:55]Install.

[00:11:00][00:11:05]Guys, now you can see all the components [00:11:10]have been installed. Now click on Close. Now [00:11:15]let's start it. Go to Start, type a [00:11:20]skill server. Here it is, Manage Studio. Click on it.

[00:11:25][00:11:30][00:11:35]So here is a skill server, Mane Studio. In the upcoming videos, we'll see [00:11:40]how we can connect and create a new database table and how we can work more on [00:11:45]Kill Server guys. In this video, we saw how we can easily download and install [00:11:50]Kill Server 2018 on Windows Sten. With that, we also installed a tool kill [00:11:55]server, Manno Studio, which will help us in working on Kill Server. [00:12:00]In this video, we will see how we can easily download and install Microsoft [00:12:05]Chill Server 2019 on Windows 11. It's very easy. Let's begin at first, [00:12:10]go to the browser. I'm using Chrome. You can use any browser here in type, [00:12:15]Microsoft Escalera 2019.

[00:12:20]On pressing Enter, you can see the official website is visible, Microsoft.com Click on it. [00:12:25]Now here you can see click on Downloads. Here [00:12:30]on Downloads, you can see different versions for a skill [00:12:35]server are visible. We will focus on the following two, [00:12:40]Developer and Express. You can see it is a free edition, both of them are free. [00:12:45]This is for development and test database in a non production environment and this is for basically [00:12:50]development and production environment. We'll go for the following. Click on download, not to download it. [00:12:55]To download, started, it's an installer, you can see six MB only [00:13:00]to download completely. Double click on it and [00:13:05]minimize this. Now you can see it is asking for the installation [00:13:10]type for cal server 2019. Download media custom as well as Basic. [00:13:15]We'll go with the basic installation. Click on it. [00:13:20]Here in the license terms are visible, you can read it and click on a set. [00:13:25]Now here you can see it will take 266 P, and it will [00:13:30]download in the following location. If you want to change the location, you can click on Browse, but the default [00:13:35]one is perfect. Go for it, Click on Install.

[00:13:40][00:13:45]Now it is installing

[00:13:50][00:13:55][00:14:00]guys. Here you can see we have successfully installed. Here is the connection sting [00:14:05]is the lock folder. So lock folder where all your error messages [00:14:10]and informational messages appear while it gets installed. This is the installation [00:14:15]media folder, installation resources folder here in all the resources of Eschel server [00:14:20]will get saved here. Guys now will also install SSMS which is [00:14:25]Esko Manno studio software application that will give you a U I to [00:14:30]work with Quil to create a database table and work on it. Basically [00:14:35]used for configuring, managing, and administrating all the components of MSS Quil. We'll work on it also. [00:14:40]Before that, we will just click on Connect Now. To at least connect Quil for the first time, click on Connect. [00:14:45]Now guys, here you can see if the following is visible. That means we have successfully [00:14:50]installed Esclava. I'll minimize this, and now I'll install SSMS. [00:14:55]Click on it to install. Here you can see [00:15:00]it directly to a new link that is the official Microsoft link [00:15:05]where it went to the download page on itself and download. [00:15:10]Sms link is visible, you just need to click on it to install the server. Manus Studio, I'll [00:15:15]click on it to download, started at 06:53 MB. Let's [00:15:20]wait guys here. And you can see we [00:15:25]have downloaded the S studio X file. Double click on it [00:15:30]to install. Minimize this.

[00:15:35]The installation [00:15:40]started. Server man studio will get installed in the following. If you want to change [00:15:45]the location, you can change from here, but I'll keep the same. I'll click Install [00:15:50][00:15:55]Guys. You can see the set up is complete. Click on [00:16:00]Close. Now guys, Latest Open Esque Server Management Studio. Go to Start. [00:16:05]Click here. Click on Start here, Type [00:16:10]Management Studio,

[00:16:15]here it is visible here, we just installed it. Click on Open [00:16:20]Guys here, and you can [00:16:25]see the server type the server name and authentication. You just need to click [00:16:30]on Connect here.

[00:16:35]Coa connected successfully. Here you can see this is the object of Spero [00:16:40]Explorer, wherein all the databases tables will be visible. When I'll click on the [00:16:45]drop down here and you can see we are having system database only. It is not having user database [00:16:50]because we have installed for the first time to create a database, you can stay awake, right click and [00:16:55]click on new database. But let us go for a query. [00:17:00]Click on new query.

[00:17:05]Here we will use a query to create a new database quickly. [00:17:10]Let me zoom it to 150. Okay, now let us take the command [00:17:15]to a create database. Database name, create database. [00:17:20]You need to always write to create a database here. And you can add any name for a database. [00:17:25]Let's say I'll add a bit deb, semicolon, [00:17:30]okay? Now double click and select it and just click on Execute here. You can see Execute [00:17:35]here. You can click, or you can use the five key on keyboard. [00:17:40]I'll just click on Execute. I clicked, [00:17:45]and you can see commands completed successfully. Now I'll just click on Refresher. [00:17:50]You can see we have our new database, okay? This is how you can create a database. [00:17:55]Now I can just click on the drop down, and you can see the entire scheme is visible. We have nothing [00:18:00]because we just created a database. You can see, you can right click and straightaway [00:18:05]create a new table from here if you want to create a new table or a new database or work [00:18:10]on all the work on the closes in the operators and other stuff, you can refer the [00:18:15]link in the description of this video wherein I provided the complete tutorial guys. In this video, we saw [00:18:20]how we can easily download and install Quills and Esser Management Studio on [00:18:25]Windows 11. In this video, we will see how we can fix the following error. I'm getting [00:18:30]a lot of comments regarding this, that what is the fix of this error? This error has started occurring in [00:18:35]the year 2022 only. Okay. Required file could not be downloaded. This could mean the version [00:18:40]of the Instalo is no longer support it. Let's see how we can fix it. To fix this error, go to start [00:18:45]type Windows powershell. [00:18:50]Click here it is. Click on Run as Administrator. Okay, the second [00:18:55]one. Now the following power shell is visible. We can increase the front [00:19:00]at first, right properties. Go to front. And let us change it. I'll change [00:19:05]it to 28. Click on. Okay, now we need to write two commands here so that we can fix the [00:19:10]chiller error. Let's see the first command. I've also mentioned these commands in the description of this video [00:19:15]after copying the commands right click and paste. Okay, [00:19:20]after what you need to do, you need to set the second command right past the second command. [00:19:25]That's it. Now you need to close this. After that, restart the system and [00:19:30]try installing clover again. In this video, we will see how we can easily create a new database [00:19:35]using Microsoft Kill server. Let's quickly see go to Start Type [00:19:40]Kill server here. And you can see Clover Manga [00:19:45]Studio visible SSMS. Click on it to open.

[00:19:50][00:19:55]Now guys click on Connect [00:20:00][00:20:05]Guys here. And you can see the Object Explorer is visible here. In databases, [00:20:10]there's no user database, Only system databases and database snapshots are visible. [00:20:15]To create a database using command, click on new query.

[00:20:20][00:20:25]Here we need to type our query. But let's say I'll zoom it first. It's 100% I'll go for [00:20:30]one, 50% Let's write the command create database. [00:20:35]Name of the database, Let's say the name of databases Am DB semicolon [00:20:40]selected by double clicking. Now click on Execute. [00:20:45]This will create a database [00:20:50]here and below you can see commands completed, successfully [00:20:55]executed, visible here. Now click on Refresh here to display a database [00:21:00]refresh. It has been refreshed. Go to [00:21:05]Databases Now here and you can see our amid DV databases visible. When you will [00:21:10]go to dropdown, nothing will be visible. Tables will be empty because we haven't added any table [00:21:15]guys. In this video, we saw how we can easily create a new database using MS Sqlserver. In this video, [00:21:20]we will see how we can easily create a new table in Esclava. Here in right now, [00:21:25]we created a new database using Create database. Database name. Here in our database name [00:21:30]is Amid. In the Object Explorer, you can see under Databases we have Amid database. [00:21:35]Now we need to create a new table. We'll be creating an employee table for that guys. [00:21:40]First, we need to use amid, deb and enter the database, [00:21:45]type space. Database name, selected it, and now click on Execute. [00:21:50]You can see command completed successfully. That means we have entered the [00:21:55]database amid. Now we can easily create a new table. Guys, to create a [00:22:00]new table in Esclava, we use the create table command. Let's write. [00:22:05]Create then the table name. Let's say the table [00:22:10]name is employee. Open the brackets, close it, and use semicon. [00:22:15]Inside this, we need to add our columns. That is which columns we need to add [00:22:20]with its type. At first I'll be adding ID. Id [00:22:25]will be ain't right. It's fine. Recent EMP [00:22:30]name. That would be employee name. The same will be in care. [00:22:35]Let's add the value. Then comes [00:22:40]City Care, only 255. [00:22:45]Then the last, we can add a salary for our employees, which will be in [00:22:50]end for obvious reasons. Everything looks fine guys. Now [00:22:55]we have added four columns. I'll select it. We could have also added primary key and [00:23:00]other constants in it that means not null check constant default. But I'll explain [00:23:05]all these in a new video. Right now I'm just creating a basic table. I'll [00:23:10]select this and click on Execute. After clicking Execute, you can see commands [00:23:15]completed successfully. That means we have successfully created a table. Now guys, on the left [00:23:20]object explorer, we need to click on Refresh. The same new [00:23:25]command we executed gets executed here itself. Now I'll just click on a Mid, [00:23:30]Aren't clicking. I'll click on Tables. Now you can see our [00:23:35]table created successfully.

[00:23:40]Here are the columns. [00:23:45]The same four columns guys. In this video we saw how we can easily create a new database [00:23:50]and table in skill server. In this way, you can create a new table using create table [00:23:55]command. Guys, thank you for watching the video. In this video, we will see how we can insert records [00:24:00]into a table in Quill server. Here in first we created a mid database [00:24:05]using the following command. Then we created a table employee. You can see the database we [00:24:10]created Amid. Here it is. Under that we created employee table. The employee [00:24:15]table we created is having four fields. The first one isn't and the last one is that is in [00:24:20]ID and salary is int integer, an employee R, R [00:24:25]being given C types data type. Now let us insert a record for inserting [00:24:30]records will be using Insert Into Command. Let's write, he [00:24:35]wrote Insert Into Employee is a table name as you can see [00:24:40]here. Now let us mention the fields one by one D. [00:24:45]When you'll type, it will automatically show under auto, complete, present, [00:24:50]select, and presenter, salary, [00:24:55]select, presenter. It's easy. Now write values and insert [00:25:00]records one by one. At first, we will insert only a single row for ID. Let [00:25:05]us write one. Let us enter in codes Employee, [00:25:10]let us write to [00:25:15]City. Let us write a sample, ABC, salary. Let us write any [00:25:20]value in it. Okay, it's [00:25:25]fine, semicolon. Now we will select this and click on Execute to enter the first [00:25:30]row in employee table. Here you can see the messages, [00:25:35]drag it, you can see one row effected completion time. [00:25:40]That means we have successfully added records in the table. Now let us [00:25:45]see the record we have added using the Select Star Command. Select Star from table table Me is employee [00:25:50]semicolon selected and execute after [00:25:55]executing. In the results section, you can see we successfully added one [00:26:00]record in the table. Okay, Now for another record, let us implement [00:26:05]the command again.

[00:26:10]Let us add the second record.

[00:26:15][00:26:20]Okay, now, executed, selected, click on execute, one reflected. Now [00:26:25]let us mention the command again. Select star from employee. That's it. Selected [00:26:30]and execute, that's it. Now you can see we have inserted two records, ID, employee names, city and salary. [00:26:35]In this way, guys, you can insert records in your Microsoft Skills, [00:26:40]our table using the insert into command. In this video, we will see how we can [00:26:45]select data from a database. That means if we want to fetch records from [00:26:50]a table in a database, you can easily do it using the select statement. Right now [00:26:55]we have a database Amid within that we created employee [00:27:00]table. Now let us see how we can fetch all the records of the employee table. It's very easy. [00:27:05]First, we will enter the B database using the use command. Click [00:27:10]on execute Q executed successfully. That means we have entered db database. [00:27:15]Now we need to fetch all the records from the employee table. We will use Select Star from [00:27:20]employee. Semicolon. Select is our statement, [00:27:25]employees table selected, and click on Execute. [00:27:30]On clicking Execute, you can see all the records from our employee table [00:27:35]are visible. Now this is how we feed all the records. [00:27:40]Let's say you need to fetch the records for specific columns. That means let's [00:27:45]say you need to only get the ID and employee name. For that you select [00:27:50]mention the name of the columns. That is, select ID, [00:27:55]EMP name from table Name, employee [00:28:00]that's selected, and click on Execute.

[00:28:05]Here you can see only ID and EMP [00:28:10]name we faced from the employee table. This is how you can use the Select command. [00:28:15]In this video, we saw how we can use the Select statement on [00:28:20]Microsoft Skill server. We faced all the records and we also saw how we can switch specific [00:28:25]column records. Thank you for watching the video. In this video, we will see how we [00:28:30]can fetch the distinct records in MS Skill server. [00:28:35]Right now we have amb database, that's why we use amb command so [00:28:40]that we can enter the Ab database, our database. Within that, we have created [00:28:45]an employee table with some records. Let's say first we will fetch all the records of the employee table [00:28:50]using the Po Select star from employee [00:28:55]semicolon. Now selected. We saw the same in the previous [00:29:00]video. Clic execute below the results. You can see these are [00:29:05]our records. But guys, what we want, we need to fit the distinct records. [00:29:10]Here you can see the city of some of the employees are same. [00:29:15]If we want only distinct, that means different records, we need to use the distinct [00:29:20]command. Let's say select distinct [00:29:25]to the column name, which is having duplicate records. So that we can display [00:29:30]the records without duplicate. It is select distinct city [00:29:35]from employee semicolon. Now let us select [00:29:40]this and click on Execute. After clicking Execute, only city will be visible, city column, [00:29:45]but the repetitive values won't be visible here. Repetitive values are ABC and Q. [00:29:50]You can see it is repeating here. Qr is repeating here, and ABC is repeating here. I click [00:29:55]Execute Here, you can see the distinct values of city is visible because we use the distinct [00:30:00]command. Guys, in this video, we saw how we can use the display [00:30:05]on different records. In this video, we will see how we can filter records [00:30:10]based on a condition using were clause. Right now we have our amid [00:30:15]database. We have entered it using the following command, use space, database name. After clicking [00:30:20]and execute, we entered it. Inside that we have a table employee table. [00:30:25]Let us first see all the records of the table using the Select star [00:30:30]from table name. Table name is employee, that's it. Semicolon. [00:30:35]Select this and click on Execute. On clicking [00:30:40]Execute, you can see we have four columns, ID, [00:30:45]EMP, name, city, and salary. Let's say we need to [00:30:50]fitch records on the basis of some condition. We want to fetch the employee name with [00:30:55]salary 8,000 Let's see how here at [00:31:00]first display on the EMP name, Select EMP name from [00:31:05]employee. Now let us write the condition. Or we can also write like this. [00:31:10]What I said, where the salary is [00:31:15]is equal to where the salary is equal to 8,000 Here on the employee name [00:31:20]will be visible for salary 8,000 That means Emma will be displayed, Select this and click [00:31:25]on Execute. Now you can see on Emma is displayed. No. Let us modify [00:31:30]the query. Now select Star from employee [00:31:35]salary is equal to 8,000 That means all the records of the employee name Emma will be visible [00:31:40]because we have set the condition as salary 8,000 I'll select [00:31:45]Execute here, you can see the red on the role [00:31:50]of the employee name. Emma is visible. Complete, complete record because we have set [00:31:55]the condition as salary 8,000 Now let us display this again. [00:32:00]You can see what we need to fetch. Let's say we need to fetch [00:32:05]a text record because right now we fetch the numeric record. [00:32:10]Let's say I want the record of the employee with name as Jenny. [00:32:15]Let's say we [00:32:20]name as Jenny, that means EMP, name is equal to Jenny. Okay, [00:32:25]But we need to add the codes for texting for string because the [00:32:30]type was were here before that we went for salary which wasn't. No [00:32:35]codes were required in that everything looks fine. Select click [00:32:40]on Execute. Now only Jenny records visible employee name Jenny. [00:32:45]You can see all the records of the employee employee name Jenny visible because we use the C [00:32:50]guys, now we can move further and change the condition. [00:32:55]Before that, let us display. The records of the employee table again. Now let's say I [00:33:00]want the records of employees whose salary is greater than 7,000 Let's say [00:33:05]what I need to write here, the field name salary is [00:33:10]greater than what I said, 7,000 [00:33:15]I can also set equal to here the greater than equal to operator, okay? That means [00:33:20]salary greater than equal to 7,000 The output greater than equal [00:33:25]to 7,000 means equal to 7,000 Also, the output should be to Emma, [00:33:30]David Henry. Let's see execute [00:33:35]here and you can see Tom David Henry. Because salaries of these employees [00:33:40]are more than, are greater than equal to 7,000 Thank you for watching the video. [00:33:45]In this video, we will see how we can easily order records. In Esclava, [00:33:50]we use the autobio, the records in ascending or descending order. [00:33:55]The default for auto by keyword is sending order. Ascending [00:34:00]order means arranging from smallest to largest, like 12345, or [00:34:05]you can say for alphabets, ABCDE, that is from smallest to largest. Here [00:34:10]we have our amid database. As you can see here, amid. We have entered it using the use command. [00:34:15]Here it is, amid database. Inside that, we have a table, [00:34:20]employee table. We have inserted some records in the employee table. Let us see [00:34:25]the records first. I hope you know to display all the records we use, select Star [00:34:30]from table name, semicolon and just selected and click on Execute. [00:34:35]Here you can see guys, the records of the employee [00:34:40]table are visible below. In output, we have four fields ID, employee [00:34:45]name, as well as employee salary. Let's say we need to [00:34:50]order the records in a sending order on the basis of salary. Which command we will [00:34:55]use? I told you order by command. Let's say we need to display all the records [00:35:00]after sorting here, I'll type select star [00:35:05]from table, that is employee presenter, type the command order [00:35:10]by and we need to arrange it in ascending order, That's the default. Just keep as it [00:35:15]is. And we need to arrange it according to salary. I'll just mention salary on [00:35:20]typing. You can see the salary is visible here. Just select it and semicolon, [00:35:25]all the results will be visible on the basis of salary [00:35:30]arranged in ascending order. That is, ascending order means from smallest to largest, select [00:35:35]this execute here you can see the least [00:35:40]salaries on the top. Then comes the next one. And the last one [00:35:45]will be the employee with the highest salary, A guys. Now you [00:35:50]need to do the reverse for that type DESC [00:35:55]for descending order that is largest to smallest. Now it will be opposite, [00:36:00]Henry will be on the top, just selected execute [00:36:05]Here you can see guys Henry is on the top and we have arranged it [00:36:10]according to descending order. That is from largest to smallest 9,500 then 8,000 [00:36:15]7,500 in the same guys. We can also order according to different [00:36:20]columns, several columns. Why we need it? Because let's say City is repeating [00:36:25]here, QR is for two employees and ABC City is for two employees. Let's [00:36:30]say we are arranging accordingly the city according to city. So we can set multiple columns [00:36:35]so that we are able to solve this multiple city order issue according to salary. [00:36:40]Let's see

[00:36:45][00:36:50][00:36:55]here guys, we have used [00:37:00]city as well as the salary in order by, Let's see what will happen, selected [00:37:05]and execute. Here you can see we have ordered [00:37:10]in ascending order because we haven't mentioned anything full sending, it has arranged it accordingly, [00:37:15]city and salary. In case of repeated values repeated, [00:37:20]it has arranged according to salary. The issue is no resolved guys, [00:37:25]In this video we saw how we can easily work with order by keyword sort the records. In [00:37:30]this video, we will see how we can easily work with the SQL operator. The operator [00:37:35]used to filter records based on more than one condition for. And you can say you can display [00:37:40]if all the conditions separated by the operator are true. [00:37:45]That means all the conditions fulfill, all the conditions satisfy, and all of them are true. [00:37:50]Guys, here we have a Mi database. Within that, we created a [00:37:55]employee table. The following are the columns of the employee [00:38:00]table. Now let us see the records of the employee table. First, select star from [00:38:05]employee, from table, semicolon select. [00:38:10]Execute on executing. Let us see the output [00:38:15]here and you can see ID, employee name, city, and [00:38:20]salary columns. Let us use the end operator and include [00:38:25]more than one condition separated by end operator here. And we will display employee [00:38:30]records. City is ABC and salary is 7,000 [00:38:35]This will allow us to fetch specific records. We'll be including [00:38:40]both the conditions that will display the employee name to, let's see, [00:38:45]Select star from employ. Let me write it again.

[00:38:50]Now remove the semicolon. Now I'll add the [00:38:55]conditions using the clause. City I told you is ABC,

[00:39:00]we're using the operator and [00:39:05]salary is equal to specific salary, 7,000 [00:39:10]That's said at semicolon. And let's execute and see the [00:39:15]result execute. You can see we are able to fetch the salary [00:39:20]with 7,000 and city with ABC for the employee named Tom. [00:39:25]We could have also set the values for, let's say [00:39:30]I'll execute it again to display all the records. [00:39:35]Let's say I want to display the specific employee name will with [00:39:40]the city ABC. The city is ABC only. But the second condition also should satisfy [00:39:45]here. In let's say I want to display 67 double zero. What I'll do, I'll just mention less [00:39:50]than everything looks fine. Selected Click on Execute. Here [00:39:55]you can see the Will employee name is visible because the salary is less than 7,000 [00:40:00]guys. In this video, we saw how we can easily execute the end operator in skill. So [00:40:05]it satisfies both the conditions separated by the end operator. In this video, we will see [00:40:10]how we can work with the operator. The operator is used to filter records based on more than one [00:40:15]condition. It displays the record if any of the conditions separated by the operator [00:40:20]is true, any of the condition, both the conditions are for hand operator and [00:40:25]only a single condition should be true for operator. Let us see [00:40:30]an example. We have a Mib database. We have entered in it. Within that, guys, we have our [00:40:35]employee table. Within that, we have four columns in the employee table. [00:40:40]Let us see the records of the employee table. We already inserted it. I hope you know, to display [00:40:45]the records, select Star from Table Name. Our table name is [00:40:50]employee. Here it is selected, and click on Execute. [00:40:55]We have executed the query. Here you can see [00:41:00]we have employee ID, employee name, city, [00:41:05]and salary here, and we'll be implementing the operator. Let's say [00:41:10]we need to fetch all the records of the employee whose name is either Jenny or [00:41:15]Henry. Let's say, [00:41:20]I'll mention the command presenter, [00:41:25]use the clause, EMP name is equal [00:41:30]to, I said Jenny or EMP name [00:41:35]is equal to Henry. [00:41:40]Both the records will get faced because both the records [00:41:45]are true. Even if one was true it would have returned the records without any error, her [00:41:50]semicolon. Now I'll selected execute [00:41:55]records for Jenny and Henry are visible. Now guys, I'll just copy this [00:42:00]command again. Let's say I'll mention it.

[00:42:05]There's no employee with the name chief. If you remember, I'll show the records again. [00:42:10]Execute. You can see there's no employee with the names chief. [00:42:15]Still this command will execute because we're having operator that means only one condition [00:42:20]should satisfy. Execute. Here you can see only one condition satisfied [00:42:25]Jenny second field. But there's no error because we're using operator. This is the benefit of using an operator [00:42:30]guys. In this video we saw how we can work with operator in Esclava. [00:42:35]In this video, we will see how we can work with the operator in esclava. The [00:42:40]operator is used to display a record. If the condition is not true, mind it not true. [00:42:45]Okay, let's see an example. We have a database here, as you can see here [00:42:50]in Esclava with a database. We have employee table with [00:42:55]four fields. As you can see here, we already inserted records in the employee table. [00:43:00]Let us see them. I hope you know to display the records from a table, [00:43:05]Select Star is used to display all the records from table name. Our table [00:43:10]is employee, as you can see here, Employee. [00:43:15]Now I'll select this and click on Execute to display all the records. [00:43:20]Here you can see Cy executed successfully and here are all the records [00:43:25]I told you. Employee ID, employee name, employee city, and employee salary. Now guys, [00:43:30]what we need to do, we need to implement the operator. [00:43:35]To implement the operator, let's say we want to fetch. All the [00:43:40]records of the employee where salary is not 8,000 Except [00:43:45]Emma, all the records will get displayed because we're using not. Let's [00:43:50]see. I'll copy this again. Select [00:43:55]star from employee [00:44:00]salary is equal to [00:44:05]8,000 I'll select it, [00:44:10]execute, you can see except 8,000 salary record, every record [00:44:15]is visible with that guys. For numeric you can remove these [00:44:20]single codes and when I'll execute again, it won't show any error. [00:44:25]This is the usage of not operator. We just remove the following record [00:44:30]from the result. That is the employee with salary 1,000 rest. All the employee records are visible. [00:44:35]Guys, in this video, we saw how we can work with the operator in killer. [00:44:40]In this video, we will see how we can back up our database on kill server. At first, we created [00:44:45]a database amid DB there. Then we created an employee table in it using the following [00:44:50]command. After that, we added records in the table. Here you can see our [00:44:55]amid DB database with employee table is having the following records. [00:45:00]We need to back up the entire database. It's very easy. You need [00:45:05]to execute in command that is back up [00:45:10]database. The name of the database B then [00:45:15]mentioned to disc is equal. In this you need [00:45:20]to add the path where in your database will get backed up. Always. Remember, [00:45:25]add in a different directory, always because in case if your directory which [00:45:30]is having Mssql crash, then at least you have the back up of your database. Let us see [00:45:35]the path here. Let's say we created studio pedia [00:45:40]back up folder. You can create any folder. I'll just copy this. After [00:45:45]that, I'll mention the path here,

now [00:45:50]I'll mention the name of the file. My backup BK [00:45:55]is the extension. Now everything looks fine. Now let us execute and [00:46:00]see what will happen. Select and click on Execute.

[00:46:05]Here you can see [00:46:10]back up database successfully process it is also showing the time [00:46:15]and the completion time. Now let us see here. You can see it automatically [00:46:20]created a backup file. In this way, guys, you can easily create [00:46:25]a back up of your database. The BA extension is here because it will allow [00:46:30]you to e, restore your backed up databases. In this video, [00:46:35]we saw how we can easily back up our database on MS Skills. In this [00:46:40]video, we will see how we can easily drop a table in Skills. Right [00:46:45]now we have a database. Let's open it. [00:46:50]Commands completed successfully. [00:46:55]Here's our database. That database, we have a table. [00:47:00]Let's see which one plot. Let's see the records of the table [00:47:05]using the select star from employee [00:47:10]selected Execute. You can see the records of the table [00:47:15]employee here. And we have ID, employee name, employee It, and employee salary. [00:47:20]Let us drop the table. To drop the table means to delete [00:47:25]all the records from the table as well as to remove the existence of the table. [00:47:30]Let us use the command drop table face table name, that is employee [00:47:35]semicolon. Now this will delete [00:47:40]the entire table. We will check select [00:47:45]Execute. Now you can see command completed [00:47:50]successfully. Now let us refresh this.

[00:47:55]Here you can see the table is not visible because [00:48:00]we deleted it. We dropped means we deleted it. Now I'll try to [00:48:05]get the records from the table, but the table doesn't exist. Now [00:48:10]when I'll select this and click on Execute, [00:48:15]this will show an error because you can see in valid object name employed because we already deleted [00:48:20]the table. There are no tables in mid DB database because we dropped it guys. [00:48:25]In this video, we saw how we can easily drop a table and remove the entire table with a record [00:48:30]in skill server. In this video, we will see how we can work with the quill in operator. [00:48:35]In operator allows you to set multiple values in a were clause. Let's [00:48:40]say you want specific values from a table. You can use the in operator. [00:48:45]We will see an example here. You can consider it as a shortened for multiple [00:48:50]or conditions. Let us see an example here. In guys, we have Amid database. [00:48:55]Under Databases Amid Database. Within that we have tables. We have a [00:49:00]single table only employee. Let us see the records of the table using Select Star. Before [00:49:05]that, guys, we have entered the mid database using space database name, that is amid [00:49:10]database. Let us quickly see the records of the employee table [00:49:15]selected and click on Execute. Here you can see [00:49:20]the output. We have ID, employee name, city as salary. [00:49:25]Now let's say you want all the records of the employees whose city is [00:49:30]Y and FGH. You can easily set it using multiple conditions, [00:49:35]that is SQL. Let's see how.

[00:49:40]Since we want all the records, we will use Select [00:49:45]Star from Employee based on a condition right here. We will write the condition where in [00:49:50]the column name City. Now use the SQL and within that [00:49:55]add the values, multiple values. That means it was Y [00:50:00]and FGH. Here it is only [00:50:05]these two will get printed. Jenny and David semicolon and run it. [00:50:10]You can see I told you David is visible because [00:50:15]the city was Y FGH. Let us move further [00:50:20]and see another example. I'll just copy it to explain it easily. Let's say [00:50:25]this is our table. We want to fetch the records of [00:50:30]the employee table based on employee name. I'll, I'll mention EMP name [00:50:35]under this. Now we need to mention the names. Let's say I want [00:50:40]to record for, let's say Emma Henry and Will. I'll mention [00:50:45]Emma Henry and Will. [00:50:50]That's it. Okay. I'll select it and click on Execute. [00:50:55]Now you can see on Emma Henry and Will employee records are visible. We [00:51:00]can also use its opposite. That is after we can use not in, [00:51:05]let's say I'll just copy this and paste. [00:51:10]I'll just mention not in what will happen except Emma Henry and will [00:51:15]all the records will be visible. Let us first display all the records. [00:51:20]Here it is. Now you can see except Emma, Henry, [00:51:25]and Will all will be visible. That means Tom, Jenny, and David will be visible. Because we have used [00:51:30]Click on Execute, you can see Tom genuine. David is visible, [00:51:35]guys, this is or you can use the n and not in operator, we have used the operator [00:51:40]to basically specify multiple values in a wear clause. We mentioned multiple values [00:51:45]here you can see guys, thank you for watching the video. In this video, we will learn about qat. [00:51:50]The operator is used in a clause, for a specific [00:51:55]pattern. In a column, you can see specified pattern. Let's say you have [00:52:00]employee name to Jack and Tim. You want only the employee name [00:52:05]records with names starting with. So you can use operator in this way. You can also [00:52:10]work around 01 or multiple correctors or even a single character for that. [00:52:15]Guys percent sign is provided by SQL. This is for 01 [00:52:20]or multiple characters. In that way, you [00:52:25]can also use underscore [00:52:30]for let's say [00:52:35]one or you can say single character. We will see all these [00:52:40]examples in this video. Okay, let us see [00:52:45]here. You can see guys, we have Am database and databases. Within that we [00:52:50]have employee table. We have entered the Amb database using the use command [00:52:55]space database name that is Amid. Now let us see the records of the table so that we can operate [00:53:00]on the operator, our table was employee using Select Star, we can get all the records. [00:53:05]Select and click on Execute. Here you can see we have all the records. [00:53:10]I told you before that using operator we can easily fetch the records using a specified [00:53:15]pattern. Let's say here we have employee named Tom, Emma, Jen, David, Henry, [00:53:20]and Will Cities with the following. Let's say we want [00:53:25]the employee records with City starting with A only. Okay for that [00:53:30]guys, let us write select Star because we want all the records we are using [00:53:35]star. We won't be using semicolon again because you want to add a condition.

[00:53:40]The column name is city, right? City [00:53:45]like the operator. And we want starting with a capital A, right? [00:53:50]That is ABC cities will be visible high percentage A because we just saw the usage [00:53:55]of percentage so that we can easily represent 01 or multiple characters. [00:54:00]Here we have mentioned a single character, it's fine semicolon [00:54:05]and we will run it. Execute here. And you can see only the city starting with A [00:54:10]is visible. Now guys, let us show execute the command again, and [00:54:15]here it is. Now let's say you want employee name. That ends with [00:54:20]the following will be visible. Let's say, I'll copy this again here in [00:54:25]what we want, guys were there was column, now we want EMP name column. [00:54:30]I'll mention MPa. That's it. Like we want it in the [00:54:35]end. That means we want the employee name records ending with that's here, [00:54:40]we will just use the percentage before and mention after executing [00:54:45]it. Only the third record will be visible, selected and click on Execute. You [00:54:50]can see only Jenny record is visible. In this figures, we can work with the percentage. [00:54:55]Now let us guide display the records again. Now let's say we want the employee records [00:55:00]with employee name that [00:55:05]in the second position. Jenny is having in the second position and Henry is having [00:55:10]in the second position. We will use a operator select star from employee [00:55:15]because we want to display all the records. Now the condition part [00:55:20]herein, we'll be using both the percentage as well as underscore [00:55:25]EMP name.

[00:55:30]Within this we will add the condition first. We will add underscore because [00:55:35]the first character is fixed, whatever it is and the second character should always be. [00:55:40]I'll mention here after that there can be any number of correctors. [00:55:45]I'll add percentage. That's it now Jenny, because the second character is Jennie. [00:55:50]And Henry will get feed selected and click on Execute here. And you can see [00:55:55]Jennie and Henry got feed easily. Now guys, let us see the condition again. [00:56:00]Let us first display the records. Okay, here we [00:56:05]will fetch the employee names that does not start with E. Here only [00:56:10]Emma won't be visible and rest all of them will be visible. I'll [00:56:15]mention the condition again. Emp name, [00:56:20]not like I told mentioned percentage [00:56:25]because there can be any number of rector after that. But the first character is fixed, which we [00:56:30]have mentioned as semicolon. Now, accept, every record will be visible. [00:56:35]Execute accept Emma, every record is visible [00:56:40]in this way, we can also work around the not like operator. Okay guys, we have worked [00:56:45]around the percentage as well as underscore. You can work around more examples on your own [00:56:50]guys. In this video, we saw how we can work with the and not like operator in Quil [00:56:55]video we will see how we can easily work with the min function in quel. If you want [00:57:00]to return the smallest value of selected column, you can use the min function. Let's [00:57:05]say you want to return the minimum salary. Then you can use the Min function and you want to [00:57:10]face the employee name specifically with the minimum salary. You can use the Kill Min [00:57:15]function. Let's see an example. Here we have a database, Amid. [00:57:20]Here it is under databases Amid. Under [00:57:25]that we have employee table. You can see, let us face the records of the employee table first using select [00:57:30]Star from employee.

Here it is [00:57:35]selected and execute [00:57:40]here. And you [00:57:45]can see the records we have ID, employee name, city, and salary in our employee table. What I want [00:57:50]to fetch, I want to fetch the records of an employee with the minimum salary. It [00:57:55]will return the following. Jenni with 5,000 salary. Let's see, [00:58:00]Use the Min function.

I'll [00:58:05]write select Min under brackets, mention the column name where [00:58:10]we want to fetch the minimum. I'll mention salary, [00:58:15]that's [00:58:20]no, mention the table name, employee, that's said. Let us [00:58:25]select and click on Execute.

[00:58:30]Here you can see it has returned the minimum salary 5,000 [00:58:35]but there's no column name. If you want to add a column name, you can write an alias [00:58:40]as let's say Result and select it again, [00:58:45]Execute [00:58:50]Here you can see it has displayed the column name as a result, because you mentioned alias [00:58:55]guys. In this video, we saw how we can return the minimum, that is the smallest value [00:59:00]of the selected column here, and we selected salary.

In this video, we will see how we can work with the [00:59:05]max function in SQL to return the largest value of the, Let's say [00:59:10]you have a table, employee table, and you want to get the maximum salary from all the employees. [00:59:15]Then you can use the max function. This way you can get the highest score from a number of players [00:59:20]in a team, let's say a cricket team. Let's see an example. Here we have [00:59:25]Amid DB database. You can see here a database under database Amid. [00:59:30]Under that we have employee table. We have entered the database using use [00:59:35]space database name. Now what we want guys, [00:59:40]we want to display all the records of the employee table. For that type, the command [00:59:45]select star from table name. That is select star from employee [00:59:50]selected Execute.

[00:59:55]Now here and you can see all the records of the employee [01:00:00]table. We have ID, employee name it, and salary. If you want to face the maximum salary, you [01:00:05]need to use the max function. So let us see how. Type select [01:00:10]max within that mention the column for which you want [01:00:15]the maximum output. Here we want maximum salary. So I'll write salary [01:00:20]after that, press center and write the name of the table that is from table that is from [01:00:25]employee semicolon. Let us now select this and click Execute. [01:00:30]Here you can see the maximum salary, 9,500 Now [01:00:35]guys, you can see there is no column name. We need to fix this, Use an alias for this. That [01:00:40]is here. Let's see result. You can write anything, [01:00:45]we are just giving the name to the column output Result selected and click on Execute. [01:00:50]Here you can see we got the maximum salary, since you can see [01:00:55]it is 9,500 Our employee table is having the maximum salary of [01:01:00]9,500 This is how we got the maximum salary. In this video, we [01:01:05]saw how we can get the maximum value using the max function in SQL. In this video, [01:01:10]we will see how we can work with the sum function to return the total sum of a numeric column. Let's [01:01:15]say you have an employee table in that you have employee salary as a column. [01:01:20]We want to calculate a total salary is given. You can use the sum function. [01:01:25]Here we have a database, DB database, and within that we have employee table. [01:01:30]Here we have entered the DB database. Let us see all the records of the employee table [01:01:35]using Select star from table name. That is, select star from employee [01:01:40]semicolon selected and click on Execute. Here [01:01:45]is the result, guys. You can see we have a salary column. Let us find the sum of [01:01:50]these salaries, it's very easy. Use the Select again, [01:01:55]select Sum, and mention the column name within it, that is salary. [01:02:00]Because you want to get the sum of salaries from [01:02:05]table name, table is employee that's selected and click on [01:02:10]Execute. Here you can see the sum of all the salaries are visible here. [01:02:15]Now let us view the records again. Now let's say you want the sum [01:02:20]of salaries only for salaries above 7,000 for that guys. [01:02:25]What do you need to do above 7,000 Let's say it's greater [01:02:30]than equal to, it will be, Tom. That means 7,000 [01:02:35]plus 8,000 plus 7,500 plus 9,500 Okay. Here in I [01:02:40]add a condition using were where salary [01:02:45]is greater than equal to 7,000 That's it. Semicolon [01:02:50]selected and click on Execute. Now you can see for all those four salaries above 7,000 [01:02:55]that is greater than equal to 7,000 is 32,000 In this way, you [01:03:00]can easily find the sum of any numeric column values of any numeric column. [01:03:05]In this video, we will see how we can use the AVG function, that is average function in Qual [01:03:10]to get the average value of a numerical column. Let's say we have product [01:03:15]table in that we have price and we want the average price. We can use the AVG function. [01:03:20]Here we will see another example in which we have our employee table. [01:03:25]Let's say right now we have a mid database, in that we have a [01:03:30]employee table. At first, we will display all the records of the employee table [01:03:35]before that we entered the Amid database. Here it is now L Display all the records of [01:03:40]the employee table using select star from table name, that is employee [01:03:45]selected. And click on Execute. You can see six records. [01:03:50]Okay, Let's say we want the average salary, which is very easy to find using [01:03:55]the AVG function. Let s [01:04:00]AG mention the columnameurse. [01:04:05]The columna is salary because you want average salary [01:04:10]from table. That means employee [01:04:15]selected. And click on Execute to get the average salary. [01:04:20]The average salary is seven to 83. In this guy, you can get the [01:04:25]average value of a numerical column. In this case it is salary. Okay, [01:04:30]here we can also add a clause where let's say salary [01:04:35]is greater than 6,000 semicolon. [01:04:40]When I'll select click on Execute, it will [01:04:45]only consider the records, the employee's salary above 6,000 and we'll find the average. That's it. [01:04:50]In this video we saw how we can find the average of a miracle column. We found the average of salaries. [01:04:55]In this video, we'll see how we can use the between operator. Between [01:05:00]itself says that in a range you can set a range between value one and [01:05:05]value two. These values can be numbers like one to three text, it can be any text [01:05:10]and dates between dates, you can get records. Let us see an example quickly [01:05:15]here. In guys, we have databases under that. We have a Mb database. [01:05:20]Within that I'm having my table employee table. We have entered the database using the use command [01:05:25]database name. That means use ambo. Let us display the records of the employee [01:05:30]table.

I hope you know, Select [01:05:35]Star is used to display all the records. And from the table name that means employee. Click on Execute, [01:05:40]on click Execute guys, you can see below, all the records of the employee [01:05:45]table are visible. Okay, Now let's say we need to fetch the employee name [01:05:50]records. That is the entire employee records with salary 7000-9 [01:05:55]thousand. Okay? For that, let's mention the following again because [01:06:00]you want all the records. Okay? Now we will add the wear clause because you want to set [01:06:05]a condition we will use between now were salary,

[01:06:10]I told you let's say [01:06:15]7,000 to 90,007,000.9 [01:06:20]thousand. It will also include both the value that is 7,000 9,000 because it is inclusive. [01:06:25]Okay, semicolon. And we will [01:06:30]select it. After that, I'll click on Execute. And only those records, [01:06:35]salaries, 7000-9 thousand including both, will be visible here. [01:06:40]You can see here is visible here, guys. Let us also see between. [01:06:45]I'll just copy this. Let's say these are records if [01:06:50]you want the rest of the records you can just mention not between.

[01:06:55]I'll just show you the records again. [01:07:00]Okay. Not 7000-9 thousand, that means except 7,000 to 9,000 everything [01:07:05]will be visible. That means Jenny records as well as Henry records will be visible [01:07:10]with that will also will be visible because it's less than 7,000 because we have [01:07:15]used Not between selected. And click on Execute. Here you can see I told [01:07:20]you Jenny Henry and will be visible. If you'll go for between again, then the rest [01:07:25]of the records will be visible. I hope you know the difference between between and not between is just the negation [01:07:30]of between. Now, move further here. We can also use [01:07:35]the text values we will use between. [01:07:40]Now let's say, I'll mention the text values. These were our records.

[01:07:45][01:07:50]Let's say between David and [01:07:55]let's say we are displaying between David and Jenny. David and Jenny [01:08:00]means alphabetically DJ. Between D and J, we have Ma [01:08:05]Henry. Okay. And here we will mention MPa. We need [01:08:10]to also order order by, let's say Pam. [01:08:15]We'll see what will be visible, selected and click on Execute. Here [01:08:20]you can see between David and Jenny. Firstly, David and Jenny will be visible because it is inclusive. [01:08:25]I told you before, between that alphabetically mine, Henry is also visible. This is how you can [01:08:30]use text values. It's so simple how we can work [01:08:35]with between and not between operator in Sql. In this video, we will see how we can return [01:08:40]the count of rows that matches the specified criterion. That means, let's say you have an employee [01:08:45]table and you want to get the number of employees who are having salary above 5,000 [01:08:50]let's say. You can easily do it using count function. Let's see here. [01:08:55]Let's say first we have a Meb database inside. We have employee table. Here [01:09:00]in under table, employee table. Let us fetch the records of the employee table [01:09:05]using select star from table name. That is, Select Star from Employee [01:09:10]Selected, and click on Execute Here. Below you can see the output. We [01:09:15]have six records and we have four columns. Id, Employee [01:09:20]name, City, and employee salary. Let us find the number of employees. [01:09:25]First you select the method name count. [01:09:30]Under that, in this you need to enter the column. This is the syntax [01:09:35]here. You need to add the column, it's written count function requires one argument.

[01:09:40]I mentioned employee name from table [01:09:45]name. I'll selected and click on Execute. [01:09:50]Here you can see there were six employees. Let's modify this [01:09:55]here. I'll remove the semicolon. Here I'll set a condition wherein [01:10:00]salary is greater than, let's say 7,000 [01:10:05]It will count the employees whose salary is greater than 7,000 And we'll display the number [01:10:10]selected and click on Execute. Here you can see three Verify it using Select [01:10:15]Star from employee, again selected and click Execute. So you can see above 7,000 [01:10:20]there were three employees, that is Emma, David, and Henry. [01:10:25]Three got displayed when we executed this command. Click execute. You can see [01:10:30]three guys. Let's say you want to display only the number of records without mentioning [01:10:35]any column. For that, right, select count under column name, bench star. [01:10:40]That's it from employee semicolon, and that's [01:10:45]it. Click Execute, and it will display six because we [01:10:50]were having six employees, six records here. And you can see we were having six records. And the same [01:10:55]is visible because we entered count bracket star. You can re check again [01:11:00]execute and you can see that we were having six employees, employee records guys. In this video we saw [01:11:05]how we can work with the count function to count the number of records. [01:11:10]In this video, we will see how we can use a top clause in SQL to specify the [01:11:15]count of records to return. That means, let's say we have a table and we want to only fetch the top [01:11:20]two rows records. Then we can easily do it using the select top clause.

[01:11:25]You can see a database here under databases, [01:11:30]this database is having a employee table. Let us fetch the records of [01:11:35]the table using Select star from table name. Select star from table [01:11:40]name, employee, semicolon. And I'll select it and click on [01:11:45]Execute here. [01:11:50]And you can see a table is visible. Let's say I want to fetch the first two [01:11:55]records on guys. What I'll do, I'll use the select top clause. Let us fetch, [01:12:00]we want two records. I'll write two [01:12:05]star because I want all the records from employee. [01:12:10]This will fetch the top two records, the top two [01:12:15]rows on selected click Execute. [01:12:20]Now you can see on the top two records are visible from the employee [01:12:25]table. Now we will look at flick top percent clause in that you [01:12:30]can face the record on the basis of percentage. For example, here is the employee [01:12:35]table, It is having six rows. If you want to phase three rows only [01:12:40]you can state, we write 50% there. That's it. Let us see. Select [01:12:45]top. Since we want 50% records, I'll write 50% [01:12:50]manually star from employee. That's it. It will face [01:12:55]50% records. That means the first three records completely from the employee table. [01:13:00]I'll select it and click on Execute. Here you can see it has phased three records [01:13:05]in the same way. Let us write this statement again. I'll just copy it. [01:13:10]Let's say 75 selected execute. Now out [01:13:15]of six record it has shown us five records. Okay, Now let us see. [01:13:20]We can also add a clause in it. I'll again use this [01:13:25]for clause. Just write, let's say I want [01:13:30]to 50% records or let's say 25% records [01:13:35]were salary is greater than 7,000 [01:13:40]Let's see what will happen. There is an error. Why? Because we added a [01:13:45]semicolon. It's fine. Now let us select this and click on Execute. [01:13:50]Here you can see it has shown us 25% record. That [01:13:55]means a single record. Or I'll modify this statement again to make [01:14:00]it simpler for you. Let's say I'll just write select top [01:14:05]two from employee salary greater than 7,000 I'll select it and click [01:14:10]on Execute. You can see two records are now visible. The top two records [01:14:15]which are having salary greater than 7,000 it is 7,500.8 [01:14:20]thousand because these are above 7,000 and these are the first two. I hope you understood [01:14:25]guys. In this video we saw how we can work with the select top clause to return a specific [01:14:30]number of records. In this video, we will see how we can work with non [01:14:35]constraint in SQL. Sql constraints are basically used to specify [01:14:40]rules for the table data we are considering here. The noon constraint, [01:14:45]the notonal constraint enforces the column to never accept null values. [01:14:50]That is why it is not null. It will never accept null values. If you'll [01:14:55]add null values after setting it as non constant, it will show an error. But [01:15:00]always remember, a column can always hold null values. But if you'll set the column [01:15:05]as non, adding a null value will show an error. Okay, let us [01:15:10]see an example. Here we have a Mib database, you can see. Let us [01:15:15]go inside the Amb database and use it. I'll create [01:15:20]a new table now so that I can easily explain you guys how to work with not constant, [01:15:25]how we can set it. Okay, let us create a table. Now. Let us create a new [01:15:30]table. Create table using create table command table name. A table name is employee [01:15:35]within that, we will now add field names with its data type [01:15:40]and constants. Let's say your first field is ID, that is employee ID. I'll cite it as T [01:15:45]and I'll cite it as non. Id is unique for every employee [01:15:50]and it is a field which is a must that employees always [01:15:55]have ID or SSN, let's say. The next one we will add is MP [01:16:00]name. That is employee name. I'll cite it as wire care. I'll [01:16:05]set it as no tonal again. Next is our [01:16:10]city. I'll cite it as wire care again. [01:16:15]Okay, the last one, I'll set it. As salary, which [01:16:20]is salary is in right 5,000.50001 C. We have added [01:16:25]the notonal constant, that means this won't accept any null values. [01:16:30]Let me execute it. It is successful, guys, here it is. [01:16:35]Now let us insert some values [01:16:40]for inserting. We use insert Into. Let's say we inserted [01:16:45]the following, Insert Into Employee, that is a table name and the values for all the four fields, [01:16:50]here it is. Okay for ID, we have added 12 here [01:16:55]to Emma and all these. When I'll execute this, let me execute this before [01:17:00]when I'll execute this here. And you can see we have [01:17:05]inserted it. Okay. Again, I'll just show all the records. Select star from employee,

[01:17:10]execute [01:17:15]two records now. [01:17:20]Okay, remember that our ID and employee name is not. We [01:17:25]can't leave that record. Okay guys, in this video we saw what is a notonal constraint in [01:17:30]qui. This video we will see what is a unique constraint in Esque and how [01:17:35]we can work with it. The unique constraint basically shows that all the [01:17:40]values in a specific column is different. For an example, let's say the role [01:17:45]number of a student or the unique number appointed to an employee. That means [01:17:50]an Ass number which is unique for every employee. You can have more [01:17:55]than one unique constraint for a table, but a primary key constant will be only a single per table. [01:18:00]That's the difference between primary key and unique key. That is primary key and unique constant. [01:18:05]Let us now create a table and add the unique constraint in it. Okay, [01:18:10]Let's begin here and you can see an example. We have a database Amid, [01:18:15]but within that we don't have any table. We will create a table and show an example for [01:18:20]unique constant. Let us first enter the database amid using the use command [01:18:25]we have entered here. Now let us create a table. [01:18:30]Here in guys we have created employee table, we have employee ID, employee name, employee [01:18:35]It, and employee salary fields columns. Id is unique. Every employee will have a unique [01:18:40]ID. Obviously, let's say there are two employees, Tom and Jack. They [01:18:45]won't be having a similar ID's. Right? They'll be having different IDs. We need to set [01:18:50]this as unique, obviously for obvious reasons. After setting [01:18:55]it unique, that means all the values in the columns are different. In the ID column [01:19:00]is different. Okay, Now let us insert some records. [01:19:05]Let's say we added record for a single [01:19:10]record for all the four columns. That is the following. A [01:19:15]first, let me create the table. Click on Execute. We created it successfully. You can see below [01:19:20]here it is. Command completed successfully. Command completed successfully. Now let us add the record here. [01:19:25]Execute. Okay. [01:19:30]I'll just select star from Employed to display [01:19:35]all the records from the employee table. We have a single record only single row [01:19:40]here is a single row. Now guys, what we can do, we can add another record. [01:19:45]Let's say I add the second record [01:19:50]in the same way. I'll add the third record and fourth record. Here it is [01:19:55]guys. We have added more records. I'll select all and click on Execute. [01:20:00]Okay, one row effect. Now execute this again, here it [01:20:05]is. We have four records. Remember, ID is a unique. As I've told you before, it is [01:20:10]unique. It will be different for every row. Now let us [01:20:15]try to add a similar ID. What will happen? It will show an error. We [01:20:20]have four here. Let's say I'll add three again. [01:20:25]I'll change everything else. Let's say Gary, we can add [01:20:30]anything here.

Let's say, [01:20:35]here it is, 3.3 is repeating. That means ID is unique, but it [01:20:40]is repeating, which is false. That won't happen. Two employees can never have a single ID, [01:20:45]you can never have a similar ID. When I run it, it will show an error execute. [01:20:50]And here is the I told you before, unique [01:20:55]constraint cannot insert duplicate key in object. [01:21:00]The duplicate key values three. Here it is. I told you [01:21:05]this is the purpose of unique constraint. Now when I'll set it five and run it again, it [01:21:10]will show no error. You can see, let's say I'll just [01:21:15]execute this here or here, I should say. [01:21:20]Now we have five records with no error. [01:21:25]We fix the errors. In this video, we saw what is a unique [01:21:30]constraint in Quel, how we can work with it. In this video, we will learn what is a [01:21:35]primary key constraint in Quill. We will also see a live example. A primary key [01:21:40]uniquely identifies each recording a table, and the primary key must have [01:21:45]unique values and it can never contain null values. Always remember that a table [01:21:50]can have only a single primary key. Example of a primary key can be [01:21:55]employee ID in an employee table, it can be student ID [01:22:00]or student roll number in a student table. Let us see an example. Here we have [01:22:05]a database here in a quill which is DB. Let us first [01:22:10]use it, Pap, using the use command execute. [01:22:15]We are inside the DB database. Now we will create a table and set one [01:22:20]of the column as primary key. Let's begin. [01:22:25]Here it is. Guys, we have our employee table. We have four columns, employee, employee, [01:22:30]employee It, and employee salary. Id and employee name are set as not constant [01:22:35]because it can never have null values. We need to [01:22:40]set the primary key to ID. As I told you before how we can set it in a skill. [01:22:45]So after just write primary key, this will set [01:22:50]primary key constant on ID column. That's it guys. In this way [01:22:55]you can easily create a primary key. You can easily set a primary key constant. Let us insert some [01:23:00]records. Here in guys, we have inserted two records using insert into [01:23:05]command. Okay, insert into table name, then the fields, that is the column [01:23:10]names, and then the values for each column value. I'll just execute this.

[01:23:15]Okay, I just forgot to create [01:23:20]table, execute this, execute this executed [01:23:25]successfully. Now I'll just insert execute [01:23:30]and executed successfully. You can see. Now let us display all the [01:23:35]records using the select star from table name employee. [01:23:40]Okay, Select it and execute. And here you can see [01:23:45]we have inserted two records. Here I D is a primary [01:23:50]keys. In this video, we saw how we can work with primary [01:23:55]key constant, what is its usage. In this video, we will learn what is a foreign [01:24:00]key constraint and how we can create it in a squaw primary key constant. [01:24:05]In the previous video, we created a primary key in a table. It is [01:24:10]related to foreign key. A foreign key is basically a column in one table which [01:24:15]is referring to the primary key in another table. You can say [01:24:20]the table with a foreign key is called child table, and the parent table is what, which is having [01:24:25]a primary key. Just like the previous video, we have an employee table. [01:24:30]Okay, Parent table. This [01:24:35]employee table is having EMP ID as primary key. Okay? This is the [01:24:40]complete table. Parent table, okay, which is having a primary key. [01:24:45]Primary key basically uniquely identifies recording a table. Here in our case [01:24:50]it's employee. Every employee will have a unique employee ID. No two employees [01:24:55]can have a similar ID. This employee table, parent table with [01:25:00]a primary key. Here, we created the table using the following syntax. [01:25:05]Here you can see, as I told you before, EMP ID is a primary key. Okay, This [01:25:10]is a parent table. Now let us go to below, which is having a [01:25:15]department table. Department table, as I told you, is having foreign key. [01:25:20]That means it is a child table [01:25:25]in department. You can see the primary key is department ID for the department [01:25:30]table, okay? Here it is also employee [01:25:35]ID from the primary key table. This is what we call a reference. [01:25:40]Here you can say it's called a reference, okay? This [01:25:45]EMP ID is a foreign key. Therefore, we said [01:25:50]a foreign key is a field in one table referring to the primary key and editable, that [01:25:55]means a foreign key is a key in one table referring to a primary key and editable. [01:26:00]In this case, we are having EMP ID. [01:26:05]We created the department table [01:26:10]using the following. As I told you before, DPT ID, department ID is a primary key, [01:26:15]Employee ID is a foreign key Because we took it from [01:26:20]the employee table, which we have shown here using the syntax. The following EMP ID, [01:26:25]the data type. Foreign key, reference, Foreign key. I told you [01:26:30]this is a foreign key here. Refers to employee ID [01:26:35]in the employee table that is references to this employee table, [01:26:40]EMP ID, okay? This is what is visible here. You can see [01:26:45]the employee ID. The EMP ID column in the employee table is the primary key in the employee table. As [01:26:50]I told you before, the EMP ID column in the department table is a foreign key in [01:26:55]the department table. This is what we can say, it's Foreign key. [01:27:00]So a table with Foreign key is called child table. So our department [01:27:05]table is child table and this is a child table and our employee table [01:27:10]above is parent table. Okay. Why foreign key is [01:27:15]it is basically used to prevent invalid data from being inserted into [01:27:20]the foreign key columns. Invalid data won't be able to come in the [01:27:25]foreign key column because it is related to the parent table. [01:27:30]It should be one of the values contained in the parent table, that is contained in the [01:27:35]employee table, which is the parent table. So these two are related, that's why it [01:27:40]will prevent invalid data from being inserted. Okay. This is how you can work with [01:27:45]primary key and foreign key. In Quil guys, in this video we saw what is a foreign [01:27:50]key? What is a primary key? What is a foreign key constraint? How we can set a foreign key [01:27:55]constraint easily. We can reference tables. We also saw what is [01:28:00]a parent table and a child table in context of foreign key. In this video, [01:28:05]we will learn about the check constraint in Quil. As the name suggests, the check constraint [01:28:10]is basically used to allow only a certain values for a column. [01:28:15]Let's say you need to limit the value range which you place in a column. Let's say the value. In case [01:28:20]of age records, you want to insert the record of students with age less [01:28:25]than 20 only. You will use the check constraint while creating the table with [01:28:30]that. Let's see another example. You want to insert records of employees with salaries [01:28:35]less than 10,000 only in a table. You can set the check constraint while creating [01:28:40]the table and while defining the column salary. Let's see a live example [01:28:45]here. And you can see we have our skill server. The databases are visible. We have DB database, [01:28:50]okay? So let us enter DB database. [01:28:55]Click Execute, executed successfully. We are inside the MDB [01:29:00]database. Now let us create a table and set a check constraint for it. [01:29:05]I told you, let's say I'll go for a create table. Employee, that [01:29:10]is an employee table. Here you can see we have employee [01:29:15]ID, employee name, city and salary employee ID. We have set Natal and primary [01:29:20]key here is the salary. We want to set a check constant so that only the [01:29:25]records with salary less than 10,000 gets inserted [01:29:30]For that, let us set the check. This will depict the check [01:29:35]constant here, salary less than 10,000 [01:29:40]Okay, that's it, we have said the check constant. Now what I'll do, I [01:29:45]create the table, click on Execute and table created [01:29:50]successfully. Now what I'll do, I'll just insert the records. Okay? And we'll show you a [01:29:55]live example again here. And I've inserted two records. [01:30:00]These are two rows with ID, employee name, city, and salary. Salary is less [01:30:05]than 10,000 so there won't be any error when I'll insert it. Let's say inserted it, I will [01:30:10]execute after selecting. You can see executed successfully. You can see here [01:30:15]rows effected. Now let us print the records. I [01:30:20]hope you know to display all the records from a table, we select Star from Employee. [01:30:25]That is from table name. Click Execute. [01:30:30]You can see we have inserted two records. Salary is less than 10,000 [01:30:35]so there is no error. Now let's say I'll insert a record with salary greater than [01:30:40]10,000 Then it will show an error. [01:30:45]Okay, let us add it here. I'll add three name, let [01:30:50]us add will location. We can add any [01:30:55]random location. Now that's 8,000 I'll set [01:31:00]11,000 but the check constraint depicts that the salary should be less [01:31:05]than 10,000 There will be an error definitely. Let's add [01:31:10]execute. You can see query completed with errors and the error will depict the check [01:31:15]constraint. You can see the insert statement conflicted with the check constraint. Definitely, [01:31:20]yes, the conflict occurred in database amid and column salary as I told you [01:31:25]before. Okay, It's all about salary because we inserted a salary [01:31:30]which is over the check constraint mark. Okay. So what I can do, I can [01:31:35]just change it to 1,000 or let's say 5,500 [01:31:40]Instead I'll run it, there won't be any error execute. [01:31:45]You can see one affected. Okay, now I can stay, [01:31:50]it's good. Yeah, I can stay away on it. [01:31:55]And let's see, the third value will get inserted easily with salary 5,500 [01:32:00]All these salaries are a check constant that is less than 10,000 [01:32:05]In this way, you can use the check constant in Esque guys. In this video, we saw [01:32:10]what is the usage of checks and how you can use it in your table. [01:32:15]In this video, we will see what are SQL aliases. These are [01:32:20]basically used to give a temporary name to a table or a column in a table. It makes the column [01:32:25]names more readable, as well as it only exists for the duration of that particular [01:32:30]query. The as keyword is used to create an alias. Let's see. [01:32:35]Here you can see.

[01:32:40]We have a database Am DB. Within that we have employee table. Let us first enter the Amd [01:32:45]database using the use command [01:32:50]select and execute. Now we have reached here, [01:32:55]Now we have a table. As I told you before, employe table, let us see the records of that table.

[01:33:00][01:33:05]Semicolon execute. [01:33:10]Now you can see these are our records.

[01:33:15]Let's say I need to display a specific column from the table. For that, [01:33:20]the column name I want is let's say employ name. Select [01:33:25]EMP name from employable, Right? [01:33:30]I'll just execute this. Here it [01:33:35]is. The column name is MPa. Now I'll mention an alias [01:33:40]for the column name. For the result, I'll use [01:33:45]the As keyword. Let my result, [01:33:50]the rest of the command is same here. Instead of EMP name, [01:33:55]my result text will be visible because it's an alias. Click on here, [01:34:00]you can see my result. But remember, as I told you before, this temporary name [01:34:05]remains for the duration of the theory only. That means if now I'll, [01:34:10]I'll execute the following command that my result will vanish and only [01:34:15]the exact EMP name column is visible. This way you can add an alias if you're having [01:34:20]a number of rows, if you're having a lot of column and rows in your table. And if you want to display [01:34:25]the result, you can always use the alias. And it makes the columns more readable, [01:34:30]the result more readable. Okay guys, in this video, what is an alias in [01:34:35]Quill? In this video, we will see how we can easily work with Select into statement [01:34:40]to copy data from one table into a new table. So this [01:34:45]will indirectly create a backup copy of any of a table. Let's see how. [01:34:50]Here we have our database. We have B database. Within that we have [01:34:55]a employee table. Let us go inside the MB database [01:35:00]using the use command. Select Execute. We have reached the [01:35:05]Mit database. You can see command successful. Now let us print the records of [01:35:10]employee table [01:35:15]I have selected. Click on Execute. Here you can see [01:35:20]the records are visible with ID, employee name, employee City, and employee salary guys, what I told [01:35:25]you we will copy into a new table. We will copy the columns into [01:35:30]a new table. I want to copy all the [01:35:35]columns for that. I'll use Select Star, because obviously Star is used when we want all [01:35:40]the records, all the columns here I'll use Into, Select Into, Select [01:35:45]Star, Into, and the new table name. Let's say [01:35:50]it would be EMP back up 2022. Okay, [01:35:55]Mention the employee. That means from the table. From [01:36:00]employee. And that's it now. I'll just copy it. Now I'll just select and execute. [01:36:05]And you can see six rows affected.

[01:36:10]I'll [01:36:15]select this and [01:36:20]execute our new table. Back up table is having all the records [01:36:25]of the employee table. I'll select it and show you again here and you can see similar [01:36:30]records. Okay? In the same way, let's say you want only specific columns. [01:36:35]Here is our employee table. Okay? [01:36:40]I'll just mention it again. Our employee table, [01:36:45]it's easier for everyone to understand.

[01:36:50]Okay, Star, [01:36:55]we used y because we wanted all the columns. Let's [01:37:00]say I want specific column. For that, I'll just remove the star. Let's say we want [01:37:05]EMP name, the salary. Emp name [01:37:10]salary into the same table. Let's say we [01:37:15]are creating a new table. Now, back up. [01:37:20]That's it. Okay, Now I'll select it and click on Execute here. And you can see six [01:37:25]rows affected.

Type [01:37:30]select star from my back up. Now let's say [01:37:35]what will be visible, a semicolon [01:37:40]here. You can see on the employee name column and salary column is visible under [01:37:45]my backup table. Through this way, you can easily create a backup copy. Or you can copy [01:37:50]the data, the records, the specific columns or all the columns into a new table. Guys, [01:37:55]in this video, we saw how we can work with the Esque Select into statement. In this video, we will see [01:38:00]how we can easily work with SQL Alter Table Statement. Alter table is used to add [01:38:05]or delete columns in an existing table. Let us see an example here. And you can see [01:38:10]we have a database am and employee table in it. So let us enter. [01:38:15]We have entered the DB database using use [01:38:20]command. Click on Execute. Now we have entered, let us show the records [01:38:25]of our employee table.

[01:38:30]Here it is. Guys select Star from employee select and click on [01:38:35]Execute. Now we have the following records in a table. Now let's say we [01:38:40]need to add a new column. That means we are altering the table, the name alter table. To add a [01:38:45]new column, we will use the alter table statement, which is very easy here. Let's [01:38:50]say we are adding age column. For that, let's use the command alter [01:38:55]table. Table name is employee [01:39:00]present, right? Add because we're adding a column. [01:39:05]And T, okay, semicolon. Let's select and execute [01:39:10]commands completed successfully. When I'll select this [01:39:15]and execute to show alter records, you can see we have a H column, [01:39:20]but it's null because we haven't added any records in H [01:39:25]column. Okay, but we added a new column. Now let's say we need to remove [01:39:30]the column. Now use the same command alter, [01:39:35]but what do you need to use to [01:39:40]drop it? Just drop column and remove the data type. Obviously, [01:39:45]because you want to just remove it, select it and click on Execute.

[01:39:50]Okay, now command successful. Now [01:39:55]use this again to show all the records. Select Star from employee Here, and you can see [01:40:00]the age column vanished. We altered a table successfully, [01:40:05]guys, in this video we saw how we can easily a table. We [01:40:10]all, we added a new column, then we deleted it using alter table command. [01:40:15]In this video, we will see how we can work with the q update statement. Update statement is [01:40:20]used to modify the existing records in a table. We will see an example here. And you can [01:40:25]see we have databases. Databases We have Amb database [01:40:30]and within that we have employee table. Let us first [01:40:35]enter the Amp database so that we can use it. Here is the command [01:40:40]select and execute. We have entered. [01:40:45]Now let us display the data of employee table. Here it is. [01:40:50]I hope you know, [01:40:55]to display all the records of a table, we use select star from [01:41:00]select and execute.

This is a [01:41:05]table. Okay, Now what we want, we need to update the table. Let's say [01:41:10]we need to update the salary of employee, David. Okay, so we'll update [01:41:15]it. Now, write update statement, update space, [01:41:20]table name, which was our employee presenter. [01:41:25]Now use the set command. Whatever new recording need to set, [01:41:30]you need to mention here we need to set the salary. Write the column name, salary [01:41:35]set salary is equal to it's 7,500 [01:41:40]Let's say I'll set it to 9,500 Salary [01:41:45]is equal to 9,504 That [01:41:50]means we command employee name is equal David or we can [01:41:55]mention the ID as four because it's for David, let me [01:42:00]mention it. Id is equal to four semicolon. [01:42:05]Okay guys, now we will the salary [01:42:10]of David is 7,500 For ID four, it will update to 9,500 okay, [01:42:15]selected and execute one reflected.

[01:42:20]We can select this and execute or we can mention it again. [01:42:25]It's a step since we updated [01:42:30]here and you can see the salary of David, updated 7500-9500 [01:42:35]This is how you can, you can use update. [01:42:40]Okay. You can also update multiple records by changing [01:42:45]two of the values. How? Let, I'll just copy [01:42:50]this again. Okay, I have updated [01:42:55]the salary to 9,500 I want to update, [01:43:00]let's say I want to change the recalls for ID one, [01:43:05]that means I want to change the name as well as City. How will I do it? Set [01:43:10]salary. Since we want to change employee name and city, I'll mention set [01:43:15]EMP name. It's let's say I'll [01:43:20]set it to Brad. Okay. Since I'm updating [01:43:25]multiple values, I'll set I also wanted to change the city. [01:43:30]Let's say I'll set it to JKL [01:43:35]for a demo. Okay, for ID, here should be one for [01:43:40]ID, one that is this. We will change the employee name from To Brad and from city [01:43:45]ABC to JKL. Okay. Now I'll select it and execute [01:43:50]one reflected. Let us execute this again.

[01:43:55]Here you can see for ID one we have changed [01:44:00]from Tom to Brad and from ABC to JKL. This is how you can work with the update [01:44:05]statement to update records. Guys, in this video, we saw how we can easily work with SQL update [01:44:10]statement to modify the existing records in a table. In this video, we will see how we [01:44:15]can work with stored procedure in kill server. And what are these? A stored [01:44:20]procedure in SQL is a prepared SQL code that you can easily create [01:44:25]and save so that you can reuse a code again and again. That means it's like a function. Okay, [01:44:30]let's say you have a query which you need to write again and again. What you'll do, you won't write [01:44:35]it again and again. You will basically create a procedure that you can just call it and [01:44:40]execute it whenever you need it. This is what we call a stored procedure, okay? For stored [01:44:45]procedure, we use the create command, okay? Let's see [01:44:50]an example. Here we have our Amb database. You can see a. [01:44:55]Within that, we have our employee table. Let us see the records enter the table [01:45:00]B command space. Database name that is A, is a database name. [01:45:05]Execute successfully. Now let us show the records of the employee [01:45:10]table. Select star from employee semicolon and just show it execute. [01:45:15]Okay. Now, at first we will create a procedure for [01:45:20]this command on we need to use again and again. We will create a procedure, the [01:45:25]command create procedure. Let [01:45:30]us give it a name, all records, Use [01:45:35]the alias and type the same command and type the same command. Select star from [01:45:40]employee. That's it. Press center, right, go [01:45:45]semicolon. Now just select it and click on Execute. [01:45:50]You can see query executed successfully. To actually execute it and show the records, [01:45:55]use the command ECC that is execute and then the name of the procedure, [01:46:00]a record semicolon, and just execute this here. And you can [01:46:05]see all the records are visible by just only typing CC keyword and [01:46:10]the name of the procedure, the name of the function, That's it. Okay. Now let's [01:46:15]say we will create a stored procedure with a single parameter for [01:46:20]that. Let us for display all the records here. It is similar. [01:46:25]Now what we will do, we will create a stored procedure, again, similar way. I'll [01:46:30]just copy it with the parameter. Right now we have no parameters. Here you can see we have no [01:46:35]parameters here. We will select records [01:46:40]on the basis of a city name for that used at the rate [01:46:45]city, okay? We want it for a specific [01:46:50]city. I'll mention the condition where city is equal to [01:46:55]at the rate of city, okay? That is the following here, and you [01:47:00]can see it is showing an error. I need to add the data type also. Let's [01:47:05]say where a 20. It's fine now. Okay, now [01:47:10]what I'll do, I'll just use the execute command. Similar way all [01:47:15]records, but it is having a parameter. Now the parameter is at [01:47:20]the rate of city is equal to, let's say we want city [01:47:25]name ABC. Let us change it to all records too so that we have a new stored [01:47:30]procedure. Here you can see guys, we created all records to procedure [01:47:35]and we added the type also to remove there for the city [01:47:40]here. We will now execute it executed successfully. [01:47:45]Let us see now. All the records for city ABC will [01:47:50]be visible under our stored procedure with a single parameter. Okay guys, now [01:47:55]we can also add multiple parameters. Let's say I'll create this again, [01:48:00]okay? This is a third one. At first, I'll just [01:48:05]display the records. [01:48:10]Okay? So now I'll add multiple parameters to our stored [01:48:15]procedure and let us name it to three, [01:48:20]for example. Okay? Now we are fetching records [01:48:25]that will select employees from a specific city. [01:48:30]Okay? Specific city with a specific name. Okay? So [01:48:35]this is our third one, city we already added, so we will add multiple [01:48:40]here. Let's say EMP name is what we're adding. Okay. [01:48:45]255. So we added [01:48:50]two parameters here, city, city [01:48:55]we are using the EMP name is [01:49:00]equal to at the rate of EMP name. This is what we added. [01:49:05]Okay? Here in what I'll do, I'll add [01:49:10]in a similar way, At the rate of, a similar way, at the rate of EMP [01:49:15]name. Okay, Is equal to I told you for a specific EMP [01:49:20]name, ABC is having two employees. [01:49:25]You can see Will and to both are having city [01:49:30]ABC, let's say I'll fetch the Tom one. [01:49:35]A storm. Now we have a specific stored [01:49:40]procedure for it with two parameters. Okay. Now I'll just select [01:49:45]it. Execute command completed. Now I'll [01:49:50]just execute this. Let me add a semicolon. Both select [01:49:55]execute, you can see we select that specific records, we have created [01:50:00]three stored procedure. For example, here it is for select from employee [01:50:05]here for a single parameter and the third one for multiple parameters. In this [01:50:10]video, we saw what is a stored procedure, how we can create it. We also saw how we can create a simple [01:50:15]stored procedure with a single parameter and film and with multiple parameters. Also [01:50:20]in this video, we will see how we can easily work with a create index statement to create indexes [01:50:25]in Quill. Basically, indexes are used to retrieve data from a database [01:50:30]faster than any other way. Let's see how we can create an index quickly. We will see [01:50:35]a live example here. And we have a database, a mid. [01:50:40]Within that, we have a employee. [01:50:45]Let's enter the database amid using the use command. [01:50:50]Here it is. Now let us see the records of the employee table using select star from [01:50:55]employee select and execute. [01:51:00]So here you can see the following, our records, okay, so we will create [01:51:05]index using it. How? Using the create index statement. Let us use the command [01:51:10]create index. Add the name of the index. [01:51:15]Let's say I'll add index name on command. [01:51:20]Now, mention a table name. That is, our table name is employee [01:51:25]under brackets, you'll be adding the column name. That is, this statement will create [01:51:30]an index named index on the column, which [01:51:35]we will add here. Let's say I'll add EMP name. [01:51:40]Okay, semicolon. And let's run it. [01:51:45]I ran it and command completed successfully. You need to remember that the users [01:51:50]can never see the indexes, but they are only used to speed up the searches. That [01:51:55]is, speed up the queries. Now, we can also create indexes with a list of column names. [01:52:00]That is using combination of columns. How? Let me use this [01:52:05]name. Second, index, my index two. I'll add multiple columns in it. [01:52:10]Let's say salary. Okay. Now I'll execute it. [01:52:15]The second index completed successfully. Command completed [01:52:20]successfully. Okay, so we created two indexes guys. [01:52:25]Now let us delete an index using drop index statement. [01:52:30]Just write drop index then the table name that is employee, [01:52:35]the index name we are deleting my index two, let's say [01:52:40]semicolon. I'll just select it and I'll click on Execute. After clicking [01:52:45]Execute, you can see Executed successfully. That means we have successfully [01:52:50]deleted the index. Remember guys, you can create indexes [01:52:55]when a column contains a wide range of values. In this video, we saw [01:53:00]what are indexes in SQL, how we can create indexes and how we can delete [01:53:05]indexes. In this video, we will see how we can work with SQL create view statement [01:53:10]to create a view. So what is the view? A view in SQL is basically a virtual table [01:53:15]which is based on the results set of an SQL statement. If you want to show or [01:53:20]present a specific data from your database or table, you can use a view. [01:53:25]View basically contains rows and columns that is part of your records, [01:53:30]okay? The fields in a view are basically fields from one of your tables. Only. As I told you before, [01:53:35]you can present a data in a more readable form using view. [01:53:40]To create a view, we use the create view statement in SQL. Let [01:53:45]us see an example. Here we have a TB database you can see, and a employable. [01:53:50]Let us enter the database using the use command [01:53:55]space database name. That is, DB is our database here [01:54:00]execute, and you can see successful. Now let us enter our table. [01:54:05]Now let us see the records of our table. Select Star from employee, [01:54:10]select it and click on Execute here. And you can see a record is having [01:54:15]employee ID, employee employee city, and employee salary. Let's say we need to create [01:54:20]a view for employees with ABC, [01:54:25]that is employees living in ABC. That would be the first one and the last [01:54:30]one. How we can fetch a view for it. Let us create a view. [01:54:35]Create view. The command, let us add the name of our view. [01:54:40]Let's say employees, ABC. We [01:54:45]can write use the alias as use the select [01:54:50]command. We wanted employee name and city, [01:54:55]EMP name and city [01:55:00]from mention the table name. That is our employee [01:55:05]city is [01:55:10]ABC. Okay, now we have created a view, okay? [01:55:15]Semicolon selected, and click on Execute Commands [01:55:20]completed to use the same select star. Okay? [01:55:25]In this case we'll be using select Star from our view name because we [01:55:30]want to get the view. I'll just copy this to [01:55:35]ease. Okay, I'll just select a semicolon. I'll, [01:55:40]I'll just select and click Execute here. And you can see both the records are visible. [01:55:45]This is what we wanted for city ABC. Okay, [01:55:50]I'll just copy this again. Let's, [01:55:55]let's say we want to create another view with,

[01:56:00]let's view the records again. Select star from employee. [01:56:05]Let's say we're viewing records with salary greater [01:56:10]than 7,000 Okay, [01:56:15]what we will do. Let's write salary above [01:56:20]7,000 Okay, What we [01:56:25]want to display, we want to the [01:56:30]employee name and city, let's say it's fine. We can also display salary as [01:56:35]that are above 7,000 From employee table where what condition we [01:56:40]need to mention under view wherein [01:56:45]salary is greater than 7,000 Okay, [01:56:50]this looks fine. I'll select it. We have a new view now. [01:56:55]Execute command completed. Now to display, I hope you remember [01:57:00]it's Select Star from your view [01:57:05]name. That is the following.

[01:57:10]It will come on its own. You can see salary above 7,000 that's it. Semicolon [01:57:15]select and that's it. We have all the records of employee [01:57:20]with salary greater than 7,000 Okay guys, this is what we displayed using [01:57:25]a view. We created a view now. Now we will see how we can remove. [01:57:30]Now we have two views here in [01:57:35]first, second, both. Okay, now [01:57:40]let's say we need to remove the employees ABC view. How we can remove it? [01:57:45]For that guys, use the drop view command, right? Drop [01:57:50]view and the Wam, that's it. Our Ame, here it is. Employee BC, [01:57:55]select and execute. You can see commands [01:58:00]completely successfully. Now, if you'll try to display it, nothing will be visible. [01:58:05]You can see invalid object name. Okay, so this is what I told you. We have dropped this view [01:58:10]guys. In this video we saw what our views in Quill. We also saw how we can create a view [01:58:15]as well as how we can drop a view.

This transcript was automatically generated using speech recognition technology. Because this method relies on machine learning algorithms, the quality of transcripts may vary. To request this transcript be improved with enhanced accuracy, please email [email protected].