Row Numbering and Ranking Functions

1. ROWNUM without Analytic Function

select employee_id, name , salary, ROWNUM
from employees ORDER BY salary;exec

2. ROW_NUMBER ( )

select employee_id, name, salary, ROW_NUMBER() OVER (ORDER BY salary  )toprank
FROM employees;exec

3. RANK ( )

select employee_id, name, salary, RANK() OVER (ORDER BY salary )toprank
FROM employees;exec

4. DENSE_RANK ( ) with WHERE clause

select * from
( select employee_id, name, salary, DENSE_RANK() OVER (ORDER BY salary  desc ) toprank
FROM employees )t
where t.toprank < 3;exec

5. RANK ( ) with order by

select employee_id, first_name, salary, RANK() OVER (ORDER BY salary  desc) toprank
FROM employees
order by salary;exec

6. RANK ( ) with a join

select e.employee_id, e.name, j.job_title, e.salary,
RANK() OVER (ORDER BY e.salary desc) rankorder
from employees e, jobs j
where e.salary < 3000
and e.job_id = j.job_id
order by salary;exec

7. RANK ( ) contains a join, group by and order by

select j.job_title, COUNT(*), MAX(e.salary) maxsalary,
MIN(e.salary) minsalary,
RANK() OVER (ORDER BY MAX(salary)) rankorder
FROM employees e, jobs j
where e.salary < 5000
AND e.job_id = j.job_id
GROUP BY j.job_title
ORDER BY j.job_title;exec

8. RANK and DENSE_RANK ( ); GROUP BY, HAVING and ORDER BY clause

More than one analytical function can be used in one sql.

select j.job_title,COUNT(*),MAX(salary) maxsalary,
MIN(salary) minsalary,RANK () OVER (ORDER BY MAX(salary)) rankorder,
DENSE_RANK () OVER (ORDER BY MIN(salary)) rankorder
FROM employees e, jobs j
WHERE e.salary < 6000
AND e.job_id = j.job_id
GROUP BY j.job_title
HAVING MIN(salary) > 2000
ORDER BY j.job_title desc;exec

9. ROW_NUMBER ( ) and NULLS LAST

select e.employee_id, e.name, e.salary curr_salary ,
ROW_NUMBER () OVER (ORDER BY salary desc NULLS LAST ) salary
from employees e
ORDER BY curr_salary desc NULLS LAST;exec

10. RANK ( ),  NVL function and NULLS LAST

SELECT e.employee_id, e.name, NVL (salary, 9999),
RANK () OVER (ORDER BY NVL (salary,9999) desc NULLS LAST) salary
from empnls e
where employee_id < 115
ORDER BY salary desc NULLS LAST;exec

11. RANK ( ), Order by after NLV function

SELECT employee_id,
name,
NVL(salary, 6666),
RANK() OVER (ORDER BY NVL (salary, 6666) desc) curr_salary
FROM empnls
ORDER BY curr_salary;exec

12. RANK ( ), Partitioning with PARTITION_BY

SELECT employee_id,
       name,
       department_id,
       salary,
       RANK() OVER(PARTITION BY department_id ORDER BY salary desc) rank
  FROM employees
  ORDER BY department_id;exec

13.  NTILE ( )

This function must apply same segment at the same intervals except last interval and if the remaining segment is equals or more than rownum, segment is always equals rownum.

Interval = (ROUND(rowcount /segment));

SELECT employee_id,
       name,
       salary,
       NTILE(4) OVER(ORDER BY salary desc) ntlist4,
       NTILE(8) OVER(ORDER BY salary desc) ntlist8,
       NTILE(100) OVER(ORDER BY salary desc) ntlist107,
       NTILE(120) OVER(ORDER BY salary desc) ntlist120
 FROM employees;exec

Case expression in sql

We can use case expression in PL/SQL basically or as a sql expression to extend query

flexibility.

Basic Sytax :

case when <condition> then <value>
when <condition> then <value>
...
else <value

end

Basic Example :

select sal, case when sal < 2000 then 'low'
                 when sal < 3000 then 'middle'
                 when sal < 4000 then 'high'
                 else 'top'
            end as "categories"
from emp;

Synonyms

 

When you reference an object in a SQL command that is owned by another user, you must preface the object name with the owner’s username.

 

