Tip of the Day - Oracle & PostgreSQL for Developer & Administrator

Tip of the Day ( Saturday, November 15, 2025 )

 ** Email to info@shreyantech.com to get the Tip of the Day in your mailbox.

Generate HTML Reports in SQL* Plus

HTML Output: SQL*Plus can also generate HTML reports using the SET MARKUP HTML ON command in conjunction with SPOOL. This allows you to create web-ready reports that can be viewed in a browser.

Example

SET MARKUP HTML ON SPOOL ON
SPOOL my_html_report.html
SELECT * FROM my_table;
SPOOL OFF
SET MARKUP HTML OFF


#####################################################################



Tip of the Day ( Friday, November 14, 2025 )

 ** Email to info@shreyantech.com to get the Tip of the Day in your mailbox.

SPOOL command in SQL* Plus

The SPOOL command in SQLPlus is used to capture the output of commands and queries into a text file. This allows you to save the results of your SQLPlus session for later review, analysis, or printing.

spool command options:

1. Basic Spooling - To start spooling output to a file, use the SPOOL command followed by the desired filename:

Example: SPOOL my_output.txt

  • You can use relative paths (e.g., SPOOL emp.txt) or absolute paths (e.g., C:\Reports\emp.txt).
  • Spooling starts when you issue SPOOL filename. All output (query results, messages, etc.) is written to the file.


2. Stopping Spooling: To stop spooling and close the output file, use: SPOOL OFF

3. Spooling to an Existing File: By default, if the file already exists, SPOOL will overwrite its contents. This is the same as using REPLACE option.

Example: SPOOL my_output.txt REPLACE

4. Appending content: To add new output to the end of an existing file, use APPEND option.

Example: SPOOL my_output.txt APPEND

#####################################################################





** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

Edit SQL Query in SQL* Plus

When you execute a SQL query in SQL* Plus, the SQL command is stored in the SQL buffer. You can edit the SQL statement from its buffer and re-execute it.

SQL> SELECT
  2  employee_id, first_name, department_id, salary
  3    FROM employees
  4   WHERE department_id = 20
  5     AND salary > 6000;

EMPLOYEE_ID FIRST_NAME           DEPARTMENT_ID     SALARY

----------- -------------------- ------------- ----------
        201 Michael                         20      13000

First, display the SQL that is in the buffer using LIST (or L in short-form) command.

SQL> LIST
  1  SELECT
  2  employee_id, first_name, department_id, salary
  3    FROM employees
  4   WHERE department_id = 20
  5*    AND salary > 6000

In the above output, the last line is the current line as it has a star (asterisk) next to the line number. You can edit only the current line.

Now, I want to change the department ID from 20 to 10. So first I need to make the 4th line as the current line.

SQL> L 4
  4*  WHERE department_id = 20
SQL> change/20/10

Now, 4th line is my current line. So I can edit this line now using the change (c for short-form) command. 

change command can be used to find and replace any text from the current line.

SQL> change/20/10
  4*  WHERE department_id = 10

So now the 4th line is changed from 20 to 10. 

If you want to list the new SQL query, just type LIST or L and hit enter key. You will see the updated query. 

SQL> L
  1  SELECT
  2  employee_id, first_name, department_id, salary
  3    FROM employees
  4   WHERE department_id = 10
  5*    AND salary > 6000

If you want to execute it, you can just type run (or r for short-from) or "/" (slash).

SQL> run
  1  SELECT
  2  employee_id, first_name, department_id, salary
  3    FROM employees
  4   WHERE department_id = 10
  5*    AND salary > 6000
no rows selected


#####################################################################




Tip of the Day ( Wednesday, November 12, 2025 )

 ** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

LIST (L) Command in SQL* Plus

LIST command is used to list the command that is in buffer. The SQL*PLus can store, the last command that is executed, in the buffer. 

This is the SQL command that I have executed now and it is stored in the SQL* Plus buffer. 

SQL> select
  2   employee_id, first_name, department_id, salary
  3  FROM employees
  4  WHERE department_id = 20;
EMPLOYEE_ID FIRST_NAME           DEPARTMENT_ID     SALARY
----------- -------------------- ------------- ----------
        201 Michael                         20      13000
        202 Pat                             20       6000

