Skip to content

SQL

Jump to bottom
Yash edited this page Mar 24, 2020 · 10 revisions

Online Fiddles

  • Second Highest salary
-- LIMIT 0 , 30 « Start row: 0, Number of rows: 30 
select DISTINCT salary from Emp order by salary DESC LIMIT 1,1; // 3,1 for 4th highest

select MAX(salary) from Emp where salary NOT IN (select MAX(salary) from Emp)

create table Employee (EmployeeID  int,  Salary int);

insert into Employee values (3, 200);
insert into Employee values (4, 800);
insert into Employee values (7, 450);


SELECT MAX(Salary) FROM Employee 
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

SELECT MAX(Salary) FROM Employee WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee);

-- MySQL
-- Highest 0,1 Second highest 1,1
SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 0,1;
SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1,1;

-- Limit coun  to return, Offset row start
SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 0;
SELECT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 2;
SELECT * FROM TABLENAME WHERE column in ('val1','val2','val3')
SELECT * FROM TABLENAME WHERE column like '%val1'
  • Update with Select
UPDATE TABLE_NAME,
  ( SELECT MAX(versionColumn) as max from TABLE2 where column='Someval') as outcome
  SET
  columnChange = 'val'
  WHERE
  version = outcome.max and id = 7

A short description of GROUP BY Clause

The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns. The aggregate function columns are not included in GROUP BY cluse, I mean to say the columns that used the aggregate function is not included in the GROUP BY cluse.

The HAVING cluse is used to filters the GROUP BY cluse.

Example:

SELECT columns1, columns2, SUM(columns3)
FROM Mytable
GROUP BY columns1, columns2
HAVING columns1>0
  • SQL CASE Statements is the closest to IF in SQL and is supported on all versions of SQL Server
SELECT CASE <variable> 
            WHEN <value>      THEN <returnvalue>
            WHEN <othervalue> THEN <returnthis>
                              ELSE <returndefaultcase>
       END AS <newcolumnname>
FROM <table>
-- (OR ) To cast for appropriate type
SELECT CAST(CASE <variable> 
            WHEN <value>      THEN <returnvalue>
            WHEN <othervalue> THEN <returnthis>
            ELSE <returndefaultcase>
       END AS columnType) as <newcolumnname>
FROM <table>

Get data form three tables.

Project_Module:

P_ID M_ID
1 2
7 1

Project:

P_ID Name
1 Loing
7 Check Out

Module:

M_ID Name
2 Possitive
1 Negative

Outer Join example

SELECT pm.`M_ID` , p.`NAME` , m.`NAME` 
FROM
`Project_Module` pm
JOIN  `Project`  p   ON ( pm.`P_ID` = p.`P_ID` ) 
JOIN  `Module`   m   ON ( pm.`M_ID` = m.`M_ID` ) 
WHERE pm.`P_ID` = 7

DUMP

SELECT `dom` FROM `objectTable` WHERE `pagename` = 'alstate' and `projectid` = '7' and `addedby` = '7' 
and `version` = (SELECT MAX(`version`) as maxvalue FROM `objectTable` WHERE `pagename` = 'alstate') LIMIT 0, 1


UPDATE `objectTable`, (SELECT `dom` as id FROM `objectTable` WHERE `pagename` = 'alstate' and `projectid` = '7' 
and `addedby` = '7' and `version` = (SELECT MAX(`version`) as maxvalue FROM `objectTable` WHERE `pagename` = 'alstate')
 LIMIT 0, 1) as crosscheck SET `dom` = '12121' WHERE crosscheck.id = '0' AND `pagename` = 'alstate'

UPDATE `objectTable`, 
(SELECT MAX(`version`) as maxvalue FROM `objectTable` WHERE `pagename` = 'alstate') as version ,
(SELECT `dom` as id FROM `objectTable` WHERE `pagename` = 'alstate' and `projectid` = '7' and `addedby` = '7' and 
`version` =  (SELECT MAX(`version`) as maxvalue FROM `objectTable` WHERE `pagename` = 'alstate') ) as crosscheck SET `dom`
 = '12121' WHERE crosscheck.id = '12121' AND `pagename` = 'alstate' AND `version` = version.maxvalue

