YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και...

43
- Relational Calculus, SQL, QBE Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός

Transcript of YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και...

Page 1: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 1

Κεφάλαιο 4Κεφάλαιο 4

Η Γλώσσα SQL και Σχεσιακός Λογισμός

Page 2: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 2

Relational Calculus: IntroductionRelational Calculus: Introduction

The Relational Calculus (RC) is a non-procedural, formal language based on first-order predicate calculus

Queries in RC specify WHAT is to be retrieved (declarative) while the system takes care of HOW

Most commercial relational languages are based on relational calculus (QUEL, QBE, and SQL). Such languages emphasize ease and convenience of use.

In terms of expressiveness, RELATIONAL ALGEBRA and RELATIONAL CALCULUS are identical.

Page 3: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 3

Relational Calculus: Introduction (2)Relational Calculus: Introduction (2)

The Relational Calculus uses the notion of VARIABLE There are two flavors of the language:

– tuple calculus

variables refer to tuples from a specified relation

e.g., t is a tuple variable referring to a tuple of r(R)

– domain calculus

variables refer to individual values from a specified domain

e.g., d is a domain variable referring to the value which a tuple in r(R) has for attribute A (from domain D)

Page 4: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 4

Tuple CalculusTuple Calculus

A TUPLE CALCULUS EXPRESSION defines a new relation in terms of existing (base) relations.

An expression is constructed from the following elements:(1) tuple variables (e.g., t, v, w, t1, t2, t3, ... tn ), which are defined

to range over a specified relation instance r(R)

Tuple variables may be restricted, where, t.A, with A an attribute of R, denotes the A-component of the value of t

Example: t.Name

(2) conditions of the form x operator y, where

-- x, y are restricted tuple variables or constant values

-- operator Examples: t.Name = ‘mary’, t.City v.City

Page 5: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 5

Tuple Calculus ExpressionsTuple Calculus Expressions

(3) Well-Formed Formulas (WFFs), defined as:

a condition is a WFF

if f is a WFF, so is (f) and (f)

(where is the logical NOT)

if f and g are WFFs, so are (f g) and (f g)

(where are the logical AND, OR respectively)

if f is a WFF with free variable t, so are t(f), t(f)

(where , are the existential and universal quantifiers)

A tuple variable t is said to be bound in a formula f if it is one of the quantified variables in f. Otherwise, t is free in f.

Example: t is free in f1 : (t.City = ‘london’) and is bound in f2 : (t ) (t.DNumber = v.Dno), while v is free in f2

Page 6: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 6

Tuple Calculus ExpressionsTuple Calculus Expressions

Examples of WFFs– t.City = v.City– (t.City = v.City)– (t.City = v.City)– (t.City v.City)– (t.City = v.City) (w.City = london)– t (t.City = v.City)– t (t.City = athens)

if f is a WFF, then a tuple calculus expression is any expression of the form:

{ ti.Aj | ti r(Rk) AND f }

where Rk are relation schemes and Aj are attributes in Rk

Page 7: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 7

Calculus Query ExamplesCalculus Query Examples

Recall the sailors-boats database

SAILORS (Sid, SName, Rating)

BOATS (Bid, BName, Color)

RESERVE (Sid, Bid, Date)

QUERY1: Find the names of sailors whose id is greater than 10 and have rating ‘a’

RANGE of t is SAILORS

t.SName where (t.Sid > 10) (t.Rating = ‘a’)

This is an equivalent form of the pure RC query:

{ t.SName | t r(SAILORS) (t.Sid > 10) (t.Rating = ‘a’)) }

Page 8: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 8

Calculus Query Examples (2)Calculus Query Examples (2)

QUERY2: Find the names and ratings of sailors who have reserved boat number 3

RANGE of t is SAILORS

RANGE of v is RESERVE

t.SName, t.Rating where v ( (t.Sid = v.Sid) v.Bid = 3) )

QUERY3: Find the names sailors who have not reserved boat number 3

RANGE of t is SAILORS

RANGE of v is RESERVE

t.SName where ( v ( (t.Sid = v.Sid) v.Bid = 3) ) )

