MySQL Clauses
Create a Table
1. CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT,
gender VARCHAR(10),
department VARCHAR(50),
gpa DECIMAL(3, 2),
enrollment_date DATE
);
2. Insert 15 Records
INSERT INTO students (first_name, last_name, age, gender, department, gpa, enrollment_date)
VALUES
('Alice', 'Smith', 20, 'Female', 'CS', 3.8, '2023-08-20'),
('Bob', 'Johnson', 21, 'Male', 'CS', 3.6, '2022-07-15'),
('Charlie', 'Brown', 22, 'Male', 'Math', 3.4, '2021-06-12'),
('Diana', 'White', 19, 'Female', 'Physics', 3.9, '2023-09-01'),
('Ella', 'Green', 20, 'Female', 'CS', 3.7, '2023-05-05'),
('Frank', 'Adams', 23, 'Male', 'Math', 3.5, '2021-04-20'),
('Grace', 'Hall', 22, 'Female', 'Physics', 3.8, '2022-03-18'),
('Harry', 'Clark', 21, 'Male', 'Biology', 3.6, '2022-08-10'),
('Ivy', 'Lewis', 20, 'Female', 'CS', 3.7, '2023-01-25'),
('Jack', 'Walker', 19, 'Male', 'Math', 3.2, '2023-02-15'),
('Karen', 'King', 22, 'Female', 'Biology', 3.5, '2021-09-05'),
('Liam', 'Harris', 23, 'Male', 'Physics', 3.4, '2021-10-12'),
('Mia', 'Young', 20, 'Female', 'CS', 3.9, '2023-11-01'),
('Noah', 'Hill', 19, 'Male', 'Math', 3.1, '2023-12-15'),
('Olivia', 'Scott', 21, 'Female', 'Biology', 3.6, '2022-05-23');
3. WHERE Clause
SELECT * FROM students WHERE age > 20;
4. WHERE Clause with AND Condition
SELECT * FROM students WHERE age > 20 AND gpa > 3.5;
5. WHERE Clause with OR Condition
SELECT * FROM students WHERE department = 'CS' OR department = 'Math';
6. WHERE Clause with Combination of AND & OR
SELECT * FROM students WHERE (department = 'CS' OR department = 'Math') AND gpa > 3.5;
7. WHERE Clause with DISTINCT (Single Expression)
SELECT DISTINCT department FROM students WHERE gpa > 3.5;
8. WHERE Clause with DISTINCT (Multiple Expressions)
SELECT DISTINCT department, gender FROM students WHERE gpa > 3.5;
9. ORDER BY Clause
SELECT * FROM students WHERE gpa > 3.5 ORDER BY gpa;
10. ORDER BY Clause with ASC
SELECT * FROM students WHERE gpa > 3.5 ORDER BY gpa ASC;
11. ORDER BY Clause with DESC
SELECT * FROM students WHERE gpa > 3.5 ORDER BY gpa DESC;
12. Using Both ASC and DESC
SELECT * FROM students ORDER BY department ASC, gpa DESC;
13. GROUP BY Clause with COUNT Function
SELECT department, COUNT(*) AS total_students FROM students GROUP BY department;
14. GROUP BY Clause with SUM Function
SELECT department, SUM(gpa) AS total_gpa FROM students GROUP BY department;
15. GROUP BY Clause with MIN Function
SELECT department, MIN(gpa) AS min_gpa FROM students GROUP BY department;
16. GROUP BY Clause with MAX Function
SELECT department, MAX(gpa) AS max_gpa FROM students GROUP BY department;
17. GROUP BY Clause with AVG Function
SELECT department, AVG(gpa) AS avg_gpa FROM students GROUP BY department;
18. HAVING Clause
SELECT department, COUNT(*) AS total_students FROM students GROUP BY department HAVING
total_students > 3;
19. HAVING Clause with SUM Function
SELECT department, SUM(gpa) AS total_gpa FROM students GROUP BY department HAVING
total_gpa > 10;