Now, if I just type LIST in the SQL*Plus, it will list the previously executed query that is in the buffer. 

SQL> LIST
  1  select
  2   employee_id, first_name, department_id, salary
  3  FROM employees
  4* WHERE department_id = 20

The current line will have the star (asterisk) sign next to the line number. all the SQL*Plus editing can be performed on the current line (such as delete the line, find & replace a word in the line, append more text to the line etc.)

"L" is the short-hand form for LIST command. It will do the same thing as list. 

SQL> l
  1  select
  2   employee_id, first_name, department_id, salary
  3  FROM employees
  4* WHERE department_id = 20

LIST 2 will list the 2nd line and make it the current line (Notice the asterisk sign).

SQL> LIST 2
  2*  employee_id, first_name, department_id, salary

LIST 2 4 will list all the lines from 2nd to 4th Line.

SQL> LIST 2 4
  2   employee_id, first_name, department_id, salary
  3  FROM employees
  4* WHERE department_id = 20  

LIST LAST will list only the Last line.

SQL> LIST LAST
  4* WHERE department_id = 20

LIST * 4 will list all the lines from the current line to the 4th line.

SQL> LIST 2
  2*  employee_id, first_name, department_id, salary  ==> 2nd line is the current line.
SQL> LIST * 4
  2   employee_id, first_name, department_id, salary
  3  FROM employees
  4* WHERE department_id = 20
SQL>


#####################################################################



Tip of the Day ( Tuesday, November 11, 2025 )

 ** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

DESCRIBE (DESC) Command (SQL* Plus - Part 08)

The DESCRIBE command in SQL*Plus is used to display the structure of a database object — typically a table, view, synonym or procedure/ function. This is one of the most commonly used and useful commands, especially for exploring database tables, views, and other objects.

Syntax: 
DESCRIBE <object_name>

In short-hand notation: 
DESC <object_name>

Since DESCRIBE command is a SQL*PLus command. You do not need semi-colon at the end of the command.

Example

Table

SQL> DESCRIBE DEPARTMENTS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPARTMENT_ID                             NOT NULL NUMBER(4)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 MANAGER_ID                                         NUMBER(6)
 LOCATION_ID                                        NUMBER(4)


Table with Short-hand notation

SQL> DESC EMPLOYEES
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)


View

SQL> DESC EMP_DETAILS_VIEW
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 JOB_ID                                    NOT NULL VARCHAR2(10)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)
 LOCATION_ID                                        NUMBER(4)
 COUNTRY_ID                                         CHAR(2)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 DEPARTMENT_NAME                           NOT NULL VARCHAR2(30)
 JOB_TITLE                                 NOT NULL VARCHAR2(35)
 CITY                                      NOT NULL VARCHAR2(30)
 STATE_PROVINCE                                     VARCHAR2(25)
 COUNTRY_NAME                                       VARCHAR2(60)
 REGION_NAME                                        VARCHAR2(25)


Procedure (Displays the parameters detail)

SQL> DESC ADD_JOB_HISTORY
PROCEDURE ADD_JOB_HISTORY
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_EMP_ID                       NUMBER(6)               IN
 P_START_DATE                   DATE                    IN
 P_END_DATE                     DATE                    IN
 P_JOB_ID                       VARCHAR2(10)            IN
 P_DEPARTMENT_ID                NUMBER(4)               IN


Synonym

SQL> DESCRIBE MY_EMP
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)


#####################################################################


Tip of the Day ( Monday, November 10, 2025 )


 ** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

PAGESIZE 0 Vs. Heading OFF (SQL* Plus - Part 07)

Today we'll see the difference between page size 0 and set heading off. 

As you know, the page size defines the number of lines after which there will be a page break and the column heading will repeat. 

For example, the default page size is 14 and Heading is enabled.

SQL> show pagesize
pagesize 14
SQL> show heading
heading ON

So in my query result, there will be a page break and the column heading will repeat after every 13 rows (including the column heading and the Page Break).


SQL> select employee_id, first_name, department_id from employees
  2  where department_id = 80;
EMPLOYEE_ID FIRST_NAME           DEPARTMENT_ID
----------- -------------------- -------------
        145 John                            80
        146 Karen                           80
        147 Alberto                         80
        148 Gerald                          80
        149 Eleni                           80
        150 Sean                            80
        151 David                           80
        152 Peter                           80
        153 Christopher                     80
        154 Nanette                         80
        155 Oliver                          80
