DATABASE

SQL Join Operators

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

Inner join

Only rows that meet a given criterion are selected

Table 8.1 SQL Join Expression Styles

JOIN[1]	Join Type	SQL SYNTAX EXAMPLE	DESCRIPTION
CROSS	CROSS JOIN	SELECT * FROM T1, T2	Returns the Cartesian product of T1 and T2 (old style).
		SELECT * FROM T1 CROSS JOIN T2	Returns the Cartesian product of T1 and T2.
INNER	Old-Style JOIN	SELECT * FROM T1, T2 WHERE T1.C1 = T2.C1	Returns only the rows that meet the join condition in the WHERE clause (old style). Only rows with matching values are selected.
	NATURAL JOIN	SELECT * FROM T1, T2 WHERE T1 NATURAL JOIN T2	Returns only the rows that meet the join condition in the WHERE clause(old style). Only rows with matching values are selected.
	JOIN USING	SELECT * FROM T1 JOIN T2 USING (C1)	Returns only the rows with matching values in the columns indicated in the USING clause.
	JOIN ON	SELECT * FROM T1 JOIN T2             ON T1.C1 = T2.C1	Returns only the rows that meet the join condition indicated in the ON clause.
OUTER	LEFT JOIN	SELECT * FROM T1 LEFT OUTER JOIN T2             ON T1.C1 = T2.C1	Returns rows with matching values and includes all rows from the left table (T1) with unmatched values.
	RIGHT JOIN	SELECT * FROM T1 RIGHT OUTER JOIN T2             ON T1.C1 = T2.C1	Returns rows with matching values and includes all rows from the right table (T2) with unmatched values.
	FULL JOIN	SELECT * FROM T1 FULL OUTER JOIN T2             ON T1.C1 = T2.C1	Returns rows with matching values and includes all rows from both tables (T1 and T2) with unmatched values.

JOIN[1] - JOIN CLASSIFICATION

Outer join 

Have common values in common columns or have no matching values

Table 8.1 SQL Join Expression Styles

JOIN[1]	Join Type	SQL SYNTAX EXAMPLE	DESCRIPTION
OUTER	LEFT JOIN	SELECT * FROM T1 LEFT OUTER JOIN T2             ON T1.C1 = T2.C1	Returns rows with matching values and includes all rows from the left table (T1) with unmatched values.
	RIGHT JOIN	SELECT * FROM T1 RIGHT OUTER JOIN T2             ON T1.C1 = T2.C1	Returns rows with matching values and includes all rows from the right table (T2) with unmatched values.
	FULL JOIN	SELECT * FROM T1 FULL OUTER JOIN T2             ON T1.C1 = T2.C1	Returns rows with matching values and includes all rows from both tables (T1 and T2) with unmatched values.

For example, the following query lists the product code, vendor code, and vendor name for all products and includes all product rows (products without matching vendors) as well as all vendor rows (vendors without matching products):

SELECT PCODE, VENDOR.V_CODE, V_NAME
FROM VENDOR FULL JOIN PRODUCT ON VENDOR.V_CODE = PRODUCT.V_CODE;

Results are shown in Figure 8.12

Subqueries and Correlated Queries

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

SELECT P_CODE, P_PRICE FROM PRODUCT
WHERE P_PRICE >= (SELECT AVG(P_PRICE) FROM PRODUCT);

SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER JOIN INVOICE USING (CUS_CODE)
              JOIN LINE USING (INU_NUMBER)
              JOIN PRODUCT USING (P_CODE)
WHERE P_CODE IN (SELECT P_CODE FROM PRODUCT WHERE P_DESCRIPT = 'Claw hammer');

IN subqueries

Used to compare a single attribute to a list of values

SELECT DISTINCT CUS_CODE, CUS_LNAME, CUS_FNAME
FROM CUSTOMER JOIN INVOICE USING (CUS_CODE)
              JOIN LINE USING (INU_NUMBER)
              JOIN PRODUCT USING (P_CODE)
WHERE P_CODE IN (SELECT P_CODE FROM PRODUCT WHERE P_DESCRIPT LIKE '%hammer%' OR P_DESCRIPT LIKE '%saw%');

