SQL

Pattern Printing in Oracle SQL

1 ————

*
**
***
****
*****

DECLARE
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := P_STAR||’*’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

OR

DECLARE
STAR VARCHAR2(5):=’*’;
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := LPAD(STAR,I,STAR);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

2 ————————————–

&
&*
&*&
&*&*
&*&*&

DECLARE
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
IF MOD(I,2)=0
THEN
P_STAR := P_STAR||’*’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
ELSE
P_STAR:=   P_STAR||’&’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
END IF;
END LOOP;
END;

3 —————————————

****
****
***
**
*

DECLARE
P_STAR VARCHAR2(5):= ‘*****’;
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
S_STAR := SUBSTR(P_STAR,I);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

4 ———————————

*
**
***
****
*****
****
***
**
*

DECLARE
V_STAR VARCHAR2(1):= ‘*’;
P_STAR VARCHAR2(5);
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(V_STAR,I,V_STAR);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR I IN 1..5
LOOP
S_STAR := SUBSTR(P_STAR,I+1);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

5 —————————————————-

*****
****
***
**
*
*
**
***
****
*****

DECLARE
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR2(5);
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := SUBSTR(V_STAR,I);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR I IN 1..5
LOOP
S_STAR := LPAD(P_STAR,I,P_STAR);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

6 ——————————————

*****
****
***
**
*
**
***
****
*****

DECLARE
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR2(5);
S_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR := SUBSTR(V_STAR,I);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR I IN 1..4
LOOP
P_STAR := P_STAR||’*’;
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

7 ——————————

*****
****
***
**
*

DECLARE
SPACE VARCHAR2(1):= ‘ ‘;
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(SUBSTR(V_STAR,I),5,SPACE);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
END;

