Information Resources Management

1
Information Resources Management

• March 6, 2001

2
Agenda

• Administrivia
• SQL Part 2
• Homework 6

3
Administrivia

• Mid-term Exam
• Homework 4
• Homework 5

4
SQLStructured Query Language

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

5
SQL - DDL

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

6
SQL - DML

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

7
Evaluating SQL Statements

• SELECT y.a, AVG(x.b)
• FROM tableX as x, tableY as y
• WHERE x.c y.c AND
• x.b IN (SELECT d FROM tableZ as z
• WHERE z.a y.a)
• GROUP BY y.a
• HAVING COUNT (x.e) 5
• tableX (b, c, e) tableY(a, c) tableZ (a, d)

8
Evaluating SQL Statements

• SELECT y.a, AVG(x.b)
• FROM tableX as x, tableY as y
• WHERE x.c y.c AND
• x.b IN (SELECT d FROM tableZ as z
• WHERE z.a y.a)
• GROUP BY y.a
• HAVING COUNT (x.e) 5
• 1 - Join all tables (Cartesian product)

9
Evaluating SQL Statements

• SELECT y.a, AVG(x.b)
• FROM tableX as x, tableY as y
• WHERE x.c y.c AND
• x.b IN (SELECT d FROM tableZ as z
• WHERE z.a y.a)
• GROUP BY y.a
• HAVING COUNT (x.e) 5
• 2 - For each row, apply WHERE conditions

10
Evaluating SQL Statements

• SELECT y.a, AVG(x.b)
• FROM tableX as x, tableY as y
• WHERE x.c y.c AND
• x.b IN (SELECT d FROM tableZ as z
• WHERE z.a y.a)
• GROUP BY y.a
• HAVING COUNT (x.e) 5
• 3 - WHERE subquery, each row evaluated separately

11
Evaluating SQL Statements

• SELECT y.a, AVG(x.b)
• FROM tableX as x, tableY as y
• WHERE x.c y.c AND
• x.b IN (SELECT d FROM tableZ as z
• WHERE z.a y.a)
• GROUP BY y.a
• HAVING COUNT (x.e) 5
• 4 - GROUP BY (with HAVING) - Order remaining data
  (all rows) by groups

12
Evaluating SQL Statements

• SELECT y.a, AVG(x.b)
• FROM tableX as x, tableY as y
• WHERE x.c y.c AND
• x.b IN (SELECT d FROM tableZ as z
• WHERE z.a y.a)
• GROUP BY y.a
• HAVING COUNT (x.e) 5
• 5 - Apply HAVING to each group

13
Evaluating SQL Statements

• SELECT y.a, AVG(x.b)
• FROM tableX as x, tableY as y
• WHERE x.c y.c AND
• x.b IN (SELECT d FROM tableZ as z
• WHERE z.a y.a)
• GROUP BY y.a
• HAVING COUNT (x.e) ALL (SELECT AVG(d) from
  tableZ as Z)
  
• 5a - HAVING with standalone SELECT

14
Evaluating SQL Statements

• SELECT y.a, AVG(x.b)
• FROM tableX as x, tableY as y
• WHERE x.c y.c AND
• x.b IN (SELECT d FROM tableZ as z
• WHERE z.a y.a)
• GROUP BY y.a
• HAVING COUNT (x.e) 5
• 6 - Calculate aggregate functions for each
  (remaining) group

15
Evaluating SQL Statements

• SELECT y.a, AVG(x.b)
• FROM tableX as x, tableY as y
• WHERE x.c y.c AND
• x.b IN (SELECT d FROM tableZ as z
• WHERE z.a y.a)
• GROUP BY y.a
• HAVING COUNT (x.e) 5
• 7 - SELECT desired output columns

16
Evaluating SQL Statements

• SELECT y.a, AVG(x.b)
• FROM tableX as x, tableY as y
• WHERE x.c y.c AND
• x.b IN (SELECT d FROM tableZ as z
• WHERE z.a y.a)
• GROUP BY y.a
• HAVING COUNT (x.e) 5
• 8 - ORDER BY (sort) is processed last

17
SQL - DML

• INSERT
• UPDATE
• DELETE

18
INSERT

• INSERT INTO table
• VALUES ()
• INSERT INTO table(attributes)
• VALUES ()

19
INSERT Example

• Add an employee
• INSERT INTO Employee
• VALUES(123456789,John Smith,,212)
• INSERT INTO Employee(EmpID, Name, OfficeNBR)
• VALUES(123456789,John Smith,212)
• MgrFlag is NULL in both cases

20
UPDATE

• UPDATE table
• SET attribute value or calculation
• UPDATE table
• SET attribute value or calculation
• WHERE conditions

21
UPDATE Example

• Increase the prices of all properties by 5
• UPDATE Property
• SET Price Price 1.05

22
UPDATE Example

• Increase the prices of all properties in St.
  Paul, MN by 7.5

23
UPDATE Example

• Increase the prices of all properties in St.
  Paul, MN by 7.5
  
• UPDATE Property
• SET Price Price 1.075
• WHERE City St. Paul
• AND State MN

24
UPDATE Example

