CLASS PRACTICALS

More Memory/ Out of Spool / Memory Out of Bounds exception

Query tuning is required. Some tips for query tuning

a)      Eliminating cross join

b)      Eliminating correlated sub queries

c)       Using Group by than Distinct

d)      Use EXISTS than IN

e)      Use Operator than function (Ex: +,Concat   for concatenation.Recommended is +)

f)       Reduce analytical queries usage

g)      Use indexed columns effectively

h)      Eliminate ambiguous conditions and wrong cols match

i)        Avoid inequality comparison  operators during queries

 

Etc…

 

SKEWNESS ISSUE AND REMIDY

Uneven distribution (or) less even distribution is called skewness. To identify the skewness we can use hash functions (or) skew factor formula.

Situations:

Usually in case of NUPI it happens

 

WORKING WITH SKEWFACTOR FOR TABLE LEVEL SKEWNESS :-

 

It tells the distribution of rows, if the skewfactor is zero means 100% parallelism is there. If the skewfactor is 10%, it means system is running 90% parallel (till 10% skew factor is acceptable).

 

QUERY TO IDENTIFY THE SKEW FACTOR:-

Select

Tablename,

sum(currentperm) As Currentperm,

sum (peakperm) As Peakperm,

100- (Avg (currentperm)/max(currentperm)*100) As skewfactor

from DBC.tablesize

where databasename = 'vinaytech' group by 1 order by 1

 

Remedy:-

For eliminating the skew factor we need to change the primary index.

That means if the PI is on a single column create on multiple columns (composite).

 

 

 

DATA REDISTRIBUTION, MORE SPOOL ISSUES AT JOINS

 

REDISTRIBUTION:

1.       AT THE BEGINNING THERE WILL BE A DISTRIBUTION ON THE TABLE.

2.       AT THE TIME OF JOIN,IF SYSTEM USES OTHER COLS TO DISTRIBUTE  FOR JOIN OPERATIONS,

THEN IT IS REDISTRIBUTION.

 

PROBLEM WITH REDISTRIBUTION

MORE TIME, MORE SOOL

 

ELIMINATE

IDENTIFY JOIN STRATEGIES AND TAKE APPROPRIATE JOIN/HASH INDICES

 

JOIN STRATGEY

INTERNAL PLAN BY THE SYSTEM FOR JOIN OPERATIONS.

 

A)     PRODUCT JOIN

B)      MERGE JOIN

C)      NESTED JOIN

D)     ROW HASH JOIN

ETC…

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

PRODUCT JOIN

1.       VERY DANGEROUS JOIN STRATEGY

(ONE TABLE DATA DUPLICATED ACROSS ALL AMPS FOR JOIN, SO LEADS TO ADDITIONAL SPOOL WITH MORE MEMORY AND MORE TIME TO OPERATE)

 

SITUATION:

A)     EXPLICIT CROSS JOIN KEYWORD (CROSS JOIN—TYPE,PRODUCT JOIN—STRATEGY)

B)      CORRELATED SUBQUERIES

C)      WRONG JOIN CONDITION (EID=DID),

D)     INEQUALITY OPERATORS( E.EID<>100 AND E.ENM=EA.ENM)

 

Etc..

 explain sel e.*, d.* from emp e

cross join dept d

 

 

REMIDY

AVOID THE ABOVE SPECIFIED SITUATIONS

MERGE JOIN (CASE1: INDEXED COLS JOIN)

IT IS GOOD FOR PERFORMANCE BECAUSE WITH IN THE SAME AMP JOIN HAPPENS.

NO REDISTRIBUTION, NO SORTING AND NO HECTIC JOIN OPERATION

 

EXPLAIN SEL E.*,EA.* FROM EMP E

INNER JOIN EMP_ADDRESS EA ON E.EID=EA.EMPID

 

Here EID and EMPID are PI cols

 

 

MERGE JOIN (CASE1: INDEXED COL +  Non indexed column JOIN)

It cause some memory and time issues because of

 

a)      REDISTRIBUTION on non indexed cols

b)      SORTING

c)       Additional memory for Join operation

explain

sel e.*, d.* from emp e

inner join dept d

on e.deptid=d.did

--Here deptid is non indexed col and did is indexed col

 

Solution to avoid the issues ion this situation

Take Join / Hash index on emp.deptid column

 

NESTED JOIN INDEX

One table UPI evaluating against a constant value and the same table non indexed col join with indexed col then this strategy occurs.

 

It is smarter than all all other join strategies.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Conclusion

Product Join require immediate action

Merge join (indexed and non indexed) required action.

Merge join (both indexed), nested join—No need to take action.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Join and hash indexes

Advantage at the time of join operations:

a)      Avoids redistribution

b)      Avoids sorting

c)       Avoids main table burden

d)      Avoids manual refresh

e)      Avoid hectic join with more spool memory
etc…

Multiple types, each one advantage in a peculiar situation.

Single table JI—One table FK is used by multiple tables

Multi table JI—Multi table join used many times

Spare JI—Required multi table joined data used for many times

Aggregate JI—Aggregate result multi usage

 

 

 

 

 

 

 

 

 

INDICES OBSERVATION IN A PARTICULAR DATABASE AND TABLE

-----------------------------------------------------------

SEL * FROM DBC.INDICES WHERE DATABASENAME='VINAYAKA' AND TABLENAME='DEPT'

 

BEFORE JOIN INDEXES

---------------------

EXPLAIN SEL EID,ENAME,DEPTID FROM EMP WHERE DEPTID=20--ALL AMP OPERATION

EXPLAIN

SELECT E.EID,E.ENAME,E.DEPTID FROM EMP E

INNER JOIN DEPT D

ON E.DEPTID=D.DEPTID --ROW DISTRIBUTION,SORTING  AND MERGE JOIN STRATEGY

 

AFTER JOIN INDEXES

==================

DROP JOIN INDEX  IDX;

SYN:

CREATE JOIN INDEX <INDEXNAME> AS <SEL QUERY> PRIMARY INDEX(COLS)

EX:

CREATE JOIN INDEX IDX  AS SEL EID,ENAME,DEPTID FROM EMP PRIMARY INDEX(DEPTID);

SHOW JOIN INDEX IDX;

 

NOTE: IF REQUIRED COLLECT STATS ON THE TABLES

                                                --COLLECT STATS ON EMP INDEX(EID)

                                                --COLLECT STATS ON DEPT INDEX(DEPTID)

EXPLAIN

SELECT E.EID,E.ENAME,E.DEPTID FROM EMP E

INNER JOIN DEPT D

ON E.DEPTID=D.DEPTID --NO ROW DISTRIBUTION,MERGE JOIN

 

EXPLAIN SEL EID,ENAME,DEPTID FROM EMP WHERE DEPTID=20 --SINGLE AMP OPERATION

 

HASH INDEX

 

HERE SUBTABLE CREATED WITH REQUIRED ROWS AND COLS FROM MAIN TABLE BASED ON HASH VALUES.

THIS SUB TABLE COMES INTO THE PICTIRE AT THE TIME OF JOIN OPERATIONS.

 

THIS IS SIMILAR TO SINGLE TABLE JI, BUT THE COLS USED IS DIFFERENT.

SJI—ON FK VALUE

HI—ON PI VALUES

 

 

 

 

 

 

 

 

 

 

CREATING A TABLE FROM ANOTHER TABLE:
------------------------------------
1st way
.CREATE TABLE <DATABASENAME>.<NEW TABLENAME> AS  <DBNAME>.<OLDTABLENAME> WITH DATA   ----WITH DATA

2nd way
.CREATE TABLE <DATABASENAME>.<NEW TABLENAME> AS <DBNAME>.<OLDTABLENAME> WITH NO DATA --WITH NO DATA WITH ONLY STRTUCTURE

3rd way
 CREATE TABLE <NEW TABLE> AS (SELECT QUERY) WITH [NO] DATA

EG: CREATE TABLE PARTY1 AS PARTY WITH DATA
CREATE TABLE PARTY1 AS PARTY WITH NO DATA
    CREATE TABLE PARTY1 AS (SELECT * FROM PARTY) WITH DATA

