In this post, we will understand the difference between a view and a materialized view.ViewsIt is a logical and virtual copy of a table that is created by executing a ‘select query’ statement.This result isn’t stored anywhere on the disk.Hence, every time, a query needs to be executed when certain data is needed.This way, the most recently updated data would be available from the tables.The tuple/result of the query doesn’t get stored.Instead, the query expression is stored on the disk.The query expression is stored, due to which the last updated data is obtained.They don’t have a storage/update cost associated with ... Read More

DELETE is a Data Manipulation Language (DML) command. It is used to remove tuples/records from a relation/table. On the other hand, DROP is a Data Definition Language (DDL) command and is used to remove named elements of schema like relations/table, constraints or entire schema. Read this article to learn more about DELETE and DROP commands in SQL and how they are different from each other. What is DELETE in SQL? In SQL, the DELETE command is used to remove some or all the records from a database table. With the DELETE command, the WHERE clause is used to define the ... Read More

The OPTIMIZE FOR N ROWS is a DB2 clause which we can add in the query to give priority for the retrieval of the first few rows only. This clause will enable the optimizer to choose the access path that minimizes the response time for fetching first few rows.The OPTIMIZE FOR N ROWS clause is not effective on SELECT DISTINCT and COUNT function because DB2 will need the entire qualifying rows in order to fetch the DISTINCT rows or COUNT the number of rows. The OPTIMIZE FOR N ROWS clause gives DB2 a better opportunity to establish the access path.The ... Read More

We can find the duplicate TRANSACTION_ID in the ORDERS DB2 table using the below query:ExampleSELECT TRANSACTION_ID, COUNT(*) AS TRANSACTION_COUNT FROM ORDER GROUP BY TRANSACTION_ID HAVING COUNT(*) > 1The purpose of COUNT(*) is to count the number of rows. We will group the result based on the TRANSACTION_ID using GROUP BY function and to display the duplicate transaction ids, we will place a predicate using HAVING statement for COUNT(*) greater than one.For example, consider the below TRANSACTIONS DB2 table:TRANSACTION_IDTRANSACTION_STATUSIRN22345PAIDIRN22345PAIDIRN22345PAIDIRN56902PAIDIRN99781UNPAIDIRN56902PAIDThe query will give the below result:TRANSACTION_IDTRANSACTION_COUNTIRN223453IRN569022IRN997811Read More

Problem: Write a SQL query to count the number of orders which were placed today from the ORDERS DB2 table. (The date should not be hardcoded)SolutionWe can find the count of orders which are placed today using the below DB2 query:ExampleSELECT COUNT(ORDER_ID) AS ORDER_COUNT FROM ORDERS WHERE ORDER_DATE = CURRENT DATEIn this query, we have used the COUNT COLUMN function which will count the total number of ORDER_ID (primary key). In the WHERE clause, we will use the predicate for the ORDER_DATE column. The CURRENT DATE is a DB2 inbuilt function which will return the current system date.For example, if ... Read More

Problem: Write a SQL query to display 2 columns. First column should have ORDER_ID, the second column should give the value as YES/NO for free shipping based on ORDER_TOTAL > 500.SolutionThe query to display ORDER_ID and free shipping result based on the ORDER_TOTAL criteria can be written as below.ExampleSELECT ORDER_ID,    CASE WHEN ORDER_TOTAL > 500 THEN ‘YES’       ELSE ‘NO’ AS FREE_SHIPPING    END FROM ORDERSWe will use CASE expressions through which we can implement a logic to check the ORDER_TOTAL. If the ORDER_TOTAL is greater than 500 then we will get ‘YES’ for the free shipping ... Read More

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-2020889Read More

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 More

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 More

A static SQL is hardcoded in COBOL-DB2 program and the SQL query cannot change during the program execution. We can only change the value of the host variables. In the case of DYNAMIC SQL, we can change the columns, tables and predicates in the COBOL-DB2 program in run time.For example, based on the current date, we can update ORDERS or ORDERS_HIST table. This query can be built using DYNAMIC SQL which includes ORDERS table or ORDER_HIST table.The main advantage of DYNAMIC SQL is its flexibility. We can add columns or change tables/predicates as per our business logic. On the other ... Read More