Recent Posts

Monday, 25 April 2016

Data Retrieval Language


Data Retrieval Language

SELECT
     This command is used to retrieve the data from the existing table. Using this command we can retrieve all records and also we can retrieve some specific records in the table(Using where clause).
Syntax
SELECT * FROM < Table Name>
E.g
SELECT * FROM EMP;
Here * represents all columns.
By using select command we can retrieve the data in three ways
1. Projection
2. Selection
3. Joins

1. Projection

     Retrieve the data from specific columns in the table is called projection.
Syntax:
SELECT col1, col2, --------------------, coln from <table name>
ALIASES
     Alias is duplicate name or an alternative name for the original column name or table name or expression name. Whenever we need to submit meaningful or understanding reports then we need to use alias names.
We can provide the alias names in three levels
1. Column level
2. Table level
3. Expression level.
1. Column level Alias
     Providing the alias name for the columns is called column level alias.
Syntax:
SELECT COL1 AS “ALIAS NAME”, COL2 AS “ALIAS NAME” ,---------,COLUMN NAME N AS “ALIAS NAME” FROM <Table name>;
E.g:
SELECT EID AS “EMPLOYEEID”, ENAME AS “EMPLOYEE NAME” FROM EMP;
     In the above example the keyword as is an optional keyword.
Syntax:
SELECT COL1 “ALIAS NAME”, COL2 “ALIAS NAME”, -------,COLN “ALIAS NAME” FROM <Table name>
E.g:
SELLECT EID “EMPLOYEEID”, ENAME “ EMPLOYEE NAME” FROM EMP;
     In the above example we no need to use double quotation.
Syntax:
SELECT COL1 ALIASNAME, COL2 ALIASNAME,--------,COLN ALIASNAME FROM <Table name>
E.g:
SELECT EID EMPLOYEEID, ENAME EMPLOYEENAME FROM EMP;
2. Table Level alias
     TABLE ALIASES are used to shorten your SQL to make it easier to read or when you are performing a self join (ie: listing the same table more than once in the FROM clause).
E.g:
SELECT p.product_id, p.product_name, categories.category_name
FROM products p
INNER JOIN categories
ON p.category_id = categories.category_id
ORDER BY p.product_name ASC, categories.category_name ASC;
3. Expression Level alias
     Providing the alias names for expressions is known as expression level alias.
E.g:
SELECT EID EMPLOYEEID, ENAME EMPLOYEENAME, SAL SALARY, SAL*12 ANNUALSALARY FROM EMP;
E.g:
SELECT  EID EMPLOYEEID, ENAME EMPLOYEENAME, SAL ANNUALSALARY, SAL*12 ANNUALSALARY FROM EMP WHERE ANNUALSALARY>150000
Output:
“ANNUAL SALARY” in valid identifier because Alias names are not identifiers. Identifier means column name.
Note:
     The above example the fallowing error message- “Annual salary invalid identifier”. So we cant check the conditions of Alias names.
E.g:
SELECT EID EMPLOYEEID, ENAME EMPLOYEENAME, SAL SALARY, SAL*12 ANNUALSALARY FROM EMP WHERE SAL*12>150000
E.g:
SELECT EMP.*, SAL*12 ANNUALSALARY FROM EMP WHERE SAL*12>150000
Where clause
     This clause is used to check the condition based on the condition we can retrieve, we can modify, and we can remove some specific records in the table.
Operators in SQL
     Operator means special symbol which performs some specific operations on operators.
1. Arithmetic operator:  +, -,*, /, MOD
2. Logical:  AND, OR, NOT.
3. Relational: > , < ,>= ,<= ,!=,<>,^=,=
4. Concatenation: ||
5. Assignment: =
6. Miscellaneous: Between, not between, like, not like, in, not in, is null, is not null, exists, not exists, any, all, some, union, union all, intersect, minus.

2. Selection

     Retrieve the data based on some condition is known as selection.
