Functions, Queries & Sub Queries

Oracle provides certain built-in function such as,

1)       Aggregate functions

2)       Arithmetic functions

3)       Character functions

4)       Data functions    

5)       String   functions

1. AGGREGATE FUNCTIONS:

The aggregate functions are sum, avg, min and count

Sum();The sum function is used to find the tota of a given column.

Eg: SQL >= Select Sum(Salary) from employee ;

Max():

This function is used to find the maximum value for the given column.

Eg: SQL>= Select max.(salary) from employee;

Min():

The minimum function is used to find the minimum data for the given column.

Eg: SQL >= select min (Salary) from employee;

Round():

The round function is used to round off the value to the fixed number of decimal place. It means, if the decimal value is > =5 then the function round the value to the next integer value

Eg;

SQL >=Select round (3.43) from dual;

Output:3

Length():

The length is a character or string function. It is used to find the total length of the given string.

Eg:

SQL >=SELECT length(‘computers’) from dual;

Output =9

UPPER():

The upper function is used to convert the given string into upper case letters.

Eg: SQL>=select upper (‘computers’) from dual ;

Output: COMPUTER

LOWER():

The lower is used to convert the given string into lower case letters . 

Eg;  SQL >select lower(‘MCA’) from dual;

OUTPUT :  mca

ASCII():

The ASCII  function is used to display the ASCII value for the given character.

Eg: SQL >SELECT ASCII(‘B’) from dual:

OUTPUT: 66

CHR():

It is used to give the equivalent character for the given ASCII value.

Eg : SQL > SELECT CHAR (97) from dual;

OUTPUT: a

LPAD():

It is used to place a particular character at left side for the given string.

SYNTAX:  SELECT Lpad (‘given string’, no of spaces , filling character);

Eg: SQL > select L pad (‘mca’, 3,  *) from dual;

OUTPUT:   ***mca.

RPAD():

THE Rpad command is used to place a particular character from right side for the given string.

SYNTAX:  SELECT Rpad (‘given string’, no of spaces, filling character);

Eg: SQL> select Rpad (‘MCA’, 3, *) from dual;

OUTPUT:   MCA***

COUNT():

The count function is used to count the no of values  in a given column.

Eg: SQL > select  “no of employees”  count (emp-no) from employee;

AVG():

This function is used to find the average of a given number / attribute.

Eg: SQL> Select avg(salary) from employee;

SQRT():

It is used to return the square root of the given number.

Eg: Select Sqrt (4) from dual ;

OutPut: 2

POWER():

It is used to return data raised to the power of ‘y’

SYNTAX :

Power (data,y);

Eg:   Select power (2, 4) from dual;

Output:16

ABS():

It is used to give the absolute value of a given number, even when it is a negative number.

It means any number given, then that will be converted into a positive value.

Eg: Select abs(-10) from dual;

Output :10

MOD():

The mod function is used to get the remainder after a division.

Eg; Select mod (10, 4) from dual;

Output 2

Floor():

It is used to round off the given value to the nearest lowest integer value.

Eg; Select for (3.65) from dual;

output:   3

CEIL():

It is used to convert the given value into the highest value.

Eg: Select ceil (3.65) from dual;

Output:   4

Date Functions():

Oracle supports date and time functions.

SYSDate();

It is used to get the current system date maintained by the System.

Eg: Select sysdate from dual;

Output 18-12-11

SysTime():

It is used to get the current system time.

Eg; Select Systime from dual;

OutPut: 10-05-35

Some of the formula used in date

D.
It gives the number of a given day, such as 1,2,3,…3, it is used to get the day in a week

Day:

It provides the character day for the given date such as Monday, Tuesday……Sunday.

MM:

It gives the month in a number for the given date such as 1,2,3.4,……12

Mon:

It gives the month in the character form like jan, feb, mar.

Month:

It gives the month in the character form like January, February, March….., December(12)

YY:

It gives the only the lost two digits in the year for the given date such as 09, 10, 11

YYYY

It gives the four digits of the year given date such as 2009, 2010, 2011.

Last date;

It is used to provide the last day or last date for the specified month in the given date.

EG: Select last_day (Sysdate) from dual;

Output 30-11-11

TO_Date;

It is used to convert any string as below

 TWO-Char();

It is used to convert any format as a string oracle supports a facility to order the records of a table.

Select from groupby;

This command is used to group all the records in a relation and display them for a selected set of fields.

SYNTAX < Set of fields > from < table-names>group by ;

Query

A query means accessing or getting a particular record form a table upon the given condition. The select statement is the most popular SQL statement& can be used to query table.

Some of the examples to query

1)       Select * from ;

It lists all attributes / fields from the table.

2)       Select first –name, last-name from employee;

It list the first name and last name form the table employee.

  

3)       Select first –name, last_name from employee where state=’AP’ ;

It lists the first –name and the last –name, from the table employee for which the state belongs to AP

4)       Select * from customer where status=’I’;

It lists all the attributes from customer table where the status attributes is ‘I’ ie invalid.

