720 likes | 948 Views
Information Resources Management. February 27, 2001. Agenda. Administrivia Exam SQL Part 1 Homework #5. Administrivia. Homework #4. Exam. Results Review. SQL Structured Query Language. The standard relational database language Two Parts DDL - Data Definition Language
E N D
Information Resources Management February 27, 2001
Agenda • Administrivia • Exam • SQL Part 1 • Homework #5
Administrivia • Homework #4
Exam • Results • Review
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 • Select, Insert, Modify, Delete Tuples • Interactive • Embedded • Transaction Control
SQL - DML • SELECT • Single table • Multiple tables • INSERT • UPDATE • DELETE
SELECT SELECT attributes FROM table(s) WHERE conditions Result is a relation/table
SELECT Example • List all information in the Office table SELECT * FROM Office
SELECT Example • List all offices in New York state SELECT * FROM Office WHERE State = ‘NY’
SELECT Example • List owners with at least 75% of some property SELECT * FROM PctOwned WHERE PctOwned >= 75
Select Example • List all offices in Springfield, IL
Select Example • List all offices in Springfield, IL SELECT * FROM Office WHERE City = ‘Springfield’ AND State = ‘IL’
Select Example • List all properties listed by office 100 or 150
Select Example • List all properties listed by office 100 or 150 SELECT * FROM Property WHERE OfficeNbr = 100 OR OfficeNbr = 150
Select Example • List the office number and phone number for all offices in New York
Select Example • List the office number and phone number for all offices in New York SELECT OfficeNbr, PhoneNbr FROM Office WHERE State = ‘NY’
String Operations - LIKE • % - match any substring (*) • __ - match any character (?) SELECT EmpID, Name FROM Employee WHERE Name LIKE ‘Tom%’
LIKE Examples • List all properties whose description includes a fireplace
LIKE Examples • List all properties whose description includes a fireplace SELECT * FROM Property WHERE Description LIKE ‘%fireplace%’
LIKE Examples • List all employees whose name is Rita and whose last name is 4 characters long
LIKE Examples • List all employees whose name is Rita and whose last name is 4 characters long SELECT * FROM Employee WHERE Name LIKE ‘Rita _ _ _ _’
LIKE Examples • List all employees whose name is Rita and whose last name is at least 4 characters long
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 _ _ _ _%’
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
Testing for Nulls • WHERE attribute IS NULL • WHERE attribute IS NOT NULL
Selecting Expressions • A mathematical expression can be selected instead of an attribute SELECT col1, col2*col3, col4+100 FROM table WHERE conditions
Expressions Example • Assuming 5% commission, give the address and potential commission for all Erie properties SELECT address, price*0.05 FROM property WHERE city=‘Erie’ AND state = ‘PA’
Selecting Expressions - 2 • Selected expressions can be given a name using AS SELECT col1, col2*col3 AS newname FROM table WHERE conditions
Expressions Example - 2 • Assuming 5% commission, give the address and potential commission for all Erie properties SELECT address, price*0.05 AS comm FROM property WHERE city=‘Erie’ AND state = ‘PA’
Renaming with AS • Can also use AS in FROM • SELECT * FROM somelongtbl AS A • rename table • useful with multiple tables
Eliminating Duplicates • SELECT DISTINCT … • Entire tuple must be the same to be eliminated as a duplicate
Specifying the Order • SELECT … ORDER BY attribute(s) • SELECT … ORDER BY attrib ASC, attrib DESC • attributes must be SELECTed
Order & Duplicate Example • List all office numbers for offices with employees; sort by office number SELECT DISTINCT OfficeNbr FROM Employee ORDER BY OfficeNbr
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
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
Set Operations • UNION () • INTERSECT () • EXCEPT (-) • Add ALL to keep duplicates
Aggregation Functions • AVG • MIN • MAX • SUM • COUNT
Aggregation Examples • How many employees work at office 200? SELECT COUNT(*) FROM Employee WHERE OfficeNbr = 200
Aggregation Examples • What is the average price of the listings for office 225?
Aggregation Examples • What is the average price of the listings for office 225? SELECT AVG(Price) FROM Property WHERE OfficeNbr = 225
GROUP BY & HAVING SELECT attributes, aggregation(attributes) FROM table WHERE conditions GROUP BY attributes HAVING condition • HAVING only applies to each group (optional)
GROUP BY Example • What is the average price of a listing for each office? SELECT OfficeNbr, AVG(Price) FROM Property GROUP BY OfficeNbr
GROUP BY Example • For offices with more than 100 listings, what is the total value of each office’s listings?
GROUP BY Example • For offices with more than 100 listings, what is the total value of each office’s listings? SELECT OfficeNbr, SUM(Price) FROM Property GROUP BY OfficeNbr HAVING COUNT(PropertyID) > 100
Cartesian Product Example • List the names of all employees who work in offices in PA SELECT name FROM Employee, Office WHERE state = ‘PA’