Tell us what’s happening:
I am at the first task in which I am required to output columns with custom headers to the terminal. I have passed the test, but no column headers are actually printed – just the data. The same query in the PSQL terminal prints out the appropriate column headers.
Is this the intended output? If so, why go to the trouble of naming your columns (edit: in this case of only printing output to the screen) when you can’t see them anyway?
Your code so far
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")"
And here is the output:
Major 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:
|8|2.97
41|6|3.53
38|4|2.73
36|6|2.92
37|6|3.38
Your browser information:
User Agent is: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/106.0.0.0 Safari/537.36
Challenge: Learn SQL by Building a Student Database: Part 2 - Build a Student Database: Part 2
The column names are identifiers so you can make inserts and other types of edits to them.
You can also see them if you want to with a different select statement.
Edit: the output you shared doesn’t match the code you shared. Did you edit it?
Major 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:
|8|2.97
41|6|3.53
38|4|2.73
36|6|2.92
37|6|3.38
I omitted the long line of text at the top to save space on the post.
If I am simply using echo to print once to the terminal, I don’t see how those custom column headers can later be used for anything without re-declaring them, nor do I see how you would be allowed to edit data inside of a column representing the results of an aggregate function.
What type of select statement would cause the column headers to be included in the output?
#!/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':"
In the instructions, I was told not to worry about the meaning of the content in the PSQL=... line because it’s just for formatting. Could something there be preventing the display of column headers?
unfortunately i am unable to get into codeally at the moment (not sure yet why), but all I wanted to investigate was this line here:
because I expect the use of “AS” to mean that the word following that keyword would get printed out as the column header (but you say it isn’t, so that’s what I was hoping to investigate. I’m not sure if the headers are being hidden by the PSQL statement or not)
finally have an answer for you.
The reason you aren’t seeing any of those headers is because of the way psql is being started in this line.
The option --tuples-only hides the headers.