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
MySQL Articles
Page 194 of 355
How 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 MoreHow to extract only the numbers from a text field in MySQL?
Let us first create a table −mysql> create table DemoTable ( Number text ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('7364746464, -'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values('-, 8909094556'); Query OK, 1 row affected (0.23 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------------+ | Number | +--------------+ | 7364746464, - | | -, 8909094556 | +--------------+ 2 rows in set (0.00 sec)Following is ...
Read MoreFormat date with DATE_FORMAT() and STR_TO_DATE() in MySQL
Let us first create a table −mysql> create table DemoTable ( DueDate varchar(100) ); Query OK, 0 rows affected (0.62 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('August 04, 2019'); Query OK, 1 row affected (0.25 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+----------------+ | DueDate | +----------------+ | August 04, 2019 | +----------------+ 1 row in set (0.00 sec)Following is the query to format date −mysql> set @stringToDate=(select date_format(str_to_date(DueDate, '%M %d, %Y'), '%Y-%m-%d') from ...
Read MoreTo return value of a number raised to the power of another number, we should use ^ operator in MySQL?
No, ^ is the Bitwise XOR operator in MySQL. For this, use POW() or POWER() from MySQL. Let us first create a table &minuns;mysql> create table DemoTable ( BaseValue int, PowerValue float ); Query OK, 0 rows affected (0.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(4, 1.9867); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(10, 6.789); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(20, 8.9); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from ...
Read More