SELECT WhatColumn(s) FROM FirstTable
FirstJoinType SecondTable ON Condition1
SecondJoinType ThirdTable ON Condition2

You start the expression by joining the first to the second table, which means that both tables should share a column in a primary key-foreign key type of relationship. In the same way, you can create the second join. of course, the second and the third table should have a common column. In most cases, there should be a column that all three tables share. Most of the time, the relationship starts with a primary column from the first table. That column is then represented as a foreign key in the other two tables.

1. Open the Monson University DDL file
2. Click inside the document, press Ctrl + A and press Ctrl + C to copy
3. Start Microsoft SQL Server and click Connect
4. On the Standard toolbar, click the New Query button
5. Click inside the empty window and press Ctrl + V
6. To execute, on the SQL Editor toolbar, click the Execute button
7. Open the Monson University DML file
8. Click inside the document, press Ctrl + A and press Ctrl + C
9. Click inside the empty window and press Ctrl + V
10. To execute, on the SQL Editor toolbar, click the Execute button
11. Click inside the Query Editor and press Ctrl + A to select everything
12. To create a few shortcut names, type the following code:

    USE MonsonUniversity1;
    GO
    
    CREATE SYNONYM Sems FOR Academics.Semesters;
    GO
    CREATE SYNONYM Empls FOR Administration.Employees;
    GO
    CREATE SYNONYM Studs FOR Admissions.UndergraduateStudents;
    GO
    CREATE SYNONYM Studs FOR Admissions.UndergraduateStudents;
    GO
    CREATE SYNONYM Regs FOR Admissions.UndergraduateRegistrations;
    GO
    CREATE SYNONYM Ugs FOR Catalogs.UndergraduateSchedules;
    GO
    CREATE SYNONYM Ugc FOR Catalogs.UndergraduateCourses;
    GO

13. To execute, press F5
14. In the Object Explorer, expand Databases. If you don't see MonsonUniversity1, right-click Databases and click Refresh
15. Under MonsonUniversity1, right-click Database Diagram and click New Database Diagram
16. Read the message box and click Yes
17. In the Add Table dialog box, click Add continuously until the Tables property page is empty
18. Click Close

19. Close the diagram
20. When asked whether you want to save it, click yes
21. Set the name to dgmMonsonUniversity
22. Click OK
23. Click inside the Query Editor and press Ctrl + A to select everything
24. To see the list of courses offered, type:

    USE MonsonUniversity1;
    GO
    SELECT * FROM Catalogs.UndergraduateCourses;
    GO

25. To see the result, on the main menu, click Query -> Execute
26. Click inside the Query Editor and press Ctrl + A to select everything
27. To see the records of the first 10 students, type:

    USE MonsonUniversity1;
    GO
    SELECT TOP 10 * FROM Admissions.UndergraduateStudents;
    GO

28. To see the result, on the main menu, click Query -> Execute
29. Click inside the Query Editor and press Ctrl + A to select everything
30. To see the employees of the human resources department, type:

    USE MonsonUniversity1;
    GO
    SELECT * FROM Administration.Employees
    WHERE DepartmentCode = N'ADMN';
    GO

31. To see the result, on the main menu, click Query -> Execute

As demonstrated so far and in previous lessons, the main reason for creating queries is to isolate records. This is done using conditions and criteria. Joins enhance this capability because they allow you to consider records from different tables and include them in a common SQL statement.

In the joins we have created so far, we considered all records and let the database engine list them using only the rules of joins built-in the SQL. To make such a list more restrictive, you can pose your own conditions that should be respected to isolate records, like a funnel. As done in previous lessons, to include a criterion in a SELECT statement, you can create a WHERE clause.

1. Click inside the Query Editor and press Ctrl + A
2. Press Delete
3. On the main menu, click Query -> Design Query in Editor...
4. In the Add Table dialog box, double-click UndergraduateStudents (Admissions)
5. Double-click UndergraduateMajors (Academics)
6. Click Close
7. On the tables, click the check boxes of the following fields: FirstName, LastName, and Major
    
   
8. Click OK
9. To see the result, on the main menu, click Query -> Execute
    
   

