3.1 Database.pdf

12/31/23, 4:54 PM zyBooks column constraint statement. The database automatically rejects insert, update, and delete statements that violate a constraint. A column constraint appears after the column name and data type in a CREATE TABLE statement. Column constraints govern values in a single colu...

12/31/23, 4:54 PM zyBooks column constraint statement. The database automatically rejects insert, update, and delete statements that violate a constraint. A column constraint appears after the column name and data type in a CREATE TABLE statement. Column constraints govern values in a single column. table constraint A table constraint appears in a separate clause of a CREATE TABLE statement and governs values in one or more columns. UNIQUE The UNIQUE constraint ensures that values in a column, or group of columns, are unique. CHECK The CHECK constraint specifies an expression on one or more columns of a table. The constraint is violated when the expression is FALSE and satisfied when the expression is either TRUE or NULL. CONSTRAINT Table constraints may be named using the optional CONSTRAINT keyword, followed by the constraint name and declaration. Progression 2.12.1 Constraints. 3. Complex Queries 3.1 Special operators and clauses IN The IN operator is used in a WHERE clause to determine if a value matches one of several values. BETWEEN The BETWEEN operator provides an alternative way to determine if a value is between two other values. The operator is written value BETWEEN minValue AND maxValue and is equivalent to value >= minValue AND value <= maxValue. LIKE The LIKE operator, when used in a WHERE clause, matches text against a pattern using the two wildcard characters % and _. BINARY The LIKE operator performs case-insensitive pattern matching by default or case-sensitive pattern matching if followed by the BINARY keyword. DISTINCT The DISTINCT clause is used with a SELECT statement to return only unique or 'distinct' values. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 13/39 12/31/23, 4:54 PM zyBooks ORDER BY The ORDER BY clause orders selected rows by one or more columns in ascending (alphabetic or increasing) order. DESC The DESC keyword with the ORDER BY clause orders rows in descending order. Progression 3.1.1 Special operators and clauses. 3.2 Simple functions function / argument Progression A function operates on an expression enclosed in parentheses, called an argument, and returns a value. Usually, the argument is a simple expression, such as a column name or fixed value. Some functions have several arguments, separated by commas, and a few have no arguments at all. 3.2.1 Simple functions. 3.3 Aggregate functions aggregate function An aggregate function processes values from a set of rows and returns a summary value. COUNT() COUNT() counts the number of rows in the set. MIN() MIN() finds the minimum value in the set. MAX() MAX() finds the maximum value in the set. SUM() SUM() sums all the values in the set. AVG() AVG() computes the arithmetic mean of all the values in the set. GROUP BY The GROUP BY clause consists of the GROUP BY keyword and one or more columns. Each simple or composite value of the column(s) becomes a group. The query computes the aggregate function separately, and returns one row, for each group. HAVING The HAVING clause is used with the GROUP BY clause to filter group results. Progression 3.3.1 Aggregate functions. 3.4 Join queries https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 14/39 12/31/23, 4:54 PM zyBooks join / left table / right table A join is a SELECT statement that combines data from two tables, known as the left table and right table, into a single result. AS To simplify queries or result tables, a column name can be replaced with an alias. The alias follows the column name, separated by an optional AS keyword. join clause A join clause determines how a join query handles unmatched rows. Two common join clauses are: . INNER JOIN INNER JOIN selects only matching left and right table rows. FULL JOIN FULL JOIN selects all left and right table rows, regardless of match. ON The ON clause specifies the join columns. LEFT JOIN LEFT JOIN selects all left table rows, but only matching right table rows. RIGHT JOIN RIGHT JOIN selects all right table rows, but only matching left table rows. outer join An outer join is any join that selects unmatched rows, including left, right, and full joins. UNION The UNION keyword combines the two results into one table. Progression 3.4.1 Inner and outer joins. 3.5 Equijoins, self-joins, and cross-joins equijoin An equijoin compares columns of two tables with the = operator. non-equijoin A non-equijoin compares columns with an operator other than =, such as < and >. self-join A self-join joins a table to itself. cross-join A cross-join combines two tables without comparing columns. CROSS JOIN A cross-join uses a CROSS JOIN clause without an ON clause. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 15/39 12/31/23, 4:54 PM zyBooks Progression 3.5.1 Equijoins, self-joins, and cross-joins. 3.6 Subqueries subquery / nested query / inner query A subquery, sometimes called a nested query or inner query, is a query within another SQL query. correlated A subquery is correlated when the subquery's WHERE clause references a column from the outer query. alias An alias is a temporary name assigned to a column or table. AS The AS keyword follows a column or table name to create an alias. EXISTS Correlated subqueries commonly use the EXISTS operator, which returns TRUE if a subquery selects at least one row and FALSE if no rows are selected. NOT EXISTS The NOT EXISTS operator returns TRUE if a subquery selects no rows and FALSE if at least one row is selected. flattening Replacing a subquery with an equivalent join is called flattening a query. Progression 3.6.1 Subqueries. 3.7 Complex query example Progression 3.7.1 Complex queries. 3.8 View tables view table / view query A view table is a table name associated with a SELECT statement, called the view query. CREATE VIEW The CREATE VIEW statement creates a view table and specifies the view name, query, and, optionally, column names. If column names are not specified, column names are the same as in the view query result table. base table A table specified in the view query's FROM clause is called a base table. materialized view A materialized view is a view for which data is stored at all times. https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 16/39 12/31/23, 4:54 PM zyBooks WITH CHECK OPTION Progression When WITH CHECK OPTION is specified, the database rejects inserts and updates that do not satisfy the view query WHERE clause. 3.8.1 View tables. 3.9 Relational algebra relational algebra In his original paper on the relational model, E. F. Codd introduced formal operations for manipulating tables. Codd's operations, called relational algebra, have since been refined and are the theoretical foundation of SQL. select operation The select operation selects table rows based on a logical expression. The select operation is written as and is equivalent to SELECT * FROM Table WHERE expression. project operation The project operation selects table columns. The project operation is written as and is equivalent to SELECT Column1, Column2, ... FROM Table. product operation The product operation combines two tables into one result. The result includes all columns and all combinations of rows from both tables. The product operation is written as and is equivalent to SELECT * FROM Table1 CROSS JOIN Table2. join The join operation, denoted with a "bowtie" symbol, is written as and is identical to a product followed by a select: https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 17/39 12/31/23, 4:54 PM theta join zyBooks The join operation is equivalent to SELECT * FROM Table1 INNER JOIN Table2 ON expressi Because of theta notation, the join operation is sometimes called a theta join. Compatible tables Compatible tables have the same number of columns with the same data types. Column names may be different. set operations Union, intersect, and difference operate on compatible tables and, collectively, are called set operations. union The union operation combines all rows of two compatible tables into a single table. Duplicate rows are excluded from the result table. The union operation is written as and is equivalent to SELECT * FROM Table1 UNION SELECT * FROM Table2. Intersect Intersect operates on two compatible tables and returns only rows th appear in both tables. The intersect operation is written as and is equivalent to SELECT * FROM Table1 INTERSECT SELECT * FROM Table difference The difference operation removes from a table all rows that appear in a second compatible table. The difference operation is written as and is equivalent to SELECT * FROM Table1 MINUS SELECT * FROM Table2. rename operation The rename operation specifies new table and column names. The rename operation is written as . aggregate operation The aggregate operation applies aggregate functions like SUM(), AVG aggregate operation is written as https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 18/39 12/31/23, 4:54 PM zyBooks equivalent and is equivalent to SELECT GroupColumn, Function(Column) FROM Table GR Relational algebra expressions are equivalent if the expressions operate on the same tables and generate the same result. query optimizer / query execution plan A query optimizer converts an SQL query into a sequence of lowlevel database actions, called the query execution plan. The query execution plan specifies precisely how to process an SQL statement. cost The cost of an operation is a numeric estimate of processing time. The cost estimate usually combines both storage media access and computation time in a single measure. Progression 3.9.1 Relational algebra. 4. Database Design 4.1 Entities, relationships, and attributes entity-relationship model An entity-relationship model is a high-level representation of data requirements, ignoring implementation details. entity An entity is a person, place, product, concept, or activity. relationship A relationship is a statement about two entities. attribute An attribute is a descript­ive property of an entity. reflexive relationship A reflexive relationship relates an entity to itself. entity-relationship diagram / ER diagram An entity-relationship diagram, commonly called an ER diagram, is a schematic picture of entities, relationships, and attributes. glossary / data dictionary / repository A glossary, also known as a data dictionary or repository, documents additional detail in text format. entity type An entity type is a set of things. Ex: All employees in a company. relationship type A relationship type is a set of related things. Ex: EmployeeManages-Department is a set of (employee, department) pairs, https://learn.zybooks.com/zybook/WGUD426v2/content-explorer/print 19/39