exercise_01_basic_queries.sql

-- Exercise 1: Basic SQL Queries
-- Complete the following exercises to practice basic SQL operations

-- Exercise 1.1: Basic SELECT statements
-- Write queries to:

-- 1. Select all columns from the students table
-- Your query here:

-- 2. Select only first_name, last_name, and email from students
-- Your query here:

-- 3. Select students with GPA above 3.5
-- Your query here:

-- 4. Select students whose last name starts with 'S'
-- Your query here:

-- 5. Select students enrolled in 2023, sorted by GPA (highest first)
-- Your query here:

-- Exercise 1.2: WHERE clause practice
-- Write queries to:

-- 1. Find all active students
-- Your query here:

-- 2. Find students with GPA between 3.0 and 3.8
-- Your query here:

-- 3. Find students whose email contains '@university.edu'
-- Your query here:

-- 4. Find students with NULL phone numbers
-- Your query here:

-- 5. Find students with status 'active' OR 'graduated'
-- Your query here:

-- Exercise 1.3: ORDER BY and LIMIT
-- Write queries to:

-- 1. Show students sorted by last name (alphabetical)
-- Your query here:

-- 2. Show top 5 students by GPA
-- Your query here:

-- 3. Show students sorted by enrollment date (newest first)
-- Your query here:

-- 4. Show the 3rd through 5th students when sorted by GPA
-- Your query here:

-- Exercise 1.4: Aggregate functions
-- Write queries to:

-- 1. Count total number of students
-- Your query here:

-- 2. Calculate average GPA of all students
-- Your query here:

-- 3. Find minimum and maximum GPA
-- Your query here:

-- 4. Count students by status
-- Your query here:

-- 5. Calculate average GPA by status
-- Your query here:

-- Exercise 1.5: Data insertion
-- Write INSERT statements to:

-- 1. Insert a new student with the following data:
--    First name: 'Test', Last name: 'Student', Email: 'test.student@university.edu'
--    Phone: '555-9999', Enrollment date: '2024-01-15', GPA: 3.5, Status: 'active'
-- Your query here:

-- 2. Insert a new department:
--    Name: 'Test Department', Budget: 100000.00, Established date: '2024-01-01'
-- Your query here:

-- 3. Insert a new course:
--    Code: 'TEST101', Name: 'Test Course', Credits: 3, Department: Test Department
-- Your query here:

-- Exercise 1.6: Data modification
-- Write UPDATE statements to:

-- 1. Update the GPA of student with ID 1 to 3.8
-- Your query here:

-- 2. Update all inactive students to have status 'active'
-- Your query here:

-- 3. Update the phone number of student with email 'test.student@university.edu' to '555-8888'
-- Your query here:

-- Exercise 1.7: Data deletion
-- Write DELETE statements to:

-- 1. Delete the test student you created
-- Your query here:

-- 2. Delete the test department you created
-- Your query here:

-- 3. Delete the test course you created
-- Your query here:

-- Exercise 1.8: Complex queries
-- Write queries to:

-- 1. Find students whose first name is exactly 4 characters long
-- Your query here:

-- 2. Find students enrolled in both 2023 and 2024 (hint: use subqueries or multiple conditions)
-- Your query here:

-- 3. Find the student with the highest GPA in each status group
-- Your query here:

-- 4. Calculate the percentage of students with GPA above 3.5
-- Your query here:

-- 5. Find students whose email domain is NOT '@university.edu'
-- Your query here:

-- Bonus Exercise: Create a comprehensive student report
-- Write a query that shows:
-- Student ID, Full Name (First Last), Email, Phone, GPA, Status, Enrollment Year
-- Only for active students, sorted by GPA (highest first)
-- Your query here: