Netside where knowledge is shared, ideas are spread.

[Notes] CSCI 585 DB Advanced Structured Query Language (SQL)

04 Feb 2020 | CSCI585, English/英文, Note

Credit to: Prof. Saty Raghavachary, CSCI 585, Spring 2020

outline

  • How to use the advanced SQL JOIN operator syntax
  • About the different types of subqueries and correlated queries
  • How to use SQL functions to manipulate dates, strings, and other data
  • About the relational set operators UNION, UNION ALL, INTERSECT, and MINUS
  • How to create and use views and updatable views
  • How to create and use triggers and stored procedures
  • How to create embedded SQL

SQL Join Operators

Recall that we looked at examples of joining - entries from two tables, and entries from a single table. These joins were based on ‘join conditions’.

It is also possible to join tables using the ‘JOIN’ keyword..

  • Relational join operation merges rows from two tables and returns rows with one of the following
    • Natural join - Have common values in common columns
    • Equality or inequality - Meet a given join condition
    • Outer join: Have common values in common columns or have no matching values
    • Inner join: Only rows that meet a given criterion are selected

Table 8.1 - SQL Join Expression Styles Table 8.1 - SQL Join Expression Styles

Subqueries and Correlated Queries

  • Subquery is a query inside another query
  • Subquery can return:
    • One single value - One column and one row
    • A list of values - One column and multiple rows
    • A virtual table - Multicolumn, multirow set of values
    • No value - Output of the outer query might result in an error or a null empty set

WHERE Subqueries

  • Uses inner SELECT subquery on the right side of a WHERE comparison expression
  • Value generated by the subquery must be of a comparable data type
  • If the query returns more than a single value, the DBMS will generate an error
  • Can be used in combination with joins

IN and HAVING Subqueries

  • IN subqueries
    • Used to compare a single attribute to a list of values
  • HAVING subqueries
    • HAVING clause restricts the output of a GROUP BY query by applying conditional criteria to the grouped rows

Multirow Subquery Operators: ANY and ALL

  • ALL operator
    • Allows comparison of a single value with a list of values returned by the first subquery
      • Uses a comparison operator other than equals
  • ANY operator
    • Allows comparison of a single value to a list of values and selects only the rows for which the value is greater than or less than any value in the list

Note that ‘greater than ALL’ is eqvt to ‘greater than the largest of’. ‘ALL’ is used to select rows [plural in general] that comparison-succeed against all values in a list.

‘ANY’ is used to select rows [plural in general] that comparison-succeed with any value in a list.

Note that ‘= ANY(list of values)’ is equivalent to the ‘IN’ operator (which is itself equivalent to multiple == conditions joined by ORs). So the following are all equivalent, for a given value of ‘M’:

(M==6) OR (M==8) OR (M==10) 

M IN (6,8,10) 

M = ANY (6,8,10)

So loosely speaking, ALL is equivalent to AND, and ANY is equivalent to OR.

FROM Subqueries

  • FROM clause:
    • Specifies the tables from which the data will be drawn
    • Can use SELECT subquery

Attribute List Subqueries

These subqueries determine what columns get output by the main query - they can be actual (existing) columns or computed columns or results of aggregate functions.

These are also known as ‘column subqueries’ or ‘inline subqueries’.

  • SELECT statement uses attribute list to indicate what columns to project in the resulting set
  • Inline subquery
    • Subquery expression included in the attribute list that must return one value
  • Column alias cannot be used in attribute list computation if alias is defined in the same attribute list

Correlated Subquery

not efficient

  • Executes once for each row in the outer query
  • Inner query references a column of the outer subquery
  • Can be used with the EXISTS special operator

In a correlated subquery, the inner (sub) query is repeatedly run, for each row of the outer query! The inner is said to be (co-)related with the outer query when it references a column in the outer query’s table. This is in effect, like a double (nested) ‘for’ loop..

Here is the Wikipedia entry on correlated subqueries. This is the example shown there [select employees who make more than the average salary for their department]:

SELECT employee_number, name
  FROM employees AS Bob
  WHERE salary > (
    SELECT AVG(salary)
      FROM employees
      WHERE department = Bob.department);

In the above, the outer query “passes in”, for each employee (each row), the employee’s dept. [which the inner query refers to as Bob.department]. The inner query selects all salaries for that dept., computes the average, compares it with the passed-in employee’s salary; if the test passes, the outer query selects the employee’s # and name.

Queries: summary

We looked at several variations of queries and subqueries (SELECT, WHERE, HAVING, IN..).

Most interestingly, a SELECT subquery can appear at the top (SELECT), middle (FROM) or bottom (WHERE) of a parent query, which provides a flexible way to express complex logic (since such subqueries can be recursively nested)

SQL Functions

  • Functions always use a numerical, date, or string value
  • Value may be part of a command or may be an attribute located in a table
  • Function may appear anywhere in an SQL statement where a value or an attribute can be used

Types

  • Date and time functions
  • Numeric functions
  • String functions
  • Conversion functions