Page 9: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 9

SQL - IntroductionSQL - Introduction

SQL (Structured Query Language) has become the “standard” in query languages. It was first used in IBM’s prototype , called SYSTEM-R, developed at San Jose in the mid-seventies. SQL has gone over many evaluations.

There are 4 basic commands:– select (not to be confused with SELECTION in algebra)– insert– update– delete

The result of any query on relations is again a relation

Page 10: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 10

SQL - Informal DefinitionSQL - Informal Definition

Assume the EMPLOYEE relation and the following query: “Find the names of employees who earn more than 30000” This is expressed in SQL as:

select e.Name

from EMPLOYEE e

where (e.Salary > 30000)– e is a tuple variable defined to range over the relation

EMPLOYEE (in the from clause)– e.Name, as a restricted tuple variable, specifies the value

of e in attribute Name, and is the target list (specifies in the select clause the projections of columns)

– (e.Salary > 30000) is the qualification (specifies in the where clause all selections and joins)

Page 11: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 11

SQL -- Formal DefinitionSQL -- Formal Definition

A selection clause is a comparison between a restricted tuple variable x and a constant c of the form : x operator c where operator Example: t.Name = ‘mary’, t.Salary

A join clause is a comparison between two restricted tuple variables x and y of the form x operator y where x, y belong to different relations and

operator Example: t.Name = v.EName

Page 12: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 12

SQL -- Formal Definition (2)SQL -- Formal Definition (2)

A qualification is a Boolean combination (i.e., with logical and, or, not) of selection and join clauses.)Example: (t.Name = v.EName) and (t.Salary > 30000)

QUALIFICATION SEMANTICS

A qualification Q describes the subset of the Cartesian product of the ranges of its tuple variables that satisfy Q

Example: Consider relations: EMPLOYEE(SSN, DNumber), and DEPARTMENT(Dno, mgrSSN), with e and d tuple variables

Q = (e.DNumber = d.Dno) and (d.mgrSSN = 9876)

Semantics of Q: The set of e, d pairs that satisfy Q

Page 13: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 13

SQL Qualification SemanticsSQL Qualification Semantics

.SSN DNumber1234 59998 49876 4

Dno MgrSSN5 33344 98761 8886

X =

SSN DNumber Dno mgrSSN1234 5 5 33441234 5 4 98761234 5 1 88869998 4 5 33449998 4 4 98769998 4 1 88869876 4 5 33449876 4 4 98769876 4 1 8886

SSN DNumber Dno mgrSSN9998 4 4 98769876 4 4 9876

Finally, after establishing the subsetof EMPLOYEE X DEPARTMENTthat satisfies the qualification Q, weget two tuples:

Cartesian Product

EMPLOYEE DEPARTMENT

Page 14: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 14

SQL -- Complete FormatSQL -- Complete Format

select [ distinct ] target_list

from tuple_variable_list

[ where qualification ]

[ group by grouping_attributes ]

[ having group_condition ]

[ order by target_list_subset ]

– A query is evaluated by first applying the WHERE clause, then GROUP-BY and HAVING (all optional), and finally the SELECT clause (target list) - ordering the resulting tuples if required in the ORDER BY clause (also optional).

Page 15: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 15

SQL: Sailor ExamplesSQL: Sailor Examples

Consider again the Sailors-Boats database:

SAILORS (Sid, SName, Rating)

BOATS (Bid, BName, Color)

RESERVE (Sid, Bid, Date)

SQUERY1: Find the names of sailors who have reserved boat number 2select s.SName

from SAILORS s, RESERVE r

where s.Sid = r.Sid and r.Bid = 2

Page 16: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 16

SQL Sailor Examples (2)SQL Sailor Examples (2)

SQUERY2: Find the names of sailors who have reserved a red boatselect s.SName

from SAILORS s, BOATS b, RESERVE r

where s.Sid = r.Sid and r.Bid = b.Bid and b.Color = “red”

SQUERY3: Find the colors of the boats reserved by eleniselect b.Color

from SAILORS s, BOATS b, RESERVE r

where s.Sid = r.Sid and r.Bid =b.Bid and s.SName= “eleni”

