Module 4 Explanation | Access Cram Kit – Miami

As we did with Module 3 and Design View queries, it is crucial to go phrase-by-phrase with SQL queries to ensure that you get each problem correct in the shortest amount of time!

This question is one of the tough ones to go into phrase-by-phrase right away.

How are we supposed to know which table's LanguageID field we're supposed to use based on this?

- Create an alphabetical list by language that includes the language, curriculum number, and instructor for all classes during the 2022S2.

If you're ever faced with a situation like this, just glance over the rest of the bullet point. Chances are, you'll find your answer.

In our case, when we take a glance, we can find the "curriculum" keyword used. That means we'll use the Curriculum table.

- Create an alphabetical list by language that includes the language, curriculum number, and instructor for all classes during the 2022S2.

So, let's go ahead and start our query by referencing the LanguageID field of the Curriculum table in a SELECT statement.

SELECT Curriculum.LanguageID
...

For our FROM statement, we're going to declare that we're pulling data from the Curriculum table.

SELECT Curriculum.LanguageID
FROM Curriculum

Now, let's make this alphabetical by language by including an ORDER BY statement...

SELECT Curriculum.LanguageID
FROM Curriculum
ORDER BY ...

...and sorting by Curriculum.LanguageID alphabetically (which is the default of ORDER BY).

SELECT Curriculum.LanguageID
FROM Curriculum
ORDER BY Curriculum.LanguageID

For the next phrase...

- Create an alphabetical list by language that includes the language, curriculum number, and instructor for all classes during the 2022S2.

...we need to include the CurriculumNumber field from the Curriculum table.

To do so, place it in the SELECT statement like so:

SELECT Curriculum.LanguageID, Curriculum.CurriculumNumber
FROM Curriculum
ORDER BY Curriculum.LanguageID

For the next phrase...

- Create an alphabetical list by language that includes the language, curriculum number, and instructor for all classes during the 2022S2.

...we're going to place the Instructor field from the Class table into our SELECT statement.

Problem is... we don't currently have access to that table. We only have access to the Curriculum table based on our FROM statement.

Therefore, we can get access to the Instructor field by directly joining to the Class table like so:

How can we code this though? We can start by adding a blank INNER JOIN statement to our FROM statement.

SELECT Curriculum.LanguageID, Curriculum.CurriculumNumber
FROM Curriculum INNER JOIN ... ON ... = ...
ORDER BY Curriculum.LanguageID

What's going to go in the first "..."? The Class table, since that's what we're joining to Curriculum.

SELECT Curriculum.LanguageID, Curriculum.CurriculumNumber
FROM Curriculum INNER JOIN Class ON ... = ...
ORDER BY Curriculum.LanguageID

For the second and third "...", we need to ask ourselves: What primary/foreign keys are we going to use to connect Class to Curriculum?

The CurriculumID field in the Class table...

SELECT Curriculum.LanguageID, Curriculum.CurriculumNumber
FROM Curriculum INNER JOIN Class ON Class.CurriculumID = ...
ORDER BY Curriculum.LanguageID

...and the CurriculumID field in the Curriculum table.

SELECT Curriculum.LanguageID, Curriculum.CurriculumNumber
FROM Curriculum INNER JOIN Class ON Class.CurriculumID = Curriculum.CurriculumID
ORDER BY Curriculum.LanguageID

Now that we have access to the Class table, we're going to include the Instructor field in our SELECT statement.

SELECT Curriculum.LanguageID, Curriculum.CurriculumNumber, Class.Instructor
FROM Curriculum INNER JOIN Class ON Class.CurriculumID = Curriculum.CurriculumID
ORDER BY Curriculum.LanguageID

To wrap up this query, let's look at the final phrase:

- Create an alphabetical list by language that includes the language, curriculum number, and instructor for all classes during the 2022S2.

Here, we need to filter for Semester field values that are equal to 2022S2. Since Semester is a field, we're going to use a WHERE statement...

SELECT Curriculum.LanguageID, Curriculum.CurriculumNumber, Class.Instructor
FROM Curriculum INNER JOIN Class ON Class.CurriculumID = Curriculum.CurriculumID
WHERE ...
ORDER BY Curriculum.LanguageID

