CIT 320 - Database

COMMENT

-- Lets you add a comment about a table, or a comment about a column in a specific table.

Union All

A ________ takes all rows returned by one query and adds the rows returned by another query. It does not filter out duplicate rows. It is most useful when both queries return unique sets because it avoids a sorting operation. Like the other set operators, the UNION requires that both queries return the same, position, number of columns, and data types.

Self Join

A _________ can be an INNER, LEFT, RIGHT, or FULL JOIN. A table supports a self join when it contains a PRIMARY KEY and FOREIGN KEY that points to it's own PRIMARY KEY. This means you can join a row in the table to the same row or another row in the table, and the join is wholly dependent on the matching of values in the PRIMARY and FOREIGN KEY column(s).

Minus

A __________ operates like the INTERSECT operator in that both queries must share select lists that match by position, number of columns, and data types. The difference is that the any rows from the first query found in the second query are removed from the result set.

SAVEPOINT

A _____________ lets you name a point inside a transaction, and effectively lets you break a transaction into subtransactions.

Natural Join

A commonly used join operator where the matching condition is equality, one of the matching columns is discarded in the result table, and the join columns have the same unqualified names.

Left (Outer) Join

A join that tells the query to return all the rows from the first table and only the rows from the second table that match the first table AKA right complement

Right (Outer) Join

A join that tells the query to return all the rows from the second table and only the rows from the first table that match the second table AKA left complment

Many-to-many

A non-specific relationship between two tables, where one row in one table may map to one to many rows in the other and vice versa. You map these two tables by using a third table that holds a foreign key from both in the same row. The third table is known as an association or translation table. Both of the original tables have a one to many relationship to the association table, and both relationships resolve through the association table.

One-to-one

A specific relationship between two table, where one row in one table maps to one and only one row in another table. You map these two tables by using the primary key of one table as a foreign key in the other. This makes the table that holds the foreign key functionally dependent on the primary key in the other table. While a one-to-one relationship allows you to choose either as the independent row, it is important that you identify the business relationship of the two tables and make the primary task element the independent row. The independent row donates a copy of its primary key to the dependent row.

One-to-many

A specific relationship between two table, where one row in one table maps to one to many rows in another table. You map these two tables by using the primary key of one table as a foreign key in the other. This makes the table that holds the foreign key functionally dependent on the primary key in the other table. The one side of the relationship is always the independent row, and it always donates a copy of its primary key to the dependent row.

SCALAR

A subquery is a SELECT statement that returns one and only one row of an atomic data type

Intersect

An ___________ operates on two queries that return select lists that share the exact position, number of columns, and data types. An __________ operator returns the rows from both queries that share the same values in all of the select list columns.

Data Definition Language (DDL)

CREATE, ALTER, DROP, RENAME, COMMENT, and TRUNCATE statements. (DDL)

Transaction Control Language (TCL)

Consists of COMMIT, ROLLBACK, and SAVEPOINT. They let you group collections of DML statements into cohesive units known as transactions. (TCL)

Data Control Language (DCL)

Consists of GRANT and REVOKE. These statements let you manage permissions to access and control database resources. (DCL)

Data Manipulation Language (DML)

Consists of SELECT, INSERT, UPDATE, DELETE, AND MERGE statements. (DML)

CREATE

Creates an object, which can be a table, view, or other structure supported by the DBMS implementation

correlated subquery

In SQL, a subquery in which processing the inner query depends on data from the outer query

INSERT

Inputs data into tables or objects.

Union

It returns the unique set of rows found in the collection of both queries. It calculates the distinct set of rows by performing a sort operation that eliminates duplicates. Like the other set operators, the ________ requires that both queries return the same, position, number of columns, and data types.

SELECT

Queries data

REVOKE

Removes a grant made previously.

RENAME

Renames a structure, which can be a table, view, or subcomponent like a column name or data type of a table.

ROLLBACK

Rolls back transactions made but uncommitted. You have three options with a __________ statement. You can roll back to the last COMMIT statement or a named SAVEPOINT statement, or you can roll back to the point where you connected to the system with a pessimistic connection (Internet socket).

COMMIT

The ________ makes permanent any changes that an individual makes while using DML commands. Individual changes before a COMMIT; are visible to the individual making them but aren't visible to others working in the database.

UPDATE

Updates data in tables or objects.

structured query language

What does SQL stand for?

multiple character wildcard

What type of wildcard uses a % (percent)

single character wildcard

What type of wildcard uses a _ (underscore)

Full Join

Will return every row from both tables including mismatched records.

TRUNCATE

________ deletes data like the DELETE statement with a twist. It preserves the structure of the table but deletes the contents without logging the deletes for recovery. Implementations vary between products, and Oracle's flashback technology does provide for recovery of truncated objects.

Cross Join

a special type of SQL join, that returns the same result as the cartesian product of two sets or tables.

second normal form

exists when a table is already in first normal form and all non-key columns depend on all of the key columns, where the list of key columns goes from 1 to n. The list of key columns makes up the natural key of a table, or the list of columns that makes any row unique in a table.

third normal form

exists when a table is already in second normal form and there are no transitive dependencies. A transitive dependency means a non-key column or set of columns are dependent on another column that generally isn't part of the natural key.

first normal form

exists when all columns, or attributes, of a table have a single data type and there are no repeating rows of data, which means rows are unique.

GRANT

gives permission to user to access, transact against, or run an object in the database. In some implementations, like Oracle, you can package grants into sets known as roles, and grant roles. This minimizes the work in granting access but comes with some restrictions, which change from release to release.

multiple-row subquery

may return one to many columns and many rows aka "subquery"

Unnormalized Normal Form

means that a table contains one or more repeating groups. It is probably important to note that a table may be in UNF and HNF at the same time. Likewise, UNF and HNF are equivalent to zero normal form.

ALTER

modify the structure of an object, which can be a table, view, or other structure supported by the DBMS implementation.

DROP

releases a structure from the database, which may be constrained when there are other objects with a dependency on the target object

DELETE

removes data from tables or objects.

Inner Join

the default join that tells a query to combine rows from two tables that share a common value.

Non-equijoin

they are always a filtered outcome of a cross join. they use an equality or a range operator. Rule(s): 1. A non-equijoin doesn't have an equijoin statement. 2. A non-equijoin that uses the CROSS JOIN doesn't have a join in the FROM clause. 3. A non-equijoin that uses the INNER JOIN supports resolution through the ON clause. 4. A non-equijoin that uses a comma separated list of tables doesn't have an equijoin in the WHERE clause.

MERGE

(Oracle) Inserts or updates data based on conditions within the statement into tables or objects. It is part of the ANSI SQL:2003 standard. (MySQL) ON DUPLICATE KEY