Information Resources Management

About This Presentation

Title:

Information Resources Management

Description:

List the office number and phone number for all offices in New York. Select Example ... List all employees whose name is Rita and whose last name is at least 4 ... – PowerPoint PPT presentation

Number of Views:40

Avg rating:3.0/5.0

Slides: 73

Provided by: KevinSt4

Learn more at: https://www.andrew.cmu.edu

more less

Transcript and Presenter's Notes

Title: Information Resources Management

1
Information Resources Management

February 27, 2001

2
Agenda

Administrivia
Exam
SQL Part 1
Homework 5

3
Administrivia

Homework 4

4
Exam

Results
Review

5
SQLStructured Query Language

The standard relational database language
Two Parts
DDL - Data Definition Language
DML - Data Manipulation Language

6
SQL - DDL

Data Definition Define schemas, delete
relations, create indices, modify schemas
View Definition
Authorization
Integrity

7
SQL - DML

Select, Insert, Modify, Delete Tuples
Interactive
Embedded
Transaction Control

8
SQL - DML

SELECT
Single table
Multiple tables
INSERT
UPDATE
DELETE

9
SELECT

SELECT attributes
FROM table(s)
WHERE conditions
Result is a relation/table

10
SELECT Example

List all information in the Office table
SELECT
FROM Office

11
SELECT Example

List all offices in New York state
SELECT
FROM Office
WHERE State NY

12
SELECT Example

List owners with at least 75 of some property
SELECT
FROM PctOwned
WHERE PctOwned 75

13
Select Example

List all offices in Springfield, IL

14
Select Example

List all offices in Springfield, IL
SELECT
FROM Office
WHERE City Springfield
AND State IL

15
Select Example

List all properties listed by office 100 or 150

16
Select Example

List all properties listed by office 100 or 150
SELECT
FROM Property
WHERE OfficeNbr 100
OR OfficeNbr 150

17
Select Example

List the office number and phone number for all
offices in New York

18
Select Example

List the office number and phone number for all
offices in New York
SELECT OfficeNbr, PhoneNbr
FROM Office
WHERE State NY

19
String Operations - LIKE

- match any substring ()
__ - match any character (?)
SELECT EmpID, Name
FROM Employee
WHERE Name LIKE Tom

20
LIKE Examples

List all properties whose description includes a
fireplace

21
LIKE Examples

List all properties whose description includes a
fireplace
SELECT
FROM Property
WHERE Description LIKE fireplace

22
LIKE Examples

List all employees whose name is Rita and whose
last name is 4 characters long

23
LIKE Examples

List all employees whose name is Rita and whose
last name is 4 characters long
SELECT
FROM Employee
WHERE Name LIKE Rita _ _ _ _

24
LIKE Examples

List all employees whose name is Rita and whose
last name is at least 4 characters long

25
LIKE Examples

List all employees whose name is Rita and whose
last name is at least 4 characters long
SELECT
FROM Employee
WHERE Name LIKE Rita _ _ _ _

26
Nulls

An attribute that does not have any value is
assigned a value of NULL
not the same as zero
not the same as empty string
indicates no or unknown value

27
Testing for Nulls

WHERE attribute IS NULL
WHERE attribute IS NOT NULL

28
Selecting Expressions

A mathematical expression can be selected instead
of an attribute
SELECT col1, col2col3, col4100
FROM table
WHERE conditions

29
Expressions Example

Assuming 5 commission, give the address and
potential commission for all Erie properties
SELECT address, price0.05
FROM property
WHERE cityErie AND state PA

30
Selecting Expressions - 2

Selected expressions can be given a name using AS
SELECT col1, col2col3 AS newname
FROM table
WHERE conditions

31
Expressions Example - 2

Assuming 5 commission, give the address and
potential commission for all Erie properties
SELECT address, price0.05 AS comm
FROM property
WHERE cityErie AND state PA

32
Renaming with AS

Can also use AS in FROM
SELECT FROM somelongtbl AS A
rename table
useful with multiple tables

33
Eliminating Duplicates

SELECT DISTINCT
Entire tuple must be the same to be eliminated as
a duplicate

34
Specifying the Order

SELECT
ORDER BY attribute(s)
SELECT
ORDER BY attrib ASC, attrib DESC
attributes must be SELECTed

35
Order Duplicate Example

List all office numbers for offices with
employees sort by office number
SELECT DISTINCT OfficeNbr
FROM Employee
ORDER BY OfficeNbr

36
Order Duplicate Example

List the name, city, and state for all owners
that own at least 50 of a property sort by
state (descending) and city

37
Order Duplicate Example

List the name, city, and state for all owners
that own at least 50 of a property sort by
state (descending) and city
SELECT DISTINCT Name, City, State
FROM Owner AS O, PctOwned AS P
WHERE O.OwnerSSN P.OwnerSSN
AND PctOwned 50
ORDER BY State DESC, City

38
Set Operations

UNION (?)
INTERSECT (?)
EXCEPT (-)
Add ALL to keep duplicates

39
Aggregation Functions

AVG
MIN
MAX
SUM
COUNT

40
Aggregation Examples

