Hi. In this lesson, we understand what is SQL? And how do we use it. SQL is the standard language to access relational database. It's also standardized by NC, which stands for American National Standards Institute. It's a non procedural language.

That means you don't have to talk about the procedural details, you just need to give the command emphasizing on what you want, not how it is to be done, because SQL takes care of how to get it done. Now, the first thing you need to know is these commands are divided into different categories. Data Definition Language commands known as DDL are meant to create tables alter the structure of the table. And dropping the tables and other objects. DML is about manipulating the data, they update existing data, delete the data and so on. dcl is about controlling access to data.

We talk about data in the context of security, transaction management commands, like commit and rollback or use it to manage transactions again a topic we take up later. But today's discussion is about how to query the data, how to retrieve the data from database using select command, the most important command in SQL and these are some rules and if you are using SQL commands in SQL plus a tool which you can use if you like, then in addition, Today's the first To three you have to follow the last point which is you must terminate the command with semicolon. But otherwise that is not needed. And then the best part of it is they are not case sensitive, you can give them an uppercase or lowercase and you can enter statements in any number of lines and keywords are not to be displayed Otherwise, there is no other restriction.

For example, you can't put a part of the word in one line and another part in another line and there are no abbreviations. You have to give the words completely there are no abbreviated versions. So those are the rules very simple to follow. So not case sensitive. You can give commands in multiple lines need not be terminated with semicolon unless you're using SQL plus as we use SQL developer, you don't have to worry about semicolon. Now, these are the tables we use throughout this course, these tables are coming along with your HR schema.

And these tables represent the data about employees, departments jobs, and the history of jobs done by employees. And this is a diagram which clearly indicates the relationship between the tables and we call it as ER diagram entity relationship diagram. And if you see department's table and locations table, they have one to many relationship and this is what you see. This indicates that we have one to many relationship. And then similarly, you see a lot of one is too many rules. Relationships between departments and employees, and also between countries and locations.

There are cases where you have one is to many relationship between departments and employees and also employees is having relationship back with the departments. So we will see more about that little later. But it's enough to know that these tables are the ones we use for all our demos, because these tables were created by Oracle. And the aim is to give you a simple but a database which represents many common scenarios and coming to the blue parts, they are all primary case. So whatever is put in blue, that primary key, and then in job history, you see what is called as composite primary key. So we have two columns.

Use it to uniquely identify rows. So it's called composite primary key. And we also have a lot of foreign keys. For example, in employee table, your manager ID is a kind of foreign key. And then department ID is a foreign key. If you see this department ID is referencing this department ID.

And similarly here, the location ID is referencing that. And then in job history, job ID is referencing this. This is the jobs table. department ID is referencing that employee ID is referencing this. So we have as many as three foreign keys in job history. So this is what we are going to use all through.

Do you remember these tables and it's very important for you to understand What data we have and the relationships between the tables, as we go deeper, you will understand more about this. Now, it's time to use a command to understand the structure of the table. So, describe is the command we can use to find out what is the structure of the table like what columns we have, whether the columns can accommodate null value means a missing value, because sometimes you don't have anything to store then you store what is called as null value and then the data type of the column. So if you take this job stable job ID is of type where character to have 10 it means we can store up to 10 characters and it cannot have null value. Whereas minimum salary can store up to six desserts and it can have null value So, if you do not know what is the minimum salary for a job, you can just leave it empty that is called null and more about null values later.

So use describe and then you give the name of the table to know more about the table structure. You can abbreviate this command because Strictly speaking, this is not a SQL command. This is called SQL plus command. So, some commands are coming from Oracle specifically, and they are not part of the standard SQL language so, we can abbreviate them so you can say describe or de se both are fine. Then this is another command to get the list of all the tables that are available to you. So, select star from tab is going to give you what are the tables that are present.

You get the table name and feel other details which are not important for now. As you can see, one of them is not a table, it's called a view. And that is a topic for some other day. But this is the command Select star from tab and this is going to tell you what tables are available. Now, the command, the most important command select, it is going to retrieve the data from the table. You can also select a few columns from the table because sometimes tables contain too many columns.

You might be interested in only a few columns not all then we call it as projection. So selecting a few columns from available columns is known as projection. Similarly, you may want to take a few rows from available rows, then we call it as selection. So we will see both Have them. And some of the clauses if you see, like a distinct is saying it'll pick up only the unique values. And then you can have either the column or even an expression.

And expression can have an alias if you like, so that a meaningful name is given to expression. And we can select a few rows using the condition and WHERE clause. And you can sort the data in any order using order by and here are some commands. So let's start by looking at the first one. This is going to bring all the columns that are present in employees table. Star here means all you're picking up every column present in employees.

As we are not talking about where class we take all the rows present in employee table. This is what we call As projection, we are selecting only a few columns from the available columns. But you got to know the names of the columns. And relational model is where you refer to columns not by the position but by the name. So this is how you select two columns from available columns. And this is also the same, but the new thing is this.

This is where I'm giving an alias to this column. So in the output, you're going to see name not first name, this is going to be the column headings. And then the next one is to get only unique job IDs. Not all because multiple employers might be doing the same job, but we get only unique because we are giving distinct and then these two are to be concatenated. This is the symbol for concatenation to pipe symbols, so I want to put first name, then concatenate that with the space, and then concatenate that with the last name. So you're going to get first name, a space and last name.

