Different Star Patterns in SQL

Introduction

Generally, the patterns are very interesting to perform. We can make as many shapes as we can in patterns. Let us dive into different types of patterns. Now, we are here making shapes using only '*' symbol. Let explore the * patterns in which we can build different patterns.

The SQL Server I am using is Oracle 10g. So, the SQL Pattern Code looks similar to the PL / SQL Codes.

1.) Pattern 1

Sample Pattern

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

Pattern Code

SQL> DECLARE
    n INTEGER;
    i INTEGER;
    j INTEGER;
  5
    BEGIN
  7
    n:=&n;
    FOR i IN 1..n LOOP
   FOR j IN 1..n LOOP
   dbms_output.put('* ');
   END LOOP;
    DBMS_OUTPUT.NEW_LINE;
   END LOOP;
   END;
   /

Input 1

Enter value for n: 5
old   8:     n:=&n;
new   8:     n:=5;

Output 2

* * * * *
* * * * *
* * * * *
* * * * *
* * * * *
PL/SQL procedure successfully completed.

Input 2

Enter value for n: 8
old   8:     n:=&n;
new   8:     n:=5;

Output 2

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

PL/SQL procedure successfully completed.

2.) Pattern 2

Pattern Design

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

Pattern Code

SQL>  DECLARE
    n INTEGER;
    i INTEGER;
    j INTEGER;
  5
    BEGIN
  7
    n:=&n;
    FOR i IN 1..n LOOP
   FOR j IN 1..i LOOP
   dbms_output.put('* ' );
   END LOOP;
    DBMS_OUTPUT.NEW_LINE;
   END LOOP;
   END;
   /

Input 1

Enter value for n: 5
old   8:     n:=&n;
new   8:     n:=5;

Output 1

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

PL/SQL procedure successfully completed.

Input 2

Enter value for n: 10
old   8:     n:=&n;
new   8:     n:=10;

Output 2

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

PL/SQL procedure successfully completed.

Input 3

Enter value for n: 6
old   8:     n:=&n;
new   8:     n:=10;

Output 3

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

PL/SQL procedure successfully completed.

3.) Pattern 3

Pattern Design

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

Pattern Code 1

SQL> DECLARE
       n INTEGER;
       i INTEGER;
       j INTEGER;
  5
      BEGIN
  7
       n:=&n;
     FOR i IN 1..n LOOP
     FOR j IN 1..n-i+1 LOOP
     dbms_output.put('* ' );
 12
 13
     END LOOP;
       DBMS_OUTPUT.NEW_LINE;
      END LOOP;
      END;
      /

Input 1

Enter value for n: 5
old   8:        n:=&n;
new   8:        n:=5;

Output 1

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

PL/SQL procedure successfully completed.

Input 2

Enter value for n: 6
old   8:        n:=&n;
new   8:        n:=6;

Output 2

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

PL/SQL procedure successfully completed.

Input 3

Enter value for n: 10
old   8:        n:=&n;
new   8:        n:=10;

Output 3

* * * * * * * * * *
* * * * * * * * *
* * * * * * * *
* * * * * * *
* * * * * *
* * * * *
* * * *
* * *
* *
*
PL/SQL procedure successfully completed.

4.) Pattern 4

Pattern Sample

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

Pattern Code

SQL> DECLARE
n INTEGER;
i INTEGER:=1;
j INTEGER;
k INTEGER;
BEGIN
n:=&n;
WHILE i<n
loop
j:=1;
WHILE j<n-i
loop
dbms_output.put(' ');
j:=j+2;
end loop;
 16
for k in 1..i
loop
dbms_output.put('* ');
end loop;
dbms_output.new_line;
i:=i+2;
end loop;
 24
i:=7;
while i>=1
loop
j:=9;
while j>i
loop
dbms_output.put(' ');
j:=j-2;
end loop;
 34
for k in 1..i
loop
dbms_output.put('* ');
end loop;
dbms_output.new_line;
i:=i-2;
end loop;
end;
/

Input 1

Enter value for n: 10
old   8:        n:=&n;
new   8:        n:=10;

Output 2

*
* * *
* * * * *
* * * * * * *
* * * * * * * * *
* * * * * * *
* * * * *
* * *
*
PL/SQL procedure successfully completed.

Input 2

Enter value for n: 5
old   7: n:=&n;
new   7: n:=5;

Output 2

*
* * *
* * * * * * *
* * * * *
* * *
*
PL/SQL procedure successfully completed.

4.) Pattern 4

Pattern Sample

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

Sample Code

SQL> DECLARE
n number;
i number:=1;
j number;
k number;
BEGIN
    n:=&n;
while i<n
LOOP
 10
j:=1;
while j<n-i
LOOP
dbms_output.put(' ');
j:=j+2;
END LOOP;
 17
for k in 1..i
LOOP
dbms_output.put('* ');
END LOOP;
 22
dbms_output.new_line;
i:=i+2;
END LOOP;
END;
/

Input 1

Enter value for n: 10
old   7:     n:=&n;
new   7:     n:=10;

Output 1

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

PL/SQL procedure successfully completed.

Input 2

Enter value for n: 5
old   7:     n:=&n;
new   7:     n:=5;e

Output 2

           *
         * * *

PL/SQL procedure successfully completed.

Input 3

Enter value for n: 20
old   7:     n:=&n;
new   7:     n:=20;

Output 3

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

PL/SQL procedure successfully completed.

5.) Pattern 5

Pattern Sample

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

Pattern Code

SQL> DECLARE
i number;
j number;
k number;
BEGIN
    i:=&i;
while i>=1
LOOP
j:=9;
while j>i
LOOP
dbms_output.put(' ');
j:=j-2;
END LOOP;
 15
for k in 1..i
LOOP
dbms_output.put('* ');
END LOOP;
 20
dbms_output.new_line;
i:=i-2;
END LOOP;
END;
/

Input 1

Enter value for i: 9
old   6:     i:=&i;
new   6:     i:=9;

Output 1

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

Input 2

Enter value for i: 13
old   6:     i:=&i;
new   6:     i:=9;

Output 2

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

6.) Pattern 6

Sample Design

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

Pattern Code

SQL> declare
n number;
i number;
j number;
k number;
begin
    n:=&n;
for i in 1..n
loop
for j in 1..n-i
loop
dbms_output.put(' ');
end loop;
 14
for k in 1..i
loop
dbms_output.put('* ');
end loop;
 19
dbms_output.new_line;
end loop;
end;
/

Input 1

Enter value for n: 5
old   7:     n:=&n;
new   7:     n:=5;

Output 1

        *
      * *
    * * *
  * * * *
* * * * *
PL/SQL procedure successfully completed.

Input 2

Enter value for n: 9
old   7:     n:=&n;
new   7:     n:=5;

Output 2

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


PL/SQL procedure successfully completed.

Next TopicSQL General Functions: NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL and NANVL

← prev next →