Home

Topics on Data Relationships

 

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 LearningPractical 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 New Query
  5. Click inside the empty window and press Ctrl + V
  6. To execute, on the SQL Editor toolbar, click the Execute button Execute
  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 Execute
  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
    Monson University
    		  
    		  
  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 LearningPractical 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
     
    Introducing Joins and Data Analysis
  8. Click OK
  9. To see the result, on the main menu, click Query -> Execute
     
    Monson University

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:

Join

Practical LearningPractical 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
     
    Introducing Joins and Data Analysis
    
    
  12. Click OK
  13. To see the result, on the SQL Editor toolbar, click the Execute SQL button Execute SQL
    Analyzing Data With Joins
    
    
  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'

    Introducing Joins and Data Analysis
  22. Click OK
  23. On the SQL Editor toolbar, click the Execute SQL button Execute SQL
    Analyzing Data With Joins
    
    
  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
    Introducing Joins and Data Analysis
    
    
  36. Click OK
  37. To see the result, on the SQL Editor toolbar, click the Execute SQL button Execute SQL
    Analyzing Data With Joins
    
    
  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
     
    Students
 
 
 

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 LearningPractical 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
    Introducing Parameterized Queries
    
    

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 LearningPractical 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 LearningPractical 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
    Executing a Parameterized Statement
    
    
  3. Click OK

    Introducing Parameterized Queries
  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
    Executing a Parameterized Statement
    
    
  15. Click OK
    Parameterized Queries
    
    
  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
    Parameterized Queries
    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-2013 FunctionX Next