I am taking a beginner's course in SQL, and have been playing around with some queries. One thing I don't really understand is how to "properly" query multiple tables, that is; compare values from two or more tables.
For instance, I have a table called Student, holding the username, name, date of birth, and major (just the code. For instance, CS would stand for "Computer Science") of a particular person. I chose to make the username a primary key.
I also have another table called Major holding the major code (such as CS) as a primary key, and the entire major name. For instance, "CS" = "Computer Science", NS = "Neuroscience", etc.
Now, suppose I want to find the name of a major, given a student's username. Following is the imagined pseudocode for this query:
1) In the Student table: Provided the username, check what the major of that particular person is.
select majorcode from Student where username='aUserName'; Doing so correctly gives me the major code.
2) In the Major table: Find the title of the major provided the code.
select majorTitle from Major where majorcode='theMajorCode'; Combinded, I write:
select majorTitle from Major where majorcode=(select majorcode from Student where username='aUserName'); However, now suppose I want BOTH the title of the major (from the Major table) as well as the name of the student (from the Student table).
Any advice on how to do this?