8 ——————————
*
**
***
****
*****

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_NO VARCHAR2(5);
P_STAR VARCHAR2(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= P_STAR||STAR;
FOR J IN 1..5
LOOP
P_NO:= LPAD(P_STAR,5,SPACE);
END LOOP;
DBMS_OUTPUT.PUT_LINE(P_NO);
END LOOP;
END;

9 ——————————
*****
****
***
**
*
*
**
***
****
*****

DECLARE
SPACE VARCHAR2(1):= ‘ ‘;
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR(5);
S_STAR VARCHAR(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(SUBSTR(V_STAR,I),5,SPACE);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR J IN 1..5
LOOP
S_STAR:= RPAD(SUBSTR(P_STAR,J),5,’*’);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

10 ——————————

*****
****
***
**
*
**
***
****
*****

DECLARE
SPACE VARCHAR2(1):= ‘ ‘;
V_STAR VARCHAR2(5):= ‘*****’;
P_STAR VARCHAR(5);
S_STAR VARCHAR(5);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= LPAD(SUBSTR(V_STAR,I),5,SPACE);
DBMS_OUTPUT.PUT_LINE(P_STAR);
END LOOP;
FOR J IN 1..5
LOOP
S_STAR:= RPAD(SUBSTR(P_STAR,J+1),5,’*’);
DBMS_OUTPUT.PUT_LINE(S_STAR);
END LOOP;
END;

11 ——————————

**** ****
***   ***
**     **
*       *

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_PRINT VARCHAR2(9);
S_SPACE VARCHAR2(9);
P_SPACE VARCHAR2(9);
BEGIN
FOR I IN 1..4
LOOP
P_SPACE:= S_SPACE;
S_SPACE:= S_SPACE||SPACE;
P_PRINT:= RPAD(LPAD(S_SPACE,5,STAR)||P_SPACE,9,STAR);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
END;

12 ——————————

*        *
**      **
***    ***
****  ****
**********

13 ——————————

****  ****
***    ***
**      **
*        *
**      **
***    ***
****  ****

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_PRINT VARCHAR2(9);
S_SPACE VARCHAR2(9);
P_SPACE VARCHAR2(9);
BEGIN
FOR I IN 1..4
LOOP
P_SPACE:= S_SPACE;
S_SPACE:= S_SPACE||SPACE;
P_PRINT:= RPAD(LPAD(S_SPACE,5,STAR)||P_SPACE,9,STAR);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
FOR I IN 1..3
LOOP
S_SPACE:= P_SPACE;
P_SPACE:= SUBSTR(P_SPACE,1,4-(I+1));
P_PRINT:= RPAD(LPAD(S_SPACE,5,STAR)||P_SPACE,9,STAR);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
END;

14 ———————————-

*
***
*****
*******
*********

DECLARE
STAR VARCHAR2(1):= ‘*’;
SPACE VARCHAR2(1):= ‘ ‘;
P_PRINT VARCHAR2(9);
S_STAR VARCHAR2(9);
P_STAR VARCHAR2(9);
BEGIN
FOR I IN 1..5
LOOP
P_STAR:= S_STAR;
S_STAR:= S_STAR||STAR;
P_PRINT:= RPAD(LPAD(S_STAR,5,SPACE)||P_STAR,9,SPACE);
DBMS_OUTPUT.PUT_LINE(P_PRINT);
END LOOP;
END;

 

 

How to print calendar from SQL Query

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,”Sun”, “Mon”, “Tue”,
“Wed”, “Thu”, “Fri”, “Sat”
FROM (SELECT TO_CHAR(dt,’fmMonthfm YYYY’) MONTH,TO_CHAR(dt+1,’iw’) week,
MAX(DECODE(TO_CHAR(dt,’d’),’1′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Sun”,
MAX(DECODE(TO_CHAR(dt,’d’),’2′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Mon”,
MAX(DECODE(TO_CHAR(dt,’d’),’3′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Tue”,
MAX(DECODE(TO_CHAR(dt,’d’),’4′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Wed”,
MAX(DECODE(TO_CHAR(dt,’d’),’5′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Thu”,
MAX(DECODE(TO_CHAR(dt,’d’),’6′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Fri”,
MAX(DECODE(TO_CHAR(dt,’d’),’7′,LPAD(TO_CHAR(dt,’fmdd’),2))) “Sat”
FROM ( SELECT TRUNC(SYSDATE,’y’)-1+ROWNUM dt
FROM all_objects
WHERE ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) – TRUNC(SYSDATE,'y'))
GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' ))
ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week);

Multiple Insert Statement in Oracle SQL

Multiple Insert Statement:-
In multiple INSERT statement, you insert data into more then one tables as a part of single DML statement.

Type of Multiple Insert Statement:

1.  Unconditional INSERT
2.  Conditional INSERT
3.  Conditional FIRST INSERT
4.  Pivoting INSERT

Syntax:-

INSERT [ALL] [CONDITION_INSERT_CLAUSE]
[insert_into_clause values_clause ] (subquery);

=> Conditional_insert_clause

[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_clause]
[else] [insert_into_clause values_clause];

Unconditional INSERT ALL:-
This INSERT statement is referred to as as unconditional INSERT because no further restriction is applied to the rows that are retrieved by SELECT statement.

Example:-

INSERT ALL
INTO XXC05_EMP1(EMP_ID, L_NAME, SAL)
INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
SELECT EMPLOYEE_ID EMP_ID,
LAST_NAME L_NAME,
FIRST_NAME F_NAME,
SALARY SAL,
MANAGER_ID MGR_ID
FROM EMPLOYEES;

Note:- Insert data into XXC05_EMP1, XXC05_EMP2 tables by a single SELECT statement.

Conditional INSERT ALL:-
This INSERT statement is referred to as as conditional INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement.

Example:-

INSERT ALL
WHEN EMPLOYEE_ID 200
INTO XXC05_EMP2(EMP_ID, F_NAME, MGR_ID)
SELECT EMPLOYEE_ID EMP_ID,
LAST_NAME L_NAME,
FIRST_NAME F_NAME,
SALARY SAL,
MANAGER_ID MGR_ID
FROM EMPLOYEES;

Conditional FIRST INSERT:-
This INSERT statement is referred to as as conditional FIRST INSERT because a further restriction is applied to the rows that are retrieved by SELECT statement. In that statement first true condition find and insert data in those table, rest of conditions or statement are switched.

Example:-

INSERT FIRST
WHEN EMPLOYEE_ID <= 24000
THEN
INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID = 2388
THEN
INTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID =0
THEN
INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMPLOYEES;

Note:- In above example First condition is true so data insert into only EMPLOYEE1 table.It do not check any other conditions.

Example:-

INSERT FIRST
WHEN EMPLOYEE_ID <= -24000
THEN
INTO employee1(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID = -2388
THEN
INTO employee2(EMPLOYEE_ID, LAST_NAME, SALARY)
WHEN EMPLOYEE_ID =0
THEN
INTO employee4(EMPLOYEE_ID, LAST_NAME, SALARY)
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
FROM EMPLOYEES;

Note:- In above example THIRD condition is true so data insert into only EMPLOYEE3 table. It do not check FORTH condition.

Pivoting INSERT:-
Using pivoting INSERT, convert the set of sales records from nonrelational database table to relational format.

Index and its Type in Oracle with Syntax

Index:-

CREATE INDEX XXC05_INDEX
ON
XXC05_TEST(ID);

Composite Index:- it is also called concatenated Index, is an index on multiple columns in a table.

CREATE INDEX XXC05_INDEX
ON
XXC05_TEST(ID,NAME);

Unique Index:-  Unique indexes guarantee that no two rows of a table have duplicate values in the key column or column.

NonUnique Index:-  NonUnique indexes permit duplicate value in a column of table.

Types of Index:

B-Tree Index:-

These indexes are the standard index type. They are excellent for primary key and highly-selective indexes

By default B-tree Index created when we create index.

Syntax:-    create index XXC05_INDEX on XXC05_TEST(id,name)

Bitmap Index:- The indexed columns have low cardinality, that is, the number of distinct values is small compared to the number of table rows.

Syntax:-    create bitmap index XXC05_INDEX on XXC05_TEST(id,name)

Unique Index:-

create unique index XXC05_INDEX on XXC05_TEST(id);

Function Based Index:-

create unique index XXC05_INDEX on XXC05_TEST (UPPER(id));

** Maxinum no of column use in Index:-    32

Rename Index:-

Alter index XXC05_INDEX
rename to XXC05_INDEX1;

Drop Index:- DROP INDEX INDEX_NAME;

Drop index XXC05_INDEX;

Index in Oracle

Index:-
Index is a schema object. Index is a performance-tuning method. Using Index retrieval of data from database is fast.

Syntax:-
CREATE INDEX index_name
ON table_name(column_name1,column_name2,column_name3,…);

Rename an INDEX:-
Alter INDEX old_index_name
RENAME TO new_index_name;

Drop an INDEX:-
DROP INDEX index_name;

Type of Index:-

1. Normal Index(B-Tree Index)
2. Bitmap Index
3. Function-based Index

Normal Index:-
It is also called B-TREE Index, By Default B-TREE index is created. It manage data in tree form.

Bitmap Index:-
Bitmap index put on that column which contain low cardinality. Low Cardinality means more duplicate data and less unique data.

Function-Based Index:-
The index expression can be an arithmetic expression or an expression that contains a SQL function, PL/SQL function, package function.

Example of Normal Index:-

CREATE INDEX xxc05_vendor_id
ON xxc05_ap_supplier(vendor_id);

Example of Bitmap Index:-

CREATE BITMAP INDEX xxc05_emp_gender
ON xxc05_employees(gender);

Example of Functional Index:-

CREATE INDEX xxc05_vendor_id
ON xxc05_ap_supplier(UPPER(vendor_name));

Analytical SQL functions

 

Oracle has introduced some exciting extensions to ANSI SQL to allow us to quickly compute aggregations and rollup. These new statements include:

  1. rollup
  2. cube

These simple SQL operators allow us to create easy aggregations directly inside the SQL without having to employ SQL*Plus break and compute statements. Let’s start by examining the ROLLUP syntax.

 

Tabular aggregates with ROLLUP

 

 

ROLLUP enables an SQL statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use.

 

SELECT deptno, job,count(*), sum(sal) FROM emp GROUP BY ROLLUP(deptno,job);

 

 

DEPTNO JOB COUNT(*) SUM(SAL)

——— ——— ——— ———

10 CLERK 1 1300

10 MANAGER 1 2450

10 PRESIDENT 1 5000

10 3 8750

20 ANALYST 2 6000

20 CLERK 2 1900

20 MANAGER 1 2975

20 5 10875

 

 

cross-tabular reports with CUBE

 

 

In multidimensional jargon, a “cube” is a cross-tabulated summary of detail rows. CUBE enables a SELECT statement to calculate subtotals for all possible combinations of a group of dimensions. It also calculates a grand total. This is the set of information typically needed for all cross-tabular reports, so CUBE can calculate a cross-tabular report with a single select statement.

 

Note in the example below that totals are calculated for each department, and also for each job category.

 

SELECT deptno,job,count(*),sum(sal) FROM emp GROUP BY CUBE(deptno,job);

 

DEPTNO JOB COUNT(*) SUM(SAL)

——— ——— ——— ———

10 CLERK 1 1300

10 MANAGER 1 2450

10 PRESIDENT 1 5000

10 3 8750

20 ANALYST 2 6000

20 CLERK 2 1900

20 MANAGER 1 2975

20 5 10875

30 CLERK 1 950

30 MANAGER 1 2850

30 SALESMAN 4 5600

30 6 9400

ANALYST 2 6000

CLERK 4 4150

MANAGER 3 8275

PRESIDENT 1 5000

SALESMAN 4 5600

14 29025

 

 

ALTER TABLE Option in Oracle.

ALTER TABLE

Specifications of ‘ALTER TABLE’ are as follows

 

Adding Foreign key

ALTER TABLE table_name

ADD CONSTRAINT constraint_name FOREIGN KEY (col1, col2)

REFERENCES table_2 (cola,colb);

 

Adding unique constraint

The following example adds a unique constraint on the column column_name in the table whose name is table_name. The name of the constraint is constraint_name.

 

ALTER TABLE table_name

add constraint constraint_name

unique (column_name)

 

Disabling constraints

Integrity constraints can be disabled with the ALTER TABLE command.

 

ALTER TABLE table-name disable constraint-specification;

ALTER TABLE table-name disable constraint constraint-name;

 

Adding new Column

ALTER TABLE foo_table add bar_column char(1);

ALTER TABLE foo_table add (bar_column1 char(1),bar_column2 char(1));

 

Modifying a column

Renaming a column name

ALTER TABLE some_table rename column column_name to new_column_name;

 

Changing a column’s type

A column’s type can be changed if the column is empty within the entire table, otherwise Oracle gives an ORA-01440: column to be modified must be empty to decrease precision or scale.

 

ALTER TABLE some_table modify (column_name number);

 

Changing null to not null or vice versa

ALTER TABLE some_table modify (column_name not null);

 

ALTER TABLE some_table modify col_not_null number null;

 

 

Specifying tablespace for index

ALTER TABLE tbl add constraint pk_tbl

primary key (col_1, col_2)

using index tablespace ts_idx

 

Removing a constraint

ALTER TABLE table_name drop constraint constraint_name;