EMPLOYEE_ID FIRST_NAME           DEPARTMENT_ID
----------- -------------------- -------------
        156 Janette                         80
        157 Patrick                         80
        158 Allan                           80
        159 Lindsey                         80
 .....
 .....


If I set the pagesize to 0, then it will disable the heading and the page break in your query result. You'll see continuous rows of data without heading or page break. 

SQL> set pages 0

SQL> show heading
heading ON
SQL>
SQL> r
  1  select employee_id, first_name, department_id from employees
  2* where department_id = 80
        145 John                            80
        146 Karen                           80
        147 Alberto                         80
        148 Gerald                          80
        149 Eleni                           80
        150 Sean                            80
        151 David                           80
        152 Peter                           80
        153 Christopher                     80
        154 Nanette                         80
        155 Oliver                          80
        156 Janette                         80
        157 Patrick                         80
        158 Allan                           80
        159 Lindsey                         80
        160 Louise                          80
        161 Sarath                          80
        162 Clara                           80
        163 Danielle                        80
 ....
 ....
        177 Jack                            80
        179 Charles                         80
34 rows selected.

But if you set the pagesize back to 14, and set heading to OFF, then you'll see there is a page break after 13 rows, (pagesize is 14), but there is no heading. (because heading is off).

SQL> set pages 14

SQL> set heading off
SQL>
SQL> r
  1  select employee_id, first_name, department_id from employees
  2* where department_id = 80
        145 John                            80
        146 Karen                           80
        147 Alberto                         80
        148 Gerald                          80
        149 Eleni                           80
        150 Sean                            80
        151 David                           80
        152 Peter                           80
        153 Christopher                     80
        154 Nanette                         80
        155 Oliver                          80
        156 Janette                         80
        157 Patrick                         80

        158 Allan                           80
        159 Lindsey                         80
        160 Louise                          80
        161 Sarath                          80
 ......
 ......
        177 Jack                            80
        179 Charles                         80
34 rows selected.
SQL>

This is the effect of using pagesize 0 Vs. Heading OFF.


#####################################################################




Tip of the Day ( Saturday, November 08, 2025 )



** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

PAGESIZE (PAGES) in SQL* Plus (SQL* Plus - Part 06)

PAGESIZE

The PAGESIZE (or PAGES) setting in SQL*Plus controls how many lines of output are printed per page before SQL*Plus repeats the column headings.

Syntax to set pagesize:

SET pagesize <number_of_lines>
OR
SET pages <number_of_lines>

pages is a short-hand notation for pagesize.

Example:

set pagesize 50
set pages 30

To see the current page size setting, use the show command

Show pagesize 
or 
show pages

The default value for pagesize is 14. This means SQL*Plus will Print 14 lines of query output, Then repeat the column headers, And start a new page of results.

if you set the pagesize to 0 using the command "set pages 0", then SQL*Plus does not repeat headings and does not insert page breaks. All results are shown continuously.

**NOTE -  If you do not set the pagesize to zero but set the heading off (using the command SET HEADING OFF), then SQL* Plus will not display the column heading in the query result, but will print the page break as per the pagesize setting. 

If you want to reset the pagesize to its default value of 14, then use the set command as in below:

SET PAGES 14

#####################################################################



Tip of the Day ( Friday, November 07, 2025 )

 ** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

Few Basic Commands for SQL* Plus (SQL* Plus - Part 05)

Few Basic Commands of SQL* Plus (Oracle 19c / 23ai)

Unlike SQL Commands, SQL* Plus command does not require a semi-colon at the end of the command. You type the SQL* Plus command and hit enter to execute the command.

  • SHOW USER - Displays the current user connected to Oracle DB

SQL> show user
USER is "HR"
SQL>

  •  show con_name - Displays the current container name for the session. (Applicable in multitenant databases such as CDB/PDB).

SQL> show con_name

CON_NAME
------------------------------
FREEPDB1
SQL>

  • HELP - Provides help information about SQL* Plus commands and topics. You can mention the any command with the "help" command to get help information about that specific command.
SQL> help
SQL> help index
SQL> help connect

  • HISTORY - Displays a list of recently executed commands in the SQL*Plus session. You will have to enable the history option with the "SET HISTORY ON" command first and then SQL* Plus will track all the executed commands in history.
