Advanced SQL

The below post is notes prepared by me by studying the book "Database Systems Design, Implementation and Management" by Peter Rob and Carlos Coronel

Content, examples and diagrams are taken from that book.

Unit –III Chapter –II ADVANCED SQL

SQL data manipulation commands operate over entire table (ex: SELECT command lists all rows from the table you specified in FROM clause) and are said to be set oriented  commands.

UNION statement:combines rows from two or more queries without including duplicate rows.

Syntax:  query UNION query

Query: SELECT cname,city FROM customer UNION  SELECT cname,city FROM customer3

Combines ouput of two or more  SELECT queries. (The select statements must be union – compatible.that is they must return the same attribute names and similar data types) without including duplicate rows.

CUSTOMER3
cno	cname	city	baldue
401	JAY	GNT	200
402	RAJ	VJA	300
CUSTOMER
cname	city
ANU	VJA
ASHA	GNT
RAJ	VJA
JAY	GNT

CUSTOMER
cno	cname	city	baldue
201	ANU	VJA	100
202	ASHA	GNT	500
203	RAJ	VJA

          

  

CUSTOMER
cname	city
ANU	VJA
ASHA	GNT
RAJ	VJA
JAY	GNT
RAJ	VJA

UNION ALL Combines ouput of two or more  SELECT queries. (The select statements must be union – compatible.that is they must return the same attribute names and similar data types) and retains duplicate rows

SELECT cname,city FROM customer UNION  ALL SELECT cname,city FROM customer3

CUSTOMER
cname	city
RAJ	VJA

INTERSECT statement: used to combine rows from two queries , returning only the rows that appear in both sets.

SELECT cname,city FROM customer INTERSECT SELECT cname,city FROM customer3

CUSTOMER
cno	cname	city
201	ANU	VJA
202	ASHA	GNT

MINUS statement: combines rows from two queries and returns only rows that appear in first set but not in the second.

SELECT cname,city FROM customer MINUS  SELECT cname,city FROM customer3

SQL JOIN OPERATORS:

A join is used to combine rows from multiple tables and returns the rows with one of the following conditions:

Join operations can be classified as inner joins and outer joins.

The inner join is traditional join in which only rows that meet a given criteria are selected.

The join criteria can be an equality condition (also called a natural join or an equijoin) or inequality condition( also called theta join)

Generally a join condition will be equality comparison of the P.K of one table and F.K of related table

An outer join returns not only matching rows but also unmatched attribute values from one table or both tables to be joined.

Join specification	Join Type	SQL Syntax Example	Description
CROSS	CROSS JOIN	SELECT *  FROM T1,T2 SELECT *   FROM T1 CROSS JOIN T2;	Returns the Cartesian product of T1 and T2(old style) Returns the Cartesian product of T1 and T2(old style)
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.
	NATURAL JOIN	SELECT * FROM T1 NATURAL JOIN T2;	Returns only the rows with matching values in the matching columns.The matching columns must have the same names and similar datatypes.
	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 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 include all rows from 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 include all rows from 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 include all rows from both table(T1 and T2) with unmatched values

RECURSIVE JOIN (OR) SELF JOIN:

An alias is an alternative name given to a column or table in any SQL statement.

An alias is especially useful when a table must be joined to itself in a recursive query

Ex:

SELECT E.Eno,E.Ename,M.Ename

FROM EMP E,EMP M

WHERE E.Mgr=E.Eno;

Cross Join:(also known as cartesian product) Examples:

SELECT * FROM invoice  CROSS JOIN line;

The above query generates 4*7=28rows  ( 4 rows in invoice table and 7 rows in line table)

Natural Join:

SELECT cno,cname,invno,invdate FROM customer NATURAL JOIN invoice;

You are not limited to two tables when performing a natural join.

It doesnot require a table qualifier for the common attribute.

SELECT  invno,pno,pdesc,line_units,line_price

FROM invoice NATURAL JOIN line NATURAL JOIN product;

JOIN USING clause

It doesnot require a table qualifier for the common attribute.

SELECT  invno,pno,pdesc,line_units,line_price

FROM invoice  JOIN line USING(invno)  JOIN product USING(pno);

JOIN ON clause

Do not require common attribute names in the joining tables.

Requires a table qualifier for the common attribute.

Lets you perform a join even when the tables do not  share a common attribute name.

SELECT  invoice.invno,pno,pdesc,line_units,line_price

FROM invoice  JOIN line ON invoice.invno=line.invno  

JOIN product ON line.pno=product.pno;

OUTER JOINS

SELECT pno,vendor.vno,vname FROM  vendor LEFT JOIN product ON vendor.vno=product.pno;

SELECT pno,vendor.vno,vname FROM  vendor RIGHT JOIN product ON vendor.vno=product.pno;

