1 / 70

Lecture 4

Lecture 4. Structured Query Language Data Definition Language Data Manipulation Language. PowerPoint Overheads. The Overheads and other materials are accessible via http://www.csse.monash.edu.au/courseware/cse3180 Files should be saved and copied to A: (filename)

ivrit
Download Presentation

Lecture 4

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lecture 4 Structured Query Language Data Definition Language Data Manipulation Language

  2. PowerPoint Overheads • The Overheads and other materials are accessible via http://www.csse.monash.edu.au/courseware/cse3180 • Files should be saved and copied to A:\ (filename) • Then you can browse these via PowerPoint 97/2000 • or you can display them on the terminal screen (in the labs this will be Office 2000/XP software).

  3. 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

  4. University Notes Thursday March 31st is the Semester 1 census date It is also the last date to discontinue Semester 1 or full-year 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.

  5. 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.

  6. 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

  7. SQL Structured Query Language Originally designed and implemented by IBM research as the interface to relational databases. ANSI Standard 1986. SQL99 Standard is current. SQL93 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. SQL - Data Manipulation

  8. 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)

  9. 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)

  10. 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 DB2, MySQL, SQLServer and Ingres support the 'owner' concept. Hence only 'owners' can DROP nnnn Some DBMS have an ALTER command to vary attribute characteristics. Ingres V6.4 does not support this feature

  11. 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.

  12. DML - Data Manipulation Language Group 2 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

  13. CREATE The syntax is : create table <tablename> ( columnname format {,columnname format}) e.g. create table wages(name varchar2(10), ID number(2,0), Department varchar2(3), date_comm date); OR create table wages(name varchar2(10) not null, ID number(2,0) not null, Department varchar2(3) not null, date_comm date) not null;

  14. 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 …)

  15. 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;

  16. 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);

  17. 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

  18. 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)

  19. Oracle Loader Similar functions - expects the Input as a text file. Some features : skip - starting point of load 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

  20. 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

  21. 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 ?)

  22. Copy There are a number of forms associated with ‘COPY’ 1. Copying data from 1 database to another remote to local local to remote remote to remote 2. Copy data from one table to another (single database) Copy data to one table from another An example: copy from rsimpson@cot2180 (to the current database) create empcopy2 using select * from user.dept

  23. 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)

  24. 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

  25. Select Example (Oracle) PART PARTNO P1 P2 P3 PNAME NUT BOLT CARAVAN PRICE 0.20 1.00 5000.00 QOH 20 40 3 SELECT 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

  26. 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)

  27. 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]

  28. 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.

  29. SELECT Vocabulary ALL AND ANY BETWEEN BY DISTINCT EXISTS FROM GROUP HAVING IS IN LIKE NOT NULL OR ORDER SELECT UNION WHERE Arithmetic Operators + - * / Relational Operators = != < > <= >= Logical Operators AND OR NOT Parentheses ( ) Special Operators BETWEEN IN LIKE NULL Existential Operator EXISTS

  30. 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, BIRTH_YR + DEATH_AGE FROM PRIME_MINISTER WHERE STATE_BORN = ‘NSW’ ORDER BY BIRTH_YR; PM_NAME BIRTH_YR BIRTH_YR + DEATH_AGE Barton E 1849 1920 Page E C G 1880 1961 Chifley J 1885 1951 Holt H E 1908 1967 McMahon W 1908 ? Whitlam E G 1916 ?

  31. 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 BIRTH_YR STATE_BORN Deakin A 1856 VIC Bruce S M 1883 VIC Scullin J H 1876 VIC Menzies R G 1894 VIC Curtin J 1885 VIC Logical Operators

  32. Combining Logical Operators Which prime ministers were born in NSW and then represented Victoria or have simply not served less than two years? SELECT PM_NAME, STATE_BORN, STATE_REP, YRS_SERVED FROM PRIME _MINISTER WHERE STATE_REP = ‘VIC’ AND STATE_BORN = ‘NSW’ OR NOT YRS_SERVED < 2; PM_NAME STATE_BORN STATE_REP YRS_SERVED Holt H E NSW VIC 1.88 Gorton J G VIC VIC 3.17 Whitlam E G NSW NSW 2.92 Fraser J M VIC VIC 7.33

  33. 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

  34. 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(*) COUNT(DISTINCT PM_NAME) Liberal PMs 5 2

  35. Union Operator Combines the contents of two identical tables create table compress as select name, identity, date_of_birth, gender from personnel1 union select name, perscode, birthdate, m_or_f from personnel2; Identical means that attribute types, size, domains of the Union attributes must be the same. Arttibute names do not.

  36. Union Assume that the following ‘Names’ data is contained in 2 tables LongtimeProspect Adah TalbotAdah Talbot Dick Jones Dory Kenson Donald Rollo Elbert Talbot Elbert Talbot George Phepps George Oscar Jed Hopkins Pat LavayPat Lavay Peter Lawson Ted Butcher Wilfred LowellWilfred Lowell (8 names) (8 names)

  37. 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 note that there are NO duplicates Jed Hopkins Pat Lavay Peter Lawson Ted Butcher Wilfred Lowell (12 names)

  38. DELETE 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;

  39. UPDATE UPDATE tablename [corr-name] [ FROM tablename [corr-name] {, tablename [corr-name]}] 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;

  40. 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) Description count integer Count of Occurrences sum integer,float,money Summation avg float,money Average (sum/count) max same as the argument Maximum value min same as the argument Minimum value

  41. Use of SET functions PART PartNo Pname Price QOH P1 NUT 1.00 20 P2 BOLT 1.00 20 P3 CARAVAN 5000.00 3 SELECT count(partno) AS Part_count, avg(price) AS Av_price, count(distinct price) AS Price_count FROM part; Part_count Av_Price Price_count 3 1667.33 2 Set functions supported = avg count max min sum Set functions may not be used directly in a search condition

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

  43. STATE_BORN STATE_REP COUNT(*) SUM(YRS_SERVED) ? NSW 4 9.67 ? QLD 1 4.81 NSW NSW 5 11.83 NSW VIC 1 1.88 QLD QLD 2 0.13 TAS TAS 1 7.25 VIC VIC 7 42.69 VIC WA 1 3.75 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;

  44. 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 COUNT(*) SUM(YRS_SERVED) WA 1 ? VIC 2 10.50 NSW 3 6.52

  45. 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 COUNT(*) SUM(YRS_SERVED) TAS 1 7.25 QLD 2 0.13

  46. Grouping with the HAVING Clause PART_SUPPLIER PARTNO SUPPNO QTY_SUPP P1 S1 20 P2 S1 30 P1 S2 20 P3 S2 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 Total_qty P1 2 40

  47. SubQueries 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_AGEFROM PRIME_MINISTER WHERE DEATH_AGE < ( SELECT AVG(DEATH_AGE) FROM PRIME_MINISTER); The subquery computes the average age 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)

  48. SubQueries 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 PM_NAME, DEATH_AGE FROM PRIME_MINISTER WHERE DEATH_AGE < (SELECT AVG(DEATH_AGE) FROM PRIME_MINISTER) ORDER BY DEATH AGE; PM_NAME DEATH_AGE Holt H E 59 Lyons J A 60 Curtin J 60 Deakin A 63

  49. SubQueries Which prime minister died the oldest? Show the name and age. SELECT PM_NAME, DEATH_AGE FROM PRIME_MINISTER WHERE DEATH_AGE = (SELECT MAX(DEATH_AGE) FROM PRIME_MINISTER); PM_NAME DEATH_AGE Forde F M 93

  50. SubQuery with ANY Operator PART_SUPPLIER PARTNO P1 P2 P1 P3 SUPPNO S1 S1 S2 S2 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 P2 P1 SUPPNO S1 S2 QTY_SUPP 30 25

More Related