Page 17: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 17

SQL Sailor Examples (3)SQL Sailor Examples (3)

SQUERY4: Find the names of the sailors who have reserved at least one boatselect s.SName

from SAILORS s, RESERVE r

where s.Sid = r.Sid

SQUERY5: Find the names of sailors who have reserved a red or a green boatselect s.SName

from SAILORS s, BOATS b, RESERVE r

where s.Sid = r.Sid and r.Bid = b.Bid and

(b.Color = “red” or b.Color = “green”)

Page 18: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 18

SQL Sailor Examples (4)SQL Sailor Examples (4)

SQUERY6: Find the names of sailors who have reserved both a red and a green boat

select s.SName

from SAILORS s, BOATS b1, RESERVE r1,

BOATS b2, RESERVE r2

where s.Sid = r1.Sid and r1.Bid = b1.Bid and b1.Color = “red” and

s.Sid = r2.Sid and r2.Bid = b2.Bid and b2.Color = “green”

Page 19: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 19

SQL Elaboration: Examples from the COMPANY database

SQL Elaboration: Examples from the COMPANY database

EMPLOYEE ( SSN, Name, BirthDate, Address, Sex, Salary, SupSSN, DNumber)

DEPARTMENT ( DNumber, DName, MgrSSN, MgrStartDate)

PROJECT ( PNumber, PName, Location, DNumber)

DEPT_LOCATION ( DNumber, DLocation)

WORKS_ON ( SSN, PNumber, HoursPW)

DEPENDENT ( SSN, DependName, Sex, BirthDate, Relationship)

Page 20: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 20

SQL: Target List Examples (1)SQL: Target List Examples (1)

TARGET LISTS: Each item in a target list can be as general as: attribute_name = expression

where expression is any arithmetic or string expression over restricted tuple variables and constants (also built-ins and aggregates.)

CQUERY1: List, increased by 10000 the salary of employees who have worked on two different projects more than 25 hours select e.Name, Salary = e.Salary + 10000

from EMPLOYEE e, WORKS_ON w1, WORKS_ON w2

where e.SSN = w1.SSN and e.SSN = w2.SSN and w1.HoursPW > 25 and w2.HoursPW > 25

and w1.PNumber != w2.PNumber

Page 21: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 21

SQL: Target List Examples (2)SQL: Target List Examples (2)

TARGET Lists may also contain the keyword DISTINCT– Since SQL does not treat relations as sets, duplicate tuples may

appear, therefore DISTINCT is used to eliminate the duplicates

CQUERY2: Show all distinct (different values) salaries that employees earn

select distinct e.Salary

from EMPLOYEE e

The above query also shows that the WHERE clause is optional (with missing WHERE, all tuples qualify)

Page 22: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 22

SQL: Target List Examples (3)SQL: Target List Examples (3)

The TARGET List may contain the wild character: “*”

When a * is used, it implies that all attributes from the relation(s) should be retrieved

CQUERY3: Show all employees in department number 4

select *

from EMPLOYEE e

where e.Dnumber = 4

Page 23: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 23

SQL: Use of Tuple VariablesSQL: Use of Tuple Variables

Relation names can be used instead of tuple variables Tuple variables can be implicit if the system can figure

out which relation each attribute belongs to.

CQUERY4: For every project located in Athens, list the project name, the controlling department number and the department’s manager name

select PName, DEPARTMENT.DNumber, Name

from EMPLOYEE, DEPARTMENT, PROJECT

where PROJECT.DNumber = DEPARTMENT.DNumber and MgrSSN = SSN and Location =

“athens”

Page 24: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 24

SQL Qualification Examples -1SQL Qualification Examples -1

QUALIFICATIONS: Each item in a qualification can be as general as: expression = expression

CQUERY5: Find the names of employees whose salary is more than double the salary of some other employee (also show their name)

select Name1 = e1.Name, Name2 = e1.Name

from EMPLOYEE e1, EMPLOYEE e2

where 2 * e1.Salary < e2.Salary

Page 25: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 25

SQL Qualification Examples -2SQL Qualification Examples -2

