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. Start the computer and log in
  2. Open the Monson University 2 file
  3. Select and copy the whole contents of the file
  4. Launch Microsoft SQL Server
  5. Make sure that, in the Authentication combo box, Windows Authentication is selected and click Connect
  6. On the Standard toolbar, click the New Query button
  7. Paste the code in it
  8. On the main menu, click Query -> Execute
  9. In the Object Explorer, expand Databases. If you don't see MonsonUniversity2, right-click Databases and click Refresh
  10. Under MonsonUniversity2, right-click Database Diagram and click New Database Diagram
  11. Read the message box and click Yes
  12. In the Add Table dialog box, click Add continuously until the Tables property page is empty
  13. Click Close
     
    Monson University
  14. Close the diagram
  15. When asked whether you want to save it, click yes
  16. Access the default name and press Enter
  17. Click inside the Query window and press Ctrl + A to select everything
  18. To see the list of courses offered, type: 
    SELECT * FROM Academics.GraduateCourses;
GO
  19. To see the result, on the main menu, click Query -> Execute
  20. Click inside the Query window and press Ctrl + A to select everything
  21. To see the records of the first 10 students, type: 
    SELECT TOP 10 * FROM Admissions.UndergraduateStudents;
GO
  22. To see the result, on the main menu, click Query -> Execute
  23. Click inside the Query window and press Ctrl + A to select everything
  24. To see the employees of the human resources department, type: 
    SELECT * FROM Administration.Employees
WHERE DepartmentCode = N'HRMN';
GO
  25. 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. On the main menu, click Query -> Design Query in Editor...
  2. In the Add Table dialog box, double-click GraduateStudents (Admissions)
  3. Double-click GraduateMajors (Academics)
  4. Click Close
  5. On the tables, click the check boxes of the following fields: FirstName, LastName, and Major
     
    Introducing Joins and Data Analysis
  6. Click OK
  7. To see the result, on the main menu, click Query -> Execute
     
    Monson University
  8. Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
  9. Press Delete
    10.

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

Join

This would produce:

Join

