VIEWS

VIEWS:

•       An Oracle VIEW, in essence, is a virtual table that does not physically exist. Rather,

                it is created by a query joining one or more tables.

•       It reduces Number of hits to the database

•       It improves the performance of queries and database

Create VIEW

CREATE VIEW view_name AS

  SELECT columns

  FROM tables

  [WHERE conditions];

EX:

CREATE VIEW V1

AS

SELECT ENAME,DNAME

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO;

GRANT CREATE VIEW TO SCOTT;

UPDATE VIEW:

CREATE OR REPLACE VIEW view_name AS

  SELECT columns

  FROM table

  WHERE conditions;

EX:

CREATE OR REPLACE VIEW V1

AS

SELECT ENAME,LOC

FROM EMP,DEPT

WHERE EMP.DEPTNO=DEPT.DEPTNO

Drop VIEW

Once an Oracle VIEW has been created, you can drop it with the Oracle DROP VIEW Statement.

Syntax:

DROP VIEW view_name;

EX:

DROP VIEW VIEW_NAME;

Complex Queries in SQL

Complex Queries in SQL

These questions are the most frequently asked in interviews.

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)
   select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from emp);
2. To select ALTERNATE records from a table. (ODD NUMBERED)
   select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from emp);
3. Find the 3rd MAX salary in the emp table.
   select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2 where e1.sal <= e2.sal);
4. Find the 3rd MIN salary in the emp table.
   select distinct sal from emp e1 where 3 = (select count(distinct sal) from emp e2where e1.sal >= e2.sal);
5. Select FIRST n records from a table.
   select * from emp where rownum <= &n;
6. Select LAST n records from a table
   select * from emp minus select * from emp where rownum <= (select count(*) - &n from emp);
7. List dept no., Dept name for all the departments in which there are no employees in the department.
   select * from dept where deptno not in (select deptno from emp); 
   alternate solution:  select * from dept a where not exists (select * from emp b where a.deptno = b.deptno);
   altertnate solution:  select empno,ename,b.deptno,dname from emp a, dept b where a.deptno(+) = b.deptno and empno is null;
8. How to get 3 Max salaries ?
   select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b where a.sal <= b.sal) order by a.sal desc;
9. How to get 3 Min salaries ?
   select distinct sal from emp a  where 3 >= (select count(distinct sal) from emp b  where a.sal >= b.sal);
10. How to get nth max salaries ?
    select distinct hiredate from emp a where &n =  (select count(distinct sal) from emp b where a.sal >= b.sal);
11. Select DISTINCT RECORDS from emp table.
    select * from emp a where  rowid = (select max(rowid) from emp b where  a.empno=b.empno);
12. How to delete duplicate rows in a table?
    delete from emp a where rowid != (select max(rowid) from emp b where  a.empno=b.empno);
13. Count of number of employees in  department  wise.
    select count(EMPNO), b.deptno, dname from emp a, dept b  where a.deptno(+)=b.deptno  group by b.deptno,dname;
14.  Suppose there is annual salary information provided by emp table. How to fetch monthly salary of each and every employee?

select ename,sal/12 as monthlysal from emp;

15. Select all record from emp table where deptno =10 or 40.

select * from emp where deptno=30 or deptno=10;

16. Select all record from emp table where deptno=30 and sal>1500.

select * from emp where deptno=30 and sal>1500;

17. Select  all record  from emp where job not in SALESMAN  or CLERK.

select * from emp where job not in ('SALESMAN','CLERK');

18. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.

select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');

19. Select all records where ename starts with ‘S’ and its lenth is 6 char.

select * from emp where ename like'S____';

20. Select all records where ename may be any no of  character but it should end with ‘R’.

select * from emp where ename like'%R';

21. Count  MGR and their salary in emp table.

select count(MGR),count(sal) from emp;

22. In emp table add comm+sal as total sal  .

select ename,(sal+nvl(comm,0)) as totalsal from emp;

23. Select  any salary <3000 from emp table. 

select * from emp  where sal> any(select sal from emp where sal<3000);

24. Select  all salary <3000 from emp table. 

select * from emp  where sal> all(select sal from emp where sal<3000);

25. Select all the employee  group by deptno and sal in descending order.

select ename,deptno,sal from emp order by deptno,sal desc;

26. How can I create an empty table emp1 with same structure as emp?

Create table emp1 as select * from emp where 1=2;

27. How to retrive record where sal between 1000 to 2000?
    Select * from emp where sal>=1000 And  sal<2000
28. Select all records where dept no of both emp and dept table matches.
    select * from emp where exists(select * from dept where emp.deptno=dept.deptno)
29. If there are two tables emp1 and emp2, and both have common record. How can I fetch all the recods but common records only once?
    (Select * from emp) Union (Select * from emp1)
30. How to fetch only common records from two tables emp and emp1?
    (Select * from emp) Intersect (Select * from emp1)
31.  How can I retrive all records of emp1 those should not present in emp2?
    (Select * from emp) Minus (Select * from emp1)
32. Count the totalsa  deptno wise where more than 2 employees exist.
    SELECT  deptno, sum(sal) As totalsal
    FROM emp
    GROUP BY deptno
    HAVING COUNT(empno) > 2
33. Nth record

SELECT * FROM emp

WHERE  rowid = (

            SELECT rowid FROM emp

            WHERE  rownum <= &n

            MINUS

            SELECT rowid FROM emp

            WHERE  rownum < &n);

Assignment questions on Single row functions

1. Display all the employees whose job has string ‘MAN’
2. Display all the employees whose name has ‘L’ as third character.
3. Display the result in the following format, first character in lower case rest in uppercase.
4. Display all the employees which has at lest 2L’s in it.
5. Display the number of occurrence of substring in a string. Ex:Display the number of L’s in each name
6. Display all the employees whose name is palindrome
7. Replace third character with * in ename column.
8. Display ename with first 3 char in uppercase and remaining in lowercase.

SINGLE ROW FUNCTIOONS

FUNCTIONS:

Function is a Reusable program that returns a value

There are 2 types of functions

1. Pre-defined or built in functions.
2. User defined functions.

• These are used both in SQL and PL/SQL. PL – Procedural Language (it’s a extension to SQL, can contain IF statements, loops, exceptions, OOPs, etc .. )

Functions are very powerful features of SQL and can be used to do the following

     Perform Calculations on data

     Modify individual data items

     Manipulate output for group of rows

     Format dates and number for display

     Converts  column data types

SQL functions are classified into

•       Single row function:

•       Multiple row function:

Single row functions:

•       Manipulate data items

•       Accept arguments  and return one value

•       Act on each row returned

•       Can be nested

•       Accept the arguments which can be column or expression

Single row functions

1. Character Functions:

Character functions are classified into

a)      Case manipulation function

b)      Character manipulation function

a)Case manipulation Functions:

1) Lower

2) Upper

3) INITCAP

Case manipulation Functions:

1. UPPER:

UPPER function converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

2. LOWER:

LOWER function converts all letters in the specified string to lowercase.

3. INITCAP:

INITCAP function sets the first character in each word to uppercase and the rest to lowercase.

SQL> select upper('Qspiders'),lower('QSPiders'),initcap('qspiders') from dual;

SQL> select upper(ename),lower(ename),initcap(ename) from emp;

select *

from emp where lower(job) like 'manager'

Dual – is a dummy table which is used for performing some independent operations which will not depend on any of the existing tables.

b)Character Manipulation Functions:

1)CONCAT

2)SUBSTR

3)LENGTH

4)INSTR

5)REPLACE

6) TRIM

Character Manipulation Functions:

CONCAT:  CONCAT function allows you to concatenate two strings together.

SYNTAX:

CONCAT( string1, string2 )

Ex:

SQL> select ename,job, concat(ename,job) from emp;

Concatenate Single Quotes

SELECT CONCAT('Let''s', ' learn Oracle') FROM dual;

1. Write a sql statement to display ‘SMITH is a clerk’

Note: Exactly 2 arguments can be given to concat function.

SUBSTR:

SUBSTR functions allows you to extract a substring from a string.

SYNTAX:

SUBSTR( string, start position [, length ] )

Parameters or Arguments

Ø  string The source string.

Ø  Start position The starting position for extraction.

    The first position in the string is always 1.

Ø  length Optional. It is the number of characters to extract. If this parameter is omitted, the SUBSTR function will return the entire string.

where position and length are both integers.

1. Display 1^st and last character of all the employees.

Length:

Length is used to find the length of the given string.

Syntax: Length(Str)

EX: Select Length(‘Qspiders’) from dual;

Display all the employees whose name has exactly 6 character.

INSTR:

INSTR function returns the location of a substring in a string.

SYNTAX:

INSTR( string, substring [, start_position [, nth_appearance ] ] )

Ex: Instr(‘Qspiders’, ‘s’ , 1 , 1) from dual;

1. Display all the employees whose name start with ‘S’

Replace:

REPLACE function replaces a sequence of characters in a string with another set of characters.

SYNTAX:

REPLACE( string1, string_to_replace [, replacement_string] )

Ex:

SQL> Select Replace('qspiders','s','*') from dual;

1.       Write a sql statement to display the number of spaces present in the given string

.

LTRIM:

LTRIM function removes all specified characters from the left-hand side of a string.

Syntax: LTRIM( string1 [, trim_string] )

RTRIM:

RTRIM function removes all specified characters from the right-hand side of a string.

