Mandalika has Published 480 Answers

SQL query describing usage of SUM aggregate function and GROUP-BY with HAVING

Mandalika
Published on 30-Nov-2020 09:34:40
Problem: Write the DB2 SQL query to give the sum of ORDER_TOTAL for the orders placed on 29th July and 30th July individually. The result should come in a single table.SolutionWe can find the sum of ORDER_TOTAL for the orders placed on 29th and 30th July individually using aggregate function ... Read More

Example and usage of JOINS in DB2

Mandalika
Published on 30-Nov-2020 09:33:33
Problem: How will you find the ORDER_ID, TRANSACTION_ID and TRANSACTION_STATUS from ORDERS and TRANSACTIONS DB2 table using joins?SolutionWe can find ORDER_ID, TRANSACTION_ID and TRANSACTION_STATUS from ORDERS and TRANSACTIONS table using the INNER JOIN query.For example, if we have below 2 ORDERS table.ORDER_IDTRANSACTION_IDORDER_DATEZ22345ITX448923-10-2020Z62998ITX431121-10-2020Z56902ITX312026-10-2020 TRANSACTION_IDTRANS_AMTTRANSACTION_STATUSITX44891128PAIDITX43112318PAIDITX312088956UNPAIDITX21675612FAILEDWe can use an inner join query as below.ExampleSELECT ... Read More

How will you find the ORDER_ID of all the orders having ORDER_TOTAL greater than the average of ORDER_TOTAL in ORDER's DB2 table

Mandalika
Published on 30-Nov-2020 09:32:16
We can find all the ORDER_ID which are having ORDER_TOTAL greater than the average value of all the ORDER_TOTAL present in the ORDERS table using the sub-query.For example, if we have below ORDERS table.ORDER_IDORDER_TOTALA223451867A629985634A569027615A5691187960A56915132A5691880363Below is the subquery to find out the desired data.ExampleSELECT ORDER_ID, ORDER_TOTAL FROM ORDERS    WHERE ORDER_TOTAL ... Read More

Behaviour of a COBOL-DB2 program when number of locks exceed the limit

Mandalika
Published on 30-Nov-2020 09:31:14
Problem: How will the COBOL-DB2 program behave once the number of locks placed on the table space exceeds the defined limit?SolutionThe number of locks which an application can place on a DB2 resource such as page, table row, etc., is defined in DSNZPARM. Once the number of page and row ... Read More

How to find out the access path selected by optimizer for a particular query?

Mandalika
Published on 30-Nov-2020 09:30:16
DB2 optimizer plays an important role in the overall performance of the database. The optimizer selects the optimal access path for each query through which data can be fetched from the database. It identifies the indexes to follow, query predicates, etc.The optimizer selects the access path automatically and we can ... Read More

Purpose and usage of ROW-ID and SEQUENCE in a DB2

Mandalika
Published on 30-Nov-2020 09:28:50
Problem: How can you implement a logic to automatically generate a unique value in a DB2 column for every new row inserted?SolutionWe can implement a logic in a DB2 table through which we can have one column which will have an automatically generated value for every new row inserted. This ... Read More

Purpose and usage of subqueries in DB with the help of an example

Mandalika
Published on 30-Nov-2020 09:27:04
Problem: What are DB2 subqueries and what is the use of subqueries? Explain subqueries with the help of an example on the ORDERS table.SolutionA subquery in DB2 is a query within a query, i.e., nested query. A subquery is executed first followed by its parent query. We can have a ... Read More

What are the limitations of using OUTER JOIN on a DB2 table?

Mandalika
Published on 30-Nov-2020 09:26:04
The result of OUTER JOIN includes matched and unmatched rows in the WHERE clause. There are two main limitations of OUTER JOINS in DB2.The WHERE clause of OUTER JOIN can only have ‘=’ relational operator. <, >, etc are not allowed in case of OUTER JOIN of two or more ... Read More

How to provide & remove user access to/from DB2 object? Give the DB2 commands?

Mandalika
Published on 30-Nov-2020 09:24:51
DB2 has a concept of DCL, through which we can control the access to DB2 objects like table, plan, etc. DCL stands for Data Control Language and using this we can provide and remove user access to the DB2 objects.GRANT command will give the user access to the mentioned object ... Read More

How will you keep the locks on the resources even after a ROLLBACK?

Mandalika
Published on 30-Nov-2020 09:23:24
DB2 places locks on the resources like table, tablespace, etc., when any application is using that resource. In a COBOL-DB2 program, if we are modifying or deleting data from a table then DB2 places locks. When we give the ROLLBACK statement to revert the changes made in the database, all ... Read More
Previous 1 ... 4 5 6 7 8 9 10 ... 48 Next
Advertisements