How many employees work at office 200?
SELECT COUNT()
FROM Employee
WHERE OfficeNbr 200

41
Aggregation Examples

What is the average price of the listings for
office 225?

42
Aggregation Examples

What is the average price of the listings for
office 225?
SELECT AVG(Price)
FROM Property
WHERE OfficeNbr 225

43
GROUP BY HAVING

SELECT attributes, aggregation(attributes)
FROM table
WHERE conditions
GROUP BY attributes
HAVING condition
HAVING only applies to each group (optional)

44
GROUP BY Example

What is the average price of a listing for each
office?
SELECT OfficeNbr, AVG(Price)
FROM Property
GROUP BY OfficeNbr

45
GROUP BY Example

For offices with more than 100 listings, what is
the total value of each offices listings?

46
GROUP BY Example

For offices with more than 100 listings, what is
the total value of each offices listings?
SELECT OfficeNbr, SUM(Price)
FROM Property
GROUP BY OfficeNbr
HAVING COUNT(PropertyID) 100

47
Cartesian Product Example

List the names of all employees who work in
offices in PA
SELECT name
FROM Employee, Office
WHERE state PA

48
Employee
49
Office
50
FROM Employee, Office
51
FROM Employee,Office WHERE state PA
What is needed to fix this?
52
Multiple Tables Example

List the names of all employees who work in
offices in PA
SELECT E.Name
FROM Employee AS E, Office AS O
WHERE E.OfficeNbr O.OfficeNBR
AND O.State PA

53
Multiple Tables Example

List the employee ID and name of all employees
(not managers) who have Full access to PC 173

54
Multiple Tables Example

List the employee ID and name of all employees
(not managers) who have Full access to PC 173
SELECT E.EmpID, E.Name
FROM Employee AS E, PCAccess AS P
WHERE E.EmpID P.EmpID
AND P.AccessType Full
AND P.PC 173

55
Multiple Tables - Join Conditions

Usually joined via foreign keys
A - B - C - D (4 tables)
3 join conditions (in WHERE)
A - B
B - C or A - C
C - D or A - D or B - D
composite keys - more conditions

56
Duplicate Attribute Names

Office x Property
Attributes are OfficeNbr, Address, City, etc. and
PropertyID, Address, City, etc.
To distinguish office address from property
address, attach relation name (dot) to the front
of those attributes that are duplicated

57
Duplicate Attribute Names

OfficeNbr, Office.Address, Office.City,
Office.State, etc
PropertyID, Property.Address, Property.City,
Property.State, etc
Can also use AS

58
Subqueries

Result of a SELECT is a new table
Can compare an attribute against a table (one
attribute only)
Is it there?
How does it compare to the values in the table?

59
Subqueries

Can also test how many rows are in a table
Are there any?
Is there only one?

60
Subqueries

IN
NOT IN
EXISTS
NOT EXISTS
UNIQUE
NOT UNIQUE

SOME
(, , )
ALL
(, , )

61
Subquery Example

List property for sale in PA at a price greater
than the average price in PA
SELECT FROM Property
WHERE state PA AND price ALL
(SELECT avg(price) FROM Property
WHERE state PA)

62
Using Subqueries

Often, either a join or a subquery can be used
for the same result
Subquery may be easier
If single value compared against list
If existence (unique) needed
If simple query identified nested

63
Subquery Example

List the names of all managers whose offices have
at least 1 property listed for at least 1,000,000

64
Subquery Example

List the names of all managers whose offices have
at least 1 property listed for at least
1,000,000
SELECT OfficeNbr
FROM Property
WHERE Price 1000000

65
Subquery Example

List the names of all managers whose offices have
at least 1 property listed for at least
1,000,000
SELECT E.Name
FROM Employee AS E, Manager AS M
WHERE E.EmpID M.EmpID
AND M.OfficeNbr IN
(SELECT OfficeNbr
FROM Property
WHERE Price 1000000)

66
Subquery Example

List the names of all managers whose offices have
at least 1 property listed for at least
1,000,000
SELECT E.Name
FROM Employee AS E, Manager AS M
WHERE E.EmpID M.EmpID
AND EXISTS
(SELECT
FROM Property as P
WHERE P.OfficeNbr M.OfficeNbr
AND Price 1000000)

67
Subquery Example

List the names of all managers whose offices have
at least 1 property listed for at least
1,000,000
SELECT E.Name
FROM Employee AS E, Manager AS M
WHERE E.EmpID M.EmpID
AND NOT UNIQUE
(SELECT
FROM Property as P
WHERE P.OfficeNbr M.OfficeNbr
AND Price 1000000)
Whats wrong with this query?

68
Subquery Example

List the names of all managers whose offices only
have one listing whose price is at least
1,000,000

69
Subquery Example

List the names of all managers whose offices only
have one listing whose price is at least
1,000,000
SELECT E.Name
FROM Employee AS E, Manager AS M
WHERE E.EmpID M.EmpID
AND UNIQUE
(SELECT P.OfficeNbr
FROM Property as P
WHERE P.OfficeNbr M.OfficeNbr
AND P.Price 1000000)

70
Subqueries