Relational Set Operators

  • SQL data manipulation commands are set-oriented
    • Set-oriented: Operate over entire sets of rows and columns at once
  • UNION, INTERSECT, and Except (MINUS) work properly when relations are union-compatible
    • Union-compatible: Number of attributes are the same and their corresponding data types are alike
  • UNION
    • Combines rows from two or more queries without including duplicate rows
    • Syntax - query UNION query
  • UNION ALL
    • Produces a relation that retains duplicate rows
    • Can be used to unite more than two queries
  • INTERSECT
    • Combines rows from two queries, returning only the rows that appear in both sets
    • Syntax - query INTERSECT query
  • EXCEPT (MINUS)
    • Combines rows from two queries and returns only the rows that appear in the first set
    • Syntax
      • query EXCEPT query
      • query MINUS query
  • Syntax alternatives
    • IN and NOT IN subqueries can be used in place of INTERSECT

Virtual Tables: Creating a View

  • View: Virtual table based on a SELECT query
  • Base tables: Tables on which the view is based
  • CREATE VIEW statement: Data definition command that stores the subquery specification in the data dictionary
    • CREATE VIEW viewname AS SELECT query

Oracle Sequences

  • Independent object in the database
  • Have a name and can be used anywhere a value expected
  • Not tied to a table or column
  • Generate a numeric value that can be assigned to any column in any table
  • Table attribute with an assigned value can be edited and modified
  • Can be created and deleted any time

Figure 8.27 - Oracle Sequence

NEXTVAL returns the current value, then does ++; CURRVAL just fetches the current value (does not ++ it).

Procedural Language SQL (PL/SQL)

PL/SQL involves extra (augmented) syntax that lets us do looping, branching, variable declaration and function declaration - these are of course not possible using ‘plain’ SQL.

PL/SQL can be used to create:

  • blocks of code for one-time execution
  • triggers - callbacks to invoke
  • stored procedures - named procedures (no return values) for repeated calling
  • stored functions - named functions (with return values) for repeated calling


  • Performs a conditional or looping operation by isolating critical code and making all application programs call the shared code
    • Yields better maintenance and logic control
  • Persistent stored module (PSM): Block of code containing:
    • Standard SQL statements
    • Procedural extensions that is stored and executed at the DBMS server


  • Procedural Language SQL (PL/SQL)
    • Use and storage of procedural code and SQL statements within the database
    • Merging of SQL and traditional programming constructs
  • Procedural code is executed as a unit by DBMS when invoked by end user
  • End users can use PL/SQL to create:
    • Anonymous PL/SQL blocks and triggers
    • Stored procedures and PL/SQL functions Table 8.9 - PL/SQL Basic Data Types

Triggers

  • Procedural SQL code automatically invoked by RDBMS when given data manipulation event occurs
  • Parts of a trigger definition
    • Triggering timing - Indicates when trigger’s PL/SQL code executes (BEFORE or AFTER)
    • Triggering event - Statement that causes the trigger to execute (INSERT, UPDATE, or DELETE).
    • Triggering level
      • Statement-level trigger: if you omit the FOR EACH ROW keywords. This type of trigger is executed once, before or after the triggering statements is completed (default)
      • Row-level trigger: requires use of the FOR EACH ROW keywords. This type of triggers is executed once for each row affected by the triggering statement.
    • Triggering action - PL/SQL code enclosed between the BEGIN and END keywords. Each statement inside the PL/SQL code mush end with a semicolon (;)
CREATE OF REPLACE TRIGGER trigger_name
  [BEFORE/AFTER] [DELETE/INSERT/UPDATE OF column_name] ON table_name
  [FOR EACH ROW]
  [DECLARE]
  [variable_name date tyoe[:=initial_value]]
BEGIN
  pl/sql instructions;
  ...
END;
  • DROP TRIGGER trigger_name command
    • Deletes a trigger without deleting the table
  • Trigger action based on DML predicates
    • Actions depend on the type of DML statement that fires the trigger

Stored Procedures

  • Named collection of procedural and SQL statements
  • Advantages
    • Reduce network traffic and increase performance
    • Reduce code duplication by means of code isolation and code sharing
CREATE OR REPLACE PROCEDURE procedure_name [(argument [IN/OUT]date-type, ...)]
      [IS/AS]
      [variable_name data type[:=initial_value]]
BEGIN
  pl/sql instructions;
  ...
END;

PL/SQL Stored Functions

Reminder - these can RETURN a value.

  • Stored function: Named group of procedural and SQL statements that returns a value
    • As indicated by a RETURN statement in its program code
  • Can be invoked only from within stored procedures or triggers

once such a function is defined, it can be CALLED inside triggers or in stored procedures

CREATE FUNCTION FUNCTION function_name(argument IN data-type, ...) 
RETURN data-type [IS]

BEGIN
  pl/sql instructions;
  ...
  RETUEN (value or expression)
END;

example Creating/defining a function:

FUNCTION findMax(x IN number, y IN number) 
RETURN number
IS
    z number;
BEGIN
   IF x > y THEN
      z:= x;
   ELSE
      Z:= y;
   END IF;

   RETURN z;
END;

Calling/executing/running the function:

DECLARE
   a number;
   b number;
   c number;
BEGIN
   a:= 23;
   b:= 45;

   c := findMax(a, b);
   dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
/

Result:

Maximum of (23,45): 45

Related Posts