Database query writing tutorials and DAL.

Nice Oracle Tutorials:
1. https://www.youtube.com/user/oraclecoach/videos
2. https://www.youtube.com/user/TheCodeMakers/videos
3. http://searchoracle.techtarget.com/answer/UPDATE-only-rows-which-match-another-table-in-SQL
4. https://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys
4.When some time our DB will be locked..!
Case 1:
When we are performing any update Query in Sql Developer and without doing commit again we are trying to do same update through code . At this time our code Application will be hanged.

Because in Sql developer the update Query has to be commited.

and then only code update Query will run successfully.


That's All..!!.

So just write "commit" in sql developer and execute.

5.How to get all tables in oracle using Querry.Query which will give all tables names belongs to the database.

SELECT table_name FROM dba_tables
WHERE table_name LIKE 'name%'
ORDER BY table_name

6.How to import db txt file to database

cmd>imp username/password@databasename file="PathToTextFile" full="Y".

For Particular Table
Here it will create table structure along with data inserting into them

imp username/password@databasename file="D:\jako.txt" tables=(
falnaTable)

7.How to export database into txt file.

first create a textfile in some location of computer.

cmd>exp username/password@databasename file="PathToTextFile" full="Y".

For Particular Table
Here it will create backup for table structure along with data

exp username/password@databasename file="D:\jako.txt" tables=(
falnaTable)

8.How to install oracle services and use the existing db.

step-1:

Install oracle service( suppose oracle 11gr2)

step-2:

create a listener.

step-3:

create db using databse configure assistant.

step-4:

Import the existing databse file(it can be your exported txt file).

step-5:

Add a user client in your oracle client.

step-6:

connect your db with sql developer using TNS.

thats all..!! 

9.https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle/24054741#24054741

How we can use outer query table column value as input to subquery tables.

UPDATE LDB1_PRODUCTIONLINERESOURCE T1

SET T1.FSITEID = (

SELECT PL.FSITEID FROM LDB1_PRODUCTIONLINERESOURCE PR

INNER JOIN LDB1_PLANTPRODUCTIONLINE PL ON PL.FPLINEID=PR.FPLINEID

WHERE PR.FRESOURCEID=T1.FRESOURCEID)

UPDATE LDB1_PRODUCTIONLINERESOURCE PR

SET PR.FSITEID = (SELECT PL.FSITEID FROM LDB1_PLANTPRODUCTIONLINE PL WHERE PL.FPLINEID=PR.FPLINEID);

10. we can also writing case level query so as per current row data we can use case level query writing.

1)//CASE :ORDERTYPE WHEN 'H' THEN :QTY ELSE P.FPACKSIZE END
2)//FAVAILABLEQTY=CASE WHEN FAVAILABLEQTY+:AVAILABLEQTY>=0 THEN FAVAILABLEQTY+:AVAILABLEQTY ELSE 0 END

11. using listagg.

SELECT LISTAGG(LTRIM(FCOMPLEMENTARYIDH,'0'), ',') WITHIN GROUP ( ORDER BY FCOMPLEMENTARYIDH) FROM LDB1_CONRATE_COMPLEMENTARY_IDH WHERE FIDHID=M.FIDHID AND FSITEID =M.FSITEID AND FSTATUS='A' GROUP BY FIDHID )AS FCOMPLEMENTARYIDH

12. IN operator cannot accept more than 1000 parameter comma separated but it can accept unlimited records from subquery.

13. How to use Query as temp Table for Joining purpose...!!
LEFT OUTER JOIN (SELECT PR.FRESOURCECODE,PR.FISWORKRESOURCE, PL.FPLINEID,PL.FPLINE,PL.FENABLEPACKAGINGSTAGING 
FROM LDB1_PRODUCTIONLINERESOURCE PR INNER JOIN LDB1_PLANTPRODUCTIONLINE PL 
ON PR.FPLINEID=PL.FPLINEID AND PR.FSITEID=PL.FSITEID AND PL.FSTATUS='A' AND PR.FSTATUS = 'A' AND PL.FSITEID=:SITEID) TR  
ON TR.FRESOURCECODE =DECODE(NVL(P.FMANFCATEGORY,'E'),'E',P.FCATEGORY,P.FMANFCATEGORY).

14. For altering a table please follow the link.
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj81859.html

15. Drop multiple coulmns in single query
http://stackoverflow.com/questions/6346120/how-do-i-drop-multiple-columns-with-a-single-alter-table-statement
https://oracle-base.com/articles/8i/dropping-columns

ALTER TABLE table_name DROP (column_name1, column_name2);

16. ListAgg Examples
http://thinktibits.blogspot.in/2013/01/oracle-listagg-function-example.html

17. How to get all foreign Key referenced Tables in Oracle.

SELECT UC.TABLE_NAME, UC.R_CONSTRAINT_NAME, UCC.TABLE_NAME, UCC.COLUMN_NAME
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME
  --AND uc.constraint_type = 'R'
ORDER BY UC.TABLE_NAME, UC.R_CONSTRAINT_NAME, UCC.TABLE_NAME, UCC.COLUMN_NAME;

Column referenced in other tables

select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints 
where constraint_type='R'
and r_constraint_name in (select constraint_name from all_constraints 
where constraint_type in ('P','U') and table_name='TABLE_NAME');

18. Video lectures

http://freevideolectures.com/Course/3684/Oracle-11g/12c/44

19. Oracle Errors.

Error: ORA-01745: invalid host/bind variable name

http://stackoverflow.com/questions/28464732/php-ora-01745-invalid-host-bind-variable-name-warning

Error: ORA-01036: illegal variable name/number

When we bind a variable in DAL but we are not using it anywhere in the query.

Error: ORA-01008: not all variables bound 

When we do not bind a variable in DAL but we are trying to use it anywhere in the query.

http://www.dba-oracle.com/t_ora_01008_plsql_variables_bound.htm

20. How to execute paging through Oracle query.

SELECT *

FROM

  (SELECT B.*,

    ROWNUM AS FROWINDEX,
    MAX(ROWNUM) OVER () AS FCOUNT

  FROM

    (

    

    ) B

  ) C

WHERE C.FROWINDEX BETWEEN (:PAGEINDEX) AND (:PAGESIZE + (:PAGEINDEX)) 

FCOUNT--for total records count before applying paging

