General Sql Queries

General Sql Queries

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL )
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual
SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt FROM all_objects WHERE
ROWNUM<= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y')) GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' )) ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week)
Counts
Provides a count of all (distinct) values in a particular column or table. The column can be either alpha or numeric. Null values in the column are included in the count.

SELECT catalog_nbr, COUNT (*) as Num_of_Stu
FROM XX_STUDENT
WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
GROUP BY catalog_nbr
Sums
Provides a sum of all (distinct) values in a particular column. The column must be numeric. Null values in the column are not included in the sum

SELECT acad_career, subject, SUM (unt_taken) units_taken
FROM XX_STUDENT
WHERE subject = 'SOIL'
GROUP BY acad_career, subject
Having 

Use the HAVING clause to restrict which groups of rows defined by the GROUP BY clause are returned by the query

SELECT catalog_nbr, class_section, COUNT (*) AS num_of_stu
FROM XX_STUDENT
WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
AND component_main = 'DIS'
GROUP BY catalog_nbr, class_section
HAVING COUNT (*) > 50
Case/Decode/NVL

Case and Decode statements both perform procedural logic inside a SQL statement without having to use PL/SQL.

SELECT DISTINCT a.NAME,
CASE
WHEN a.emailid_2 IS NULL THEN a.emailid_1
ELSE a.emailid_2
END
email_add
FROM XX_STUDENT a, XX_STUDENT1 b
WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

SELECT DISTINCT a.NAME,
DECODE (a.emailid_2, NULL, a.emailid_1, a.emailid_2) email_add
FROM XX_STUDENT a, XX_STUDENT1 b
WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

SELECT DISTINCT a.NAME,
NVL (a.emailid_2, a.emailid_1) email_add
FROM XX_STUDENT a, XX_STUDENT1 b WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

All of these queries will return a list of student names with there secondary email addresses unless they didn’t report a secondary address, then it will return their primary email address

It is best to use the CASE statement when comparing ranges or more complex logic.

SELECT a.NAME,
(CASE
WHEN a.vac_hrs_taken_ytd <= 40 THEN 'GET A LIFE' WHEN a.vac_hrs_taken_ytd BETWEEN 41 AND 100 THEN 'NEED A BREAK?' WHEN a.vac_hrs_taken_ytd >= 101 THEN 'WELL RESTED'
END) mental_wellbeing
FROM XX_STUDENT a
WHERE a.deptid = '831A'
AND a.fisc_yr = '2003'
AND a.pay_period = '06'
AND a.empl_status = 'A'
ORDER BY 2
Rollup
The use of a ROLLUP clause in the GROUP BY part of the SQL expression displays subtotals and grand totals depending on it’s use.

SELECT NVL (catalog_nbr, 'GRAND_TOTAL') catalog_nbr, class_section,
SUM (unt_taken)
total_units, COUNT (*) num_of_stu
FROM XX_STUDENT WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
GROUP BY ROLLUP (catalog_nbr, class_section)
Inline Views

You can use a SQL statement in the FROM clause of a SQL statement. This is called a inline view. Oracle treats the data set that is returned from the inline view as if it were a table.

SELECT a.NAME, a.office1_phone
FROM XX_STUDENT a,
(SELECT x.emplid
FROM XX_STUDENT1 x
WHERE x.deptid = '831A'
AND x.status_flg = 'C'
AND x.job_terminated ='N') b
WHERE a.emplid = b.emplid;



Round

Returns a number rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer

SELECT ROUND(AVG(ENG_ACT_SCORE), 4)
FROM XX_STUDENT
WHERE ENG_ACT_SCORE != 0