Syntax
SELECT * FROM EMP WHERE <Condition>
E.g:
SELECT FROM EMP WHERE SAL<=3000 and DEPTNO in(10);
Order By
* This clause is used to arrange the data either ascending or descending order. By default order by clause will arrange the data in ascending order.
* If we want to arrange the data in descending order then we use an option called DESC stands for descending order.
* We can order by clause on character columns then it will arrange the data in alphabetic order.
* We can apply the order by clause on more than one column in the same table.
* We can apply the order by clause only with the “select” command.
Syntax by applying order by clause
SEELCT * FROM EMP ORDER BY <Column name> <ASCE><DESC>
E.g:
Select * from xyz order by ename desc;
Functions
* A function is a self contained block which is use to perform some task.
* The main advantage of function is code reusability.
* Then it automatically reduces the redundancy of the instructions so that reduces the maintenance cost and increase the system performance.
* Function always returns only one value.
Functions are dividing into two types.
1. System/predefined functions
2. User defined functions
1. System defined functions
     A function which is defined by the system comes along with the software those functions can be called as System defined functions.
2. User defined functions
     A function which is defined manually and programmatically those functions are called used defined functions.
     In SQL predefined functions are further divided into three types.
1. Single row functions
2. Multiple row functions
3. Miscellaneous functions.
Single row functions
     Single row functions are those functions will work on each and every record in the table and returs one value.
Multiple row functions
     Multiple row functions are those functions will work on the entire table and returns only one value.
Miscellaneous functions
     Miscellaneous functions are those functions which are used to convert the null values into not null values.
     Single row functions are further divided into following types.
1. STRING/CHAR
2. MATH/NUMERIC
3. DATE
4. DATA Conversions
     Multiple row functions are divided into 5 types.
1. MAX 
2. MIN
3. SUM
4. AVG
5. COUNT
     Count divided into three types.
1. COUNT(*)
2. COUNT(EXP)
3. COUNT(dis.EXP)
     Miscellanies functions are classified into four types.
1. NVL
2. NVL2
3. NULLIF
4. COALASCE
STRING/CHAR FUNCTIONS
1. ASCII(): This function returns the ASCII value from the high level language code.
E.g: 
select ASCII('a') from dual;
2. Chr(): This function return the high level language value from ASCII code.
E.g:  
SQL> select chr(97) from dual;
3. CONCAT: This function is used to concatenate  two strings
E.g: 
select concat('Sathya','Technologies') from dual;
4. INITCAP(): This function allows first letter of every word into the Capital letter.
E.g:  
select initcap('siva bhargava reddy') from dual;
5. LENGTH: This function returns the length of the String.
E.g:  
select length('Sathya technologies') from dual;
E.g2:  
select ename , length(ename) from emp;
6. LOWER(): This function converts the String into the small letters.
E.g:  
select lower('BHARGAV') from dual;
7. UPPER(): This function converts the String into the capital letters.
E.g: 
select upper('bhargav') from dual;
8. LPAD(): This function is used to adding the special character from the left hand side to right hand side.
E.g: 
select LPAD('Sathya', 10, '*') from dual;
     If we give negative number or zero it won’t display any thing.
9. RPAD():   This function adds the no of special characters from the right hand side to left hand side.
E.g:  
select RPAD('Sathya',10,'*') from dual;
10. LTRIM(): This function allows us to remove the blank spaces from the left hand side of the string.
E.g: 
select ltrim('      Bhargav') from dual;
11. RTRIM(): This function allows us to remove the blank spaces from the right hand side of the string.
E.g:  
select rtrim('BAHRGAV     ') from dual;
Select rtrim(‘BHARGAV’,’AV’) from dual;
12. TRIM():  This function trims both sides of values.
E.g:  
select trim( 'i' from 'indiani') from dual;
     We can use the trim function fallowing ways. 
E.g: select trim( leading'i' from 'indiani') from dual;
E.g2: select trim( trailing'i' from 'indiani') from dual;
13. REPLACE(): This function is used to replaces more than a character.
E.g: 
select replace('jack and jar','ja','bl') from dual;
14. REVERSE(): This function allows us to reverse a word.
E.g:  
select reverse('bhargav') from dual;
15. SOUNDE.g(): This function traces out similar words which are similar to pronounce.
E.g: 
select * from emp where soundE.g(ename)=soundE.g('ALINN');
select * from emp where soundE.g(ename)=soundE.g('KARLING');
     In the above E.gample I used KARLING it is similar to CARLING. But letters are not similar hence forth it shows error message.
16. SUBSTR(): This function is to be used to returns the path of the string from the main string.
E.g: 
select substr('Sathya technologies','7') from dual;
select substr('Sathya technologies','7','5') from dual;
     By using above command we can get substring through 7 and no of letters to display through 5.
