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
Database Articles
Page 211 of 547
Placing order according to the condition in MySQL?
For this, use ORDER BY CASE WHEN statement.Let us create a table −mysql> create table demo51 −> ( −> id int not null auto_increment primary key, −> name varchar(20) −> ); Query OK, 0 rows affected (1.08 sec)Insert some records into the table with the help of insert command −mysql> insert into demo51(name) values('John'); Query OK, 1 row affected (0.15 sec) mysql> insert into demo51(name) values('Bob'); Query OK, 1 row affected (0.09 sec) mysql> insert into demo51(name) values('David'); Query OK, 1 row affected (0.35 sec) mysql> insert into demo51(name) values('Sam'); Query OK, 1 row affected (0.14 sec)Display ...
Read MoreSum of digits of year in MySQL?
At first, you need to extract the last digit and add the extracted value. And the same goes in till we get the sum of all digits of the year, for example, for the year 2020 −2 + 0 + 2 + 0 = 4The concept is as follows to extract the last digit from the year. Following is the query −select floor(@yourVariableName % 10);Following is the query to sum the digits of year −mysql> set @year_column_value = 2020; Query OK, 0 rows affected (0.00 sec) mysql> select −> floor(@year_column_value / 1000) −> + floor(@year_column_value % 1000 / 100) −> ...
Read MoreCreating a table with a TIMESTAMP field in MySQL?
For this, you can use TIMESTAMP keyword in MySQL.Let us create a table −mysql> create table demo50 −> ( −> id int not null auto_increment primary key, −> start_date timestamp default current_timestamp not null, −> end_date timestamp default current_timestamp not null −> ); Query OK, 0 rows affected (1.35 sec)Insert some records into the table with the help of insert command −mysql> insert into demo50 values(); Query OK, 1 row affected (0.15 sec) mysql> insert into demo50(end_date) values('2020−12−21'); Query OK, 1 row affected (0.07 sec) mysql> insert into demo50(start_date) values('2020−01−01'); Query OK, 1 row affected (0.14 sec)Display records ...
Read MoreSelect all records if it contains specific number in MySQL?
For this, use concat() along with LIKE. Following is the syntax −select *from yourTableName where concat(', ', yourColumnName, ', ') like '%, yourValue, %';Let us create a table −mysql> create table demo49 −> ( −> id varchar(20) −> , −> first_name varchar(20) −> ); Query OK, 0 rows affected (1.45 sec)Insert some records into the table with the help of insert command −mysql> insert into demo49 values('4, 5, 6', −> 'Adam'); Query OK, 1 row affected (0.20 sec) mysql> insert into demo49 values('5, 3, 2', 'Mike'); Query OK, 1 row affected (0.19 sec) mysql> insert into demo49 values('3, ...
Read MoreAppend wildcards in SELECT with MySQL?
For appending, use the concept of concat(). The syntax is as follows −select *from yourTableName where yourColumnName like concat('%', yourValue, '%');Let us create a table −mysql> create table demo48 -> ( −> id int not null auto_increment primary key, −> name varchar(20) −> ); Query OK, 0 rows affected (0.70 sec)Insert some records into the table with the help of insert command −mysql> insert into demo48(name) values('John Smith'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo48(name) values('John Doe'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo48(name) values('Adam Smith'); Query OK, 1 row ...
Read MoreWhat is the MySQL syntax error in this query – Creating a table with reserved keyword?
Let’s say we tried creating a table with name “groups”, which is a reserved keyword in MySQL You cannot use “groups” because groups is a reserved keyword in MySQL.Following error occurred while creating a table with name “groups” −mysql> create table groups −> ( −> id int, −> name varchar(40) −> ); 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 'groups ( id int, name varchar(40) )' at line 1In order to create a table with reserved keyword, you need ...
Read MoreRemove specific fields/ rows and show other records in MySQL?
For this, use CASE WHEN statement in MySQL. Let us create a table −mysql> create table demo47 −> ( −> first_name varchar(20), −> last_name varchar(20) −> ); Query OK, 0 rows affected (1.57 sec)Insert some records into the table with the help of insert command −mysql> insert into demo47 values('John', 'Smith'); Query OK, 1 row affected (0.18 sec) mysql> insert into demo47 values('David', 'Miller'); Query OK, 1 row affected (0.11 sec) mysql> insert into demo47 values('John', 'Doe'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo47 values('Chris', 'Brown'); Query OK, 1 row affected (0.12 sec)Display ...
Read MoreHow to convert MM/YY to YYYY-MM-DD with a specific day in MySQL?
To convert, use STR_TO_DATE(), as in the below syntax. Concatenate the day value with CONCAT() −select str_to_date(concat('yourDateValue/', yourColumnName), '%d/%m/%y') as anyAliasName from yourTableName;Let us create a table −mysql> create table demo46 −> ( −> id int not null auto_increment primary key, −> short_date varchar(20) −> ); Query OK, 0 rows affected (0.60 sec)Insert some records into the table with the help of insert command −mysql> insert into demo46(short_date) values('09/18'); Query OK, 1 row affected (0.08 sec) mysql> insert into demo46(short_date) values('12/20'); Query OK, 1 row affected (0.12 sec) mysql> insert into demo46(short_date) values('11/20'); Query OK, 1 row affected ...
Read MoreSelect highest salary in MySQL?
For this, you can use MAX(). The syntax is as follows −select MAX(yourColumnName) AS anyAliasName from yourTableName;Let us create a table −mysql> create table demo44 −> ( −> employee_id int not null auto_increment primary key, −> employee_name varchar(20), −> employee_salary int −> ) −> ; Query OK, 0 rows affected (1.27 sec)Insert some records into the table with the help of insert command −mysql> insert into demo44(employee_name, employee_salary) values('John', 3000); Query OK, 1 row affected (0.13 sec) mysql> insert into demo44(employee_name, employee_salary) values('David', 4500); Query OK, 1 row affected (0.12 sec) mysql> insert into demo44(employee_name, employee_salary) values('Bob', 3500); ...
Read MoreCreating a Table in MySQL to set current date as default
Following is the syntax for creating a table and adding DEFAULT constraint to set default value −CREATE TABLE yourTableName ( yourColumnName1 dataType not null , yourColumnName2 dataType default anyValue, . . . N );;Let us create a table wherein we have set “employee_joining_date” with default constraint for current date as default −mysql> create table demo43 −> ( −> employee_id int not null auto_increment primary key, −> employee_name varchar(40) not null, −> employee_status varchar(60) default "NOT JOINED", −> employee_joining_date date default(CURRENT_DATE) −> ); Query OK, 0 rows affected (0.66 sec)Insert some records into the table with the help of insert command ...
Read More