SQL provides direct support of several SET operations, like:– union– minus– intersect

CQUERY6: Find the names of employees who work in department number 4 and earn at most 40000

(select Name

from EMPLOYEE

where DNumber = 4 ) minus (select Name

from EMPLOYEE

where Salary > 40000 )

Page 26: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 26

SQL Qualification Examples -3SQL Qualification Examples -3

CQUERY7: List all project names for projects that involve an employee whose name is “jenny” as a worker or as a manager of the department that controls the project

(select PName from EMPLOYEE, PROJECT, DEPARTMENT where EMPLOYEE.DNumber=DEPARTMENT.DNumber

and MgrSSN = SSN and Name = “jenny” ) union

(select PName from EMPLOYEE, PROJECT,

WORKS_ON where PROJECT.PNumber = WORKS_ON.PNumber and WORKS_ON.SSN=EMPLOYEE.SSN and

Name = “jenny” )

Page 27: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 27

SQL Qualification Examples-4SQL Qualification Examples-4

NESTING OF SQL QUERIES: A complete SELECT query (called the nested query) can be specified in the qualification of another query (called the outer query)

CQUERY8: List all employees that work in the research departmentselect Name

from EMPLOYEE

where DNumber in

(select DNumber

from DEPARTMENT

where DName = “research” )

Page 28: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 28

SQL Qualification Examples-5SQL Qualification Examples-5

CORRELATED NESTED Queries: If a condition in the qualification of a nested query references an attribute in the outer query, the two are said to be CORRELATED

The result of the nested query is different for each tuple of the relation(s) in the outer query

CQUERY9: List the name of each employee who has a dependent with the same sex as the employee

select Name from EMPLOYEE e where e.SSN in

(select d.SSN from DEPENDENT d where d.SSN = e.SSN and d.Sex = e.Sex )

Page 29: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 29

SQL Qualification Examples-6SQL Qualification Examples-6

Any query that uses the IN comparison operator (tests for set membership) can always be expressed as a single block query (flat query)

CQUERY9a: List the name of each employee who has a dependent with the same sex as the employee

select Name from EMPLOYEE e, DEPENDENT d where e.SSN = d.SSN and d.Sex = e.Sex )

Page 30: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 30

SQL Qualification Examples-7SQL Qualification Examples-7

Similar connectives to IN are:not in (tests for set non-membership)

OP any (OP relationship with some tuple in a set)

OP all (OP relationship with all tuples in a set)

where OP CQUERY10: List all employees that earn more than

everybody in the research departmentselect Name from EMPLOYEE

where Salary > all (select Salary from EMPLOYEE e,

DEPARTMENT d where e.DNumber = d.DNumber and DName = “research” )

Page 31: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 31

SQL Qualification Examples-8SQL Qualification Examples-8

SQL also provides SET COMPARATORS:– contains, not contains (a set (not) being a superset)– exists, not exists (a set (not) being empty)

CQUERY11: List all employees who work on all projects controlled by department number 4select Name

from EMPLOYEE e

where (select w.PNumber from WORKS_ON where w.SSN = e.SSN)

contains (select PNumber

from PROJECT where DNumber = 4)

Page 32: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 32

SQL Qualification Examples-9SQL Qualification Examples-9

CQUERY9b: List the name of each employee who has a dependent with the same sex as the employee select Name

from EMPLOYEE e where exists (select *

from DEPENDENT d where d.SSN=e.SSN and d.Sex =

e.Sex)

CQUERY12: List the employees with no dependents select Name

from EMPLOYEE e where not exists (select *

from DEPENDENT d where d.SSN=e.SSN )

Page 33: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 33

SQL Aggregates and Groups-1SQL Aggregates and Groups-1

SQL supports FIVE AGGREGATE FUNCTIONS (can be applied to any attribute X of a relation):

count ( [DISTINCT] X) :number of unique values in X

sum ( [DISTINCT] X) :sum of unique values in X

avg ( [DISTINCT] X) :average of unique values in X

max (X) :maximum value in X

min (X) :minimum value in X

Aggregates return a single value

Page 34: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 34