SELECT pno,vendor.vno,vname FROM  vendor FULL JOIN product ON vendor.vno=product.pno;

SUBQUERIES: used when it is required to process data based on other processed data

Characteristics of sub queries:

A subquery or nested query or inner query is a query inside another query.

A subquery is normally expressed inside parentheses

The output of inner query is used as input for the outer(high-level) query.

So inner query is executed first and then the outer query.

Subquery is based on the use of the SELECT statement to return one or more values to another query. If the table into which you are inserting rows has one date attribute and one number attribute, the SELECT subquery should return rows in which 1st column has date values and 2nd column has number values.

Inserting table rows with a select subquery or Copying parts of tables:

It add multiple rows to a table, using another table as source of the data.

CREATE TABLE PART(

part_no char(3) PRIMARY KEY,

part_desc varchar2(35),

vno number(3));

Syntax:

INSERT INTO target_tablename SELECT source_columnlist FROM source_tablename;

Example:   INSERT INTO part SELECT * FROM product;

Both the tables(PART and PRODUCT) must have same attributes.The above query returns all rows from table PRODUCT.

SELECT subquery Examples	Explanation
UPDATE product SET price=(SELECT AVG(price) FROM product ) WHERE vno=’100’………………Ex(2)	Updates the product price to average product price for the products provided by vendor 100.
DELETE FROM product WHERE vno  IN(SELECT vno FROM vendor WHERE vcity=’VJA’)……………..Ex(3)	Delete the PRODUCT table rows that are provided by vendors with vcity=’VJA’

A subquery can return

1. One value as in Ex(2) ( the select subquery returns avg(price) which is one value).
2. A list of values as in Ex(3) (the select subquery returns a list of vendors from ‘VJA’)
3. A virtual table
4. No value at all i.e, NULL . the output of the outer query might result in an error or a null empty set.

WHERE subqueries

Ans:Ex: Find all products with a price greater than or equal to the average product price, you write the following query.

SELECT pno,price FROM product

WHERE price>=(SELECT AVG(price) FROM product);

Note that this type of query,when used in a >,<,==,>= or <= conditional expression, requires a subquery that returns only one single value.If the query returns more than a single value, the DBMS will generate an error.

IN subqueries: Ans:Ex(2)

HAVING subqueries

Example:To list all products with the total quantity sold greater than the average quantity sold

SELECT pno,SUM(line_units) FROM line

GROUP BY pno  HAVING SUM(line_units)>(SELECT AVG(line_units) FROM line);

MULTIROW subquery operators: ANY and ALL

1. ALL:used  to do an inequality comparison(> or <) of one value to a list of values.

Example: What products  have a product cost that is greater than all individual product costs for products provided by vendor with vno 101

SELECT pno, qoh*price FROM product

WHERE qoh*price> ALL(SELECT  qoh*price FROM product WHERE vno = 101);

In the above query the ALL operator allows you to compare a single value(qoh*price) with a list of values returned by the subquery.

2. ANY: ANY operator allows you to compare a single value with a list of values, selecting only the

rows whose qoh*price is greater than any value of the list.

FROM subqueries

FROM clause specifies the table from which data will be drawn.

Example:To find all customers who purchased both products  ‘PEN’ and ‘PENCIL’

SELECT DISTINCT cno, cname FROM customer,

( SELECT  invoice.cno FROM  invoice NATURAL JOIN line WHERE pdesc=’PEN’) cp1,

(SELECT  invoice.cno FROM  invoice NATURAL JOIN line WHERE pdesc=’PENCIL’) cp2

WHERE  customer.cno = cp1.cno AND cp1.cno=cp2.cno;

(OR)

CREATE VIEW cp1 AS

SELECT  invoice.cno FROM  invoice NATURAL JOIN line WHERE pdesc=’PEN’;

CREATE VIEW cp2 AS

SELECT  invoice.cno FROM  invoice NATURAL JOIN line WHERE pdesc=’PENCIL’;

SELECT DISTINCT cno, cname FROM customer NATURAL JOIN cp1 NATURAL JOIN cp2;

Attribute List Subqueries or inline subquery.

The attribute list can also include a subquery expression also known as inline subquery.

The inline subquery must return one single value otherwise an error code is raised.

SELECT pno, price ,(SELECT AVG(price) FROM product) AS AVGPRICE ,

price - (SELECT AVG(price) FROM product) AS DIFF

FROM product;

The query used the full expression instead of column aliases when computing DIFF.

The column aliases cannot be used in computations in the attribute list when the alias is defined in the same attribute list.

We can use Attribute List Subqueries to include data from other tables that are not directly related to main table or tables in the query.

SELECT pno,SUM(line_units*line_price) AS sales,

(SELECT COUNT(*) FROM employee) AS ecount,

SUM(line_units*line_price)/ (SELECT COUNT(*) FROM employee) AS contib

FROM line