Syntax: RTRIM( string1 [, trim_string] )

TRIM:  Is used to remove the all specified characters in the given string.

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 )

Ex:  select trim(leading 'e' from 'eewelcome') from dual

2. NUMBER FUNCTIONS:

Round: Round the Value to specified decimal

Ex: Round(23.34)à  23

Trunc:  Truncate the values to specified decimal.

Ex: Trunc(34.6554) à 34

Mod: Returns Reminder of division.

Ex: Mod(10,3)à 1

Ex:

SQL> SELECT * FROM EMP WHERE MOD(SAL,2)=1;

ABS Function:

ABS function returns the absolute value of a number.

Syntax:

ABS( number )

SQRT Function

SQRT function returns the square root of n.

Syntax:

SQRT( n )

POWER Function

POWER function returns m raised to the nth power.

Syntax:

POWER( m, n )

SIGN Function

SIGN function returns a value indicating the sign of a number

Syntax:

SIGN( number )

Note:

If number < 0, then sign returns -1.

If number = 0, then sign returns 0.

If number > 0, then sign returns 1.

Working with Dates:

ü  Oracle database stores dates in an internal numeric format:

     Century, Year, Month, day, hours, minutes, Seconds.

ü  The default date display format is DD-MON-YY

Ex: Select ename, hiredate from emp

•       Sysdate: It is a function that returns date

•       Systimestamp: It is a function that returns date,  time including milliseconds and time zone

Arithmetic With dates:

•       Add or subtract a number to or from a date for a resultant date value

•       Subtract two dates to find the number of days between those date.

Ex : To get future or past date

Select sysdate+100 , sysdate-70 from dual;

Ex: Experience with years;

Select ename,hiredate, round((sysdate-hiredate)/365) from emp;

ADD_MONTHS(date, n)

Adds the specific number of months (n) to a date. The ‘n’ can be both negative and positive:

Select add_months(sysdate, -1) as prev_month , sysdate, add_months (sysdate, 1) as next_month

from dual;

LAST_DAY(date) – Returns the last day in the month of the specified date.

select sysdate, last_day(sysdate) as last_day_curr_month,

last_day(sysdate) + 1 as first_day_next_month

from dual;

The number of days until the end of the month.

select last_day(sysdate) - sysdate as days_left

from dual

MONTHS_BETWEEN(date, date) – Calculates the number of months between two dates.

Example:

select MONTHS_BETWEEN ('31-MAR-2016', '28-FEB-2015')

from dual;

Let’s select the number of months an employee has worked for the company.

 Select months_between (sysdate, HIREDATE)

 from emp

GENERAL FUNCTION:

1> NVL(arg1,arg2)

•       If agr1 is null it returns arg2

•       If arg1 is not null then it returns itself

Converts a null to actual values:

Ex:

1.select ename,sal,comm,nvl(comm,0) from emp

2. SQL> select ename,sal,comm,sal+comm, sal+nvl(comm,0) totalsalary from emp;

NVL2:

SYNTAX:

NVL2(ARG1,AGR2,ARG3)

If Arg1 is null it returns Arg3

If Agr1 is not null then returns Agr2

Ex: SELECT ENAME,SAL, NVL2(COMM,COMM+SAL,SAL) "TOTAL SALARY" FROM EMP;

Conversion Functions:

Conversion function s are used to convert one data type into another data type

There are two type of conversion Functions

1. Implicit conversion Function

Ex: Select 10+’20’ from dual;

2. Explicit conversion function

Ex: Select 10+Ascii(‘A’) from dual;

TO_CHAR: TO_CHAR function is used to typecast a numeric or date input to character type 

TO_NUMBER: The TO_DATE function converts the characters to a date data type

TO_DATE: The TO_NUMBER function converts a character value to a numeric datatype. If the string being converted contains nonnumeric characters, the function returns an error.

Using TO_CHAR function with dates

TO_CHAR(date, ‘Format model’)

The format model:

•       It must enclosed with single quotation mark.

•       Is separated from the date value of a comma

Element of date format:

YYYYà Full year in number

YEARà year spelled out

MMà Two digit value for month

MONTHà Full name of month

MONà Three letter abbreviation of month

DYà Three letter abbreviation of day

DAYà Full name of day

DDà Numeric day of month

Ex: select sysdate,to_char(sysdate,'yyyy year mm month mon dy day dd') from dual

Display the joined day of all the employees?

SELECT CONCAT(ENAME,CONCAT(' JOINED ON ',TO_CHAR(HIREDATE,'DAY'))) FROM EMP

/

Display all the employees who joined on the weekends?

 Select ename,hiredate,to_char(hiredate,'day')

 from emp

 where to_char(hiredate,'DY') IN('SUN','SAT');