SQL Queries (Notes)

Assorted SQL Queries. If no SQL Server, Oracle, MySQL, or MS-SQL Server, is specified, consider the query generic.

• Beginner SQL Tutorial[1]
  
• JOIN Statements[2][3]
• SS64 Command-Line references or Oracle and MS-SQL Server[4]
  

View - Read Only

Adding a UNION ALL with a non-existent table to a view statement effectively makes that VIEW statement read-only because the non-existent table always fails to update.

-- This one fails every update
create view SampleView
as
  select ID, value from table
  union all
  select 0, '0' where 1=0

Assorted

-- select the TEXT that defines a VIEW, owner_name and view_name are ALL CAPS
SQL> select TEXT    
FROM DBA_VIEWS    
where OWNER = '<owner_name>'  
and VIEW_NAME  = '<view_name>';

-- fetch column headers as name
select utc.column_name 
  from all_tab_columns utc
 where upper(utc.TABLE_NAME) = '<some name>'
 order by utc.COLUMN_ID

--Fetch row count for each table in a namespace
select 
   table_name, 
   num_rows counter 
from 
   dba_tables 
where 
   owner = 'XXX'
order by 
   table_name;

Aggregate List

Use listagg to stitch together multiple results into one column.

In Sqlserver: 
SELECT FieldA , STUFF(( SELECT  ','+ FieldB FROM TableName a
WHERE b.FieldA = a.FieldA FOR XML PATH('')),1 ,1, '')  Members
FROM TableName b
GROUP BY FieldA;

In Mysql:
SELECT FieldA, GROUP_CONCAT(FieldB) AS Members
FROM  TableName 
GROUP BY FieldA
ORDER BY FieldA;

In Oracle:
SELECT FieldA, LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldA) AS Members
FROM TableName 
GROUP  BY  FieldA;

Count Words in a CLOB

Use REGEXP_COUNT to count the word occurrences' in SAMPLE_TABLE.NOTE where the NOTE column is a CLOB datatype.
For example:

select REGEXP_COUNT( regexp_replace(NOTE,'<.*?>'), 'Addendum') as text_count
from SAMPLE_TABLE

The REGEXP_REPLACE part also strips out HTML and XML code.

Using MAX as a Subquery

The MAX clause will select the highest value returned.

-- Fetches Patient's Last Diagnosis
select listagg(diag.description,',') within group ( order by description) 
from Table
where Table.ID = 
    (select MAX(table2.id) from table2 where Table.id = table2.id)

Users Running a Query

• Find users running a query. Change p.status to what you want. Any user not listed is idle or not logged in.

SELECT
    p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, r.command,
    p.program_name, text 
FROM
    sys.dm_exec_requests AS r,
    master.dbo.sysprocesses AS p 
    CROSS APPLY sys.dm_exec_sql_text(p.sql_handle)
WHERE
    p.status NOT IN ('sleeping', 'background') 
AND r.session_id = p.spid

PERL

Execute a query that returns an unknown number of columns.

    $sql_query = "select * from Table";
    $sth = $dbh->prepare($sql_query) or die "Couldn't prepare statement: " . $dbh->errstr;
    $sth->execute or die "Couldn't execute statement: " . $sth->errstr;
	
    while( my @row = $sth->fetchrow_array() ){
		foreach (@row) {
			$_ = "\t" if !defined($_);
			print "$_\t";
		}
		print "\n";
	} # end while

Foreign Keys

-- Finding Foreign Keys on a Table
select (select COLUMN_NAME from dba_cons_columns where constraint_name = ac.CONSTRAINT_NAME) as 'Column_Name',
ac.CONSTRAINT_NAME as 'Foreign_Key_Name', 
(select Table_Name from dba_cons_columns where constraint_name = ac.R_CONSTRAINT_NAME) as 'Foreign Table Name' ,
(select COLUMN_NAME from dba_cons_columns where constraint_name = ac.R_CONSTRAINT_NAME) as 'Foreign Column Name',
ac.R_CONSTRAINT_NAME as 'Foreign Key'
from all_constraints ac, 
where owner = 'DATABASE_OWNER' 
AND table_name = 'TABLE_NAME'  
AND constraint_type = 'R'

Time in Years

If you just want the difference in years, there's:

SELECT EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2) FROM mytable;

Or do you want fractional years as well?

SELECT (date1 - date2) / 365.242199 FROM mytable;