CREATING TABLE WITH STATISTICS  FROM OTHER TABLE:
----------------------------------------------------
CREATE TABLE NEW AS OLD WITH DATA AND STATISTICS;
CREATE TABLE NEW AS OLD WITH NO DATA AND STATISTICS;
CREATE TABLE NEW AS (SELECT * FROM OLD) WITH DATA AND STATISTICS;

DDL CONSTRUCT (ALTER COMMAND syntaxes:-------)
====================================================
MODIFY TABLE STRUCTURE BY ADDING/MODIFYING/REMOVING COLUMNS,DATA TYPES,
CONSTRAINTS AND OTHER OPTIONS.

ADDING COLUMN:
SYNTAX: ALTER TABLE <TABLENAME> ADD <COLUMNNAME> <DATATYPE>
 EG: ALTER TABLE PARTY ADD JDATE DATE

DROPING COLUMN:
SYNTAX: ALTER TABLE <TABLENAME> DROP <COLUMNNAME>
 EG: ALTER TABLE PARTY DROP JDATE

INCREASING DATATYPE LENGH:
SYNTAX:ALTER TABLE <TABLENAME> ADD COLUMNNAME DATATYPE<NEWLENGTH>
EG: ALTER TABLE PARTY ADD PARTYNAME VARCHAR(50)

ADDING CONSTRAINT:

SYNTAX: ALTER TABLE <TABLENAME> ADD CONSTRAINT <CONSTRAINTAME> PRIMARY/UNIQUE/CHECK
/REFERENCES(COLUMNS OR EXPRESSION)
 EG:ALTER TABLE PARTY ADD CONSTRAINT CHK_SAL CHECK (PARTYSAL>100000)
Note: to add primary key or unique the column should have not null defined on it.
      EG:   ALTER TABLE PARTY ADD CONSTRAINT uk  UNIQUE(PARTYNAME)

MODIFYING CONSTRAINT:

SYNTAX: ALTER TABLE <TABLENAME> MODIFY CONSTRAINT <CONSTRAINTAME> PRIMARY/UNIQUE
/CHECK/REFERENCES
 EG:ALTER TABLE PARTY MODIFY CONSTRAINT CHK_SAL
 CHECK (PARTYSAL>100000 AND PARTYSAL<200000)

 DROPING CONSTRAINT:
 SYNTAX:ALTER TABLE <TABLENAME> DROP CONSTRAINT <CONSTRAINTNAME>
 EG: ALTER TABLE PARTY DROP CONSTRAINT CHK_SAL

NO FALLBACK TO FALLBACK:
 SYNTAX: ALTER TABLE <TABLENAME>,FALLBACK
 EG: ALTER TABLE PARTY,FALLBACK

 CHANGING DATA BLOCKSIZE:
 SYNTAX:ALTER TABLE <TABLENAME>, DATABLOCKSIZE=<MEMORY>BYTES IMMEDIATE
 EG: ALTER TABLE <TABLENAME>, DATABLOCKSIZE=1200 BYTES IMMEDIATE

 RENAMING THE COLUMN:
 SYNTAX: ALTER TABLE <TABLENAME> RENAME <OLDNAME>TO <NEWNAME>
  EG: ALTER TABLE PARTY RENAME PARTYCD TO PARTYCODE

RENAMING A TABLE:
SYN:  RENAME TABLE <OLDTABLE> TO <NEW TABLE>
EG: RENAME TABLE PARTY TO PARTY_NEW

ADDING COMPRESS:
ALTER TABLE PARTY ADD PARTYCODE COMPRESS NULL;
ALTER TABLE PARTY ADD ACCOUNTTYPE COMPRESS 'sAVINGS';
ALTER TABLE PARTY ADD ACCOUNTTYPE COMPRESS 'sAVINGS','Current';

DDL CONSTRUCT (DROP COMMAND)
=============================
REMOVE OBJECTS FROM DATA DICTIONARY
IT IS FOR ALL OBJECTS (TABLES/VIEWS/MACROS ETC...)
NO ROLL BACK OPTION (OBJECT REMOVED, YOU CAN'T GET BACK)
NO CONDITIONAL CLAUSES(NO CONDITIONAL REMOVAL)
NOTE: INCASE OF TABLE, STRUCTURE AND ROWS REMOVED.
      INCASE OF PROCEDURE, PROCEDURE STRUCTURE AND PROCEDURAL TABLE IS REMOVED.


SYN: DROP <OBJECT> <OBJECTNAME>
EX: DROP TABLE <TABLENAME>
    DROP PROCEDURE <PROCEDURENAME>
    DROP MACRO <MACRONAME>
    DROP VIEW <VIEWNAME>
    DROP TRIGGER <TRIGGERNAME>
    ETC...

DML--DATA MANIPULATION LANGUAGE

IT DEALS WITH "DATA" ONLY (NO STRUCTURE AFFECT).
OPERATIONS ARE ADD/REMOVE/MODIFY/RETRIEVE ROWS/COLUMNS.
DML COMMANDS APPLIED ON TABLES AND VIEWS
SOME COMMANDS OPERATE COLUMN WISE AND SOME OPERATE ROW WISE.


COMMANDS:

COLUMN WISE OPERATION COMMANDS:
===============================

SELECT (RETRIEVE) --SEL -- "RETRIEVE COLUMNS" FROM TABLES /VIEWS
UPDATE (MODIFY) --UPD--"MODIFY COLUMN CELL VALUES" IN A TABLE/VIEW

ROW WISE OPERATION COMMANDS:
==============================

INSERT (ADDING) --INS--"ADDING ROWS" TO THE TABLES/VIEWS
DELETE (REMOVAL)--DEL --"REMOVE ROWS" FROM TABLES/VIEWS

COLUMN AND ROW OPERATION COMMANDS
================================

UPSERT --UPDATE IF THE ROW EXISTS OTHERWISE INSERTS
MERGE -- BASED ON CONDITION IT INSERTS/ UPDATES



SQL FILTERS
============
There are different filters to "FILTER COLUMN CELL" Values

DML FILTER-- WHERE
RANK,ROW_NUMBER FILTER-- QUALIFY
GROUP FILTER--HAVING
JOIN FILTER--ON

FILTER SYNTAX:

FILTER_OPERATOR       <ARGUMENT1> <OPERATOR> <ARGUMENT2>

OPERATOR DECIDED BASED ON EITHER OF THE ARGUMENT VALUES.
IF EITHER OF THE ARGUMENT RETURN SINGLE VALUE -->COMPARISION OPERATOR(<,<=,>,>=,<>

ETC...)


IF EITHER OF THE ARGUMENT RETURN MULTIPLE VALUES -->CONDITIONAL EXPRESSIONS(IN,NOT

IN,EXISTS,NOT EXISTS)

EX:
WHERE PARTYLOC='HYD'
WHERE PARTYINCOME>30000
WHERE PARTYID IN (2,3,4) --MULTIPLE VALUES





DML CONSTRUCT (INSERT)
======================
1ST WAY
---------
INSERT COMPLETE ROW
--------------------------
SY:
A)INSERT INTO <TABLENAME> (COLUMN VALUES SEPERATED WITH COMMAS)
B)INSERT INTO <TABLENAME> VALUES(COLUMN VALUES SEPERATED WITH COMMAS)

2ND WAY
-------------
INSERT REQUIRED COLUMNS OF A ROW
-------------------------------------
INSERT INTO <TABLENAME> (COLUMNNAMES) VALUES(VALUES SEPERATED WITH COMMAS)

3RD WAY
---------

INSERT FROM ANOTHER TABLE
------------------------------
INSERT INTO <TNM/VIEW NM> (SELECT QUERY)

DML CONSTRUCT (UPDATE)
=======================
1ST
-----
UPDATE COMPLETE COLUMN DATA

SYN: UPDATE <TNM/VNM> SET <COLNAME>=<VALUE>

2ND WAY
---------
UPDATE REQUIRED ROWS OF A COLUMN

SYN:

SYN: UPDATE <TNM/VNM> SET <COLNAME>=<VALUE> WHERE <CONDITION>


3RD WAY
------------
UPDATE FROM ANOTHER TABLE


SYN: UPDATE <TAB2> SET <COLNAME>=<TAB1.COLUMN> WHERE <TABL1.COL>=<TAB2.COL>