UPDATE `objectTable`, (SELECT MAX(`version`) as maxvalue FROM `objectTable` WHERE `pagename` = ? ) as version, (SELECT 
`dom` as id FROM `objectTable` WHERE `pagename` = ? and `projectid` = ? and `addedby` = ? and `version` =  (SELECT 
MAX(`version`) as maxvalue FROM `objectrepository_temp` WHERE `pagename` = ?) ) as crosscheck SET `dom` = ? WHERE 
crosscheck.id = '0' AND `pagename` = ? AND `version` = version.maxvalue

SQL Statement using Where clause with multiple values 

 PersonName      SongName    Status 
 Holly           Highland    Complete
 Holly           Mech        Complete 
 Ryan            Highland    Complete

select songName from t where personName in ('Ryan', 'Holly') group by songName having count(distinct personName) = 2

select songName from t where personName in ('Ryan', 'Holly') and status = 'Complete'

How to check RMAN backup status and timings Stackpost

V$RMAN_BACKUP_JOB_DETAILS displays details about backup jobs.

select d.name, to_char(START_TIME,'dd-mon-yyyy hh24:mi:ss') start_time, 
  to_char(END_TIME,'dd-mon-yyyy hh24:mi:ss') end_time, ELAPSED_SECONDS, time_taken_display, INPUT_TYPE 
  from   v$rman_backup_job_details, v$database d
  where  INPUT_TYPE = 'DB INCR'
  order  by to_date(start_time,'dd-mon-yyyy hh24:mi:ss');
  
select d.name, to_char(START_TIME,'dd-mon-yyyy hh24:mi:ss') start_time, 
  to_char(END_TIME,'dd-mon-yyyy hh24:mi:ss') end_time, ELAPSED_SECONDS, time_taken_display, INPUT_TYPE 
  from   v$rman_backup_job_details, v$database d
  where  INPUT_TYPE like '%DB%' 
  order  by to_date(start_time,'dd-mon-yyyy hh24:mi:ss') desc
  FETCH FIRST 1 ROWS ONLY;
  -- OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
  
select d.name from v$database d;
select max(start_time) as max_start_time from v$rman_backup_job_details;
CREATE TABLE YASH_INFO
  (
    "ID"   NUMBER NOT NULL ENABLE,
    "NAME" VARCHAR2(500 BYTE),
    "TEXTFILE" CLOB,
    "BLOBFILE" BLOB,
    "DATEINSERTION" DATE,
    "TIMESTAMPDATE" TIMESTAMP (6),
    "CONTENTTYPE" VARCHAR2(50 BYTE)
  );

SELECT SYSDATE FROM DUAL;
SELECT SYSTIMESTAMP FROM DUAL;

