100天编程挑战：修订间差异

* Day 28（2025-01-19）：继续编写<!-- #!/bin/bash # Info about my computer science students from students database  echo -e "\n~~ My Computer Science Students ~~\n"  PSQL="psql -X --username=freecodecamp --dbname=students --no-align --tuples-only -c"  echo -e "\nFirst name, last name, and GPA of students with a 4.0 GPA:"  echo "$($PSQL "SELECT first_name, last_name, gpa FROM students WHERE gpa=4.0")"  echo -e "\nAll course names whose first letter is before 'D' in the alphabet:"  echo "$($PSQL "SELECT course FROM courses WHERE course<'D'")"  echo -e "\nFirst name, last name, and GPA of students whose last name begins with an 'R' or after and have a GPA greater than 3.8 or less than 2.0:"  echo "$($PSQL "SELECT first_name, last_name, gpa FROM students WHERE last_name>='R' AND (gpa>3.8 OR gpa<2.0)")"  echo -e "\nLast name of students whose last name contains a case insensitive 'sa' or have an 'r' as the second to last letter:"  echo "$($PSQL "SELECT last_name FROM students WHERE last_name ILIKE '%sa%' OR last_name ILIKE '%r_'")"  echo -e "\nFirst name, last name, and GPA of students who have not selected a major and either their first name begins with 'D' or they have a GPA greater than 3.0:"  echo "$($PSQL "SELECT first_name, last_name, gpa FROM students WHERE major_id IS NULL AND (first_name LIKE 'D%' OR gpa > 3.0)")"  echo -e "\nCourse name of the first five courses, in reverse alphabetical order, that have an 'e' as the second letter or end with an 's':"  echo "$($PSQL "SELECT course FROM courses WHERE course LIKE '_e%' OR course LIKE '%s' ORDER BY course DESC LIMIT 5;")"  echo -e "\nAverage GPA of all students rounded to two decimal places:"  echo "$($PSQL "SELECT ROUND(AVG(gpa),2) FROM students")"  echo -e "\nMajor ID, total number of students in a column named 'number_of_students', and average GPA rounded to two decimal places in a column name 'average_gpa', for each major ID in the students table having a student count greater than 1:"  echo "$($PSQL "SELECT major_id, COUNT(*) AS number_of_students, ROUND(AVG(gpa),2) AS average_gpa FROM students GROUP BY major_id HAVING COUNT(*) > 1;")"  echo -e "\nList of majors, in alphabetical order, that either no student is taking or has a student whose first name contains a case insensitive 'ma':"  echo "$($PSQL "SELECT major FROM majors LEFT JOIN students ON majors.major_id = students.major_id WHERE first_name ILIKE '%ma%' OR first_name IS NULL ORDER BY major")"  echo -e "\nList of unique courses, in reverse alphabetical order, that no student or 'Obie Hilpert' is taking:"  echo "$($PSQL "SELECT DISTINCT(course) FROM courses          LEFT JOIN majors_courses USING (course_id)          LEFT JOIN students USING (major_id)        WHERE student_id IS NULL OR        (first_name='Obie' AND last_name='Hilpert')        ORDER BY course DESC        ;")"  echo -e "\nList of courses, in alphabetical order, with only one student enrolled:"  echo "$($PSQL "SELECT course FROM courses        LEFT JOIN majors_courses USING (course_id)        LEFT JOIN students USING (major_id)        GROUP BY course        HAVING COUNT(student_id) = 1        ORDER BY course;  ")" -->来练习 SQL 查询；