GROUP BY pno;

CORRELATED SUBQUERIES

To produce correlated subquery the DBMS does

1. It iniates 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.( inner query references a column of the outer subquery)

Example:To find all product sales who units sold > average units sold for that product.

SELECT  invno, pno, line_units FROM line LS

WHERE LS.line_units>(SELECT AVG(line_units) FROM line LA WHERE LA.pno = LS.pno);

The inner query runs once using the first pno found in the outer line table and returns average sale for that product.

CORRELATED subqueries can also be used with EXISTS special operator

Example: To know the vendor code and name for the products having qoh<10

SELECT vno, vname FROM vendor

WHERE EXISTS(SELECT * FROM product WHERE qoh<10 AND vendor.vno=product.vno);

SQL functions: Used to generate information from data.

DUAL: is Oracle pseudo table used only for cases when a table is not really needed.

DATE/TIME FUNCTIONS

Function	Examples
LAST_DAY: returns the last day of the month based on a date value. Syntax: last_day( date_value )	SELECT  last_day(to_date('2003/03/15', 'yyyy/mm/dd')) FROM DUAL;  would return Mar 31, 2003 SELECT  last_day(to_date('2003/02/03', 'yyyy/mm/dd')) FROM DUAL; would return Feb 28, 2003 List employees born in the last seven days of a month SELECT * FROM emp WHERE dob >= LAST_DAY(dob)-7;
TO_CHAR function : converts a number or date to a string. Syntax: TO_CHAR(date_value,fmt) fmt = format used can be MONTH Name of month MON:three-letter month name MM-two digit month name D Day of week (1-7). DAY Name of day. DD Day of month (1-31). YYYY 4-digit year YY: two digit year value	SELECT  to_char(sysdate, 'yyyy/mm/dd') FROM DUAL; would return '2003/07/09' List all employees born in 1994. SELECT * FROM emp WHERE TO_CHAR(dob,’YYYY’)=’1994’; List all employees born in the month of NOVEMBER SELECT * FROM emp WHERE TO_CHAR(dob,’MM’)=’11’; List all employees born on 14th of a month SELECT * FROM emp WHERE TO_CHAR(dob,’DD’)=’14’;
TO_DATE function:converts a string to a date. Also used to translate a date between formats. Syntax: TO_DATE(char_value,fmt) fmt = format used can be as above	SELECT to_date('2003/07/09', 'yyyy/mm/dd') FROM DUAL; would return a date value of July 9, 2003. Find the age of employess as on 12-31-2012 SELECT  e_lname,TO_DATE(’12/31/2012’,’MM/DD/YYYY’)-dob/365 AS YEARS FROM emp; NOTE: ‘12/31/2012’ is a text string, not a date, TO_DATE translates the text string to a valid oracle date used in date arithmetic. How many days are between 6/25/2011 and 10/27/2011 SELECT TO_DATE(’2011/06/25’,’YYYY/MM/DD’)- TO_DATE(‘OCTOBER 27,2011’,’MONTH ,DD,YYYY’) FROM DUAL;
SYSDATE : returns todays date	SELECT TO_DATE(’25-DEC-2011’,’DD-MON-YYYY’)-SYSDATE FROM dual;
ADD_MONTHS: adds months to a date. Syntax: add_months( date_value, n ) date_value is the starting date (before the n months have been added). n is the number of months to add to date1.	SELECT  add_months('01-Aug-03', 3) FROM DUAL; would return '01-Nov-03' SELECT pno,p_indate,ADD_MONTHS(p_indate,24) FROM product

NUMERIC FUNCTIONS

Aggregate functions operate over a set of values(multiple rows) while numeric functions operate over a single row.

Function	Example
ABS Returns absolute value of a number. Syntax: ABS(numeric_value)	SELECT  ABS(1.95),ABS(-1.93) FROM DUAL; Would return 1.95 1.93
ROUND function returns a number rounded to a certain number of decimal places. Syntax:ROUND(numeric_value,p) p=precision	SELECT  round(125.315) FROM DUAL;   would return 125 SELECT  ROUND(sal) as sal1, ROUND(sal) as sal2 FROM emp;
CEIL function returns the smallest integer value that is greater than or equal to a number. Syntax: ceil( number )	SELECT ceil(-32.65) FROM DUAL; would return -32. SELECT ceil(32.65) FROM DUAL; would return 33. SELECT CEIL(sal) ,FLOOR(sal) FROM emp;
FLOOR function returns the largest integer value that is equal to or less than a number. Syntax: floor( number )	SELECT floor(5.9) FROM DUAL;  would return 5 SELECT floor(-5.9) FROM DUAL; would return -6
The sqrt function returns the square root of n. Synatx: sqrt( n ) n is a positive number.	sqrt(9) would return 3
mod function returns the remainder of m divided by n	mod(15, 4) would return 3
power function returns m raised to the nth power. Syntax : power( m, n ) m is the base.  n is the exponent. If m is negative, then n must be an integer.	power(3, 2) would return 9
exp function returns e raised to the nth power, where e = 2.71828183.	exp(3) would return 20.0855369231877
trunc function returns a number truncated to a certain number of decimal places.	trunc(125.815, 0) would return 125 trunc(125.815, 1) would return 125.8
ln function returns the natural logarithm of a number.	ln(20) would return 2.99573227355399
log function returns the logarithm of n base m. Syntax: log( m, n ) m must be a positive number, except 0 or 1. n must be a positive number.	log(100, 1) would return 0

