SQL Tutorial part :7 Why and how to do ALIAS in SQL table and field

SQL Tutorial part :7 Why and how to do ALIAS in SQL table and field
Image

Alias ​​is used for the convenience of queries. Elias can be used in both table and column. Alias ​​can be called as nickname or nickname. If we use the query used in Left Join Elias then it will be like below

Image




** It is a good practice to put a table name with a dot (.) In front of the column name in the query, this is how the query should be done, especially when data is queried from the respective table. You have to name the table with.
Explanation: In order to use Elias in the users table, I have given users AS u in this way. Now I don't have to use the name of the whole table anywhere else, but just write the whole users. In this way, the work of profiles table is also done with only p. This is the Elias of users table here. p is the alias of the profiles table.
 
In the case of columns, see u.username AS UserName. To give the AS User Name in this way, look at the output below. This formatted name is also showing. This is also an advantage.
 
Output
Image




Suppose I change the above query a little bit to see which rows are the real ones whose salary is the highest then I can do that using a function here, the function name is MAX ().



Image
The output will come in a row.
 
 I have used a function in one column here, many times you can use different functions in more than one column to give the name of Elias in such a way that when the data shows you can see the name of Elias (in the column) to guess what you are doing here.
8 essential sequel functions and their uses
You can use some functions to increase query efficiency, many function functions. You can use them in any query to extract calculated data from the database.
 
 
MAX ()
 
This function allows you to find the highest value from the rows you query. If you want to see the maximum age from the "applicant" table I've worked with before
Image




View the output A row is coming up showing the maximum age.
 
 
MIN ()
Image




View the output A row is coming up showing the minimum age.
 
 
AVG ()
 
Suppose the average age of everyone is what it takes then



Image
COUNT ()
 
If you want to see how many rows your query returned



Image
You can see any column counted like this, see the output showing the sum of all the rows as a number (৭).
 
 
SUM ()
 
If you want to see the total age of everyone



Image
NOW ()
 
It is used to find out the current time and date



Image
It is actually used when there is a date / time field in the table and the data has to be filtered by date.



Image
CONCAT ()
 
All of the queries that have been shown so far have column-based data.




"--->" It has been separated, it will be even if you do not give it, but it is a little like to see / understand. "--->" You can replace it with any character.
 
 
Image
** Here is a simple and simple query, these functions can be used no matter how big the conditioned query you use.
 
** Here MIN (), AVG (), MAX (), COUNT (), SUM () are called aggregate functions.
 

SQL Subquery :

Subquiries are a powerful feature of SQL. The query is written inside the query, it is a subquery. Subquiries are always placed within parentheses. Usually the main query that contains the subquery is called containing statement or outer query. Subquiries are often called inner queries or nested queries.

Subquiries are commonly used in searches, such as to find the second highest value of a column from a table but you do not know the maximum value. There are also many more areas that will require different times to work.

Now create a database with any name and create a table there, the code given below is that the table will be set with data only if the query is run. This code will create a table called users and there will be 3 fields and some data will be entered in the fields. Everything is understood by looking at the code below.

Image

Now if you want to see the 2nd highest value from the table, you can write the subquery inside the query as below.

Image

The output will be 1 column i.e. the column with the user number that is 65. You have already seen in the previous tutorial how to find the maximum value with MAX ().

Explanation of the query: Here it is said to find the highest value which is less than the maximum value i.e. the 2nd highest value.

** The query inside the first parentheses after the less than (<) sign is the subquery.

If you want to see the maximum number

Image

See the output 90 will come.

SubQuery and WHERE … IN

A subquery can return multiple columns. So using this facility we can make queries with WHERE .. IN. For example, if I want to see the information of all those whose number is below 70

Image

The output will look like the following

Image

I am using HeidiSQL. It is a software like PHPMyAdmin for database operation but desktop based. However you can see in phpmyadmin this output will come. In phpmyadmin, go to the SQL tab, enter the query and click Go.

We have seen queries with WHERE .. IN clause before. I could do this query like below

Image

Or we can see the desired result by comma value of number column. There is no problem in doing this when you know the data in the table. But when there are thousands of data in the table and you do not know the value of any data, you want to see the information of those who have numbers below 60. Then you can see all the information using subquery as we have shown.

The few subqueries that have been shown so far are called noncorrelated subqueries. This means that the subquery has no dependence on the original query or containing statement. He will do his job even if the subquery is run separately.

There are also correlated subqueries. Here the subquery relates to the original query / containing statement. For example, users can make the test by creating another table called meta_data by relating one to many to the table.

meta_data with table code data

Image

** The meta_data table can have multiple rows opposite each single row of the users table, and here it is.

Now by doing a correlated query from users and meta_data, if we wish, we will see all the information of those users from the meta_data table, if all the clicks are added, it will be above 1000. (Suppose meta_data has a number of clicks per day)

Image

Output

Image

In this way many big problems can be solved electronically by doing complex queries with subqueries.

** In subqueries you can perform complex queries using operators like <>, <,>, =, BETWEEN, IN, ANY, ALL, NOT IN. You can even use table JOIN etc. In addition to SELECT, you can use subqueries in UPDATE, DELETE.

 
 

Leave a Reply

Your email address will not be published. Required fields are marked *