Practical LearningPractical Learning: Analyzing Data With Joins

  1. Right-click inside the top section of the Query window and click Design Query in Editor...
  2. To get a list of students attendance based on semesters, in the Add Table dialog box, double-click Semesters (admissions)
  3. Double-click UndergraduateRegistrations (Admissions)
  4. Double-click UndergraduateCourses (Academics)
  5. Click Close
  6. On the tables, click the check boxes of the following fields:
    Semesters(Admissions): Semester
    UndergraduateRegistrations (Admissions): StudentNumber
    UndergraduateCourses (Academics): CourseCode, CourseName, Credits
  7. In the Criteria pane, click the box at the intersection of the Semester and Sort Type
  8. Select Ascending
     
    Introducing Joins and Data Analysis
  9. Click OK
  10. To see the result, on the SQL Editor toolbar, click the Execute SQL button Execute SQL
    Analyzing Data With Joins
  11. Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
  12. Press Delete
  13. Right-click inside the top section of the Query window and click Design Query in Editor...
  14. In the Add Table dialog box, double-click UndergraduateStudents (Admissions)
  15. Double-click UndergraduateMajors (Academics)
  16. Click Close
  17. On the tables, click the check boxes of the following fields: Major, FirstName, and LastName
  18. To see a list of students who are majoring in CIT, in the Criteria pane, under Filter, click the box that corresponds to Major and type
    Computer Information Technology

    Introducing Joins and Data Analysis
  19. Click OK
  20. On the SQL Editor toolbar, click the Execute SQL button Execute SQL
    Analyzing Data With Joins
  21. Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
  22. Press Delete
  23. Right-click inside the top section of the Query window and click Design Query in Editor...
  24. In the Add Table dialog box, double-click UndergraduateStudents (Academics)
  25. Double-click UndergraduateRegistrations (Admissions)
  26. Double-click Semesters (admissions)
  27. Click Close
  28. On the tables, click the check boxes of the following fields:
    • UndergraduateStudents (Admissions): StudentNumber, FirstName, LastName
    • Semesters (Admissions): Semester
  29. In the Criteria pane, click the box at the intersection of StudentNumber and Filter
  30. Type 24795711

    Introducing Joins and Data Analysis
  31. Click OK
  32. To see the result, on the SQL Editor toolbar, click the Execute SQL button Execute SQL
    Analyzing Data With Joins
  33. Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
  34. Press Delete
  35. Right-click inside the top section of the Query window and click Design Query in Editor...
  36. To see the list of courses attended by a certain student in various semesters, in the Add Table dialog box, double-click UndergraduateStudents (Academics)
  37. Double-click UndergraduateRegistrations (Admissions)
  38. Double-click Semesters (admissions)
  39. Double-click UndergraduateCourses (Academics)
  40. Click Close
  41. On the tables, click the check boxes of the following fields:
    UndergraduateStudents (Admissions): StudentNumber, FirstName, and LastName
    Semesters (Admissions): Semester
    UndergraduateCourses (Academics): CourseCode, CourseName, and Credits
  42. In the Criteria pane, click the box at the intersection of StudentNumber and Filter
  43. Type 94708257 
    Analyzing Data With Joins
  44. Click OK
  45. On the SQL Editor toolbar, click the Execute SQL button Execute SQL 
    
		Analyzing Data With Joins
  46. Close the Query window
  47. When asked whether you want to save, click No
 
 
 

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 MonsonUniversity2, right-click Admissions.Semesters 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. On the main menu, click Query Designer -> Add Table...
  6. 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 UndergraduateRegistrations (Admissions)
  7. Double-click UndergraduateCourses (Academics)
  8. Click Close
  9. In the Diagram pane, click the check box of SemesterID to remove it
  10. In the UndergraduateRegistrations (Admissions) tables, click the check box of StudentNumber
  11. In the UndedrgraduateCourses (Academics) tables, click the check boxes of CourseCode, CourseName, and Credits

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 window. 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
  3. In the SQL pane, delete TOP (200)

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 2010 
    Executing a Parameterized Statement
  3. Click OK
    Introducing Parameterized Queries
  4. In the Diagram pane, right-click each of the tables and click Remove
  5. On the main menu, click Query Designer -> Add Table
  6. To let the user see the records of a certain student, in the Add Table dialog box, double-click GraduateStudents (Academics)
  7. Double-click GraduateRegistrations (Admissions)
  8. Double-click Semesters (admissions)
  9. Double-click GraduateCourses (Academics)
  10. Click Close
  11. In the GraduateStudents (Admissions) tables, click the check box of StudentNumber
  12. In the Criteria pane, click the first empty box under StudentNumber and type
    LastName + N', ' + FirstName
  13. Press Tab and type [Student Name]
  14. In the Diagram pane, in the Semesters (Admissions) table, click the check box of Semester
  15. In the GraduateCourses (Academics) table, click the check boxes of CourseCode, CourseName, and Credits
  16. In the Criteria pane, click the box at the intersection of StudentNumber and Filter
  17. Type @StdNbr
    Parameterized Queries
  18. To see the result, on the main menu, click Query Designer -> Execute SQL
  19. In the Query Parameters dialog box, under Value, type 24795711
  20. Click OK 
    Parameterized Queries
  21. To see the records of another student, on the main menu, click Query Designer -> Execute SQL
  22. In the Query Parameters dialog box, under Value, type 31741957 
    Parameterized Queries
  23. To let a user specify a student and a semester to view records, in the Criteria pane, click the box at the intersection of Semester and Filter, type @SpecifySemester
  24. To test it, right-click the Diagram pane and click Execute SQL
  25. Make sure 31741957 is specified for the @StdNbr parameter.
    At the intersection of @SpecifySemester and Value, type FALL 2010
    Parameterized Queries
  26. Click OK
  27. Close Microsoft SQL Server
 
 
   
 
Previous Copyright © 2011 Fianga.com Next