Course Web Pages - Spring 2012 - LIBR 242-01/10 Greensheet - Sample Codes - SQL Querying with View

Creating/Using/Deleting Views of Tables in Database

First, we retrieve records, generate summary info, and store in a temporary table (view). Note how summary info such as counts and totals is captured into a FIELD in the view.


CREATE VIEW TOTALBYC AS (
	SELECT CUS_CODE, COUNT(DISTINCT LINE.INV_NUM) NUM_INV,
		SUM(LINE.LINE_UNITS * LINE.LINE_PRICE) TOTSLS
			FROM INVOICE, LINE
				WHERE INVOICE.INV_NUM = LINE.INV_NUM
					GROUP BY CUS_CODE
);

Now, we can query the view just like a regular table.


SELECT SUM(NUM_INV), SUM(TOTSLS), MIN(TOTSLS), MAX(TOTSLS), AVG(TOTSLS)
FROM TOTALBYC;

If the view is no longer needed, it may be deleted (or rather, DROPped)


DROP VIEW TOTALBYC;

Select here to return to the sample codes page.

Programs

Courses

Creating/Using/Deleting Views of Tables in Database