[go: up one dir, main page]
Scribd
Upload
4 views7 pages

dbms lab 3

The document outlines SQL formatting rules, including default justification for data types and the use of wildcard searches with the LIKE keyword. It explains handling NULL values in arithmetic expressions, the use of DISTINCT to eliminate duplicates, and various SQL functions such as single-row and multiple-row functions. Additionally, it covers date formatting, conversion functions, and the NVL function to manage NULL values in calculations.

Uploaded by

Prerana laha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views7 pages

dbms lab 3

The document outlines SQL formatting rules, including default justification for data types and the use of wildcard searches with the LIKE keyword. It explains handling NULL values in arithmetic expressions, the use of DISTINCT to eliminate duplicates, and various SQL functions such as single-row and multiple-row functions. Additionally, it covers date formatting, conversion functions, and the NVL function to manage NULL values in calculations.

Uploaded by

Prerana laha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

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

You might also like