Lecture 4 Structured Query Language Data Definition Language

Lecture 4 Structured Query Language Data Definition Language Data Manipulation Language CSE 3180 Semester 1 2005 week 4 / 1

Power. Point Overheads • The Overheads and other materials are accessible via http: //www. csse. monash. edu. au/courseware/cse 3180 • Files should be saved and copied to A: (filename) • Then you can browse these via Power. Point 97/2000 • or you can display them on the terminal screen (in the labs this will be Office 2000/XP software). CSE 3180 Semester 1 2005 week 4 / 2

Doctor’s Diary 1. I examined your patient today who is still under our car for physical therapy 2. Discharge status : Alive, but without my permission CSE 3180 Semester 1 2005 week 4 / 3

University Notes Thursday March 31 st is the Semester 1 census date It is also the last date to discontinue Semester 1 or fullyear units with ‘WD’ showing on your academic record It is the last day to discontinue Semester 1 or full-year units without incurring HECS or full-fees relevant for Semester 1 It is the last day to discontinue all studies by candidates enrolled coursework programs and in single units to qualify for a full refund of the student course fee relevant to Semester 1 And it is the last date to qualify for a 100% Refund of student amenities fee relevant to Semester 1. CSE 3180 Semester 1 2005 week 4 / 4

Objectives • This lecture we will be looking at some of the aspects and functions of SQL - Structured Query Language, which provides the means of accessing data in a Relational Data Base. • Microsoft Access provides users with a Graphics User Interface which in turn is translated into (non standard) SQL • When you have developed part of your assignment in Access, run a query, and then display the query in its SQL statement form. You should be easily able to recognise the table names and the functions you have set up. CSE 3180 Semester 1 2005 week 4 / 5

Objectives - cont’d The objectives of this lecture are 1. To introduce the Data Definition and Data Manipulation components of SQL (Structured Query Language) as expressed in Oracle 2. To proceed through some of the SQL commands such as Select, From, Where, Having, Group By, Order By 3. To introduce other components such as Logical Operators, Arithmetic Operators, Sub-queries and Views CSE 3180 Semester 1 2005 week 4 / 6

SQL - Data Manipulation SQL Structured Query Language Originally designed and implemented by IBM research as the interface to relational databases. ANSI Standard 1986. SQL 99 Standard is current. SQL 93 in many applications A declarative language. The DBMS performs retrieval. A choice of access routines is made to optimise the query. SQL specifies syntax features for retrieval update and definition of the database. CSE 3180 Semester 1 2005 week 4 / 7

