Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Database Articles
Page 275 of 547
How MySQL SUM() function evaluates if it is used with SELECT statement that returns no matching rows?
When MySQL SUM() function used with SELECT statement that returns no matching rows then there is nothing to evaluate and it returns NULL as output. Sometimes, we thought it must return 0 as output but 0 is a number itself and for no matching rows it not significant to return 0 hence it returns NULL. To understand the above concept, consider an ‘employee_tbl’ table, which is having the following records −mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2007-01-24 | 250 ...
Read MoreWhat is the benefit of using MySQL SUM() function with GROUP BY clause?
When we use MySQL SUM() function with GROUP BY Clause the SUM() function evaluates the sum for every group specified in the GROUP BY clause. The benefit of using SUM() with GROUP BY clause is that we can easily find the total of a particular group. To understand the above concept, consider an ‘employee_tbl’ table, which is having the following records −mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2007-01-24 | 250 | | 2 | Ram ...
Read MoreHow can we use MySQL SUM() function with HAVING clause?
By using MySQL SUM() function with the HAVING clause, it filters the result based on a specific condition given after the HAVING clause. To understand the above concept, consider an ‘employee_tbl’ table, which is having the following records −mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+--------------------+ | 1 | John | 2007-01-24 | 250 | | 2 | Ram | 2007-05-27 | 220 | | 3 | Jack | 2007-05-06 | ...
Read MoreWhat are the prerequisites for starting writing and using MySQL stored procedure?
We must have the following prerequisites before starting writing and using MySQL stored procedures −MySQL VersionAs we know that MySQL 5 introduced stored procedures, hence first of all we need to check for the version of MySQL before staring writing and using stored procedures. It can be done with the following query −mysql> Select VERSION(); +-----------+ | VERSION() | +-----------+ | 5.7.20 | +-----------+ 1 row in set (0.10 sec)Privileges for the current userActually, CREATE PROCEDURE and CREATE FUNCTION require the CREATE ROUTINE privilege. By default, MySQL automatically grants the ALTER ROUTINE and EXECUTE privileges to the routine creator. ...
Read MoreHow to use MySQL Date functions with WHERE clause?
By using the WHERE clause with any of the MySQL date functions, the query will filter the rows based on the condition provided in the WHERE clause. To understand it, consider the data from ‘Collegedetail’ table as followsmysql> Select * from Collegedetail; +------+---------+------------+ | ID | Country | Estb | +------+---------+------------+ | 111 | INDIA | 2010-05-01 | | 130 | INDIA | 1995-10-25 | | 139 | USA | 1994-09-25 | | 1539 | UK ...
Read MoreHow can we extract the Year and Month from a date in MySQL?
It can be done with the following three ways in MySQLBy using EXTRACT() function For extracting YEAR and MONTH collectively then we can use the EXTRACT function. We need to provide the YEAR_MONTH as an argument for this function. To understand it, consider the following function using the data from table ‘Collegedetail’ −mysql> Select EXTRACT(YEAR_MONTH From estb) from collegedetail; +-------------------------------+ | EXTRACT(YEAR_MONTH From estb) | +-------------------------------+ | 201005 | | ...
Read MoreHow to get the MySQL interactive output format in batch mode also?
We can get the MySQL output format in batch mode with the help of –t option. For example, after running the same query in batch mode with –t option we will get the output like interactive format.ExampleC:\Program Files\MySQL\bin>mysql -u root -p gaurav < hh.sql -t Enter password: *****Output+------+ | id | +------+ | 1 | | 2 | +------+
Read MoreWhat would be the difference between default output format when running MySQL in batch mode or interactively?
The default MySQL output would be different if we will run the same query interactively or in batch mode. For example, if we will run the query select * from hh interactively then following would be a format of output −mysql> select * from hh; +------+ | id | +------+ | 1 | | 2 | +------+ 2 rows in set (0.01 sec)On the other hand, if we will run the same query in batch mode then following would be the format of output −C:\Program Files\MySQL\bin>mysql -u root -p gaurav < hh.sql Enter password: ***** id 1 2
Read MoreHow can we add day/s in the date stored in a column of MySQL table?
Two functions can be used for this purpose and in both the functions we need to provide column name as an argument along with INTERVAL keyword. The functions are as follows −DATE_ADD() functionThe syntax of this function is DATE_ADD(date, INTERVAL expression unit). It can be demonstrated by following the example which uses the data from table ‘collegedetail’ −mysql> Select estb, DATE_ADD(estb, INTERVAL 10 DAY) from collegedetail; +------------+---------------------------------+ | estb | DATE_ADD(estb, INTERVAL 10 DAY) | +------------+---------------------------------+ | 2010-05-01 | 2010-05-11 | | 1995-10-25 | 1995-11-04 ...
Read MoreHow to check table status of the tables in a particular MySQL database?
We can check the status of tables in a database with the help of show table status statement. For example, in the database named tutorial, by executing this statement we can get the status of tables as follows −mysql> show table status \G*************************** 1. row *************************** Name: student Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 7340032 Auto_increment: NULL Create_time: 2017-10-24 09:34:29 ...
Read More