• Change the zip code of all offices in 15214 to
  15217-0173

25
UPDATE Example

• Change the zip code of all offices in 15214 to
  15217-0173
  
• UPDATE Office
• SET Zip 15217-0173
• WHERE Zip LIKE 15214

26
DELETE

• DELETE FROM table
• DELETE FROM table
• WHERE conditions

27
DELETE Example

• Delete everything from the gift table
• DELETE FROM Gift

28
DELETE Example

• Delete all employees who do not have access to a
  PC

29
DELETE Example

• Delete all employees who do not have access to a
  PC
  
• DELETE FROM Employee
• WHERE EmpID NOT IN
• (SELECT EmpID
• FROM PCAccess)

30
SQL - DDL

• CREATE TABLE
• DROP TABLE
• ALTER TABLE

31
CREATE TABLE

• CREATE TABLE name
• (attribute defn ,
• attribute defn ,
• )

32
CREATE TABLE

• CREATE TABLE name
• (attribute defn ,
• attribute defn ,
• )
• Attribute Definitions - Table 9-1, p. 329
• Attribute Constraints
• NOT NULL
• UNIQUE

33
CREATE TABLE

• CREATE TABLE name
• (attribute defn ,
• attribute defn ,
• )
• Integrity Constraints
• PRIMARY KEY (attribute, )
• FOREIGN KEY (attribute,) REFERENCES (table
  name)
  

34
CREATE TABLE Example

• Create the Office table
• CREATE TABLE Office
• (OfficeNbr INTEGER NOT NULL UNIQUE,
• Address VARCHAR(50),
• City VARCHAR(25),
• State CHAR(2),
• Zip CHAR(10),
• PhoneNbr CHAR(13),
• PRIMARY KEY (OfficeNbr))

35
CREATE TABLE Example

• Create the Manager table
• CREATE TABLE Manager
• (EmpID CHAR(9),
• OfficeNbr INTEGER,
• PRIMARY KEY (EmpID),
• FOREIGN KEY (EmpID) REFERENCES (Employee),
• FOREIGN KEY (OfficeNbr) REFERENCES (Office))

36
CREATE TABLE Example

• Create the MgrPCAccess table -- access type is
  required

37
CREATE TABLE Example

• Create the MgrPCAccess table -- access type is
  required
  
• CREATE TABLE MgrPCAccess
• (PC INTEGER,
• EmpID CHAR(9),
• AccessType CHAR(15) NOT NULL,
• PRIMARY KEY (PC, EmpID),
• FOREIGN KEY (EmpID) REFERENCES (Employee),
• FOREIGN KEY (PC) REFERENCES (PC))

38
DROP TABLE

• DROP TABLE name
• DROP TABLE Office
• DROP vs. DELETE

39
ALTER TABLE

• ALTER TABLE name ADD attributes
• ALTER TABLE name DROP attributes
• Add - existing tuples get NULLs
• Nulls must be allowed
• Drop - cannot drop the primary key

40
Other SQL DDL

• CREATE INDEX
• DROP INDEX
• CREATE VIEW
• DROP VIEW
• CREATE SCHEMA

41
Multiple Tables - JOINs

• FROM multiple tables WHERE cond
• INNER join
• Equi-join Natural join variations
• What if second table is optionally included?
• OUTER join

42
Outer Join

• List the address, city, and manager name (if any)
  of all offices.
  
• SELECT address, city, e.name
• FROM Office as O, Employee as E
• WHERE O.OfficeNbr E.OfficeNbr AND MgrFlag 1
• What happens to offices without a mgr?

43
Outer Join

• List the address, city, and manager name (if any)
  of all offices.
  
• SELECT address, city, e.name
• FROM Office as O, Employee as E
• WHERE O.OfficeNbr E.OfficeNbr AND MgrFlag 1
• UNION
• SELECT address, city,
• FROM Office as O
• WHERE O.OfficeNbr NOT IN (SELECT OfficeNbr FROM
  Employee WHERE MgrFlag 1)

44
Outer Join - Access

• List the address, city, and manager name (if any)
  of all offices.
  
• SELECT address, city, e.name
• FROM Office as O LEFT JOIN Employee as E
• WHERE O.OfficeNbr E.OfficeNbr AND MgrFlag 1
• LEFT JOIN - all rows for table on left included
  (RIGHT JOIN)

45
Outer Join - Oracle

• List the address, city, and manager name (if any)
  of all offices.
  
• SELECT address, city, e.name
• FROM Office as O, Employee as E
• WHERE O.OfficeNbr E.OfficeNbr () AND MgrFlag
  1
  
• () here LEFT JOIN
• WHERE O.OfficeNbr () E.OffficeNbr RIGHT JOIN

46
Outer Join

• Outer Joins are not SQL standard
• Not always available
• Not consistent
• Can always do the same query using standard SQL
  (UNION NOT IN)
  

47
Other Relational Languages

• Chapter 10 of book
• Query-by Example (QBE)
• Access

48
In-Class Exercise

• SQL
• All 21 queries

49
Homework 6

• Do remaining 5 from HW 5
• Keep problem numbers