ORACLE QUERY TO REPEAT A NUMBER N TIMES
I need to write one query which will accept input parameter from user and display that number of
times the user input. For example if I give input parameter as 5 then it should display 55555, if I
give 4 it should give output as 4444.
This question was asked by one of my blog reader. Here is the oracle sql query.
Solution
SELECT sys_connect_by_path('','5')
FROM dual
WHERE level = 5
CONNECT BY level <= 5
Here the connect by level clause is used to repeat the rows n number of times. In our case it
repeats the row 5 times. As dual table contains only one record, the connect by clause repeats
that single row 5 times.
SYS_CONNECT_BY_PATH is valid only in hierarchical queries. It returns the path of a column
value from root to node, with column values separated by char (In this example empty string) for
each row returned by CONNECT BY condition. In our case, it concatenates all the 5's in each
row.
The condition level=5 is used to get only the last row.
CURSOR FOR LOOP EXAMPLE IN ORACLE PLSQL
Cursors provide easy way to fetch records from database table(s) and store them in memory for
future use.
The following steps are followed when fetching rows from the cursor:
Open the cursor
Start loop
Fetch record from the cursor
Check whether the cursor returned a row or not
Process the record
Close the loop
Close the cursor
Cursor for loop simplifies the above steps by implicitly doing most of the above steps. Cursor for
loop opens the cursor, iterates over the cursor and fetches the records from the cursor. Finally it
closes the cursor.
CURSOR FOR LOOP EXAMPLE:
The following anonymous oracle plsql block is used to find the total salary of all the employees:
DECLARE
CURSOR emp_cur IS
SELECT sal FROM employees;
total_salary numeric;
BEGIN
total_salary := 0;
FOR emp_record IN emp_cur LOOP
total_salary :=
total_salary + emp_rec.sal;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Salary of employees= '|| total_salary);
END;
/
How Cursor For Loop Works:
Cursor for loop implicitly declares a loop index as a record which represents the row to be
fetched from the cursor (database). Next it opens the cursor, fetches the records from the cursor
repeatedly and then closes the cursor.
Recommended Reading:
MAX OF PRODUCT N CONSECUTIVE DIGITS - ORACLE SQL QUERY
Q) I have a table which has only one column and one row. The numeric column contains a 6 digit
number. My requirements are
Find out the product of the 3 consecutive digits and display them in separate rows.
Find out the Max prodcut of the 3 consecutive digits and print it.
I need separate SQL queries for each of them. Sample input data output are shown below:
Source Table Name: Digits
Value
-----123456
Output1:
product
------6
24
60
120
Output2:
Max_product
----------120
Solution:
1. Use the connect by clause and level features of oracle to print the product N consecutive digits
in separate rows. The sql query is shown below:
select
(TO_NUMBER(SUBSTR (value,l, 1)))*
(TO_NUMBER(SUBSTR (value,l+1, 1)))*
(TO_NUMBER(SUBSTR (value,l+2, 1)))
from digits ,
(select level l from dual connect by level <= 6-2); -- Total digits-(N+1)
2. From the above query, we got the Product of N consecutive numbers in multiple rows. Now its
easy to find the maximum value by simply using the max function. The sql query is shown
below:
select
MAX((TO_NUMBER(SUBSTR (value,l, 1)))*
(TO_NUMBER(SUBSTR (value,l+1, 1)))*
(TO_NUMBER(SUBSTR (value,l+2, 1))))
from digits ,
(select level l from dual connect by level <= 6-2);
ORACLE TRIGGER AFTER INSERT EXAMPLE & CREATE
The after insert trigger in oracle is a DML trigger which is fired by an insert statement on a table.
The oracle after insert trigger is used to update the values on the table and to log the inserts in
another table. Both the insert and the trigger executes in a single transaction. First the insert
statement executes and then the trigger executes. As the insert statement executes first, you
cannot generate primary key values using the after insert trigger. User the before insert trigger to
generate the primary key values.
Oracle Trigger After Insert Example:
1. Updating values in the table
you can create after insert trigger to update the values in the table on which the insert statement
is fired. See the following example which updates the hire date of the employee
CREATE OR REPLACE TRIGGER update_employees
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
UPDATE employees
SET
hire_date = sysdate
WHERE
employee_id = :new.employee_id;
END update_employees;
/
By default an employees joining date is on the day which you insert a record into the employees
table. So, you dont need to explicitly specify the hire_date value in the insert statement. Just use
the above trigger statement which update the hire date when you insert a record into the
employees table.
2. Logging the rows in another table.
You can use the after insert tigger to track the newly records created in a table in another log
table. See the following example:
CREATE OR REPLACE TRIGGER log_rows_products
AFTER INSERT ON products
FOR EACH ROW
BEGIN
INSERT INTO products_changes
(
product_id,
product_name
)
VALUES
(
:new.product_id,
:new.product_name
);
END log_rows_products;
/
Whenever a new insert statement is fired on the products table, the above trigger gets fired and
inserts the records into another table (products_changes).
SQL QUERY TO GROUP / AGGREGATE N CONSECUTIVE ROWS
Q) In one of my project, I got a requirement to group N consecutive rows and find the min, max
and sum of values in each group. In this article I will show you how to do aggregation on N
successive rows.
Let's take the sales table as an example. The data in the source (sales) table is shown below:
Table Name: Sales
Sales_Key Price
--------------1
10
20
30
-----------4
40
50
60
-----------7
80
90
Here, I have to group 3 (N) consecutive records and then find the sum of the price within each
group. In the above data, the rows to be grouped are separated with dotted lines. The output is
shown below:
Group_Price
-----------
60
--Sum of first 3 rows
150
--Sum of fourth, fifth and sixth rows
170
--Sum of seventh and eight row.
At first I thought, this cannot be implemented using SQL query and wrote a oracle plsql
procedure. Later after trying for some time I got the solution using the sql query. Here, I am
providing the sql query, so that if you get such a requirement it will be easy for you to implement.
SQL Query to Aggregate Rows:
The following sql query aggregates 3 (N) consecutive rows and computes the sum of the price:
SELECT
SUM(s.price)
FROM
sales s
LEFT OUTER JOIN
(
SELECT s1.sales_key min_range, s2.sales_key max_range
FROM sales s1,
sales s2
WHERE s2.sales_key-s1.sales_key = 3-1 -- N-1
AND MOD(s2.sales_key,3) IN (0,1)
) r
ON (s.sales_key BETWEEN r.min_range AND r.max_range
Group
By
NVL(r.min_range,-1)
In the above query replace 3 with the required number
MIN AND MAX VALUES OF CONTIGUOUS ROWS - ORACLE SQL QUERY
Q) How to find the Minimum and maximum values of continuous sequence numbers in a group of
rows.
I know the problem is not clear without giving an example. Let say I have the Employees table
with the below data.
Table Name: Employees
Dept_Id Emp_Seq
--------------10
10
10
10
10
10
10
10
11
20
20
I want to find the minimum and maximum values of continuous Emp_Seq numbers. The output
should look as.
Dept_Id Min_Seq Max_Seq
----------------------10
10
10
10
11
11
20
Write an SQL query in oracle to find the minimum and maximum values of continuous Emp_Seq
in each department?
STEP1: First we will generate unique sequence numbers in each department using the
Row_Number analytic function in the Oracle. The SQL query is.
SELECT
Dept_Id,
Emp_Seq,
ROW_NUMBER() OVER (PARTITION BY Dept_Id ORDER BY Emp_Seq) rn
FROM
employees;
Dept_Id Emp_Seq
rn
-------------------10
10
10
10
10
10
10
10
11
20
20
STEP2: Subtract the value of rn from emp_seq to identify the continuous sequences as a group.
The SQL query is
SELECT
Dept_Id,
Emp_Seq,
Emp_Seq-ROW_NUMBER() OVER (PARTITION BY Dept_Id ORDER BY Emp_Seq)
Dept_Split
FROM
employees;
Dept_Id Emp_Seq
Dept_Split
--------------------------10
10
10
10
10
10
10
10
11
20
20
STEP3: The combination of the Dept_Id and Dept_Split fields will become the group for
continuous rows. Now use group by on these fields and find the min and max values. The final
SQL query is
SELECT
Dept_Id,
MIN(Emp_Seq) Min_Seq,
MAX(Emp_Seq) Max_Seq
FROM
(
SELECT
Dept_Id,
Emp_Seq,
Emp_Seq-ROW_NUMBER() OVER (PARTITION BY Dept_Id ORDER BY Emp_Seq)
Dept_Split
FROM
employees;
) A
Group BY Dept_Id, Dept_Split
SQL QUERY TO GROUP / AGGREGATE N CONSECUTIVE ROWS
Q) In one of my project, I got a requirement to group N consecutive rows and find the min, max
and sum of values in each group. In this article I will show you how to do aggregation on N
successive rows.
Let's take the sales table as an example. The data in the source (sales) table is shown below:
Table Name: Sales
Sales_Key Price
--------------1
10
20
30
-----------4
40
50
60
-----------7
80
90
Here, I have to group 3 (N) consecutive records and then find the sum of the price within each
group. In the above data, the rows to be grouped are separated with dotted lines. The output is
shown below:
Group_Price
----------60
--Sum of first 3 rows
150
--Sum of fourth, fifth and sixth rows
170
--Sum of seventh and eight row.
At first I thought, this cannot be implemented using SQL query and wrote a oracle plsql
procedure. Later after trying for some time I got the solution using the sql query. Here, I am
providing the sql query, so that if you get such a requirement it will be easy for you to implement.
SQL Query to Aggregate Rows:
The following sql query aggregates 3 (N) consecutive rows and computes the sum of the price:
SELECT
SUM(s.price)
FROM
sales s
LEFT OUTER JOIN
(
SELECT s1.sales_key min_range, s2.sales_key max_range
FROM sales s1,
sales s2
WHERE s2.sales_key-s1.sales_key = 3-1 -- N-1
AND MOD(s2.sales_key,3) IN (0,1)
) r
ON (s.sales_key BETWEEN r.min_range AND r.max_range
Group
By
NVL(r.min_range,-1)
In the above query replace 3 with the required number
ORACLE QUERY TO SPLIT THE DELIMITED DATA IN A COLUMN TO
MULTIPLE ROWS
1. Consider the following table "t" data as the source
id
value
---------1
A,B,C
P,Q,R,S,T
M,N
Here the data in value column is a delimited by comma. Now write a query to split the delimited
data in the value column into multiple rows. The output should look like as
id value
-------1
Solution:
SELECT
t.id,
CASE
WHEN
a.l = 1
THEN
substr(value, 1, instr(value,',',1,a.l)-1)
ELSE
substr(value, instr(value,',',1,a.l-1)+1,
CASE WHEN instr(value,',',1,a.l)-
instr(value,',',1,a.l-1)-1 > 0
instr(value,',',1,a.l-1)-1
THEN instr(value,',',1,a.l)-
ELSE length(value)
END
)
END
FROM
final_value
t,
( SELECT level l
FROM DUAL
CONNECT BY LEVEL <=
(
SELECT Max(length(value) - length(replace(value,',',''))+1)
FROM t
)
) a
WHERE
length(value) - length(replace(value,',',''))+1 >= a.l
order by t.id, a.l;
Recommended Reading:
Oracle SQL Queries Interview Questions
SQL Interview Questions and Answers
Oracle Complex Queries
How to find (calculate) median using oracle sql query
Min and Max values of contiguous rows - Oracle SQL Query
If you like this article, then please share it or click on the google +1 button.
HOW TO FIND (CALCULATE) MEDIAN USING ORACLE SQL QUERY
A median is a value separating the higher half of sample from the lower half. The median can be
found by arranging all the numerical values from lowest to highest value and picking the middle
one. If there are even number of numerical values, then there is no single middle value; then the
median is defined as the mean of the two middle values.
Now let see how to calculate the median in oracle with the employees table as example.
Table name: employees
empid, deptid, salary
1,
100,
5000
2,
100,
3000
3,
100,
4000
5,
200,
6000
6,
200,
8000
The below query is used to calculate the median of employee salaries across the entire table.
select
empid,
dept_id,
salary,
percentile_disc(0.5) within group (order by salary desc)
over () median
from
employees;
The output of the above query is
empid, deptid, salary, median
----------------------------1,
100,
5000,
5000
2,
100,
3000,
5000
3,
100,
4000,
5000
5,
200,
6000,
5000
6,
200,
8000,
5000
Now we will write a query to find the median of employee salaries in each department.
select
empid,
dept_id,
salary,
percentile_disc(0.5) within group (order by salary desc)
over (partition by department_id) median
from
employees;
The output of the above query is
empid, deptid, salary, median
-----------------------------1,
100,
5000,
4000
2,
100,
3000,
4000
3,
100,
4000,
4000
5,
200,
6000,
7000
6,
200,
8000,
7000
Recommended Reading:
