How to design a SQL project about tender management system?

Step 1: Tender Architecture Design

Step 2: Create Table Script

Oracle default Aggregate Function Script

Concatenate string : LISTAGG Analytic Function in 11g Release 2
The LISTAGG analytic function was introduced in Oracle 11g Release 2,  default aggregate strings.

Example 1:
SELECT LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp;

EMPLOYEES
---------- --------------------------------------------------
CLARK,KING,MILLER,ADAMS,FORD,JONES,SCOTT,SMITH,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD


Example 2:
Select LISTAGG(gm.attribute2, ',') WITHIN GROUP (ORDER BY gm.attribute2) 
from gme.gme_material_details gm



Example 3:
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO   EMPLOYEES
---------- --------------------------------------------------
        10            CLARK,KING,MILLER
        20             ADAMS,FORD,JONES,SCOTT,SMITH
        30            ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Oracle User Defined Aggregate Function Script

The WM_CONCAT function and LISTAGG function if not supported then  you can create your own user defined aggregate function as below:

 

-----------------------
CREATE OR REPLACE TYPE xxtex_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  xxtex_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  xxtex_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   xxtex_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  xxtex_string_agg,
                                     ctx2  IN      xxtex_string_agg)
    RETURN NUMBER
);


------------------------------
CREATE OR REPLACE TYPE BODY xxtex_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  xxtex_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := xxtex_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  xxtex_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   xxtex_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  xxtex_string_agg,
                                     ctx2  IN      xxtex_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;

---------------------------------------
CREATE OR REPLACE FUNCTION xxtex_string_agg_f (p_input VARCHAR2)
   RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
   USING xxtex_string_agg;

------------------------------using in select statement -----------------
SELECT  xxtex_string_agg_f(distinct pa.name) AS project_name
FROM po_distributions_all po_dist,pa_projects_all pa
     WHERE po_dist.po_header_id= :po_header_id
           AND po_dist.project_id=pa.project_id;

Return 91234,92345,93456,94567,95678

Materialized views Example

SET DEFINE OFF;
DROP SYNONYM APPSRO.EMP_SNAPSHOT;


DROP MATERIALIZED VIEW SCOTT.EMP_SNAPSHOT;

CREATE MATERIALIZED VIEW SCOTT.EMP_SNAPSHOT
TABLESPACE SYSTEM
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
            TABLESPACE SYSTEM
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
AS
SELECT "EMP"."EMPNO" "EMPNO","EMP"."ENAME" "ENAME","EMP"."JOB" "JOB","EMP"."MGR" "MGR","EMP"."HIREDATE" "HIREDATE","EMP"."SAL" "SAL","EMP"."COMM" "COMM","EMP"."DEPTNO" "DEPTNO" FROM "SCOTT"."EMP" "EMP";

GRANT SELECT ON  SCOTT.EMP_SNAPSHOT TO APPSRO;

GRANT SELECT ON  SCOTT.EMP_SNAPSHOT TO PUBLIC;

CREATE SYNONYM APPSRO.EMP_SNAPSHOT FOR SCOTT.EMP_SNAPSHOT;

How many Colummn in a table?

SELECT column_name, data_type, data_length, data_precision, nullable
FROM all_tab_cols
WHERE table_name ='RA_CUSTOMER_TRX_ALL';

YTD Opening Date Wise

SELECT CASE
          WHEN TO_DATE (:psdate) <
                             TO_DATE ('01-APR-' || SUBSTR (:psdate, 8, 4))
             THEN    '01-APR-'
                  || TO_CHAR (TO_NUMBER (SUBSTR (:psdate, 8, 4)) - 1)
          ELSE '01-APR-' || SUBSTR (:psdate, 8, 4)
       END CURRENT_YTD
FROM DUAL;

SQL statement of syntax or Basic SQL

What is a primary key?

A primary key is a single field or combination of fields that uniquely defines a record. None of the fields that are part of the primary key can contain a null value. A table can have only one primary key.

The syntax for creating a primary key using a CREATE TABLE statement is:

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...
  
  CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)
);

ALTER TABLE table_name
add CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n);

What is a foreign key?

A foreign key means that values in one table must also appear in another table.
The referenced table is called the parent table while the table with the foreign key is called the child table. The foreign key in the child table will generally reference a primary key in the parent table.

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT fk_column
    FOREIGN KEY (column1, column2, ... column_n)
    REFERENCES parent_table (column1, column2, ... column_n)
);

ALTER TABLE table_name
disable CONSTRAINT constraint_name;

ALTER TABLE table_name
enable CONSTRAINT constraint_name;

What is a unique constraint?

A unique constraint is a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long as the combination of values is unique.

CREATE TABLE table_name
(
  column1 datatype null/not null,
  column2 datatype null/not null,
  ...

  CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n)
);

ALTER TABLE table_name
add CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);

Note:

In Oracle, a unique constraint can not contain more than 32 columns.
A unique constraint can be defined in either a CREATE TABLE statement or an ALTER TABLE statement.

What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, ... column_n)
  [ COMPUTE STATISTICS ];

 Rename Index

ALTER INDEX index_name
  RENAME TO new_index_name;

 Rebuild :If you forgot to collect statistics on the index when you first created 
it or you want to update the statistics, you can always use the ALTER 
INDEX command to collect statistics at a later date.

ALTER INDEX index_name
  REBUILD COMPUTE STATISTICS; 

DROP INDEX index_name;

SQL: SELECT Statement

The SQL SELECT statement allows you to retrieve records from one or more tables in your SQL database.
 syntax:
SELECT columns FROM tables WHERE predicates;

SQL: INSERT Statement

The SQL INSERT statement allows you to insert a single record or multiple records into a table.

Steps of insert data into table using both the insert commands

 syntax:

INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);

  insert multiple rows of explicit data in one SQL command

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

 

INSERT INTO suppliers (supplier_id, supplier_name) 
  SELECT ss.supplier_id, ss.supplier_name FROM xx_suppliers_stg ss;

SQL: UPDATE Statement

The SQL UPDATE statement allows you to update a single record or multiple records in a table.

UPDATE table
SET column = expression
WHERE predicates;

UPDATE suppliers
SET supplier_name = (SELECT customers.name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

SQL: DELETE Statement

The SQL DELETE statement allows you to delete a single record or multiple records from a table.

DELETE FROM table

WHERE predicates;

DELETE FROM TableA
WHERE NOT EXISTS
  ( select *
    from TableB
     where TableA.field1 = TableB.fieldx
     and TableA.field2 = TableB.fieldz ); 

SQL: ALTER Statement
ALTER PACKAGE [schema.]package_name COMPILE [DEBUG PACKAGE|SPECIFICATION|BODY];

ALTER PACKAGE APPS.AD_ZD_SEED COMPILE BODY

Fibbonacci Series

Declare
x number;
y number;
z number;
ctr number;
q number;

begin
q:=&q;
x:=0;
y:=1;
ctr :=2;
dbms_output.put_line('........FIBBONACCI SERIES........'||sysdate);
dbms_output.put_line(x);
dbms_output.put_line(y);
for ctr in 1..q
loop
z:=x+y;
dbms_output.put_line(z);
x:=y;
y:=z;
end loop;
end;