SQL Tutorial part :8 SQL Conditional query and Advanced SQL

SQL Tutorial part :8 SQL Conditional query and Advanced SQL
Image

Conditional queries can be made with SQL. Just like if..else, CASE..WHEN is used in programming languages, you can also filter data while retrieving data in SQL. You can do this filtering using CASE .. WHEN etc. If we want to make such a query from the two tables (users and meta_data) created in the previous tutorial.

Image

Explanation: Here from two tables (by joining right) select user name number and click date. When the number of clicks of a user is 600 then the ID of that user and the number of clicks will be shown by adding the word “_eH Hundred_” (which we did with CONCAT. You can add any string). It looks like that in the picture below.

If the number of clicks is 200 then it is shown with “_tH Hundred_”. And when neither of these will happen, it will go into ELSE and “not 2 or 8 hundred club” will show it. You have to start with CASE, then give the condition to WHEN and give it to THEN. If the condition matches, you will see what it will show (even if you can give subquery in THEN, etc.), then you have to give END at the end of all necessary conditions. ID_Status_Clicked Elias, can give anything. Whatever you give will be the name of the column like the picture below.

 

 Output

Image

I used HeidiSQL, it’s a software like phpMyAdmin, you can do similar tests on phpMyAdmin if you want.

 

 

Suppose users in the table whose number is equal to or greater than 60 are A + and those whose number is less than 60 are required to see A separately.

Image

Output

Image

Thus, while creating an application, it may take thousands of ways to retrieve data. Know the rule, work time will seem easy.

 

Advanced SQL By Amidur Rahman

Those professionals know how many kinds of problems are created while coding every day. One problem all day long is to stay behind in one project after another. There are no programmers on this planet who don’t have to run on Google or Stackoverflow.com (stackoverflow.com). However, in our new “Advanced Escrow” section, we will address these various issues. Hopefully there will be many benefits. Not only SQL but also MySQL, PostgreSQL etc. databases will be discussed here.

These tutorials are for professionals only. Newcomers should check out “Basic SQL” first, then it will be difficult to understand the tutorials here.

 

Since it is an advanced level tutorial, there will be no continuity. So there is no specific table of contents but I can give some idea of ​​the articles that will be there. E.g.

How to perform complex queries related to date and time, how to retrieve data a few days before a given time, how to calculate the value of seconds by adding two datitimes or how many hours or days in that interval, how to find the value of multiple columns, How to join more than 2 tables, how to join the same table. How to view the timezone of the system (database server) etc. The article will be given when it seems necessary.

Dynamic Datetime Add Subtract Count

Below is a table SQL code with some data. This table displays various queries. Once the query is run, the table with data will be created.

Image

This table is a user table. Here is some user information and the date and time of who registered.

 

 

 

Viewing some previous data from the current time (DATE_SUB)

 

Suppose I want all the data of those who registered during this period 1 month ago from today.

Image

Output

Image

If you make this query in the table given by us, you will not get the same data because I made the query on June 7, 2015. So it will show if there is data one month before the day you make the query. See query CURDATE () I have brought the current date with this function. And the 1st parameter in the DATE_SUB () function is the current date and the 2nd parameter is the time range. As I gave “INTERVAL 1 MONTH”. As a result, 1 month has been deducted from the present time.

The 2nd parameter can be used to query at any time, such as “INTERVAL 15 DAY” to show data 15 days before the current time.

 

 

 

You can see by subtracting time from a specific date as I want to subtract 1 month from June 24, by subtracting 1 month (May 24, ), I want to see all the data from that date on wards.

Image

This way any data can be filtered.

 

 

 

Viewing some data from the current time (DATE_ADD)

 

 Suppose you want to see the date by adding 1 month to the registration date of certain people. If you register for a service and the service expires after 1 month, then this type of query will be useful. I just mentioned a situation. In fact, there will be millions of situations in professional life where you have to add and subtract dates.

Image

Output: Those whose id 1, 2 and 3 are added 1 month to their registration date, that date is shown below as “Expire_Date”. Like DATE_SUB, there are two parameters, the 1st parameter is the date you want to add and the 2nd parameter is how long you want to add.

Image

View data by date (GROUP BY and COUNT)

 

Suppose you need user data according to the month. For example, with the following query, we will see the number of people who have registered in a month

Image

Output: See how many people have registered in a given month. In the DATE_FORMAT function, I have given the name of the column in the 1st parameter, which means we have to give a date here. And in the 2nd parameter you have to give how to format the date. Then the only parameter in the MONTH function is the date, the month will return the date you enter here. Since we have the “reg_date” column here as the date column, we have given it and all the data of the month returned due to GRUOP BY is added to the COUNT () function showing the number.

Image

I have given according to the month. If you want, you can also see the data on the basis of year and year. In case of year, only “YEAR” should be given in place of MONTH.

 

 

Leave a Reply

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