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. Launch Microsoft SQL Server
  3. Make sure that, in the Authentication combo box, Windows Authentication is selected and click Connect
  4. In the Object Explorer, make sure the Monson University database created in the previous lesson is there.
    Right-click MonsonUniversity and click New Query
  5. To see the list of courses offered, type:
    SELECT * FROM Academics.Courses;
    GO
  6. To see the result, on the main menu, click Query -> Execute
  7. Click inside the Query window and press Ctrl + A to select everything
  8. To see the records of the first 10 students, type:
    SELECT TOP 10 * FROM Admissions.Students;
    GO
  9. To see the result, on the main menu, click Query -> Execute
  10. Click inside the Query window and press Ctrl + A to select everything
  11. To see the employees of the human resources department, type:
    SELECT * FROM Administration.Employees
    WHERE DepartmentCode = N'HRMN';
    GO
  12. To see the result, on the main menu, click Query -> Execute

Practical LearningPractical Learning: Joining More Than Two Tables

  1. Click in the top section of the Query window where the code was written and press Ctrl + A
  2. To create new records, type the following:
  3. To execute, press F5

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 in the top section of the Query window where the code was written and press Ctrl + A to select everything
  2. Press Delete
  3. On the main menu, click Query -> Design Query in Editor...
  4. In the Add Table dialog box, double-click Majors (Academics)
  5. Double-click Students (Admissions)
  6. Click Close
  7. On the tables, click the check boxes of the following fields: Major, FirstName, LastName, and AcademicCategory
     
    Introducing Joins and Data Analysis
  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

Join

This would produce:

Join

Practical LearningPractical Learning: Analyzing Data With Joins

  1. Click in the top section of the Query window 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 window 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 (admissions)
  5. Double-click Registrations (Admissions)
  6. Double-click Courses (Academics)
  7. Click Close
  8. On the tables, click the check boxes of the following fields:
    Semesters(Admissions): Semester
    Registrations (Admissions): StudentNumber
    Courses (Academics): CourseCode, CourseName, Credits
  9. In the Criteria pane, click the box at the intersection of the Semester and Sort Type
  10. Select Ascending
     
    Introducing Joins and Data Analysis
  11. Click OK
  12. To see the result, on the SQL Editor toolbar, click the Execute SQL button Execute SQL
     
    Analyzing Data With Joins
  13. Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
  14. Press Delete
  15. Right-click inside the top section of the Query window and click Design Query in Editor...
  16. In the Add Table dialog box, double-click Majors (Academics)
  17. Double-click Students (Admissions)
  18. Click Close
  19. On the tables, click the check boxes of the following fields: Major, FirstName, LastName, and AcademicCategory
  20. 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
  21. Click OK
  22. On the SQL Editor toolbar, click the Execute SQL button Execute SQL
     
    Analyzing Data With Joins
  23. Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
  24. Press Delete
  25. Right-click inside the top section of the Query window and click Design Query in Editor...
  26. In the Add Table dialog box, double-click Students (Academics)
  27. Double-click Registrations (Admissions)
  28. Double-click Semesters (admissions)
  29. Click Close
  30. On the tables, click the check boxes of the following fields:
    • Students (Admissions): StudentNumber, FirstName, LastName
    • Semesters (Admissions): Semester
  31. In the Criteria pane, click the box at the intersection of StudentNumber and Filter
  32. Type 24795711
  33. Click OK
  34. To see the result, on the SQL Editor toolbar, click the Execute SQL button Execute SQL
     
    Analyzing Data With Joins
  35. Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
  36. Press Delete
  37. Right-click inside the top section of the Query window and click Design Query in Editor...
  38. To see the list of courses attended by a certain student in various semesters, in the Add Table dialog box, double-click Students (Academics)
  39. Double-click Registrations (Admissions)
  40. Double-click Semesters (admissions)
  41. Double-click Courses (Academics)
  42. Click Close
  43. On the tables, click the check boxes of the following fields:
    Students (Admissions): StudentNumber
    Semesters (Admissions): Semester
    Courses (Academics): CourseCode, CourseName
  44. In the Criteria pane, click the box at the intersection of StudentNumber and Filter
  45. Type 94708257
     
    Analyzing Data With Joins
  46. Click OK
  47. On the SQL Editor toolbar, click the Execute SQL button Execute SQL
     
    Analyzing Data With Joins
  48. Close the Query window
  49. 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 MonsonUniversity1, 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 Registrations (Admissions)
  7. Double-click Courses (Academics)
  8. Click Close
  9. In the Diagram pane, click the check box of SemesterID to remove it
  10. In the Registrations (Admissions) tables, click the check box of StudentNumber
  11. In the Courses (Academics) tables, click the check boxes of CourseCode and CourseName

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 Students (Academics)
  7. Double-click Registrations (Admissions)
  8. Double-click Semesters (admissions)
  9. Double-click Courses (Academics)
  10. Click Close
  11. In the Students (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 Courses (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
  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 94708257
     
    Executing a Parameterized Statement
  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
     
    Executing a Parameterized Statement
  24. To let the user see the records of a certain student in a specify semester, in the Add Table dialog box, double-click Students (Academics)
  25. Double-click Registrations (Admissions)
  26. Double-click Semesters (admissions)
  27. Double-click Courses (Academics)
  28. Click Close
  29. In the Students (Admissions) tables, click the check box of StudentNumber
  30. In the Criteria pane, click the first empty box under StudentNumber and type LastName + N', ' + FirstName
  31. Press Tab and type [Student Name]
  32. In the Diagram pane, in the Semesters (Admissions) table, click the check box Semester
  33. In the Courses (Academics) table, click the check boxes of CourseCode, CourseName, and Credits
  34. In the Criteria pane, click the box at the intersection of StudentNumber and Filter
  35. Type @StdNbr
  36. Still in the Criteria pane, click the box at the intersection of Semester and Filter, type @SpecifySemester
  37. To see the result, on the main menu, click Query Designer -> Execute SQL
  38. In the Query Parameters dialog box, under Value, on the right side of @StdNbr and type 20409220
  39. At the intersection of @SpecifySemester and Value, type FALL 2010
     
    Query Parameters
  40. Click OK
  41. To see the records of another student, on the main menu, click Query Designer -> Execute SQL
  42. In the Query Parameters dialog box, under Value, on the right side of @StdNbr and type 94708257
  43. At the intersection of @SpecifySemester and Value, type SUMMER 2010
  44. Click OK
     
    Executing a Parameterized Statement
  45. Since we know the student and the semester we are querying, there is no reason to show it in the result.
    In the Criteria pane, in the Output column, click the check boxes of StudentNumber and Semester to remove them
  46. To see the records of another student, on the main menu, click Query Designer -> Execute SQL
  47. In the Query Parameters dialog box, under Value, on the right side of @StdNbr and type 71513159
  48. At the intersection of @SpecifySemester and Value, type FALL 2010
  49. Click OK
  50. Close Microsoft SQL Server
 
 
   
 

Previous Copyright © 2011 FunctionX.com Next