Functions, Queries & Sub Queries
https://www.computersprofessor.com/2016/04/functions-queries-sub-queries.html
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;
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;
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
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.
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.
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.
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.
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
OutPut: 2
POWER():
It is used to return data
raised to the power of ‘y’
SYNTAX :
Power (data,y);
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
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.
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.
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’;
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>
<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>;
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.