In today’s video, I learned about the use of the Select in SQL, I want to show them with examples, just like yesterday’s example, we set three relations: college, student, and apply to the college. The college we have some information like cName which is college name, and enrollment of the college. The student we have sID which is ID of student, sName which is student’s name, GPA, and HS: the size of high school that the student attended. Basic Select is SELECT FROM WHERE. SELECT gives the table name, WHERE gives the filtering condition. And if we want to find the students whose GPA are higher than 3.6, we can set the query like:
Select sID, sName, GPA
From Student
Where GPA > 3.6;
Then, we can get the result of the students whose GPA are greater than 3.6, and if we don’t write the GPA which is behind the Select, we can also get the same result. We want to find the student and the major they apply, and we may get some same result from the relations, at this time we can just add a “distinct” behind the Select, and we can eliminate the same result.
Then, we want to find the students who applied the CS major of the colleges’ enrollment are larger than 20000. We cannot set the two attribute that we want to get equal, which means, when we set the query like:
Select cName
From College, Apply
Where College.cName = Apply.cName
And enrollment > 20000 and major = ‘CS’;
We will get an error here, we should select clearer, so, we can set the query like:
Select College.cName
From College, Apply
Where College.cName = Apply.cName
And enrollment > 20000 and major = ‘CS’;
Then, we can get a right answer. And if we don’t want the duplicate, we can still add “distinct” behind the select
Finally, we want to put three relation together, college, student, and apply, and we want to get the students’ ID, their name, their GPA, the college they applied, and the enrollment of that college. Which can be written like:
Select Student.sID, sName, GPA, Apply.cName, enrollment
From Student, College, Apply
Where Apply.sID = Student.sID and College.cName = Apply.cName
Then, we can get the result for the things that we want to get. Furthermore, if we want this kind of results show in ordered, such as the GPA show in a descending way, we can set the query like:
Select Student.sID, sName, GPA, Apply.cName, enrollment
From Student, College, Apply
Where Apply.sID = Student.sID and College.cName = Apply.cName
Order by GPA desc;
Right now, we can get the result that are showed in order that the GPA are descending.