To create a criterion in a query you create from the SQL Server Management Studio, first select a column to display it in the Grid section. When creating the query, to specify a criterion, in the Criteria box corresponding to the column, type the condition using any of the operators we reviewed in previous lessons. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName,
       Genders.GenderID, Genders.Gender
FROM   Persons LEFT OUTER JOIN
       Genders ON Persons.GenderID = Genders.GenderID
WHERE  Genders.Gender = N'female';
GO 

This would produce:

Practical Learning: Analyzing Data With Joins

1. (At this time, or in the real world, we would simply edit the SQL statement in the Query Editor but, for the sake of learning and to let the database engine do the work for us, we will visually recreate the query).
   Click in the top section of the Query Editor where the code was written and press Ctrl + A to select everything
2. Press Delete
3. Right-click inside the top section of the Query Editor and click Design Query in Editor...
4. To get a list of students attendance based on semesters, in the Add Table dialog box, double-click Semesters (Academics)
5. Double-click UndergraduateRegistrations (Admissions)
6. Double-click UndergraduateCourses (Catalogs)
7. Double-click UndergraduateSchedules (Catalogs)
8. Click Close
9. On the tables, click the check boxes of the following fields:
   Semesters(Admissions): Semester
   UndergraduateRegistrations (Admissions): StudentNumber
   UndergraduateCourses (Catalogs): CourseCode, CourseName, Credits
10. In the Criteria pane, click the box at the intersection of the Semester and Sort Type
11. Select Ascending
     
    

12. Click OK
13. To see the result, on the SQL Editor toolbar, click the Execute SQL button
    

14. Click inside the Query Editor and press Ctrl + A
15. Press Delete
16. Right-click inside the Query Editor and click Design Query in Editor...
17. In the Add Table dialog box, double-click UndergraduateStudents (Admissions)
18. Double-click UndergraduateMajors (Academics)
19. Click Close
20. On the tables, click the check boxes of the following fields: FirstName, LastName, and Major
21. To see a list of students who are majoring in CNS, in the Criteria pane, under Filter, click the box that corresponds to Major and type
    N'Computer Networks and Security'
    
    
22. Click OK
23. On the SQL Editor toolbar, click the Execute SQL button
    

24. Click in the top section of the Query Editor and press Ctrl + A
25. Press Delete
26. To see the list of courses attended by a certain student in various semesters, right-click inside the top section of the Query Editor and click Design Query in Editor...
27. In the Add Table dialog box, double-click UndergraduateStudents (Admissions)
28. Double-click UndergraduateRegistrations (Admissions)
29. Double-click Semesters (Acadmics)
30. Double-click UndergraduateSchedules (Catalogs)
31. Double-click UndergraduateCourses (Catalogs)
32. Click Close
33. On the tables, click the check boxes of the following fields:
    • UndergraduateStudents (Admissions): StudentNumber, FirstName, and LastName
    • Semesters (Admissions): Semester
    • UndergraduateCourses (Catalogs): CourseCode, CourseName, and Credits
34. In the Criteria pane, click the box at the intersection of StudentNumber and Filter
35. Type 24795711

36. Click OK
37. To see the result, on the SQL Editor toolbar, click the Execute SQL button
    

38. Click inside the Query Editor and press Ctrl + A
39. To create another version of the above SQL statement, type the following code:

    SELECT StudentNumber [Student #],
           FirstName N'First Name',
           LastName "Last Name"
    FROM   Studs
    WHERE  Studs.StudentNumber = N'24795711';
    GO
    SELECT Sems.Semester,
           Ugc.CourseCode,
           Ugc.CourseName,
           Ugc.Credits
    FROM   Studs
    INNER JOIN Regs ON Regs.StudentNumber = Studs.StudentNumber
    INNER JOIN Ugs  ON Ugs.UndergraduateScheduleID = Regs.UndergraduateScheduleID
    INNER JOIN Sems ON sems.SemesterID = Ugs.SemesterID
    INNER JOIN Ugc  ON Ugc.CourseCode = Ugs.CourseCode
    WHERE Studs.StudentNumber = N'24795711';
    GO

40. To execute, press F5