DEFAULT COLUMN HEADINGS :
• Default justification includes Date and Character Data is left justified
, Numeric Data is right justified.
• Character/Date columns headings will be truncated.
• Numerical column headings are not truncated.
• Alias name can be replaced by the column name.
LIKE KEYWORD does WILDCARD SEARCHES in Valid String values.
% ------------------------zero or many characters.
- ---------------- one character
Compare with NULL value :
SQL> SELECT ename , mgr FROM EMP
WHERE mgr IS NULL;
NULL VALUES IN ARITHEMETIC EXPRESSIONS:
• NULL as an operand will result NULL.
SQL> SELECT ENAME NAME , SAL +COMM “TOTAL SALARY” FROM
EMP; //INCORRECT
SQL> SELECT ENAME NAME,SAL+NVL( COMM,0).”TOTAL SALARY”
FROM EMP; //CORRECT
LITERAL CHARACTER STRINGS :
• Date and Character literal values must be enclosed within single
quotation marks.
CHARACTER STRINGS AND DATES:
• Character /Dates are represented by the single quotation marks.
• Default date format is ’DD-MON-YY’
USING ‘DISTINCT ‘ CLAUSE
• Eliminate duplicate rows by using the Distinct keyword.
• SQL> SELECT DISTINCT DEPTNO FROM EMPLOYEE;
SQL FUNCTIONS
TYPES OF SQL FUNCTIONS :
1.SINGLE – ROW FUNCTIONS
2.MULTIPLE-ROW FUNCTIONS
SINGLE-ROW FUNCTIONS :
Act on every row as a result of every row .
function_name( column|expression , [arg1,arg2,…..])
DATE
• Default date format is DD-MON-YY.
• SYSDATE is a function which returns the system date and time.
• Add/Subtract a number to the Date.
• Add/Subtract hours to a date by dividing the number of hours by
24.
• MONTHS_BETWEEN : number of months between two dates
• ADD_MONTHS: add calendar months to date
• NEXT_DAY: next day of the date specified.
• LAST_DAY: last day of the month.
• ROUND: round date
• TRUNC: truncate date
CONVERSION ():
1.VARCHAR2/CHAR TO NUMBER :TO_NUMBER(‘char’)
2.NUMBER TO VARCHAR2:TO_CHAR(number)
EXAMPLE:
SQL> SELECT SYSDATE FROM DUAL;
SQL> SELECT TO_CHAR ( SYSDATE,’DAY,DDTH MONTH YYYY’)
FROM DUAL;
OUTPUT :
TO_CHAR(SYSDATE, ’DAY,DDTHMONTHYYYY)
MONDAY ,10 TH MARCH 2025
NOTE : To remove the blank padding use fm( fill mode) prefix
SQL> SELECT TO _ CHAR (SYSDATE , ‘fmDay, ddth Month YYYY’) FROM
DUAL ;
OUTPUT :
TO_CHAR(SYSDATE,
Monday, 10th March 2025
DATE FORMAT :
• YYYY : full year in numbers
• YEAR : year spelled out
• MM: 2 digit value for month
• MONTH : full name of the month
• DY: 3 letter abbreviation of the day of the week
• DAY : Full name of the day
SUFFIXES:
• TH: ordinal numbers(e.g, ‘DDTH’ for 24 TH)
• SP: spelled-out number(e.g., ‘DDSPTH’ for TWENTY FOUR)
• SPTH: spelled-out ordinal numbers(e.g., ‘DDSPTH’, TWENTY
FOURTH)
Note : The codes are case sensitive and will affect display of
date elements .
NVL :
SQL> SELECT ename, sal, comm, (sal*12)+NVL(comm,0) FROM emp;
The NVL function forces group functions to include null values.
SQL> SELECT AVG(NVL(comm,0) FROM emp;
Multiple-row functions (Group Functions):
• Operate on sets of rows to give one result per group(AVG,COUNT,
MAX,MIN,SUM).
SQL>SELECT column,group_function(column)
FROM table
[WHERE condition]
[ORDER BY column]
Example:
SQL>SELECT COUNT(*) FROM emp
WHERE deptno = 30;
COUNT(expr) returns the number of non null values in the given column.
SQL> SELECT COUNT(comm) FROM emp
WHERE deptno=30;
OUTPUT:
COUNT(COMM)
4
Group functions ignore null values in the column.
SQL> SELECT AVG(comm) FROM emp;
OUTPUT:
AVG(COMM)
550