SELECT * FROM ( SELECT B.*, DENSE_RANK() OVER(ORDER BY B.FBINID) FDRNK FROM (

SELECT * FROM ( SELECT B.*,ROWNUM AS FROWINDEX FROM (

https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering?rq=1

21. How to use subquery as a table for inner join in Oracle query.

...

INNER JOIN(SELECT ABC1, MAX(ABC) AS MAXVALUE1 FROM TABE WHERE AAM=:BAAM GROUP BY ABC1) B

ON I.ABC1=B.ABC1 AND I.VALUE1=B.MAXVALUE1

22. How to get the records between dates using timestamp in Oracle query.

http://stackoverflow.com/questions/30070691/oracle-sql-query-for-records-with-timestamp-that-falls-between-two-timestamps

SELECT *

FROM LDB1_PLANTMATERIAL

WHERE ftimestamp BETWEEN CURRENT_TIMESTAMP-90 AND CURRENT_TIMESTAMP;

23. How to sum two sub query results in Oracle query.

SELECT TH.FTOKENID,TH.FARRIVALDATE,TH.FARRIVALTIME,((SELECT NVL(SUM(PR.FNUMPALLET),0) AS FPALLETS

  FROM LDB1_PALLETRECEIVEDTL PR

  INNER JOIN LDB1_RECEIVEHEADER RH ON PR.FRECEIVENUM=RH.FRECEIVENUM

  WHERE RH.FTOKENID=TH.FTOKENID AND RH.FSITEID=TH.FSITEID) +

  (SELECT NVL(SUM(PR.FNUMPALLET),0) AS FPALLETS

  FROM LDB1_PALLETRECEIVEDTL_TEMP PR

  INNER JOIN LDB1_RECEIVEHEADER_TEMP RH ON PR.FRECEIVENUM=RH.FRECEIVENUM

  WHERE RH.FTOKENID=TH.FTOKENID AND RH.FSITEID=TH.FSITEID

  )) AS TOTALPALLETS

FROM LDB1_TOKENHDR TH

WHERE TH.FSITEID=:FSITEID AND TH.FSTATUS <> 'R' AND TH.FAREATYPE='G' 

AND ((TO_DATE(:ARRIVALDATETIME,'YYYYMMDD HH24MISS') - TO_DATE(TH.FARRIVALDATE || ' ' || LPAD(TH.FARRIVALTIME,6,0),'YYYYMMDD HH24MISS') ) * 24) <= :FTOKENVALIDITYHOUR

ORDER BY TH.FTOKENID DESC

23. How to join two query results in one query using left outer join Oracle query.

SELECT TH.FTOKENID,TH.FTRUCK_NUM,TH.FARRIVALDATE,TH.FARRIVALTIME, 'IN' AS FTRUCKSTATUS FROM LDB1_TOKENHDR TH

WHERE TH.FSITEID=:SITEID AND TH.FSTATUS <> 'R' AND TH.FAREATYPE='G'

AND ((TO_DATE(:ARRIVALDATETIME,'YYYYMMDD HH24MISS') - TO_DATE(TH.FARRIVALDATE || ' ' || LPAD(TH.FARRIVALTIME,6,0),'YYYYMMDD HH24MISS') ) * 24) <= :TOKENVALIDITYHOUR

UNION 

SELECT DISTINCT TH.FTOKENID,TH.FTRUCK_NUM,TH.FARRIVALDATE,TH.FARRIVALTIME, 'OUT' AS FTRUCKSTATUS FROM LDB1_TOKENHDR TH

INNER JOIN LDB1_TOKENMOV TM ON TH.FTOKENID=TM.FTOKENID AND TH.FAREATYPE=TM.FAREA

WHERE TH.FSITEID=:SITEID AND TH.FSTATUS = 'R' AND TH.FAREATYPE='G' AND TM.FSTARTDATE<>0 AND TM.FENDDATE=0

AND ((TO_DATE(:ARRIVALDATETIME,'YYYYMMDD HH24MISS') - TO_DATE(TH.FARRIVALDATE || ' ' || LPAD(TH.FARRIVALTIME,6,0),'YYYYMMDD HH24MISS') ) * 24) <= :TOKENVALIDITYHOUR

ORDER BY FTOKENID DESC

SELECT DISTINCT TH.FTOKENID,TH.FTRUCK_NUM,TH.FARRIVALDATE,TH.FARRIVALTIME,DECODE(TH.FSTATUS,'R','OUT','IN') AS FTRUCKSTATUS

FROM LDB1_TOKENHDR TH

LEFT OUTER JOIN LDB1_TOKENMOV TM ON TH.FTOKENID=TM.FTOKENID AND TH.FAREATYPE=TM.FAREA AND TM.FSTARTDATE<>0 AND TM.FENDDATE=0

WHERE TH.FSITEID=:SITEID AND TH.FAREATYPE='G' AND (TH.FSTATUS <> 'R' OR (TH.FSTATUS = 'R' AND TM.FTOKENID IS NOT NULL))

AND ((TO_DATE(:ARRIVALDATETIME,'YYYYMMDD HH24MISS') - TO_DATE(TH.FARRIVALDATE || ' ' || LPAD(TH.FARRIVALTIME,6,0),'YYYYMMDD HH24MISS') ) * 24)<= :TOKENVALIDITYHOUR

ORDER BY TH.FTOKENID DESC

24. How to add multilple column for a table using alter statement

ALTER TABLE TABLEABC ADD(FCREATEDBY NUMBER(6),FCREATEDDATE NUMBER(8), FCREATEDTIME NUMBER(6));

25. How to pass list of string in DAL if db coulmn is numeric.
PL.FPLINEID IN (" + productionLineID + ")--when FPLINEID is a numeric coulmn.

FSTATUS IN ('A','N','D','R')-- when FSTATUS  is a character column.

ORDER IN( " + processOrder + ")--when ORDER is Varchar2  column then from WCF side we need to pass
processOrder ='10001545654','1000565666';--in case of comma separated strings.
processOrder ='10001545654';--in case of single string.

26. How to handle IN operator if records came more than 1000.

1)use sub query.

2)break the list from 900. Means one time pass around 900 entry get the data and again make db call for next 900 data.

27. Oracle Tutorials

https://oracle-base.com/articles/11g/articles-11g


28. How to use translate function and its alternative in query
TRANSLATE(' ' USING NCHAR_CS) AS FRESOURCECODE
OR
NULL AS FRESOURCECODE

29. How to handle 0 as date in oracle query for ranging dates
(TO_DATE(DECODE(NVL(P.FFINISHDATE,0),0,'99990101',P.FFINISHDATE) || '' || LPAD(P.FFINISHTIME,6,'0'),'YYYYMMDDHH24MISS')-TO_DATE(DECODE(NVL(P.FSTARTDATE,0),0,'00010101',P.FSTARTDATE) || '' || LPAD(P.FSTARTTIME,6,'0'),'YYYYMMDDHH24MISS'))

30.Writing query using LISTAGG

SELECT DISTINCT MI.FIDHID, 
(SELECT LISTAGG(TO_CHAR(DL.FFORMATFILENAME), ',') WITHIN GROUP (ORDER BY DL.FFORMATFILENAME)
FROM LDB1_LABEL_IDHFORMATS LI
INNER JOIN LDB1_DYNAMICLABELFORMAT DL ON DL.FIDENTIFIER=LI.FFORMATIDENTIFIERID AND DL.FSTATUS='A' WHERE LI.FIDHID=MI.FIDHID
GROUP BY FIDHID) AS FCOMPLEMENTARYIDH
FROM LDB1_LABEL_IDHFORMATS MI WHERE MI.FSITEID=2

How to unlock the table in oracle
===========================================================================================================
STEP-1: ISSUE CREATED BY SOME UPDATE(DML) STATEMENT
----------------------------------------------------------
UPDATE LDB1_PRESB_ITEM SET FSTATUS=:STATUS
WHERE FAUFNR =:AUFNR  AND FLINEITEM=:LINEITEM  AND NVL(FSTATUS,'N') <> 'C' AND FSITEID=:SITEID

STEP-2: TO SEE THE WHO ALL HAVE RESERVED/LOCKED ACCESS TO THE TABLES
---------------------------------------------------------
SELECT B.OWNER, B.OBJECT_NAME, A.ORACLE_USERNAME, A.OS_USER_NAME  
FROM V$LOCKED_OBJECT A, ALL_OBJECTS B
WHERE A.OBJECT_ID = B.OBJECT_ID

STEP-3: 
---------------------------------------------------------
SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='LDB1_PRESB_ITEM';

STEP-4: 
---------------------------------------------------------
SELECT SID FROM V$LOCK WHERE ID1=75351[OBJECT_ID]
EX:-
132
201
19
82
77
12
135
80

STEP-5: 
---------------------------------------------------------
SELECT SID, SERIAL# FROM V$SESSION WHERE SID=201[SID]

STEP-6: KILL THE SESSION AND UNLOCK THE TABLE
---------------------------------------------------------
ALTER SYSTEM KILL SESSION 'SID,SERIAL' ;
ALTER SYSTEM KILL SESSION '201,10958' ;

ref example links
https://dba.stackexchange.com/questions/56615/how-to-determine-if-an-oracle-table-is-locked-or-not
http://dwhlaureate.blogspot.com/2014/07/how-to-unlock-locked-table-in-oracle.html
http://www.dbatodba.com/oracle/how-to-check-the-locked-object-details/


Functional Index example
===============================================================================================================
SELECT P.FORDERNUMBER, M.FIDHDESC,P.FINVOICENUMBER,P.FMATNR,P.FINVOICEQTY,P.FRECEIVEDQTY,P.FMEINS 
,(SELECT MIN(DECODE(NVL(R.FUNLOADSTARTDATE,0),0,RT.FUNLOADSTARTDATE,R.FUNLOADSTARTDATE)) AS FUNLOADSTARTDATE
FROM LDB1_POINVOICEINFO PO
LEFT OUTER JOIN LDB1_RECEIVEHEADER_TEMP RT ON PO.FTOKENID=RT.FTOKENID AND PO.FORDERNUMBER=RT.FORDERNUM AND PO.FSITEID=RT.FSITEID
LEFT OUTER JOIN LDB1_RECEIVEHEADER R ON PO.FTOKENID=R.FTOKENID AND PO.FORDERNUMBER=R.FORDERNUM AND PO.FSITEID=R.FSITEID
WHERE PO.FTOKENID=P.FTOKENID AND PO.FSITEID=P.FSITEID AND PO.FORDERNUMBER=P.FORDERNUMBER AND ROWNUM=1
) AS FUNLOADSTARTDATE
FROM LDB1_POINVOICEINFO P 
INNER JOIN LDB1_PLANTMATERIAL M ON P.FMATNR=M.FIDHID AND P.FSITEID=M.FSITEID
WHERE P.FTOKENID=:TOKENID AND P.FSITEID=:SITEID AND P.FORDERNUMBER=:ORDERNUMBER 
ORDER BY P.FINVOICEITEMID;

CREATE INDEX INDX_POINVOICE_TOKENORDER 
ON LDB1_POINVOICEINFO(FTOKENID,FSITEID,FORDERNUMBER);



http://www.gurujipoint.com/2017/04/difference-between-rank-denserank-and.html
http://www.sqlines.com/oracle-to-mysql
http://weblogs.sqlteam.com/jeffs/archive/2007/10/11/mixing-inner-outer-joins-sql.aspx

SQL Server Configuration
================================================================================================================
https://www.youtube.com/watch?v=VOUDdUJ5BLY

Using CASTING Vs RTRIM for in inner join for converting char into varchar2 in oracle
-------------------------------------------------------------------------------------------------
SELECT *
FROM PAPAJOHN PI 
INNER JOIN DOMINOZ ST ON PI.FAUFNR=ST.FAUFNR AND PI.FLINEITEM=ST.FLINEITEM AND PI.FMATNR=ST.FMATNR AND PI.FUOM=CAST(ST.FUOM AS VARCHAR2(2))
WHERE PI.FAUFNR ='001000940513';[Not Working]

SELECT *
FROM PAPAJOHN PI 
INNER JOIN DOMINOZ ST ON PI.FAUFNR=ST.FAUFNR AND PI.FLINEITEM=ST.FLINEITEM AND PI.FMATNR=ST.FMATNR AND PI.FUOM <>RTRIM(ST.FUOM)
WHERE PI.FAUFNR ='001000940513';[Working]

https://stackoverflow.com/questions/25721848/is-possible-cast-char-to-varchar-while-join-table-in-oracle


Changing connection string at runtime in Enterprise Library
-------------------------------------------------------------------------
https://stackoverflow.com/questions/326365/changing-connection-string-at-runtime-in-enterprise-library


How to get all tables and their owner in oracle Database
------------------------------------------------------------------------------
https://stackoverflow.com/questions/205736/get-list-of-all-tables-in-oracle?rq=1
SELECT OWNER, TABLE_NAME  FROM DBA_TABLES

SELECT TABLE_NAME FROM ALL_TABLES  

How to alter primary key in oracle
---------------------------------------------------------------------------
ALTER TABLE SOMETABLE DROP CONSTRAINT PK_USERROLE_ID;

DROP INDEX PK_USERROLE_ID;

COMMIT

Run Migration Project

ALTER TABLE SOMETABLE ADD CONSTRAINT PK_USERROLE_ID PRIMARY KEY (FUSERID, FROLEID, FSITEID);

COMMIT

How to check table exist in oracle database?
select table_name from user_tables where table_name='falnaTabel'
select * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('TABLE','VIEW')

Nice Sql Tutorials
http://sql-plsql.blogspot.in/2012/04/column-exists-in-sql-table-or-not.html


Insert Using Select Query in oracle
--------------------------------------------------
INSERT INTO Table2(FSITEID,FIDNUM,FCREATEDDATE,FCREATEDBY)
SELECT DISTINCT C.FSITEID,T.FIDNUM,:CREATEDDATE,:CREATEDBY FROM Table1 C
INNER JOIN Table3 RT ON RT.FROLEID=C.FROLEID--some roles will be eliminated from the query as they may not have assigned any tasks
INNER JOIN Table4 T ON RT.FTASKID=T.FTASKID
WHERE C.FSITEID<>0 ORDER BY C.FSITEID,T.FIDNUM;

In a database table nature of row changes as per certain type of column value.


How to check table exist in oracle database
--------------------------------------------------------------------------
SELECT TABLE_NAME FROM  ALL_TABLES WHERE TABLE_NAME='falnatable'
SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME='falnatable';

https://stackoverflow.com/questions/8156784/check-to-see-if-a-temporary-table-exists

How to create or modify view in oracle
---------------------------------------------------------------------
CREATE OR REPLACE FORCE EDITIONABLE VIEW "OMDB"."VW_VIEWNAME" ("COL1", "COL2") AS
Plz write query here which contains output as COL1 and COL2

To find out all oracle tables having column:
------------------------------------------------------------------
SELECT TABLE_NAME
FROM DBA_TAB_COLUMNS
WHERE COLUMN_NAME='FDIMENSIONID';

SELECT TABLE_NAME
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME='FDIMENSIONID';

https://community.oracle.com/thread/2144602

How to get the all foreign key tables for my primary key table:
SELECT FK.OWNER, FK.TABLE_NAME, COL.COLUMN_NAME FROM DBA_CONSTRAINTS PK
JOIN DBA_CONSTRAINTS FK ON PK.CONSTRAINT_NAME = FK.R_CONSTRAINT_NAME  AND FK.CONSTRAINT_TYPE = 'R'
JOIN DBA_CONS_COLUMNS COL ON FK.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
WHERE PK.OWNER = :OWNER AND PK.TABLE_NAME = 'LDB1_PACKAGETYPE' AND PK.CONSTRAINT_TYPE = 'P';

SELECT * FROM DBA_CONSTRAINTS

How to use upper in DAL side:
sqlInnerCmdBuilder.Append(" AND UPPER(SB.FSTORAGELOCATION) =:STORAGELOCATION ");
sqlInnerCmdBuilder.Append(" AND UPPER(SB.FBINLOCATION) LIKE :BINLOCATION ");
         
db.AddInParameter(dbCmd, ":BINLOCATION", DbType.AnsiString, "%" + storageBin.Trim().ToUpper() + "%");     
db.AddInParameter(dbCmd, ":STORAGELOCATION", DbType.AnsiString, storageLocation.ToUpper());

Using transaction in sequence next value is of no use. It cannot be reverted.

When not to use Values in Insert query:
When we are inserting data by reading from other table

How to read from a sequence:
SELECT sequence_name, last_number curr_val, increment_by,
last_number + Increment_by next_val
FROM USER_SEQUENCES
WHERE sequence_name LIKE 'SEQUENCE_PACKAGETYPE_ID%';

How to rename a column name:
ALTER TABLE LDB1_PACKAGETYPE
RENAME COLUMN FHIRINGACCOUNT TO FHIREACCOUNTNUMBER

ORA-01745: invalid host/bind variable name:
This Issue comes when we try use identifier same as reserved keywords in DAL side
UPDATE Table1 SET FACCESS=:ACCESS WHERE FTASKID=:TASKID AND FROLEID=:ROLEID

How to decrements sequence value in oracle:
ALTER SEQUENCE SEQUENCE_PACKAGETYPE_ID INCREMENT  BY -1020;
SELECT SEQUENCE_PACKAGETYPE_ID.NEXTVAL FROM DUAL;
ALTER SEQUENCE SEQUENCE_PACKAGETYPE_ID INCREMENT BY 1
note: all three has to execute at one short and there is no decrement keyword for this.

How to drop a table properly in oracle:
http://www.adp-gmbh.ch/ora/sql/drop_table.html

drop table table_name;
drop table table_name cascade constraints;
drop table table_name purge;

How and Why to create multi column index to increase performance and reduce cost of the query:
CREATE INDEX INDX_F1 ON F1(C1,C2);
CREATE INDEX INDX_F2_ARCH ON F2(C1,C2);

SELECT DISTINCT C1 FROM F1 DI WHERE DI.C1=:C1AND DI.C2=:C2
UNION
SELECT DISTINCT C1 FROM F2 DIA WHERE DIA.C1=:C1AND DIA.C2=:C2

Date level query for oracle:
SELECT (TO_NUMBER(TO_CHAR(TO_DATE(NVL(20180111,0),'YYYYMMDD'),'YYYYMMDD'))-TO_NUMBER(TO_CHAR(TO_DATE(NVL(20180110,0),'YYYYMMDD'),'YYYYMMDD'))) AS DAR FROM DUAL
SELECT CASE WHEN (TO_NUMBER(TO_CHAR(TO_DATE(NVL(20180111,0),'YYYYMMDD'),'YYYYMMDD'))-TO_NUMBER(TO_CHAR(TO_DATE(NVL(20180110,0),'YYYYMMDD'),'YYYYMMDD')))>0 THEN 'RAM' ELSE 'SITA' END AS DAR from dual
select TO_NUMBER(TO_CHAR(TO_DATE(NVL(20180111,0),'YYYYMMDD'),'YYYYMMDD')) from dual

SELECT  BU.FSTORAGEID,BU.FBINID,BU.FCREATEDON,BU.FENDDATE,ST.FSTORAGETYPE
,CASE NVL(BU.FENDDATE,0) WHEN 0 THEN TO_NUMBER(TO_CHAR(TO_DATE(:ENDDATE,'YYYYMMDD'),'YYYYMMDD')) ELSE TO_NUMBER(TO_CHAR(TO_DATE(BU.FENDDATE,'YYYYMMDD'),'YYYYMMDD')) END FENDDATE
,CASE WHEN((TO_DATE(BU.FCREATEDON,'YYYYMMDD'))-(TO_DATE(:STARDATE,'YYYYMMDD')))<0 THEN TO_NUMBER(TO_CHAR(TO_DATE(:STARDATE,'YYYYMMDD'),'YYYYMMDD')) ELSE TO_NUMBER(TO_CHAR(TO_DATE(BU.FCREATEDON,'YYYYMMDD'),'YYYYMMDD')) END AS FSTARTDATE
FROM LDB1_BIN_UTILIZATION BU
INNER JOIN LDB1_STORAGETYPE ST ON BU.FSTORAGEID=ST.FSTORAGEID AND BU.FSITEID=ST.FSITEID AND ST.FSTATUS='A'
INNER JOIN LDB1_STORAGEBINLOCATION SB ON ST.FSTORAGEID=SB.FSTORAGEID AND ST.FSITEID=SB.FSITEID AND BU.FBINID=SB.FBINID AND SB.FSTATUS='A'
WHERE BU.FSITEID=:SITEID AND BU.FCREATEDON <=:ENDDATE AND (:STARDATE <=BU.FENDDATE OR NVL(BU.FENDDATE,0)=0)

SELECT BU.FBINID,SUM(((CASE NVL(BU.FENDDATE,0) WHEN 0 THEN TO_NUMBER(TO_CHAR(TO_DATE(:ENDDATE,'YYYYMMDD'),'YYYYMMDD')) ELSE TO_NUMBER(TO_CHAR(TO_DATE(BU.FENDDATE,'YYYYMMDD'),'YYYYMMDD')) END)
-(CASE WHEN((TO_DATE(BU.FCREATEDON,'YYYYMMDD'))-(TO_DATE(:STARDATE,'YYYYMMDD')))<0 THEN TO_NUMBER(TO_CHAR(TO_DATE(:STARDATE,'YYYYMMDD'),'YYYYMMDD')) ELSE TO_NUMBER(TO_CHAR(TO_DATE(BU.FCREATEDON,'YYYYMMDD'),'YYYYMMDD')) END))) AS FUSEDDAYS
FROM LDB1_BIN_UTILIZATION BU
INNER JOIN LDB1_STORAGETYPE ST ON BU.FSTORAGEID=ST.FSTORAGEID AND BU.FSITEID=ST.FSITEID AND ST.FSTATUS='A'
INNER JOIN LDB1_STORAGEBINLOCATION SB ON ST.FSTORAGEID=SB.FSTORAGEID AND ST.FSITEID=SB.FSITEID AND BU.FBINID=SB.FBINID AND SB.FSTATUS='A'
WHERE BU.FSITEID=:SITEID AND BU.FCREATEDON <=:ENDDATE AND (:STARDATE <=BU.FENDDATE OR NVL(BU.FENDDATE,0)=0)
GROUP BY BU.FBINID

SELECT DISTINCT ST.FSTORAGEID,ST.FSTORAGETYPE,SB.FBINID,BU.FCREATEDON,BU.FENDDATE,
CASE WHEN BU.FCREATEDON IS NULL THEN 0 ELSE (CASE WHEN BU.FCREATEDON=BU.FENDDATE THEN 1 ELSE ((CASE NVL(BU.FENDDATE,0) WHEN 0 THEN TO_DATE(:ENDDATE,'YYYYMMDD') ELSE TO_DATE(BU.FENDDATE,'YYYYMMDD') END)
-(CASE WHEN((TO_DATE(BU.FCREATEDON,'YYYYMMDD'))-(TO_DATE(:STARDATE,'YYYYMMDD')))<0 THEN TO_DATE(:STARDATE,'YYYYMMDD') ELSE TO_DATE(BU.FCREATEDON,'YYYYMMDD') END)) END) END AS  FUSEDDAYS
,CASE WHEN((TO_DATE(BU.FCREATEDON,'YYYYMMDD'))-(TO_DATE(:STARDATE,'YYYYMMDD')))<0 THEN TO_NUMBER(TO_CHAR(TO_DATE(:STARDATE,'YYYYMMDD'),'YYYYMMDD')) ELSE TO_NUMBER(TO_CHAR(TO_DATE(BU.FCREATEDON,'YYYYMMDD'),'YYYYMMDD')) END AS FSTARTDATE
,CASE NVL(BU.FENDDATE,0) WHEN 0 THEN TO_NUMBER(TO_CHAR(TO_DATE(:ENDDATE,'YYYYMMDD'),'YYYYMMDD')) ELSE TO_NUMBER(TO_CHAR(TO_DATE(BU.FENDDATE,'YYYYMMDD'),'YYYYMMDD')) END FENDDATE
FROM LDB1_STORAGEBINLOCATION SB
INNER JOIN LDB1_STORAGETYPE ST ON ST.FSTORAGEID=SB.FSTORAGEID AND ST.FSITEID=SB.FSITEID 
LEFT OUTER JOIN LDB1_BIN_UTILIZATION BU ON ST.FSITEID=BU.FSITEID AND ST.FSTORAGEID=BU.FSTORAGEID AND SB.FBINID=BU.FBINID AND BU.FCREATEDON <=:ENDDATE AND (:STARDATE <=BU.FENDDATE OR NVL(BU.FENDDATE,0)=0)
WHERE SB.FSITEID=:SITEID AND ST.FSTATUS='A' AND SB.FSTATUS='A'

SELECT ST.FSTORAGEID,ST.FSTORAGETYPE,SB.FBINID
,CASE WHEN BU.FCREATEDON IS NULL THEN 0 ELSE (CASE WHEN((TO_DATE(BU.FCREATEDON,'YYYYMMDD'))-(TO_DATE(:STARDATE,'YYYYMMDD')))<0 THEN TO_NUMBER(TO_CHAR(TO_DATE(:STARDATE,'YYYYMMDD'),'YYYYMMDD')) ELSE TO_NUMBER(TO_CHAR(TO_DATE(BU.FCREATEDON,'YYYYMMDD'),'YYYYMMDD')) END) END AS FSTARTDATE
,MAX(CASE WHEN BU.FCREATEDON IS NULL THEN 0 ELSE (CASE NVL(BU.FENDDATE,0) WHEN 0 THEN TO_NUMBER(TO_CHAR(TO_DATE(:ENDDATE,'YYYYMMDD'),'YYYYMMDD')) ELSE TO_NUMBER(TO_CHAR(TO_DATE(BU.FENDDATE,'YYYYMMDD'),'YYYYMMDD')) END) END) FENDDATE
FROM LDB1_STORAGEBINLOCATION SB
INNER JOIN LDB1_STORAGETYPE ST ON ST.FSTORAGEID=SB.FSTORAGEID AND ST.FSITEID=SB.FSITEID 
LEFT OUTER JOIN LDB1_BIN_UTILIZATION BU ON ST.FSITEID=BU.FSITEID AND ST.FSTORAGEID=BU.FSTORAGEID AND SB.FBINID=BU.FBINID AND BU.FCREATEDON <=:ENDDATE AND (:STARDATE <=BU.FENDDATE OR NVL(BU.FENDDATE,0)=0)
WHERE SB.FSITEID=:SITEID AND ST.FSTATUS='A' AND SB.FSTATUS='A'
GROUP BY ST.FSTORAGEID,ST.FSTORAGETYPE,SB.FBINID,CASE WHEN BU.FCREATEDON IS NULL THEN 0 ELSE (CASE WHEN((TO_DATE(BU.FCREATEDON,'YYYYMMDD'))-(TO_DATE(:STARDATE,'YYYYMMDD')))<0 THEN TO_NUMBER(TO_CHAR(TO_DATE(:STARDATE,'YYYYMMDD'),'YYYYMMDD')) ELSE TO_NUMBER(TO_CHAR(TO_DATE(BU.FCREATEDON,'YYYYMMDD'),'YYYYMMDD')) END) END

How to get the last 24 hour dated records using oracle:
string currentDateTime = currentDate + " " + currentTime.ToString().PadLeft(6, '0');
AND ((TO_DATE(:CURRENTDATETIME,'YYYYMMDD HH24MISS') - TO_DATE(FDBDATE || ' ' || LPAD(FDBITIME,6,0),'YYYYMMDD HH24MISS') ) * 24)>=24

Final Query:
SELECT ST.FSTORAGEID,ST.FSTORAGETYPE,SB.FBINID
,SUM((CASE WHEN BU.FCREATEDON IS NULL THEN 0 ELSE ((CASE NVL(BU.FENDDATE,0) WHEN 0 THEN TO_DATE(:ENDDATE,'YYYYMMDD') ELSE TO_DATE(BU.FENDDATE,'YYYYMMDD') END)-(CASE WHEN(TO_DATE(BU.FCREATEDON,'YYYYMMDD')-TO_DATE(:STARDATE,'YYYYMMDD'))<0 THEN TO_DATE(:STARDATE,'YYYYMMDD') ELSE TO_DATE(BU.FCREATEDON,'YYYYMMDD') END))+1 END)) AS FUSEDDAYS
FROM LDB1_STORAGEBINLOCATION SB
INNER JOIN LDB1_STORAGETYPE ST ON ST.FSTORAGEID=SB.FSTORAGEID AND ST.FSITEID=SB.FSITEID 
LEFT OUTER JOIN LDB1_BIN_UTILIZATION BU ON ST.FSITEID=BU.FSITEID AND ST.FSTORAGEID=BU.FSTORAGEID AND SB.FBINID=BU.FBINID AND BU.FCREATEDON <=:ENDDATE AND (:STARDATE <=BU.FENDDATE OR NVL(BU.FENDDATE,0)=0)
WHERE SB.FSITEID=:SITEID AND ST.FSTATUS='A' AND SB.FSTATUS='A'
GROUP BY ST.FSTORAGEID,ST.FSTORAGETYPE,SB.FBINID

Calculate Date differences in oracle in format of 'YYYYMMDD': 
SELECT (TO_NUMBER(TO_CHAR(TO_DATE(NVL(20180509,0),'YYYYMMDD'),'YYYYMMDD'))-TO_NUMBER(TO_CHAR(TO_DATE(NVL(20180101,0),'YYYYMMDD'),'YYYYMMDD'))) AS DAR FROM DUAL--wrong days difference 408
SELECT (TO_DATE(NVL(20180509,0),'YYYYMMDD')-TO_DATE(NVL(20180101,0),'YYYYMMDD')) AS DAR FROM DUAL--proper value for difference in days=>128
SELECT (20180509-20180101) AS DAR FROM DUAL--wrong days difference 408

The sequence of execution in sql query:
https://stackoverflow.com/questions/1130062/what-is-the-execution-sequence-of-group-by-having-and-where-clause-in-sql-serve

How to use datareader in DAL part:
public static int mETHIOD(Database db, int siteID, int storageID)
        {
            IDataReader datareaderBinCount = null;
            try
            {

                StringBuilder sqlCmdBuilder = new StringBuilder();
                
                DbCommand dbCmd = db.GetSqlStringCommand(sqlCmdBuilder.ToString());
                dbCmd.CommandType = CommandType.Text;

                db.AddInParameter(dbCmd, ":", DbType.Int32, );
                db.AddInParameter(dbCmd, ":", DbType.AnsiString, );

                int binsER = 0;
                using (datareaderBinCount = db.ExecuteReader(dbCmd))
                {
                    if (datareaderBinCount.Read())
                        if (!System.DBNull.Value.Equals(datareaderBinCount["COULMN"]))
                            binsER = datareaderBinCount.GetInt32(datareaderBinCount.GetOrdinal("COULMN"));
                }
                datareaderBinCount.Close();
                return binsER;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (datareaderBinCount != null && !datareaderBinCount.IsClosed)
                    datareaderBinCount.Close();
            }
        }

How to use main table row data as an input to where clause subquery:
SELECT H.FVBELN FROM LDB1_DELIVERY_HDR H");
INNER JOIN LDB1_DISPATCH_CONFIG DC ON DECODE(NVL(H.FAREACODE,'E'),'E',H.FVSTEL,H.FAREACODE) = DC.FDIS_KEYVALUE AND DC.FSITEID=H.FSITEID AND DC.FSITEID=:SITEID AND DC.FSTATUS ='A'
WHERE H.FHDRSTATUS='G' AND H.FSITEID=:SITEID AND H.FPICKSTARTDATE > TO_NUMBER(TO_CHAR(SYSDATE - 30,'yyyymmdd')) ");
/* Remove group delivery orders if any one order in group not in 'G' */
AND (SELECT COUNT(DISTINCT HT.FVBELN) FROM LDB1_DELIVERY_PICKER P  ");
INNER JOIN LDB1_DELIVERY_PICKER PT ON P.FGROUPNAME=PT.FGROUPNAME AND P.FDIS_AREAID=PT.FDIS_AREAID AND P.FSITEID=PT.FSITEID ");
INNER JOIN LDB1_DELIVERY_HDR HT ON PT.FVBELN=HT.FVBELN AND HT.FSITEID=:SITEID ");

WHERE P.FVBELN=H.FVBELN AND P.FSITEID=H.FSITEID AND DC.FDIS_AREAID=P.FDIS_AREAID AND HT.FHDRSTATUS<>'G')=0 ");

While reading from excel to datatable with OLEDB dll there is a limit of 256 columns to read at a time to fix this:
Using name ranged cells.
https://stackoverflow.com/questions/2766466/how-to-read-more-than-256-columns-from-an-excel-file-2007-format-using-oledb

How to check condition based data exist in child table or not?
CASE
  WHEN EXISTS
  (Select * from employee=PI.empID)
  THEN  'K'
  ELSE 'C'
END

PI.empID = parent row

Update one table by selecting from another table with multiple column:
http://technicalconsultancybyom.blogspot.com/2016/12/oracle-query-writing-tutorials.html

How to install 12C:
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/Windows_DB_Install_OBE/Installing_Oracle_Db12c_Windows.html

https://www.youtube.com/watch?v=ejkzNVOTfWU

How to uninstall 12C:
https://www.youtube.com/watch?v=lyN9aJfMlzQ&t=185s

Oracle 12C client 32 bit installing facing error:
[INS-10002] Inventory initialization failed
https://community.oracle.com/thread/3725625?start=0&tstart=0

I ran into this same issue when installing a 32-bit Oracle Admin Client in preparation for installing a 32-bit JD Edwards Deployment Server.  Using regedit, I copied the 64-bit Oracle database setting for "inst_loc" to the WOW6432Node\Oracle registry entry.  I found the correct value for "inst_loc" from the 64-bit Oracle entry here:

[HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE]

inst_loc      C:\Program Files\Oracle\Inventory

I copied this path and put it into a new string value in this registry entry:

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ORACLE]

inst_loc      C:\Program Files\Oracle\Inventory

Once both registry settings were pointing to the same Oracle Inventory, this error went away.

Copying data into another table from table of same structure :
https://community.oracle.com/thread/611474

create table TABLE2 as select * from TABLE1 where 1=2;--ONLY STRUCTURE

create table TABLE2 as select * from TABLE1;                  --STRUCTURE WITH DATA

Count number of occurrences of values in a single column at Oracle :

https://stackoverflow.com/questions/4729825/oracle-sql-count-number-of-occurrences-of-values-in-a-single-column

SELECT column,COUNT(*) AS NUMINSTANCES  FROM table GROUP BY column

How to set a value to a sequence in oracle:

ALTER SEQUENCE sample_seq INCREMENT BY -500;

SELECT sample_seq.NEXTVAL FROM dual;

ALTER SEQUENCE sample_seq INCREMENT BY 1;

How to convert any datatype into NCLOB OR CLOB:
https://stackoverflow.com/questions/13402510/oracle-changing-varchar2-column-to-clob

ALTER TABLE some_table ADD (foo CLOB);
UPDATE some_table SET foo = old_column;

Commit;
ALTER TABLE some_table DROP COLUMN old_column;
ALTER TABLE some_table RENAME COLUMN foo TO old_column;

How to export oracle 12c data into 11gr2 importable dmp file:

1. https://oracle-base.com/articles/10g/oracle-data-pump-10g

2. C:\Users\Administrator>expdp user/password@DBName VERSION=11.2 full=Y DI

RECTORY=data_pump_dir DUMPFILE=filename.dmp LOGFILE=emp2.log;

3. https://stackoverflow.com/questions/31040847/oracle-12c-datapump-error-when-exporting-from-11g-database

Foreign key reference in which table?:

select fk.table_name

from   user_constraints fk

join   user_constraints pk

on     pk.constraint_name = fk.r_constraint_name

and    pk.owner = fk.owner

where  fk.constraint_name = 'FK_KEYNAME';

How to import from oracle 12c exported data into 11gr2 importable dmp file :

https://stackoverflow.com/questions/6463614/how-to-import-an-oracle-database-from-dmp-file-and-log-file

impdp user/password@DBName full=Y DIRECTORY=data_pump_dir DUMPFILE=filename.dmp LOGFILE=emp2.log;

How to delete top 50000 records from table:

DELETE FROM MYTABLE WHERE ROWID IN (SELECT ROWID FROM MYTABLE WHERE FCONDITION<>63 FETCH FIRST 50000 ROWS ONLY);

How to drop multiple tables in one short:

BEGIN

  FOR rec IN

    (

      SELECT table_name

      FROM USER_TABLES

      WHERE TABLE_NAME LIKE '%table_%' AND TABLE_NAME NOT IN('table_63','table_63_TEMP','table_ARCH_63','tableABC','tableCDE')

    )

  LOOP

    EXECUTE immediate 'DROP TABLE  '||rec.table_name || ' PURGE';

  END LOOP;

END;

How to drop multiple sequences in one short:

BEGIN

  FOR rec IN

    (

      SELECT SEQUENCE_NAME

      FROM USER_SEQUENCES

      WHERE SEQUENCE_NAME LIKE '%SEQUENCE_ABC_%' AND SEQUENCE_NAME NOT IN('SEQUENCE_ABC_63_ID')

    )

  LOOP

    EXECUTE immediate 'DROP SEQUENCE  '||rec.SEQUENCE_NAME ;

  END LOOP;

END;

How to create multiple sequence at a time:
BEGIN
FOR rec IN
(
SELECT SEQUENCE_NAME
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME IN('seq1_ID' ,'seq2_ID' )
)
LOOP
EXECUTE immediate 'CREATE SEQUENCE '||rec.SEQUENCE_NAME||' START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE' ;
END LOOP;
END;

Store all the sequence in a temp table and then create all the sequences and then drop that table:
BEGIN
FOR rec IN
( SELECT TEMPDATA FROM TEMP )
LOOP
EXECUTE immediate 'CREATE SEQUENCE '||rec.TEMPDATA||' START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE' ;
END LOOP;
END;

Explain Plan in Oracle:
Step-1:
explain plan for
select * from tablename;

Step-2:
select * from table(dbms_xplan.display);

How to extend table space by creating a new datafile under the same table name space:

(ORA-01658: Unable to create INITIAL extent for segment in tablespace SYSTEM)

SELECT TABLESPACE_NAME, FILE_NAME
, BYTES / 1024 / 1024 AS BYTES_MB, AUTOEXTENSIBLE
, MAXBYTES / 1024 / 1024 AS MAXBYTES_MB
FROM DBA_DATA_FILES;


ALTER TABLESPACE SYSTEM ADD
DATAFILE 'E:\ORACLE\APP\ORACLE\ORADATA\XE\SYSTEM123.DBF'
SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED;

How to fetch records on a tree structure:

SELECT FLOCATIONID, FLOCATIONNAME, FPARENTLOCATIONID, LEVEL AS FTREELEVEL FROM LDB1_MAINT_LOCATIONS
WHERE FSITEID=:SITEID AND FSTATUS='A'
START WITH FPARENTLOCATIONID = :LOCATIONID
CONNECT BY FPARENTLOCATIONID = PRIOR FLOCATIONID
ORDER SIBLINGS BY FLOCATIONID

https://oracle-base.com/articles/misc/hierarchical-queries
How to fetch records in a reverse tree structure:
SELECT CONNECT_BY_ROOT FLOCATIONID AS FTOPPARENTLOCTIONID
FROM LDB1_MAINT_LOCATIONS
WHERE FLOCATIONID = :locationID
CONNECT BY PRIOR FLOCATIONID = FPARENTLOCATIONID

How to give create view access:Login as SYS/SYSDBA user & give this access
GRANT CREATE VIEW TO DBA_USERNAME;

MySQL upgrade to 8.0.18:
-----------------------------------------

MySQL (8.0.18) is compatible with .net framework 4.5.2 onwards.

Latest MySQL (8.0.18) community versions list:

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/

MySQL latest (8.0.18) workbench

https://dev.mysql.com/downloads/workbench/

MySQL latest (8.0.18) connector(client)

https://dev.mysql.com/downloads/connector/net/

How to upgrade MySQL (8.0.18) Tutorial link:

https://www.youtube.com/watch?v=B62zFccDvOQ

https://www.urtech.ca/2019/01/solved-video-complete-instructions-how-to-upgrade-mysql-5-1-to-5-5-to-5-6-to-5-7/

MySQL import data error

https://stackoverflow.com/questions/50336378/variable-sql-mode-cant-be-set-to-the-value-of-no-auto-create-user

Determine which .NET Framework versions are installed

https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/how-to-determine-which-versions-are-installed

MySQL Connector/NET (Archived Versions):

https://downloads.mysql.com/archives/c-net/

Mysql Tricks:
#How to take a individual db table's backup
C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin>mysqldump -u [username] -p [dbname] [table1] [table2] > c:\filename.sql
note:password will be entered in next line

#How to take a individual db backup
C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin>mysqldump -u [username] -p [dbname] > c:\filename.sql
note:password will be entered in next line

#find-all-stored-procedures-that-reference-a-specific-table  
https://stackoverflow.com/questions/45454709/mysql-find-all-stored-procedures-that-reference-a-specific-table
         
SELECT *
FROM Mysql.proc
WHERE type = 'PROCEDURE'
AND body LIKE '%tablename%';

#Connection String Explanation:
https://www.dofactory.com/reference/connection-strings

#Persist Security Info= true, we should remove in case of sql server authentication.

MySql Upgrade from  version 5 to version 8:
MySQL (8.0.18) is compatible with .net framework 4.5.2 onwards.
Determine which .NET Framework versions are installed
https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/how-to-determine-which-versions-are-installed

Latest MySQL (8.0.18) community versions list:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/
note: while installing server itself we can also add workbench to install.

MySQL latest (8.0.18) workbench
https://dev.mysql.com/downloads/workbench/

MySQL latest (8.0.18) connector(client)
https://dev.mysql.com/downloads/connector/net/

How to upgrade MySQL (8.0.18) Tutorial link:
https://www.youtube.com/watch?v=B62zFccDvOQ
https://www.urtech.ca/2019/01/solved-video-complete-instructions-how-to-upgrade-mysql-5-1-to-5-5-to-5-6-to-5-7/

How to upgrade MySQL from 5.1.31 to 8.0.18:
By default while installing 8.0.18 MySQL Database. It won’t show to upgrade directly to any major versions upgrade like from 5.1.31 to 8.0.18.
Currently it showed we can upgrade to 5… versions only.
So we can cancel this process from modal popup.
Then in MySQL Installer modal popup will come with “Add” option. There we need to click and continue.
 We can see how to perform this from the above video link.
-------------------------------------------------------------------------------------------------------------------------------------
MySQL import data error
https://stackoverflow.com/questions/50336378/variable-sql-mode-cant-be-set-to-the-value-of-no-auto-create-user
MySQL restores database issue when we restore the big binary file as blob data to the MySQL database:
https://stackoverflow.com/questions/8062496/how-to-change-max-allowed-packet-size

Determine which .NET Framework versions are installed
https://docs.microsoft.com/en-us/dotnet/framework/migration-guide/how-to-determine-which-versions-are-installed

File location to making this change
"C:\Program Files (x86)\MySQL\MySQL Server 5.1\my.ini" if older version of 5 is installed
otherwise, "C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"

--

1. my.inf changes
max_allowed_packet = 1000M
max_prepared_stmt_count = 22000
net_buffer_length = 22000

May not require
log-bin-trust-function-creators=1
log_bin_trust_function_creators=1

2.varchar(21000)
REPLACE BY
varchar(16383)

2. CREATE DEFINER=`root`@`%` FUNCTION `xyz`(str TEXT, delim VARCHAR(124)) RETURNS text CHARSET latin1

REPLACE BY
CREATE DEFINER=`root`@`%` FUNCTION ` xyz `(str TEXT, delim VARCHAR(124)) RETURNS text CHARSET latin1
READS SQL DATA
DETERMINISTIC

#Mysql DB Export:
from cmd

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump.exe --user=root --password=novellus --host=localhost --port=3306 "sakila" --routines --triggers > results123456.sql 

from workbench:
Step-1:
Go to Data export.

Step-2:
In object, selection select a single database.

step-3:
In objects to export check all three 1)dum stored procedures and functions. 2) Dump events. 3)Dump Triggers.

Step-4:
Select exports to self-contained files.

Step-5:
Check Create dump in a single transaction 

Step-6:
Uncheck Include create schema.

#Mysql DB Import:

Step-1:

Import from self-contained file.

Click on the new button to create a new schema from UI and then import it.

How to get all the tables list from mysql database:
https://dataedo.com/kb/query/mysql/list-of-tables-in-the-database
select table_schema as database_name, table_name from information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'database_name' -- enter your database name here order by database_name, table_name;


https://stackoverflow.com/questions/18965980/exit-single-user-mode

-- Start in master
USE MASTER;

-- Add users
ALTER DATABASE [my_db] SET MULTI_USER
GO

https://www.mssqltips.com/sqlservertip/5460/sql-server-database-stuck-in-restoring-state/
RESTORE DATABASE [earnings] WITH RECOVERY