**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

- Projection
- Selection
- 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

- Column level
- Table level
- 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**

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.

**Syntax**

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

**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.

**Arithmetic operator:**+, -,*, /, MOD**Logical:**AND, OR, NOT.**Relational:**> , < ,>= ,<= ,!=,<>,^=,=**Concatenation:**||**Assignment:**=**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.

- System/predefined functions
- 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.

- Single row functions
- Multiple row functions
- Miscellaneous functions.

**Single row functions**

Single row functions are those functions will work on each and every record in the table and returns 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.

- STRING/CHAR
- MATH/NUMERIC
- DATE
- DATA Conversions

Multiple row functions are divided into 5 types.

- MAX
- MIN
- SUM
- AVG
- COUNT

Count divided into three types.

- COUNT(*)
- COUNT(EXP)
- COUNT(dis.EXP)

Miscellanies functions are classified into four types.

- NVL
- NVL2
- NULLIF
- 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; select ename , length(ename) from emp;

**6. LOWER(): **This function converts the String into the small letters.

**E.g**

select lower('ASHOK') from dual;

**7. UPPER(): **This function converts the String into the capital letters.

**E.g**

select upper('ashok') 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(‘BHARGAV’,’AV’) from dual; select rtrim('BAHRGAV ') 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; 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. SOUNDEX():** This function traces out similar words which are similar to pronounce.

**E.g**

select * from emp where soundex(ename)=soundex('ALINN'); select * from emp where soundex(ename)=soundex('KARLING');

In the above Example 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 sub string 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 Example 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('Ashok') 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 Example 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 cos, 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

- MONTHS_BETWEEN
- ADD_MONTHS
- LAST_DAY
- NEXT_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. Next_DAY():** This function is used to display next day .

**E.g**

select Next_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(Exp), COUNT(dis Exp).

**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(Exp):** This function is used to count the no of records in the given table including duplicate values but Excluding 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 Excluding duplicate and null values.

**E.g**

select count(distinct mgr) from emp;

**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

**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;

**HAVING**

This will work as where clause which can be used only with group by because of absence of where clause in group by.

**E.g**

select deptno, count(*), sum(sal), max(sal), min(Sal) from emp group by deptno having count(*)>5; select deptno, count(*), sum(sal), max(sal), min(sal), avg(sal) from emp where deptno=20 group by dept no;

Here we use where clause but we used where clause before the group by.

**Sequences**

- Sequence is a database object which automatically generates unique numeric values on a particular column in the table. Sequence default values start with 1 and it is incremented by +1 up to hard disk capacity.
- Sequence always generates numeric values we can apply the same sequence on more than one column in sequences on different columns in the same table.
- Sequence never generates character values.
- Sequence generates positive values ascending order and descending order.
- Sequence generates negative values ascending order and descending order.
- Sequence mechanism we can’t apply an alpha numeric columns.

**Syntax for sequence**

First we should create sequence.

**Syntax**

create sequence <sequence name>

**E.g**

create sequence s1;

##### **3.** **Joins**

- This is a mechanism which is used to combine or add one or more than one table.
- Joins are classified into following types those are

- Equi join/ simple join/ inner join
- Natural join
- Non – equi join
- Outer join
- Self join
- Cross join/ Cross product join / Cartesian product join

Whenever we need to get complete and whole information regarding a particular point then we need to join the tables.

**1. Simple or equi or Inner join**

- In this join the matching rows will be displayed as output.
- In this join we need to use to equal operator in the where clause of the select statement.
- If we want to perform the join operation at least we require one common column between the tables.
- In this join whenever the rows are not matching those records will not be displayed.

**Syntax**

SELECT * FROM <tab1>,<tab2>, -----------,<tab n> where <condition>

**E.g**

select e1.*, e2.* from std e1,std e2 where e1.deptno=e2.deptno; select e1.*, e2.no, e2.name from std e1, std e2 where e1.deptno=e2.deptno; select e1.*, e2.* from std e1 inner join std1 e2 on e1.deptno=e2.deptno;

**2. Natural join**

This join was introduced from Oracle 9i on wards, equi join and natural join both are Exactly same as far as output concerned. There are there difference between equi join and natural join.

- No need to check where condition.
- No need to mention table name before common column.
- Common column will be displayed in front of the output.

**E.g**

select * from emp natural join dept;

**Note**

for Example two tables contain same structure(eno,ename,marks) then its not possible to do natural join.

**3. Non equi join**

In this join we need to use only relational operator such as >,<,>=,<=,!= Except ‘=’ operator. The main advantage of non equi join is even through there is no common column. We can perform the join operation.

**E.g**

select * from manu m1, manu2 m2 where m1.no>=m2.no; select * from manu m1, manu2 m2 where m1.no>=m2.no and m1.deptno!=m2.deptno;

**4. Outer join **

In equi join there might be a chance of losing information to recover the lossed information we need to use outer joins. Outer joins are broadly divide into three types.

- Left outer joins
- Right outer joins
- Full outer joins

**1. Left outer join**

This join is the combination of equi join operation plus lossed information from the left hand side of the table.

**E.g**

select * from manu m1 left outer join manu1 m2 on m1.deptno=m2.deptno; // Oracle 8i model Left outer joins select * from manu m1, manu1 m2 where m1.deptno=m2.deptno(+);

**2. Right outer join**

This join is the combination of equi join operation + lossed information from the right hand side table.

**E.g**

select * from manu m1 right outer join manu1 m2 on m1.deptno=m2.deptno; // Oracle 8i model Right outer join select * from manu m1, manu1 m2 where m1.deptno(+)=m2.deptno;

**3. Full outer join**

This join is the combination of equi join operation + lossed information from left hand side + lossed information from right hand side table.

**E.g**

select * from manu full outer join manu1 on manu.deptno=manu1.deptno; // Oracle 8i model select * from manu m1, manu1 m2 where m1.deptno=m2.deptno(+) union select * from manu m1, manu1 m2 where m1.deptno(+)=m2.deptno;

**5. Self join**

Joining a table by itself known as self join. Whenever we have relationship between two columns in the same table then we need to use self join.

**E.g**

select e1.empno,e2.mgr from emp e1,emp e2 where e1.empno=e2.mgr;

**No use in real time**

**6. Cross join**

Cross join is the product of two or more than two tables it means that m no of rows in one table n no of tables in another table then we will get the product of mXn.

**E.g**

select * from emp1 cross join dept1;