Query Wild Card

Pipe the % sign with the variable.

Grand Totals Across Multiple Columns

-- Produces Grand Totals across multiple columns
select 
 vsf.SALESMAN_NAME as Saleman_Name, 
 to_char( vsf.SALE_DATE, 'DD-Mon-YYYY HH24:MI' ) as Sale_Date,
 vsf.CUSTOMER_NAME as Customer_Name,
 vsf.SALE_ID as Sale_ID,
 vsf.SALE_TOTAL as Sale_Total,
       count( * ) as Sale_Count
from V_SALE_FINDER vsf
WHERE vsf.SALES_REGION_ID = US_SE
group by vsf.SALESMAN_NAME,
 rollup ((
	to_char( vsf.SALE_DATE, 'DD-Mon-YYYY HH24:MI' ),
	vsf.CUSTOMER_NAME,
	vsf.SALE_ID,
	vsf.SALE_TOTAL
	))

Rollup[5][6]

ROLLUP works like GROUP BY, but produces subtotals for each sub-group and a grand total, add count() to get numbers.
The DECODE clause inserts 'subtotal' where it should go.

   CASE GROUPING_ID (T.division_id )
   WHEN 0 THEN T.division_id
   WHEN 1 THEN T.division_id || ' Sub Total: '
   WHEN 3 THEN '{Grand Total}: '
    ELSE NULL
	END
	as DIV_ID,
    count(*) as COUNT
from TABLE T
group by rollup (T.division_id)
order by 1,2

Social Security Number (Formatting)

Format
substr(p.ssn,1,3) || '-' || substr(p.ssn,4,2) || '-' || substr(p.ssn,6,4) as SSN, (leaves -- for null values.)

DECODE( SSN, null, null, substr(SSN,1,3)||'-'||substr(SSN,4,2)||'-'||substr(SSN,6,4)) as SSN, (leaves null when no ssn is found)

Stored Procedures Fetch by User

-- Find Stored Procedures

SELECT *
  FROM SYS.DBA_PROCEDURES
  where procedure_name is not null
  and owner = 'USER_NAME'
  order by procedure_name

-- Fetch Stored Procedure text

SELECT *
FROM DBA_source
WHERE 
OWNER = 'USER_NAME'
AND UPPER(NAME) LIKE 'AAA%'

Escape Character

• Use single-quote ' for escape in a query. Example, "Salemans Notes: " for "Salesman's Notes: "
• Escape for ampersand. "Vial & Strength: " to "Vial ID '|| chr(38) ||' Strength: ". The chr(38) is the ampersand character.

Distinct

The distinct clause can be used across the entire SELECT clause with out braces to remove duplicates.

Stripping HTML from a CLOB

Oracle's dbms_lob.substr has a hard coded 4000 character limit. It only reads the first 4000 characters from a CLOB. The work-around is to create a function that sequentially links consecutive 4000 VARCHAR(2) strings together until the CLOB is fully extracted.

-- strip new line \n, from CLOB
--		\r, CR, CHR(13)
--		\n, LF, CHR(10)
 dbms_lob.substr( 
       regexp_replace(TABLE.CLOB_LOG, CHR(10), ' ')
  	   , 4000, 1 ) as LOG_column,

-- Fetches a CLOB and effectively strips out all HTML tags, replacing them with spaces, BEFORE applying the 4000 character dbms_lob.substr function
select
dbms_lob.substr( 
regexp_replace( 
regexp_replace(NOTE, '<[^>]*>',' ' )
,CHR(38)||'nbsp;',' ')
, 4000, 1 )
from TABLE

-- Fetches a CLOB and strips out all HTML tags. Replaces '<BR>' with carriage-return.
select
dbms_lob.substr( 
regexp_replace( 
regexp_replace( 
regexp_replace(note, '<BR>', CHR(13))
,'<[^>]*>',' ' )
,CHR(38)||'nbsp;',' ')
, 4000, 1 )
from TABLE

-- fetches the first date after "Sign" if the CLOB is < 4000 characters after stripping.
select
regexp_substr(
regexp_substr(
dbms_lob.substr( 
regexp_replace( 
regexp_replace(NOTE, '<[^>]*>',' ' )
,CHR(38)||'nbsp;',' ')
, 4000, 1 )
, 'Signed .+ ')
, '\d{2}/\d{2}/\d{2}')
from TABLE

