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
Articles on Trending Technologies
Technical articles with clear explanations and examples
What is the SQL query describing usage of MAX aggregate function and GROUP-BY with HAVING?
We can find the highest ORDER_TOTAL datewise from the ORDERS DB2 table using below query.ExampleSELECT ORDER_DATE, MAX(ORDER_TOTAL) FROM ORDERS GROUP BY ORDER_DATEWe will use ‘GROUP BY’ on ORDER_DATE to group the result date wise and MAX aggregate function will help us to get the maximum ORDER_TOTAL placed at that particular date.For example, if we have below ORDERS DB2 table.ORDER_IDORDER_TOTALORDER_DATEZ2234534229-07-2020Z6299854330-07-2020Z5699043128-07-2020Z56902674329-07-2020Z9978144328-07-2020Z5611288930-07-2020 Then the SQL query - SELECT ORDER_DATE, MAX(ORDER_TOTAL) FROM ORDERS GROUP BY ORDER_DATE will return the result below.ORDER_DATEORDER_TOTAL28-07-202044329-07-2020674330-07-2020889
Read MoreWrite the DB2 SQL query to find the third highest ORDER_TOTAL in a ORDERS DB2 table
We can find the third highest ORDER_TOTAL in the ORDERS DB2 table using the below query.ExampleSELECT ORDER_ID, MIN(ORDER_TOTAL) FROM ORDERS ORDER BY ORDER_TOTAL DESC FETCH FIRST 3 ROWS ONLYThe ‘FETCH FIRST 3 ROWS ONLY’ clause will give only 3 rows in the output and these 3 rows will be in descending order. The first row will have the highest ORDER_TOTAL in the entire ORDERS table, second row will have the second highest ORDER_TOTAL in the entire ORDERS table and so on.The MIN aggregate function will give the least value of the ORDER_TOTAL among those 3 rows and this ...
Read MoreSQL query describing usage of SUM aggregate function and GROUP-BY with HAVING
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 SUM, GROUP BY and HAVING.For example, if we have an ORDER table as below.ORDER_IDORDER_TOTALORDER_DATEZ2234534229-07-2020Z6299854330-07-2020Z5699043112-07-2020Z56902674329-07-2020Z9978144310-07-2020Z5611288930-07-2020 Below is the query which will give the desired result.ExampleSELECT ORDER_DATE, SUM(ORDER_TOTAL) FROM ORDERS GROUP BY ORDER_DATE HAVING ORDER_DATE IN (‘29-07-2020’, ‘30-07-2020’)In this query, we have selected ORDER_DATE and ORDER_TOTAL with aggregate function SUM.The GROUP BY will ...
Read MoreExample and usage of JOINS in DB2
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 ORDER_ID, TRANSACTION_ID, TRANSACTION_STATUS FROM ORDERS INNER JOIN TRANSACTIONS ON ORDERS.TRANSACTION_ID = TRANSACTIONS.TRANSACTION_IDThe above query will return the result below.ORDER_IDTRANSACTION_IDTRANSACTION_STATUSZ22345ITX4489PAIDZ62998ITX4311PAIDZ56902ITX3120UNPAID
Read MoreHow 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
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 > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)The result of the above query will be as below.ORDER_IDORDER_TOTALA2234587960A6299880363
Read MoreBehaviour of a COBOL-DB2 program when number of locks exceed the limit
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 level locks in any table exceeds the permissible limit, then the lock escalation takes place.In lock escalation, DB2 releases the page or row level lock which it has held and attempts to acquire a tablespace level or higher lock. In this case, the application now has wider access/scope to DB2 ...
Read MoreHow to find out the access path selected by optimizer for a particular query?
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 easily find the access path using EXPLAIN DB2 command. We have to SET the query number first and then give our SQL query to find out its access path in three simple steps.For example, We have a DB2 ORDERS table and we want to examine the SELECT query which has ...
Read MorePurpose and usage of ROW-ID and SEQUENCE in a DB2
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 column can serve as a primary key and hence it is very useful for the random access of the DB2 table. This logic can be implemented via ROW-ID and SEQUENCE.Any one column of the DB2 table can be defined as type ROW-ID following which DB2 will automatically assign a new ...
Read MorePurpose and usage of subqueries in DB with the help of an example
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 subquery up to 15 levels.For example, if we have below ORDERS table in DB2.ORDER_IDORDER_TOTALZ223451267Z629986734Z569028815Z5691178990Z56915432Z5691877453If we want to extract all the orders from the ORDERS table which have ORDER_TOTAL more than overall average, then we can use the below sub query.ExampleSELECT ORDER_ID FROM ORDERS WHERE ORDER_TOTAL > (SELECT AVG(ORDER_TOTAL) FROM ORDERS)The ...
Read MoreWhat are the limitations of using OUTER JOIN on a DB2 table?
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 tables. Also two or more conditions in WHERE clause can only be used with AND logical operator, other logical operators such as OR, NOT is not allowed.The functions to handle NULL operators such as VALUE and COALESCE could not be used with the OUTER JOINS.For example, if we have below 2 ...
Read More