is331-quizlet-import

HAVING Clause

Filters groups after GROUP BY is applied; it can filter on aggregated results like COUNT or SUM.

LEFT JOIN

Returns all rows from the left table and matched rows from the right, with unmatched rows from the right as NULL.

WITH Clause (CTE)

Creates a temporary named result set that can be reused in the next query; acts like a named subquery.

COUNT(column) Function

Returns the number of non-NULL values in a column. COUNT(*) counts all rows including NULLs.

FOR XML AUTO

Converts a query result set into an XML document, with element names derived from table and column names.

UNION vs. UNION ALL

UNION removes duplicate rows; UNION ALL keeps all rows including duplicates and is faster.

ORDER BY Clause

Sorts results in descending order when using 'ORDER BY col DESC'; ASC is the default direction.

LIKE Operator '%10%'

Matches any string containing '10' anywhere since the % wildcard represents zero or more characters.

1NF Requirements

All attributes must be atomic and there cannot be repeating groups; each row must be unique.

Partial Dependency

Occurs when a non-key attribute depends on only part of a composite primary key, violating 2NF.

Moving from 2NF to 3NF

Transitive dependencies must be eliminated, where a non-key attribute determines another non-key attribute.

BCNF Violation

Occurs when a non-prime attribute determines part of a candidate key; every determinant must be a candidate key in BCNF.

Transitive Dependency Example

If StudentID determines ZipCode and ZipCode determines City, City is transitively dependent on StudentID.

Functional Dependency

Occurs when the value of one attribute uniquely determines another attribute's value, written as A → B.

PL/SQL Block Sections

Includes DECLARE (variables), BEGIN (logic/statements), and END; EXCEPTION is optional.

Guaranteed PL/SQL Loop Execution

A basic LOOP…END LOOP with EXIT WHEN condition checks after the first iteration.

Cursor in PL/SQL

Points to a SQL query result set and allows row-by-row processing by OPEN, FETCH, and CLOSE.

Variable Declaration in PL/SQL

Syntax: v_name VARCHAR2(50); in the DECLARE section, e.g., v_sal NUMBER(8,2).

%ROWTYPE in PL/SQL

Declares a variable with the same structure as an entire table row, e.g., v_emp employees%ROWTYPE.

Raising User-Defined Exception in PL/SQL

Use RAISE exception_name to transfer control to the EXCEPTION block.

ACID in Transactions

Stands for Atomicity, Consistency, Isolation, Durability; properties ensuring reliable transaction processing.

ROLLBACK Statement

Undoes all changes made since the last COMMIT or SAVEPOINT, reverting the database to its previous state.

READ COMMITTED Isolation Level

Prevents dirty reads but allows non-repeatable reads; reads only committed data.

Dirty Read Definition

Occurs when a transaction reads uncommitted changes made by another transaction.

Deadlock in Database

Occurs when two transactions each hold a lock the other needs and both wait indefinitely; DBMS resolves by killing one.

Star Schema in Data Warehousing

A central fact table surrounded by denormalized dimension tables, optimized for fast analytical queries.

Snowflake Schema vs. Star Schema

Dimension tables in the snowflake schema are further normalized into sub-dimension tables.

OLTP vs. OLAP

OLTP handles frequent short transactions; OLAP supports complex analytical queries on large historic data.

ETL Process

Extract, Transform, Load; a process for moving data from source systems into a data warehouse.

Fact Table in Data Warehouse

Contains numeric measures and foreign keys that reference dimension tables for context.

VIEW in SQL

A virtual table defined by a stored SELECT query that runs the underlying query each time it's accessed.

Benefits of Using a VIEW

Simplifies complex queries and restricts column-level access for security, hiding sensitive columns.

WITH CHECK OPTION in Updatable View

Ensures INSERT or UPDATE through the view satisfies its WHERE clause, rejecting non-compliant rows.

Removing a VIEW from Database

Use DROP VIEW view_name; the underlying base tables remain unaffected.