HAVING subqueries

HAVING clause restricts the output of a GROUP BY query by applying conditional criteria to the grouped rows

SELECT P_CODE, SUM(LINE_UNITS)
FROM LINE
GROUP BY P_CODE
HAVING SUM(LINE_UNITS) > (SELECT AVG(LINE_UNITS) FROM LINE);

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

SELECT P_CODE, P_QOH*P_PRICE
FROM PRODUCT
WHERE P_QOH*P_PRICE > ALL
(SELECT P_QOH*P_PRICE FROM PRODUCT
WHERE V_CODE IN (SELECT V_CODE FROM VENDOR WHERE V_STATE='FL'));

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

FROM Subqueries

FROM clause:

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

All customers who bought both specified products

Figure 8.17 FROM subquery example

SELECT DISTINCT CUSTOMER.CUS_CODE, CUSTOMER.CUS_LNAME
FROM CUSTOMER,
(SELECT INVOICE.CUS_CODE
FROM INVOICE NATURAL JOIN LINE WHERE P_CODE = '13-Q2/P2') CP1, (SELECT INVOICE.CUS_CODE
FROM INVOICE NATURAL JOIN LINE WHERE P_CODE = '23109-HB') CP2 
WHERE CUSTOMER.CUS_CODE = CP1.CUS_CODE AND
         CP1.CUS_CODE = CP2.CUS_CODE;

Attribute List 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
  

Figure 8.18 Inline subquery example

SELECT P_CODE, P_PRICE, (SELECT AVG(P_PRICE) FROM PRODUCT) AS AVGPRICE, P_PRICE-(SELECT AVG(P_PRICE) FROM PRODUCT) AS DIFF
FROM PRODUCT;

Figure 8.19 Another example of an inline subquery

SELECT P_CODE, SUM(LINE_UNITS*LINE_PRICE) AS SALES, 
       (SELECT COUNT(*) FROM EMPLOYEES) AS ECOUNT,
       SUM(LINE_UNITS*LINE_PRICE)/(SELECT COUNT(*) FROM EMPLOYEE) AS CONTRID
FROM LINE
GROUP BY P_CODE;

Correlated Subquery

• 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
  

Until now, all subqueries you have learned execute independently. That is, each subquery in a command sequence executes in a serial fashion, one after another. The inner subquery executes first; its output is used by the outer query, which then executes until the last outer query executes (the first SQL statement in the code).
In contrast, a correlated subquery is a subquery that executes once for each row in the outer query. That process is similar to the typical nested loop in a programming language. For example:
FOR X = 1 TO 2
            FOR Y = 1 TO 3
                        PRINT “X = “X, “Y = “Y END
            END

END

The relational DBMS uses the same sequence to produce correlated subquery results:

1. It initiates the outer query.
2. For each row of the outer query result set, it executes the inner query by passing the outer row to the inner query.

That process is the opposite of that of the subqueries as you have already seen. The query is called a correlated subquery because the inner query is related to the outer query by the fact that the inner query references a column of the outer subquery.

Figure 8.20 Corrected subquery examples

SELECT INV_NUMBER, P_CODE, LINE_UNITS
FROM LINE LS
WHERE LS.LINE_UNITS >
  (SELECT AVG(LINE_UNITS)
  FROM LINE LA
  WHERE LA.P_CODE = LS.P_CODE);

SELECT INV_NUMBER, P_CODE, LINE_UNITS,
          (SELECT AVG(LINE_UNITS) FROM LINE LX WHERE LX.P_CODE = LS.P_CODE) AS AVG
FROM LINE LS
WHERE LS.LINE UNITS > 
          (SELECT AVG(LINE_UNITS)
          FROM LINE LA
          WHERE LA.P_CODE = LS.P_CODE);

SQL Functions

Functions always use a numerical, date, or string value

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

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

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
    

Relational Set Operators

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

	Syntax
