Friday, September 25, 2020

Sample Dummy Queries for Testing

use temp;
CREATE TABLE IF NOT EXISTS BOOKS (
  BOOK_ID INT(5) NOT NULL AUTO_INCREMENT,
  CATEGORY VARCHAR(30) NOT NULL,
  TITLE VARCHAR(80) NOT NULL,
  DESCRIPTIONS VARCHAR(200) NOT NULL,
  PRIMARY KEY (BOOK_ID)
);

INSERT INTO BOOKS (BOOK_ID, CATEGORY, TITLE, DESCRIPTIONS) VALUES(1, 'Java', 'Concurrency in Practice', 'Java Concurrency Book');
INSERT INTO BOOKS (BOOK_ID, CATEGORY, TITLE, DESCRIPTIONS) VALUES(2, 'Hibernate', 'Hibernate In Action', 'Hibernate In Action Learning');
INSERT INTO BOOKS (BOOK_ID, CATEGORY, TITLE, DESCRIPTIONS) VALUES(3, 'Spring', 'Spring In Action', 'Spring In Action Learning'); 
INSERT INTO BOOKS (BOOK_ID, CATEGORY, TITLE, DESCRIPTIONS) VALUES(4, 'C','Let Us C','C Programming Book');
INSERT INTO BOOKS (BOOK_ID, CATEGORY, TITLE, DESCRIPTIONS) VALUES(5, 'Java','Java for Advanced Learners', 'Java for Advanced Learners by Deepak Modi');
commit;
select * from BOOKS;

use temp;
CREATE TABLE DEPARTMENT(
    DID INTEGER(3) PRIMARY KEY, 
    DNAME VARCHAR(25)
);
CREATE TABLE JOB(
    JOBID INTEGER(3) PRIMARY KEY, 
    DESIGNATION VARCHAR(25)
);

CREATE TABLE EMPLOYEE(
    EID INTEGER(3) PRIMARY KEY, 
    ENAME VARCHAR(25), 
    SALARY INTEGER(8), 
    JOBID INTEGER(3) REFERENCES JOB(JOBID), 
    DID INTEGER(3) REFERENCES DEPARTMENT(DID) ON DELETE SET NULL
);

CREATE TABLE PROJECTS(
    PID INTEGER(3) PRIMARY KEY, 
    TITLE VARCHAR(25), 
    EID INTEGER(3) REFERENCES EMPLOYEE(EID) ON DELETE SET NULL
);

insert into DEPARTMENT values(1, 'MobilePayments');
insert into DEPARTMENT values(2, 'FRM');
insert into DEPARTMENT values(3, '3DS');
insert into DEPARTMENT values(4, 'OPERATIONS');
insert into DEPARTMENT values(5, 'BIZOPS');
insert into DEPARTMENT values(6, 'L1');
insert into DEPARTMENT values(7, 'DBA');
insert into DEPARTMENT values(8, 'PSE');

insert into JOB values(1, 'Developer');
insert into JOB values(2, 'Tester');
insert into JOB values(3, 'ProductionSupport');
insert into JOB values(4, 'Finance');
insert into JOB values(5, 'Banking');
insert into JOB values(6, 'Sales');
insert into JOB values(7, 'Marketing');
insert into JOB values(8, 'PSE');
insert into JOB values(9, 'REPORTS');

insert into EMPLOYEE values(1, 'Deepak Kumar Modi', 125000, 1, 1);
insert into EMPLOYEE values(2, 'Ajay Mahto', 80000, 1, 1);
insert into EMPLOYEE values(3, 'Ajay Ramu', 100000, 9, 4);
insert into EMPLOYEE values(4, 'Navaneeth Kumar', 130000, 9, 5);
insert into EMPLOYEE values(6, 'Manjunath S', 85000, 9, 5);
insert into EMPLOYEE values(7, 'Abhilash', 95000, 9, 4);
insert into EMPLOYEE values(8, 'Pavan K', 145000, 1, 1);
insert into EMPLOYEE values(9, 'Imran Khan', 75000, 8, 3);

insert into PROJECTS values(1, 'PayZapp_Project', 1);
insert into PROJECTS values(2, 'PayApt_Project', 2);
insert into PROJECTS values(3, 'Management_Project', 1);
insert into PROJECTS values(4, 'Report_Sharing_Project', 7);
insert into PROJECTS values(5, 'Prod_Support_Project', 9);

select * from temp.DEPARTMENT;
select * from temp.JOB;
select * from temp.EMPLOYEE;
select * from temp.PROJECTS;

Tuesday, September 8, 2020

ConnectTimeout ReadTimeout WriteTimeout

Message Hash Generation Code: public class HashGenerator { public static void main(String[] args) throws NoSuchAlgorithmException, UnsupportedEncodingException { MessageDigest messageDigest = java.security.MessageDigest.getInstance("SHA-256"); String raw="IN656912225942"; String x = new String(Base64.encode(messageDigest.digest(raw.getBytes("UTF-8")))); System.out.println(raw+" "+x); } } ConnectTimeout: 5 Seconds (Finding Host, who is the server) ReadTimeout: 5 Seconds (Reading the value from Server, Server is processing) WriteTimeout: 5 Seconds (Posting the request to Server like posting JSON, File Upload, depends on bandwidth)

Thursday, May 28, 2020

Split Column Data from Mysql


DROP TABLE W2A_FEE;

CREATE TABLE W2A_FEE (CSV_VALUE VARCHAR(100));

INSERT INTO W2A_FEE VALUES('2.85,9,9');

INSERT INTO W2A_FEE VALUES('2.00,9,9');

INSERT INTO W2A_FEE VALUES('2.00,9,9');

INSERT INTO W2A_FEE VALUES('3.5,9,9');

 

SELECT CSV_VALUE FROM W2A_FEE;

 

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(CSV_VALUE, ',', 1), ',', -1) AS Fee,
       SUBSTRING_INDEX(SUBSTRING_INDEX(CSV_VALUE, ',', 2), ',', -1) AS CGST,
       SUBSTRING_INDEX(SUBSTRING_INDEX(CSV_VALUE, ',', 3), ',', -1) AS SGST
FROM   W2A_FEE;