String Functions: are useful to concatenate strings of characters, printing names in upper case or knowing the length of a given attribute.

Function	Example
UPPER function converts all letters in the specified string to uppercase. Syntax: UPPER(string)	upper('Tech on'); would return 'TECH ON List all employee names in upper case. SELECT UPPER (e_initial) || ‘.’|| UPPER (e_fname) || UPPER(e_lname) FROM EMP;
LOWER function converts all letters in the specified string to lowercase. Syntax: LOWER(string)	List all employee names in lower case. SELECT LOWER (e_initial) || ‘.’|| LOWER (e_fname) || LOWER(e_lname) FROM EMP;
SUBSTR function allows you to extract a substring from a string. Syntax:substr( string, p, l ) string is the source string. p is the position for extraction. l is optional. It is the number of characters to extract.	substr('This is a test', 6, 2) would return 'is' substr('This is a test', 6) would return 'is a test' substr('This is a test', -3, 3) would return 'Net' List first 3 characters of all employee last names.. Ex:SELECT SUBSTR(e_lname,1,3) AS prefix FROM EMP;
LENGTH function returns the number of characters in  the specified string. Syntax: length( string)	length(NULL) would return NULL. length('') would return NULL. \ length('Tech on the Net') would return 15. List all employees last names and length of their last names. SELECT e_lname, LENGTH(e_lname) FROM EMP;
Concatenation The || operator allows you to concatenate data from two different character columns and returns a single column. Syntax: string1 || string2	'a' || 'b' || 'c' || 'd' would return 'abcd'. List all employee names (concatenated) SELECT e_initial || ‘.’|| e_fname || e_lname AS NAME  FROM EMP;

CONVERSION FUNCTIONS:allows you take a value of given data type and convert it to the equivalent value in another data type.

Functions	Example
TO_CHAR : returns a character string from a numeric value. Syntax: TO_CHAR(numeric_value, fmt)	SELECT eno, TO_CHAR(sal, ‘9,999.99’) AS PRICE FROM EMP;
TO_NUMBER : returns a formatted number from a character string. Syntxa:TO_NUMBER (char_value, fmt) fmt= format used can be: 9 - displays a digit 0 – displays a leading zero , - displays the comma . – displays the decimal point $ - displays the dollar sign B – leading blank S – leading sign MI – trailing minus sign	SELECT TO_NUMBER(‘-123.99’,’S9999.99’), TO_NUMBER(’99.78-’,’B999.99MI’), FROM DUAL;
DECODE: compares an attribute or expression with a series of values and returns an associated value or a default value if no match is found Syntax: DECODE(e,x,y,d) e – attribute or expression x – value with which to compare e y – value to return in e = x d – default value to return if e is not equal to x	The following example returns the sales tax for specified cities. Compares vcity to ‘VJA’ ;if the value matches it returns .08 Compares vcity to ‘GNT’ ;if the value matches it returns .05 If there is no match it returns 0.00( the default value) SELECT vno, vcity, DECODE(vcity,’VJA’,.08,’GNT’,.05,0.00) AS TAX FROM VENDOR;

Oracle sequences: generates a numeric value that can be assigned to any column in any table.

Use of sequences is optional, you can enter the values manually.

Oracle sequences have a name and can be used any where a value is expected.

Sequences can be created and deleted anytime.

The table attribute to which you assigned a value based on a sequence can be edited and modified.

Oracle sequences are

• Independent objects in the database.
• Not a data type
• Not tied to a table or column

Syntax:

CREATE SEQUENCE name [START WITH n] [INCREMENT BY n] [CACHE | NOCACHE]

where name is the name of the sequence

n is an integer that can be positive or negative.

START WITH  specifies initial sequence value( the default value is 1)

INCREMENT BY determines the value by which the sequence is incremented.

The CACHE or NOCACHE indicates whether oracle will preallocate sequence numbers in memory. (Oracle preallocates 20 values by default)

Example: CREATE SEQUENCE CSEQ1 START WITH 204 INCREMENT BY 1 NOCACHE;

To check all the sequences you have created.

SELECT * FROM USER_SEQUENCES;

To use sequences during data entry

