The %TYPE Attribute in PL/pgSQL

The %TYPE attribute

• Is used to declare a variable according to : 
• A database column definition
• Another declared variable
• Is prefixed with :
• The database table and column
• The name of the declared variable

Declaring Variables with the %TYPE Attribute


Syntax :
identifier table.column_name%TYPE;


Examples :
emp_name emp.ename%TYPE;
balance NUMERIC;
min_balance balance%TYPE := 1000;

Use of Variables in PL/pgSQL

Variables can be used for :

• Temporary storage of data
• Manipulation of stored values
• Reusability

Handling Variables in PL/pgSQL

Variables are :

• Declared and initialized in the declarative section
• Used and assigned new values in the executable section
• Passed as parameters to PL/pgSQL subprograms
• Used to hold the output of a PL/pgSQL subprogram

         DECLARE
         emp_hiredate DATE;
         emp_deptno NUMERIC := 10;
         location VARCHAR(13) := 'Atlanta';

Guidelines for Declaring PL/pgSQL Variables

• Avoid using column names as identifiers.
• Use the NOT NULL constraint when the variable must hold a value.

PL/pgSQL

PL/pgSQL Block Structure

• DECLARE (Optional) 
• Variables, 
• cursors,
• BEGIN (Mandatory) 
• SQL statements 
• PLPGSQL statements
• EXCEPTION (Optional) 
• Actions to perform when errors occur
• END; (Mandatory)

• All key words and identifiers can be written in mixed upper and lower case. Identifiers are implicitly converted to lowercase unless doublequoted.
• There are two types of comments in PL/pgSQL.
• -- starts a comment that extends to the end of the line.
• /* multi-line comments */

Procedural Languages

Procedural Languages Overview

• PostgreSQL allows user-defined functions to be written in a variety of procedural languages. 
• The database server has no built-in knowledge about how to interpret the function's source text. 
• Instead, the task is passed to a handler that knows the details of that particular language.
• PostgreSQL currently supports several standard procedural languages :
• PL/pgSQL
• PL/Tcl
• PL/Perl
• PL/Python
• PL/Java
• PL/Ruby
• Other languages can be defined by users

• PL/pgSQL is a loadable procedural language for the PostgreSQL database system.
• PL/pgSQL has several distinct features :
• Can be used to create functions and trigger procedures,
• Adds control structures to the SQL language,
• Can perform complex computations,
• Inherits all user-defined types, functions, and operators,
• Can be defined to be trusted by the server,
• Is easy to use.
  

Indexes

An index:

• Is a schema object
• Can be used by the PostgreSQL server to speed up the retrieval of rows by using a pointer
• Can reduce disk I/O by using a rapid path access method to locate data quickly
• Is independent of the table that it indexes
• Is used and maintained automatically by the PostgreSQL server

How Are Indexes Created?

• Automatically : A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.

• Manually : Users can create non unique indexes on columns to speed up access to the rows.

Creating an Index

• Create an index on one or more columns : 

         CREATE INDEX index ON table (column[, column]...);

• Improve the speed of query access to the 
• LAST_NAME column in the EMPLOYEES table :

         CREATE INDEX emp_last_name_idx ON employees(last_name);

Index Creation Guidelines :

Create an index when :

• A column contains a wide range of values
• A column contains a large number of null values
• One or more columns are frequently used together in a WHERE clause or a join condition
• The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table

Do not create an index when :

• The columns are not often used as a condition in the query
• The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table
• The table is updated frequently
• The indexed columns are referenced as part of an expression

Removing an Index

• Remove an index from the data dictionary by using the DROP INDEX command :

          DROP INDEX index;

• Remove the UPPER_LAST_NAME_IDX index 
• from the data dictionary :

          DROP INDEX emp_last_name_idx;

Sequences

A sequence:

• Can automatically generate unique numbers
• Is a sharable object
• Can be used to create a primary key value
• Replaces application code
• Speeds up the efficiency of accessing sequence values when cached in memory

CREATE SEQUENCE Statement: 

• Define a sequence to generate sequential numbers automatically
• Syntax:

        CREATE [ TEMPORARY | TEMP ] SEQUENCE name         
        [ INCREMENT [ BY ] increment ]         
        [ MINVALUE minvalue | NO MINVALUE ]         
        [ MAXVALUE maxvalue | NO MAXVALUE ]        
        [ START [ WITH ] start ]        
        [ CACHE cache ] [ [ NO ] CYCLE ]         
        [ OWNED BY { table.column | NONE } ]

Creating a Sequence

• Create a sequence named DEPT_DEPTID_SEQ to be used for the primary key of the DEPARTMENTS table. 
• Do not use the CYCLE option.

        CREATE SEQUENCE dept_deptid_seq 
                INCREMENT BY 10
                START WITH 120
                MAXVALUE 9999
                NO CYCLE;

NEXTVAL and CURRVAL Pseudocolumns

