For specific day of week, use DAYOFWEEK().Let us first create a table −mysql> create table DemoTable785 ( CustomerId int NOT NULL AUTO_INCREMENT PRIMARY KEY, CustomerName varchar(100), ShoppingDate date ); Query OK, 0 rows affected (0.61 sec)Insert some records in the table using insert command −mysql> insert into DemoTable785(CustomerName, ShoppingDate) values('Chris', '2019-07-03'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable785(CustomerName, ShoppingDate) values('Robert', '2019-07-01'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable785(CustomerName, ShoppingDate) values('David', '2019-07-06'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable785(CustomerName, ShoppingDate) ... Read More

To ignore quotes while ordering column values, use ORDER BY TRIM().Let us first create a table −mysql> create table DemoTable784 ( Message varchar(100) ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable784 values('Good'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable784 values('\"This is not a Message\"'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable784 values('Bye'); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable784 values('Hello'); Query OK, 1 row affected (0.15 sec)Display all records from the table using ... Read More

Let us first create a table −mysql> create table DemoTable783 ( FirstName varchar(100) ); Query OK, 0 rows affected (0.65 sec)Insert some records in the table using insert command −mysql> insert into DemoTable783 values('Adam'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable783 values('Chris'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable783 values('Chris'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable783 values('Adam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable783 values('Adam'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable783 values('Robert'); Query OK, 1 ... Read More

For this, use GROUP BY HAVING clause.Let us first create a table −mysql> create table DemoTable782 ( Name varchar(100), Score int ); Query OK, 0 rows affected (1.18 sec)Insert some records in the table using insert command −mysql> insert into DemoTable782 values('John', 156); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable782 values('Carol', 250); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable782 values('Bob', 140); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable782 values('John', 126); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable782 values('John', ... Read More

Let us first create a table −mysql> create table DemoTable781 ( Name varchar(100) ); Query OK, 0 rows affected (0.66 sec)Insert some records in the table using insert command −mysql> insert into DemoTable781 values(''); Query OK, 1 row affected (0.29 sec) mysql> insert into DemoTable781 values('Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable781 values(''); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable781 values(null); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable781 values(null); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable781 values(''); Query OK, 1 ... Read More

To exclude some of the column names, use NOT IN.Let us first create a table −mysql> create table DemoTable780 (    CustomerId int,    CustomerName varchar(100),    CustomerAge int, CustomerCountryName varchar(100), isMarried tinyint(1) ); Query OK, 0 rows affected (0.47 sec)Here is the query to exclude result −mysql> select group_concat(column_name) from `information_schema`.`COLUMNS` m where table_schema = 'web' and table_name = 'DemoTable780' and column_name not in ('CustomerId','CustomerCountryName') group by table_schema,table_name;This will produce the following output -+------------------------------------+ | group_concat(column_name) | +------------------------------------+ | CustomerName,CustomerAge,isMarried | +------------------------------------+ 1 row in set (0.01 sec)

Since the column value ‘ADD’ is already a reserved word, therefore you need to use backticks around the word ADD like `ADD`.Let us see an example and create a table −mysql> create table DemoTable779 ( Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, `ADD` int ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable779(`ADD`) values(54); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable779(`ADD`) values(89); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable779(`ADD`) values(66); Query OK, 1 row affected ... Read More

Let us first create a table −mysql> create table DemoTable778 ( ClientId varchar(100), ClientName varchar(100) ); Query OK, 0 rows affected (1.05 sec)Insert some records in the table using insert command −mysql> insert into DemoTable778 values('J-101', 'John'); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable778 values('A-102', 'Adam'); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable778 values('C-103', 'Chris'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable778 values('D-104', 'David'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable778 values('R-105', 'Robert'); Query OK, 1 row affected ... Read More

Let us first create a table −mysql> create table DemoTable777 ( StudentId int NOT NULL AUTO_INCREMENT PRIMARY KEY, StudentName varchar(100), StudentAge int ); Query OK, 0 rows affected (1.34 sec)Insert some records in the table using insert command −mysql> insert into DemoTable777(StudentName, StudentAge) values('Chris', 23); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable777(StudentName, StudentAge) values('Robert', 21); Query OK, 1 row affected (0.22 sec) mysql> insert into DemoTable777(StudentName, StudentAge) values('Mike', 19); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable777(StudentName, StudentAge) values('Bob', 22); Query OK, 1 row ... Read More

Let’s say you have set column values as unique key and try to insert duplicate values in the table. This will lead to ERROR 1062 (23000): Duplicate entry.Let us first create a table -mysql> create table DemoTable776 (    FirstValue int,    SecondValue int,    unique key(FirstValue, SecondValue) ); Query OK, 0 rows affected (0.74 sec)Insert some records in the table using insert command. While inserting duplicate value, the same error arises as shown below -mysql> insert into DemoTable776 values(10, 20); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable776 values(10, 40); Query OK, 1 row affected (0.15 ... Read More