UNION	query UNION query UNION ALL	Combines rows from two or more queries without including duplicate rows UNION ALL: - Produces a relation that retains duplicate rows  - Can be used to unite more than two queries
INTERSECT	query INTERSECT query	Combines rows from two queries, returning only the rows that appear in both sets IN and NOT IN subqueries can be used in place of INTERSECT
Except (MINUS)	query EXCEPT query query MINUS query	Combines rows from two queries and returns only the rows that appear in the first set

Virtual Tables: Creating a View

View: Virtual table based on a SELECT query

Base tables: Tables on which the view is based

statement		command
CREATE VIEW	Data definition command that stores the subquery specification in the data dictionary	CREATE VIEW viewname AS SELECT query
Updatable Views	Used to update attributes in any base tables used in the view

• Batch update routine: Pools multiple transactions into a single batch to update a master table field in a single operation
• Updatable view restrictions:
  • GROUP BY expressions or aggregate functions cannot be used
  • Set operators cannot be used
  • JOINs or group operators cannot be used

CREATE VIEW PRICEGT50 AS
      SELECT P_DESCRIPT, P_QOH, P_PRICE
      SELECT PRODUCT
      SELECT P_PRICE > 50.00;

SELECT * FROM PRICEGT50;

CREATE VIEW PSUUPD AS (
      SELECT PRODMASTER.PROD_ID, PROD_QOH, PS_QTY
      FROM PRODMASTER, PRODSALES
      WHERE PRODMASTER.PROD_ID = PRODSALES.PROD_ID);

SELECT * FROM PSVUPD;

UPDATE PSUUPD
SET PROD_QOH = PROD_QOH - PS_QTY;

SELECT * FROM PRODMASTER;

How to create and use triggers and stored procedures

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

CREATE SEQUENCE CUS_CODE_SEQ START WITH 20010 NOCACHE;
CREATE SEQUENCE INU_NUMBER_SEQ START WITH 4010 NOCACHE;
SELECT * FROM USER_SEQUENCES;

INSERT INTO CUSTOMER VALUES (CUS_CODE_SEQ.NEXTVAL, 'Connery', 'Sean', NULL, '615', '898-2007', 0.00);
SELECT * FROM CUSTOMER WHERE CUS_CODE = 20010;

INSERT INTO INVOICE
VALUES (INU_NUMBER_SEQ.NEXTVAL, 20010, SYSDATE);
SELECT * FROM INVOICE WHERE INU_NUMBER = 4010;

INSERT INTO LINE
VALUES (INU_NUMBER_SEQ.CURVAL, 1, '13-02/P2', 1, 14.99);
INSERT INTO LINE
VALUES (INU_NUMBER_SEQ.CURVAL, 2, '23109-HB', 1, 9.95);
SELECT FROM LINE WHERE INU_NUMBER = 4010;

COMMIT;

Procedural SQL

• 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
    

BEGIN
INSERT INTO VENDOR
VALUES (25678, 'Microsoft Corp.', 'Bill Gates', '765', '546-8484', 'WA', 'N');
END;
/

SET SERVEROUTPUT ON

BEGIN
INSERT INTO VENDOR
VALUES (25572, 'Clue Store', 'Issac Hayes', '456', '323-2009', 'VA', 'N');
DBMS_OUTPUT.PUT_LINE('New Vendor Added!');
END;
/

SELECT * FROM VENDOR;

TABLE 8.8 PL/SQL Basic Data Types (P340)

DATA TYPE	DESCRIPTION
CHAR	Character values of a fixed length; for example: W_ZIP CHAR(5)
VARCHAR2	Variable length character values; for example: W_FNAME VARCHAR2(15)
NUMBER	Numeric values; for example: W_PRICE NUMBER(6,2)
DATE	Date values; for example: W_EMP_DOB DATE
%TYPE	Inherits the data type from a variable that you declared previously or from an attribute of a database table; for example: W_PRICE PRODUCT.P_PRICE%TYPE Assigns W_PRICE the same data type as the P_PRICE column in the PRODUCT table

Triggers

• Procedural SQL code automatically invoked by RDBMS when given data manipulationevent occurs
  • A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
    
  • A database definition (DDL) statement (CREATE, ALTER, or DROP).
    
  • A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
    
  • Triggers could be defined on the table, view, schema, or database with which the event is associated.
    
