Reference no: EM1333294

Write a SQL query that returns the names of the students (lname, fname), and the major of the major with most students. If more than one major is tied for most students, then list all the names of the students from both majors (as well as the majors). Order the results by major (in the case of a tie) and then alphabetically by last name and first name.

Table Data:
id, fname, lname, major

1;"Jeff";"Salvage";"Computer Science"
2;"Thurman";"Munson";"Computer Science"
3;"Chase";"Utley";"Physical Fitness"
4;"Jimmy";"Rollins";"Computer Science"
5;"Derek";"Jeter";"World Series Championships"
6;"Jeff";"Popyack";"Mathematics"
7;"Kurt";"Schmidt";"Mathematics"
8;"Jason";"Lenhart";"Mathematics"
;"";"";""

Table SQL:

-- student information
CREATE TABLE Student (
id SERIAL PRIMARY KEY, -- student id number
fname VARCHAR(20) NOT NULL, -- first name
lname VARCHAR(20) NOT NULL, -- last name
major VARCHAR(60) NOT NULL -- major
);

INSERT INTO Student (fname, lname, major) VALUES ('Jeff', 'Salvage', 'Computer Science');
INSERT INTO Student (fname, lname, major) VALUES ('Thurman', 'Munson', 'Computer Science');
INSERT INTO Student (fname, lname, major) VALUES ('Chase', 'Utley', 'Physical Fitness');
INSERT INTO Student (fname, lname, major) VALUES ('Jimmy', 'Rollins', 'Computer Science');
INSERT INTO Student (fname, lname, major) VALUES ('Derek', 'Jeter', 'World Series Championships');
INSERT INTO Student (fname, lname, major) VALUES ('Jeff', 'Popyack', 'Mathematics');
INSERT INTO Student (fname, lname, major) VALUES ('Kurt', 'Schmidt', 'Mathematics');