SELECT * FROM scott.dept ;

 

A synonym is an alternate name for an object that references the owner name, as well as the object name.

 

Public Synonyms

 

A public synonym can be created only by a DBA or by a user who has the privilege to create public synonyms.

 

CREATE synonym_name FOR owner_name.tablename.

 

CREATE PUBLIC SYNONYM public_dept

FOR scott.dept;

SELECT *  FROM

Public_dept;

 

Private Synonyms

 

When a user creates a private synonym, only that user can use the private synonym.

 

CREATE SYNONYM synonym_name FOR owner_name.object_name

 

CREATE SYNONYM private_dept

FOR scott.dept;

Indexes

 

A database index is a table that contains data values, along with a corresponding column that specifies the physical locations of the records (ROWID) that contain the associated sorted data values.

You can find rowid column for every record in each table.

 

SELECT rowid, total_hours FROM project_consultant

ORDER BY total_hours;

 

Oracle automatically creates an index on the primary key of the table.

Managing records in an indexed table can be slow process because the DBMS must modify the index every time the record values changed.

 

Creating an Index

 
CREATE INDEX index_name ON tablename (index_column);

Index name should be index_tablename_fieldname.

 

CREATE INDEX project_consultant_total_hours

ON project_consultant (total_hours);

Composite indexes can contain multiple columns that the DBMS can use for identifying the row location.

 

CREATE INDEX index_name ON tablename ( index_column1, index_column2, …);

CREATE INDEX consultant_skill_skill_id_cert

ON consultant_skill (skill_idi certification);

View Index Information Using the Data Dictionary Views

You can retrieve the names of the indexes in your user schema by querying the USER_INDEXES data dictionary view.

 

SELECT index_name

FROM user_indexes;

Dropping an Index

DROP INDEX index_name;

Database Views

 

A view does not physically exist in the database. It is derived from other database tables. When the data in its source table is updated , the view reflects the updates as well.

If structure of a view’s source table is altered, or if a view’s source table is dropped, then the view becomes invalid and can no longer be used until it is dropped and re-created or replaced.

 

Creating and Manipulating Simple Views

CREATE VIEW view_name AS sql_query; 
The view name must follow the Oracle naming standards.
The view name can not already exist in the user’s database schema.

You must have sufficient object privileges are related with the view to manage the records.

CREATE OR REPLACE VIEW view_name AS Sql_query;

 

 

CREATE VIEW faculty_view as

SELECT f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank

FROM faculty

 

SELECT f_last, f_first, bldg_code, room

FROM faculty_view, location

WHERE faculty_view.loc_id = location.loc_id;

 

Creating and Manipulating Complex Views

 

Complex views, unlike simple views, cannot be used to directly manage data in the underlying database tables.

 

CREATE VIEW inventory_view AS

SELECT item_desc, item_size, color, price, qoh, price*qoh AS value

FROM inventory, item

WHERE item.item_id = inventory.item_id

ORDER BY item_desc;

 

Dropping Views

 

DROP VIEW view_name;

 

Using Data Dictionary Views to Retrieve Information about the Views

 

Dictionary views: All_views

                               User_views

 

You can only see information about views for which you have been granted object privileges.

SELECT view_name

FROM ALL_VIEWS

WHERE owner = ‘SYSTEM’;

Selecting Records For Update

      When you enter a SELECT command to query database records, no locks are placed on the selected records, and other database users can use view and update these records at the sane time you are viewing them. This is necessary databases with the many concurrent users.

     However, records that have been changed with an INSERT or UPDATE statement are locked until the user holding the lock releases it by issuing a COMMIT or ROLLBACK command.

     Sometimes you might want to view a record and then update it in the same transaction.

    You must be able to view the quantity on hand and then update it in a single transation. This can be achieved by using SELECT FOR UPDATE command in oracle.

      The SELECT FOR UPDATE command palces a shared lock on the record, which means that other users can view the data but con not update or delete the data.

 

SELECT column1, column2, …

FROM table1, table2, …

WHERE search and join conditions

FOR UPDATE OF column1, column2, …

NOWAIT;

The NOWAIT command instructs Oracle to not wait if the selected record is currently locked by another user. It is used for the DELETE and UPDATE command.

Using Set Operators to Combine Query Results

