• The TRUNCATE statement is a fast and efficient way to empty a table of all rows. Unlike the DELETE statement that you will see shortly, TRUNCATE wipes the entire table without going through the table row by row

DROP STATEMENT - removes any object from database, including data it contains

ABOUT INDEXES *indexes can make queries faster, much like looking for a term in the index of a textbook instead of skimming through all the pages in the book to find all the references to the term. However, indexes take storage space and the DBMS must maintain the index as rows are inserted, updated and deleted in the table.

foreign key = the combination of one or more columns in a table that reference (match) the primary key in the parent table (or parent row in the case of recursive relationships)

ASPECTS CHANGED BY ALTER TABLE STATEMENT * Adding columns to the table * Removing columns from the table * Altering the data type for existing table columns * Changing physical storage properties of the table * Adding, removing, or altering constraints

STATEMENT FORMAT OVERVIEW * The statement starts with the keywords CREATE TABLE, followed by the name of the table, which must be unique within the database or schema. The remainder of the statement consists of a comma-separated lists of column definitions, enclosed in a pair of parentheses. Each column definition consists of the name of the column, which must be unique within the table, followed by a data type for the column and other property

data types = STRING, NUMERIC,DATETIME, BOOLEAN

DDL is used to create database objects (tables, views, indexes, and so forth). Once objects are setup, they can be modified using the ALTER statement, destroyed using the DROP statement, and tables can be instantly cleared of contents using the special-purpose TRUNCATE statement.

Main diff between HAVING and WHERE

• • WHERE is applied before grouping, it is usually used to narrow the SELECT statement's output
• • HAVING is applied after grouping. It is usually used when grouping is needed to incorporate function generated values into a new table requested by the query

okay what i'm missing here is that the race_result table I think groups rows by race, not by horse. So each horse can have multiple races.

The above statements asks the dbms to find each race (row) with a specific horse_id, and then average certain column values from all the rows corresponding to the same horse_id. The average is then to be output as a new row containing a single horse id, the sum of that horses earnings across all race rows, the COUNT(*) statement is then used to count how many rows in the horse table had a specific horse id.So really the query is requesting a table with

horse_id | (earnings sum from race_result rows with horse_id) | (number of race result rows featuring horse_id)

IMPORTANT!! : The group by clause is needed here because unlike in previous queries, we aren't simply taking a value in a prexisting table and adding it to a smaller table using the SELECT clause. We are using functions to create entirely new values. These values dont exist as part of the table, they kinda just float. Because these are floating unindexed values, the group by clause is needed to indicate how these new values should be presented in the output table for the query

• COUNT(*) - counts rows regardless of data values in the rows
• COUNT - (column_name) counts only non-null values in the named colum

see above?

TLDR inner join = exclusive to matches in both tables * Any row from the key table which has a value that is omitted from the key column (Horse table has key columns of sire_id and dam_id) is exlcuded from the final result. So if horse_id_x has a null value for its sire or dam id, then any information about the row corresponding to horse_id_X is entirely ommitted from the final result of the joined table.

TLDR outer join = inclusive join that can contained rows that dont match to the key column?

inner joins I think match two rows based on a common column

ohhh I get it. I thought all the sire and dam information was in each row for every horse, but I think all that is included on each horse row is the ids for their mom and dad. So if u wanted to get the horses parents names you would need to join name info from the id column for the parents with the info from the offspring column. The parent id would be used as the matching key.

Recursive join = joining rows in a table to other rows in the same table.

ON keyword needed before equivilancy statement when join keyword used

This process uses the FROM clause to create an alias for the horse/owner tables as A and B respectively. The format is simply

FROM TableA Alias1, TableB Alias2

Where clauses can be used to specify join matching logic. A where clause can be used to indicate that a column in table A is equal to a column in table B, and that equivilancy can be used to join table A rows to table B rows. THe format is as simple as

TableA.MatchingColumn = TableB.MatchingColumn

Where every row from table a is joined with each row of table b ( totalling A*B rows), Cartesian products arise when tables are merged with no matching logic (ie a shared column like owner_id which can distinguish how each row in table A should be joined with each row of table B)