Found 118 Articles for SQL

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

Suppose, we have an object like this −const obj = {"100":"Jaipur", "101":"Delhi", "102":"Raipur", "104":"Goa"};We are required to write a JavaScript function that takes in one such object as the first argument and a search query term as the second argument. Then our function should return all those key/value pairs whose value includes the search term provided to the function as the second argument.We will simply iterate through the object, building the resulting object (if it matches the condition) as we move through and lastly return that object.ExampleThe code for this will be −const obj = {    "100":"Jaipur",    "101":"Delhi", ... Read More

Stored procedures are a pieces of the code in written in PL/SQL to do some specific task. Stored procedures can be invoked explicitly by the user. It's like a java program , it can take some input as a parameter then can do some processing and can return values.On the other hand,  trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete). Triggers are more like an event handler they run at the specific event. Trigger can not take input and they can’t return values.Sr. No.KeyTriggersStored procedures1Basic trigger is a stored procedure that runs automatically when ... Read More

SQL query is used to fetch data from the database. In some of the scenario you may need some perquisite data to call subsequent SQL query to fetch data from a table so instead of writing two seperate query we can write SQL query within the query.Therefore subQuery is a way to combine or join them in single query. Subqurey can have two types −Correlated subquery - In correlated subquery, inner query is dependent on the outer query. Outer query needs to be executed before inner queryNon-Correlated subquery - In non-correlated query inner query does not dependent on the outer query. ... Read More

In Hierarchical data model, relationship between table and data is defined in parent child structure. In this structure data are arranged in the form of a tree structure. This model supports one-to-one and one-to-many relationships.On the other hand, network model arrange data in graph structure. In this model each parents can have multiple children and children can also have multiple parents. This model supports many to many relationships also.Sr. No.KeyHierarchical Data ModelNetwork Data Model1Basic Relationship between records is of the parent child typeRelationship between records is expressed in the form of pointers or links.2        Data Inconsistency It can have data ... Read More

In Relational database tables are associated with each other and we used foreign key to maintain relationships between tables. We used join clause to retrieve data from associated tables. The join condition indicates how column in each table are matched against each other. There are two types of joins clause in SQL Inner join Outer joinOuter join is again divided into parts −LEFT OUTER JOIN - It will return all data of the left table and matched  records in both table RIGHT OUTER JOIN - it will return all the data of the right table and matched records in both tableSr. No.KeyInner joinOuter join1Basic It ... Read More