you must use two special pseudo columns NEXTVAL and CURRVAL.

NEXTVAL retrieves  the next available value from a sequence. Each time you use NEXTVAL , the sequence is incremented.

CURRVAL retrieves  the current value of sequence.

Example

INSERT INTO CUSTOMER VALUES (CSEQ1.NEXTVAL,’RAVI’,’NELLORE’, 500);

INSERT INTO INVOICE VALUES (‘I05’ , CSEQ1.CURRVAL,’22-AUG-2011’);

You cannot use CURRVAL unless a NEXTVAL was issued previously in the same session.

NEXTVAL retrieves  the next available sequence number( here 204) and signs to cno in CUSTOMER table.

CSEQ1.CURRVAL refers to last used CSEQ1.NEXTVAL sequence number(204).

In this way the relationship between INVOICE and CUSTOMER is established.

COMMIT; statement must be issued to make the changes permanent.

You can also issue a ROLLBACk statement , in which case the rows you inserted in INVOICE and CUSTOMER will be rolled back.( but sequence number would not) That is, if you use sequence number again you must get 204 but you will get 205 eventhough the row 204 is deleted.

DROPPING a SEQUENCE doesnot delete the values you assigned to table attributes.

Syntax: DROP SEQUENCE CSEQ1;

VIEWS

A view is a virtual table based on SELECT query.

The tables on which view is based are called base tables.

Syntax:

CREATE VIEW viewname AS SELECT query

Characteristics:

A relational view has several special characteristics

• We can use the view instead of table in a SQL statement.
• Views are dynamically updated when the base table is updated.
• Views provide a level of security in the database. The view can restrict users to only specified columns and specified rows in a table.
• View may also be used as the basis for reports

Example: CREATE VIEW PROD_STATS AS SELECT vno, SUM(qoh * price) AS TotalCost

                                                                            FROM PRODUCT  GROUP BY vno;

To drop a view

Syntax: DROP VIEW <view name>

Example:

DROP VIEW PROD_STATS

UPDATABLE VIEWS:To use batch update routines to update master table attribute with transaction data.

To demonstrate a batch update routine, consider two tables

ProdMaster
pno	pdesc	qoh
P01	SCREWS	60
P02	NUTS	37
P03	BOLTS	50
ProdSales
pno	qty
P01	7
P02	3

      

NOTE:There is 1:1 relationship between two tables

To Update qoh attribute (qoh – qty as that much quantity has been sold)

1. We have to join two tables
2. update qoh for each row of  ProdMaster table with matching pno values in ProdSales table.

We use a updatable view to do that.

Updatable view is a view that can be used to update attributes in the base tables that are used in the view.

Not all views are updatable.

The most common updatable view restrictions are as follows:

1. GROUP BY and aggregate functions cannot be used.
2. Cannot use SET operators.
3. The P.K columns of base table you want to update must have unique values in the view. That is, the two tables must have 1:1 relationship then only the view can be used to update a base table.

Example: CREATE VIEW QUP AS ( SELECT ProdMaster.pno, qoh, qty FROM ProdMaster, ProdSales

                                                             WHERE ProdMaster.pno=ProdSales.pno);

UPDATE QUP SET qoh=qoh-qty;

Q: What is PSM (Persistent Stored Module)?

Ans: A Persistent Stored Module is a block of code containing standard SQL statements and procedural extensions that is stored and executed at the DBMS server. The PSM represents business logic that can be encapsulated, stored and shared among multiple database users. A PSM lets an administrator assign specific access rights to a stored module to ensure that only authorized users can use it. Oracle implements PSMs through its procedural SQL language.(PL/SQL)

Q: What is PL/SQL? Explain?

Ans: PL/SQL is a language that makes it possible to use and store procedural code and SQL statements within the database.

It is also used to merge SQL and traditional programming constructs, such as

• Variables,
• conditional processing (IF-THEN-ELSE),
• basic loops (FOR and WHILE loops) and
• Error trapping.

The procedural code is executed as a unit by the DBMS when it is invoked by the end user.

End users can use PL/SQL  to create

• Anonymous PL/SQL blocks.
• Triggers
• Stored Procedures
• PL/SQL functions

You can write PL/SQL code block by enclosing the commands inside BEGIN and END clause.

Ex:

BEGIN

INSERT INTO vendor VALUES (105, ‘SITA’, ‘TNL’);

END;

/

This is an example of anonymous PL/SQL block because it has not given a specific name.

The above PL/SQL block executes as soon as you press ENTER key after typing /

You will see the message “PL/SQL procedure successfully completed”

If you want a more specific message such as “new vendor added”. You must type as follows:

SQL> SET SERVEROUTPUT ON

This SQL * plus command enables the client console (SQL * plus) to receive messages from the server side(ORACLE DBMS).To send messages from the PL/SQL block to SQL * plus console, use the DBMS_OUTPUT.PUT_LINE function.