Real time scenario
     If someone would like to know acno in case forgot accno then if we know last no then we can use this function to retrieve the accno.
17. Translate: This function is used to replaces a character with another character with in the string.
E.g:  
select translate ('jack and jue','j','b') from dual;
select translate('jack and jue','j','bl') from dual;
     In the above E.gample we got only one value translate. Because it works on one value.
18. VSIZE(): This function is used to count the no of bytes bytes  occupied by the given string.
E.g: 
select vsize('Bhargav') from dual;
Math functions
1. ABS(): This function is used to convert –VE values into +VE values.
E.g:
select abs(-9.5) from dual;
2. CEIL(): This function is used to round the given number to highest number.
E.g:
select ceil(-9.4) from dual;
3. FLOOR():  This function is used to round the given number to least number.
E.g:
select floor(-9.4) from dual;
4. Greatest(): This function is used to returns the maximum value from list of numbers.
E.g:
select greatest (5,6,7,8) from dual;
5. Least(): This function is used to returns the minimum value from list of numbers.
E.g:
select least(5,6,7,8) from dual;
6. MOD(): this function returns reminder value from given number.
E.g:
select mod(10,2) from dual;
7. ROUND(): This function round converts given number to nearest number.
E.g:
Select round(-9.4) from dual;
Select round(-9.5) from dual;
     The above E.gample shows the output -10 because round treats 9.5 as 10.
8. SIGN(): This function returns the sign of the given value.
E.g:
select sign(-9.4) from dual;
9. SIN(): This function give sin value of given number.
E.g:
select sin(12) from dual;
     We can get cot,tan,cot,sec,cosec also.
10. SQRT(): This function square roots the given function.
E.g:
select sqrt(16) from dual;
Date functions
     These functions are classified into four types
1. MONTHS_BETWEEN
2. ADD_MONTHS
3. LAST_DAY
4. NE.gT_DAY
1. MONTHS BETWEEN(): This function is used to find out months between two dates.
E.g:
select MONTHS_BETWEEN( sysdate, '12-FEB-11') from dual;
2. ADD_MONTHS:  This function is used to add the no of months to the given date.
E.g:
select ADD_MONTHS(sysdate, 12) from dual;
3. LAST_DAY(): This is used to display last day from current day or other days.
E.g:
select last_day(sysdate) from dual;
4. NE.gT_DAY(): This function is used to display nE.gt day .
E.g:
select NE.gt_day(SYSDATE) from dual;
Group and Aggregate functions
     These functions are classified into five types those are SUM(), MAX(), MIN(), AVG(), COUNT().
1. SUM(): This function is used to find out sum of the all values of a particular column in the table.
E.g:
select sum(sal) from emp;
2. AVG(): This function is used to find out an average value of table’s column
E.g:
Select avg(sal) from dual;
3. MAX(): This function is used to find maximum value of particular table’s column
E.g:
select MAX(SAL) from emp;
4. MIN(): This function is used to find minimum value of particular column’s value.
E.g:
select MIN(SAL) from emp;
5. COUNT(): This function is used to count the no of records in the given table. It contains the sub functions COUNT(*), COUNT(E.gp), COUNT(dis E.gp).
i. Count(): This function is used to count the no of records in the given table including duplicate and null values.
E.g:
select count(*) from emp;
ii. Count(E.gp):  This function is used to count the no of records in the given table including duplicate values but E.gcluding null values.
E.g:
select count(mgr) from emp;
iii. Count(distinct): This function is used to count no of records in the given table E.gcluding duplicate and null values.
E.g:
select count(distinct mgr) from emp;
Data Conversion Functions
1. Bin_to_num
2. Char_to_rowid
3. Rowid_to_char
4. To_number
5. To_char
6. To_date
1. BIN_TO_NUM: This will convert the binary value to its numerical equivalent.
Syntax: bin_to_num( binary_bits)
E.g:
SQL> select bin_to_num(1,1,0) from dual;
BIN_TO_NUM(1,1,0)
-----------------
       6
* If all the bits are zero then it produces zero.
* If all the bits are null then it produces an error.
2. CHARTOROWID: This will convert a character string to act like an internal oracle row identifier or rowid.
3. ROWIDTOCHAR: This will convert an internal oracle row identifier or rowid to character string.
4. TO_NUMBER: This will convert a char or varchar to number.
E.g:
SQL> select to_number('10') from dual;
TO_NUMBER('10')
---------------
     10
