Please enable JavaScript.

Coggle requires JavaScript to display documents.

Zybooks 3 - Coggle Diagram

- - - - A table may contain sensitive data like salary.
      - A view can exclude sensitive columns by giving certain people access
    - - Complex SELECT statements ca be saved as a view.
      - You can reference the view without writing the SELECT statement
    - - Often, the same result table can be generated with equivalent SELECT statements.
      - Although the results of equivalent statements are the same, performance may vary.
      - To ensure fast execution, the optimal statement can be saved as a view and distributed to database users
  - - - Primary keys
        
        If a base table primary key does not appear in a view, an insert to the view generates a NULL primary key value. Since primary keys may not be NULL, the insert is not allowed.
      - Aggretated values
        
        A view query may contain aggregate functions such as AVG() or SUM(). One aggregate value corresponds to many base table values.
        
        An update or insert to the view may create a new aggregate value, which must be converted to many base table values
        
        The conversion is undefined, so the insert or update is not allowed
      - Join views
        
        In a join view, foreign keys of one base table may match primary keys of another.
        
        A delete from a view might delete foreign key rows only, or primary key rows only, or both the primary and foreign key rows.
        
        The effect of join view delete is undefined and therefore not allowed.
- - - - this is called a prefix
      - makes it more complex
      - us AS to rename it so its more simple
  - - - INNER JOIN
        
        selects only matching left and right table rows
      - FULL JOIN
        
        selects all left and right table rows, regardless of match
        
        unmatched left table rows appear with NULL values in right table columns. and vice versa
        
        not supported by MySQL?
      - JOIN clause appears between a FROM clause and an ON clause
        
        FROM clause specifies the left table
        
        the INNER JOIN or FULL JOIN clause specifies the right table
        
        The ON clause specifies the join columns
      - The rules of this can be tricky
      - Left and Right Joins
        
        LEFT JOIN
        
        selects all left table rows, but only matching right table rows
        
        RIGHT JOIN
        
        selects all right table rows, but only matching left table rows
        
        outer join
        
        any join that selects unmatched rows, including left, right and full joins
  - - - Combines two results into one table
- - - - Have same number of columns with same data types
      - Column names may be different.
      - Union, intersect, and difference operate on compatible tables and, collectively, are called set operations
    - - Combines all rows of two compatible tables into a single table.
      - Duplicate rows are excluded from the result table.
      - Equivalent to SELECT FROM Table1 UNION SELECT FROM Table2
    - - Operates on two compatible tables and returns only rows that appear in both tables
      - SELECT FROM Table1 INTERSECT SELECT FROM Table2
    - - Removes from a table all rows that appear in a second compatible table.
      - Difference operation is written as Table1 - Table2
      - SELECT FROM Table1 MINUS SELECT FROM Table2
    - - Doesn't support all these, you have to do it in a special workaround way
  - - - applies aggregate functions like SUM(), AVG(), MIN(), and MAX()
      - written in a weird way before the table in parens
  - - - if they generate the same results
    - - converts an SQL query into a sequence of low-level database actions, called the query execution Plan
      - Similar to a compiler
      - Wudzit do?
        
        Converts a query to relational algebra expression
        
        Generates equivalent expressions
        
        Estimates the 'cost' of each operation of each expression
        
        Determines the optimal expression with the lowest total cost
        
        Converts the optimal expression into a query execution plan
        
        Cost?
        
        numerical estimate of processing time.
        
        usually combines both storage media access and computation time into a single measure.
- - - - % - any number of characters
      - _ - one character
- - - - retain outer query
      - Add INNER JOIN clauses for each subquery table
      - Move comparisons between subquery and outer query columns to ON clauses
      - Add a WHERE clause with the remaining expressions in the subquery and outer query WHERE clauses
      - If necessary, remove duplicate rows with SELECT DISTINCT
- - - - These are weird. SO instead of equals, you use an inequality
      - The side that has preference Matches with any column where to condition is true. So, yeah, you can make a bunch of them