Extract a Text String between Two Text Strings from a CLOB

-- fetches a char string from between 'STRING_ONE' and 'STRING_TWO' then strips out those tags.
select
regexp_replace(
regexp_substr( 
dbms_lob.substr(
regexp_replace( 
regexp_replace(note, '<[^>]*>',' ' )
,CHR(38)||'nbsp;',' ')
, 4000, 1 )
,'STRING_ONE(.*?)STRING_TWO' )
, 'STRING_ONE|STRING_TWO')
from TABLE

Partition By

The PARTITION BY clause sets the range of records that will be used for each "GROUP" within the OVER clause.

SELECT empno, deptno, COUNT(*) OVER (PARTITION BY deptno) DEPT_COUNT FROM emp

String Functions

Apostrophe - to match O'Conner correctly escape ' by inserting one next to it, so O'Conner becomes OConner.

PERL example:

double ' to  so queries don't break

$var = "O'Conner";
$var=~ s/\'/\'\'/g;
print $var;
produces OConner

string match WHERE LIKE 'username'

Where (Z.column like '%X%' OR Z.column like '%Y%')

You can also use "IS NOT NULL" in an SQL statement. For example:

select * from suppliers where supplier_name IS NOT NULL;

Converting CLOBs [7][8][9]

select dbms_lob.substr( alert, 4000,1) from TABLE
where UPPER( alert ) = 'PIF'

Converting BLOBs requires converting the binary stream to hex, then to characters.

Random Sample[10]:

SELECT  * 
FROM    ( 
        SELECT  * 
        FROM    mytable 
        ORDER BY 
                dbms_random.value 
        ) 
WHERE rownum <= 1000 

Variable Syntax

create_date between :startdate and :enddate
PL/SQL Syntax

DOB_DATE BETWEEN to_date('07/01/2011', 'mm/dd/yyyy') AND to_date('06/30/2012', 'mm/dd/yyyy')

Make sure to use YY year for trunc dates.

select count(*)
from TABLE
where input_code = '90658' 
and trunc(create_date) between to_date('01-Oct-12', 'dd-Mon-YY') and to_date('13-Nov-12', 'dd-Mon-YY')

Date as Character String

select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time" from dual;

Fetches: Thu 08-Nov-2012 09:37:18

Time

Time Duration

select  extract( day from numtodsinterval(  start_date - CREATE_DATE, 'day' )) as d 
from TABLE
order by 1 desc

• For time in minutes

select ( end_date - start_date ) * 1440
from table

Select for a Month

where to_char( DATE, 'Mon-YYYY') = 'Jul-2014'

TRUNC[11]

TRUNC(date) returns the date with no time.

select trunc(sysdate) from dual

Fetches 08-NOV-12

select field1, field2
from table
where
trunc(date) = '26-SEP-2012'

Fetches all field1 and field2 for September 26, 2012.

-- Select exact date

select *
from table
where trunc(create_date) = to_date('11-DEC-2013','dd-MON-yyyy')

TO_DATE[12]

TO_DATE converts a character string to a date format given the format string.

SELECT TO_DATE(sysdate, 'YYYY/MM/DD') from dual;

Fetches '12-Nov-08'

Mon-YYYY Rollup

select 
DECODE( to_char(DATE, 'yyyy'),NULL, 'Grand Total',to_char(DATE, 'yyyy'))  as "Year",
 to_char(DATE, 'Mon') as "Month",
      count(1) as total_scheduled
from TABLE 
where trunc(DATE) between to_date('10-2013','mm/yyyy') and to_date('03-2014','mm/yyyy')
group by rollup(to_char(DATE, 'yyyy'), to_char(DATE, 'Mon') )
order by 1,2

Produces
2013	dec	4545
2013	nov	7343
2013	oct	9263
2013		21151
2014	feb	8228
2014	jan	8449
2014		16677
Grand Total     37828

-- Gives subtotals for each weekday by column 2 - Note the single ( )
group by rollup(to_char( start_date, 'Dy'), trunc(start_date))

-- Gives one 'Grand Total' by column 2 - Note the extra ( )
group by rollup((to_char(start_date, 'Dy'), trunc(start_date)))

Select Between Dates

to_date(create_date, 'yyyy/MM/DD') > '2012/01/01'

Fetches records created after 2012/01/01.