• NEXTVAL returns the next available sequence value. It returns a unique value every time it is referenced, even for different users.
• CURRVAL obtains the current sequence value.
• NEXTVAL must be issued for that sequence before CURRVAL contains a value.

Using a Sequence

• Insert a new department named “Support” in location Boston:

        INSERT INTO departments 
                       (department_id, department_name, location_id)
        VALUES (NEXTVAL(‘dept_deptid_seq’), 'Support', ‘Boston’);

• View the current value for the DEPT_DEPTID_SEQ sequence:

        SELECT CURRVAL(‘dept_deptid_seq’);

Caching Sequence Values

• Caching sequence values in memory gives faster access to those values.
• Gaps in sequence values can occur when:
• A rollback occurs
• The system crashes
• A sequence is used in another table

Modifying a Sequence

• Change the increment value, maximum value, minimum value, cycle option, or cache option:

        ALTER SEQUENCE dept_deptid_seq
                    INCREMENT BY 20
                    MAXVALUE 999999
                   NO CYCLE;

Creating a View

Creating a View

•   You embed a subquery in the CREATE VIEW statement:

         CREATE [OR REPLACE] [TEMP|TEMPORARY  ] VIEW view
         [(column_name,..)] AS subquery

• The subquery can contain complex SELECT syntax.
•  Create the EMPVU80 view, which contains details of employees in department 80:

        CREATE VIEW empvu80  AS SELECT employee_id, last_name, salary
        FROM employees WHERE  department_id = 80;

• Describe the structure of the view by using the psql \d command:

       \d empvu80

Retrieving Data from a View

SELECT * FROM   empvu80;


Creating a Complex View

• Create a complex view that contains group functions to display values from two tables:

    CREATE OR REPLACE VIEW dept_sum_vu (name, minsal, maxsal, avgsal)
    AS SELECT   d.department_name, MIN(e.salary), MAX(e.salary),AVG(e.salary)
    FROM     employees e JOIN departments d
    ON       (e.department_id = d.department_id)
    GROUP BY d.department_name;

Removing a View

• You can remove a view without losing data because a view is based on underlying tables in the database.

       Syntax :
       DROP VIEW view;

      Example :
      DROP VIEW empvu80;

Multiple-Row Subqueries

• Return more than one row
• Use multiple-row comparison operators

Using the ANY Operator in Multiple-Row Subqueries :

SELECT empno, ename, job, sal FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';

Using the ALL Operator in Multiple-Row Subqueries :

SELECT empno, ename, job, sal FROM emp WHERE sal < ALL (SELECT sal FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';
Null Values in a Subquery :

SELECT emp.ename FROM emp emp WHERE emp.empno NOT IN (SELECT mgr.mgr FROM emp mgr);

• The SQL statement in the slide attempts to display all the emp who do not have any subordinates. Logically, this SQL statement should have returned 12 rows. However, the SQL statement does not return any rows. One of the values returned by the inner query is a null value, and hence the entire query returns no rows
• Here we have to use IS NOT NULL in the inner query

Single-Row Subqueries

• Return only one row
• Use single-row comparison operators

Executing Single-Row Subqueries :

SELECT ename, job, sal FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7698) AND sal > (SELECT sal FROM emp WHERE empno = 7900);

Using Group Functions in a Subquery :

SELECT ename, job, sal FROM emp WHERE sal = (SELECT MIN(sal) FROM emp);

The HAVING Clause with Subqueries :

• The PostgreSQL server executes subqueries first
• The PostgreSQL server returns results into the HAVING clause of the main query

SELECT deptno, MIN(sal) FROM emp GROUP BY deptno HAVING MIN(sal) > (SELECT MIN(sal) FROM emp WHERE deptno = 30);

What Is Wrong with This Statement ?

SELECT empno, ename FROM emp WHERE sal = (SELECT MIN(sal) FROM emp GROUP BY deptno);

• ERROR : more than one row returned by a subquery used as an expression
• Single-row operator with multiple-row subquery
  

Using Subqueries to Solve Queries

• Who has a sal greater than Scott’s ?

• 

Subquery Syntax :

• SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table);

• The subquery (inner query) executes once before the main query (outer query)
• The result of the subquery is used by the main query

Using a Subquery :

• SELECT ename FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename = ‘Scott');

Guidelines for Using Subqueries :

1. Enclose subqueries in parentheses
2. Place subqueries on the right side of the comparison condition
3. The ORDER BY clause in the Subquery is not needed unless you are performing Top-N analysis
4. Use single-row operators with single-row subqueries, and use multiple-row operators with multiple-row subqueries

Types of Subqueries :

• Single-row subquery

• Multiple-row subquery

INNER Versus OUTER Joins

• In SQL the join of two tables returning only matched rows is called an inner join
• A join between two tables that returns the results of the inner join as well as the unmatched rows from the left (or right) tables is called a left (or right) outer join
• A join between two tables that returns the results of an inner join as well as the results of a left and right join is a full outer join

LEFT OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e LEFT OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

