DOC PREVIEW
UW-Milwaukee COMPSCI 557 - Lecture Notes

This preview shows page 1-2-20-21 out of 21 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 21 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Announcements• Written Homework 1 due Nov 2– See course web page– Exercises 5.12, 5.15, 6.17, 6.20, 6.22 (a,c,f only). • Today– continue with SQL (chapter 8)•SELECTSELECTSELECT <attribute and function list>FROM <table list>WHERE <condition>GROUP BY <grouping attributes>HAVING <group conditions>ORDER BY <attribute list>optionalclausesSELECT SELECTSTATEMENTmany tablessingle tableinput to a SELECT statement is N tablesoutput is a single tableSELECT-FROM• all SELECT statements must have SELECT and FROM clauses– SELECT a1, a2, ..., an FROM t1, t2, ..., tm• conceptually evaluation of SELECT-FROM1. form cross product (t1 x t2 x ... x tm)2. project out attributes (a1, a2, ..., an) from the CP* in attribute list• a * in attribute list is shorthand for “all attributes”– SELECT * FROM employee;– above statement returns the entire employee tableexamples 1,2,3WHERE• the WHERE condition used to limit which tuplesof the m-way cross product are retainedSELECT dname, dlocationFROM department, dept_locationsWHERE department.dnumber = dept_locations.dnumber; # note = *not* ==Conceptual processing of SELECT-FROM-WHERE SELECT a1, a2, ..., an FROM t1, t2, ..., tmWHERE cond1. form cross product (t1 x t2 x ... x tm)2. retain CP rows where cond evaluates to ‘true’3. project (a1, a2, ..., an) from retained rowssimilar to a m-way joinSimple SQL Queries (contd.)• Query 2: For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate.Simple SQL Queries (contd.)• Query 2: For every project located in 'Stafford', list the project number, the controlling department number, and the department manager's last name, address, and birthdate.SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM PROJECT, DEPARTMENT, EMPLOYEEWHERE DNUM=DNUMBER AND MGRSSN=SSN AND PLOCATION='Stafford'Aliasing• Tables can be given short names to make queries easier to write (and to resolve ambiguity)SELECT dname, dlocationFROM department as D, dept_locations as DLWHERE D.dnumber = DL.dnumber;ALIASES• Some queries need to refer to the same relation twice– In this case, aliases are given to the relation name• Query 8: For each employee, retrieve the employee's name, and the name of his or her immediate supervisor.Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAMEFROM EMPLOYEE AS E, EMPLOYEE AS SWHERE E.SUPERSSN=S.SSN– In Q8, the alternate relation names E and S are called aliases or tuplevariables for the EMPLOYEE relation– We can think of E and S as two different copies of EMPLOYEE; E represents employees in role of supervisees and S represents employees in role of supervisorsSELECT DISTINCT• the default behavior of select is to not eliminate duplicate rows in the result table• remove duplicate rows w/ SELECT DISTINCTSELECT DISTINCT dlocationFROM dept_locations;SELECT DISTINCT dlocation, dnumberFROM dept_locations;returns set of departmentlocationsreturns entiredept_locationstableORDER BY• ORDER BY clause causes returned tuples to be ordered by some SELECT attribute (or attributes)SELECT fname, lname, salaryFROM employeeORDER BY salary;SELECT fname, lname, salaryFROM employeeORDER BY salary DESC;returns results in descending orderORDER BY# the ordering field can be a string # (lexical ordering is used)SELECT fname, lname, salaryFROM employeeORDER BY lname;# ordering attr need not be in SELECTSELECT fname, lnameFROM employee ORDER BY salary;Aggregates• Like RA, SQL supports “aggregate functions”SELECT MAX(salary) FROM employeeWHERE dno = 5;SELECT MIN(salary), MAX(salary), AVG(SALARY), COUNT(*) FROM employee;Conceptual processing of SELECT-FROM-WHEREaggregatesSELECT MAX(salary) FROM employeeWHERE dno = 5;1. form cross product (t1 x t2 x ... x tm)2. retain CP rows where cond evaluates to ‘true’3. apply aggregate functions in SELECT to column(s) of retained rowsmixing attributes and aggregates in SELECT clause?• you cannot mix aggregate functions and attributes in select clause (without GROUP BY)SELECT ssn, MAX(salary) FROM employee;not a legal SQL statementhow do you write a query to return the SSN and salary the highest paid employee?GROUP BY• The GROUP BY clause is used with aggregate functions to define groups of tuples to which to apply the aggregate functionsSELECT sex, MIN(salary), MAX(salary), AVG(SALARY), COUNT(*)FROM employeeGROUP BY sex;GROUPING (contd.)• Query 21: For each project, retrieve the project number, project name, and the number of employees who work on that project.Q21: SELECT PNUMBER, PNAME, COUNT (*)FROM PROJECT, WORKS_ONWHERE PNUMBER=PNOGROUP BY PNUMBER, PNAME– In this case, the grouping and functions are applied after the joining of the two relationsHAVING• HAVING is used w/ GROUP BY to impose conditions on groupspresent in resultTHE HAVING-CLAUSE (contd.)• Query 22: For each project on which more than two employees work, retrieve the project number, project name, and the number of employees who work on that project.Q22: SELECT PNUMBER, PNAME, COUNT(*)FROMPROJECT, WORKS_ONWHERE PNUMBER=PNOGROUP BY PNUMBER, PNAMEHAVING COUNT (*) >


View Full Document

UW-Milwaukee COMPSCI 557 - Lecture Notes

Download Lecture Notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Lecture Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Lecture Notes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?