SELECT Statement SQL SELECT Statement is a form of SQL QUERY which gives access to a result set(Temporary table). We can use SQL SELECT to look inside the table or create a new table based on the result set of queries. There are 3 components of SQL SELECT: SubSelect FullSelect Select-Statement SubSelect SubSelect…

Structured Query Language aka SQL is the industry standard to talk to Relation database management systems (RDBMS). RDBMS and SQL are like an alias to each other. To perform any action on RDBMS you need SQL. All the basic concepts of SQL are the same for every RDBMS like SQLServer…

When processing a CURSOR, DB2 SQL gives the option to update or delete data in the base table based on CURSOR's current position in the result table. This is called POSITIONED UPDATE or POSITIONED DELETE. Let say there is a table EXAMPLETABLE like CREATE TABLE EXAMPLETABLE (id numeric, name char(10)…

The result of a SELECT Statement which returns more than one row, is easy to view on an SQL interface like STRSQL. But How can we handle this result set pragmatically in Embedded SQL or SQL/PL code? The answer is SQL CURSOR. CURSOR provides a simple way to access Result…

GLOBAL VARIABLES GLOBAL VARIABLES are database objects to save a single value at a time(like DTAARA object) They can be accessed and modified using SQL statements. Their definition (only definition, not actual value) is stored in the database catalogs. The reason for this is that global variables have a session scope. This…

We can use a very simple correlated query to find the nth highest or lowest record in a table. Here is the query to find 2nd highest salary from the EMPLOYEE table Select * from EMPLOYEE E1 where 2 = (Select count(*) from EMPLOYEE E2 where E1.salary <= E2.salary) Number 2 in the “where 2 in” part of the query is the nth factor of the query. To find the 3rd highest salary replace this “2” with “3” and so on.

There are two ways to write SQL: Static SQL, which gets the job done in most cases, and the second one is Dynamic SQL for the rest of the complex stuff. Static SQL In a static SQL statement, most of the statement is hard-coded like table names and Column names…

Materialized Query Table(MQT) A materialized query table is a table whose structure and definition are based on the result of a given query. Materialized query table gets its data from the same query. And MQT actually contains that data. That query may contain more than 1 table. Syntax for MQT CREATE TABLE mqt-name…

There are multiple ways to Delete duplicate data from A Table using SQL Query. Here we are going to talk about using Correlated subqueries to Delete duplicate data. Let say we have an EMPLOYEE table as follows. In the above EMPLOYEE table, multiple records are duplicates. To remove all duplicate…