SQL Queries: Understanding SELECT, WHERE, GROUP BY, and HAVING Clauses, Study Guides, Projects, Research of Introduction to Database Management Systems

Download SQL Queries: Understanding SELECT, WHERE, GROUP BY, and HAVING Clauses and more Study Guides, Projects, Research Introduction to Database Management Systems in PDF only on Docsity!

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) -^ SELECT

SELECT

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

optionalclauses

SELECT-FROM

-^ all SELECT statements must have SELECTand 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,

Conceptual processing ofSELECT-FROM-WHERE

SELECT

a1, a2, ..., an FROM^

t1, t2, ..., tm WHERE cond

1.^ 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 join

Simple SQL Queries (contd.)

-^ Query 2: For every project located in 'Stafford', list the projectnumber, the controlling department number, and the departmentmanager's last name, address, and birthdate.

Aliasing

-^ Tables can be given short names to make querieseasier to write (and to resolve ambiguity) SELECT

dname, dlocation FROM^

department as D, dept_locations as DL WHERE 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 thename of his or her immediate supervisor.Q8:^ SELECT

E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM^

EMPLOYEE AS E, EMPLOYEE AS S WHERE

E.SUPERSSN=S.SSN

-^ In Q8, the alternate relation names E and S are called

aliases^ or

tuple

variables 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

supervisors

ORDER BY

-^ ORDER BY

clause causes returned tuples to be ordered by some

SELECT

attribute (or attributes) SELECT fname, lname, salaryFROM^

employee ORDER BY salary;SELECT fname, lname, salaryFROM^

employee ORDER BY salary DESC;

returns results indescending order

ORDER BY

# the ordering field can be a string# (lexical ordering is used)SELECT

fname, lname, salary FROM^

employee ORDER BY

**lname;

ordering attr need not be in SELECTSELECT**

fname, lname FROM^

employee ORDER BY

salary;

Conceptual processing ofSELECT-FROM-WHERE

aggregates

SELECT

MAX(salary) FROM^

employee WHERE

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 tocolumn(s) of retained rows

mixing attributes and aggregates in

SELECT clause?

-^ you cannot mix aggregate functions andattributes in select clause (without GROUP BY) SELECT ssn, MAX(salary)FROM employee;

not a legal SQLstatement

how do you write a query to return the SSN andsalary the highest paid employee?

GROUPING (contd.)

-^ Query 21: For each project, retrieve the project number,project name, and the number of employees who workon that project.Q21:

SELECT

PNUMBER, PNAME, COUNT (*) FROM^

PROJECT, WORKS_ON WHERE

PNUMBER=PNO GROUP BY

PNUMBER, PNAME

• In this case, the grouping and functions are applied after thejoining of the two relations

HAVING

-^ HAVING is used w/ GROUP BY to imposeconditions on groups present in result