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: How will the COBOL-DB2 program behave when there is a mismatch between the host variable and number of columns in the SELECT statement?SolutionIn case there is a mismatch in the number of columns and number of host variables, the query will fail. For example, if we have used the below query in a COBOL-DB2 program which processes the ORDERS DB2 table.ExampleEXEC SQL    SELECT ORDER_ID,       ORDER_AMOUNT,       ORDER_DATE,       ORDER_STATUS    INTO :WS-ORDER-ID,       :WS-ORDER-AMOUNT,       :WS-ORDER-DATE,    FROM ORDERS WHERE ORDER_DATE = ‘2020-09-15’ END-EXECThere is a mismatch ... Read More

Problem: What is the purpose of the "FOR UPDATE OF" clause in a cursor? What will happen if we fire an UPDATE statement without using this clause in a COBOL-DB2 program?SolutionThe “FOR UPDATE OF” clause is given in the cursor declaration and it is used when we want to update the table. All the columns which need to be updated should be given in the cursor declaration.The “FOR UPDATE OF” clause will place the exclusive lock on all the qualifying rows once the cursor is open. We can also update the table without using “FOR UPDATE CLAUSE” but in that ... Read More

Problem: Is it possible to update a CURSOR in which we have used JOIN on 2 tables ORDERS and TRANSACTIONS? Why or why not? How can we proceed to UPDATE any of these tables?SolutionWhenever we use JOIN in a cursor on two or more tables (ORDERS and TRANSACTIONS in this case) a temporary table is generated in the virtual memory. However, since this is a temporary table we can fetch data from this table but it is not possible to update this table.If we want to update any of the tables used in the JOIN then we have to declare ... 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: The DB2 program PROGA was changed and compiled, but it was binded after 3 days. What will happen if we execute this program on the 4th day?SolutionThe pre-compiler inserts the current timestamp in the modified source code and in DBRM. In case of modified source code, this timestamp is passed on to the load module and in case of DBRM the timestamp is passed on to the package. At the time of program execution, the timestamp of load module and package is compared. This comparison takes place to ensure that the correct version of package and load module is ... Read More