Set Operator	Purpose
UNION	Returns all rows from both queries, but displays duplicate rows only once.
UNION ALL	Returns all rows from both queriesi and displays all duplicate rows.
INTERSECT	Return only rows returned by both queries.
MINUS	Returns the rows returned b the first query minus the matching rows returned by the second query.

UNION AND UNION ALL

SELECT s_last, s_first, s_phone

FROM student

– hide one of the duplicate rows

UNION

SELECT f_last, f_frst f_phone

FROM faculty;

 

 

SELECT s_last, s_first, s_phone

FROM student

– show all rows

UNION ALL

SELECT f_last, f_frst f_phone

FROM faculty;

 

INTERSECT

 

SELECT f_first, f_last

FROM faculty, location

WHERE faculty.loc_id = location.loc_id

AND bldg_code = ‘BUS’

INTERSECT

SELECT f_first, f_last

FROM faculty, location, course_section

WHERE faculty.f_id = course_section.f_id

AND location.loc_id = course_section.loc_id

AND bldg_code = ‘BUS’;

 

MINUS

 

SELECT f_first, f_last

FROM faculty, location

WHERE faculty.loc_id = location.loc_id

AND bldg_code = ‘BUS’

MINUS

SELECT f_first, f_last

FROM faculty, location, course_section

WHERE faculty.f_id = course_section.f_id

AND location.loc_id = course_section.loc_id

AND bldg_code = ‘BUS’;

Creating Nested Query

 

A nested query is created when a second query, called a subquery, is nested within a main query.

The main query is the first query that is listed in the SELECT command.

The subquery is used to specify values that are used in a search condition in the main query.

 

Creating Subqueries That Return a Single Value

 

SELECT column1, column2,…

FROM table1,table2,…

WHERE join conditions

AND search_column1 = (SELECT column1

                                          FROM table1, table2,…

                                          WHERE search and join conditions)

 

 

 

SELECT s_last, s_first

FROM student

WHERE s_class =

(SELECT s_class

FROM student

WHERE s_last = ‘Mobley’

AND s_first = ‘Amanda’);

 

Creating Subqueries that Return Multiple Values

 

SELECT column1, column2,…

FROM table1,table2,…

WHERE join conditions

AND search_column1 = IN (SELECT column1

                                          FROM table1, table2,…

                                          WHERE search and join conditions)

 

SELECT DISTINCT s_last, s_first

FROM student, enrollment

WHERE student.s_id = enrollment.s_id
AND c_sec_id IN

( SELECT c_sec_id FROM student, enrollment.s_id

AND s_last = ‘Mobley’,

AND s_first = ‘Amanda’ ) ;

 

Using Multiple Subqueries

 

SELECT DISTINCT s_last, s_first

FROM student, enrollment

WHERE student.s_id = enrollment.s_id

AND c_sec_id IN

( SELECT c_sec_id

FROM student, enrollment

WHERE student.s_id = enrollment.s_id

AND s_last = ‘Mobley’

AND s_first = ‘Amanda’ )

AND s_class =

(SELECT s_class

FROM student

WHERE s_last = ‘Mobley’

AND s_first = ‘Amanda’);

 

Creating Nested Subqueries

 

When a subquery contains a subquery it is called nested subquery.

 

SELECT DISTINCT s_last, s_first

FROM student, enrollment

WHERE student.s_id = enrollment.s_id

AND c_sec_id IN

( SELECT course_section.c_sec_id

FROM student, enrollment, course_section

WHERE student.s_id = enrollment.s_id

AND enrollment.c_sec_id  = course_section.c_sec_id

AND s_last = ‘Moley’

AND s_first = ‘Amanda’

AND course_section.c_sec_id IN

(SELECT c_sec_id

FROM course_section , location

WHERE course_section.loc_id = location.loc_id

And bldg_code = ‘CR’));

 

 Creating Subqueries That Use the EXISTS Operator

 

SELECT c_last

FROM consultant, consultant_skill

WHERE colsultant .c_id = colsultant_skill.c_id

HAVING COUNT(skill_id) > 2

GROUP BY c_last;

 

Retrieving names of consultants who have two or more skills

 

SELECT c_last, skill_description

FROM colsultant , consultant _skill  con_skill_alias, skill