...and state that the Semester field...

SELECT Curriculum.LanguageID, Curriculum.CurriculumNumber, Class.Instructor
FROM Curriculum INNER JOIN Class ON Class.CurriculumID = Curriculum.CurriculumID
WHERE Semester ...
ORDER BY Curriculum.LanguageID

...must equal "2022S2".

SELECT Curriculum.LanguageID, Curriculum.CurriculumNumber, Class.Instructor
FROM Curriculum INNER JOIN Class ON Class.CurriculumID = Curriculum.CurriculumID
WHERE Semester = "2022S2"
ORDER BY Curriculum.LanguageID

Don't forget to include a semicolon to wrap up this query!

SELECT Curriculum.LanguageID, Curriculum.CurriculumNumber, Class.Instructor
FROM Curriculum INNER JOIN Class ON Class.CurriculumID = Curriculum.CurriculumID
WHERE Semester = "2022S2"
ORDER BY Curriculum.LanguageID;

Run the query and assert you got 31 records...

...then close and save it as Q11_SQL_Classes2022S2.

To start this query, let's go phrase-by-phrase on the first bullet point.

- Create a list of all the students who are from Ohio and who are studying Access (ACS). Display all fields associated with a student and sort by graduation year in ascending order.

For now, we're just going to place "*" in our SELECT statement. This means that our query will currently show all fields belonging to the Student table. We can modify this later when we learn more about what fields this question wants placed in the output.

SELECT *
...

For our FROM statement, we must declare that we're pulling from the Student table.

SELECT *
FROM Student

- Create a list of all the students who are from Ohio and who are studying Access (ACS). Display all fields associated with a student and sort by graduation year in ascending order.

To filter for only students from Ohio, let's start with a WHERE statement (since we're filtering based on a field value).

SELECT *
FROM Student
WHERE ...

Next, we must filter for students who have a State field value...

SELECT *
FROM Student
WHERE Student.State ...

...is equal to "OH" (for Ohio).

SELECT *
FROM Student
WHERE Student.State = "OH"

- Create a list of all the students who are from Ohio and who are studying Access (ACS). Display all fields associated with a student and sort by graduation year in ascending order.

Before we add the filtering for students studying Access, we must first add the AND keyword to our WHERE statement.

SELECT *
FROM Student
WHERE Student.State = "OH" AND ...

Why? Because we're filtering for where students are from Ohio and are studying Access.

Now, let's add the filtering for Access. We're looking for students who have a LanguageID...

SELECT *
FROM Student
WHERE Student.State = "OH" AND Student.LanguageID ...

...equal to "ACS" (meaning that they study Access).

SELECT *
FROM Student
WHERE Student.State = "OH" AND Student.LanguageID = "ACS"

- Create a list of all the students who are from Ohio and who are studying Access (ACS). Display all fields associated with a student and sort by graduation year in ascending order.

We're already doing this with the "*" in our SELECT statement!

SELECT *
FROM Student
WHERE Student.State = "OH" AND Student.LanguageID = "ACS"

- Create a list of all the students who are from Ohio and who are studying Access (ACS). Display all fields associated with a student and sort by graduation year in ascending order.

For this part of the query, we first need to add an ORDER BY statement to our query. That way we can sort our results.

SELECT *
FROM Student
WHERE Student.State = "OH" AND Student.LanguageID = "ACS"
ORDER BY ...

Next, we want to sort by the GraduationYear field in the Student table...

SELECT *
FROM Student
WHERE Student.State = "OH" AND Student.LanguageID = "ACS"
ORDER BY Student.GraduationYear

...in ascending order (which is the default for ORDER BY statements, so we're all set!)

Don't forget to add a semicolon to the end of your query!

SELECT *
FROM Student
WHERE Student.State = "OH" AND Student.LanguageID = "ACS"
ORDER BY Student.GraduationYear;

When we plug this into Access and run it, we should get 11 results.

Now close and save the query as Q12_SQL_OhioAccessStudents.