is331-quizlet-import

What does the HAVING clause do that WHERE cannot? Filters groups after GROUP BY is applied. WHERE filters rows before aggregation; HAVING filters on aggregated results like COUNT or SUM.

Which JOIN returns all rows from the left table and matched rows from the right? LEFT JOIN. Unmatched rows from the right table come back as NULL.

What is the purpose of a WITH clause (CTE)? Creates a temporary named result set reusable in the next query. Acts like a named subquery scoped to a single SELECT/INSERT/UPDATE/DELETE.

Which aggregate function returns the number of non-NULL values? COUNT(column). COUNT(*) counts all rows including NULLs; COUNT(column) only counts non-NULL values.

What does FOR XML AUTO do in SQL Server? Converts a query result set into an XML document. Element names are derived from table and column names.

What is the difference between UNION and UNION ALL? UNION removes duplicate rows from the combined result. UNION ALL keeps all rows including duplicates and is faster.

Which SQL clause sorts results in descending order? ORDER BY col DESC. ASC is the default direction.

What does the LIKE operator '%10%' match? Any string containing '10' anywhere. The % wildcard matches zero or more characters on either side.

What are the requirements for a table to be in 1NF? All attributes must be atomic (indivisible) and there can be no repeating groups. Each row must be unique.

What is a partial dependency? When a non-key attribute depends on only part of a composite primary key — not the whole key. Violates 2NF.

What must be eliminated to move from 2NF to 3NF? Transitive dependencies — where a non-key attribute determines another non-key attribute.

Which normal form is violated when a non-prime attribute determines part of a candidate key? BCNF (Boyce-Codd Normal Form). Every determinant must be a candidate key in BCNF.

What is an example of a transitive dependency? StudentID → ZipCode and ZipCode → City. City is transitively dependent on StudentID through ZipCode — a 3NF violation.

What is a functional dependency? When the value of one attribute uniquely determines another attribute's value. Written A → B.

What are the three main sections of a PL/SQL block? DECLARE (variables), BEGIN (logic/statements), END. The EXCEPTION section is optional but common.

Which PL/SQL loop is guaranteed to execute at least once? A basic LOOP…END LOOP with EXIT WHEN inside. The condition is checked after the first iteration runs.

What does a CURSOR do in PL/SQL? Points to a SQL query result set and allows row-by-row processing. You OPEN it, FETCH rows one at a time, then CLOSE it.

What is the correct syntax to declare a variable in PL/SQL? v_name VARCHAR2(50); written in the DECLARE section. Example: v_sal NUMBER(8,2);

What does %ROWTYPE do in PL/SQL? Declares a variable with the same structure (all columns and datatypes) as an entire table row. Example: v_emp employees%ROWTYPE;

Which PL/SQL statement raises a user-defined exception? RAISE exception_name. Transfers control to the EXCEPTION block.

What does ACID stand for in database transactions? Atomicity, Consistency, Isolation, Durability. The four properties that guarantee reliable transaction processing.

What does a ROLLBACK statement do? Undoes all changes made since the last COMMIT or SAVEPOINT, reverting the database to its previous state.

Which isolation level prevents dirty reads but allows non-repeatable reads? READ COMMITTED. It only reads committed data but another transaction can modify a row between two reads in the same transaction.

What is a dirty read? When a transaction reads uncommitted changes made by another transaction. Occurs under READ UNCOMMITTED isolation.

What is a deadlock? When two transactions each hold a lock the other needs and both wait indefinitely. The DBMS detects and kills one victim transaction to resolve it.

What is a star schema? A central fact table surrounded by denormalized dimension tables. Simple structure optimized for fast analytical queries.

How does a snowflake schema differ from a star schema? Dimension tables are further normalized into sub-dimension tables. More storage-efficient but requires more joins to query.

What is the difference between OLTP and OLAP? OLTP handles frequent short transactions (INSERT/UPDATE/DELETE). OLAP supports complex analytical queries on large amounts of historical data.

What is ETL? Extract, Transform, Load. The process of moving data from source systems into a data warehouse: extract from sources, transform/clean it, load into the warehouse.

What does a fact table in a data warehouse typically contain? Numeric measures (e.g. sales amount, units sold) and foreign keys that reference dimension tables for context.

What is a VIEW in SQL? A virtual table defined by a stored SELECT query. It doesn't store data itself — it runs the underlying query each time it's accessed.

What are two key benefits of using a VIEW? Simplifies complex queries (abstraction) and restricts column-level access for security. Views can hide sensitive columns from users.

What does WITH CHECK OPTION do on an updatable view? Ensures that any INSERT or UPDATE made through the view still satisfies the view's WHERE clause. Rejects rows that would be excluded by the view.

Which command removes a view from the database? DROP VIEW view_name. The underlying base tables are unaffected.