SQL Aggregates and Groups-2SQL Aggregates and Groups-2

Some SQL implementations do not allow more than one value in the target list

CQUERY13: List the maximum salary, the minimum salary, and the average salary among all employees

select max(Salary), min(Salary), avg(Salary from EMPLOYEE

QUERY14: Find the number of employees

select count(*) from EMPLOYEE

Page 35: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 35

SQL Aggregates and Groups-3SQL Aggregates and Groups-3

Qualified Aggregates: The set on which aggregates apply can be restricted by the where-clause

CQUERY15: Find the average salary of employees in department with number 4

select avg(Salary)

from EMPLOYEE

where DNumber = 4

Page 36: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 36

SQL Aggregates and Groups-4SQL Aggregates and Groups-4

Aggregate Functions: Aggregates or groups of tuples are computed using the GROUP BY clause

CQUERY16: In each department, find the minimum age of employees who earn more than 40000

select DNumber, max(BirthDate)

from EMPLOYEE

where Salary > 40000

group by DNumber

– Note that the grouping attributes MUST ALSO appear in the select clause

Page 37: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 37

SQL Aggregates and Groups-5SQL Aggregates and Groups-5

CQUERY17: For each project, retrieve the project number, project name, and the number of employees that work on that project

select p.PNumber, p.PName, count(*)

from PROJECT p, WORKS_ON w

where p.PNumber = w.PNumber

group by PNumber, PName

In the above query, the grouping and functions are applied after joining the relations PROJECT and WORKS_ON.

Page 38: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 38

SQL Aggregates and Groups-6SQL Aggregates and Groups-6

HAVING CLAUSE: Qualifications that have to be satisfied by each group formed by the group by- clause are put in a HAVING clause

CQUERY18: Find the average salary of employees born after 1950 for each department with more than 10 such employees

select DNumber, avg(Salary)

from EMPLOYEE

where BirthDate > “1.1.51”

group by DNumber

having count(*) > 10

Page 39: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 39

SQL Aggregates and Groups-6SQL Aggregates and Groups-6

CQUERY19: Find the average salary of employees born after 1950 for each department with more than 10 employees

select e1.DNumber, avg(e1.Salary)

from EMPLOYEE e1

where e1.BirthDate > “1.1.51”

group by e1.DNumber

having 10 < any

(select count(e2.SSN)

from EMPLOYEE e2

where e2.DNumber = e1.DNumber )

Page 40: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 40

SQL Updates (1)SQL Updates (1)

INSERT command

insert into relation_name select-statement

or

insert into relation_name values (value_list)

CUPDATE1: Insert a new department

insert into DEPARTMENT

values (6, “inventory”, 9879, “30.5.45”)

Page 41: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 41

SQL Updates (2)SQL Updates (2)

Suppose we have a relation DEPT_INFO as in:DEPT_INFO ( DeptName, NoOfEmpl, TotalSalary)

We can insert tuples in this relation with (CUPDATE2):

insert into DEPT_INFO

select d.DName, count(*), sum(e.Salary)

from DEPARTMENT d, EMPLOYEE e

where d.DNumber=e.DNumber

group by d.DName

Page 42: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 42

SQL Updates (3)SQL Updates (3)

DELETE command

delete from relation_name where-qualification

SEMANTICS:

-- Execute the corresponding SELECT command and then remove the resulting tuples from relation_name

CUPDATE3:

delete from EMPLOYEE

where DNumber in (select DNumber

from DEPARTMENT

where DName = “admin”)

Page 43: YV - Relational Calculus, SQL, QBE 154 Κεφάλαιο 4 Η Γλώσσα SQL και Σχεσιακός Λογισμός.

YV - Relational Calculus, SQL, QBE 43

SQL Updates (4)SQL Updates (4)

UPDATE commandupdate relation_name

set target_list

where qualification

SEMANTICS:

-- Execute the two corresponding SELECT commands, then remove the old tuples from relation, then insert the new ones

CUPDATE4: update EMPLOYEEset Salary = Salary * 1.14

where DNumber in (select DNumber

from DEPARTMENT

where DName = “admin”)