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 on Trending Technologies
Technical articles with clear explanations and examples
Display and concatenate records ignoring NULL values in MySQL
Use CONCAT() to concatenate records whereas IFNULL() to check for NULL values.Let us first create a table −mysql> create table DemoTable802 ( FirstName varchar(100), LastName varchar(100) ); Query OK, 0 rows affected (1.01 sec)Insert some records in the table using insert command −mysql> insert into DemoTable802 values('Adam', 'Smith'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable802 values('Carol', NULL); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable802 values(NULL, 'Taylor'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable802 values(NULL, NULL); Query OK, 1 row affected (0.21 sec)Display ...
Read MoreMySQL query to perform sort order on same field
For this, use ORDER BY IF().Let us first create a table −mysql> create table DemoTable801 ( Score int ); Query OK, 0 rows affected (0.69 sec)Insert some records in the table using insert command −mysql> insert into DemoTable801 values(30); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable801 values(99); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable801 values(45); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable801 values(55); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable801 values(99); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable801 values(69); ...
Read MoreMySQL select only a single value from 5 similar values?
Let us first create a table −mysql> create table DemoTable800 ( Value int ); Query OK, 0 rows affected (0.59 sec)Insert some records in the table using insert command. Here, we have inserted 5 similar values −mysql> insert into DemoTable800 values(10); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable800 values(10); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable800 values(10); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable800 values(10); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable800 values(10); Query OK, 1 row affected (0.10 sec)Display all ...
Read MoreWhy the #1054 - Unknown column error occurs in MySQL and how to fix it?
Let’s see when the #1054 error occurs in MySQL. While inserting a varchar value, if you will forget to add single quotes, then this error will arise. Following is the error −mysql> insert into DemoTable798 values(100, Adam); ERROR 1054 (42S22): Unknown column 'Adam' in 'field list'You need to use single quotes around the string value to fix this error as shown below −mysql> insert into DemoTable798 values(100, ’Adam’);Let us first create a table −mysql> create table DemoTable798 ( StudentId int, StudentName varchar(100) ); Query OK, 0 rows affected (0.51 sec)Insert some records in the ...
Read MoreWhat is the correct DateTime format for a MySQL Database?
The correct datetime format for MySQL database is as follows −‘YYYY-MM-DD HH:M:SS’Let us first create a table −mysql> create table DemoTable797 ( ArrivalDatetime datetime ); Query OK, 0 rows affected (0.68 sec)Insert some records in the table using insert command −mysql> insert into DemoTable797 values(NOW()); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable797 values('2016-12-21 12:50:34'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable797 values('2017-03-01 17:40:21'); Query OK, 1 row affected (0.24 sec)Display all records from the table using select statement −mysql> select *from DemoTable797;This will produce the following output. We have ...
Read MoreHow to display the day name on the basis of Date of Birth records in MySQL?
Use the DAYNAME() to display the day name from records with Date of Birth.Let us first create a table −mysql> create table DemoTable795 ( DateOfBirth date ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable795 values('1996-01-21'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable795 values('2004-11-01'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable795 values('1990-03-31'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable795 values('2010-12-03'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select ...
Read MoreWrite a single MySQL query to exclude a record and display NULL value
To check records which are NULL, use IS NULL. However, to exclude any of the records, use the NOT IN clause. Use both of them in the same query.Let us first create a table −mysql> create table DemoTable793 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100) ); Query OK, 0 rows affected (0.81 sec)Insert some records in the table using insert command −mysql> insert into DemoTable793(StudentName) values('Adam'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable793(StudentName) values('Bob'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable793(StudentName) values(null); Query ...
Read MoreReshuffle the values in a table with MySQL
To reshuffle the values in a table, use MySQL RAND().Let us first create a table −mysql> create table DemoTable792 ( Name varchar(100), Subject varchar(100) ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable792 values('Chris', 'MySQL'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable792 values('David', 'MySQL'); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable792 values('Robert', 'MongoDB'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable792 values('Carol', 'MongoDB'); Query OK, 1 row affected (0.18 sec)Display all records ...
Read MoreMySQL query to exclude some of the values from the table
Use NOT IN() to exclude some of the values from the table.Let us first create a table −mysql> create table DemoTable791 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(100) ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable791(FirstName) values('Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable791(FirstName) values('Robert'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable791(FirstName) values('David'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable791(FirstName) values('Mike'); Query OK, 1 row affected (0.11 ...
Read MoreGet the highest score value from a single column and the greatest from two columns in MySQL
Let us first create a table −mysql> create table DemoTable790 ( Score1 int, Score2 int ); Query OK, 0 rows affected (0.45 sec)Insert some records in the table using insert command −mysql> insert into DemoTable790 values(98, 76); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable790 values(78, 89); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable790 values(85, 68); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable790;This will produce the following output -+--------+--------+ | Score1 | Score2 | +--------+--------+ ...
Read More