BrainPatchworkDX.com

-- DAY 7: LIKE Pattern Matching
-- -----------------------------------------------------------------------------
-- LIKE with % (zero or more characters)
SELECT * FROM students WHERE last_name LIKE 'S%';

SELECT * FROM students WHERE first_name LIKE '%e%';

-- LIKE with _ (exactly one character)
SELECT * FROM courses WHERE course_name LIKE 'Data_______';

-- NOT LIKE
SELECT * FROM students WHERE email NOT LIKE '%gmail%';

-- Case sensitivity handling (use LOWER or UPPER for case-insensitive)
SELECT * FROM students
WHERE LOWER(last_name) LIKE LOWER('smith%');

-- EXERCISES:
-- 1. Find students whose last name ends with 'son'
-- 2. Find courses with 'Computer' in the name
-- 3. Find students with exactly 3-letter first names

-- ============================================================================
-- WEEK 2 (Days 8-14): AGGREGATE FUNCTIONS & GROUPING
-- ============================================================================

-- DAY 8: Basic Aggregate Functions
-- -----------------------------------------------------------------------------
-- COUNT
SELECT COUNT(*) AS total_students FROM students;

SELECT COUNT(DISTINCT department) AS unique_departments FROM courses;

-- SUM
SELECT SUM(credits) AS total_credits FROM courses;

-- AVG
SELECT AVG(gpa) AS average_gpa FROM students;

-- MIN and MAX
SELECT
MIN(gpa) AS lowest_gpa,
MAX(gpa) AS highest_gpa
FROM students;

-- Multiple aggregates
SELECT
COUNT(*) AS student_count,
AVG(gpa) AS avg_gpa,
MIN(gpa) AS min_gpa,
MAX(gpa) AS max_gpa
FROM students;

-- EXERCISES:
-- 1. Count total number of courses
-- 2. Find average credits across all courses
-- 3. Get min, max, and average grade from assignments

-- DAY 9: GROUP BY Clause
-- -----------------------------------------------------------------------------
-- Group by single column
SELECT
department,
COUNT(*) AS course_count
FROM courses
GROUP BY department;

-- Group by with multiple aggregates
SELECT
department,
COUNT(*) AS course_count,
AVG(credits) AS avg_credits,
SUM(credits) AS total_credits
FROM courses
GROUP BY department;

-- Group by multiple columns
CREATE TABLE enrollments (
enrollment_id INTEGER PRIMARY KEY,
student_id INTEGER,
course_id INTEGER,
semester VARCHAR(20),
year INTEGER,
final_grade VARCHAR(2)
);

INSERT INTO enrollments VALUES
(1, 1, 101, 'Fall', 2023, 'A'),
(2, 1, 102, 'Fall', 2023, 'B'),
(3, 2, 101, 'Fall', 2023, 'B'),
(4, 3, 103, 'Fall', 2023, 'A'),
(5, 3, 104, 'Fall', 2023, 'A'),
(6, 4, 101, 'Fall', 2023, 'C'),
(7, 5, 102, 'Fall', 2023, 'A');

SELECT
semester,
year,
COUNT(*) AS enrollment_count
FROM enrollments
GROUP BY semester, year;

-- EXERCISES:
-- 1. Count enrollments per student
-- 2. Count courses per instructor
-- 3. Count enrollments by semester and final_grade

-- DAY 10: HAVING Clause
-- -----------------------------------------------------------------------------
-- HAVING filters groups (use after GROUP BY)
SELECT
department,
COUNT(*) AS course_count
FROM courses
GROUP BY department
HAVING COUNT(*) >= 2;

-- HAVING with multiple conditions
SELECT
department,
AVG(credits) AS avg_credits,
COUNT(*) AS course_count
FROM courses
GROUP BY department
HAVING COUNT(*) >= 2 AND AVG(credits) >= 3;

-- WHERE vs HAVING
-- WHERE filters rows before grouping, HAVING filters groups after
SELECT
department,
COUNT(*) AS course_count
FROM courses
WHERE credits >= 3
GROUP BY department
HAVING COUNT(*) >= 2;

-- EXERCISES:
-- 1. Find students with more than 1 enrollment
-- 2. Find departments with average credits > 3.5
-- 3. Find instructors teaching more than 1 course