SQL Some Variations • Microsoft Access SQL is generally ANSI-89 Level 1 compliant. • Certain ANSI SQL features are not implemented in Access SQL • MS Access SQL includes reserved words and features not supported in ANSI SQL • Some differences – Matching character MS Access ANSI SQL Single character ? _ Zero or more chars * % • MS Access does not support COMMIT, GRANT, LOCK • DISTINCT aggregate functions (e. g. SUM(DISTINCT att) CSE 3180 Semester 1 2005 week 4 / 8

Why SQL ? • SQL is the one industry standard language for querying databases • All of the database features can be accessed via SQL which doesn’t occur with a Graphics interface • GUI tools frequently do not exploit the underlying language functionality • In client-server applications, an SQL query in any application host language will return exactly the same results (ODBC) CSE 3180 Semester 1 2005 week 4 / 9

DDL - Data Definition CREATE (1) Table - define table name, attributes, types (2) View - define user view of data (3) Index - create index on nominated attributes DROP (1) Table - delete table, attributes and values (2) View - delete user view(s) (3) Index - delete index, indexes NOTE: Oracle DB 2, My. SQL, SQLServer and Ingres support the 'owner' concept. Hence only 'owners' can DROP nnnn Some DBMS have an ALTER command to vary attribute characteristics. Ingres V 6. 4 does not support this feature CSE 3180 Semester 1 2005 week 4 / 10

DML - Data Manipulation Language Group 1 - Data Modification Insert - Add a single row (interactive) - Perform successive INSERTS as a 'transaction ‘set’ - interactive Copy - From an external file to a database table - From a table to an external file Update - Amend attribute values in rows Delete - Delete rows of data from a table WHEN IN DOUBT, USE HELP. CSE 3180 Semester 1 2005 week 4 / 11

Group 2 DML - Data Manipulation Language DATA CONTROL - User control of transaction processing Commit - Commit or enable changes to the database Rollback - Rollback and reprocess (or some other action) transaction which could not be COMMITTed. Group 3 DATA SECURITY - Authority over users - generally only available to the DBA Grant - Allow access privileges to users (e. g. read, write, update to nominated tables or attribute values in tables) Revoke - Revoke or cancel access privileges CSE 3180 Semester 1 2005 week 4 / 12

CREATE The syntax is : create table <tablename> ( columnname format {, columnname format}) e. g. create table wages(name varchar 2(10), ID number(2, 0), Department varchar 2(3), date_comm date); OR create table wages(name varchar 2(10) not null, ID number(2, 0) not null, Department varchar 2(3) not null, date_comm date) not null; CSE 3180 Semester 1 2005 week 4 / 13

Optional CREATE create table highincome as select name, salary from wages where salary > 75000; This creates an ‘extract file’ of only those entries from the table ‘wages’ where the salary is in excess of $75, 000 (not all DBMS support Currency designators $A, $US …) CSE 3180 Semester 1 2005 week 4 / 14

Create Table Wages (Name, Date of Birth, ID, Salary, …… High Income Name, Salary Condition: Salary > 75000 create table highincome as select name, salary from wages where salary > 75000; CSE 3180 Semester 1 2005 week 4 / 15

INSERT This command allows data to be inserted, one row at a time, into an existing table Syntax: Insert into <tablename> (list of attributes) values (list of values) Note: The list of attribues can be ignored providing the order of attribute values, and completenes of attribute instances, is as per the table list. example: insert into emp(name, sal, byear) values(‘Jones, Bill’, 45000, 1967); or(see note) insert into emp values(‘Jones, Bill’, 45000, 1967); CSE 3180 Semester 1 2005 week 4 / 16

INSERT - an extension The Insert command can also use a feature similar to create, i. e. use data from an existing table to populate another table. insert into job(jid, jtitle, lowsal, highsal) as select job_no, title, lowsal, highsal) from newjob where title = ‘system analyst’); The attributes of the table ‘newjob’ comprise at least job_no, title, lowsal, highsal CSE 3180 Semester 1 2005 week 4 / 17

Oracle - SQLLOAD Inserting is a slow process, and the sqlload command allows for data to be bulk loaded from an existing text file (upload) to a database table. The Loader requires a Control File which • names the file from which data is to be directed into tables • names the table into which the ‘external’ data is to be loaded • sets the attribute name(s), order and datatype • provides additional files for logging, ‘bad’, and ‘discard’ data into these particular files. Also requires a Unix account (on the Monash system) CSE 3180 Semester 1 2005 week 4 / 18

Oracle Loader Similar functions - expects the Input as a text file. Some features : skip - starting point of load - default of all log - log of records bad - log of discards Control file : details of loader file, name of Oracle table to load into, column and field specifications CSE 3180 Semester 1 2005 week 4 / 19

Import and Export • These commands are available to the DBA and the application developers • The commands make quick and dependable copies of Oracle data • EXPORT makes a copy of data and data structures in a operating system file (external directory e. g. Unix) • IMPORT reads file created by Export and places data and data structures into Oracle database tables • Their uses are: – backup and recovery – moving data between instances of Oracle – moving data between tablespaces CSE 3180 Semester 1 2005 week 4 / 20

Access Import • MS-Access offers an Import function which directs data from others sources (e. g. other Access databases, Excel, and various other sources such as SQL server) to an Access database. These sources can be local or remote. • This serves the same purpose which is to use (or reuse) existing data and bulk copy to a target. • Error messages (mismatches, data missing, data type mismatch etc) are generated as a by-product of the process. • The target database table (or tables) must be compatible with the source ( or is that vice - versa ? ) CSE 3180 Semester 1 2005 week 4 / 21

Copy There a number of forms associated with ‘COPY’ 1. Copying data from 1 database to another remote to local to remote 2. Copy data from one table to another (single database) Copy data to one table from another An example: copy from rsimpson@cot 2180 (to the current database) create empcopy 2 using select * from user. dept CSE 3180 Semester 1 2005 week 4 / 22

General Form of a Query SELECT as a function applies algebra in developing a result table from a base table, (or tables). The result table may have 0 to n rows. . . The Select Command is used to query data in the database. . Its syntax is : Select (select-list - attributes or derived data) From (table name or names) Where (sets up conditions) Group By (attribute names) Having (search-conditions) Order By (attribute name or names) CSE 3180 Semester 1 2005 week 4 / 23

