Microsoft SQL Server - Lesson 27: Topics on Data Relationships

	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 Learning: Creating a One-to-Many Relationship

Start the computer and log in
Launch Microsoft SQL Server
Make sure that, in the Authentication combo box, Windows Authentication is selected and click Connect
In the Object Explorer, make sure the Monson University database created in the previous lesson is there.
Right-click MonsonUniversity and click New Query
To see the list of courses offered, type:
```
SELECT * FROM Academics.Courses;
GO
```
To see the result, on the main menu, click Query -> Execute
Click inside the Query window and press Ctrl + A to select everything
To see the records of the first 10 students, type:
```
SELECT TOP 10 * FROM Admissions.Students;
GO
```
To see the result, on the main menu, click Query -> Execute
Click inside the Query window and press Ctrl + A to select everything

To see the employees of the human resources department, type:

SELECT * FROM Administration.Employees
WHERE DepartmentCode = N'HRMN';
GO

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

Practical Learning: Joining More Than Two Tables

Click in the top section of the Query window where the code was written and press Ctrl + A
To create new records, type the following:
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 Learning: Introducing Joins and Data Analysis

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

Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
Press Delete
Right-click inside the top section of the Query window and click Design Query in Editor...
To get a list of students attendance based on semesters, in the Add Table dialog box, double-click Semesters (admissions)
Double-click Registrations (Admissions)
Double-click Courses (Academics)
Click Close
On the tables, click the check boxes of the following fields:
Semesters(Admissions): Semester
Registrations (Admissions): StudentNumber
Courses (Academics): CourseCode, CourseName, Credits
In the Criteria pane, click the box at the intersection of the Semester and Sort Type
Select Ascending
Click OK
To see the result, on the SQL Editor toolbar, click the Execute SQL button
Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
Press Delete
Right-click inside the top section of the Query window and click Design Query in Editor...
In the Add Table dialog box, double-click Majors (Academics)
Double-click Students (Admissions)
Click Close
On the tables, click the check boxes of the following fields: Major, FirstName, LastName, and AcademicCategory
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
Click OK
On the SQL Editor toolbar, click the Execute SQL button
Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
Press Delete
Right-click inside the top section of the Query window and click Design Query in Editor...
In the Add Table dialog box, double-click Students (Academics)
Double-click Registrations (Admissions)
Double-click Semesters (admissions)
Click Close
On the tables, click the check boxes of the following fields:
- Students (Admissions): StudentNumber, FirstName, LastName
- Semesters (Admissions): Semester
In the Criteria pane, click the box at the intersection of StudentNumber and Filter
Type 24795711
Click OK
To see the result, on the SQL Editor toolbar, click the Execute SQL button
Click in the top section of the Query window where the code was written and press Ctrl + A to select everything
Press Delete
Right-click inside the top section of the Query window and click Design Query in Editor...
To see the list of courses attended by a certain student in various semesters, in the Add Table dialog box, double-click Students (Academics)
Double-click Registrations (Admissions)
Double-click Semesters (admissions)
Double-click Courses (Academics)
Click Close
On the tables, click the check boxes of the following fields:
Students (Admissions): StudentNumber
Semesters (Admissions): Semester
Courses (Academics): CourseCode, CourseName
In the Criteria pane, click the box at the intersection of StudentNumber and Filter
Type 94708257
Click OK
On the SQL Editor toolbar, click the Execute SQL button
Close the Query window
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 Learning: Introducing Parameterized Queries

In the Object Explorer, in the Tables section under MonsonUniversity1, right-click Admissions.Semesters and click Edit Top 200 Rows
On the main menu, click Query Designer -> Pane -> Diagram
On the main menu, click Query Designer -> Pane -> Criteria
On the main menu, click Query Designer -> Pane -> SQL
On the main menu, click Query Designer -> Add Table...
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)
Double-click Courses (Academics)
Click Close
In the Diagram pane, click the check box of SemesterID to remove it
In the Registrations (Admissions) tables, click the check box of StudentNumber
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 Learning: Creating a Parameter

In the Criteria pane, click the box at the intersection of Semester and Filter
Type @SpecifySemester
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 Learning: Executing a Parameterized Statement

To see the result, on the main menu, click Query Designer -> Execute SQL
In the Query Parameters dialog box, under Value, type FALL 2010
Click OK
In the Diagram pane, right-click each of the tables and click Remove
On the main menu, click Query Designer -> Add Table
To let the user see the records of a certain student, in the Add Table dialog box, double-click Students (Academics)
Double-click Registrations (Admissions)
Double-click Semesters (admissions)
Double-click Courses (Academics)
Click Close
In the Students (Admissions) tables, click the check box of StudentNumber
In the Criteria pane, click the first empty box under StudentNumber and type LastName + N', ' + FirstName
Press Tab and type [Student Name]
In the Diagram pane, in the Semesters (Admissions) table, click the check box of Semester
In the Courses (Academics) table, click the check boxes of CourseCode, CourseName, and Credits
In the Criteria pane, click the box at the intersection of StudentNumber and Filter
Type @StdNbr
To see the result, on the main menu, click Query Designer -> Execute SQL
In the Query Parameters dialog box, under Value, type 24795711
Click OK
To see the records of another student, on the main menu, click Query Designer -> Execute SQL
In the Query Parameters dialog box, under Value, type 94708257
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
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)
Double-click Registrations (Admissions)
Double-click Semesters (admissions)
Double-click Courses (Academics)
Click Close
In the Students (Admissions) tables, click the check box of StudentNumber
In the Criteria pane, click the first empty box under StudentNumber and type LastName + N', ' + FirstName
Press Tab and type [Student Name]
In the Diagram pane, in the Semesters (Admissions) table, click the check box Semester
In the Courses (Academics) table, click the check boxes of CourseCode, CourseName, and Credits
In the Criteria pane, click the box at the intersection of StudentNumber and Filter
Type @StdNbr
Still in the Criteria pane, click the box at the intersection of Semester and Filter, type @SpecifySemester
To see the result, on the main menu, click Query Designer -> Execute SQL
In the Query Parameters dialog box, under Value, on the right side of @StdNbr and type 20409220
At the intersection of @SpecifySemester and Value, type FALL 2010
Click OK
To see the records of another student, on the main menu, click Query Designer -> Execute SQL
In the Query Parameters dialog box, under Value, on the right side of @StdNbr and type 94708257
At the intersection of @SpecifySemester and Value, type SUMMER 2010
Click OK
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
To see the records of another student, on the main menu, click Query Designer -> Execute SQL
In the Query Parameters dialog box, under Value, on the right side of @StdNbr and type 71513159
At the intersection of @SpecifySemester and Value, type FALL 2010
Click OK
Close Microsoft SQL Server