SQL> history
SP2-1650: History is off, use "SET HIST[ORY] ON" to enable History.
SQL> set history ON
SQL> history
  1  help
  2  help index
  3  show con_name

SQL>

  • PASSWORD - Allows you to change the password of the currently connected Oracle user. SQL*Plus will prompt you to enter old and new passwords securely.

#####################################################################



Tip of the Day ( Thursday, November 06, 2025 )

 ** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

Connecting to Oracle Database using TNSNAMES via SQL* Plus (SQL* Plus - Part 04)

You can connect from SQL* Plus in Oracle Client to an Oracle Database running on a remote server using TNS alias. Below are the steps:

1. Add the TNS alias into the tnsnames.ora file. The default location for tnsnames.ora file is <OracleClient_Home>/network/admin.

2. Get the IP Address and port number where the Oracle Database is running. Also get the database service name which is listed in the Listener process. These detail can be requested from your Database Administrator.

3. Add the TNS Alias in the tnsnames.ora file using the following format:

ORCL_REMOTE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.50)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = orclpdb)
    )
  )


4. From your OS command prompt (such as Windows Command prompt), launch SQL* Plus and connect to the database with the TNS Alias name using the syntax below:

sqlplus hr/oracle@ORCL_REMOTE
 
Here is an Example:

TNS Alias Entry:

PDB26AI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.244)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = freepdb1)
    )
  )

DB connection using SQL* PLus

C:\Users\suchi>sqlplus hr/oracle@pdb26ai

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 6 06:04:50 2025
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Thu Nov 06 2025 05:53:33 -05:00

Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0

SQL>

#####################################################################


Tip of the Day ( Wednesday, November 05, 2025 )

 ** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

Launching SQL* Plus (SQL* Plus - Part 03)

You can Launch SQL* Plus in various modes:

  1. just type "sqlplus" in your OS command prompt and hit enter. It will start the SQL* Plus utility and prompt for username and password.
Example:

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 23.0.0.0.0 - Production on Wed Nov 5 14:05:36 2025

Version 23.9.0.25.07

Copyright (c) 1982, 2025, Oracle.  All rights reserved.


Enter user-name: hr

Enter password:

Last Successful login time: Wed Nov 05 2025 14:03:03 +00:00

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.9.0.25.07


SQL>

 

2. Type sqlplus and pass the username and (or) password and DB connection string as parameters to launch and connect to the database in a single command. This method will expose your password on the screen. If you do not provide the password with sqlplus parameter list, SQL* Plus will prompt for password.

Example:

[oracle@localhost ~]$ sqlplus hr/oracle@freepdb1

SQL*Plus: Release 23.0.0.0.0 - Production on Wed Nov 5 14:03:03 2025

Version 23.9.0.25.07

Copyright (c) 1982, 2025, Oracle.  All rights reserved.


Last Successful login time: Wed Nov 05 2025 13:58:55 +00:00

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.9.0.25.07

SQL>

 3. if you need to ONLY launch sqlplus and do not want to connect to the database or do not want SQL* Plus to prompt for username & password, you can use the /nolog option. After you are in SQL* Plus prompt, you can use CONNECT command to connect to the database.

Example:

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 23.0.0.0.0 - Production on Wed Nov 5 13:58:42 2025
Version 23.9.0.25.07

Copyright (c) 1982, 2025, Oracle.  All rights reserved.

SQL> connect hr/oracle
Connected.
SQL>


#####################################################################



Tip of the Day ( Tuesday, November 04, 2025 )

** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

Setting up Environment Variables for SQL* Plus (SQL* Plus - Part 02)

To make the SQL* Plus work and you can launch it without issue, following environment variables must be set: For any Operating system such as WINDOWS or Linux etc.)

  • ORACLE_HOME - Set the ORACLE_HOME variable to your Oracle (database server or client) installation home directory.
  • PATH - sqlplus executable is installed in directory $ORACLE_HOME/bin directory. Add this path to the PATH variable.
  • LD_LIBRARY_PATH must be set to $ORACLE_HOME/lib directory.
  • TNS_ADMIN - By default, database network related files (such as tnsnames.ora and sqlnet.ora) are located in directory $ORACLE_HOME/network/admin. if these files are placed in any other non-default location, please set that location to TNS_ADMIN variable.