5)       Select * from customer where pin  is Null;

It lists all the attributes where pin code has not been entered (empty).

    6) Select DISTINCT state from customers; 

it is used to select the single occurrence of any value form the table.

   7) you can select the rows form a table by giving logical operator, ie, AND, OR, NOT 

for example, to select the rows of valid customer from the state AP, you can give the following query.

Eg: Select * from customer where state= ‘AP’ and status = ’v’;

This query displays all the rows that satisfy both the conditions.(State = AP as well as Status = ‘V’).

   8) To Select the rows of customer either form the state Karnataka or Kerala. We give.

Eg: Select* from customer where state = ’Karnataka’ or  state = ’Kerala’;     

   9) To list the name & state from the customer table by using concatenation we give the following (table by using comm) commands.

Eg: select first- name ||’ ‘ || last-name ||’  ‘|| ’ state from customer;

Note that  the split bar twice ( || ) is the concatenation operator, it is used to joining of multiple strings.

10) You can retrieve all the rows from the customers table except the customer number = 1005

Eg: Select * from customer where no cust -no = 1005

SUB- QUERYS

A sub query is a query within a query.  You can use the select statement as a part of another SQL statements

THE DULTER statement is called the parent and the nearest QUERY passes a value to the outer query. Note that the nearest query executes first

1. The nearest query must return a single column.

2. The result can only contains columns from the tables referenced in the over most query.

3. The nearest query returns a single row when the standard operators is used such as <,>,=…..e.t.c

4. The between operator cannot be used  with a sub query.

5. Sub-query’s can also be used with  insert, update and delete statements.

For example under to that list names of employees

Who do not work in the marketing department.

SQL>= SELECT EMP-NO, EMP-NAME FROM EMPLOYEE WHERE EMP-NO NOT IN

            (SELECT EMP-NO FROM SALARY WHERE DEPT = (‘MKTG’);

Similarly you can create a sub –query as,

SQL >  SELECT * FROM EMPLOYEE WHERE EMP-NO

           (SELECT EMP-NO, FROM SALARY WHERE DEDUCTION = 150);

It returns only the rows in salary table when deduction is 150.

You can use the operates as follows

SQL>=SELECT * FROM EMPLOYEE WHERE EMP-NO IN

           (Select EMP-NO from salary <150>);
<150>

It returns the more rows in salary table where deduction is less than 150

To select the name of the employees who are in the computer department a sub query can be given as

SQL>=select emp-name from employee where  emp-no in,

           (select emp-no from salary where dept = ‘comp’);

To select salary rows where basic is less than the average salary.

SQL >= select * from salary where basic < (select avg (BASIC)FROM SALARY );

Similarly to select the name of the employees who are in the marketing departments a sub query is given as ,

SQL >= select emp-name from employee where exists (select * from salary where emp.Emp-no = salary , emp-no  AND dept =’MKTG”);

VIEWS:

1.       Views are logical tables of a data extracted from existing tables. It can be queried just like a table but does not require any risk space.

2.       It can be used to hide sensitive data. For this user must have to grant the select privilege on the view.

3.       It can be used to hide complex query involving multiple tables.

4.       VIEWS can be created  with a cheek option preventing updating of other rows and columns

To create a view there are two  syntaxes is as follows,

SYNTAX:

1. CREATE VIEW AS QUERY;

2. REPLACE VIEW as query;

Eg: CREATE VIEW EMPLOYEE- view as follows,

SELECT EMP-NO, EMP-NAME FROM EMPLOYEE;                     to display view.

SYNTAX: SELECT * FROM ;

Eg: SQL>= select * from emp-view;               to see the column in a view.

DESCRIBE;

Eg:   DESC ;

To delete,

1. DROP view ;

Eg:     DROP VIEW EMP- VIEW ;

CURSER:

CURSORS are used when the sql select command\statement is  expected to run more than one row. Therefore, cursers in a better way to store the results of the recent query.

Syntax;

Cursor < cursor- name> is < select .Statement>;

A query must be declared and its definition contaians the query . The cursor must be declared section of the program also a cursor must be opened useful processing and close after processing.

EG: Cursor cur-sal is

       Select Emp-No, Basic, Commission

       From salary where basic >2500;

SYNTAX TO OPEN CURSOR

Eg : open <cur-name>;

SYNTAX TO STORE DATA IN CURSOR

Fetch <cur- name> into <var-1>,<var-2>;
Fetch <cur- name into <Record- Name>;
To close the cursor, the syntax is;
Syntax:  Close<cur- name>;

EG: close <cur-sal>;

There are four cursor attributes used to provide information on the status of a cursor.

a)       % found it determines if row was retrieved  use fetch

b)       Not found it determines if row was retrieved  fetch if a row was retrieved.

c)       % Row count – Row count is a when the cursor is a opened . The row count retrieve the number of rows are retrieved.

d)       % is open it is used to determine if a cursor is open.

      Note:Is open, is true if a cursor is open

        1.False  if a cursor is not open.