This is how you can concatenate. So those are some of the examples. And these are the operators you can use. If you're planning to use any artomatic calculations, then you need to use these operators. So star slash plus minus, you might be aware of that. The only thing that is missing is a person taste which is typically found in many languages, but it's not available, so you can't use this.

And these are the standard relational operators single equal to check whether two values are equal. And this is about not equal to, and the rest are pretty self explanatory. These are called logical operators, they are used to combine conditions. So let's look at some examples. To start with. Here we are.

The first one is about an expression. I'm calculating 50% of salary, that becomes the bonus. So first name and bonus will be the headings. And this is going to be the expression from where the value is derived. And the second one is where we subtract the minimum salary from maximum salary in job stable, and we call that as the difference. This is again the alias and this is about selection.

We started selecting a few rows from available rows. So I say take only those employees where salary is greater than 5000. So, it will select only a few rows and also it is displaying only first name the same here but we are just extending gate by saying department ID should be hundred and salary should be less than 10,000. So, we get only those employees where this condition is true as well as this is true because we are using end to join the conditions. So, those are the commands to understand how to use where and then you have some more special operators that are called SQL operators. And these operators are very widely used.

So let's look at them between and a straightforward. It is use it to create Compare whether a value is in between the given range. So I want to get employees where the salary is between 10,015 thousand, inclusive of 10,015 thousand. And the next one is where I'm using in. So, I want to know whether the Department of the employee is 1020 or 30. If department ID is in any of these values, the condition is true.

And then the last one here is about now these two are related to null. And we will discuss little more about null. But to understand this, all that you need to remember is null indicates a value that is not present not available not known. So if for number is containing null value, then I'm interested and just a slight Different one is when phone number is not null. So, if there is something in phone number then we are interested. So, those are the operators and then the like operator is another very useful operator.

This is where we put it like this, I want all the employees where the first name of the employ is matching this pattern. So, like followed by the pattern, the pattern can use two special characters percentage means anything, underscore means only one character. So, this says take out all the implies where the first name is starting with S and the be aware it's always case sensitive. So it is uppercase s and in this case, we are taking all the names where S is present In the name, we are looking for employers where the name the first name contains s. And here we are looking for the names where the second letter is a is, first letter can be anything. And underscore indicates that position can be taken by any character, second character must be as that two small s, and the rest can be anything like that we need to form the pattern based on our requirement.

So this is called as, like operator. And then you can sort the data in any order you like. This is how you can sort the data. The first example is where we are sorting the data by minimum salary. The second example is where we sort the data by different which points to the alias which represents the difference between minimum and maximum salary. And then we can also use the position of the column.

So order by two means you're saying take the second column selected and sorted by that. So we are going to take everything and then sorted by the second column. Remember, second column of the selected columns. So these are the selected columns, not the columns in the table. You might have 10 columns in the table. It doesn't consider them, it counts.

There's only what is selected second column among the columns you selected. And this example is where you can sort by more than one column. That means first everything is sorted by job ID Within the job ID, because you may have two or more employees with the same job ID. And within that there will be further sorting by salary. So first sort by job ID, then within that sought by salary. Of course, this makes sense provided job ID is repeated.

Only within those multiple rows. Further sorting is done by salary. And this is about descending order. default is ascending, but you can sort even in descending order. So those are some examples of select command, which is, of course, the most important. And now before we conclude this, let me demo a few things.

Let's go to our SQL Developer. So this is where we give those commands so if I want to get the details of jobs, Just type that Select star from jobs and go run it. And it's going to display all the jobs like this. And then if you say you want only those jobs where the minimum salary is greater than 10,000 rupees, then give that and then click on this file, it could be 10,000 in the currency of your country or whatever. So minimum salary is greater than 10,000, only two rows are selected. If I'm not interested in all I want only the job titles to be selected, I can go for job title.

And I can also call it as title if I like, go ahead and run. And you can see the heading of the column is title. And we are selecting only the job title. So this is an example for what we call as per section plus selection. you're selecting few rows You are selecting a few columns. If you want to sort the data by an order, you can say sort by one, and then the data will be sorted by job title.

And I'll just change this to 5000 to include more rows go ahead run and you can see all these job titles are arranged in the ascending order. And for any reason, if you want to sort them in descending order, you can say d c, same thing will be in the descending order, a comes last s comes first. And these are the simple things but if you say for example, you want all the titles where the title contains manager, you can put it like this job title, like percent days manage percenters. This is going to get you all the jobs where the title is. containing manager, because before that we can have anything after that you can have anything. Yes, go ahead.

And this is what you get. And also you can display all the jobs where the minimum salary is not known to us. So we can say minimum salary is null. Well, if you don't get anything, that's fine, that means there is no row where minimum salary is null. So these are the commands and we discussed about more commands here. So in this lesson, we got started with SQL.

So we understood SQL commands are divided into categories, like DDL DML dcl, queries and transaction control commands. HR schema is very important. Please focus on that. It's simple to understand and we Use this all through this code. So I want you to get comfortable with that describe command is going to display the structure of the table. And you can list all the tables using Select star from tab.

Tab is a special entity, and it's called a data dictionary. We'll see more about that later. Select command is obviously the most frequently used command. And we will see a lot of it there's so much involved in select it is just tip of the iceberg at this point, then we have operators we looked at them some special operators, and then we understood how we can sort the data by using order by class. So those are the things I want you to understand at this point. So this lesson is all about getting you started with SQL and more about SQL will be discussed all through So enjoy your lending get started and keep going