Fetch data - SQL教程

- [Facilitator] The first thing we need to learn is how to fetch data from different tables in our database. We use the SELECT statement to fetch data from the tables. We can fetch data by columns. That means we can fetch one single column or multiple columns from the table by specifying it in the SELECT statement. We can fetch data by filtering the rows in a table by specifying a condition. We can also fetch data from one or multiple tables by using joints, which we learn later. SQL statements are not case sensitive, but the best practice is to write the keywords in uppercase and the table names, column names, and all others in lowercase. Keywords cannot be abbreviated or cannot be split across lines. We can write the complete SQL statement in one line. However, we use indentation to improve readability, especially when we are writing long SQL statements. Now this is the syntax for the SELECT statement. If we give star, it'll fetch all the columns from the table, but if I specify the column names explicitly, only those columns data will be fetched. DISTINCT keyword is optional. It is used only when we need to fetch distinct values in a particular column. We can also ask to fetch an expression instead of a column name. We give the alias name if we do not want the expression name to be shown as the column name. We'll understand this better when we look at an example. Then we give the FROM keyword followed by the table name. So now let's look at a few examples in codespace. On your codes interface, find the GitHub codespace's open button. Click on it to go to GitHub. You then click on create codespace on main button and wait for it to fully load. This might take some time. Now once it's fully loaded, we can see an install button. Click on that. Once it's installed, navigate to File Explorer. This is where we have all the files that are needed for our project. I will be adding all the SQL queries here under the queries folder. These three are example SQL statements. This is just for your reference. Currently, we are on the main branch. We will have to navigate to chapter one, video one branch. So I click on main and let's try to find 0101 branch. That is, chapter one, video one branch. So this is the one, I click on it. And now we can see that there are two folders here. The first one is to create the entire database that is needed for our course. So we will not be looking into that. The folder that we will be looking into is 0101, which corresponds to our video. So I click on that and we have all the SQL statements that are needed for this specific video. So since we are in chapter one, video one, so I go there and click on the SELECT statement .SQL. So this is a basic SELECT statement, where I'm fetching all the rows and columns from EMP tab. So when I run this, I right click and go to run selected query. Click on that and it fetches all the rows and all the columns from EMP tab table. Now in the second SELECT statement, I am specifying the columns that I want to fetch. I want only EMP number and name columns from EMP tab table. So when I run this, it only fetches those two columns. Now, if I want to give an expression along with the column names and want to fetch that data, I give the column names first, followed by an expression where I want to calculate the annual salary for all employees. So I'm multiplying it with 12 and then I run this. So now we got the annual salary, but the column name looks a little weird like salary times 12. So for it to look a little nicer, we give, suppose I give it as salary and run this, it shows it as salary. But if you want to give a more appropriate name, I would like to call it as annual salary. Now if I run this, it's going to throw me an error because there are two words in the alias name. When there are multiple words in the alias name, we have to put them in quotes. So now when I run this, it shows it annual salary and this looks much more nicer. Now let's learn about the different operators that we have. We have arithmetic operators and character operators. Any arithmetic expression uses arithmetic operators. That is, plus, minus, multiplication, and division. The order of precedence is multiplication, division, addition, and then finally, subtraction. The parenthesis overrides the order of precedence, which means that anything in the parenthesis will be calculated first and then the other operators outside the parenthesis in the order of precedence. Character strings use character operators. Those are two pipelines that is for contamination and single quotes and double quotes for giving character strings. So here we have learned about the basic SELECT statements, how to use alias names, and about arithmetic and character operators.