The standard SQL , the PL/SQL code are executed at server side, not at client side.To stop receiving messages from sever , enter SET SERVEROUTPUT OFF.

In oracle , you can use the SQL * plus command SHOW ERRORS to help you diagnose errors found in PL/SQL blocks.

Q: Write anonymous PL/SQL program to insert rows into VENDOR table and display the message “New vendor added”.

Ans:

BEGIN

INSERT INTO vendor VALUES (106,’GITA’,’VJA’);

DBMS_OUTPUT.PUT_LINE(‘New vendor added’);

END;

/

PL/SQL Basic data types

Data Type	Description
CHAR	character values of a fixed length
VARCHAR2	variable length character values
NUMBER	numeric values
DATE	Date values
%TYPE	inherits the datatype from a variable that you declared previously or from an attribute of a database table. Ex: price1  PRODUCT.price %TYPE ; assigns price1 the same datatype as the price column in the PRODUCT table.

Q: Write anonymous PL/SQL program to display the number of products in price range 0 and 10, 11 and 60 ,61 and 110 etc..

Ans:

DECLARE

P1 NUMBER(3) := 0;

P2 NUMBER(3) := 10;

NUM NUMBER(2) := 0;

BEGIN

WHILE P2<5000 LOOP

SELECT COUNT(pno) INTO NUM FROM product WHERE price BETWEEN P1 AND P2;

DBMS_OUTPUT.PUT_LINE(‘There are ‘|| NUM|| ‘ products with price between ‘||P1|| ‘ and ‘||P2);

P1 := P2+1;

P2 := P2+50;

END LOOP;

END;

/

The PL/SQL block shown above has following characteristics.

1. Each statement inside the PL/SQL code must end with a semicolon
2. The PL/SQL block starts with the DECLARE section in which you declare the variable names, the data types and an initial value(optional).
3. A WHILE loop is used.
4. Uses the string concatenation symbol.
5. SELECT statement uses the INTO keyword to assign output of the query to a PL/SQL variable

The most useful feature of PL/SQL block is that they let you create code that can be named, stored and executed either implicitly or explicitly by the DBMS.

What is Trigger ? Explain.

Ans: A trigger is a procedural sql code which is fired when a DML statements like Insert, Delete, Update is executed on a database table.

The syntax to create a trigger in oracle is:

CREATE OR REPLACE TRIGGER trigger_name       

[BEFORE / AFTER] [DELETE /INSERT/UPDATE OF column_name ] ON table_name

[FOR EACH ROW]

[DECLARE]

[variable_name data-type [:= initial_value]]

BEGIN

PL/SQL instructions;

……

END;

A trigger definition contains the following parts:

1. The triggering timing: BEFORE or AFTER. This timing indicates at what time the trigger should get fired. (before or after the triggering statement is completed.)
2. The triggering statement/event: The statement that causes the trigger to execute

(INSERT, UPDATE or DELETE)

The triggering level: There are two types of triggers: statement – level triggers and row – level triggers

• Statement – level triggers: This type of trigger is executed once, before or after the triggering statement is completed.
• Row – level triggers: requires the use of the FOR EACH ROW keywords. This type of trigger is executed once for each row affected. ( if you update 10 rows, the trigger executes 10 times.
• Triggering Action: The PL/SQL code enclosed between BEGIN and END keywords.

You can use a trigger to update an attribute in a table other than the one being modified.

CREATE OR REPLACE TRIGGER TLP

AFTER INSERT ON line

FOR EACH ROW

BEGIN

UPDATE product

SET qoh = qoh - :NEW.LINE_UNITS

WHERE product.pno = :NEW.pno;

END;

/

TLP is a row level trigger that executes after inserting a new  LINE row and reduces quantity on hand (in PRODUCT table) of  recently sold product by the number of units sold.

CREATE OR REPLACE TRIGGER trigger_name  =>creates a trigger with the given name or overwrites an existing trigger with the same name.

OF column_name =>This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated

ON table_name=> the name of the table or view to which the trigger is associated.

Example of a statement level trigger that is executed after an update of the qoh, pmin  attribute for an existing row or after an insert of a new row in the product table.

CREATE or REPLACE TRIGGER TPR

AFTER INSERT OR UPDATE OF QOH,PMIN  ON PRODUCT

BEGIN

     UPDATE PRODUCT

SET REORDER =1

WHERE QOH <= PMIN;

END;

/

Q: When does a trigger fire?

Ans: A trigger is triggered automatically when an associated DML statement is executed.

• A trigger is invoked before or after a data row is inserted, updated or deleted.
• A trigger is associated with a database table.
• Each database table may have one or more triggers.
• A trigger is executed as part of the transaction that triggered it.

Q: How to delete a trigger?

Ans: When you delete a table, all its trigger objects are deleted with it.

If you want to delete a trigger without deleting the table, give the following command

DROP TRIGGER triggername.

Q: Write a program to update the customer balance in the CUSTOMER table after inserting every new LINE row.

CREATE OR REPLACE TRIGGER TLC

AFTER INSERT ON line

FOR EACH ROW

DECLARE

cus CHAR(5);

tot NUMBER := 0; --to compute total cost

BEGIN                

SELECT cno  INTO cus   FROM invoice     --1) get the customer code

WHERE invoice.invno = :NEW.line_units;

           tot := :NEW.line_price * :NEW.invno;     --2)compute the total of the current line

                                                                    

UPDATE customer  SET baldue = baldue + tot  WHERE cno = cus;                         

DBMS_OUTPUT.PUT_LINE(‘ *** Balance updated for customer : ‘ || cus);

END;

/

The trigger is a row level trigger that executes for each new LINE row inserted.

The SELECT statement returns only one attribute (cno) from INVOICE  table and that attribute returns only one value.

You use the INTO clause to assign a value from a SELECT statement to a variable (cus) used within a trigger.

Double dashes “--“ are used to indicate comments within the PL/SQL block.

Trigger action based on conditional DML predicates

You can create a trigger that executes after an insert, an update or a delete on the PRODUCT table and to know which one of the three statements caused the trigger to execute use the following syntax:

IF INSERTING THEN ……END IF;

IF UPDATING THEN ……END IF;

IF DELETING THEN……END IF;

Triggers can be used to

• To enforce constraints that cannot be enforced at the DBMS design and implementation levels.
• To facilitate enforcement of  referential integrity.
• Update table values, insert records in tables and call other stored procedures.
• Triggers add functionality by automating critical actions and providing appropriate warnings and suggestions.
• Triggers add processing power to RDBMS and to database system as a whole.

Oracle recommends triggers for

• Auditing purposes (creating audit logs)
• Automating generation of derived column values.
• Enforcement of business or security constraints.
• Creation of replica tables for back up purposes.

Q:What are the various type of triggers?

Statement – level triggers: This type of trigger is executed once, before or after the triggering statement is completed.

Example of a statement level trigger that is executed after an update of the qoh, pmin  attribute for an existing row or after an insert of a new row in the product table.

CREATE or REPLACE TRIGGER TPR

AFTER INSERT OR UPDATE OF QOH,PMIN  ON PRODUCT

BEGIN

     UPDATE PRODUCT

SET REORDER =1

WHERE QOH <= PMIN;

END;

/