WHERE consultant.c_id = con_skill_alias.c_id

AND skill.skill_id = con_skill_alias.skill_id

AND EXISTS

     (SELECT c_id

        FROM consultant_skill

        – using the table alias in the subqery

        WHERE con_skill_alias.c_id = c_id

        HAVING COUNT ( skill_id) > 2

        GROUP BY c_id );

 

Note : If you do not use the table alias to associate the main query with the subquery the subquery always evaluates as TRUE.

 

The criteria for returning only values for consultants with more than two skills ignored.

Therefore, when you use the EXISTS operator in a subquery, you must always use a table alias to correlate the parent query to subquery.

Joining Multiple Tables

The general syntax of a SELECT statement that joins two tables is :

SELECT column1, column2, …

FROM table1, table2

WHERE table1.join_column = table2.join_column ;

 

Inner ( Equality ) Joins

 

SELECT s_id, s_last, s_first, student.f_id, f_last

FROM student, faculty

WHERE student.f_id = faculty.f_id ;

 

Outer Joins

 

An outer join returns all rows in the first table, which is called the inner table, along with their

Matching rows in the second table which is called the outer table. What makes an outer join different is that it also returns values  from the iner tablet that do not have a match in the outer table.

 

To create an outer join in Oracle, use syntax outer_table.join_column (+) in the join condition.

 

The plus sign ( + ) , which is called the outer join marker, indicates that a NULL value is inserted fort the fields in the outer table that do not have matching row in the inner table.

 

 

SELECT inventory.inv_id, item_size, color, qoh, shipment_id, date_received, ship_quantity

FROM inventory, shipment_line

WHERE inventory.inv_id = shiment_line.inv_id (+);

Self Joins

 

When you crate a query based on this relationship by joining a table to itself, you create a self-join.

 

To create a self-join, you must create a table alias and structure the query as if you are joining the table to a copy of itself. A table alias is an alternate name assigned to a table in the FROM clause of a query.

 

When you create a table alias, you must then use the alias, rather than the table name, to qualify field names in the SELECT clause and in join conditions.

 

FROM table1 alias1.

 

WHERE sub_project.parent_p_id = parent_project.p_id.

 

 

SELECT parent_project.project_name “Parent Project”, sub_project.project_name “Sub Project” FROM project parent_project, Project sub_project

WHERE sub_project.parent_p_id = parent_project.p_id;

 

Inequality Joins

 

        Tables can also be joined using an inequality statement, where each record in one table is joined with every record in the second table where the join condition satisfies an inequality condition.

 

SELECT f_last, faculty.loc_id, location.loc_id

FROM faculty, location

WHERE faculty.loc_id < location.loc_id;

Oracle SQL Group Functions

Function	Description	Example	Result
AVG (fieldname)	Returns the average value of a numeric column’s returned values	AVG (capacity )	33.23030768
COUNT ( fieldname)	Returns an integer representing a count of the number of returned rows. COUNT (field_name) counts only rows where the specified field is not NULL, while COUNT (*) counts all rows	COUNT (*)   COUNT (grade)	All row Row number that exists   grade
MAX ( fieldname)	Returns the maximum value of a numeric column’s returned values	MAX (max_enrl)	140
MIN (fieldname)	Returns the minimum value of a numeric column’s returned values	MIN (max_enrl)	30
SUM (fieldname)	Sums a numeric column’s returned values	SUM (capacity)	432

 

 

SELECT SUM ( max_enrl), AVG (max_enrl), MAX (max_enrl), MIN (max_enrl)

FROM course_section

WHERE term_id = 6;

SELECT COUNT (*)

FROM enrollment

WHERE c_sec_id = 1010;

 

SELECT COUNT (grade )

FROM enrollment

WHERE c_sec_id = 1010;

Using the GROUP BY Clause with Group Functions

 

SELECT bldg_code, SUM (capacity)

FROM location

GROUP BY bldg_code;

 

SELECT bldg_code, room, SUM (capacity)

FROM location

GROUP BY bldg_code, room ;

 

Filtering Grouped Data Using the HAVING Clause

 

HAVING group_function operator search_condition

 

SELECT bldg_code, SUM (capacity)

FROM location

GROUP BY bldg_code

HAVING SUM (capacity) >= 100;