SQL to Get dates missing in a table for given date range

 WITH all_dates AS (

    SELECT DATE '2025-01-01' + LEVEL - 1 AS dt

    FROM dual

    CONNECT BY LEVEL <= (DATE '2026-01-01' - DATE '2025-01-01') + 1

),

existing_dates AS (

    SELECT TRUNC(creation_date) AS dt

    FROM  XXCUST.CUSTOM_TL -- your table name

    WHERE creation_date BETWEEN DATE '2025-01-01' AND DATE '2026-01-01'

)

SELECT a.dt AS missing_date

FROM all_dates a

LEFT JOIN existing_dates e

       ON a.dt = e.dt

WHERE e.dt IS NULL

ORDER BY a.dt;

SQL And PLSQL OVERVIEW

Here is the **HTML version** of your formatted “SQL & PL/SQL Overview” document. It includes structured sections, syntax-highlighted code blocks, and well-organized content. --- ### ✅ \[Downloadable HTML File Option] If you'd like, I can also give you a `.html` file to upload directly to your Blogspot or view offline. Just let me know. --- ### 📄 Full HTML Code: ```html

SQL & PL/SQL Overview

🔖 Audience

This tutorial is for software professionals who want to learn PL/SQL in a simple and practical way. After finishing this, you'll understand key PL/SQL concepts and be ready to advance further in database development.

✉️ Prerequisites

• What a database is
• Programming fundamentals
• SQL basics

Tip: Knowledge of any programming language is helpful.

What is a Database?

There are two main types of databases:

1. Relational Databases (RDBMS)
   • Examples: Oracle DB, MySQL, SQL Server, IBM DB2
2. Non-Relational Databases (NoSQL)
   • Examples: MongoDB, Cassandra, Apache HBase

Oracle DB Versions

Common versions: 8i, 10g, 12c

Key Features

• Manageability
• High Availability
• Performance
• Security (Roles and Grants)

SQL (Structured Query Language)

• Pronounced as "SEQUEL"
• Used for accessing RDBMS
• Set-oriented: handles multiple records at once
• Case-insensitive
• Does not support control structures
• Can be embedded in other languages

📒 SQL Command Types

1. DDL (Data Definition Language)

Used to define and manage database objects like tables and views.

• CREATE: Create database objects
• ALTER: Modify existing objects
• DROP: Delete objects
• TRUNCATE: Remove all rows quickly
• COMMENT: Add descriptive notes

CREATE TABLE NEWTAB (SNO NUMBER(5), SNAME VARCHAR2(10));
ALTER TABLE NEWTAB ADD (ID NUMBER(5));
DROP TABLE NEWTAB;

2. Flashback & Recycle Bin

• FLASHBACK TABLE: Restore dropped table
• PURGE: Permanently delete from recycle bin

SQL Data Types

Data Type	Description
NUMBER	Stores numeric values (up to 38 digits)
CHAR	Fixed-length strings (up to 2000 bytes)
VARCHAR2	Variable-length strings (up to 4000 bytes)
DATE	Stores date/time values
TIMESTAMP	Includes fractional seconds
TIMESTAMP WITH TIMEZONE	Stores time zone info
LONG, RAW	Stores large or binary data
CLOB, BLOB, BFILE	Stores text, images, or external files
ROWID	Stores physical row address
NCLOB	Supports multilingual data

PL/SQL Overview

PL/SQL is Oracle's procedural extension to SQL that allows for programming logic and control structures.

Key Features

• Modular programming
• Supports loops, IF statements
• Error handling with EXCEPTION block
• Tightly integrated with SQL

Block Structure

DECLARE
BEGIN
  -- code
EXCEPTION
END;

Examples

Print Message:

BEGIN
  DBMS_OUTPUT.PUT_LINE('Welcome to PL/SQL');
END;

Using Constants:

DECLARE
  PI CONSTANT NUMBER(5,3) := 3.142;
  radius NUMBER := 5;
  area NUMBER;
BEGIN
  area := PI * radius * radius;
  DBMS_OUTPUT.PUT_LINE('Area: ' || area);
END;

Bind Variables

Allow runtime input for flexible programs.

DECLARE
  A NUMBER := &n;
  B NUMBER := &m;
