referential integrity ensures that data is consistent between tables. Specifically, referential constraints ensure that a foreign key value in a child table (or child row in the case of a recursive constraint) always has a matching primary key value in the parent table (or parent row). In a physical design, a foreign key is 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). Primary keys and foreign keys are the fundamental building blocks of the relational database because these are the components that make the relationships work while allowing tables to be accessed independently. One-to-many relationships between primary keys and foreign keys are thus defined as referential constraints in the physical database.
28 Matching Annotations
- Jan 2023
-
onlinelearning.berkeley.edu onlinelearning.berkeley.edu
-
-
TRUNCATE Statement
- 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
DROP STATEMENT - removes any object from database, including data it contains
-
CREATE INDEX Statement
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.
-
Primary keys and foreign keys are the fundamental building blocks of the relational database because these are the components that make the relationships work while allowing tables to be accessed independently. One-to-many relationships between primary keys and foreign keys are thus defined as referential constraints in the physical database
-
foreign key is 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)
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)
-
primary key is a column or set of columns that uniquely identifies each row in a table.
-
ALTER TABLE Statement
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
-
CREATE TABLE Statement
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
-
string, numeric, datetime, and Boolean.
data types = STRING, NUMERIC,DATETIME, BOOLEAN
-
Whenever you define a column in a table, you must specify a data type. The data type restricts the values that can be placed in the column and it provides metadata to the DBMS that helps it efficiently process and display the column data
-
Data Definition Language (DDL)
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.
-
hat you need is to filter on the count of the number of races for each horse. The HAVING clause does exactly what we need here – it has syntax like the WHERE clause, but it is applied to each group after grouping has taken place
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
-
-
SELECT HORSE_ID, SUM(PURSE), COUNT(*) AS NUM_RACES FROM RACE_RESULT GROUP BY HORSE_ID;
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)
-
SUM(PURSE), COUNT(*) AS NUM_RACES
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
-
GROUP BY clause directs the DBMS to form groups of rows for each value of the column(s) named in the clause, returning one row in the result set for each group
-
COUNT function provides a count of the rows in a result set. It has two forms. COUNT(*) counts rows regardless of data values in the rows, while COUNT(column_name) counts only non-null values in the named colum
- COUNT(*) - counts rows regardless of data values in the rows
- COUNT - (column_name) counts only non-null values in the named colum
-
the DBMS assigns a column name for the result based on its own internal rules. You can always provide your own column name by assigning an alias right after the expression that forms the column
-
As in mathematics, an SQL function returns a single value each time it is executed
see above?
-
An inner join is best thought of as an exclusive join because the result set includes only rows where matches were found in both tables (unmatched rows are excluded from the results)
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.
-
An outer join, on the other hand, can be thought of as an inclusive join because unmatched rows from either side of the join (the left side or the right side) can be included
TLDR outer join = inclusive join that can contained rows that dont match to the key column?
-
We used an inner join in the previous query, so non-matched rows are discarded
inner joins I think match two rows based on a common column
-
SIRE_HORSE_ID
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)
Recursive join = joining rows in a table to other rows in the same table.
-
However, when we place the JOIN keyword between the tables in the FROM clause, the predicate needs to be preceded by the ON keyword.
ON keyword needed before equivilancy statement when join keyword used
-
FROM HORSE A, OWNER B WHERE A.OWNER_ID = B.OWNER_ID;
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 HORSE.OWNER_ID = OWNER.OWNER_ID;
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
-
Cartesian product
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)
Tags
- count(*)
- Why primary & foreign keys are important
- referential_contstraints
- keys
- conceptual_definition
- inner_join
- create_table
- recursive_join
- data_langauges
- index explanation
- indexes
- data_types
- foreign_key
- table_joining
- probably_quiz_question
- filtering
- primary_key
- matching
- explanation
- important_detail
- join
- joining
- primary&foreign_keys
- truncate
- SQL_code_explained
- HAVING
- long_explanation
- clause
- sql_function
- data_types_list
- count
- matching_logic
- referential_integrity
- create_index
- explained_count()
- WHERE_clause
- explained_count(*)
- outer_join
- cartesian_product
- count_function
- definition
- sql_rules
- alias
- statements
- group_by
- summarize
- functions
- column_name
- DROP
Annotators
URL
-