General Form of a Query SELECT and FROM are compulsory. Other clauses are optional but occur in the order shown HAVING is normally associated with GROUP BY CSE 3180 Semester 1 2005 week 4 / 24

Select Example (Oracle) PARTNO P 1 P 2 P 3 SELECT PNAME NUT BOLT CARAVAN PRICE 0. 20 1. 00 5000. 00 QOH 20 40 3 pname, price*qoh AS pvalue FROM PART WHERE price > 0. 20 AND price * qoh > 30 ORDER BY pname desc; pname CARAVAN BOLT pvalue 15, 000. 00 40. 00 CSE 3180 Semester 1 2005 week 4 / 27

Same Data - Oracle Datatypes Oracle does not support a ‘money’ datatype as in some DBMSs nor a ‘currency’ datatype (as in Microsoft). The Oracle datatype ‘numeric’ has many forms and some of these are shown below using the value 7456123. 89 number results in 7456123. 89 being stored number(9) results in 7456123 being stored number(9, 2) results in 7456123. 89 being stored number(9, 1) results in 7456123. 9 being stored number(15, 1) results in 7456123. 9 being stored To represent $ values in an output table : column (attribute) format $99. 999. 00 heading (new name) CSE 3180 Semester 1 2005 week 4 / 28

Format of a Query Script Select (attribute list ) From (tables list) Where (conditions for joins) Group By (selected groupings Having (condition for grouping) Order By (Attribute(s) order [ Asc or Desc] CSE 3180 Semester 1 2005 week 4 / 29

Expressions in Select Arithmetic operators are + - ** * / Comparison operators are = != <> ^= > < >= <= Logical operators are AND OR NOT Parentheses may be used to alter order of evaluation unary, **, * /, + Wildcard % = any string of zero or more character _ = any one character [ ] = any of the characters enclosed in brackets A range of numeric, string, date and other functions are available. CSE 3180 Semester 1 2005 week 4 / 30

SELECT Vocabulary ALL BY GROUP LIKE ORDER AND DISTINCT HAVING NOT SELECT Arithmetic Operators Relational Operators Logical Operators Parentheses Special Operators Existential Operator ANY EXISTS IS NULL UNION + = AND ( BETWEEN FROM IN OR WHERE != OR ) * < / > <= >= NOT BETWEEN IN EXISTS LIKE NULL CSE 3180 Semester 1 2005 week 4 / 31

Arithmetic Operators List the name, birth year and year of death of each prime minister who was born in New South Wales. List in order of birth year. SELECT PM_NAME, BIRTH_YR + DEATH_AGE FROM PRIME_MINISTER WHERE STATE_BORN = ‘NSW’ ORDER BY BIRTH_YR; PM_NAME Barton E Page E C G Chifley J Holt H E Mc. Mahon W Whitlam E G BIRTH_YR 1849 1880 1885 1908 1916 BIRTH_YR + DEATH_AGE 1920 1961 1951 1967 ? ? CSE 3180 Semester 1 2005 week 4 / 32

Logical Operators Which prime ministers were born in Victoria before the turn of the century? SELECT PM_NAME, BIRTH_YR, STATE_BORN FROM PRIME_MINISTER WHERE STATE_BORN=‘VIC’ AND BIRTH_YR < 1900; PM_NAME Deakin A Bruce S M Scullin J H Menzies R G Curtin J BIRTH_YR 1856 1883 1876 1894 1885 STATE_BORN VIC VIC VIC CSE 3180 Semester 1 2005 week 4 / 33

Combining Logical Operators Which prime ministers were born in NSW and then represented Victoria or have simply not served less than two years? SELECT FROM WHERE AND OR PM_NAME, STATE_BORN, STATE_REP, YRS_SERVED PRIME _MINISTER STATE_REP = ‘VIC’ STATE_BORN = ‘NSW’ NOT YRS_SERVED < 2; PM_NAME Holt H E Gorton J G Whitlam E G Fraser J M STATE_BORN NSW VIC STATE_REP VIC NSW VIC YRS_SERVED 1. 88 3. 17 2. 92 7. 33 CSE 3180 Semester 1 2005 week 4 / 34

Select Examples - ‘PART’ table SELECT PNAME FROM PART WHERE QOH IS NULL; Selects those rows where qoh has a null value SELECT * FROM PART WHERE PNAME LIKE '_ _T' or PNAME LIKE '%LT'; Selects rows where pname has three letters the last of which is a T or PNAME ends in LT CSE 3180 Semester 1 2005 week 4 / 35

Use of COUNT and Distinct operators How many liberal prime ministers were commissioned between 1970 and 1980? SELECT ‘Liberal PMs’, COUNT(*), COUNT(DISTINCT PM_NAME) FROM MINISTRY WHERE PARTY = ‘Liberal’ AND YR_COMM BETWEEN 1970 AND 1980; COUNT(*) Liberal PMs 5 COUNT(DISTINCT PM_NAME) 2 CSE 3180 Semester 1 2005 week 4 / 36

Union Operator Combines the contents of two identical tables create table compress as select name, identity, date_of_birth, gender from personnel 1 union select name, perscode, birthdate, m_or_f from personnel 2; Identical means that attribute types, size, domains of the Union attributes must be the same. Arttibute names do not. CSE 3180 Semester 1 2005 week 4 / 37

Union Assume that the following ‘Names’ data is contained in 2 tables Longtime Prospect Adah Talbot Dick Jones Dory Kenson Donald Rollo Elbert Talbot George Phepps George Oscar Jed Hopkins Pat Lavay Peter Lawson Ted Butcher Wilfred Lowell (8 names) CSE 3180 Semester 1 2005 week 4 / 38

Union The statement select name from longtime union select name from prospect; would give this result Adah Talbot Dick Jones Donald Rollo Dory Kenson Elbert Talbot George Oscar George Phepps Jed Hopkins Pat Lavay Peter Lawson Ted Butcher Wilfred Lowell (12 names) note that there are NO duplicates CSE 3180 Semester 1 2005 week 4 / 39

DELETE FROM tablename [corr-name] [ WHERE search-condition ] Delete one or many rows in a table. Without the search condition, all rows will be deleted DELETE FROM PART WHERE qoh < 4. 00; CSE 3180 Semester 1 2005 week 4 / 41

UPDATE tablename [corr-name] [ FROM tablename [corr-name] {, tablename [corrname]}] SET colname = expression { , colname = expression} [ WHERE search_condition ] Replaces values of the specified columns with expression values for all rows satisfying the search-condition. Expressions in the set clause may be constants or column values from the UPDATE tablename or FROM tablename UPDATE PART SET price = price * 1. 1 WHERE price < 20; CSE 3180 Semester 1 2005 week 4 / 42

SET Funtions • A SET Function is one which operates on an entire column of values, not just a single value • The SET functions supported are: Name Format(Result) count integer sum integer, float, money avg float, money max same as the argument min same as the argument Description Count of Occurrences Summation Average (sum/count) Maximum value Minimum value CSE 3180 Semester 1 2005 week 4 / 43

Use of SET functions PART Part. No P 1 P 2 P 3 Pname Price NUT 1. 00 BOLT 1. 00 CARAVAN 5000. 00 QOH 20 20 3 SELECT count(partno) AS Part_count, avg(price) AS Av_price, count(distinct price) AS Price_count FROM part; Part_count Av_Price 3 1667. 33 Price_count 2 Set functions supported = avg count max min sum Set functions may not be used directly in a search condition CSE 3180 Semester 1 2005 week 4 / 44

Use of GROUP BY List the number of prime ministers from each party. SELECT PARTY, COUNT(*) FROM MINISTRY GROUP BY PARTY; PARTY Country Free Trade Labor Liberal National Labor Nationalist Protectionist United Australia COUNT(*) 3 1 15 17 1 3 4 5 CSE 3180 Semester 1 2005 week 4 / 45

Grouping by More than One Attribute Group prime ministers by their state born and by the state they represented. Give the numbers of prime ministers and the total numbers of years served. SELECT STATE_BORN, STATE_REP, COUNT(*), SUM(YRS_SERVED) FROM PRIME_MINISTER GROUP BY STATE_BORN, STATE_REP; STATE_BORN ? ? NSW QLD TAS VIC STATE_REP NSW QLD NSW VIC QLD TAS VIC WA COUNT(*) 4 1 5 1 2 1 7 1 SUM(YRS_SERVED) 9. 67 4. 81 11. 83 1. 88 0. 13 7. 25 42. 69 3. 75 CSE 3180 Semester 1 2005 week 4 / 46

Grouping with the WHERE Clause For Prime Ministers born after 1900, list the number of Prime Ministers born in each state and the total number of years served. SELECT STATE_BORN, COUNT(*), SUM(YRS_SERVED) FROM PRIME_MINISTER WHERE BIRTH_YR > 1900 GROUP BY STATE_BORN; STATE_BORN WA VIC NSW COUNT(*) 1 2 3 SUM(YRS_SERVED) ? 10. 50 6. 52 CSE 3180 Semester 1 2005 week 4 / 47

Grouping with the HAVING Clause For each state where the total years served by Prime Ministers born in that state is less than 10 years, give the number of Prime Ministers born in that state and the total number of years served. SELECT STATE_BORN, COUNT(*), SUM(YRS_SERVED) FROM PRIME_MINISTER GROUP BY STATE_BORN HAVING SUM(YRS_SERVED) < 10; STATE_BORN TAS QLD COUNT(*) 1 2 SUM(YRS_SERVED) 7. 25 0. 13 CSE 3180 Semester 1 2005 week 4 / 48

Grouping with the HAVING Clause PART_SUPPLIER PARTNO SUPPNO QTY_SUPP P 1 S 1 20 P 2 S 1 30 P 1 S 2 20 P 3 S 2 10 SELECT partno, Count(suppno) AS Supp_count, Sum(qty_supp) AS Total_qty FROM part_supplier GROUP BY partno HAVING count(suppno) > 1; PARTNO Supp_count P 1 2 Total_qty 40 CSE 3180 Semester 1 2005 week 4 / 49

Sub. Queries Provides the facility of a query supplying dynamic values to another query for use in the search conditions of the main query. Example: Give the name and age at which death occurred for each Prime Minister who died at an age less than the average. List in order of age at death. SELECT PM_NAME, DEATH_AGE FROM PRIME_MINISTER WHERE DEATH_AGE < ( SELECT AVG(DEATH_AGE) FROM PRIME_MINISTER); The subquery computes the average at death. The main query then selects the appropriate names and ages based on the values supplied by the sub-query. (in this case where the age at death is less than the average) CSE 3180 Semester 1 2005 week 4 / 50

Sub. Queries Give the name and death age for each prime minister who died at an age less than the average death age of prime ministers. List in ascending order of death age. SELECT FROM WHERE PM_NAME, DEATH_AGE PRIME_MINISTER DEATH_AGE < (SELECT AVG(DEATH_AGE) FROM PRIME_MINISTER) ORDER BY DEATH AGE; PM_NAME Holt H E Lyons J A Curtin J Deakin A DEATH_AGE 59 60 60 63 CSE 3180 Semester 1 2005 week 4 / 51

Sub. Queries Which prime minister died the oldest? Show the name and age. SELECT FROM WHERE PM_NAME, DEATH_AGE PRIME_MINISTER DEATH_AGE = (SELECT MAX(DEATH_AGE) FROM PRIME_MINISTER); PM_NAME Forde F M DEATH_AGE 93 CSE 3180 Semester 1 2005 week 4 / 52

Sub. Query with ANY Operator PART_SUPPLIER PARTNO SUPPNO P 1 S 1 P 2 S 1 P 1 S 2 P 3 S 2 QTY_SUPP 20 30 25 10 SELECT partno, suppno, qty_supp FROM Part_supplier WHERE qty_supp > ANY (SELECT avg(qty_supp) FROM Part-supplier); PARTNO SUPPNO P 2 S 1 P 1 S 2 QTY_SUPP 30 25 CSE 3180 Semester 1 2005 week 4 / 53

Multiple Nested Sub. Queries Give the name and birth year of each prime minister who was commissioned after Bob Hawke had turned 21. Order by birth year. SELECT FROM WHERE PM_NAME, BIRTH_YR PRIME_MINISTER PM_NAME = ANY (SELECT PM_NAME FROM MINISTRY WHERE YR_COMM > (SELECT BIRTH_YR + 21 FROM PRIME_MINISTER WHERE PM_NAME = ‘Hawke R J L’)) AND PM_NAME <> ‘Hawke R J L’ ORDER BY BIRTH_YR; CSE 3180 Semester 1 2005 week 4 / 54

Result of Previous Query Give the name and birth year of each prime minister who was commissioned after Bob Hawke had turned 21. Order by birth year. PM_NAME Menzies R G Mc. Ewan J Holt H E Mc. Mahon W Gorton J G Whitlam E G BIRTH_YR 1894 1900 1908 1911 1916 CSE 3180 Semester 1 2005 week 4 / 55

Sub. Queries SELECT partno, suppno, qty_supp FROM Part_supplier WHERE qty_supp > ANY (SELECT avg(qty_supp) FROM Part-supplier); Subqueries may be used in a number of SQL statements. select, update, insert, delete, create table, create view, create permit, create integrity · Subqueries may be nested to several levels. · Special comparison operators are used in additional to =, <>, >, <, etc to indicate comparison to a set of values: IN equals one of the values returned by the subquery ANY true if any value returned meets the condition ALL true if all values returned meet the condition CSE 3180 Semester 1 2005 week 4 / 56

Correlated Sub. Queries PART_SUPLIER PARTNO SUPPNO P 1 S 1 P 2 S 1 P 1 S 2 P 2 S 2 P 3 S 2 SELECT partno, suppno, qty_supp QTY_SUPP 20 30 25 20 10 FROM Part_supplier PS 1 WHERE qty_supp > ANY (SELECT avg(qty_supp) FROM Part-supplier PS 2 WHERE PS 2. partno = PS 1. partno); PARTNO SUPPNO QTY_SUPP P 1 S 2 25 P 2 S 1 30 CSE 3180 Semester 1 2005 week 4 / 57

Correlated Sub. Queries Which suppliers are supplying more than the average for a part and how much of that part do they supply? SELECT partno, suppno, qty_supp FROM Part_supplier PS 1 WHERE qty_supp > ANY (SELECT avg(qty_supp) FROM Part-supplier PS 2 WHERE PS 2. partno = PS 1. partno); · Subqueries (inner queries) are generally executed once and return a value or a set of values to the outer query. · With a correlated subquery, the outer query passes values to the inner query which then evaluates and returns a set of values to the outer query. This process repeats until the outer query terminates. CSE 3180 Semester 1 2005 week 4 / 58

Joining Tables EMPNO E 1 E 2 E 3 DEP ENAME RED BLUE BROWN MGRNO E 1 E 1 DEPTNO D 1 D 2 D 3 DNAME TAX PAY LEAVE SELECT e. empno AS Number, e. ename AS Name, d. dname AS Department FROM emp e, dep d WHERE e. deptno = d. deptno; Number Name E 1 RED E 2 BLUE E 3 BROWN Department TAX PAY CSE 3180 Semester 1 2005 week 4 / 59

A Search and Join Condition For each prime minister born in or after 1900, give his name, birth year and party. SELECT FROM WHERE P. PM_NAME, BIRTH_YR, PARTY PRIME_MINISTER P, MINISTRY M P. PM_NAME = M. PM_NAME AND BIRTH_YR >= 1900; PM_NAME Holt H E Mc. Ewen J Gorton J G BIRTH_YR 1908 1900 1911 PARTY Liberal Country Liberal CSE 3180 Semester 1 2005 week 4 / 60

A Search and Join Condition • For each prime minister born in or after 1900, give his name, birth year and party. This is the same as the previous query, but uses the DISTINCT operator SELECT FROM WHERE DISTINCT P. PM_NAME, BIRTH_YR, PARTY PRIME_MINISTER P, MINISTRY M P. PM_NAME = M. PM_NAME AND BIRTH_YR >= 1900; PM_NAME Holt H E Mc. Ewen J Gorton J G BIRTH_YR 1908 1900 1911 PARTY Liberal Country Liberal CSE 3180 Semester 1 2005 week 4 / 61

Multiple Joins Give the name, birth year, party and the year of marriage of prime ministers born in or after 1900. SELECT FROM WHERE AND DISTINCT P. PM_NAME, BIRTH_YR, MAR_YR, PARTY PRIME_MINISTER P, PM_MARRIAGE W, MINISTRY M P. PM_NAME = W. PM_NAME P. PM_NAME = M. PM_NAME BIRTH_YR >= 1900; PM_NAME Fraser J M Gorton J G Hawke R J L Holt H E Mc. Ewen J BIRTH_YR 1930 1911 1929 1908 1900 MAR_YR 1956 1935 1956 1946 1921 1968 PARTY Liberal Labor Liberal Country CSE 3180 Semester 1 2005 week 4 / 62

‘Mirroring’ Tables Base tables are held on disk. Consequently when a query is executed, it is a copy of the base table which is used to satisfy the query. Where a single table query is run and internal joining is required, a procedure of ‘mirroring’ or creating another copy of the table is used. This is done by using the correlation feature, and an example is shown on the next slide. If the base table values are altered, a new copy is written to disk after the successful completion of the query (commit) CSE 3180 Semester 1 2005 week 4 / 63

Joining a Table to Itself EMPNO E 1 E 2 E 3 ENAME RED BLUE BROWN MGRNO E 1 E 1 DEPTNO D 1 D 2 SELECT X. empno AS Number, X. ename AS Name, Y. ename AS Manager FROM emp X, emp Y (we now have two copies of the same table, x X and Y) WHERE X. mgrno = Y. empno; Number E 1 E 2 E 3 Name RED BLUE BROWN Manager RED RED CSE 3180 Semester 1 2005 week 4 / 64

Sub. Query with IN Operator List the name and party of each deputy prime minister who was also prime minister. SELECT FROM WHERE DISTINCT DEPUTY_NAME, PARTY DEPUTY_PM DEPUTY_NAME IN (SELECT PM_NAME FROM PRIME_MINISTER); DEPUTY_NAME Chifley J B Cook J Deakin A PARTY Labor Free Trade Protectionist CSE 3180 Semester 1 2005 week 4 / 65

The EXISTS Operator List the name, birth year and state represented for prime ministers who were also deputy prime ministers. SELECT FROM WHERE PM_NAME, BIRTH_YR, STATE_REP PRIME_MINISTER EXISTS (SELECT * FROM DEPUTY_PM WHERE DEPUTY_NAME = PM_NAME); PM_NAME Deakin A Cook J Hughes W M BIRTH_YR 1856 1860 1862 STATE_REP VIC NSW CSE 3180 Semester 1 2005 week 4 / 66

The NOT EXISTS Operator Give the name, state represented and death age of each prime minister who has no recreational interests. SELECT FROM WHERE PM_NAME, BIRTH_YR, DEATH_AGE PRIME_MINISTER P NOT EXISTS (SELECT * FROM PM_RECREATION WHERE PM_NAME = P. PM_NAME); PM_NAME Reid G H Fisher A Cook J STATE_REP NSW QLD NSW DEATH_AGE 73 66 87 CSE 3180 Semester 1 2005 week 4 / 67

Create View CREATE VIEW view-name [ (colname { , colname} ) ] AS subselect [WITH CHECK OPTION] Creates a virtual table by storing the view definition in the catalog. Updates, inserts and deletes are not permitted if the subselect accesses more than one base table; the view was created from a non-updatable view; any columns in the view are derived. Additionally inserts are not allowed if the view contains a where clause with the check option or if any not null not default column of the base table is not part of the view. CSE 3180 Semester 1 2005 week 4 / 68

Create View · The ‘with check option’ will not allow update of columns that are part of the view's where clause. · Provides logical data independence when base table structure changes. · Same data may be seen in different ways by different users. · Users perception may be simplified and views provide automatic security. CSE 3180 Semester 1 2005 week 4 / 69

Create View Emp (empno, empname, salary_pa, deptno) Dept (deptno, dname) CREATE VIEW empdetails (empno, empname, dname, salary_fn) AS SELECT e. empno, e. empname, d. dname, e. salary / 26 FROM emp e, dept d WHERE e. deptno = d. deptno; CSE 3180 Semester 1 2005 week 4 / 70

To Execute a Views are made active (executed) by running a select operation. To run the previous View the command structure would be: Select * from empdetails; Conditions (where) could be applied to further limit the output (result table) scope. And just to test you out, what would be the outline content of the result of the above select command ? CSE 3180 Semester 1 2005 week 4 / 71

The Purpose of SQL. . . and so ? SQL is a database access language which allows applications (and users) to enter, retrieve, update and delete data in a database. Ingres DBMS does NOT have a programming language which can be used to develop powerful database applications To achieve this, there must be a procedural language which interacts with SQL. Ingres has an ‘embedded SQL’ feature which interacts with Cobol, C, PL/1, . . . Ingres has a Knowledge Management module which allows for the building of Rules and Procedures CSE 3180 Semester 1 2005 week 4 / 72

The Purpose of SQL. . . and so ? Oracle has a procedural language - PL/SQL It also supports packages procedures snapshots triggers which are developed to provide specific application logic CSE 3180 Semester 1 2005 week 4 / 73