RIGHT OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e RIGHT OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

FULL OUTER JOIN :

• SELECT e.ename, e.deptno, d.dname FROM emp e FULL OUTER JOIN dept d ON (e.deptno = d.deptno) ;

• 

Non-Equijoins

Retrieving Records with Non-Equijoins :

• SELECT e.ename, e.sal, j.gra FROM emp e JOIN job_grd j ON e.sal BETWEEN j.lowest_sal AND j.highest_sal;

Outer Joins :

Self-Joins Using the ON Clause

• SELECT e.ename as emp, m.ename as mgr FROM emp e JOIN emp m ON (e.mgr = m.empno);

• 

Applying Additional Conditions to a Join :

• SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno) AND e.mgr = 7698 ;
  

Creating Joins with the ON Clause

• The join condition for the natural join is basically an equijoin of all columns with the same name
• Use the ON clause to specify arbitrary conditions or specify columns to join
• The join condition is separated from other search conditions
• The ON clause makes code easy to understand

Retrieving Records with the ON Clause :

• SELECT e.empno, e.ename, e.deptno, d.deptno, d.loc FROM emp e JOIN dept d ON (e.deptno = d.deptno);

• 

Creating Joins with the USING Clause

• If several columns have the same names but the data types do not match, the NATURALJOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin
• Use the USING clause to match only one column when more than one column matches
• Do not use a table name or alias in the referenced columns
• The NATURAL JOIN and USING clauses are mutually exclusive

Joining Column Names :



Retrieving Records with the USING Clause :

• SELECT emp.empno, emp.ename, dept.loc, deptno FROM emp JOIN dept USIN (deptno) ;

Few Important Points reg Joins

Qualifying Ambiguous Column Names :

• Use table prefixes to qualify column names that are in multiple tables
• Use table prefixes to improve performance
• Use column aliases to distinguish columns that have identical names but reside in different tables
• Do not use aliases on columns that are identified in the USING clause and listed elsewhere in the SQL statement

Using Table Aliases :

• Use table aliases to simplify queries
• Use table aliases to improve performance

SELECT e.empno, e.ename, d.loc, deptno FROM emp e JOIN dept d USING (deptno) ;

Creating Natural Joins

• The NATURAL JOIN clause is based on all columns in the two tables that have the same name
• It selects rows from the two tables that have equal values in all matched columns
• If the columns having the same names have different data types, an error is returned

Retrieving Records with Natural Joins :

• SELECT empno, dname,loc FROM emp NATURAL JOIN dept ;

• 

Creating Joins with the USING Clause :

• If several columns have the same names but the data types do not match, the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equijoin
• Use the USING clause to match only one column when more than one column matches
• Do not use a table name or alias in the referenced columns
• The NATURAL JOIN and USING clauses are mutually exclusive

Joining Column Names :



Retrieving Records with the USING Clause :

• SELECT emp.empno, emp.ename, dept.loc, deptno FROM emp JOIN dept USIN (deptno) ;

Displaying Data from Multiple Tables

Obtaining Data from Multiple Tables :



Types of Joins :

• Joins that are compliant with the SQL:1999 standard include the following :

1. Cross joins
2. Natural joins
3. USING clause
4. Full (or two-sided) outer joins
5. Arbitrary join conditions for outer joins

Joining Tables Using SQL : 1999 Syntax

• Use a join to query data from more than one table

SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]| [CROSS JOIN table2];

Restricting Group Results

• In the same way that you use the WHERE clause to restrict the rows that you select, you use the HAVING clause to restrict groups

• To find the maximum salary in each of the departments that have a maximum salary greater than $10,000, you need to do the following :

1. Find the average salary for each department by groupingby department number
2. Restrict the groups to those departments with a maximum salary greater than $10,000

Restricting Group Results with the HAVING Clause :

• When you use the HAVING clause, the PostgreSQL server restricts groups as follows:

1. Rows are grouped
2. The group function is applied
3. Groups matching the HAVING clause are displayed

SELECT column, group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];
Example :

• SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING MAX(sal)>4000 ;
• SELECT job, SUM(sal) as PAYROLL FROM emp WHERE job NOT LIKE '%MAN%' GROUP BY job HAVING SUM(sal) > 4000 ORDER BY SUM(sal);

SELECT column, group_function FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[HAVING group_condition]
[ORDER BY column];

Illegal Queries - Using Group Functions

• Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY clause

• SELECT deptno, COUNT(ename) FROM emp;
• ERROR : column "emp.deptno" must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803
• Column missing in the GROUP BY clause

• You cannot use the WHERE clause to restrict groups
• You use the HAVING clause to restrict groups
• You cannot use group functions in the WHERE clause

• SELECT deptno, AVG(sal) FROM emp WHERE AVG(sal) > 8000 GROUP BY deptno;
• WHERE AVG(salary) > 8000
  ERROR : aggregates not allowed in WHERE clause
• Cannot use the WHERE clause to restrict groups