Aggregates, views and ACID transactions

• ConstructCalculations within a database using SQL’s aggregate functions
• DescribeDifferent database views
• DescribeHow transactions maintain data integrity in a database

Say the answer aloud before flipping. Mark secure knowledge quickly and spend time on the gaps.

SELECT CourseID,
       COUNT(*) AS N,
       AVG(Score) AS MeanScore,
       MAX(Score) AS Highest,
       MIN(Score) AS Lowest,
       SUM(Score) AS Total
FROM ENROLLMENT
GROUP BY CourseID
HAVING COUNT(*) >= 5;

BEGIN TRANSACTION;
UPDATE ACCOUNT SET Balance = Balance - 100 WHERE ID = 1;
UPDATE ACCOUNT SET Balance = Balance + 100 WHERE ID = 2;
COMMIT;
-- ROLLBACK would undo the transaction if the operation cannot complete safely.

1. Construct: Calculations within a database using SQL’s aggregate functions in the context of a relational database used for reporting and transactional updates.
2. Describe: Different database views in the context of a relational database used for reporting and transactional updates.
3. Describe: How transactions maintain data integrity in a database in the context of a relational database used for reporting and transactional updates.