-- DAY 11: Arithmetic Operations and Calculations
-- -----------------------------------------------------------------------------
-- Basic arithmetic
SELECT
course_name,
credits,
credits * 15 AS lecture_hours,
credits * 30 AS study_hours
FROM courses;

-- Calculations with aggregates
SELECT
student_id,
COUNT(*) AS courses_taken,
AVG(CASE
WHEN final_grade = 'A' THEN 4.0
WHEN final_grade = 'B' THEN 3.0
WHEN final_grade = 'C' THEN 2.0
WHEN final_grade = 'D' THEN 1.0
ELSE 0.0
END) AS semester_gpa
FROM enrollments
GROUP BY student_id;

-- ROUND, FLOOR, CEIL
SELECT
gpa,
ROUND(gpa, 1) AS rounded_gpa,
FLOOR(gpa) AS floor_gpa,
CEILING(gpa) AS ceil_gpa
FROM students;

-- EXERCISES:
-- 1. Calculate total tuition (assume $1000 per credit)
-- 2. Calculate percentage of A grades per student
-- 3. Round all GPAs to nearest 0.5

-- DAY 12: CASE Expressions
-- -----------------------------------------------------------------------------
-- Simple CASE
SELECT
student_id,
first_name,
last_name,
gpa,
CASE
WHEN gpa >= 3.7 THEN 'Excellent'
WHEN gpa >= 3.3 THEN 'Good'
WHEN gpa >= 3.0 THEN 'Satisfactory'
ELSE 'Needs Improvement'
END AS performance
FROM students;

-- CASE in aggregation
SELECT
COUNT(*) AS total_students,
COUNT(CASE WHEN gpa >= 3.5 THEN 1 END) AS high_achievers,
COUNT(CASE WHEN gpa < 3.5 THEN 1 END) AS others
FROM students;

-- CASE with multiple conditions
SELECT
course_name,
credits,
CASE
WHEN credits >= 4 AND department = 'Computer Science' THEN 'Major Core'
WHEN credits >= 3 THEN 'Standard'
ELSE 'Light Course'
END AS course_type
FROM courses;

-- EXERCISES:
-- 1. Categorize students by enrollment date (Early, Regular, Late)
-- 2. Create grade categories for assignments (A: 90+, B: 80-89, etc.)
-- 3. Flag courses as 'Heavy' (4 credits) or 'Normal' (3 or less)

-- DAY 13: Date and Time Functions
-- -----------------------------------------------------------------------------
-- Current date and time
SELECT CURRENT_DATE AS today;
SELECT CURRENT_TIMESTAMP AS now;

-- Date arithmetic
SELECT
first_name,
enrollment_date,
CURRENT_DATE - enrollment_date AS days_enrolled
FROM students;

-- EXTRACT parts of date
SELECT
enrollment_date,
EXTRACT(YEAR FROM enrollment_date) AS year,
EXTRACT(MONTH FROM enrollment_date) AS month,
EXTRACT(DAY FROM enrollment_date) AS day
FROM students;

-- Date comparisons and ranges
SELECT * FROM students
WHERE enrollment_date BETWEEN DATE '2023-09-01' AND DATE '2023-09-03';

-- EXERCISES:
-- 1. Find students enrolled in the last 30 days
-- 2. Extract month and year from all enrollment dates
-- 3. Calculate age of each enrollment in days

-- DAY 14: Review and Practice - Week 2
-- -----------------------------------------------------------------------------
-- Comprehensive exercise combining Week 2 concepts
SELECT
s.student_id,
s.first_name || ' ' || s.last_name AS full_name,
s.gpa,
COUNT(e.enrollment_id) AS courses_enrolled,
AVG(CASE
WHEN e.final_grade = 'A' THEN 4.0
WHEN e.final_grade = 'B' THEN 3.0
WHEN e.final_grade = 'C' THEN 2.0
WHEN e.final_grade = 'D' THEN 1.0
ELSE 0.0
END) AS calculated_gpa,
CASE
WHEN COUNT(e.enrollment_id) >= 3 THEN 'Full Load'
WHEN COUNT(e.enrollment_id) >= 2 THEN 'Part Time'
ELSE 'Light Load'
END AS enrollment_status,
CURRENT_DATE - s.enrollment_date AS days_since_enrollment
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.first_name, s.last_name, s.gpa, s.enrollment_date
HAVING COUNT(e.enrollment_id) > 0
ORDER BY calculated_gpa DESC;