In class SQL commands Pt 2

Week 2

Select all students

SELECT * FROM students;

Select particular columns

SELECT name, major FROM students;

WHERE with equality

SELECT * FROM students

WHERE major = ‘Computer Science’;

only students whose major is CS is returned

WHERE with numerical comparison

SELECT * FROM students

WHERE student_id > 101;

Only students whose id is greater thna 101 are returned

Other comparison operators

SELECT * FROM students

WHERE student_id <= 102;

WHERE with a character comparison

SELECT * FROM students

WHERE name > ‘Bob’;

SQL compares character strings lexicographically and returns names that sort after Bob

WHERE with IN

SELECT * FROM students

WHERE major IN (‘Computer Science’, ‘Mathematics’);

How does IN select rows?

based on whether a column’s value belongs to a specified set. It does NOT select columns

WHERE with NOT IN

SELECT * FROM students

WHERE major NOT IN (‘Computer Science’);

LIKE with names that begin with A

SELECT * FROM students

WHER name like ‘A%’;

What does the % represent?

represents zero or more characters

LIKE with underscore, return names whose second character is o

SELECT * FROM students

WHERE name LIKE ‘_o%’;

NOT LIKE - return students whose names do not begin with A

SELECT * FROM students

WHERE name NOT LIKE ‘A%’;

Combining conditions with AND. Both must be true, return only cs and IDs > 101

SELECT * FROM students

WHERE major = ‘Computer Science’

AND student_id > 101;

Combining conditions with OR. return student if either condition is true

SELECT * FROM students'

WHERE major = ‘Computer Science’

OR major = ‘Mathematics’;

Combining AND and OR

SELECT * FROM students

WHERE major = ‘Computer Science’

OR (major = ‘Mathematics’ AND student_id > 101);

students satisfying either the first condition or the complete second condition are returned, parentheses make the intended logic explicit

NULL insert a student whose major is unknown

INSERT INTO students VALUES

(105, ‘Eve’, NULL);

Find rows containing NULL

SELECT * FROM students

WHERE major IS NULL;

Eve is returned

What is NULL not the same as?

zero

empty string

or the word ‘NULL’

Find rows that are NOT NULL

SELECT * FROM students

WHERE major IS NOT NULL;

Demonstrate that = NULL does not work

SELECT * FROM students

WHERE major = NULL;

no rows are returned. Since NULL represents an unknown value it cannot be tested using ordinary equality.

Count all enrollment records

SELECT COUNT(*) FROM enrollments;

returns total number of rows in enrollments

Count enrollments by course

SELECT course_id, COUNT(*)

FROM enrollments

GROUP BY course_id;

one group is formed for each course_id and counts the rows in each group

Give the aggregate column an alias

SELECT course_id, COUNT(*) AS enrollment_count

FROM enrollments

GROUP BY course_id;

column named enrollment_count instead of the less descriptive COUNT(*)

Count enrollments by semester

SELECT semester, COUNT(*) AS enrollment_count

FROM enrollments

GROUP BY semester;

all rows having the same semester are placed into one group, and the number of enrollments in each group is counted

What is an aggregate function?

An example with multiple aggregate functions

SELECT course_id,

COUNT(*) AS number_of_students,

MIN(student_id) AS first_student,

MAX(student_id) AS last_student

FROM enrollments

GROUP BY course_id;

Use HAVING to keep groups with multiple students.

How does MySQL first group these?

SELECT course_id, COUNT(*) AS enrollment_count

FROM enrollments

GROUP BY course_id

HAVING COUNT(*) > 1;

MySQL first groups the enrollment records by course, counts the rows in each group, and then keeps only groups whose count is greater than 1

WHERE vs. HAVING

SELECT course_id, COUNT(*) AS enrollment_count

FROM enrollments

WHERE semester = 'Fall 2026'

GROUP BY course_id

HAVING COUNT(*) > 1;

What is the difference between WHERE and HAVING

Key idea: WHERE filters rows BEFORE grouping; HAVING filters groups AFTER grouping

WHERE is applied first and eliminates individual rows whose semester is not Fall 2026. The remaining rows are then grouped by course, and HAVING eliminates groups whose count is not greater than 1.

Showing the difference between WHERE and HAVING EXPLICITLY

SELECT course_id, COUNT(*) AS enrollment_count

FROM enrollments

GROUP BY course_id

HAVING COUNT(*) > 1;

The count is calculated using all rows that reach the grouping stage.

ORDER BY - sort students alphabetically

SELECT * FROM students

ORDER BY name;

sorted by ascending alphabetical order

Sort in descending order

SELECT * FROM students

ORDER BY name DESC;

reverse alphabetical order

Sort by an AGGREGATE value

SELECT course_id, COUNT(*) AS enrollment_count

FROM enrollments

GROUP BY course_id

ORDER BY enrollment_count DESC;

courses re grouped and counted first, and the resulting groups are then sorted from the largest enrollment to the smallest

INSERT one row

INSERT INTO students

VALUES (106, ‘Frank’, ‘Physics’);

Verify the new row

SELECT * FROM students

WHERE student_id = 106;

UPDATE a row

UPDATE students

SET major = ‘Computer Science’

WHERE student_id = 106;

Verify the UPDATE

SELECT * FROM students

WHERE student_id = 106;

UPDATE multiple rows

UPDATE students

SET major = ‘Computer Science’

WHERE major IN (‘Computer Engineering, ‘Computer Science’);

Every row whose major is either CE OR CS is updated

How many rows can an update affect?

An UPDATE can affect MULTIPLE rows when the WHERE condition matches multiple rows

DELETE one row

DELETE FROM students

WHERE student_id = 106;

DELETE using IN

DELETE FROM students

WHERE student_id IN (104,105);

DELETE using multiple conditions

DELETE FROM enrollments

WHERE course_id = ‘CS429’

AND semester = ‘Fall 2026’

AND student_id = 103;

Logical Processing Order

FROM

↓

WHERE ← Eliminate individual rows

↓

GROUP BY ← From Groups

↓

HAVING ← Eliminate groups

↓

SELECT ← Produce the requested columns and expressions

↓

ORDER BY ← sort the results

REMEMBER

• WHERE filters individual rows.

• GROUP BY creates groups of rows.

• Aggregate functions such as COUNT, SUM, AVG, MIN, and MAX operate on groups.

• HAVING filters the resulting groups.

• SELECT determines what appears in the result.

• ORDER BY sorts the final result.