Row – level triggers: requires the use of the FOR EACH ROW keywords. This type of trigger is executed once for each row affected. ( if you update 10 rows, the trigger executes 10 times.

Example: CREATE or REPLACE TRIGGER TPR

BEFORE INSERT OR UPDATE OF QOH, PMIN ON PRODUCT

FOR EACH ROW

BEGIN

IF :NEW.QOH <= :NEW.PMIN  THEN  

           :NEW.REORDER :=1;

ELSE

           :NEW.REORDER :=0;

END IF;

END;

/

What are Stored Procedures? Explain?

A stored procedure is a named group of SQL statements that have been previously created and stored in the server database.

Advantages:

• Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data.

• Stored procedures reduce network traffic and improve performance.
• Stored procedures can be used to help ensure the integrity of the database.
• Stored procedures help reduce code duplication by means of code isolation and code sharing, there by minimizing the chance of errors and the cost of application development and maintenance.
• Stored procedures are useful to encapsulate shared code to represent business transactions i.e, you need not know the name of newly added attribute and would need to add new parameter to the procedure call.

Syntax to create procedure:

CREATE OR REPLACE PROCEDURE procedure_name [(argument [in/out] data-type,….)]

                       [IS / AS] [variable_name data-type [:= initial_value]]

BEGIN

PL/SQL or SQL statements;

…

END;

Syntax to execute a stored procedure

EXEC procedure_name[(parameter_list)];

Ex: Write a stored procedure to assign an additional 5 % discount for all products when the QOH = 2PMIN

CREATE OR REPLACE PROCEDURE  prod_discount  AS

BEGIN

UPDATE product

SET discount = discount + .05

WHERE qoh >= pmin * 2;

DBMS_OUTPUT.PUT_LINE(‘*** Update Finished ***’);

END;

/

1. argument specifies the parameters that are passed to the stored procedures. A stored procedure could have zero or more arguments.

2. IN/OUT indicates whether the parameter is for input, output or both.

3. Variables can be declared between the keywords IS and BEGIN.

To make percentage increase an input variable in the above procedure---

CREATE OR REPLACE PROCEDURE  prod_discount ( pd  IN NUMBER)

AS BEGIN

IF ((pd <= 0) OR (pd >= 1)) THEN

DBMS_OUTPUT.PUT_LINE(‘Error value must be greater than 0 and less than 1’);

ELSE

     UPDATE product

        SET discount = discount + .05

                   WHERE qoh >= pmin * 2;

     DBMS_OUTPUT.PUT_LINE(‘*** Update Finished ***’);

END IF;

END;

/

To execute the above procedure---

EXEC prod_discount(.05);

Q: write a stored procedure to add new customer.

CREATE OR REPLACE PROCEDURE cadd (w_cname IN VARCHAR2, w_city IN VARCHAR2)

AS

BEGIN

INSERT INTO customer (cno, cname, city) values(CSEQ1.NEXTVAL, w_cname, w_city);

DBMS_OUTPUT.PUT_LINE(‘Customer added ’);

END;

/

The procedure uses

2063597984. several parameters one for each required attribute in the CUSTOMER table.
2063597985. CSEQ1 sequence to generate a new customer code.

The parameters can be null only when the table specifications permit null for that parameter.

To execute:

EXEC cadd(‘KALA’, ‘VJA’,NULL);

Q: Write procedures to add new invoice and line row.

Ans:

CREATE OR REPLACE PROCEDURE invadd(w_cno IN NUMBER, w_date IN DATE)

AS BEGIN

    INSERT INTO invoice

    VALUES(ISEQ.NEXTVAL, w_cno, w_date);

DBMS_OUTPUT.PUT_LINE(‘Invoice Added’);

END;

/

CREATE OR REPLACE PROCEDURE lineadd (ln IN CHAR, pn IN CHAR, lu IN NUMBER)

AS

lp NUMBER := 0;

BEGIN

SELECT price INTO lp

FROM product

WHERE pno = pn ;

INSERT INTO line VALUES(ISEQ.    CURRVAL, ln, pn, lu, lp);

DBMS_OUTPUT.PUT_LINE(‘Invoice Line  Added’);

END;

/

Q: What is a cursor? How many types of cursors are there? How to handle cursors?

Ans:Cursor is reserved area in memory in which output of the query is stored,

like an array holding rows and columns.

There are two types of cursors: implicit and explicit.

An implicit cursor is automatically created in PL/SQL when the SQL statement returns only one value.

An explicit cursor is created to hold the output of an SQL statement that may return two or more rows.(but could return 0 or only one row)

To create an explicit cursor, use the following syntax inside PL/SQL DECLARE section.

CURSOR cursor_name IS select-query;

The cursor declaration section only reserves a named memory area for the cursor.

Once you declared a cursor, you can use cursor processing commands anywhere between the BEGIN and END keywords of the PL/SQL block.

Cursor Processing Commands

Cursor Command	Explanation
OPEN	Executes the SQL command and populates the cursor with data Before you can use a cursor, you need to open it Ex: OPEN cursor_name.
FETCH	To retrieve data from the cursor and copy it to the PL/SQL variables. The syntax is : FETCH cursor_name INTO variable1 [,variable2,…..]
CLOSE	The CLOSE command closes the cursor for processing

Cursor style processing involves retrieving data from the cursor one row at a time.

The set of rows the cursor holds is called the active set.

The data set contains a current row pointer.

Therefore after opening a cursor, the current row is the first row of the cursor.

When you fetch a row from the cursor, the data from the current row in the cursor is copied to the pl/sql variables. After the fetch, the current row pointer moves to the next row in the set and continues until it reaches the end of the cursor.

Cursor Attributes determine when you reached the end of the cursor data set, number of rows in cursor etc…

Attribute	Description
%ROWCOUNT	Returns the number of rows fetched so far. If the cursor is not OPEN, it returns an ERROR. If no fetch has been done but the cursor is OPEN, it returns 0.
%FOUND	Returns TRUE if the last FETCH returned a row and FALSE if not. If the cursor is not OPEN, it returns an ERROR. If no fetch has been done, it contains NULL.
%NOTFOUND	Returns TRUE if the last FETCH did not return any row and FALSE if it did. If the cursor is not OPEN, it returns an ERROR. If no fetch has been done, it contains NULL.
%ISOPEN	Returns TRUE if the cursor is OPEN or FALSE if the cursor is CLOSED.

CREATE OR REPLACE PROCEDURE pce IS

p product.pno%TYPE;

desc  product.pdesc%TYPE;

tot NUMBER(3);

CURSOR pc IS

SELECT pno, pdesc  FROM product

WHERE qoh > (SELECT AVG(qoh) FROM product);

BEGIN

DBMS_OUTPUT.PUT_LINE(‘PRODUCTS WITH QOH > AVG(QOH)’);

OPEN pc;

LOOP

FETCH pc INTO p,desc;

EXIT WHEN pc%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(p||’  =>   ‘||desc);

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘TOTAL PRODUCT PROCESSED ‘||pc%ROWCOUNT);

CLOSE pc;

END;

/