to_char(date,'Mon-YYYY') = 'Sep-2012' --Set 'MON-YYYY' = 'SEP-2012' make them the same case.

Fetches records from September, 2012

where trunc( date) between to_date('26/sep/2012','dd/mon/yyyy')
and to_date('26/sep/2012','dd/mon/yyyy')

This fetches all for 26-SEP-2012.

Between Date and Time

--Between Date and Time, TO_DATE version
SELECT to_char(date, 'dd-mon-yyyy hh24:mi ') FROM encounter
WHERE trunc( date )  BETWEEN TO_DATE('11.12.2006. 12:00', 'dd.mm.yyyy. hh24:mi')
               AND TO_DATE('11.12.2006. 18:00', 'dd.mm.yyyy. hh24:mi')
   OR trunc( date )  BETWEEN TO_DATE('11.12.2006. 12:00', 'dd.mm.yyyy. hh24:mi')
               AND TO_DATE('11.12.2006. 18:00', 'dd.mm.yyyy. hh24:mi')
-- Between Date and Time, TO_CHAR version
select  to_char(date, 'dd-mon-yyyy hh24:mi')
from TABLE
where to_char(date,'hh24mi' ) between '1200' and '1800'
and to_char(date,'dd-mon-yyyy') IN
(select to_char(date, 'dd-mon-yyyy')
from TABLE
where
to_char(date,'Mon-YYYY')='Jan-2012')

-- Between Date and Time - Over a Month - TO_CHAR version
select count(*)
from TABLE
where to_char( date,'hh24mi' ) between '1200' and '1800'
and to_char(date,'dd-mon-yyyy') IN
(select to_char(date, 'dd-mon-yyyy')
from TABLE
where
to_char(date,'Mon-YYYY')='Sep-2011')

Time in Years

 select patient_id, extract(YEAR from (SYSDATE - DATE_OF_BIRTH) YEAR TO MONTH) as age
 from TABLE

-- Count people less than 18 years old
SELECT count(*)>
from TABLE
where extract(YEAR from (SYSDATE - DATE_OF_BIRTH) YEAR TO MONTH)<18;

Duplicates - Fetching Duplicate Entries

select column_name, count(column_name)
from table
group by column_name
having count (column_name) > 1;

Duplicates - Finding duplicates with an INNER JOIN

select o.filename, oc.dupeCount, o.fileid
from files o
inner join (
    SELECT filename, COUNT(*) AS dupeCount
    FROM files
    GROUP BY filename
    HAVING COUNT(*) > 1
) oc on o.filename = oc.filename

Select from a Subquery as a JOIN

-- select from a subquery as a join
 select t1.DATE,
t1.ID as ID,
t1.LAST_NAME as NAME,
t1.DIVISION_NAME as DIVISION_NAME
from TABLE1 t1,  (
    select DATE,
           ID,
           count( ID )
           from TABLE2 t2
           where trunc( DATE ) between :startdate and :enddate
           group by DATE, ID 
           having count( ID ) > 1) dup_query
where t1.DATE = dup_query.DATE
and t1._ID = dup_query.ID
order by 1,2;

Pseudo Tables[13]

Use the WITH clause to create a pseudo virtual table. Example below.

--Using WITH to create a pseudo table simplifies the main query
WITH visit as (select *
              from table1 t1, table2 t2
              where t1.id = t2.id 
              and t1.check is not null
              and t1.status_text = 'Active')

DECODE[14]

Fetching an unknown use DECODE (x,y,z , 'unk') to avoid an exception.
x expression
y comparison
z what to do if a match
'unk' default

List

select * from table where ID IN (4,5,6)

ORDERY BY[15]

By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in descending order, you must explicitly specify it as shown below.

SELECT name, salary 
FROM employee 
ORDER BY name, salary DESC; 

String Concatenate

--Concatenate results in a string with translation
select wmsys.WM_CONCAT( CASE constraint_type WHEN 'P' THEN 'PK' WHEN 'R' THEN 'FK' ELSE  END )
from all_constraints 
where constraint_name = 'NAME'

Multiple counts on the same line requires multiple select counts as below:

select (select count( table.id) 
        from table
        where table.index_id IS NULL) Non_ID,
(select count(t1.id)
 from table1 t1, table2 t2 where t2.id IS NULL and t1.id = t2.ID) Non_ID
from dual

Internal Links

Parent Article: Databases