For Linux OS, you can use export command for you session or add it to your profile file (e.g: bash_profile) to set these variables automatically at every login.

For WINDOWs OS, these variables can be set in the Windows registry or on the environment variable setting.

Once all the above environment variables are set properly, you can invoke sqlplus and launch it without any issue.

  #####################################################################


Tip of the Day ( Monday, November 03, 2025 )

** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.


What is SQL* Plus (SQL* Plus - Part 01)             Click here for a 1 min. Video of this topic

SQL* Plus is an interactive and batch query tool that is installed with every Oracle Database Installation (Server or Client). It is a command-line user interface.

There is also the SQL* Plus instant client which is a standalone command-line interface available on platforms that support the OCI instant client.

SQL* Plus has its own commands and environment, and it provides access to the Oracle database. It enables you to enter and execute SQL, PL/SQL, SQL* Plus and operating system commands to perform the following:

  • Format, perform calculation on, store, and print from query result
  • Examine table and object definitions
  • Develop and run batch scripts
  • Perform database administration

·     #####################################################################


Tip of the Day ( Sunday, November 02, 2025 )

 ** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.


Right Outer Join (ANSI Syntax) (Video: https://youtube.com/shorts/4_wj0UgtqsE?feature=share )

A RIGHT OUTER JOIN returns all the rows from the table which is on the right side of the JOIN clause and returns the matching rows from the table which is on the left side of the JOIN clause.
For the non-matching rows, Oracle returns NULLs for the left side table’s columns.

Example:

SELECT d.department_id, d.department_name, location_id, l.city
  FROM departments d RIGHT OUTER JOIN locations l
 USING(location_id);


Since locations table is on the right side of the JOIN clause, this query will list all the rows from the locations table columns. 

The departments table is on the left side of the JOIN clause. So, only the matching rows (matched by location_id column) will be listed for department table. 

For the non-matching rows from the locations tables, NULL will be listed for departments table.

Below is the output:

DEPARTMENT_ID DEPARTMENT_NAME        LOCATION_ID CITY
------------- ------------------------------ ----------- ----------------------------
    10 Administration         1700 Seattle
        20 Marketing               1800 Toronto
        30 Purchasing              1700 Seattle
        40 Human Resources         2400 London
        50 Shipping              1500 South San Francisco
        60 IT                   1400 Southlake
        70 Public Relations         2700 Munich
        80 Sales              2500 Oxford
        90 Executive          1700 Seattle
       100 Finance         1700 Seattle
       110 Accounting         1700 Seattle
       120 Treasury         1700 Seattle
       130 Corporate Tax         1700 Seattle
       140 Control And Credit         1700 Seattle
       150 Shareholder Services         1700 Seattle
       160 Benefits         1700 Seattle
       170 Manufacturing         1700 Seattle
       180 Construction         1700 Seattle
       190 Contracting         1700 Seattle
       200 Operations         1700 Seattle
       210 IT Support         1700 Seattle
       220 NOC         1700 Seattle
       230 IT Helpdesk         1700 Seattle
       240 Government Sales         1700 Seattle
       250 Retail Sales         1700 Seattle
       260 Recruiting         1700 Seattle
       270 Payroll         1700 Seattle
    2200 Sydney
    2300 Singapore
    1000 Roma
    1600 South Brunswick
    2600 Stretford
    2900 Geneva
    3100 Utrecht
    3200 Mexico City
    2800 Sao Paulo
    1100 Venice
         1900 Whitehorse
         1300 Hiroshima
         3000 Bern
    1200 Tokyo
    2000 Beijing
    2100 Bombay
43 rows selected.

###########################################################################

Tip of the Day ( Saturday, November 01, 2025 )

** Email to info@shreyantech.com to get the Daily Tip of the Day in your mailbox.

LEFT Outer Join (ANSI Syntax)

** Watch This Tip of the Day at: https://youtube.com/shorts/6axYED8wjzw?feature=share 

A LEFT OUTER JOIN returns all rows from the table which is on the left side of the JOIN clause and only the matching rows from the right side table.

If there is no match in the right side table, Oracle returns NULLs for the right table’s columns.

Example:
SELECT l.location_id, l.city, d.department_id, d.department_name
   FROM locations l LEFT OUTER JOIN departments d
   ON l.location_id = d.location_id;

In the above query, all the rows from LOCATIONS table (Left side table) are returned and only the matching rows are returned from the DEPARTMENTS table. For non-matching rows, NULL are returned for DEPARTMENTS table. 

Below is the output for the above query. As you can see, for the city locations, which do not have any departments, the department ID and names are listed as NULL.

LOCATION_ID CITY                DEPARTMENT_ID DEPARTMENT_NAME
----------- ------------------------------ ------------- ----------------------------
       1700 Seattle       10           Administration
       1800 Toronto       20           Marketing
       1700 Seattle       30           Purchasing
       2400 London            40           Human Resources
       1500 South San Francisco       50           Shipping
       1400 Southlake       60           IT
       2700 Munich            70           Public Relations
       2500 Oxford            80           Sales
       1700 Seattle       90           Executive
       1700 Seattle      100           Finance
       1700 Seattle      110           Accounting
       1700 Seattle      120           Treasury
       1700 Seattle      130           Corporate Tax
       1700 Seattle      140           Control And Credit
       1700 Seattle      150           Shareholder Services
       1700 Seattle      160           Benefits
       1700 Seattle      170           Manufacturing
       1700 Seattle      180           Construction
       1700 Seattle      190           Contracting
       1700 Seattle      200           Operations
       1700 Seattle      210           IT Support
       1700 Seattle      220           NOC
       1700 Seattle      230           IT Helpdesk
       1700 Seattle      240           Government Sales
       1700 Seattle      250           Retail Sales
       1700 Seattle      260           Recruiting
       1700 Seattle      270           Payroll
       2200 Sydney
       2300 Singapore
       1000 Roma
       1600 South Brunswick
       2600 Stretford
       2900 Geneva
       3100 Utrecht
       3200 Mexico City
       2800 Sao Paulo
       1100 Venice
       1900 Whitehorse
       1300 Hiroshima
       3000 Bern
       1200 Tokyo
       2000 Beijing
       2100 Bombay
43 rows selected.

###########################################################################


Tip of the Day ( Friday, October 31, 2025 )

NVL2 function in Oracle 19c / 23ai

The NVL2 function is an enhanced version of NVL that allows you to return different values depending on whether an expression is NULL or NOT NULL.

Syntax: NVL2(expr1, expr2, expr3)

Where:

expr1 → The expression to check for NULL.
expr2 → The value returned if expr1 is NOT NULL.
expr3 → The value returned if expr1 IS NULL.

 

Example:
SELECT 
     first_name, salary, commission_pct,
     NVL2(commission_pct, 'Earns Commission', 'No Commission') AS commission_status
 FROM employees
WHERE department_id = 30;

FIRST_NAME SALARY     COMMISSION_PCT COMMISSION_STATU
-------------------- ---------- -------------- ----------------
Den       11000                    No Commission
Alexander        3100               .25  Earns Commission
Shelli   2900                    No Commission
Sigal   2800                    No Commission
Guy        2600                    No Commission
Karen   2500               .5   Earns Commission
6 rows selected.
SQL> 



Explanation:

If an employee’s commission_pct has a value, the result shows “Has Commission.” If it’s NULL, it shows “No Commission.”

Example 2 – You can also use NVL2 for numeric operations.

SELECT first_name, salary, commission_pct,
   NVL2(commission_pct, salary + (salary * commission_pct), salary) AS total_earnings
FROM employees
WHERE department_id = 30;

FIRST_NAME     SALARY     COMMISSION_PCT COMMISSION_STATU
-------------------- ---------- -------------- ----------------
Den           11000                No Commission
Alexander        3100            .25 Earns Commission
Shelli        2900                No Commission
Sigal        2800                No Commission    
Guy            2600                No Commission
Karen        2500             .5 Earns Commission

6 rows selected.

SQL> 

 Explanation:

If commission_pct is NOT NULL, then it adds commission to salary. If commission_pct is NULL. then it just returns the salary.


Check the Youtube video on this topic: https://youtube.com/shorts/6axYED8wjzw?feature=share 

Send email to info@shreyantech.com to join to the Daily Tip of the Day mailing List.

############################################################################

Tip of the Day ( Thursday, October 30, 2025 )

NVL function in Oracle SQL

In Oracle SQL, NVL function is used to handle NULL values. NULL represents missing or unknown data. The NVL function helps you replace or react to NULL values dynamically.

Syntax: NVL(expr1, expr2)

If expr1 is not null, it returns expr1; otherwise, it returns expr2.

Example:

SELECT first_name,
       salary,
       commission_pct,
   NVL(commission_pct, 0)
 FROM employees
WHERE department_id = 30;

If commission_pct is NULL, it returns 0 instead — perfect for avoiding NULLs in calculations.


Check the Youtube video on this topic.

Send email to info@shreyantech.com to join to the Daily Tip of the Day mailing List.

#####################################################################


Tip of the Day ( Wednesday, October 29, 2025 )

Different between SQL, PL/SQL & SQL* Plus in Oracle

SQL (Structured Query Language) is used to interact with the database by performing operations such as retrieving, inserting, updating, and deleting data. It is a declarative language, meaning you specify what you want to be done, and the database figures out how to do it. For example, SELECT * FROM employees; retrieves all records from the employees table. SQL is a standard language used across various database systems, not just Oracle. SQL is pronounced as “Sequel”

PL/SQL (Procedural Language/SQL) is Oracle’s extension to SQL that adds programming features like loops, conditions, and exception handling. It allows you to group multiple SQL statements into one block of code, making it ideal for writing stored procedures, functions, and triggers.

SQL*Plus is a command-line tool provided by Oracle to execute SQL and PL/SQL commands. It’s often used by database administrators and developers to connect to the database, run queries, execute scripts, and view results directly from the terminal.

Check the Youtube video on this topic: https://youtube.com/shorts/FszUoBL6XNg?feature=share

Send email to info@shreyantech.com to join to the Daily Tip of the Day mailing List.

#####################################################################

Tip of the Day ( Tuesday, October 28, 2025 )


IS SELECT statement in Oracle a DML?

The SELECT statement is widely considered a part of Data Manipulation Language (DML). However, it is fundamentally different from other DML commands because it does not modify the data in the database.

The core function of DML is to access and manipulate data within database objects. Since SELECT is used to retrieve and process data (e.g., using WHERE, GROUP BY, and aggregate functions), it fits this definition.

The main point of debate is that SELECT is a "read-only" command, while the others change the data. This has led some to classify SELECT as part of a separate Data Query Language (DQL), a subset of DML.


Check the Youtube video on this topic: https://youtube.com/shorts/fnUe6hzyTHc?feature=share

Send email to info@shreyantech.com to join to the Daily Tip of the Day mailing List.

#####################################################################


Tip of the Day ( Monday, October 27, 2025 )

Precision and scale of Number data type in Oracle

In Oracle database, you can specify a fixed-point number using the form: NUMBER(p.s)

  • ‘p’ is the precision which indicates the maximum number of total significant decimal digits. (From the most to the least significant digit)
  • ‘s’ is the scale which indicates the number of digits from the decimal point to the least significant digit, The scale can range from -84 to 127.

Please see the image below for a better understanding.



##################################################################### 


Tip of the Day ( Sunday, October 26, 2025 )

About VARCHAR2 data type in Oracle

VARCHAR2 – Variable length character string in the database character set. VARCHAR2 only uses as much space as needed for the stored data, making it more efficient.

Syntax: VARCHAR2 (size [BYTE | CHAR])

  • size option is mandatory and defines the maximum length of the VARCHAR data type.
  • Minimum size : 1 byte or character
  • Maximum size : 4000 bytes or characters


##################################################################### 


Saturday, October 25, 2025

About CHAR datatype in Oracle

CHAR is a Fixed length character strings in the Database character set. 

  • Syntax : CHAR [(size [BYTE | CHAR])]
  • Size is optional. Default size: 1 BYTE
  • Minimum size : 1 byte, Maximum size : 2000 bytes

Example: (In a CREATE TABLE statement)

CREATE TABLE local
(
 state_code CHAR(2),   // Fixed 2 byte state code
 country_code CHAR(3), // Fixed 3 byte country code
 active CHAR           // 'Y' or 'N' - Fixed 1 byte active status. Default size
);


No comments:

Post a Comment

Subscribe to: Comments (Atom)