• Parts of a trigger definition
  • Triggering timing - Indicates when trigger’s PL/SQL code executes
    • BEFORE or AFTER. This timing indicates when the trigger’s PL/SQL code executes; in this case, before or after the triggering statement is completed.
      
  • Triggering event - Statement that causes the trigger to execute (INSERT, UPDATE, or DELETE)
  • Triggering level
    • A statement-level trigger is assumed if you omit the FOR EACH ROW keywords. This type of trigger is executed once, before or after the triggering statement is completed. This is the default case.
    • A row-level trigger requires use of the FOR EACH ROW keywords. This type of trigger is executed once for each row affected by the triggering statement. (In other words, if you update 10 rows, the trigger executes 10 times.)
  • Triggering action - PL/SQL code enclosed between the BEGIN and END keywords. Each statement inside the PL/SQL code must end with a semicolon “;”.
• Benefits of Triggers
  
  • Triggers can be written for the following purposes:
    
    • Generating some derived column values automatically
      
    • Enforcing referential integrity
    • Event logging and storing information on table access
    • Auditing
    • Synchronous replication of tables
    • Imposing security authorizations
    • Preventing invalid transactions
      

NAME OF FUNCTION	FUNCTION	SYNTAX	EXAMPLE
Creating Triggers	Creating Triggers	CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT [OR] |  UPDATE [OR] | DELETE} [OF col_name] ON table_name [FOR EACH ROW] DECLARE [variable_namedata type[:=initial_value]] BEGIN  PL/SQL instructions; ...... END;	CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER AFTER INSERT OR UPDATE OF P_QOH ON PRODUCT BEGIN       UPDATE PRODUCT             SET P_REORDER = 1                   WHERE P_QOH <= P_MIN END; /
Triggering a Trigger	Triggering a Trigger	[perform some DML operations]	INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Kriti', 22, 'HP', 7500.00 );
Deletes a trigger	...  without deleting the table	DROP TRIGGER trigger_name	Trigger action based on DML predicates - Actions depend on the type of DML statement that fires the trigger

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF } {INSERT [OR] | UPDATE [OR] | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW]
WHEN (condition) DECLARE Declaration-statements
BEGIN Executable-statements EXCEPTION Exception-handling-statements
END;

• [REFERENCING OLD AS o NEW AS n]:
  • This allows you to refer new and old values for various DML statements, like INSERT, UPDATE, and DELETE.
• [FOR EACH ROW]: 
  • This specifies a row level trigger, i.e., the trigger would be executed for each row being affected.
  • Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.
• WHEN (condition): 
  • This provides a condition for rows for which the trigger would fire. This clause is valid only for row level triggers.

Example: creates a row level trigger for the customers table that would fire for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table. This trigger will display the salary difference between the old values and new values:

CREATE OR REPLACE TRIGGER display_salary_changes BEFORE DELETE OR INSERT OR UPDATE ON customers FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
         sal_diff number;
BEGIN
         sal_diff := :NEW.salary - :OLD.salary;
         dbms_output.put_line('Old salary: ' || :OLD.salary);
         dbms_output.put_line('New salary: ' || :NEW.salary);
         dbms_output.put_line('Salary difference: ' || sal_diff);
END;

Above trigger has been written in such a way that it will fire before any DELETE or INSERT or UPDATE operation on the table, but you can write your trigger on a single or multiple operations, for example BEFORE DELETE, which will fire whenever a record will be deleted using DELETE operation on the table.

NOTE:

1. OLD and NEW references are not available for table level triggers, rather you can use them for record level triggers.
2. If you want to query the table in the same trigger, then you should use the AFTER keyword, because triggers can query the table or change it again only after the initial changes are applied and the table is back in a consistent state.

<span style="font-family: Arial, Helvetica, sans-serif; background-color: rgb(255, 255, 255);">When a record is created in CUSTOMERS table, above created trigger display_salary_changes will be fired and it will display the following result:</span>

       Old salary:
       New salary: 7500

       Salary difference:
Because this is a new record so old salary is not available and above result is coming as null.

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 <em>procedure_name</em> [(<em>argument</em> [IN/OUT] <em>data-type</em>,