SELECT (SYSDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 FROM DUAL; -- 1584029850000

-- Convert current time to epoch.
select ((cast (systimestamp at time zone 'UTC' as date) - date '1970-01-01') * 86400) as epoch from dual;
-- Convert hard-coded timestamp to epoch.
select ((cast (timestamp '2019-12-31 23:59:59' at time zone 'UTC' as date) - date '1970-01-01') * 86400) as epoch from dual;

SELECT TIMESTAMPDATE, to_char(TIMESTAMPDATE, 'YYYYMMDD') as sot from YASH_INFO order by TIMESTAMPDATE, sot asc;
select ((cast (TIMESTAMPDATE at time zone 'UTC' as date) - date '1970-01-01') * 86400) as epoch from YASH_INFO;

-- ======================
SELECT * from YASH_INFO order by TIMESTAMPDATE asc;
SELECT * from YASH_INFO order by TIMESTAMPDATE, ((cast (TIMESTAMPDATE at time zone 'UTC' as date) - date '1970-01-01') * 86400) asc;

SELECT * from YASH_INFO order by DATEINSERTION asc;
-- ======================

SELECT TIMESTAMPDATE, CONVERT(VARCHAR(6), TIMESTAMPDATE, 112) as sot from YASH_INFO order by TIMESTAMPDATE, sot asc;

select (cast(sys_extract_utc(current_timestamp) as date) - TO_DATE('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400 
  as gmt_epoch from dual;

SELECT TIMESTAMPDATE, ( CAST(FROM_TZ( CAST( TIMESTAMPDATE AS TIMESTAMP ), 'Europe/Berlin'  ) AT TIME ZONE 'UTC'  AS DATE  )*24*60*60) as epoch 
  from YASH_INFO order by TIMESTAMPDATE, epoch asc;

SELECT * from YASH_INFO order by ((TIMESTAMPDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000) asc;
 
SELECT (TIMESTAMPDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 as aa from YASH_INFO;
 
SELECT * from YASH_INFO order by (cast(sys_extract_utc(TIMESTAMPDATE) as date) - TO_DATE('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) * 86400 asc;

SELECT * from YASH_INFO order by TIMESTAMPDATE, TO_DATE(to_char(TIMESTAMPDATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') asc;
SELECT TIMESTAMPDATE, TO_DATE(to_char(TIMESTAMPDATE, 'DD-MON-YYYY'), 'DD-MON-YYYY') as sot from YASH_INFO order by TIMESTAMPDATE, sot asc;
SELECT * from YASH_INFO order by DATEINSERTION asc;

SELECT * from YASH_INFO ORDER BY cast(DATEINSERTION, datetime) ASC;

SELECT CONVERT(DATEINSERTION, SWITCHOFFSET(CONVERT(datetimeoffset, gETDATE()), DATENAME(TzOffset, gETDATE() 
  AT TIME ZONE 'Eastern Standard Time'))) FROM YASH_INFO order by DATEINSERTION asc;

INSERT INTO "NEON30"."YASH_INFO" (ID, NAME, DATEINSERTION, TIMESTAMPDATE) VALUES ('5', 'test', TO_DATE('12-MAR-20 15.21.43',
   'DD-MON-RR HH24.MI.SS'), TO_TIMESTAMP('08-FEB-20 11.21.49.847000000 PM', 'DD-MON-RR HH.MI.SS.FF AM'))
INSERT INTO "NEON30"."YASH_INFO" (ID, NAME, DATEINSERTION, TIMESTAMPDATE) VALUES ('6', 'test', TO_DATE('12-MAR-20 15.21.43',
   'DD-MON-RR HH24.MI.SS'), TO_TIMESTAMP('08-FEB-20 11.21.49.847000000 AM', 'DD-MON-RR HH.MI.SS.FF AM'))

INSERT INTO "NEON30"."YASH_INFO" (ID, NAME, TEXTFILE, DATEINSERTION, TIMESTAMPDATE, CONTENTTYPE) VALUES ('1', 'test.xml',
   '<xml> </xml>', TO_DATE('07-FEB-20', 'DD-MON-RR'), TO_TIMESTAMP('07-FEB-20 11.01.36.408000000 AM', 'DD-MON-RR HH.MI.SSXFF AM'), 'text/xml')
INSERT INTO "NEON30"."YASH_INFO" (ID, NAME, BLOBFILE, DATEINSERTION, TIMESTAMPDATE, CONTENTTYPE) VALUES ('2', 'jira.xlsx',
   empty_blob(), TO_DATE('07-FEB-20', 'DD-MON-RR'), TO_TIMESTAMP('07-FEB-20 11.04.26.831000000 AM', 'DD-MON-RR HH.MI.SSXFF AM'), 'application/vnd.ms-excel')
SELECT BLOBFILE FROM "NEON30"."YASH_INFO" WHERE ROWID='AAAvD2AAnAAAAQ1AAB' AND ORA_ROWSCN='8662269138106' FOR UPDATE

TO_DATE('07-FEB-20', 'DD-MON-RR'), TO_TIMESTAMP('07-FEB-20 11.01.36.408000000 AM', 'DD-MON-RR HH.MI.SSXFF AM')

UPDATE SET field_name = REPLACE(field_name , 'oldstring', 'newstring') and File Upload from server path.

-- test.xml
update YASH_INFO t 
set t.name = REPLACE(t.name, substr(t.name, instr(t.name,'.xml')), 'Sample.xml')
where ID = 1; -- testSample.xml

-- test_Sample.xml
update YASH_INFO t 
set t.name=replace(t.name, 'Sample', '_Sample')
where ID = 1;

In a class-based object-oriented language, in general, state is carried by instances, methods are carried by classes, and inheritance is only of structure and behavior. Basic, Refactoring Techniques

Method signature: It consists of method name and parameter list (number/type/order of the parameters). methodName(parametersList y). An instance method in a subclass with the same signature and return type as an instance method in the super-class overrides the super-class's method.

Java OOP concepts
Class  - Collection of a common features of a group of object [static/instance Fields, blocks and Methods]
Object - Instance of a class (instance fields)
Abstraction - Process of hiding complex info and providing required info like API, Marker Interfaces ...
Encapsulation(Security) - Class Binding up with data members(fields) and member functions.
Inheritance  (Reusability by placing common code in single class)
 1. Multilevel - {A -> B -> C} 2. Multiple - Diamond problem {A <- (B) -> C} [Java not supports] 3. Cyclic {A <-> B} [Java not supports]
 * Is-A   Relation - Class A extends B
 * Hash-A Relation - Class A { B obj = new B(); } - (Composition/Aggregation)
Polymorphism (Flexibility) 1. Compile-Time Overloading    2. Runtime Overriding  [Greek - "many forms"]


int[] arr = {1,2,3}; int arrLength = arr.length;   // Fixed length of sequential blocks to hold same data type
String str = "Yash"; int strLength = str.length(); // Immutable Object value can't be changed.

List<?> collections = new ArrayList<String>(); int collectionGroupSize = collections.size();
Map<?, ?> mapEntry = new HashMap<String, String>();
Set<?> keySet = mapEntry.keySet(); // Set of Key's
Set<?> entrySet = mapEntry.entrySet(); // Set of Entries [Key, Value]

// Immutable Objects once created they can't be modified. final class Integer/String/Employee
Integer val = Integer.valueOf("100"); String str2 = String.valueOf(100); // Immutable classes
final class Employee { // All Wrapper classes, java.util.UUID, java.io.File ...
    private final String empName; // Field as Final(values can be assigned only once) Only getter functions.
    public Employee(String name) { this.empName = name; }
}

Native Java Code for Hashtable.h, Hashtable.cpp SQL API.

You can check your current JDK and JRE versions on your command prompt respectively,

  • JDK javac -version [C:\Program Files\Java\jdk1.8.0_121\bin] o/p: javac 1.8.0_121
  • JRE java -version [C:\Program Files\Java\jdk1.8.0_121\bin] o/P: java version "1.8.0_102"

JAVA_HOME - Must be set to JDK otherwise maven projects leads to compilation error. [ERROR] No compiler is provided in this environment. Perhaps you are running on a JRE rather than a JDK? C:\Softwares\OpenJDK\, 7-zip

Fatal error compiling: invalid target release: JRE and JDK must be of same version 1.8.0.XXX

Disable TLS 1.0 and 1.1security-libs/javax.net.ssl: TLS 1.0 and 1.1 are versions of the TLS protocol that are no longer considered secure and have been superseded by more secure and modern versions (TLS 1.2 and 1.3).

Core Java

Stack Posts

Clone this wiki locally