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 216 of 355
Resolve ERROR 1064 (42000) that occurred after using varchar (without providing the size)
Let us first see when this situation can arise. Create a table and set column name with datatype but without the size −mysql> create table DemoTable ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar, LastName varchar ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', LastName varchar )' at line 4You can correct the above error by providing the size for varchar data type like varchar(100). The same will fix the issue.Let’s fix it ...
Read MoreWhile creating a MySQL table use the reserved keyword 'Key'
To use the reserved keyword ‘Key’, use the concept of the backtick symbol. Here, for our example, I am using the column name key which needs a backtick symbol around the column name.Let us first create a table −mysql> create table DemoTable ( `Key` int ); Query OK, 0 rows affected (0.67 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(101); Query OK, 1 row affected (0.55 sec) mysql> insert into DemoTable values(110); Query OK, 1 row affected (0.28 sec) mysql> insert ...
Read MoreMySQL SUM function to add decimal values
Let us first create a table −mysql> create table DemoTable ( Money DECIMAL(7, 2) ); Query OK, 0 rows affected (0.58 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(100.67); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values(199.33); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable values(500); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(400); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable values(800); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> ...
Read MoreMySQL query to select a record with two exact values?
For this, you can use GROUP BY HAVING clause. 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.52 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(Value) values(600); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Value) values(600); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable(Value) values(800); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(Value) values(800); Query OK, 1 row affected (0.09 sec)Display all records from the table ...
Read MoreDisplay records on the basis of key-value pairs in MySQL
For this, use JSON_OBJECTAGG(). Let us first create a table −mysql> create table DemoTable ( Id int, FirstName varchar(100), Age int ); Query OK, 0 rows affected (0.56 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(10, 'John', 23); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(20, 'Carol', 21); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(10, 'Sam', 24); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values(20, 'Chris', 20); Query OK, 1 row affected (0.13 sec)Display all records from the ...
Read MoreMySQL query to count the number of 0s and 1s from a table column and display them in two columns?
For this, you can use the aggregate function SUM(). Let us first create a table −mysql> create table DemoTable ( isMarried tinyint(1) ); Query OK, 0 rows affected (0.84 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.26 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable values(0); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable values(1); Query OK, 1 row affected (0.19 sec) mysql> ...
Read MoreHow to use COUNT() and IF() in a single MySQL query?
Let us first create a table −mysql> create table DemoTable ( isValidUser boolean ); Query OK, 0 rows affected (0.70 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values(true); Query OK, 1 row affected (0.19 sec) mysql> insert into DemoTable values(false); Query OK, 1 row affected (0.07 sec) mysql> insert into DemoTable values(false); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable values(true); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(true); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values(true); Query OK, 1 row affected ...
Read MoreFilter dates from a table with DATE and NULL records in MySQL
Let us first create a table −mysql> create table DemoTable ( FirstDate datetime, SecondDate datetime ); Query OK, 0 rows affected (0.57 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2019-01-21', '2018-01-21'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable values('2019-10-04', '2019-08-14'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable values('2019-05-01', '2019-09-11'); Query OK, 1 row affected (0.65 sec) mysql> insert into DemoTable values(NULL, NULL); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values('2019-03-01', NULL); Query OK, 1 row affected (0.13 sec)Display all records ...
Read MoreDynamically choosing a column in MySQL?
First, you need to prepare a query and then you need to execute the PREPARED statement to dynamically choose a column in MySQL.Let us first create a table −mysql> create table DemoTable ( EmployeeName varchar(100) ); Query OK, 0 rows affected (0.77 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('John Doe'); Query OK, 1 row affected (0.18 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+--------------+ | EmployeeName | +--------------+ | John Doe | +--------------+ 1 row in set ...
Read MoreHow to filter dates in MySQL to fetch date record only for a specific month?
Let us first create a table −mysql> create table DemoTable ( AdmissionDate varchar(100) ); Query OK, 0 rows affected (1.06 sec)Insert some records in the table using insert command −mysql> insert into DemoTable values('2018-01-21'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2019-08-13'); Query OK, 1 row affected (0.56 sec) mysql> insert into DemoTable values('2019-07-08'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('2016-02-12'); Query OK, 1 row affected (0.14 sec)Display all records from the table using select statement −mysql> select *from DemoTable;This will produce the following output −+---------------+ | AdmissionDate | +---------------+ ...
Read More