Study Guides (238,072)
CSC343H5 (1)
Ivan Chow (1)
Midterm

# Midterm 1 Notes - Lectures 1 to 5

2 Pages
235 Views

School
University of Toronto Mississauga
Department
Computer Science
Course
CSC343H5
Professor
Ivan Chow
Semester
Fall

Description
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 ﬁltered 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

OR

Don't have an account?

Join OneClass

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

Join to view

OR

By registering, I agree to the Terms and Privacy Policies
Just a few more details

So we can recommend you notes for your school.