CASE STUDY 1:

CREATE
=======
create table s1(tid integer,tnm varchar(30))
create table s2 as s1 with no data



insert (3 ways)
------
ins s1(1,'xxx') --complete row insertion
ins s1(tid) values(2) --required cols of row insertion
insert into s2 sel * from s1--inserting from another table
sel * from s1
sel * from s2

update (3 ways)
----------------
update s1 set tnm='vinaytech'--complete col updation
update s1 set tnm='vinay' where tid=2--required rows of col updation
update s2 set tnm=s1.tnm where s1.tid=s2.tid--updating from another table
sel * from s1
sel * from s2

======

CASE STUDY 2:

INS S10(1,'XX')--COMPLETE ROW INSERTION
INS S10(TID) VALUES(2)--ADDING ROW WITH ONLY  REQUIRED COLUMN VALUES
CREATE TABLE S20 AS S10 WITH NO DATA
INS S20 SEL * FROM S10 --INSERTING ROWS FROM ANOTHER TABLE
============
UPD S10 SET TNM='VINAYTECH' --COMPLETE COLUMN UPDATION
UPD S10 SET TNM='VINAY' WHERE TID=2--REQUIRED ROWS OF COLUMN UPDATION
UPD S20 SET TNM=S10.TNM WHERE S10.TID=S20.TID--UPDATING COLUMN VALUES FROM ANOTHER

TABLE

DELETE COMMAND (3 WAYS)
========================
1. ALL ROWS DELETION

A) SLOWER DELETION

SYN: DELETE FROM <TABLENAME>
EX: DELETE FROM S1

B) FASTER DELETION

SYN: DELETE FROM <TABLENAME> ALL OR DEL<TABLENAME>
EX: DEL S1
DEL FROM S1 ALL

NOTE: DELETE ALL WORKS LIKE TRUNCATE IN ORACLE ,BUT IT HAS BEAUTIFUL
PERFORMANCE FEATURES.

2. REQUIRED ROWS DELETION

SYN: DELETE FROM <TABLENAME> WHERE <CONDITION>
EX: DELETE FROM S1 WHERE TID<30;

UPSERT COMMAND:
================
UPDATE ELSE INSERT
EX:
INSERT A ROW WITH VALUES IF THE RESPECTIVE ID DOES NOT HAVE A MATCH

UPDATE PARTY SET PARTYNAME='VINAY' WHERE PARTYID=1 ELSE INSERT PARTY(1,'VINAY')
-========================

MERGE COMMAND:
================
MULTI DML OPERATION COMMAND. PERFORMS SPECIFIED OPERATION BASED ON CONDITION.
GENERALLY UPDATE OR INSERT OPERATION WE USE THIS.

MERGE INTO PARTY USING VALUES(1,'VENKATESH',30) AS D(PID,PNM,PCD)
ON D.PID=PARTYID
WHEN MATCHED THEN
UPDATE  SET PARTYNM=D.PNM
WHEN NOT MATCHED THEN
INSERT VALUES(D.PID,D.PNM,D.PCD)

NOTE: HERE 'D' IS DERIVED / INLINE TABLE

DCL CONSTRUCT
================
SPECIALLY DESIGNED FOR DATA AND STRUCTURE CONTROLLING (DDL,DML CONTROL).
SIMPLY TO PROVIDE,REMOVE OR TRANSFER PRIVILEGES.

GRANT--> PROVIDE PRIVILEGES
REVOKE-->REMOVES PRIVILEGES
GIVE-->TRANSFER OWNER SHIP TO OTHER USER

EX:  GRANT SELECT,INSERT,UPDATE,DELETE ON VINAYAKA.PARTY TO USER_7AM

     REVOKE SELECT,INSERT ON VINAYAKA.PARTY FROM USER_7AM
     REVOKE ALL BUT SELECT ON VINAYAKA.PARTY FROM USER_7AM

GIVE-->TRANSFER DATABASES/USERS TO OTHER USERS/DATABASES
GIVE DB2 TO USER 2

FAQS1:
1. DIFFERENCE BETWEEN DROP AND DELETE
2. WHY DELETE ALL RUNS FASTER THAN NORMAL DELETE?
3. WHY INSERT INTO SELECT RUNS FASTER THAN ALL ETL FACILITIES?
4. I HAVE A TABLE WITH 100 RECORDS, I WANT TO ADD A NEW COLUMN AND ADD
    RECORD VALUES IN TO THAT. HOW DO YOU PERFORM THIS?



WORKING WITH SELECT STATEMENT
=============================
REAL TIME USAGES
-----------------
1)THE MOST IMPORTANT STATEMENT IN DWH AND BI LEVEL IS SELECT.
2)DWH NON VOLATILE (ONLY READ),SELECT IS BEST SUITED.
3)ANALYSIS TIME,REPORTING TIME (EITHER DIRECT SELECT OR SELECT INCLUDED

VIEW/PROC/MACRO/FUNCTION)
4) HELPS TO IMPLEMENT
A) AGGREGATES (SUM,AVG,COUNT,MAX,MIN ETC...)
B) CLASSFICATIONS (GROUPS)
C) FILTERING (UPPER,LOWER,TRIM,CONCATINATING ETC...)
D)* ANALYTICAL/REPORT WAY DATA DISPLAY



HOW BEST WE CAN WRITE:
=======================
READ THE BUSINESS DESCRIPTION THROUGHLY AND IDENTIFY THE BELOW 5 FACTORS AND PLACE

IN THE SELECT SYNTAX:

1. COLUMNS TO BE RETURNED FROM TABLE/TABLES (DISPLAY COLUMNS)
2. COLUMN CELL VALUES TO BE FILTERED (FOR REQUIRED ROWS)
3. CLASSFICIATION COLUMNS (GROUPS)
4. CLASSFICATION FILTERING (REQUIRED GROUPS)
5. ORDERING COLUMNS (TYPE OF ORDER)


----------------------------------
SELECT 1 FROM <TABLE/VIEW>
WHERE 2
GROUP BY 3
HAVING 4
ORDER BY 5

