Home
Ray
Articles
This Old House
Family History
Time Line
Fab Five
Printable Resume
Movie Ratings
Book Ratings
Hobbies
Strange Events
Vintage Ray Photos
The Family Fleet
Nature
Christmas Memories
Code Library
javaScript
CSS
Forms
jQuery
SQL
NFL Picks
Bible Study
Organized Crime
SQL Examples
1 - Check for Duplicates
3 - Return records from one table that are not in another
5 - Display counts horizontally
7 - Capitalize first character of a string
9 - Group Count
11 - Sum Example
13 - Case & Sum Example
15 - Ranking Records
17 - Exclude rows from 1st query if they occurr in the 2nd
19 - Calling a procedure
21 - Numeric check
23 - Escape Characters
25 - BETWEEN Keyword
27 - Insert Record
29 - Case
31 - SUM with Group By
33 - Limit Rows
35 - EXCEPT
2 - Extract first and last names from full name
4 - Remove a comma from a string
6 - Count number of spaces in a string
8 - Date edit (ie Tuesday, June 14 2005)
10 - Count by Location
12 - Decode Example
14 - Calculate Age
16 - Employee totals
18 - Limit Row Numbers
20 - Calling a Function
22 - Parsing Street Type from Street Name
24 - Wild Cards
26 - AUTO_INCREMENT Keyword
28 - Update Record
30 - SUM Function
32 - MIN and MAX
34 - SUB STRING
Check for Duplicates
SAMPLE CODE select loc, Count(*) thecount from facmanagers group by cube (loc) order by thecount desc
OUTPUT loc thecount 292 1010 1 1011 1 1014 1 1020 1 1022 1 1030 1
Back to Top
Extract first and last names from full name
SAMPLE CODE select full_name, NVL(SUBSTR(full_name, 0, INSTR(full_name, ',')-1), full_name) lastname, REGEXP_SUBSTR(full_name, '[^,]+$') firstname from absensecontrol.tt_seedfile
OUTPUT FULL_NAME LASTNAME FIRSTNAME Bagley, D Maxine Bagley D Maxine Belser, Amin Belser Amin Biddle, Christopher K. Biddle Christopher K. BOGAN, MARISOL BOGAN MARISOL
Back to Top
Return records from one table that are not in another
SAMPLE CODE select ulc_code, directory_name_1 from BSYS.ULC_VIEW_ULCS where ulc_code IN ( select ulc_code from BSYS.ULC_VIEW_ULCS where school_level IN ('0', '1', '2', '3', '4', '5', '6') minus select ulc_code from fs_locations ) order by ulc_code
OUTPUT ulc_code directory_name_1 0597 HIGH ROAD PROGRAM AT FRANKFORD 0598 IU26 LAMBERTON 0599 HIGH ROAD PROGRAM AT FERGUSON 1440 PENROSE SCHOOL 2530 PHILA JUV JUSTICE SVCS CTR 3040 EXCEL MIDDLE YEARS ACADEMY 3170 VISIONQUEST NATIONAL LTD.
Back to Top
remove a comma from a string
SAMPLE CODE select dept_name, NVL(SUBSTR(dept_name, 0, INSTR(dept_name, ',') -1), dept_name) from RECSMGT.DEPARTMENT_BOX
OUTPUT dept name CATO CATO MILLER, E. SPENCER MILLER SOUTH PHILADELPHIA SOUTH PHILADELPHIA FRANKLIN, BENJAMIN FRANKLIN BOK, EDWARD BOK
Back to Top
Display horizontal counts
SAMPLE CODE select distinct(application_id), (select count(*) from users_log where application_id = 2954 and active_date = (SELECT TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy') - 1 FROM DUAL) ) day1, ( select count(*) from users_log where application_id = 2954 and active_date = (SELECT TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy') - 2 FROM DUAL) )day2, ( select count(*) from users_log where application_id = 2954 and active_date = (SELECT TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy') - 3 FROM DUAL) ) day3, ( select count(*) from users_log where application_id = 2954 and active_date = (SELECT TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy') - 4 FROM DUAL) ) day4, ( select count(*) from users_log where application_id = 2954 and active_date = (SELECT TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy') - 5 FROM DUAL) ) day5, ( select count(*) from users_log where application_id = 2954 and active_date = (SELECT TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy') -6 FROM DUAL) ) day6, ( select count(*) from users_log where application_id = 2954 and active_date = (SELECT TO_DATE (TO_CHAR (SYSDATE, 'mm/dd/yyyy'), 'mm/dd/yyyy') - 7 FROM DUAL) ) day7 from users_log where application_id = 2954
OUTPUT 2954 0 0 0 0 0 0 0
Back to Top
Count number of spaces in a string
SAMPLE CODE select distinct(LTRIM(pha_directionstreetname)), length(LTRIM(pha_directionstreetname)) - LENGTH(replace(LTRIM(pha_directionstreetname), ' ')) "spacesCount" from public_housing
OUTPUT N 10TH STREET 2 N BUSTI ST 2 HOOPES ST 1 WALLACE ST 1 ADDISON ST 1 MARTIN ST 1 W DIAMOND ST 2
Back to Top
Capitalize first character of a string
SAMPLE CODE select INITCAP(lower(TRIM(lname))) || ', ' || INITCAP(lower(fname)) "Emp Name" from aviolators
OUTPUT Gardner, Jimmy Webb, Lillie Byrd, Zelda Rollins, Maria Pollard, James Teti, Christopher Flowers, Donna
Back to Top
Date edit (ie Tuesday, June 14 2005)
SAMPLE CODE select to_char(step_dt0, 'Day') || '- ' || to_char(step_dt0, 'Month dd, yyyy') thedate from aviolators
OUTPUT Friday - June 29, 2018 Wednesday- January 13, 2016
Back to Top
Group Count
SAMPLE CODE select step, count(step) thecount from aviolators group by step order by step
OUTPUT 0 780 1 2 2 13 3 17
Back to Top
Count by Location
SAMPLE CODE select emp_loc, count(emp_loc) from aviolators group by emp_loc order by emp_loc
OUTPUT 1010 2 1020 3 1030 3 1100 2 1130 4 1160 2 1190 1 1200 4
Back to Top
Sum Example
SAMPLE CODE select SUM(CASE WHEN step ='0' THEN 1 ELSE 0 END) as "Warning", SUM(CASE WHEN step ='1' THEN 1 ELSE 0 END) as "Step 1", SUM(CASE WHEN step ='2' THEN 1 ELSE 0 END) as "Step 2", SUM(CASE WHEN step ='3' THEN 1 ELSE 0 END) as "Step 3", SUM(CASE WHEN step is null THEN 1 ELSE 0 END) as "Null" from aviolators
OUTPUT Warning Step1 Step2 Step3 Null 780 2 13 17 10
Back to Top
Decode Example
SAMPLE CODE select step, Decode(step, '0', SUBSTR(fname,1,1) || '. ' || INITCAP(LOWER(RPAD(TRIM(lname), 20, '.'))) || ' is a Warning violator', '1', SUBSTR(fname,1,1) || '. ' || INITCAP(LOWER(RPAD(TRIM(lname), 20, '.'))) || ' is a Step 1 violator', '2', SUBSTR(fname,1,1) || '. ' || INITCAP(LOWER(RPAD(TRIM(lname), 20, '.'))) || ' is a Step 1 violator', '3', SUBSTR(fname,1,1) || '. ' || INITCAP(LOWER(RPAD(TRIM(lname), 20, '.'))) || ' is a Step 3 violator') name from aviolators
OUTPUT J. Gardner............. is a Warning violator C. Bain................ is a Step 1 violator W. Jackson............. is a Step 2 violator D. Drayton............. is a Step 3 violator
Back to Top
Case & Sum Example
SAMPLE CODE select sum (case when trim(reason_cd) = '04' then duration else 0 end) as "F04", sum (case when trim(reason_cd) = '02' then duration else 0 end) as "F02", sum (case when trim(reason_cd) = '01' then duration else 0 end) as "F01", sum (case when trim(reason_cd) = '13' then duration else 0 end) as "F13", sum (case when trim(reason_cd) = '58' then duration else 0 end) as "F58", sum (case when trim(reason_cd) = '59' then duration else 0 end) as "F30" from oars_abs_view where empl_id = '12345'
OUTPUT F04 F04 F01 F13 F58 F30 29 83 10 2 0 0
Back to Top
Calculate Age
SAMPLE CODE select dob, to_number (months_between((to_date(dob, 'mm/dd/yyyy')), to_date('09/01/2007', 'mm/dd/yyyy')) / 12) age from EARLYCHILDHOOD.PREK_STUDENTS
OUTPUT 12/01/2012 5.25 10/11/2012 5.11021505376344 04/19/2012 4.63172043010753 08/29/2012 4.99193548387097 11/21/2011 4.22043010752688 12/11/2012 5.27688172043011
Back to Top
Rank the Records
SAMPLE CODE select row_number() over (order by lname || ' ' || fname) as row_number, lname, fname from aviolators
OUTPUT 1 AARON ANDREA 2 ABDULLAH ABDUL 3 ABNEY JAMES 4 ADAMS GEORGE 5 ADAMS JOSEPH 6 ADAMS-MCCULLOUGH ORIONNA 7 ADAMS-WHITE LORRAINE
Back to Top
Employee totals
SAMPLE CODE select ghrs_internal_empl_id_no || ' ' || TRIM(lname) || ' ' || fname as empname, count(*) as emp_tot from avstep3hist group by rollup(ghrs_internal_empl_id_no || ' ' || TRIM(lname) || ' ' || fname) order by emp_tot desc
OUTPUT 31 15145 FRAZIER DONNA 9 76751 ALFORD JAMAAL 3 76652 ALI SUSANA 3 20396 PHILMORE CHARLENE 2 12155 DRAYTON DARRYL 2 23948 ALTIERI MICHAEL 2 9301 SAYLES SHEILA 2 58589 ANDERSON TAWANDA 2 4108 HARTMAN DIANE 2 11634 AARON ANDREA 1 42112 BOYD IRA 1 17297 CARSLEY JOSEPH 1
Back to Top
Exclude rows from 1st query if they occurr in the 2nd
SAMPLE CODE select employee_id from aviolators minus select emp_id from uvt
OUTPUT 0000000933 0000001065 0000001224 0000001958 0000002618
Back to Top
Limit Row Numbers
SAMPLE CODE select lname || ' ' || fname from aviolators where rownum <= 5
OUTPUT GARDNER JIMMY WEBB LILLIE BYRD ZELDA ROLLINS MARIA POLLARD JAMES
Back to Top
Calling a procedure
SAMPLE CODE set serveroutput on; DECLARE begin deleteproductionlosstable; end; -------------------------------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE ABSENSECONTROL.deleteproductionlosstable IS BEGIN DELETE from productionlosstable; END deleteproductionlosstable;
OUTPUT
Back to Top
Calling a Function
SAMPLE CODE DECLARE yesorno CHAR := fncChkForHoliday(to_date('12/25/2009', 'mm/dd/yyyy')); begin dbms_output.put_line(yesorno); end; --------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION ABSENSECONTROL."FNCCHKFORHOLIDAY" (p_occurr_dt HOLIDAY_TABLE.holiday%TYPE) RETURN CHAR IS v_holiday HOLIDAY_TABLE.holiday%TYPE; BEGIN SELECT holiday INTO v_holiday FROM HOLIDAY_TABLE where holiday = p_occurr_dt; RETURN ('Y'); EXCEPTION WHEN no_data_found THEN RETURN ('N'); END fncChkForHoliday;
OUTPUT
Back to Top
Numeric Check
-- the following is a numeric check.The last condition checks for nullbecase some student codes are only 6 characters and (substr(student_code, 1, 1) IN ( '0', '1','2', '3', '4', '5', '6', '7', '8', '9')) and (substr(student_code, 2, 1) IN ( '0', '1','2', '3', '4', '5', '6', '7', '8', '9')) and (substr(student_code, 3, 1) IN ( '0', '1','2', '3', '4', '5', '6', '7', '8', '9')) and (substr(student_code, 4, 1) IN ( '0', '1','2', '3', '4', '5', '6', '7', '8', '9')) and (substr(student_code, 5, 1) IN ( '0', '1','2', '3', '4', '5', '6', '7', '8', '9')) and (substr(student_code, 6, 1) IN ( '0', '1','2', '3', '4', '5', '6', '7', '8', '9')) and ( (substr(student_code, 7, 1) IN ( '0', '1','2', '3', '4', '5', '6', '7', '8', '9')) or (substr(student_code, 7, 1) is null) )
OUTPUT
Back to Top
Parsing Street Type from Street Name
SAMPLE CODE select pha_directionstreetname, substr(pha_directionstreetname, instr(pha_directionstreetname, ' ', -1)+1) Street_Type from public_housing
OUTPUT MCMICHAEL ST ST N 10TH STREET STREET ALFRED ST ST N 21ST STREET STREET ALGON AVE AVE N NEWKIRK ST ST N 19TH STREET STREET
Back to Top
Escape Characters
SAMPLE CODE Insert Into my_contacts (location) Values ('Grover\'s Mill'); OR Insert Into my_contacts (location) Values ('Grover''s Mill');
OUTPUT
Back to Top
Wild Cards
SAMPLE CODE select first_name from my_contacts where first_name LIKE '%im'; OR select first_name from my_contacts where first_name LIKE '_im';
OUTPUT
Back to Top
Between Keyword
SAMPLE CODE select drink_name from drink_info where calories BETWEEN 30 and 60;
OUTPUT
Back to Top
Auto Increment
SAMPLE CODE Alter table my_contacts add column contact_id INT NOT NULL AUTO_INCREMENT FIRST Add primary key (contact_id) ***NOTE*** FIRST tells the software to make the new column the first rcord on the table ***
OUTPUT
Back to Top
Insert
SAMPLE CODE INSERT INTO brokerage (account_id, create_date, blance) VALUES ('Keisha',SYSDATE, 5000)
OUTPUT
Back to Top
Update Record
SAMPLE CODE UPDATE order_rollup SET phone = '2154005005082', fax = '7735551212' WHERE customer_id = 'KOHL'
OUTPUT
Back to Top
CASE
SAMPLE CODE SET new_column - CASE When column1 = somevalue1 Then newvalue1 When column2 = somevalue2 Then newvalue2 ELSE newvalue3 END
OUTPUT
Back to Top
CASE
SAMPLE CODE Select SUM(sales) from cookie_sales where first_name = 'Nicole'
OUTPUT
Back to Top
CASE
SAMPLE CODE Select first_name, SUM(sales) from cookie_sales group by first_name order by SUM(sales) Desc
OUTPUT first_name sum(sales) Britney 107.91 Paris 98.23 Nicole 96.03
Back to Top
CASE
SAMPLE CODE Select first_name, MAX(sales) from cookie_sales group by first_name Select first_name, MIN(sales) from cookie_sales group by first_name
OUTPUT first_name MAX(sales) Nicole 26.82 Britney 43.21 Lindsay 96.03 first_name MIN(sales) Nicole 0.00 Britney 2.58 Lindsay 0.00
Back to Top
LIMIT
SAMPLE CODE select first_name, SUM(sales) From cookie_sales Group By first_name Order by SUM(sales) Desc LIMIT 2
Back to Top
Sub String
SAMPLE CODE Interests - first, second, third, fourth Interest1 - Interest2 - Interest3 - Update my_contacts Set interest1 = substring_index(interests, ',', 1) (this will place first in the interest1 field) Interests - first, second, third, fourth Interest1 - first Interest2 - Interest3 - Update my_contacts Set interest1 = substring_index(interests, Length(interest1) +2)) (this will place first in the interest1 field)
Back to Top
EXCEPT - returns only those columns that are in the first query but not in the second query
SAMPLE CODE Select title from job_current EXCEPT Select title from job_desired
Back to Top