BEGIN
  DBMS_OUTPUT.PUT_LINE(A + B);
END;

%TYPE and %ROWTYPE

%TYPE: Assigns data type from table column to variable.

vname emp.ename%TYPE;

%ROWTYPE: Assigns all column types from a table.

vrow emp%ROWTYPE;

Nested Blocks

BEGIN
  DECLARE
    v_inner NUMBER := 10;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(v_inner);
  END;
END;

Lexical Units in PL/SQL

• Identifiers: Names of variables, tables, etc.
• Reserved Words: BEGIN, END, etc.
• Delimiters: Special characters like `;`
• Literals: Fixed values like 'Hello', 123
• Comments: -- or /* */

``` --- Would you like this in a downloadable `.html` file? I can provide it directly.

String Functions with example in SQL ,PLSQL

           -:        String functions in plsql               :--

PL/SQL offers the concatenation operator (||) for joining two strings. The following table provides the string functions provided by PL/SQL .

 

S.No Function & Purpose    
1 ASCII(x);Returns the ASCII value of the character x.    

2 CHR(x); Returns the character with the ASCII value of x.    

3 CONCAT(x, y);Concatenates the strings x and y and returns the appended string.    

4 INITCAP(x);Converts the initial letter of each word in x to uppercase and returns that string.    

5 INSTR(x, find_string [, start] [, occurrence]);Searches for find_string in x and returns the position          at which it occurs.    

7 LENGTH(x);Returns the number of characters in x.    

8 LENGTHB(x);Returns the length of a character string in bytes for single byte character set.    

9 LOWER(x);Converts the letters in x to lowercase and returns that string.    

10 LPAD(x, width [, pad_string]) ;Pads x with spaces to the left, to bring the total length of the string          up to width characters.    

11 LTRIM(x [, trim_string]);Trims characters from the left of x.    

17 NVL(x, value);Returns value if x is null; otherwise, x is returned.    

19 REPLACE(x, search_string, replace_string);Searches x for search_string and replaces it with                    replace_string.    

20 RPAD(x, width [, pad_string]);Pads x to the right.    

21 RTRIM(x [, trim_string]);Trims x from the right.    

22 SOUNDEX(x) ;Returns a string containing the phonetic representation of x.    

25 TRIM([trim_char FROM) x);Trims characters from the left and right of x.    

26 UPPER(x);Converts the letters in x to uppercase and returns that string.  

e.g. 1 

set serveroutput on;

  DECLARE 

   v_greet varchar2(100) := 'Good Morning !!!'; 

BEGIN 

   dbms_output.put_line(UPPER(v_greet)); 

    

   dbms_output.put_line(LOWER(v_greet)); 

    

   dbms_output.put_line(INITCAP(v_greet)); 

    

   /* retrieve the first character in the string */ 

   dbms_output.put_line ( SUBSTR (v_greet, 1, 1)); 

    

   /* retrieve the last character in the string */ 

   dbms_output.put_line ( SUBSTR (v_greet, -1, 1)); 

    

   /* retrieve five characters,  

      starting from the seventh position. */ 

   dbms_output.put_line ( SUBSTR (v_greet, 7, 5)); 

    

   /* retrieve the remainder of the string, 

      starting from the second position. */ 

   dbms_output.put_line ( SUBSTR (v_greet, 2)); 

     

   /* find the location of the first "e" */ 

   dbms_output.put_line ( INSTR (v_greet, 'e')); 

END; 

/

E.g 2 :

DECLARE 

   VGREET varchar2(30) := '......Hello World.....'; 

BEGIN 

   dbms_output.put_line(RTRIM(VGREET,'.')); 

dbms_output.put_line(LTRIM(VGREET, '.')); 

dbms_output.put_line(TRIM( '.'from VGREET)); 

END;

Performance Tuning Tips in Oracle

 TIPS FOR PERFORMANCE TUNING 

1. try to Analyze cost of the query using explain plan

2. using explain plan try to find out the cost of query and find out which join is taking more cost , that is taking more time , so try to rewrite that join or expression.

3. Try to use exists () , instead of in () / like expression.

4. try to Add indexes on the id columns

5. If creating indexes doesnot work then try creating functional index which is more of related to a expression of a query join

6. try to use partition by clause.

7. Try to avoid the full table scans.