SQL described by Professor Widom.
Since I have already learned a little bit basic knowledge about SQL, select, select distinct, where, and & or, order by, insert into, update and delete, watching prof. Widom’s video is a deeper learning about the use of these SQL key words. First of all, just for a reminder, select is followed by attributes of some tables or relations. From is used to define which relation to pick up results, so from is followed by relations. And where, is to define the condition for the result. For example, we want to pick up students whose GPA is greater than 3.7 from Cornell College student list, ‘Student’, so the GPA is the one to select, ‘Student’ is where the data from, and GPA that is greater than 3.7 is the condition.
Select GPA, Name
From Student
Where GPA > 3.7
When we have one more relation to query and in different table, same attribute name is used, “Relation.Attribute” is used to distinguish. For Example, we have another admission table, ‘Admission’, then we also want to pick up those whose high school GPA is greater than 3.7
Select Student.GPA, Student.Name, Admission.GPA, Admission.Name
From Student, Admission
Where Student.GPA > 3.7 and Admission.GPA > 3.7
It is allow to abbreviate the Relation name.
Select S.GPA, S.Name, A.GPA, A.Name
From Student S, Admission A
Where S.GPA > 3.7 and A.GPA > 3.7
SQL use like %element% to recognize different words. For example, we have many different field in biology, and these different fields’ name may started with or contain “bio”. if we wanted to find out students who learn any field in biology,
Select major
From Student
Where major like %bio%
As it described before, the relation can be abbreviated, that function can also be used in comparison. For example, if we want to pick up students who have the same GPA, we can number those abbreviation.
Select S1.GPA, S1.Name, S2.GPA, S2.Name
From Student S1, Student S2
Where S1.GPA = S2.GPA.
But in the case above, there will be a problem that one’s GPA equals to himself’ s GPA, and same pair of students will be picked up twice in different order, so it is necessary to use the unique KEY to solve this problem, in this case, the unique KEY of each student is their ID.
Select S1.GPA, S1.Name, S1.ID, S2.GPA, S2.Name,S2.ID
From Student S1, Student S2
Where S1.GPA = S2.GPA and S1.ID is > S2.ID
Then if we have different naming in the table for the attributes, like student name for sName, employee name for eName, but in the result table we only want it to display Name, we use “as” to do this job.
Select sName as Name from Student
Union
Select eName as Name from Employee