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 317 of 547
How can we use WHERE condition when creating a table with CTAS (Create Table as Selected) script?
As we know that we can copy the data and structure from an existing table by CTAS script. Use of WHERE clause is demonstrated in the example belowmysql> Create table EMP_BACKUP2 AS SELECT * from EMPLOYEE WHERE id = 300 AND Name = 'Mohan'; Query OK, 1 row affected (0.14 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Select * from EMP_BACKUP2; +------+-------+ | Id | Name | +------+-------+ | 300 | Mohan | +------+-------+ 1 row in set (0.00 sec)In the example above, we have created a table named EMP_BACKUP1 from table ‘Employee’ with some conditions. MySQL creates the table with only one row based on those conditions.
Read MoreHow can we ignore the negative values return by MySQL DATEDIFF() function?
As we know that DATEDIFF() function is used to get the difference in a number of days between two dates. Hence, it is quite possible that it returns negative value as well.mysql> select * from differ; +------------+-------------+ | OrderDate | WorkingDate | +------------+-------------+ | 2017-10-22 | 2017-10-29 | | 2017-10-25 | 2017-10-30 | | 2017-10-25 | 2017-11-30 | +------------+-------------+ 3 rows in set (0.00 sec)Above query will return the values from table ‘differ’. Now, if someone wants to get the difference between OrderDate and WorkingDate then the output would be negative as follows −mysql> Select DATEDIFF(OrderDate, WorkingDate)AS 'DIFFERENCE IN DAYS' ...
Read MoreWith the help of function, how can we return the difference in Year, Month and Days between two date values?
We can create a function, which accepts the date values as its argument and returns the difference in year, month and days, as followsmysql> CREATE FUNCTION date_difference(Date1 DATE, date2 DATE) RETURNS VARCHAR(30) -> RETURN CONCAT( -> @years := TIMESTAMPDIFF(YEAR, date1, date2), IF (@years = 1, ' year, ', ' years, '), -> @months := TIMESTAMPDIFF(MONTH, DATE_ADD(date1, INTERVAL @years YEAR), date2), IF (@months = 1, ' month, ', ' months, '), -> @days := TIMESTAMPDIFF(DAY, DATE_ADD(date1, INTERVAL @years * 12 + @months MONTH), date2), IF (@days = 1, ' day', ' days')) ; Query OK, 0 ...
Read MoreHow can we fetch all the records from a particular MySQL table?
We can fetch all the record from a MySQL table by using SELECT * from table_name; query. An example is as follows, fetched all the records from ‘Employee’ table −mysql> Select * from Employee; +------+--------+ | Id | Name | +------+--------+ | 100 | Ram | | 200 | Gaurav | | 300 | Mohan | +------+--------+ 3 rows in set (0.00 sec)
Read MoreWhat are the different ways in MySQL to add 'half year interval' in date?
We can add ‘half year interval’ in date ith the following ways −(A) By adding interval of 6 Monthsmysql> Select '2017-06-20' + INTERVAL 6 Month AS 'After Half Year Interval'; +--------------------------+ | After Half Year Interval | +--------------------------+ | 2017-12-20 | +--------------------------+ 1 row in set (0.00 sec)(B) By adding interval of 2 quartersmysql> Select '2017-06-20' + INTERVAL 2 Quarter AS 'After Half Year Interval'; +--------------------------+ | After Half Year Interval | +--------------------------+ | 2017-12-20 | +--------------------------+ 1 row in set (0.00 sec)Above query will add half year interval in date with the help of Quarter keyword.
Read MoreHow can it be possible to add 3 months interval in a MySQL date without using the word 'Months' with interval?
It is possible with the help of keyword Quarter as follows −mysql> Select '2017-06-20' + INTERVAL 1 Quarter AS 'After 3 Months Interval'; +-------------------------+ | After 3 Months Interval | +-------------------------+ | 2017-09-20 | +-------------------------+ 1 row in set (0.00 sec)
Read MoreHow can we fetch one or more columns as output from a MySQL table?
The SELECT command can be used to fetch one or more columns as output from MySQL table. An example is given below to fetch one or more columnsmysql> Select * from Student; +------+---------+---------+-----------+ | Id | Name | Address | Subject | +------+---------+---------+-----------+ | 1 | Gaurav | Delhi | Computers | | 2 | Aarav | Mumbai | History | | 15 | Harshit | Delhi | Commerce | | 17 | Raman | Shimla | Computers | +------+---------+---------+-----------+ 4 rows in set (0.01 sec) ...
Read MoreHow can I add one day to DATETIME field in MySQL query?
With the help of DATE_ADD() function, we can add one day to the DATETIME field of a table.mysql> Select StudentName, RegDate, Date_ADD(RegDate, INTERVAL +1 day) AS 'NEXT DAY' from testing where StudentName = 'gaurav'; +-------------+---------------------+---------------------+ | StudentName | RegDate | NEXT DAY | +-------------+---------------------+---------------------+ | Gaurav | 2017-10-29 08:48:33 | 2017-10-30 08:48:33 | +-------------+---------------------+---------------------+ 1 row in set (0.00 sec)Above query will add one day to the RegDate where StudentName is Gaurav in MySQL table named ‘testing’.
Read MoreWhat is the way to get self-computed output from MySQL without a dummy table named dual?
In MySQL, we can simply specify the SELECT conditions all alone to get the self-computed output. Following example will demonstrate it −mysql> Select 1+1; +-----+ | 1+1 | +-----+ | 2 | +-----+ 1 row in set (0.02 sec) mysql> Select 1; +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec)
Read MoreHow can we use MySQL self-computed output from any expression, function etc. for inserting values in a row?
While inserting the values in a row, we can use the value of self-computed output from any expression, function etc. Here is an example to demonstrate it −mysql> Insert into employee(id, emp_name)Select 1+1, Concat_ws(' ','Gaurav', 'Kumar'); Query OK, 1 row affected (0.04 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> Select * from employee; +------+--------------+ | id | emp_name | +------+--------------+ | 2 | Gaurav Kumar | +------+--------------+ 1 row in set (0.00 sec)
Read More