To display only the month number, use DATE_FORMAT() along with STR_TO_DATE(). Let us first create a table:mysql> create table DemoTable1320 -> ( -> MonthName varchar(20) -> ); Query OK, 0 rows affected (0.43 sec)ExampleInsert some records in the table using insert command −mysql> insert into DemoTable1320 values('10/10/2010'); Query OK, 1 row affected (0.17 sec) mysql> insert into DemoTable1320 values('11/12/2018'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1320 values('12/01/2019'); Query OK, 1 row affected (0.15 sec)Display all records from the table using select statement −mysql> select *from DemoTable1320;Output+------------+ | MonthName  | +------------+ | 10/10/2010 | | 11/12/2018 | ... Read More

To create a table from an already created table, use CREATE TABLE AS SELECT statement. Let us first create a table −mysql> create table DemoTable1318 -> ( -> Id int, -> FirstName varchar(10), -> LastName varchar(10), -> Age int -> ); Query OK, 0 rows affected (0.50 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1318 values(1, 'Chris', 'Brown', 21); Query OK, 1 row affected (0.20 sec) mysql> insert into DemoTable1318 values(2, 'David', 'Miller', 24); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1318 values(3, 'Carol', 'Taylor', 23); Query OK, 1 row affected (0.11 ... Read More

In MySQL, you can easily return multiple results, but also achieve this with subquery using IN(). Let us first create a table −mysql> create table DemoTable1317 -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> Name varchar(20) -> ); Query OK, 0 rows affected (0.49 sec)Insert some records in the table using insert commandmysql> insert into DemoTable1317(Name) values('Chris Brown'); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable1317(Name) values('John Doe'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1317(Name) values('Adam Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable1317(Name) values('John Smith'); ... Read More

Let us first create a table −mysql> create table DemoTable1316 -> ( -> Value varchar(40) -> ); Query OK, 0 rows affected (0.47 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1316 values('MySQL'); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1316 values('Java'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable1316 values('MongoDB'); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1316 values('C++'); Query OK, 1 row affected (0.13 sec)Display all records from the table using select statement −mysql> select *from DemoTable1316;This will produce the following output. These are the ... Read More

It’s easy to convert empty values to NULL using SET and WHERE. Let us first create a table −mysql> create table DemoTable1315 -> ( -> CountryName varchar(10) -> ); Query OK, 0 rows affected (0.60 sec)Insert some records in the table using insert command. We have set some empty values here as well −mysql> insert into DemoTable1315 values('US'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1315 values(''); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable1315 values('UK'); Query OK, 1 row affected (0.70 sec) mysql> insert into DemoTable1315 values(''); Query OK, 1 row affected (0.12 ... Read More

Set the comma-separated string in the IN() as in the below syntax:select *from yourTableName where yourColumnName IN('yourCommaSeparatedValue');Let us first create a table −mysql> create table DemoTable1314 -> ( -> Number varchar(100) -> ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1314 values('45, 67, 89'); Query OK, 1 row affected (0.13 sec) mysql> insert into DemoTable1314 values('10, 20, 50'); Query OK, 1 row affected (0.10 sec) mysql> insert into DemoTable1314 values('90, 56, 45'); Query OK, 1 row affected (0.23 sec)Display all records from the table using select statement −mysql> select ... Read More

Use ORDER BY and set the difference to display records ordered by numeric difference. Following is the syntax −select *from yourTableName order by (yourIntegerColumnName1 - yourIntegerColumnName2);Let us first create a table −mysql> create table DemoTable1313 -> ( -> Name varchar(20), -> Score1 int, -> Score2 int -> ); Query OK, 0 rows affected (3.48 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1313 values('Chris', 40, 60); Query OK, 1 row affected (0.38 sec) mysql> insert into DemoTable1313 values('David', 70, 50); Query OK, 1 row affected (0.16 sec) mysql> insert into DemoTable1313 values('Adam', 35, 30); Query OK, ... Read More

To get the sum of count from different tables, use UNION ALL. Let us first create a table −mysql> create table DemoTable1 -> ( -> Id int, -> Name varchar(30) -> ); Query OK, 0 rows affected (1.55 sec)Insert some records in the table using insert command −mysql> insert into DemoTable1 values(10, 'Chris Brown'); Query OK, 1 row affected (0.83 sec) mysql> insert into DemoTable1 values(20, 'David Miller'); Query OK, 1 row affected (0.50 sec) mysql> insert into DemoTable1 values(30, 'John Adam'); Query OK, 1 row affected (0.83 sec)Display all records from the table using select statement −mysql> select *from ... Read More

Problem statementER diagram is pictorial representation of shows various tables and relations amongst them. ER diagram we can reduce the number of database.One to one cardinalityLet us consider below diagram with one to one cardinality −Above ER diagram represents 3 entities −Employee entity has 2 attributes namely emp_name. emp_id is the primary keyCompany entity has 2 attributes namely cmp_name. cmp_id is the primary keyPrimary key of Work entity can be emp_id or cmp_idWe cannot combine 3 tables into single one can either merge Work into Employee or Company. minimum 2 tables are required in one to one cardinality scenario.One to ... Read More

Let us first create a table −mysql> create table DemoTable (    Id int NOT NULL AUTO_INCREMENT PRIMARY KEY,    FirstName varchar(20),    LastName varchar(20) ); Query OK, 0 rows affected (0.53 sec)Insert some records in the table using insert command −mysql> insert into DemoTable(FirstName, LastName) values('John', 'Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into DemoTable(FirstName, LastName) values('David', 'Miller'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable(FirstName, LastName) values('John', 'Doe'); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable(FirstName, LastName) values('Chris', 'Brown'); Query OK, 1 row affected (0.15 sec)Display all records from ... Read More