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
SQL Articles
Page 10 of 12
Example of SQL query describing the conditional processing
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 MoreWhat 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 MoreExplain the concept of DYNAMIC SQL in DB2 with the help of an example
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 MoreJavaScript - Find keys for the matched values as like query in SQL
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 MoreExecution time while running a SQL query in HANA Studio
When a SQL query is executed, you can see the confirmation that the query is executed in time duration and also with server processing time. In this scenario, you can see the time taken by SAP HANA processor to create a new table in the HANA database as below −“Statement 'Create Table Demo_HANA ( ID INTEGER, NAME VARCHAR(10), PRIMARY KEY (ID) )' successfully executed in 3 ms 117 µs (server processing time − 2 ms 458 µs) - Rows Affected − 0”
Read MoreCreate View SQL in SAP HANA database
>Create View View_name as Select Col1,Col2 From Table_name;In the above SQL statement, you are creating a view that contains Col1 and Col2 from the table.Table Name − Emp_InfoExampleIdEmplNameEmpluserEmplpasswordJoining_Date1Employee 1Emp1Emp1Pwd9/11/20162 Employee 2Emp2 Emp2Pwd 16/08/20153Employee 3Emp3Emp3Pwd15/09/20164Employee 4Emp4 Emp4Pwd 3/07/20145Employee 5Emp5Emp5Pwd10/09/20126Employee 6Emp6 Emp6Pwd 1/10/2013 To create a view, which contains only 3 columns you have to write->Create View View_EmpInfo As Select Id, EmplName,Joining_Date From Emp_Info;This view can be used by users to get Id, EmplName, and Joining_date.
Read MoreFunctions in Oracle DBMS
The different types of functions in Oracle are −Oracle String FunctionsOracle Numeric FunctionsOracle Date FunctionsMore details about these functions are given as follows −Oracle String FunctionsThe following are the Oracle String Functions −ASCII(str)This function returns the ASCII or numeric value of the first word in the string str provided. If it is an empty string, it returns 0. For example:SQL> SELECT ASCII('Apple'); +---------------------------------------------------------+ | ASCII('Apple') | +---------------------------------------------------------+ | 65 | +---------------------------------------------------------+ 1 row in set (0.00 sec)This returns the ASCII value of A i.e. 65 as it is the first character in the string.CONCAT(str1, str2…..strn)This function returns the string that ...
Read MoreCursors in Oracle DBMS
When a SQL statement is executed in Oracle, the temporary context area is created. This area contains all the relevant information relating to the statement and its execution. The cursor is a pointer to this context area and allows the PL/SQL program to control this area.There are two types of Cursors.Implicit CursorsExplicit CursorsLet us begin with Implicit Cursors −Implicit CursorsWhenever an SQL statement is executed, the implicit cursors are automatically created. This happens if there is no explicit cursor for the particular statement. Implicit cursors cannot be controlled by the programmers.There are many different attributes for Implicit Cursors. Some of ...
Read More