Options on Joins

Joining More Than Two Tables

So far, our join statements involved only two tables. Actually, you can use more than that. The basic formula to join three tables is:

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.

Practical Learning: Creating a One-to-Many Relationship

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

Introduction to Joins and Data Analysis

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.

Practical Learning: Introducing Joins and Data Analysis

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
    
   

Using a Criterion

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
     
    

A Parameterized Query

Introduction

In all conditional statements we have used so far, we knew the value to check against a column. In reality, you can create a query without specifying the value to check. A query is referred to as parameterized if it would expect an external value to perform its comparison. The statement starts like a normal SELECT operation with a WHERE clause. Here is an example:

SELECT * FROM Students WHERE Sex = N'male';

Instead of specifying the value of the column(s) in the WHERE clause, you can wait for the user to do so.

Practical Learning: Introducing Parameterized Queries

1. In the Object Explorer, in the Tables section under MonsonUniversity1, right-click Catalogs.UndergraduateCourses and click Edit Top 200 Rows
2. On the main menu, click Query Designer -> Pane -> Diagram
3. On the main menu, click Query Designer -> Pane -> Criteria
4. On the main menu, click Query Designer -> Pane -> SQL
5. In the SQL pane, delete TOP (200)
6. On the main menu, click Query Designer -> Add Table...
7. To prepare to let a user select a semester to get a list of students attendance based on that semester, in the Add Table dialog box, double-click UndergraduateSchedules (Catalogs)
8. Double-click Semesters (Academics)
9. Double-click UndergraduateRegistrations (Admissions)
10. Click Close
11. In the Diagram pane, click the check boxes of Credits, CourseDescription, Prerequisites, and Notes to remove them
12. In the Semesters (Academics) table, click the check box of Semester
13. In the UndergraduateRegistrations (Admissions) table, click the check box of StudentNumber
14. In the UndedrgraduateSchedules (Catalogs) table, click the check boxes of LocationCode, RoomNumber, StartDate, StartTime, EndDate, EndTime, Weekdays, and TeacherNumber
15. In the Criteria pane, move the columns to get them in the following order:
     

    Column	Alias
    StudentNumber	Student #
    Semester
    CourseCode	Course
    CourseName	Course Name
    Weekdays
    StartDate	Start Date
    EndDate	End Date
    StartTime	Start Time
    EndTime	End Time
    LocationCode	Building
    RoomNumber	Room #
    TeacherNumber	Teacher #

16. To execute, on the main menu, click Query Designer -> Execute SQL

Creating a Parameterized Query

To visually create a parameterized statement, in the Object Explorer, right-click the table and click Edit Top 200 Rows. In the Criteria pane, click the box at the intersection of the column and Filter. Type @ followed by a variable name.

To create a parameterized query with code, open a Query Editor. Start a normal SELECT expression that contains a condition. In the WHERE expression, replace the value with the name of a variable starting with @. Here is an example:

SELECT StudentNumber, LastName, FirstName, City, State
FROM Students
WHERE StudentNumber = @StdNbr;

Practical Learning: Creating a Parameter

1. In the Criteria pane, click the box at the intersection of Semester and Filter
2. Type @SpecifySemester

Executing a Parameterized Statement

After creating a parameterized statement, you can test and/or use it. When you run the query, the SQL interpreter would request a value for the column. When you execute the statement, a dialog box would come up, asking you to enter a value for the filtered field. You can then type the appropriate value and click OK (or press Enter).

Practical Learning: Executing a Parameterized Statement

1. To see the result, on the main menu, click Query Designer -> Execute SQL
2. In the Query Parameters dialog box, under Value, type FALL 2012

3. Click OK
   
   
4. On the main menu, click Query Designer -> Add Table
5. To get the name of the student, in the Add Table dialog box, double-click UndergraduateStudents (Admissions)
6. Click Close
7. In the Criteria pane, click the first empty box under Column and type
   LastName + N', ' + FirstName
8. Press Tab and type [Student Name]
9. In the Criteria pane, move the new Student Name column and position it under StudentNumber
10. Still in the Criteria pane, click the box at the intersection of StudentNumber and Filter
11. Type @SpecifyStudentNumber
12. To test the new version of the parameterized query, on the main menu, click Query Designer -> Execute SQL
13. At the intersection of @SpecifySemester and Value, type SUMMER 2013
14. At the intersection of @SpecifyStudentNumber and Value, type 18073572

15. Click OK

16. To see another student's schedule for a different semester, on the main menu, click Query Designer -> Execute SQL
17. At the intersection of @SpecifySemester and Value, type FALL 2012
18. At the intersection of @SpecifyStudentNumber and Value, type 24795711
19. Click OK
    
    Notce a conflict in the schedule. The student registered to take two courses at the same time in the same weekdays of the same semester
20. Close Microsoft SQL Server

Previous	Copyright © 2009-2022, FunctionX	Next