5. TO_CHAR: This will convert a number or date to character string.
SQL> select to_char(round(to_date('24-dec-06','dd-mon-yy')), 'dd mon yyyy hh:mi:ss am') from dual;
TO_CHAR(ROUND(TO_DATE('
---------------------------------
24 dec 2006 12:00:00 am
E.g:
select to_char('24-dec-06') from dual;
6. TO_DATE: This will convert a number, char or varchar to a date.
E.g:
SQL> select round(to_date('24-dec-04','dd-mon-yy'),'year'), round(to_date('11-mar-06','dd-mon-yy'),'year') from dual;
ROUND(TO_ ROUND(TO_
------------   ---------------
01-JAN-05   01-JAN-06
SQL> select round(to_date('11-jan-04','dd-mon-yy'),'month'), round(to_date('18-jan-04','dd-mon-yy'),'month') from dual;

ROUND(TO_ ROUND(TO_
-------------  ---------------
01-JAN-04    01-FEB-04

SQL> select round(to_date('26-dec-06','dd-mon-yy'),'day'), round(to_date('29-dec-06','dd-mon-yy'),'day') from dual;
ROUND(TO_ ROUND(TO_
--------------  --------------
24-DEC-06     31-DEC-06
E.g:
select dob from date_test1 where dob=to_date('1999-05-12','yyyy-mm-dd');
Similar to insert and update.
E.g: update date_test1 set dob=to_date('1999-09-24','yyyy-mm-dd') where dob='23-SEP-19';
Miscellaneous functions
1. NVL (): This function is used to converting the null values into not null values. These are classified into following types.
1. NVL()
2. NVL2()
3. NULLIF()
4. COALESCE()
1. NVL(): This function is used to converting the null values into not null values.
Syntax:
NVL(a,b)  the first parameter  a represents column names and b represents replaced values if column contains null value then it is replaced with b value.
E.g:
select empno,ename,sal,comm,sal+nvl(comm,100) from emp;
select  emp.*,nvl(comm,100) as NEWSALARY from emp;
2. NVL2(): This function is used to converting the  null values into not null values.
Syntax:
NVL2(a,b,c)
E.g:
select emp.*,nvl2(comm,100,200) as NEWSALARY from emp;
     Here a represents column name, b and c represents replaced values. If column contains null values then it is replaced with c if column doesn’t contain null value then it is replaced with B.
3. NULLIF(): This function is used to converting the not null values into null values.
Syntax:
NULLIF(a,b) if a value, b values both are equal then it is replaced with null if a not equal to b it replaced with a value.
E.g:
select ename,length(ename),job,length(job), nullif(length(ename),length(job)) from emp;
4. COALESCE(): This function returns first occurance of not null value.
E.g:
select coalesce(null,20,null,20) from dual;
Case Statement
     It is also working as a switch case statement in C language. The only difference between if and switch case is in case of if condition we can check the condition or relation between more than one variable at a time but where as in switch statement we can check all the conditions on one variable.
E.g:
Select sal,
          Case sal
                    When 500 then ‘low’
                    When 5000 then ‘high’
                    Else ‘medium’
          End case
          From emp;
Decode: It will checks value by value whatever we specify afterwards display result .
E.g:
select sal, decode(sal,1234,'LOW',2975,'MEDIUM',5000,'HIGH') from emp;
Rowid and Rownum: These psudo columns are used to retrieve specific record from the duplicate records.
Syntax:
select rowid, rownum from <Table name>
E.g:
select rowid,rownum from emp;
Syntax for retrieve specific rowid:
Syntax:
SELECT * FROM <Table name> WHERE ROWID LIKE ‘% ROWIDVALUE’
     We can’t retrieve row num through like command. We can retrieve only row id is possible.
E.g:
select * from emp where rowid like '%C';
     (sysdate, current_date these are also psudo columns)
Group by clause
* This clause is used to divide the values depending on it’s a true.
* Group by clause always used along with group functions.
E.g:
select deptno,count(*),sum(sal),max(sal),min(sal),avg(Sal) from emp group by deptno;

Next Tutorial  Data Retrieval Language Part 2

Previous Tutorial  Data Manipulation Language 

No comments:

Post a Comment