CSCE 315 Lecture 6
Jump to navigation
Jump to search
« previous | Monday, January 30, 2012 | next »
SQL Queries
Insertion
INSERT INTO <Relation> (<Schema (optional)>) VALUES (<Values>);
INSERT INTO Senator VALUES (Jill Smitth, Republican, NY, 5);
INSERT INTO Senator(Name, State) VALUES (Jill Smith, NY);Modification
UPDATE <Relation> SET <Attribute> = '<Value>' WHERE <Condition>;
UPDATE Senator SET Party = 'Independent' WHERE Name = 'Joseph Lieberman';Deletion
DELETE FROM <Relation> WHERE <Condition>;
DELETE FROM Senator WHERE State = 'TX';Retrieval
SELECT <Attribute> FROM <Relation> WHERE <Condition>;Aggregation
SUM, MAX, MIN, COUNT(*), GROUP BY...
SELECT State, COUNT(*) FROM USRepresentatives GROUP BY State HAVING MIN(Years) > 3
Conditions
More complicated boolean expressions: AND, OR, NOT
NULL values treated as "unknown":
- TRUE = 1
- FALSE = 0
- UNKNOWN = 1/2
Operations
- OR = MAX
- AND = MIN
- NOT = 1-x
Inclusions
<tuple> IN <Relation>TRUE iff exact tuple is in relation
EXISTS(<Relation>)TRUE iff the relation is not empty (usually applied to results of a subquery):
EXISTS(
SELECT * FROM Senator WHERE Party = 'WHIG'
)
Quantifications
x = ANY(Relation);
x = ALL(Relation);
Combining Results
(subquery) UNION (subquery)
(subquery) INTERSECT (subquery)
(subquery) DIFFERENCE (subquery)use ALL after operation to include duplicates. they can be removed again using the DISTINCT operator later.
Joins
- CROSS JOIN: every combination from two relations
- INNER JOIN: based on cross join, but limited by using ON
- (JOIN: natural join?)
- OUTER JOIN: include tuples from both relations, even if no match is in other (sets those attributes to NULL)
- LEFT: keep from left table
- RIGHT: keep from right table
- FULL: keep from both