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
MySQLi Articles
Page 17 of 341
How to check if any value is Null in a MySQL table single row?
For this, you can use ISNULL in MySQL.Let us create a table −Examplemysql> create table demo86 -> ( -> value1 varchar(20) -> , -> value2 varchar(20) -> ); Query OK, 0 rows affected (2.77Insert some records into the table with the help of insert command −Examplemysql> insert into demo86 values(null, null); Query OK, 1 row affected (0.34 mysql> insert into demo86 values(null, 'John'); Query OK, 1 row affected (0.16 mysql> insert into demo86 values('David', 'Mike'); Query OK, 1 row affected (0.17 mysql> insert into demo86 values('Sam', null); Query OK, 1 row affected ...
Read MoreMySQL - SUM rows with same ID?
To sum rows with same ID, use the GROUP BY HAVING clause.Let us create a table −Examplemysql> create table demo84 -> ( -> id int, -> price int -> ) -> ; Query OK, 0 rows affected (0.60Insert some records into the table with the help of insert command −Examplemysql> insert into demo84 values(1, 2000); Query OK, 1 row affected (0.08 mysql> insert into demo84 values(1, 2000); Query OK, 1 row affected (0.14 mysql> insert into demo84 values(2, 1800); Query OK, 1 row affected (0.14 mysql> insert into demo84 values(2, 2200); Query ...
Read MoreShow date like 30-04-2020 instead of 2020-04-30 from MySQL database?
For this, use DATE_FORMAT() in MySQL. The syntax is as follows −Exampleselect date_format(yourColumnName, '%d-%m-%Y') as anyAliasName from yourTableName;Let us create a table −Examplemysql> create table demo80 -> ( -> due_date date -> ); Query OK, 0 rows affected (0.74Insert some records into the table with the help of insert command −Examplemysql> insert into demo80 values('2020-04-30'); Query OK, 1 row affected (0.14 mysql> insert into demo80 values('2016-01-10'); Query OK, 1 row affected (0.17 mysql> insert into demo80 values('2018-03-21'); Query OK, 1 row affected (0.12Display records from the table using select statement −Examplemysql> select *from demo80;This will ...
Read MoreHow to get username using ID from another table in MySQL database?
To get username using ID from two tables, you need to use JOIN and join the tables.Let us create a table −Examplemysql> create table demo77 -> ( -> userid int not null primary key, -> username varchar(20) -> ); Query OK, 0 rows affected (2.63Insert some records into the table with the help of insert command −Examplemysql> insert into demo77 values(1, 'John'); Query OK, 1 row affected (0.19 mysql> insert into demo77 values(2, 'Bob'); Query OK, 1 row affected (0.36Display records from the table using select statement −Examplemysql> select *from demo77;This will produce the following ...
Read MoreFind all users with a unique last name in MySQL?
To find all users with unique last name, use GROUP BY HAVING COUNT().Let us create a table −Examplemysql> create table demo76 -> ( -> firstName varchar(20), -> lastName varchar(20) -> ); Query OK, 0 rows affected (9.29Insert some records into the table with the help of insert command −Examplemysql> insert into demo76 values('John', 'Doe'); Query OK, 1 row affected (2.52 mysql> insert into demo76 values('David', 'Smith'); Query OK, 1 row affected (6.31 mysql> insert into demo76 values('Adam', 'Smith'); Query OK, 1 row affected (1.52Display records from the table using select statement −Examplemysql> select *from ...
Read MoreFetching rows updated at timestamp older than 1 day in MySQL?
For this, yYou can use from_unixtime() along with now().Let us create a table with some data type −Examplemysql> create table demo75 -> ( -> due_date int(11) -> ); Query OK, 0 rows affected, 1 warning (2.87Insert some records into the table with the help of insert command −Examplemysql> insert into demo75 values(unix_timestamp("2020-01-10")); Query OK, 1 row affected (0.46 mysql> insert into demo75 values(unix_timestamp("2020-11-19")); Query OK, 1 row affected (0.59 mysql> insert into demo75 values(unix_timestamp("2020-12-18")); Query OK, 1 row affected (0.44 mysql> insert into demo75 values(unix_timestamp("2020-11-10")); Query OK, 1 row affected (0.70Display records from the ...
Read MoreMySQL LIKE query with dynamic array?
To implement LIKE query with dynamic array, the syntax is as follows −Exampleselect *from yourTableName where yourColumnName2 like "%yourValue%" order by yourColumnName1 asc limit yourLimitValue;Let us create a table −Examplemysql> create table demo74 -> ( -> user_id int not null auto_increment primary key, -> user_names varchar(250) -> ) -> ; Query OK, 0 rows affected (0.67Insert some records into the table with the help of insert command −Examplemysql> insert into demo74(user_names) values("John Smith1, John Smith2, John Smith3"); Query OK, 1 row affected (0.18 mysql> insert into demo74(user_names) values("John Smith1"); Query OK, ...
Read MoreWhat is the execution result when non-SQL changes are made in the program without BIND?
Problem: A COBOL-DB2 program is changed to increase the length of a variable from PIC X(5) to PIC X(8). However, there are no changes in the SQL of the program. What will be the result if the program's plan/package is not binded for these changes?SolutionThe variable length change from PIC X(5) to PIC X(8) is not a DB2 change and there are no modifications required for SQL statements in the program. However, still we need to BIND its PLAN/PACKAGE else we will get SQL error code -818 which states that “THE PRECOMPILER-GENERATED TIMESTAMP x IN THE LOAD MODULE IS DIFFERENT ...
Read MoreExample of SQL query describing COUNT function and GROUP BY
Problem: Write a query on TRANSACTIONS DB2 table to list down the number of orders (ORDER_ID) assigned to a particular transaction (TRANSACTION_ID).SolutionWe can use the below query to find out the number of orders assigned to a particular transaction id on TRANSACTIONS DB2 table.ExampleSELECT TRANSACTION_ID, COUNT(ORDER_ID) FROM TRANSACTIONS GROUP BY TRANSACTION_IDWe will use GROUP BY function on the ORDER_ID to fetch the result order wise. The COUNT function will count the number of orders. For example, we have below DB2 ORDERS table.TRANSACTION_IDORDER_IDIRN22345A23118IRN22345A45901IRN22345A67990IRN56902A23119IRN99781A67921IRN56902A23167 The result of our DB2 query will return the below result.TRANSACTION_IDCOUNT(ORDER_ID)IRN223453IRN569022IRN997811
Read MoreHow does spring boot connect localhost MySQL
For this, use application.properties −spring.datasource.username=yourMySQLUserName spring.datasource.password=yourMySQLPassword spring.datasource.url=jdbc:mysql://localhost:3306/yoruDatabaseName spring.datasource.driver-class-name=com.mysql.cj.jdbc.DriverTo understand the above syntax, let us create a table −mysql> create table demo71 −> ( −> id int, −> name varchar(20) −> ); Query OK, 0 rows affected (3.81 sec)Insert some records into the table with the help of insert command −mysql> insert into demo71 values(100, 'John'); Query OK, 1 row affected (0.13 sec) mysql> insert into demo71 values(101, 'David'); Query OK, 1 row affected (0.49 sec) mysql> insert into demo71 values(102, 'Bob'); Query OK, 1 row affected (0.15 sec)Display records from the table using select statement −mysql> select *from ...
Read More