MySQL Query Practice Set
🔄 Student-Course Relationship Queries
All students with their enrolled courses
SELECT s.student_id, s.first_name, c.course_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;
Students taking the "Algorithms" course
SELECT s.student_id, s.first_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE c.course_name = 'Algorithms';
All courses with students enrolled
SELECT c.course_name, s.first_name, s.last_name
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
JOIN students s ON e.student_id = s.student_id;
Students in more than one course
SELECT student_id
FROM enrollments
GROUP BY student_id
HAVING COUNT(course_id) > 1;
Students not enrolled in any course
SELECT *
FROM students
WHERE student_id NOT IN (SELECT student_id FROM enrollments);
Number of students enrolled in each course
SELECT c.course_name, COUNT(e.student_id) AS num_students
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_name;
Courses taught by 'Prof. Smith' with enrolled students
SELECT c.course_name, s.first_name, s.last_name
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
JOIN students s ON e.student_id = s.student_id
WHERE c.instructor = 'Prof. Smith';
Students with their classes (grades) and enrolled courses
SELECT s.student_id, s.first_name, s.grade, c.course_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;
Students enrolled after '2025-03-02'
SELECT s.student_id, s.first_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.enrollment_date > '2025-03-02';
Students taking both 'Data Structures' and 'Algorithms'
SELECT s.student_id, s.first_name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE c.course_name IN ('Data Structures', 'Algorithms')
GROUP BY s.student_id
HAVING COUNT(DISTINCT c.course_name) = 2;
Database & Table Management
Create database
CREATE DATABASE school_db;
Select database
USE school_db;
Delete (drop) database
DROP DATABASE school_db;
Create `students` table
CREATE TABLE students (
student_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50),
age INT,
grade VARCHAR(5)
);
Drop `students` table
DROP TABLE students;
Add `email` column
ALTER TABLE students ADD email VARCHAR(100);
Change `age` to TINYINT
ALTER TABLE students MODIFY age TINYINT;
Remove `email` column
ALTER TABLE students DROP COLUMN email;
Add UNIQUE to `email`
ALTER TABLE students ADD CONSTRAINT unique_email UNIQUE(email);
Set `grade` to NOT NULL
ALTER TABLE students MODIFY grade VARCHAR(5) NOT NULL;
Add primary key to `student_id`
ALTER TABLE students ADD PRIMARY KEY(student_id);
📥 Insert / Update / Delete Data
Insert one record
INSERT INTO students (student_id, first_name, last_name, age, grade)
VALUES (1, 'John', 'Doe', 15, '10');
Insert multiple records
INSERT INTO students (student_id, first_name, last_name, age, grade)
VALUES
(2, 'Alice', 'Smith', 14, '9'),
(3, 'Bob', 'Brown', 16, '11');
Update grade
UPDATE students SET grade = '11' WHERE student_id = 1;
Delete record
DELETE FROM students WHERE student_id = 1;
📋 Select Queries & Filtering
Select all
SELECT * FROM students;
Select specific columns
SELECT first_name, grade FROM students;
Students older than 14
SELECT * FROM students WHERE age > 14;
Grade '10' or '12'
SELECT * FROM students WHERE grade IN ('10', '12');
Older than 14 AND grade '10'
SELECT * FROM students WHERE age > 14 AND grade = '10';
Grade '10' or '11'
SELECT * FROM students WHERE grade IN ('10', '11');
Not in grade '12'
SELECT * FROM students WHERE grade != '12';
Order by last_name
SELECT * FROM students ORDER BY last_name ASC;
Order by age descending
SELECT * FROM students ORDER BY age DESC;
Top 3 youngest
SELECT * FROM students ORDER BY age ASC LIMIT 3;
Last name is NULL
SELECT * FROM students WHERE last_name IS NULL;
Update NULL last_names
UPDATE students SET last_name = 'Unknown' WHERE last_name IS NULL;
🔢 Aggregates & Wildcards
Max age
SELECT MAX(age) FROM students;
Average age
SELECT AVG(age) FROM students;
Count total students
SELECT COUNT(*) FROM students;
Sum of ages
SELECT SUM(age) FROM students;
First name starts with 'J'
SELECT * FROM students WHERE first_name LIKE 'J%';
Last name ends with 'e'
SELECT * FROM students WHERE last_name LIKE '%e';
First name contains 'an'
SELECT * FROM students WHERE first_name LIKE '%an%';
Grades '9', '10', '11' using IN
SELECT * FROM students WHERE grade IN ('9', '10', '11');
Age between 14 and 16
SELECT * FROM students WHERE age BETWEEN 14 AND 16;
🎨 Aliases and Renaming
Alias `first_name` as `fname`
SELECT first_name AS fname FROM students;
Rename `students` table in query
SELECT s.first_name FROM students AS s;
