CS-10337 – Applied Database Technologies� Lecture 11
By Prof. Rafael Orta
Wayground
Last class we covered
Agenda
Lab # 3 Explanation
Aggregation Functions
Aggregation Functions
SUM(column): adds up all the values in the specified column for the rows being considered.
AVG(column): calculates the average (mean) of the values.
MIN(column): finds the smallest value.
MAX(column): finds the largest value.
COUNT(column_or_*): counts the number of rows (or number of non-NULL values in a column).
DISTINCT within these: e.g., COUNT(DISTINCT aircraft_type) counts unique types, not duplicates.
Group by
GROUP BY: Takes rows and partitions them into groups based on one or more column values; then aggregate functions are applied per group rather than over all rows
Aviation analogy: imagine you have all flights of the year logged; you might group by aircraft_type to get totals/averages per aircraft type; or by airport to get summaries per airport.
Having
HAVING
HAVING acts like a WHERE but on groups (after grouping), allowing you to filter groups based on aggregate results. Eg: only include aircraft types with more than two flights.
Round
ROUND()
Use ROUND(expr, decimals) to round numeric results to a given number of decimal places.
Aviation analogy: when reporting average speed, you might show it to one decimal place (e.g., 446.3 knots) rather than full precision. Example:
Distinct
DISTINCT
Use DISTINCT inside aggregate functions to consider only unique values. Eg: COUNT(DISTINCT tail_number) gives number of unique aircraft, not number of flights.
Aviation analogy: if you log 300 flights on route A but only 10 different aircraft flew it, COUNT(DISTINCT tail_number) captures that uniqueness.
With Rollup – Top-down rollovers
WITH ROLLUP – automatic subtotals and grand totals. WITH ROLLUP is a MySQL extension to GROUP BY that automatically adds summary rows for each grouped level and a grand total.
Aviation analogy: imagine you group by airport and aircraft_type and then you want subtotals for each airport (across all types) and a grand total across all airports/types—WITH ROLLUP gives you that.
The result set will include rows where aircraft_type is NULL representing the subtotal for that airport, and a final row where both airport and aircraft_type are NULL representing the grand total.
Window Functions – OVER, WINDOW, BETWEEN, RANGE
Window Functions – calculations without collapsing rows
Aggregate functions collapse rows by group. Window functions retain each row but allow summary/analytic calculations across a “window” of rows related to the current row.
The syntax revolves around … OVER (… ), and optionally a WINDOW clause to define a named window.
Aviation analogy: suppose you want for each minute of flight the cumulative fuel burn so far, or the average speed of the past 10 minutes — you’re still looking at each row (each minute) but you’re performing calculations across a set of rows “around” that minute.
Window Functions – OVER, WINDOW, BETWEEN, RANGE
PARTITION BY and WINDOW clause
You might want to partition by, say, aircraft_type so you get cumulative fuel per aircraft type.
Named WINDOW usage (available in MySQL 8.0+)
The WINDOW clause lets you define a named window once and reuse it.
Window Functions – OVER, WINDOW, BETWEEN, RANGE
BETWEEN / RANGE: BETWEEN … AND … can define a window frame relative to the current row.
Example: ROWS BETWEEN 9 PRECEDING AND CURRENT ROW → last 10 rows including current.
RANGE frames refer to values (e.g., time or numeric difference) rather than row counts.
Aviation analogy: “calculate the average ground speed over the last 5 minutes (i.e., 5 rows) or the last 5000 ft of descent” – that’s a frame of data relative to current row.
Window Functions – OVER, WINDOW, BETWEEN, RANGE
RANGE frames are especially useful when your ordering column is a numeric value, date, or time rather than a simple row sequence.
ROWS looks at a fixed number of rows before/after the current one.
RANGE looks at rows within a range of values in the ordering column.
Supplemental Video(s)
Supplemental Reading
Knowledge Check
Select all the statements that are true about MySQL summary queries.
Correct Answers: 2, 3, 4
String Functions
CONCAT and CONCAT_WS (With Separator)
Purpose: Combine strings or columns into one.
String Functions
LTRIM, RTRIM, and TRIM
Purpose: Remove extra spaces.
String Functions
LENGTH
Purpose: Returns number of bytes (not characters).
String Functions
LOCATE
Purpose: Finds the position of a substring.
String Functions
LEFT, RIGHT, SUBSTRING, and SUBSTRING_INDEX
Purpose: Extract parts of strings.
String Functions
REPLACE and INSERT
Purpose: Replace text or insert into a string.
String Functions
REVERSE, LOWER, and UPPER
String Functions
LPAD and RPAD
Purpose: Pad a string to a certain length.
String Functions
SPACE and REPEAT
String Functions
TRIM (Advanced Option)
You can specify which side and what character to remove:
Knowledge Check
Which of the following statements about MySQL string functions is true?
A. CONCAT() automatically adds spaces between concatenated strings.�B. LENGTH() returns the number of characters, not bytes, in a string.�C. REPLACE() can substitute part of a string with another substring.�D. LPAD() removes leading spaces from a string.
Correct Answer: C.
Supplemental Video(s)
Supplemental Reading
Numeric Functions
TRUNCATE()
Purpose: Cuts off decimals without rounding.
Numeric Functions
CEILING() and FLOOR()
Purpose:
CEILING() → always rounds up to the next integer.
FLOOR() → always rounds down.
Numeric Functions
ABS()
Purpose: Returns the absolute value (removes negative sign).
SIGN()
Purpose: Identify positive and negative numbers �Returns:
1 if number > 0
0 if number = 0
-1 if number < 0
Numeric Functions
SQRT()
Purpose: Returns the square root of a number.
POWER()
Purpose: Raises a number to a power (exponentiation).
Numeric Functions
RAND()
Purpose: Returns a random number between 0 and 1.
Supplemental Video(s)
Supplemental Reading
Knowledge Check
Which of the following statements about MySQL numeric functions is true?
A. CEILING() always rounds a number down to the nearest integer.�B. TRUNCATE(7.89, 1) returns 7.8 because it cuts off digits without rounding.�C. ABS(-10) returns -10 because it preserves the sign.�D. ROUND(3.14159, 2) returns 3.1 because it always rounds down.
Correct Answer: B.
Date Functions
Date Functions
EXTRACT(field FROM date)
Returns one specific component (year, month, day, etc.).
DATE_FORMAT() and TIME_FORMAT()
Allow custom string formatting of dates/times.
Date Functions
Date Arithmetic
DATE_ADD() and DATE_SUB()
DATEDIFF()
Calculates days between two dates (date1 - date2).
Date Functions
TO_DAYS()
Converts a date to the number of days since year 0 (useful for comparisons).
TIME_TO_SEC()
Converts a time value to total seconds.
Date Functions
MONTH(), DAYOFMONTH(), YEAR(), HOUR()
Extract numeric components directly.
Supplemental Video(s)
Supplemental Reading
Knowledge Check
Which of the following statements about MySQL date and time functions is true?
A. NOW() and CURDATE() both return the same value including the current time.�B. DATEDIFF(date1, date2) returns the number of hours between two dates.�C. DATE_ADD('2025-01-01', INTERVAL 10 DAY) returns '2025-01-11'.�D. DATE_SUB('2025-01-01', INTERVAL 1 MONTH) returns '2025-02-01'.
Correct Answer: C.
Attendance
Reference Material used in this presentation