MySQL cheatsheet: query

select_query.sql

-- Basic query
SELECT * FROM Students;

-- Get only some columns, and filter the results
SELECT name, country FROM Students WHERE age>28;

-- Limit the number of results and sort them alphabetically
SELECT * FROM Students SORT BY name LIMIT 20;

-- Get the number of students for each country
SELECT COUNT(age) FROM Students GROUP BY country;

-- Get the average age of the students of each country, and show only those where it's lower than 30
SELECT AVG(age) FROM Students GROUP BY country HAVING AVG(age)<30;

-- Get the name and age of all students and teachers, without skipping those who have the same name and age
SELECT (name, age) FROM Students UNION ALL SELECT (name, age) FROM Teachers;

-- Join the data from a student and her grades
SELECT Student.name, Grades.course, Grades.value FROM Students INNER JOIN Grades ON Students.id = Grades.student_id;

-- Get the names of the students with at least one A grade
SELECT name FROM Students WHERE id IN (SELECT student_id FROM Grades WHERE value=='A');