SQL - Yash-777/LearnJava GitHub Wiki

-- 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 TABLE_NAME,
  ( SELECT MAX(versionColumn) as max from TABLE2 where column='Someval') as outcome
  SET
  columnChange = 'val'
  WHERE
  version = outcome.max and id = 7

SELECT columns1, columns2, SUM(columns3)
FROM Mytable
GROUP BY columns1, columns2
HAVING columns1>0

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>

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

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

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'

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')

-- 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;

-- 🧹 Bonus — Detect & Clean Existing Zero Dates
SELECT id, product_name, manufactured_date, created_on
FROM products
WHERE CAST(manufactured_date AS CHAR) LIKE '0000-00%'
   OR CAST(created_on AS CHAR) LIKE '0000-00%';

-- SQL Error: 0, SQLState: S1009  - Zero date value prohibited
-- Replaces invalid datetime values ('0000-00-00', '0000-00-00 00:00:00', '0000-00-00 00:00:00.000000') with NULL
UPDATE products
SET
    manufactured_date = CASE WHEN CAST(manufactured_date AS CHAR) LIKE '0000-00%' THEN NULL ELSE manufactured_date END,
    created_on = CASE WHEN CAST(created_on AS CHAR) LIKE '0000-00%' THEN NULL ELSE created_on END
WHERE
    CAST(manufactured_date AS CHAR) LIKE '0000-00%'
 OR CAST(created_on AS CHAR) LIKE '0000-00%';

SET @table = 'Account';
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = @table
  AND DATA_TYPE IN ('date', 'datetime', 'timestamp');