Oracle for B.SC Final Year Student.
Building a simple report or creating procedure:
(1) Remark: The main command allows adding comments, documentation and explanation to any start file i.e. it gives remark on the top of start files, giving the file name create and date of creation, date of modification etc.
(2) set headsep (heading separator): It provides the facility using which we head the title or column name.
syntax: set headsep!
(3) TTitle and btitle: (title stands for top title and btitle stands for bottom title).
If we want to add any title on the top of page of the report title. The title must be enclosed in a single quotation mark.
For example: ttitle ‘Bookname’
Similarly, btitle works i.e. it adds the title on the bottom of page of report.
(4) It allows to change the heading and the format of any column in select statement. If the name of column exceed the character that we define using the statement.
Column name format a20
Then those column name have more than 20 character long wrap onto next line. We also defines a column with some digits and decimal points.
(5) Break on: If any three rows have a bookname ABC and another three rows has bookname DEF and those bookname are situation in a column name then following statement.
Break on Name Skip 1 on report.
It will display first three rows of value ABC on the fourth row name changes to DEF so SQL remembers our break instruction which tell it that when name changes it should break away from normal display of a row after row and skip one line.
For example:
Above example calculates the average of book price.
(7) Set line size: It governs the maximum number of characters that will appear on a single line. It also uses to determine where to center the title, and where to place the date and page number. Both date and page number appear on the top line and the distance between first letter of the date and the last digit of the page number.
(8) Spool: The magenetic tape and compact disks are used for storing the data of computer. The term spooling refers to any process of moving information from one places to another.
Spool Activity.1st
Above statement tells to the output from SQL and write it to the filename ‘activity.1st’ once we tell the SQL to spool, the process is continuous until we tell it to stop which is done by Spool off.
* Break and Compute: A break occurs when SQL detects a specified change such as end of page or change in the value of the expression. A break performs some action that we have specified in the break command such as skip, print se result on compute command, avg of total for a column. Break statement can also be used with on and order by clauses. A break on report cause a break at theend of report on row clause a break for evry row. On page skip skips a number of lines or skip page skips to a new page. Break by itself display the current break settings clear breaks will remove any existing break. That is we use break command with * break on column. * break on row. * break on report. * skip n. * skip page.
(2) To break on page. Break on report page on book_name.
Compute: Compute performs computation on column or expressions selected from a table. It work sonly with a break command. By default oracle will use the function name sum and avg with the compute command.
Syntax is: compute [Avg | cou | max | min | num | std | sum | var] of {expression | column} on {expression | column | report | row}
Here label allows to specifiy label name of names the column or expression whose value is to be computed. On co-ordinates the compute with break command. Compute works with following functions.
AVG: gives the average value.
Count: gives count of non null values.
Maximum: gives maximum value.
Minimum: gives minimum value.
Number: gives count of all rows returned.
Std: gives standard deviation.
Sum: gives sum of non null values.
Variants: gives variance.
For example: Compute sum avg max of amount rate on report.
This will compute sum, average, maximum of both amount and rate for the entire report.
*Spooling: Spool starts or stops sporting (coping) of a SQL plus output to the system file or system printer. It creates spool of all output files to the named file. If the file type is not specified spool acts a default lst.
Syntax is Spool [file | off | out];
Off stops spolling out stops spooling and sends the file to the printer. To spool output without displaying it set term out off in the start file to the SQL statement. The magnetic tapes and compact disks are used for storing the data of computer.
For example: spool Book_name.lst
Above statement tells to the SQL to take all the output from SQL and write it to the file name Book_name. once we tell the SQL to spool, the process is continued until we tell it to stop which is done by spool off.
Question: Explain any four date functions with suitable example? Date is an oracle data type which includes month, date, year, hour, mnute, second. We can format the date column on a report. The following functions are used with date data types.
(1) Sysdate: (System date) It displays current date and time of the operating system. Sysdate is a functionwhose result is always current date and time.
For example: Sysdate from emp;
Output: sysdate 15 Mar 03
(2) Current date: This function displays the current date of the system.
For example: Select current_date from emp;
Output: Current-d 13-Mar-05
(3) Greatest and Least: The least function chose the earliest date from a list of dates and greatest choose the latest date.
For example:
(i) Select least (’20-Jan-02’, ’20-Dec-02) From emp;
output: Least (‘20
20- dec-02
(ii) Select greatest (’20-Jan-02’,’20-dec-02’) From emp;
output: greatest (‘20
20-Jan-02
(4) Last_Day: It produce the date of last day of the month. For example: Select Last_day As endmonth From pay_day;
*FUNCTION:
ARITHMETIC (NUMBER): This function always acts as a single numeric value and gives result in numeric form they are:
1) ABS ( ):
The ABS ( ) value of a number is equal to its numeric value ignoring a positive or negative sign. This function is actually used to find absolute value of any signed number. It is often used to find the difference between numbers when it doesn’t matter if the value is above or below the target. Syntax: ABC (
When ‘expn’ is any number sign value
Example: SELECT *from emp
WHERE ABS (Advance_Del) > 0
2) MODULES (MOD): This function returns the remainder of the function dividing the value expn1 by expn2. Syntax: MOD (
3) Power: This function returns the result of using the first argument to the power indicated by 2nd argument. The 2nd argument must be an integer.
Syntax: Power (
Example: Power (5, 2) we will get result as 25
4) ROUND ( ): The ROUND ( ) function is after used to round up the result of calculation to a particular decimal point if there is no argument given specifying decimal place, the default is zero, which will round up value to the closest integer. The value can also be rounded to the nearest 10 or 100 by using -1 or -2 resp.
Syntax: ROUND (
Example: (5.397, 2) will give result as 5.4
5) TRUNC ( ): The trunc( ) function is used to truncate result of a calculation on a particular decimal point.
Syntax: trunk (
If no argument is given specifying the decimal place
6) CHARACTER ( ): Several common function are available that operate on a single value made up of one or more characters. These values are often called as strings. The function used for the operations on the string or characters called character factions. They are,
(i) inticap (
Lower : This function is used to convert string value into lower case letter. The lower ( ) takes the value of
Syntax: LOWER (Expc)
Ex: Select name, lower (name) from 000;
OUTPUT:
Name Lower (Name)
aaa aaa
BBB bbb
Similarly upper ( ) function returns the string into uppercase letters.
Syntax: UPPER (expc)
Ex: Select name, Upper (Name) from 000;
OUTPUT:
Name Upper (Name)
aaa AAA
bb BB
(ii) Concatenating: (concat())
Syntax:
concat (‘str 1’, ‘str 2’) as str 3 from table name;
Example: Select concat (‘aaa’, ‘bbb’) as SSS from OOO;
Output: SSS
aaa bbb
aaabbb - - - - -
(iii) Padding and trimming:
L-Pad and R-Pad
The function L-Pad and R-Pad provide a way to concatenate a second series of a no. of character to left or to right of original string. The length argument of function tells exact length of original string including the original string. If s3scond string is emitted the string is pad with space. Syntax L Pad (‘st1’, n, ‘st2’)
Ex: Select name, lpad (name, 20, ‘*’)
From OOO;
(iv) Replace ( ): The replace function is used to search a required string from file and replace it with another string.
Syntax: Replace (‘str1’, ‘str2’, ‘str3’)
Where; St1 - actual string
St2 - search string
St3 - Replace string
Ex: Select name, replace (name, ‘a’, ‘*’) n*me form OOO;
(v) Substring (substr()): This function will return a string of characters found in string argument beginning at position in and continuing for n number of characters. If length n is not specified it will return all letter to end of string. Syntax:
substr (st, m, n)
m – beginning position.
n – number of characters.
Ex: Select name, substr (name, 3, 2) From OOO;
(vi) Length: The length function is used to count the member of character of characters of the string and return the result in numeric form. Syntax: Length (St)
Ex: Select name; length (name) as length form OOO;
(vii) L trim and R trim: These functions trim the blank space from left and right position.
Ex: select name, cancat (L trim (name), ‘111’)
(viii) To – char ( ): (To undersare character)
This function takes a numbers value and converts it into string. Syntax: To char (number);
Ex: Select to char (1234) from OOO;
Select concat (to char (age), ‘ddd’) from OOO;
(ix) To_number( ): This function converts any digits in string form into numeric (st).
Where st is any string containing a number.
Ex: Select age + to number (‘333’) form OOO;
GROUP FUNCTION OR STATIC FUNCTION
This function works on group of value. These are
1) SUM ( ): The sum function is used to calculate sum of number. Syntax: Sum (n);
Where n is any numeric column or group of numeric numbers.
Ex: Select sum (age) form OOO;
Output Sum (age) 284
Select Sum (age) from OOO;
Where age >40
2) AVG ( ): The Avg function is used to calculate the average of the numbers. Syntax: Avg (n);
Where n is any number column or group of numeric values. This function firstly finds the sum then count the total rows and than divide the sum by number of rows.
Ex: Select avg (age) from OOO;
O/P: AVG (age) 35.5
Select avg (age) from OOO where age > 40;
3) Min ( ) and Max ( ): The min and max function finds the minimum and maximum value form group of values respectively. Syntax: Min (n); and Max (n);
Where n is any numerical column or group of value
Ex: Select min (age), Max (age) from OOO;
O/P: Min(age) Max(age)
23 70
4) Count (*): The count function that takes the ‘*’ as a parameter. It count all the rows in the entries table or a subset specified by the query and ignored any null value in any column
Ex: Select count (*) form OOO;
O/P: count (*)
8
Select count (*) from OOO
Where age > 40;
O/P count (*)
2
5) count (all n) and (n): These two function are usually equivalent. They display the number of values present in the particular column n. You could read it as display the count of known values in column n. The ‘all’ keyword specifics to look at all columns when counting.
Ex: Select count (all age), count (age) from OOO;
O/P count (all age) count (age)
8 8
6) count (distinct n): The use of the keyword distinct in count function return the no. of distinct values present in the particular column n. It can be used with any data type and the purpose of this function to count number of rows.
Ex: Select count (*), count (distinct age) from OOO; O/P count (*) count (distinct age)
7 7
* Conversion function:
(1) To-char(): This function converts date into a character string. To-char function enclosed within to-date function. For ex: Select birthday, To-char (birthdate, ‘MM/DD/YY’) formatted From Birthdate Where Name = ‘vijay’;
Output: Birthdate formatted 27-Dec-85 12/27/85
(2) To-Date(): This function converts character string or number into date.
For ex: Select Holiday, celebrateDate From Holiday Where celebrate = To-date (‘17-nov-02’), To-date (’02-sep-02’)
Output: Holiday celebrate
Diwali 17-nov-02
(3) To-number (): This function converts character values into a number data types. Syntax is To-number (char)
For Ex: To-number (‘333.46’)
Number indicates just the digits 0-9,+,-&. Symbols.
* Transformation Function: The process of converting one data type into another data type is called transformation.
Transformation function: Any function that changes its object is called as transformation function. Most commonly used transformation functions are translated and decode. (1) Translate: Translate is a function that does not an orderly character-by-character substation in a string. The syntax is Translate (string, it, then)
Translate look at each character in a string and then check whether the character is in if or not. If it is there then notes the position in if where it found the character, and then looks at the same position in then.
For ex: Select translate (7671234, 2345678990, ‘BCDEFGHIJ’) From dual;
Output: GFGIBCD
(2) Decode: It is a function that does not an orderly value-by-value substitution in a strng. For vevery values it checks in a field row by row. If the value is found then it will substitude the declared value in then. Syntax is Decode (value, if1, then1, if2, then2,----,else)
for ex:
Select Name from emp;
Decode (Id, ‘A’ ‘class1’, ‘Turnto’ //Id)
Output:
Name Decode (Id, ‘A’ ‘class1’, ‘Turnto’ //Id)
Herry class1
Ajay Turnto B
Rahul Trunto C
Satish Class1
Sachin Class1.
UNIT IV ENDS HERE
<<<< Previous PAGE Next >>>>