syntax: SELECT(OR)SEL  COLUMNS/* FROM <TABLENAME/VIEWNAME>---retrieving COLUMNS

WHERE <CONDITION OR SUB QUERY> --- FITERS COLUMN CELL VALUES

GROUP BY <COLUMNS OR EXPRESSION> OR <NUMERICS> --KEEPS IN GROUPS

HAVING <CONDITION OR SUB QUERY> ----FILTERS GROUPING DATA

ORDER BY <COLUMNNAMES> OR <NUMERICS> ASC/DESC --SORTS THE RETRIEVED DATA


----------------------------------
SELECT STATEMENT 1ST SECTION (ALL COLS/REQUIRED COLS)
-------------------------------------------------------------------------------

SELECT * FROM PARTY -- ALL COLS FROM PARTY TABLE/VIEW

SELECT PID,PNM FROM PARTY --TWO COLS FROM ...

SEL PID,PNM FROM PARTY WHERE PID>100 -- TWO COLS FROM PARTY BY ROW FILTER OF ID>100

ALIAS:

1.ALTERNATIVE NAME/ SHORTCUT/REFERENCE
2. IN TERADATA THERE ARE TWO TYPES OF ALIASES

A) COLUMN ALIAS--

ACTS LIKE ALTERNATIVE TITLE AND WE CAN USE THAT TITLE FOR FILTERING

(EXCLUSIVE FEATURE OF TERADATA)

SELECT PARTYINCOME AS PI FROM PARTY WHERE PI>20000

--WORKS ONLY IN TD,OTHER DB DOESN'T SUPPORT (ORACLE,SQLSERVER ...)

B) TABLE ALIAS --FOR SHORTCUT/REFERENCE (FOR LENGTHY TABLE NAMES IT IS HELPFUL)

SEL E.*,D.DID,D.DNM FROM EMP E,DEPT D

--EMPLOYEE ALL COLUMNS AND DEPT TWO COLS

----------------------------------
SELECT STATEMENT 2ND SECTION (WHERE CLAUSE FOR ROW FILTERING)
---------------------------------------------------------------

SYN:

WHERE ARGUMENT1 OPERATOR ARGUMENT2

ARGUMENT--EXPRESSION/VALUE/COLUMN

OPERATOR:

A) SUINGLE VALUE MATCH --COMPARISON OPERATORS (=,<,>,>=,<=,<>,!=)
B) MULTI VALUE MATCH-- IN,NOT IN,EXISTS,NOT EXISTS ETC...(CONDITIONAL EXPRESSIONS)

COMPARISION OPERATOR EXCERCISES:
--------------------------------

DISPLAY PARTY INFO WHOSE INCOME MORE THAN 20000
ANS:
THIS QUERY HAS TWO PARTS
1. PARTY.*
2. PARTYINCOME>20000

SEL PARTY.* FROM PARTY
WHERE PARTYINCOME>20000

DISPLAY ID AND NAME OF CUSTOMERS WHO BELONG TO DEPT 10
AND LOCATION IS HYDERBAD
IT HAS TWO PARTYS
1. PARTYID,PARTYNAME
2. PARTYCODE=10 AND PARTYLOC='HYD'
SEL PARTYID,PARTYNAME FROM PARTY
WHERE PARTYCODE=10 AND PARTYLOC='HYD'


CONDITIONAL EXPRESSIONS:
------------------------

IN: IT SEARCHES WITH IN THE LIST OF VALUES PROVIDED.AVIODS MULTI 'OR'ING
---
NOT IN: IT SEARCHES NOT  IN THE LIST OF VALUES PROVIDED
------
EG:
DISPLAY EMPLOYEES WHOSE IDS ARE 2,3,9

SELECT * FROM PARTY WHERE PARTYID IN(2,3,9)--SHORTCUT
SEL * FROM PARTY WHERE PARTYID=2 OR PARTYID=3 OR PARTYID=9--LENGTHY WAY

EXISTS:
--------
CHECKS EXISTENCE OF VALUES.
WORKS ON TRUE /FALSE BASIS.
ALWAYS IMPLEMENTED IN CORRELATED SUB QUERIES.
---
NOT EXISTS: CHECKS NON EXISTENCE OF VALUES
----------
EG:DISPLAY EMPLOYEES WHOSE DEPT IDS VALID IN DEPT TABLE

SELECT * FROM EMP WHERE EXISTS
(SELECT * FROM DEPT WHERE EMP.DEPTID=DEPT.DEPTID)

Process: Each record from emp goes inside and does a comparision with dept table,
if there is a match it would return result and exists consider as true,hence row
would be displayed.If there is no match in the inner query, exists false ignores
the row.

IS NULL OR IS NOT NULL:IT CHECKS WHETHER THE COLUMN
-----------------------
CONTAINS NULL VALUES OR NOT.
EG:
SEL * FROM PARTY WHERE PARTYNAME IS NULL
SEL * FROM PARTY WHERE PARTYNAME IS NOT NULL

NOTE: NULL EVALUATED WITH IS OPERATOR.

BETWEEN AND:IT FETCHES THE DATA BETWEEN THE RANGE PROVIDED (INCLUSIVE)
-----------
EG:
SELECT * FROM PARTY WHERE PARTYINCOME BETWEEN 20000 AND 50000--SHORTCUT

SEL * FROM PARTY WHERE PARTYINCOME<=50000 AND PARTYINCOME>=20000--LONG


LIKE:IT FETCHES A FULL STRING FROM PARTIAL STRING(FUZZY MATCH/ SIMILARITY MATCH).
-----
SIMILARITY MATCH ON STRING VALUES. (NUMERICS AND DATES NOT SUPPORTED)

IT USES TWO NORMAL CHARS AS WILD CARD CHARACTERS( WILD INDICATE A NORMAL CHARACTER
WITH DIFFERENT BEHAVIOUR).INCASE YOU WANT TO TREAT AS NORMAL CHAR,THEN
ESCAPE FUNCTIONALITY SHOULD BE USED.

A. % (PERCENTAGE) -->Any character,any number of characters
B._ (UNDERSCORE)--> Any character, Single character

OTHER OPERATORS WITH LIKE:
--------------------------
ALL: ALL CNDITIONS TO BE MET
SOME:SOME CONDITIONS TO BE MET
ANY: ANY CONDITION TO BE MET

EG:Display the names which starts with 'S'
SELECT * FROM PARTY WHERE PARTYNAME LIKE 'S%'

EG: DISPLAY NAMES STARTS WITH 'V' OR STARTS WITH 'R'

SEL * FROM PARTY WHERE PARTYNAME LIKE 'V%' OR PARYTNAME LIKE 'R%'--LONG

SELECT * FROM PARTY WHERE PARTYNAME LIKE SOME('V%','R%')--SHORT

DISPLAY NAMES STARTING WITH '%'

SELECT * FROM PARTY WHERE PARTYNAME LIKE 'Z%%' ESCAPE 'Z'
--IMMEDIATE TO ESCAPE CHAR IS NORMAL CHAR

FAQS:

BETWEEN IN and Exists WHICH RUNS FASTER AND WHY?(IN THE SAME SITUATION CONSIDERED)
Ans:
Example of values 10,20,30,20,40,10

In (10)--Scan all the values in the list and fetch matched result

Exists(10) -- Starts from first cell, if there is any match simply stops the

process.


So Exists run faster than IN clause

[Exists gives good performance than IN because exists stops the process once it

finds the MATCH.]

Display names starting with 'v' without using LIKE operator
Ans:

There are two ways

a) Substr method
sel * from party where substr(partyname,1,1)='v'

b) Between and operator

sel * from party where partyname between 'va' and 'vz'



----------------------------------------------------------------
SELECT STATEMENT 3RD SECTION --GROUP BY
----------------------------------------------------------------
A. TO CLASSIFY THE DATA (TO CREATE CATOGERIES)
B. TO IMPLEMENT AGGREGATE OPERATIONS EASILY

ODD RULE--GROUP BY REQUIRE ALL AGGREGATE COLS IN THE SELECT CLAUSE
 MEANS AGGREGATE AND NON AGGREGATE COLS WE CAN'T TAKE IN A SINGLE AREA.

AGGREGATE COLUMN: AGGREGATE FUNCTIONED COLUMN(SUM,AVG,MIN,MAX,COUNT, ETC...) OR

GROUP COLUMN.

SYNTAX:SELECT COL1,COL2....COLN FROM <TABLE/VIEW NAME>
GROUP BY COL1,COL2.... (OR)1,2,3...
HAVING <CONDITION>

EX:
DISPLAY LOCATION WISE SUM AND AVG OF INCOMES
ANS:
SEL PARTYLOC,SUM(PARTYINCOME),AVG(PARTYINCOME) FROM PARTY
GROUP BY PARTYLOC

OR

SEL PARTYLOC,SUM(PARTYINCOME),AVG(PARTYINCOME) FROM PARTY
GROUP BY 1--1ST COLUMN IN THE SELECT CLAUSE

WRONG QUERY
---------------
SEL PARTYLOC,PARTYID,SUM(PARTYINCOME),AVG(PARTYINCOME) FROM PARTY
GROUP BY PARTYLOC --wrong because of PARTYID non aggregate column

CORRECT QUERIES
---------------
TWO WAYS

1ST WAY (TAKING AGGREGATE FUNCTION)
--------
SEL PARTYLOC,MAX(PARTYID),SUM(PARTYINCOME),AVG(PARTYINCOME) FROM PARTY
GROUP BY PARTYLOC

--Location wise max partyid, sum and avg of incomes

2ND WAY (ADDING IN THE GROUP LEVEL)
----------
SEL PARTYLOC,PARTYID,SUM(PARTYINCOME),AVG(PARTYINCOME) FROM PARTY
GROUP BY PARTYLOC,PARTYID

-- Location and Id wise sum and avg of incomes

REAL TIME USAGES OF GROUP BY IN DWH PROJECTS
============================================
A) IDENTIFYING DUPLICATES
B) ELIMINATING DUPLICATES
C) DISPLAY UNIQUE LIST OF VALUES
D) AGGREGATES BASEDON CATEGORY



----------------------------------------------------------------
SELECT STATEMENT 4TH SECTION --HAVING
----------------------------------------------------------------

FILTERS THE DATA OF GROUP BY.TO GET CUSTOMIZED CLASSIFICATIONS THIS IS REQUIRED.

IDENTIFYING DUPLICATES IN A TABLE
===================================

THE BEST AMONG ALL IS GROUP BY AND HAVING CLAUSE



SINGLE COL DUPLICATE
--------------------
SEL COL1 FROM <OBJECTNAME>
GROUP BY COL1
HAVING COUNT(*)>1
EX:
DUPLICATES BASED ON EID

SEL EID FROM EMP
GROUP BY EID
HAVIN COUNT(*)>1

MULTI COLUMN /ALL COLS DUPLICATE
---------------------------------
SEL COL1,COL2...COLN FROM <OBJECT>
GROUP BY COL1,COL2....COLN
HAVING COUNT(*)>1




ELIMINATING DUPLICATES
=======================
INCASE OF ORACLE
--------------------
BY USING ROWID OR ROW_NUMBER WE CAN ELIMINATE IT EASILY.
EX:DELETE THE RECENTLY ADDED DUPLICATES

DELETE FROM emp e1 WHERE e1.rowid > (SELECT MIN(ROWID) FROM emp e2
WHERE e1.PID=e2.PID);

INCASE OF TERADATA
===============
DIRECTLY WE CAN'T USE ROW ID/ROW ID USAGE IS COMPLEX SO WE GO FOR THE BELOW

APPROACHES


1) HAVING ONE TIMESTAMP COLUMN AS UNIQUENESS IN THE TABLE
-----------------------------------------------------------

CREATE TABLE TEMP(TID TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PID INTEGER,PNM VARCHAR(30));

INSERT INTO TEMP(,1,'X');
INSERT INTO TEMP(,2,'Y');
INSERT INTO TEMP(,3,'X');
INSERT INTO TEMP(,1,'Z');
INSERT INTO TEMP(,2,'X');

SEL * FROM TEMP;



DELETE FROM TEMP T WHERE T.TID > (SELECT MIN(TID) FROM TEMP T1
WHERE T.PID=T1.PID);

===
B) INCASE OF COMPLETE ROW DUPLICATES
======================================

CREATE MULTISET  TABLE X(TID INTEGER,TNM VARCHAR(30))
INSERT INTO X(1,'VINAY');
INSERT INTO X(1,'VINAY');
INSERT INTO X(2,'MADHU');
INSERT INTO X(2,'MADHU');
INSERT INTO X(3,'KRISNAY');
SEL * FROM X;
CREATE SET  TABLE Y(TID INTEGER,TNM VARCHAR(30))---
INSERT INTO Y SELECT * FROM X;
DROP TABLE X;
RENAME TABLE  Y TO  X;
SEL * FROM X;

WITHOUT DROP OPTION
1. CREATE AN INTERMEDIATE TABLE AND TAKE DISTINCT DATA FROM MULTISET TABLE
2. DELETE DATA FROM MULTISET BASED ON DUPLICATES
3. LOAD THE INTERMEDIATE TABLE DATA INTO THE ACTUAL TABLE

====
3RD WAY:NO UNIQUENESS COLUMN AND NOT A DUPLICATE
=================================================
CREATE TABLE TEMP1(PID INTEGER,PNM VARCHAR(30));

INSERT INTO TEMP1(1,'X');
INSERT INTO TEMP1(2,'Y');
INSERT INTO TEMP1(3,'X');
INSERT INTO TEMP1(1,'Z');
INSERT INTO TEMP1(2,'X');

SEL * FROM TEMP1;

alter table temp1 add row_no smallint;

insert into temp1(pid,pnm,row_no) sel pid,pnm,2 from temp1
qualify row_number() over(partition by pid order by pid )=2;

delete from temp1 a where a.row_no is null and exists(sel null from temp1 b
where a.pid=b.pid and b.row_no=2)

alter table temp1 drop row_no

sel * from temp1


GROUP BY USING TO IDENTIFY UNIQUE VALUES IN DWH PROJECTS
===========================================================
DISPLAY UNQIUE PARTYIDS

SEL PARTYID FROM PARTY
GROUP BY PARTYID


--------------------------------------------------------------------------------


WORKING WITH SELECT STATEMENT 5TH CONSTRUCT (ORDER BY CLAUSE)
==================================================================
1. SORTS THE DATA IN THE SPECIFIED ORDER(DESC,ASC)
2. DEFAULT ORDER IS ASCENDING
3. SUPPORTS MULTI COLUMN SORTING ORDER

EX:
SYN:
SEL COL1,COL2...COLN / *  FROM <OBJECT>
ORDER BY COLS/ NUMERICS

Q1:DISPLAY PARTY DATA BY KEEPING PARTYID IN DESCENDING ORDER
A;
SEL * FROM PARTY
ORDER BY PARTYID DESC

Q2: DISPLAY LOCATIONS IN DESCENDING ORDER ,WITH IN THE LOCATIONS NAMES IN ASCENDING
      ORDER
 A:
 SEL PARTYLOC,PARTYNAME FROM PARTY
 ORDER BY PARTYLOC DESC ,PARTYNAME ASC

 SEL PARTYLOC,PARTYNAME FROM PARTY
 ORDER BY 1 DESC ,2 ASC

 GENERIC: COUNTRIES IN DESCENDING ORDER,WITH IN COUNTRY STATES IN ASCENDING ORDER,
 WITH IN STATES DISTRICTS IN DESCENDING ORDER(MULTI COL SORTING)


WORKING WITH DISTINCT CLAUSE
----------------------------

DISTINCT CLAUSE:IT TAKES DISTINCT COL VALUES/ ROWS  FROM THE OBJECT

SYNTAX: DISTINCT <COLUMNS> / *
EG:
SEL DISTINCT * FROM PARTY --DISTINCT ROWS
SELECT DISTINCT PARTYID,PARTYNAME FROM PARTY--DISTINCT PARTYID,PARTYNAME

REALTIME USAGE: AT DISPLAY TO AVOID DUPLICATES THIS IS THE BEST OPTION

PERFORMANCE COMPARISONS BETWEEN GROUP BY AND DISTINCT
========================================================
IN THE GIVEN SCENARIO,BOTH WORK SIMILAR BUT GROUP BY RUNS FASTER THAN DISTINCT

SINCE IT USES LES MEMORY
AND OPERATE LESS ROWS EVERY TIME IN BUFFER MEMORY.

SC1:
SEL DISTINCT PARTYID FROM PARTY
SEL PARTYID FROM PARTY GROUP BY PARTYID

SC2:
SEL DISTINCT PARTYID,PARTYNAME FROM PARTY
SEL PARTYID,PARTYNAME FROM PARTY GROUP BY PARTYID,PARTYNAME

AGGREGATE FUNCTIONS
======================
SUM,AVG,COUNT,COUNT DISTINCT,MAX,MIN ETC...

MOST OF THE NUMERIC AGGREGATES: MIN,.MAX,SUM,AVG,COUNT,COUNT

DITINCT,STDDEV,VARIANCE,GROUP ETC...

MOST OF STRING AGGREGATES: COUNT,COUNT DISTINCT,GROUP

MOST OF DATE AGGREGATES: MIN,MAX,COUNT,COUNT DISTINCT,GROUP



Differences between count(*),COUNT(NUMERIC) and count(coulmnname);
===================================================================
COUNT(*) AND COUNT(NUMERIC) WORK AT ROW LEVEL,THEY DISPLAY NUMBER OF ROWS

COUNT(COLNAME) WORKS AT COLUMN VALUES LEVEL AND IT WILL NOT INCLUDE NULL COUNT.
EX:

SEL COUNT(*),COUNT(1),COUNT(20000),COUNT(JDATE) FROM PARTY
==================================================================================

CHAPTER NO : NAMING CONVENTION, RULES, LITERALS , OPERATORS, PREDICATES
==========================================================================

FOR MORE DETAILED
 CONTENT REFER TO MAIN MATERIAL.

NAMING CONVENTIONS
-------------------
EACH OBJECT NAME CAN BE MAXIMUM OF '30' CHARS LENGTH.(TABLE/VIEW ETC... NAMES)
NAME CAN BE COMBINATION OF
A-Z
a-z
0-9
$,#,_ (underscore)

1.NO NAME SHOULD START WITH NUMERIC
2.SPECIAL CHARS NOT ALLOWED

EX: 9PARTY (WRONG BECAUSE OF NUMERIC START)
   PARTY^ (WRONG BECAUSE OF SPECIAL CHARACTER)

SOME ADDITIONAL OBSERVATIONS:

SELECT * FROM "table";  --table is reserved wors,incase you want to use keep in

double quotes


SELECT * FROM "table one"; --keep in double quotes incase object name has multiple
words

Note: in some databases braces required

select * from [table one] --sqlserver

Single Quotes (‘ ‘):

• Single Quotes are used on all Character string literals.
• Select * From Employee_Table where Last_Name =‘Wilmes’;
• Select * From Employee_Table Where Last_Name Like ‘%Tom%’;

Double Quotes (“ “):

• Double Quotes are used when aliasing a column that has multiple words and spaces

separating them.
• You would also use Double Quotes on all aliases that are reserved words.
• Select Product_ID as Prod, Sale_Date as “Date of Sale”, Amount as “MAX”
From Employee_Table Order by “MAX”;
Once you ALIAS you must use the new name throughout the SQL


FULLY COVERED COLUMN SYNTAX:
=============================

COLUMN NAME IS UNIQUE IN THE TABLE,TABLE NAME IS UNIQUE IN THE DATABASE AND
DATABASENAME IS UNIQUE IN THE SYSTEM.

COLUMN IDENTIFICATION:
<DB/USER NAME>.<TABLENAME>.<COLUMN NAME>

VINAYTECH_DB.PARTY.PARTYID --FULL SYNTAX

PARTY.PARTY--PARTIAL SYNTAX

PARTYID--UN QUALIFIED

LITERALS
---------
THESE ARE CONSTANTS / HARD CODED VALUES.

REAL TIME:
A) FOR AUDITING INFO ALONG WITH ROWS [USERNAME, SCRIPT NAME, MACHINE NAME ETC...]
B) TO RECOGNIZE SOURCE SYSTEMS / OLTP SYSTEMS IN THE WAREHOUSE LOAD.

They are of two types
a) Numeric Literal
b) String Literal

SEL
PARTYID ,
21000 AS COMPANY_CODE, --Numeric Literal
PARTYNAME,
'VINAYTECH' AS COMPANY_NAME --String Literal
'2017-05-04' as Business_date --string Literal
FROM PARTY;


SQL Expressions:
=================
There are six types of expressions.
a) Numeric expression
b) String expression
c) DateTime Expression
d) Interval expression
e) Period expression
f)Conditional expression

Examples:
Numeric Expression :  Salary * 20/100
Srtring Expression: 'Ramana' + '_' + 'madhu'

Date expressions:

INTERVAL '10' MONTH * 4

Interval expression producing an interval value

CURRENT_DATE + INTERVAL '2' DAY

DateTime expression producing a DATE val

CURRENT_TIME - INTERVAL '1' HOUR

Conditional Expression:

CASE Rating
WHEN salary<30000
THEN 'Poor'
ELSE 'Good'
END


SQL STATES:

A) TRUE
B) FALSE
C) NULL (or unknown)

Logical Predicates:

SQL provides the following logical predicates:

• Comparison operators
• [NOT] BETWEEN
• LIKE
• [NOT] IN
• [NOT] EXISTS
• OVERLAPS
• IS [NOT] NULL

Logical Operators that Operate on Predicates:

• NOT --NEGATING A CONDITION
• AND --ALL CONDITIONS TO BE MET
• OR --ANY CONDITION TO BE MET

ORDER OF PRECEDENCE: NOT, AND,OR
EX:
 a AND b OR c NOT d

Predicate Quantifiers
These used with LIKE operator (Refer to select statement second section)

• SOME --Some conditions to be met
• ANY --Any condition to be met
• ALL -- All conditions to be met

Arithmetic Operators:

**-->Exponentiation
 *-->Multiply
/ --> Divide
MOD--> Modulo (Reminder)
+ -->Addition
- --> Substraction

+ --> Unary positive (Single value positive)
- --> Unary Negative (Single value nagative)

Order of precedence: BODMAS [Braces, Of , Disivion,Multiplication, Addition and

Substraction]

Ex:

[2*4/5*6/(7+3)]

Arithmetic Operators and LOBs:

Arithmetic operators do not support BLOB or CLOB types [images, large text etc...]

Arithmetic Functions:

• ABS --Absolute value of an argument.
• CASE_N -- Returns first condition satisfied

• EXP --Raises e (natural logarithms) to the power of the argument, where

e = 2.71828182845905.

• LN --Natural logarithm of the argument.

• LOG -- Base 10 logarithm of an argument.

• NULLIFZERO --Converts data from zero to null to avoid problems with division by

zero.

Ex:

100/nullifzero(0)

• RANDOM --Returns a random integer
• RANGE_N -- Returns the range pointed
• SQRT --Square root of an argument

• ZEROIFNULL --Converts data from null to 0 to avoid cases where a null result

creates an error.


100 + NULL =NULL

100 + zeroifnull( null) =100


ANSI AND BTET Comparision operators:

ANSI  BTET
= --> EQ--> Tests for equality.
<> --> ^=  / NE / NOT= --> Tests for inequality
< --> LT --> Tests for less than.
<= -->LE --> Tests for less than or equal.
> --> GT --> Tests for greater than
>= -->GE --> Tests forgreater than or equal

BUILT IN FUNCTIONS:

ACCOUNT - contains the users account string
DATABASE - contains the current default database
DATE - contains the current date
SESSION - contains the current session-id
TIME - contains the current time
USER - contains the user name

Q1: DIFFERENCE BETWEEN ANSI AND BTET NAMING RULES?  --PAGE NO:108







================================================


INDICES (PRIMARY AND SECONDARY INDEX) PRACTICALS
=================================================
syn:
create table <tnm>(cols) [unique/no] primary index [cols] [partition by <type>[cols]]
upi
====
create table uti(tid integer,tnm varchar(30))unique primary index(tid)
show table uti
NUPI
====
create table Nuti(tid integer,tnm varchar(30))primary index(tid)

PPI(NORMAL)
====
create table Pti(tid integer,tnm varchar(30),tcd integer)
primary index(tid) partition by tcd

PPI(case_n)
====
create table cti(tid integer,tnm varchar(30),tcd integer)
primary index(tid) partition by case_n(tcd=10,tcd=20,no case,unknown)

PPI(range_n)
====
create table rti(tid integer,tnm varchar(30),tcd integer)
primary index(tid) partition by range_n(tcd between 0 and 60 each 20)

MLPI(jdate main partition ,tcd inner partition)
====
create table mti(tid integer,tnm varchar(30),tcd integer,jdate date)
primary index(tid)
partition by
(
range_n(jdate between '2014-08-01' and '2014-12-01' each interval '1' month),
case_n(tcd=10,tcd=20,no case,unknown)
)
show table mti

nopi creation
===============
two ways
a) create table noti(tid integer,tnm varchar(30)) no primary index
b) create table noti1 as (sel * from uti) with no data no primary index

modifying primary index (table empty and having nopi on it)
=====================================================
syn: alter table <tnm> modify primary index (cols)
ex: show table uti
alter table uti modify primary index(tnm)

CONVERTING NOPI TO PI TABLE
============================
ASUME TABLE(X--NOPI) -->TO TABLE (X--PI)
A) CREATE SIMILAR TABLE Y (LIKE X) WITH A PI .
B)  LOAD DATA X->Y (DATA DISTRIBUTED)
C) DROP X
D) RENAME Y->X  (NOW X IS HAVING PI WITH VALID DISTRIBUTION)

NOTE: THIS APPROACH IS STAGE TABLE AND SWAP APPROACH

DEFAULT PRIMARY INDEX
===========================
IF WE DO NOT DEFINE ANY INDEX SYSTEM FOLLOWS THE BELOW PROTOCOL
A) PRIMARY AND UNIQUE COLUMNS CONSIDER AS UPI COL
B) BOTH P[RIMARY AND UNIQUE AVAILABLE GIVES HIGH PREFERENCE TO PI
C) NO PRIMARY /UNIQUE THEN IT WOULD TAKE NUPI ON FIRST COLUMN
D) IF PI ASSIGNED EXPLICITLY/INTERNALLY REMAINING PK/UK COLS
    CONSIDERED AS USI COLUNS (UNIQUE SECONDARY INDEX)
 
SCENARIOS:
1.   CREATE TABLE T1(TID INTEGER,TNM VARCHAR(30))
 NUPI (TID)
2.   CREATE TABLE T1(TID INTEGER,TNM VARCHAR(30) PRIMARY KEY NOT NULL)
UPI(TNM)

3.   CREATE TABLE T1(TID INTEGER UNIQUE NOT NULL,
    TNM VARCHAR(30) PRIMARY KEY NOT NULL)
UNPI(TNM) USI(TID)  
4. CREATE TABLE T1(TID INTEGER UNIQUE NOT NULL,
    TNM VARCHAR(30) PRIMARY KEY NOT NULL,TCD INTEGER)
    UNIQUE PRIMARY INDEX (TCD)
    USI(TID),USI(TNM)




















===============================================================






VIEWS (CLASSROOM)

creating:
CREATE VIEW <VIEWNAME> AS(SELECT QUERY <where condition>
<with check option>)
replacing
REPLACE  VIEW <VIEWNAME> AS(SELECT QUERY <where condition>
<with check option>)
droping a view:DROP VIEW <VIEWNAME>
calling a view: SELECT * FROM <VIEWNAME>
updating a view: UPDATE <VIEWNAME> SET COLNAME=<VALUE>WHERE CONDITION
delete from a view: DEL VIEWNAME [WHERE CLAUSE]
NOTE:a) ALL DML COMMANDS SUPPORTED ON VIEW (INS,UPD,DEL,MERGE,SEL)
       B) VIEW CONTAINS ONLY ONE SELECT STATEMENT WITHOUT SEMICOLON
   
========================================
DROP VIEW V1
CREATE MODIFIABLE VIEW
=======================
CREATE  VIEW V1 AS (SEL * FROM PARTY)
SHOW VIEW V1
HELP VIEW V1
SEL * FROM V1--CALLING A VIEW
UPDATE V1 SET PARTYINCOME=30000 WHERE PARTYID=3--VIEW UPDATION
SEL * FROM PARTY WHERE PARTYID=3--TABLE VERIFICATION
READONLY VIEW (JOIN VIEW)
===========================
REPLACE VIEW V1 AS (SEL E.*,D.DEPTID AS D_DEPTID,D.DEPTNAME FROM EMP E INNER JOIN DEPT D
ON E.DEPTID=D.DEPTID)
SHOW VIEW V1
SEL * FROM V1
READONLY VIEW (AGGREGATE VIEW)
===========================
REPLACE VIEW V1 AS (SEL SUM(PARTYINCOME) AS SINCOME,
AVG(PARTYINCOME) AS AVINCOME FROM
PARTY)
SHOW VIEW V1
SEL * FROM V1
VIEW 'WITH CHECK OPTION'
=========================
IT WORKS LIKE CHECK CONSTRAINT ON A VIEW.
SEL * FROM PARTY1
REPLACE VIEW V1 AS (SEL * FROM PARTY1 WHERE PARTYINCOME>300 WITH CHECK OPTION)
SHOW VIEW V1
SEL * FROM V1
UPDATE V1 SET PARTYINCOME=100 WHERE PARTYID=3--FAILS
UPDATE V1 SET PARTYINCOME=500 WHERE PARTYID=3--SUCCESS

VIEWS (advanced)

The purposes of views are to restrict access to certain columns, derive columns or Join Tables, and to restrict access to certain rows (if a WHERE clause is used).

Exceptions to the ORDER BY Rule inside a View

There are EXCEPTIONS to the ORDER BY rule. The TOP command allows a view to work with an ORDER BY inside. ANSI OLAP statements also work inside a View.

View with TOP Command with Order by

CREATE VIEW VVW AS

(

SELECT TOP 3 * FROM PARTY

ORDER BY PARTYLOC DESC

)

View with ANSI OLAP and Order by

REPLACE VIEW VVW AS

(

SELECT PARTYID,PARTYINCOME,SUM(PARTYINCOME) OVER(ORDER BY PARTYINCOME DESC ROWS

UNBOUNDED PRECEDING) AS "CSUM" FROM PARTY

)

Help Views

The Help View command does little but show you the columns.

Syn:

Help View <Viewname> and see the result

Views sometimes CREATED for Formatting or Row Security

CREATE VIEW VW_PARTY AS

SELECT PARTYID AS Emp_No

,PARTYNAME AS Last

,PARTYINCOME/12 (format '$$$$,$$9.99') AS Monthly_Salary

FROM PARTY

WHERE PARTYCODE = 20 ;

SEL * FROM VW_PARTY

Views are designed to do many things. In the example above, this view formats and derives data, limits columns, and also limits the rows coming back with a WHERE.

Another Way to Alias Columns in a View CREATE (No AS clause)

CREATE VIEW VW_PARTY(EMPID,ENAME,SALARY) AS

SELECT PARTYID

,PARTYNAME

,PARTYINCOME/12 (format '$$$$,$$9.99')

FROM PARTY

WHERE PARTYCODE = 20 ;

SEL * FROM VW_PARTY

Resolving Aliasing Problems in a View CREATE

CREATE VIEW VW_PARTY(PARTYID,PARTYNAME,SALARY) AS

SELECT PARTYID

,PARTYNAME

,PARTYINCOME/12 (format '$$$$,$$9.99') AS Monthly_Salary

FROM PARTY

WHERE PARTYCODE = 20 ;

SEL * FROM VW_PARTY ORDER BY 3;

Resolving Aliasing Problems in a View CREATE

CREATE VIEW VW_PARTY(PARTYID,PARTYNAME,SALARY) AS

SELECT PARTYID

,PARTYNAME

,PARTYINCOME/12 (format '$$$$,$$9.99') AS Monthly_Salary

FROM PARTY

WHERE PARTYCODE = 20 ;

SEL * FROM VW_PARTY ORDER BY MONTHLY_SALARY DESC; --FAILS

If you ALIAS at the top, then that is the only ALIAS that the query can recognize. So, it is a good idea to alias at the top or the bottom, but not do both

CREATING Views for Complex SQL such as Joins

CREATE VIEW Customer_Order_v AS

SELECT Customer_Name AS Customer

,Order_Number

,Order_Total (FORMAT '$$$,$$9.99' ) AS Total_Amount

FROM Customer_Table AS Cust

,Order_Table AS Ord

WHERE Cust.Customer_Number = Ord.Customer_Number ;

SELECT * FROM Customer_Order_v ORDER BY 1 ;

A huge reason for Views, other than security, is to make Complex SQL easy for users. This view already has the Inner Join built into it, but users just SELECT.

WHY certain columns need Aliasing in a View

CREATE VIEW Aggreg_Order_v AS

SELECT Customer_Number

,Order_Date/100+190000 (format '9999-99') AS Yr_Mth_Orders

,COUNT(Order_Total) AS Order_Cnt

,SUM(Order_Total) AS Order_Sum

,AVG(Order_Total) AS Order_Avg

FROM Order_Table

GROUP BY Customer_Number, Yr_Mth_Orders ;

SELECT Customer_Number

,Order_Sum

FROM Aggreg_Order_v ;

When you CREATE a view, you have to ALIAS any aggregation or derived data (such as math). Why? So you can SELECT it later without having to do a SELECT *. Here, we only chose two columns and used their ALIAS to retrieve them.

Aggregates on View Aggregates

CREATE VIEW Aggreg_Order_v AS

SELECT Customer_Number

,Order_Date/100+190000 (format '9999-99') AS Yr_Mth_Orders

,COUNT(Order_Total) AS Order_Cnt

,SUM(Order_Total) AS Order_Sum

,AVG(Order_Total) AS Order_Avg

FROM Order_Table

GROUP BY Customer_Number, Yr_Mth_Orders ;

SELECT SUM (Order_Sum)

FROM Aggreg_Order_v ;

The examples above show how we put a SUM on the aggregate Order_Sum.

Locking Row for Access

CREATE VIEW VW_PARTY AS

LOCKING ROW FOR ACCESS

SELECT PARTYID

,PARTYNAME

FROM PARTY;

The PARTY used above will automatically use an ACCESS Lock, which allows ACCESS during UPDATES or table

Loads.

Most views utilize the Locking row for ACCESS command. This is because they want to be able to read while a table is being updated and loaded into. If the user knows a dirty read won't have a huge effect on their job, why not make a view lock with an ACCESS Lock, thus preventing unnecessary waiting?

Creating Views for Temporal Tables

CREATE VIEW SQL01.Prop_As_Is

AS

Locking row for access

CURRENT VALIDTIME

SELECT Cust_No

,Prop_No

BEGIN(Prop_Val_Time) AS Beg_Val_Time,

END(Prop_Val_Time) AS End_Val_Time,

FROM Property_Owners;

SELECT * FROM SQL01.Prop_As_Is ;

CREATE VIEW SQL01.Prop_As_Was

AS

Locking row for access

NONSEQUENCED VALIDTIME

SELECT Cust_No

,Prop_No

BEGIN(Prop_Val_Time) AS Beg_Val_Time,

END(Prop_Val_Time) AS End_Val_Time,

FROM Property_Owners;

SELECT * FROM SQL01.Prop_As_Was ;

You can create views that will allow users to see the way things are or the way things were. Above, are two excellent examples

Altering a Table after a View has been created

CREATE VIEW VW_PARTY AS

SELECT  * FROM PARTY

ALTER TABLE  PARTY ADD TTA INTEGER

SEL * FROM VW_PARTY—IT WILL NOT SHOW YOU TTA

This view runs after the table has added an additional column, but it won't include Mgr_No in the view results even though there is a SELECT * in the view. The View includes only the columns present when the view was CREATED.

A View that errors After an ALTER

CREATE VIEW VW_PARTY AS

SELECT  * FROM PARTY

ALTER TABLE  PARTY DROP TT

SEL * FROM VW_PARTY –FAILS BY SAYING TT DOESN’T EXIST

This view will NOT run after the table has dropped a column referenced in the view.

Maintenance Restrictions on a Table through a View (INSERT,UPDATE,DELETE)

There are a few restrictions that disallow maintenance activity on a view with an INSERT, UPDATE or DELETE request. A view cannot be used for maintenance if it:

1. Performs a join operation - more than one table

2. Selects the same column twice - wouldn't know which one to use

3. Derives data - because it does not undo the math or calculation

4. Performs aggregation - because this eliminates detail data

5. Uses OLAP functions - because OLAP data is calculated

6. Uses a DISTINCT or GROUP BY - eliminates duplicate rows

======================================================================
ANALYTICAL FUNCTIONS
======================================================================
FINDING CUMULATIVE(RUNNING) SUM OR AVGS WITH THE HELP OF SUM ,AVG ,OVER KEYWORDS:

SYNTAX:
SUM OR AVG OR DIFF(COLNAME) OVER(PARTITION
BY COLNAME ORDER BY COLNAME ROWS UNBOUNDED/N PRECEDING/FOLLOWLING)

EG1: DISPLAY AGAINST TO EACH ROW ALL PREVIOUS ROWS CUMULATIVE SUM

SELECT PARTYID,PARTYCODE,PARTYINCOME,SUM(PARTYINCOME)
OVER(ORDER BY PARTYINCOME ROWS UNBOUNDED PRECEDING) FROM PARTY;

EG2: DISPLAY AGAINST TO EACH ROW  PREVIOUS ALL AND FOLLOWED ALL ROWS CUMULATIVE SUM
SELECT PARTYID,PARTYCODE,PARTYINCOME,SUM(PARTYINCOME)
OVER(ORDER BY PARTYINCOME ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) FROM PARTY;

EG3: DISPLAY AGAINST TO EACH ROW  PREVIOUS 2 AND FOLLOWED 3 ROWS CUMULATIVE SUM
SELECT PARTYID,PARTYCODE,PARTYINCOME,SUM(PARTYINCOME)
OVER(ORDER BY PARTYINCOME ROWS BETWEEN 2 PRECEDING AND 3 FOLLOWING) FROM PARTY;

Eg3:DISPLAY AGAINST TO EACH ROW 2 PREVIOUS ROWS CUMULATIVE SUM

SELECT PARTYID,PARTYCODE,PARTYINCOME,SUM(PARTYINCOME)
OVER(ORDER BY PARTYINCOME ROWS 2 PRECEDING) FROM PARTY;

eg4: BASED ON THE PARTYCODE I REQUIRE FRESH CUMULATIVE SUM

SELECT PARTYID,PARTYCODE,PARTYINCOME,SUM(PARTYINCOME)

OVER(PARTITION BY PARTYCODE ORDER BY PARTYINCOME ROWS UNBOUNDED PRECEDING) FROM PARTY;

ANALYTICAL RANK


RANK: Display rank for every column cell value staring with 1.

For same value displays same rank.
There are gaps between ranks.

ROW_NUMBER: Display unique sequence value in the column cell values starting with 1.
QUALIFY –Filters the data of rank and row_number.

RANK()OVER (PARTITION BY <COLS> ORDER BY <COLS> des/asc)
ROW_NUMBER() ....

NOTE:
PARTITION--GROUP
OVER-->INDICATE ANALYTICAL FUNCTION

Q: DISPLAY RANK VALUES FOR EVERY CELL VALUE BY KEEPING IN DESCENDING ORDER
DISPLAY RANK FOR EVERY VALUE ACCORDING TO RANK PROTOCOL
SEL PARTYID,PARTYINCOME,RANK() OVER (ORDER BY PARTYINCOME DESC) FROM PARTY

DISPLAY RANK UNIQUELY FOR EVERY UNIQUE VALUE
SEL PARTYINCOME,RANK() OVER (ORDER BY PARTYINCOME DESC) FROM PARTY
GROUP BY PARTYINCOME

DISPLAY TOP 3 PARTYINCOME VALUES
SEL PARTYINCOME,RANK() OVER (ORDER BY PARTYINCOME DESC) R FROM PARTY
QUALIFY R<=3
GROUP BY PARTYINCOME

DISPLAY 3RD MAX PARTYINCOME VALUE
SEL PARTYINCOME,RANK() OVER (ORDER BY PARTYINCOME DESC) R FROM PARTY
QUALIFY R=3
GROUP BY PARTYINCOME

NOTE: R<=N--FOR TOP N VALUES,R=N FOR N TH MAXIMUM SALARY

DISPLAY LOCATIONWISE TOP 2 PARTYINCOME VALUES
SEL PARTYLOC,PARTYINCOME,RANK() OVER (PARTITION BY PARTYLOC ORDER BY PARTYINCOME DESC) R
FROM PARTY
QUALIFY R<=2
GROUP BY PARTYLOC,PARTYINCOME

DISPLAY UNIQUE VALUE FOR EVERY CELL VALUE STARTING with 1
SEL PARTYINCOME,ROW_NUMBER() OVER (ORDER BY PARTYINCOME DESC) R FROM PARTY
TOP 3,OR 3RD MAX OR LOCATION WISE TOP 2--
IN THE PREVIOUS RANK() FUNCTION REPLACE WITH ROW_NUMBER()

TO WORK WITH BOTTOM VALUES
=============================

USE ORDER BY AND TAKE ASCENDING ORDER

FINDING CUMULATIVE SUM OR AVGS WITH THE HELP OF MSUM, MAVG,MDIFF FUNCTIONS:
SYNTAX:
MDIFF(COLUMN,N,SORT COLUMN1,SORTCOLUMN2);
MSUM(COLUMN,N,SORT COLUMN1,SORTCOLUMN2);
MAVG(COLUMN,N,SORT COLUMN1,SORTCOLUMN2);
NOTE: THESE FUNCTIONS ONLY FOR PRECEDING ROWS

EG: DISPLAY 2 PRECEDING ROWS CUMULATIVE SUM

SELECT PARTYID,PARTYCODE,PARTYINCOME,MSUM(PARTYINCOME,3,PARTYINCOME) FROM PARTY;

NOTE: USE N+1 FOR N ROWS MOVING AGGREGATION