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 More

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 More

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 More

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 More

To select all records with specific numbers, use the FIND_IN_SET() in MySQL.Let us create a table −Examplemysql> create table demo73    -> (    -> interest_id varchar(100),    -> interest_name varchar(100)    -> ); Query OK, 0 rows affected (1.48Insert some records into the table with the help of insert command −Examplemysql> insert into demo73 values("100, 101, 103, 105", "SSC"); Query OK, 1 row affected (0.34 mysql> insert into demo73 values("105, 103, 1005, 1003, 104", "Computer"); Query OK, 1 row affected (0.10 mysql> insert into demo73 values("110, 105, 104, 111", "Novel"); Query OK, 1 row affected (0.31Display records ... Read More

To convert, use str_to_date() in MySQLLet us create a table and add date records −Examplemysql> create table demo72    -> (    -> due_date varchar(40)    -> ); Query OK, 0 rows affected (2.96 sec)Insert some records into the table with the help of insert command −Examplemysql> insert into demo72 values("11/15"); Query OK, 1 row affected (0.26 sec) mysql> insert into demo72 values("02/20"); Query OK, 1 row affected (0.09 sec) mysql> insert into demo72 values("07/95"); Query OK, 1 row affected (0.15 sec)Display records from the table using select statement −Examplemysql> select *from demo72;This will produce the following output ... Read More

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 More

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)IRN223453IRN569022IRN997811Read More

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

The difference between AND, OR is that AND evaluates both conditions must be true for the overall condition to be true. The OR evaluates one condition must be true for the overall condition to be true.Let us create a table −mysql> create table demo70 −> ( −> id int not null auto_increment primary key, −> name varchar(20), −> age int −> ); Query OK, 0 rows affected (0.67 sec)Insert some records into the table with the help of insert command −mysql> insert into demo70(name, age) values('John', 23); Query OK, 1 row affected (0.18 sec) mysql> insert into demo70(name, age) ... Read More