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
Articles by AmitDiwan
Page 781 of 840
How to get the difference between date records and the current date in MySQL?
Let’s say the current date is 2019-09-06. For our example, we will first create a table −mysql> create table DemoTable ( AdmissionDate date ); Query OK, 0 rows affected (0.54 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-08'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values('2018-09-06'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('2016-10-26'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------------+ | AdmissionDate | +---------------+ | 2019-01-08 ...
Read MoreUpdate a column of text with MySQL REPLACE()
Let us first create a table −mysql> create table DemoTable ( Code varchar(100) ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('8565-9848-7474'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('9994-6464-8737'); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values('6574-9090-7643'); Query OK, 1 row affected (0.17 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------------+ | Code | +----------------+ | 8565-9848-7474 | | 9994-6464-8737 | | ...
Read MoreMySQL query to return a string as a result of IF statement?
Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, EmployeeSalary int ); Query OK, 0 rows affected (1.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(EmployeeSalary) values(12000); Query OK, 1 row affected (0.24 sec) mysql> insert into DemoTable(EmployeeSalary) values(20000); Query OK, 1 row affected (0.55 sec) mysql> insert into DemoTable(EmployeeSalary) values(11500); Query OK, 1 row affected (0.94 sec) mysql> insert into DemoTable(EmployeeSalary) values(15500); Query OK, 1 row affected (0.44 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce ...
Read MoreHow to update column values with date records and set 1 for corresponding records before the current date in SQL
Let’s say the current date is 2019-08-20. Now for our example, we will create a table −mysql> create table DemoTable ( ProductStatus tinyint(1), ProductExpiryDate date ); Query OK, 0 rows affected (1.03 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(0, '2019-06-12'); Query OK, 1 row affected (0.43 sec) mysql> insert into DemoTable values(0, '2019-10-11'); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable values(0, '2018-07-24'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(0, '2018-09-05'); Query OK, 1 row affected (0.27 sec)Display all records from the table ...
Read MoreMySQL query for grouping and summing the values based on specific records
Use GROUP BY to group records, whereas SUM() function is used to add. Let us first create a table −mysql> create table DemoTable ( Name varchar(40), Subject varchar(40), Marks int ); Query OK, 0 rows affected (2.89 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('Chris', 'MySQL', 76); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable values('Sam', 'MongoDB', 86); Query OK, 1 row affected (0.39 sec) mysql> insert into DemoTable values('Mike', 'MySQL', 98); Query OK, 1 row affected (0.46 sec) mysql> insert into DemoTable values('David', 'Java', 93); Query OK, ...
Read MoreUpdate the table by calculating the sum and display the result as last column value
Use a variable to store SUM(total) and update it with the UPDATE command. Let us first create a table −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, Value int ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values(70); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable(Value) values(100); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Value) values(150); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(Value) values(250); Query OK, 1 row affected (0.14 sec) mysql> ...
Read MoreHow can I return a record from a table nearest to a user-defined variables value in MySQL?
Let us first create a table −mysql> create table DemoTable ( CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, ProductAmount int ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(ProductAmount) values(5000); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(ProductAmount) values(6000); Query OK, 1 row affected (0.08 sec) mysql> insert into DemoTable(ProductAmount) values(7000); Query OK, 1 row affected (0.26 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+------------+---------------+ | CustomerId | ProductAmount | +------------+---------------+ | ...
Read MoreHow does COALESCE order results with NULL and NON-NULL values?
The COALESCE() finds the NON-NULL value first If it finds the same in the beginning, then it returns, otherwise moves ahead to check NON-NULL value.Let us first create a table −mysql> create table DemoTable ( Number1 int, Number2 int ); Query OK, 0 rows affected (5.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100, 200); Query OK, 1 row affected (0.40 sec) mysql> insert into DemoTable values(NULL, 50); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(10, NULL); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable ...
Read MoreMySQL query to find latest 3 dates in a table and the resultant dates shouldn't be duplicate
To find the latest dates, order the date records with ORDER BY DESC. Since we want only 3 dates, use LIMIT 3.Let us first create a table −mysql> create table DemoTable ( AdmissionDate date ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-09-04'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('2019-08-10'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('2019-09-21'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-09-18'); Query OK, 1 row affected (0.17 sec) mysql> ...
Read MoreDisplay specific name from a table with repeated individual FirstName and LastName using LIKE clause twice
Let us first create a table −mysql> create table DemoTable ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(30) ); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(StudentName) values('John Smith'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentName) values('David Miller'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(StudentName) values('Carol Taylor'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(StudentName) values('John Doe'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable(StudentName) values('Chris Brown'); Query OK, 1 row ...
Read More