490 likes | 604 Views
Information Resources Management. March 6, 2001. Agenda. Administrivia SQL Part 2 Homework #6. Administrivia. Mid-term Exam Homework #4 Homework #5. SQL Structured Query Language. The standard relational database language Two Parts DDL - Data Definition Language
E N D
Information Resources Management March 6, 2001
Agenda • Administrivia • SQL Part 2 • Homework #6
Administrivia • Mid-term Exam • Homework #4 • Homework #5
SQLStructured Query Language • The standard relational database language • Two Parts • DDL - Data Definition Language • DML - Data Manipulation Language
SQL - DDL • Data Definition: Define schemas, delete relations, create indices, modify schemas • View Definition • Authorization • Integrity
SQL - DML • Insert, Modify, Delete Tuples • Interactive • Embedded • Transaction Control
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)
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)
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
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
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
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
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
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
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
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
SQL - DML • INSERT • UPDATE • DELETE
INSERT INSERT INTO table VALUES (………) INSERT INTO table(attributes) VALUES (………)
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
UPDATE UPDATE table SET attribute = value or calculation UPDATE table SET attribute = value or calculation WHERE conditions
UPDATE Example • Increase the prices of all properties by 5% UPDATE Property SET Price = Price * 1.05
UPDATE Example • Increase the prices of all properties in St. Paul, MN by 7.5%
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’
UPDATE Example • Change the zip code of all offices in 15214 to 15217-0173
UPDATE Example • Change the zip code of all offices in 15214 to 15217-0173 UPDATE Office SET Zip = ‘15217-0173’ WHERE Zip LIKE ‘15214%’
DELETE DELETE FROM table DELETE FROM table WHERE conditions
DELETE Example • Delete everything from the gift table DELETE FROM Gift
DELETE Example • Delete all employees who do not have access to a PC
DELETE Example • Delete all employees who do not have access to a PC DELETE FROM Employee WHERE EmpID NOT IN (SELECT EmpID FROM PCAccess)
SQL - DDL • CREATE TABLE • DROP TABLE • ALTER TABLE
CREATE TABLE CREATE TABLE name (attribute defn <constraint>, attribute defn <constraint>, … <integrity constraints>)
CREATE TABLE CREATE TABLE name (attribute defn <constraint>, attribute defn <constraint>, … <integrity constraints>) • Attribute Definitions - Table 9-1, p. 329 • Attribute Constraints • NOT NULL • UNIQUE
CREATE TABLE CREATE TABLE name (attribute defn <constraint>, attribute defn <constraint>, … <integrity constraints>) • Integrity Constraints • PRIMARY KEY (attribute, …) • FOREIGN KEY (attribute,…) REFERENCES (table name)
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))
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))
CREATE TABLE Example • Create the MgrPCAccess table -- access type is required
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))
DROP TABLE • DROP TABLE name • DROP TABLE Office • DROP vs. DELETE
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
Other SQL DDL • CREATE INDEX • DROP INDEX • CREATE VIEW • DROP VIEW • CREATE SCHEMA
Multiple Tables - JOINs • FROM multiple tables WHERE cond • “INNER” join • Equi-join & Natural join variations • What if second table is “optionally” included? • “OUTER” join
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?
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)
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)
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
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)
Other Relational Languages • Chapter 10 of book • Query-by Example (QBE) • Access
In-Class Exercise • SQL • All 21 queries
Homework #6 • Do remaining 5 from HW #5 • Keep problem numbers