Midterm 1 Notes - Lectures 1 to 5

2 Pages
Unlock Document

University of Toronto Mississauga
Computer Science
Ivan Chow

CSC343 - Intro To Databases! - Projection (Π) : Filter by column SELECT A,B FROM R )" - Selection (σ) : Filter rows according to equalitySELECT * FROM R WHERE A=3 ) " - Renaming (ρ) : Rename columns (SELECT * FROM R [AS] S) or (SELECT A [AS] B FR"M R) - Cartesian Product (R × S) : All possible combinations ( SELECT L FROM R,S )" - Union ( R ∪ S) : R, S needs to have the same columns" - Intersection (R ∩ S): R, S needs to have the same columns" - Difference (R − S): R, S needs to have the same columns" - Assignment R(A1,..,An) := S" - Natural Join (R⋈ S) : Needs to have at least one common column.( SELECT L FROM R NATURAL JOIN S )" - Theta Join (R ⋈ S) : Cartesian join, rows filtered by condition C. (SELECT L FROM R JOIN S ON C )" C - Set model vs Bags (Bags has duplicates, Sets don’t)" - Duplicate Elimination δ(R) : Removes duplicate rows ( SELECT DISTINCT A FROM R )" - Sorting (τa,b: Sorts by attributes by a, then b. “-“ makes descending. SELECT L FROM R ORDER BY A [ASC | DE)"] - Extended Projection Π a,b+→ x,→y(R) (SELECT A, B+C AS X, D AS Y FROM R )" - Outer Join ( R ⋈ [L | R]) - Dangling tuples are included with null(⊥). Left, and Right outer join includes dangling tuples from R, and S.) (SELECT L FROM R NATURAL [FULL | LEFT | RIGHT] OUTER JOIN S )" - Grouping γ (L) - Operators - SUM, AVG, MIN, MAX, COUNT. eg. γ a,AVG(b))" - Pattern Matching: ( SELECT * FROM R WHERE A [NOT] LIKE ‘hello’ ) Also, ‘_ello’ ‘%lo’ (single, multi char)" - Boolean Algebra: ( SELECT * FROM R WHERE A < 2 OR A >= 3 )" - Subquery (FROM) - SELECT * FROM flights NATURAL JOIN (SELECT aid, cap FROM planes WHERE cap > 100) AS R. - If a subquery is guaranteed to return 1 row and 1 col. Then subquery can be in WHERE x = (SUBQ) ;" - If S returns non-empty it’s trueSELECT L FROM R WHERE [NOT] EXISTS (SUBQ); - SELECT L FROM R WHERE E [NOT] IN (S); TRUE if and only if E is equal to one of the tuples returned by S. - SELECT L FROM R WHERE E [NOT] ANY (S) TRUE iff ∃▯∈▯ st ▯ ▯." - SELECT L FROM R WHERE E [NOT] ALL (S); TRUE iff ∀▯∈▯ st ▯ ▯." - Grouping ( SELECT L FROM R WHERE C1 GROUP BY A [HAVING C2]; ) " • WHERE -> GROUP BY -> HAVING. WHERE remove rows before grouping, HAVING remove groups after grouping." • Use DISTINCT to ignore duplicates eg. COUNT(DISTINCT A)." - InsertionINSERT INTO R(A ,1..,A ) nALUES (v ,…,1 ); On INSERT INTO R(A, B) (SELECT D
More Less

Related notes for CSC343H5

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.