Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
It is because without GROUP BY clause the output returned by MySQL can mislead. We are giving following example on the ‘Student’ table given below, to demonstrate it −mysql> Select * from Student; +------+---------+---------+-----------+ | Id | Name | Address | Subject | +------+---------+---------+-----------+ | 1 | Gaurav | Delhi | Computers | | 2 | Aarav | Mumbai | History | | 15 | Harshit | Delhi | Commerce | | 20 | Gaurav | Jaipur | Computers | +------+---------+---------+-----------+ 4 rows in set (0.00 sec) mysql> ... Read More
We can use COUNT(*) and GROUP BY clause to find out the repetition of a value in the column. Following is the example, using COUNT(*) and GROUP BY clause on ‘Name’ column of table ‘Student’, to demonstrate it −mysql> select count(*), name from student group by name; +----------+---------+ | count(*) | name | +----------+---------+ | 1 | Aarav | | 2 | Gaurav | | 1 | Harshit | +----------+---------+ 3 rows in set (0.00 sec)The result set of above query shows that which value is repeated ... Read More
The idea is to implement the fact that the Greedy approach provides the best solution for Fractional Knapsack problem.To check whether a particular node can give us a better solution or not, we calculate the optimal solution (through the node) implementing Greedy approach. If the solution calculated by Greedy approach itself is more than the best so far, then we can’t obtain a better solution through the node.Complete Algorithm is given below −Sort all items according to decreasing order of ratio of value per unit weight so that an upper bound can becalculated implementing Greedy Approach.Initialize maximum profit, such as ... Read More
We can offload the time/date handling to MySQL with the help of DATE_FORMAT() function. The date and time would be offloaded on the basis of format units passed as arguments to the function.For example, when we pass the date format units as arguments to MySQL DATE_FORMAT() function then MySQL offloaded only the date as follows −mysql> Select DATE_FORMAT("2017-10-22 13:03:45", "%Y %M %D")AS 'OFFLOADED DATE'; +-------------------+ | OFFLOADED DATE | +-------------------+ | 2017 October 22nd | +-------------------+ 1 row in set (0.00 sec)Whereas, when we pass the time format units as arguments to MySQL DATE_FORMAT() function then MySQL offloaded only ... Read More
TIME_FORMAT() function can be used in a similar fashion as DATE_FORMAT() function but it can only be used for offloading time values. MySQL returns a NULL value if TIME_FORMAT() function is used for offloading date values.For example, when we pass the time format units as arguments to MySQL TIME_FORMAT() function then MySQL offloaded only the time as follows −mysql> Select TIME_FORMAT("2017-10-22 13:03:45", "%h %i %s %p")AS 'OFFLOADED TIME'; +----------------+ | OFFLOADED TIME | +----------------+ | 01 03 45 PM | +----------------+ 1 row in set (0.00 sec)Whereas, when we pass the date format units as arguments to MySQL TIME_FORMAT() ... Read More
It can be done inflowing two ways −(A) With the help of EXRACT() function - EXTRACT() function can fetch any part from MySQL TIMESTAMP value. Following is the example of fetching month and day from a given date.mysql> Select EXTRACT(Month from '2017-10-22') AS 'MONTH'; +-------+ | MONTH | +-------+ | 10 | +-------+ 1 row in set (0.00 sec) mysql> Select EXTRACT(day from '2017-10-22')AS 'DAY'; +------+ | DAY | +------+ | 22 | +------+ 1 row in set (0.00 sec)(B) With the help of MONTH() or DAY() function - Rather than passing month and day as one of ... Read More
We can use 2-digit year value either in single date expression or in both date expressions used as argument/s in MySQL DATEDIFF() function.For example, the query below is using 2-digit year value in first date expression and other is having 4-digit year value.mysql> Select DATEDIFF('18-10-22', '2017-10-22'); +-----------------------------------+ | DATEDIFF('18-10-22', '2017-10-22') | +-----------------------------------+ | 365 | +-----------------------------------+ 1 row in set (0.00 sec)And the query below is using 2-digit year value in both date expressions.mysql> Select DATEDIFF('18-10-22', '17-10-22'); +---------------------------------+ | DATEDIFF('18-10-22', '17-10-22') | +---------------------------------+ | ... Read More
In MySQL, UNIX timestamp format is the way to represent time value as an integer. The integer value represented for the date value would be the number of seconds. The starting date for counting these number of seconds is ‘1970-01-01’.mysql> SELECT UNIX_TIMESTAMP('2017-10-22 04:05:36')AS 'Total Number of Seconds'; +-------------------------+ | Total Number of Seconds | +-------------------------+ | 1508625336 | +-------------------------+ 1 row in set (0.00 sec)The UNIX_TIMESTAMP value is 10 digits long.
The range of date time value that we can pass as an argument to MySQL UNIX_TIMESTAMP function is the same as the range of TIMESTAMP data type i.e. between ‘1970-01-01 00:00:01’ to ‘2038-01-19 08:44:07’. If we give the date time values in UNIX_TIMESTAMP function beyond or below TIMESTAMP range, MySQL will return 0 as output. It can be understood with the help of the following example −mysql> select UNIX_TIMESTAMP('2038-01-19 08:44:07'); +---------------------------------------+ | UNIX_TIMESTAMP('2038-01-19 08:44:07') | +---------------------------------------+ | 2147483647 | +---------------------------------------+ 1 row in set (0.00 sec) ... Read More
In this case, MySQL will take into consideration right most unit given in compound INTERVAL unit. It will return the output after calculating the interval on the basis of single value provided in the enclosed set of unit values. The following example will clarify it −mysql> Select TIMESTAMP('2017-10-22 04:05:36' + INTERVAL '2 ' year_month) AS 'Only Month Value Changed'; +--------------------------+ | Only Month Value Changed | +--------------------------+ | 2017-12-22 04:05:36 | +--------------------------+ 1 row in set (0.00 sec)The query above changes the month (right most in compound INTERVAL unit) from 10 to 12 based on the single value ... Read More