Microsoft SQL Server - Lesson 27: Introduction to Data Joins

Data Relationships Fundamentals

Introduction

A relational database is a system of two or more lists that share records. The relationship can be explicit or implicit.

A relationship is explicit if an obvious and clear relationship has been established between two lists (tables or views). We already know how to explicitly create a relationship, which is done using primary keys and foreign keys. When an explicit relationship has been created, every time a value is created (or added) to a foreign key in the child table, that value must be found in the primary key of the parent table. Otherwise, the value would be rejected.

An implicit relationship is one you make up or figure out. The relationship is not formally established between the tables but in one table (considered a child table), there is a field that represents the records of another table (considered the parent table).

Let's consider an example. We have a small business named College Park Auto Repair whose main job is to fix cars. On each invoice, a customer wants to see what parts were used (ou bought and added to the car; in other words, the parts the customer was charged for) and a list of the jobs that were performed. To start the database, we create a table for repair orders. It contains the customer's name, his address, the information about the car, and a description of the problem. Since the invoice must include the list of parts that were used, we are tempted to create fields for the parts. Let's say we create 5 fields. If a repair order includes only 1 or 2 parts (or less than 5), there would be empty fields. This is a waste of resources (waste of computer memory) and it is not professional. Since some repairs can include more than 5 parts, we are tempted to create 8 or 10 (or more) empty fields. Again, we would be confronted with waste of computer memory on orders that use fewer parts. The alternative, probably more professional, is to create a table for parts. The repair shop does not sell auto parts. The company orders them from separate companies, and each order depends on the job to perform on the car. This means that the auto repair company doesn't keep an inventory of parts; it only needs the name of a part and its price to charge to the customer. Therefore, each record of our table of parts will have a part name and its price. Since each part is used for a particular repair order, the receipt number will also be entered in the record.

College Park Auto Repair is a fictional company that fixes cars.

Customers bring cars that give them problems or just need a mechanic to look at, or to look over, something. Of course, a car has to be identified with the make, the model, the year, and the owner. The customer must also specify what the concern is. The company would then (try to) fix the car. After fixing the car, the company must create an invoice; we will call it a repair order. Such an order contains the list of parts, if any, that were used to fix the car, and a list of the jobs that were performed on the car.

We will create a database that can assist the College Park Auto Repair company to manage its business. The database will have the following tables:

Repair Orders: This table holds the information that identifies the car to repair and the owner of the car. The table has the following information:
- Receipt Number: This is a unique number (the primary key) that identifies each repair order
- Customer information
- Car Information
- Total Parts: This is the amount to pay for parts. In reality, the repair company buys parts from other companies and the customer will have to reimburse the company. The Total Parts column gets its value from the Parts Used table (next) where the total depends on the receipt number
- Total Jobs: This the total of the jobs that were performed on the repair order
- Repair Summary: This includes information other than mentioned above, such as recommendations to customers
Parts Used: This is the list of parts that are used for the repairs. To identify the parts used for a particular repair order, each part record has:
- The receipt number of the order repair for which the part was used
- The cost of the part
Jobs Performed: This is the list of jobs done to repair the car. Each job has a description and a cost

Practical Learning: Introducing Data Relationships/p>

Start the computer and log in
Launch Microsoft SQL Server and click Connect
Right-click the name of the server and click New Query

To start a new database, type the following code:

CREATE DATABASE CollegeParkAutoRepair1;
GO

USE CollegeParkAutoRepair1;
GO

CREATE SCHEMA Management;
GO
CREATE SCHEMA Inventory;
GO

CREATE TABLE Management.RepairOrders
(
	ReceiptNumber int identity(100001, 1) not null,
	CustomerName nvarchar(60),
	PhoneNumber nvarchar(32),
	Address nvarchar(50),
	City nvarchar(40),
	State nvarchar(40),
	ZIPCode nvarchar(20),
	Make nvarchar(30),
	Model nvarchar(32),
	CarYear int,
	ProblemDescription nvarchar(MAX),
	TotalParts money,
	TotalLabor money,
	TaxRate decimal(6, 2) DEFAULT 7.75,
	TaxAmount AS (TotalParts + TotalLabor) * TaxRate / 100,
	OrderTotal AS TotalParts + TotalLabor +  ((TotalParts + TotalLabor) * TaxRate / 100),
	Notes nvarchar(MAX),   
	CONSTRAINT PK_RepairOrders PRIMARY KEY(ReceiptNumber)
);
GO
CREATE TABLE Inventory.PartsUsed
(
	PartID int identity(1, 1) not null,
	ReceiptNumber int not null,
	PartName nvarchar(50) not null,
	UnitPrice money,
	Quantity smallint,
     	SubTotal AS (UnitPrice * Quantity),
	CONSTRAINT PK_PartsUsed PRIMARY KEY(PartID)
);
GO
CREATE TABLE Inventory.JobsPerformed
(
	JobID int identity(1, 1) not null,
	ReceiptNumber int not null,
	JobName nvarchar(80),
	Cost money,
	CONSTRAINT PK_JobsPerformed PRIMARY KEY(JobID)
);
GO

INSERT INTO Management.RepairOrders(CustomerName, PhoneNumber,
	Address, City, State, ZIPCode, Make, Model, CarYear,
	ProblemDescription, Notes)
VALUES(N'Jeannette Duncan', N'202-620-5814', N'9246 Eulaw Drive N.W',
       N'Washington', N'DC', N'20018', N'Honda', N'Accord', 2002,
       N'The customer requested tune up on the car.',
       N'The whole tune up was done.');
GO
INSERT INTO Management.RepairOrders(CustomerName, PhoneNumber,
	Address, City, State, ZIPCode, Make, Model, CarYear,
	ProblemDescription, Notes)
VALUES(N'Eugenie Sanders', N'(301) 283-8074',
       N'3057 Daventry Road', N'Upper Marlboro', N'MD',
       N'20772', N'Dodge', N'Sprinter 2500', 2004,
N'The customer is complaining of a noise whenever she applies the brakes.',
       N'It appeared that the brakes were finished.');
GO

INSERT INTO Inventory.PartsUsed(ReceiptNumber, PartName, UnitPrice, Quantity)
VALUES(100001, N'Air Filter', 24.95, 1);
GO
UPDATE Management.RepairOrders
SET TotalParts = 24.95
WHERE ReceiptNumber = 100001;
GO
INSERT INTO Inventory.PartsUsed(ReceiptNumber, PartName, UnitPrice, Quantity)
VALUES(100001, N'Fuel Filter', 50.85, 1);
GO
UPDATE Management.RepairOrders
SET TotalParts += 50.85
WHERE ReceiptNumber = 100001;
GO
INSERT INTO Inventory.PartsUsed(ReceiptNumber, PartName, UnitPrice, Quantity)
VALUES(100001, N'Spark plugs', 4.35, 4);
GO
UPDATE Management.RepairOrders
SET TotalParts += 4.35
WHERE ReceiptNumber = 100001;
GO
INSERT INTO Inventory.PartsUsed(ReceiptNumber, PartName, UnitPrice, Quantity)
VALUES(100002, N'Replacement Front Brake Disc', 21.5, 1);
GO
UPDATE Management.RepairOrders
SET TotalParts = 21.5
WHERE ReceiptNumber = 100002;
GO
INSERT INTO Inventory.PartsUsed(ReceiptNumber, PartName, UnitPrice, Quantity)
VALUES(100002, N'Replacement Rear Brake Disc', 40.5, 1);
GO
UPDATE Management.RepairOrders
SET TotalParts += 40.5
WHERE ReceiptNumber = 100002;
GO
INSERT INTO Inventory.PartsUsed(ReceiptNumber, PartName, UnitPrice, Quantity)
VALUES(100002, N'Front Brake Pad Set', 32.35, 1);
GO
UPDATE Management.RepairOrders
SET TotalParts += 32.35
WHERE ReceiptNumber = 100002;
GO
INSERT INTO Inventory.PartsUsed(ReceiptNumber, PartName, UnitPrice, Quantity)
VALUES(100002, N'Rear Brake Pad Set', 65.15, 1);
GO
UPDATE Management.RepairOrders
SET TotalParts += 65.15
WHERE ReceiptNumber = 100002;
GO
INSERT INTO Inventory.JobsPerformed(ReceiptNumber, JobName, Cost)
VALUES(100001, N'Replaced the air filter, the fuel filter, and the spark plugs', 50.25);
GO
UPDATE Management.RepairOrders
SET TotalLabor = 50.25
WHERE ReceiptNumber = 100001;
GO
INSERT INTO Inventory.JobsPerformed(ReceiptNumber, JobName, Cost)
VALUES(100001, N'Adjusted the valves', 125.85);
GO
UPDATE Management.RepairOrders
SET TotalLabor += 125.85
WHERE ReceiptNumber = 100001;
GO
INSERT INTO Inventory.JobsPerformed(ReceiptNumber, JobName, Cost)
VALUES(100002, N'Changed the front and rear brakes', 70.00);
GO
UPDATE Management.RepairOrders
SET TotalLabor += 70.00
WHERE ReceiptNumber = 100002;
GO
INSERT INTO Inventory.JobsPerformed(ReceiptNumber, JobName, Cost)
VALUES(100002, N'Installed a new brake booster', 110.00);
GO
UPDATE Management.RepairOrders
SET TotalLabor += 110.00
WHERE ReceiptNumber = 100002;
GO

Notice that there are no foreign keys in the tables.
Right-click inside the Query Editor and click Execute

A Common Field for a Relationship

Probably the most important aspect of a relationship between two tables is a field they share. This means that you don't even have to establish a relationship between two tables. Once they share a column (both columns have the same data type but they don't have to have the same name), the relationship is implicit.

The easiest way to check a relationship between two tables is to match their records.

Practical Learning: Checking a Common Field

Right-click inside the Query Editor and press Ctrl + A

Type the following:

USE CollegeParkAutoRepair1;
GO
SELECT *
FROM Management.RepairOrders;
GO
SELECT *
FROM Inventory.PartsUsed;
GO
SELECT *
FROM Inventory.JobsPerformed;
GO

Right-click inside the Query Editor and click Execute.
Notice that the results include all records of the database
Right-click inside the Query Editor and press Ctrl + A

To get the records for one of the repair orders, change the statements as follows:

USE CollegeParkAutoRepair1;
GO
SELECT *
FROM Management.RepairOrders
WHERE ReceiptNumber = 100001;
GO
SELECT *
FROM Inventory.PartsUsed
WHERE ReceiptNumber = 100001;
GO
SELECT *
FROM Inventory.JobsPerformed
WHERE ReceiptNumber = 100001;
GO

Right-click inside the Query Editor and click Execute

To get a detail summary of the order, change the statement as follows:

USE CollegeParkAutoRepair1;
GO
SELECT ReceiptNumber, CustomerName, PhoneNumber
FROM Management.RepairOrders
WHERE ReceiptNumber = 100001;
GO
SELECT Address, City, State, ZIPCode
FROM Management.RepairOrders
WHERE ReceiptNumber = 100001;
GO
SELECT Make, Model, CarYear "Year"
FROM Management.RepairOrders
WHERE ReceiptNumber = 100001;
GO
SELECT ProblemDescription
FROM Management.RepairOrders
WHERE ReceiptNumber = 100001;
GO
SELECT PartName [Part Name], UnitPrice [Unit Price],
       Quantity, SubTotal [Sub-Total]
FROM Inventory.PartsUsed
WHERE ReceiptNumber = 100001;
GO
SELECT JobName, Cost
FROM Inventory.JobsPerformed
WHERE ReceiptNumber = 100001;
GO
SELECT TotalParts [Total Parts], TotalLabor [Total Labor],
       TaxRate [Tax Rate],
       CAST(TaxAmount AS decimal(6,2)) [Tax Amt],
       CAST(OrderTotal AS decimal(6,2)) [Order Total]
FROM Management.RepairOrders
WHERE ReceiptNumber = 100001;
GO

Press F5 to exexute
Click inside the Query Editor and press Ctrl + A

To start a new database, type the following code:

USE master;
GO
DROP DATABASE University6
GO

CREATE DATABASE University7;
GO
USE University7;
GO
CREATE SCHEMA Academics;
GO
CREATE SCHEMA Administration;
GO
   
CREATE FUNCTION Administration.SetDateOfBirth(@days int)
RETURNS Date
AS
BEGIN
	RETURN DATEADD(d, @days, SYSDATETIME());
END
GO

CREATE TABLE Administration.Genders
(
    GenderLetter nvarchar(3) not null,
    GenderName nvarchar(50)
);
GO
CREATE TABLE Administration.Departments
(
    DepartmentCode nvarchar(4) not null,
    DepartmentName nvarchar(50) default N'N/A'
);
GO
CREATE TABLE Administration.Employees
(
    EmployeeNumber nvarchar(8) not null,
    FirstName nvarchar(20),
    MiddleName nvarchar(20),
    LastName nvarchar(20) not null,
    DepartmentCode nvarchar(4),
    Title nvarchar(100),
    Gender nvarchar(3) default N'N/A'
);
GO
CREATE TABLE Academics.UndergraduateMajors
(
    MajorID int identity(1001, 1) not null,
    Major nvarchar(60) unique,
    Dean nvarchar(8) not null
);
GO
CREATE TABLE Academics.Minors
(
    MinorID int identity(1001, 1) not null,
    Minor nvarchar(60) unique,
    Notes nvarchar(max)
);
GO
CREATE TABLE Academics.UndergraduateStudents
(
    StudentID int identity(1, 1) not null,
    StudentNumber nvarchar(8) not null,
    FirstName nvarchar(20),
    MiddleName nvarchar(20),
    LastName nvarchar(20),
    BirthDate date,
    Gender nvarchar(3) default N'N/A',
    MajorID int not null,
    MinorID int not null
);
GO
CREATE TABLE Academics.Teachers
(
    TeacherID int identity(1, 1) not null,
    TeacherNumber nvarchar(10),
    FirstName nvarchar(25),
    MiddleName nvarchar(25),
    LastName nvarchar(25),
    [Degrees] nvarchar(40),
    DepartmentCode nvarchar(4),
    Gender nvarchar(3)
);
GO

INSERT INTO Administration.Genders
VALUES(N'M', N'Male'), (N'F', N'Female'), (N'U', N'Unknown');
GO
INSERT INTO Administration.Departments(DepartmentCode, DepartmentName)
VALUES(N'N/A', NULL),
      (N'ADMN', N'Administration, Admissions, and Students Affairs'),
      (N'HRMN', N'Human Resources and Management'),
      (N'WRTG', N'Wrighting'),
      (N'EDUC', N'Educational Studies'),
      (N'LNGS', N'Languages and Linguistics'),
      (N'PSOP', N'Psychology, Sociology, and Philosophy'),
      (N'ITEC', N'Information Technology'),
      (N'FINA', N'Finances'),
      (N'ACCT', N'Accounting'),
      (N'EBCM', N'Economics, Business, Commerce, and Marketing'),
      (N'CJLE', N'Criminal Justice and Law Enforcement'),
      (N'CHEM', N'Chemistry'),
      (N'CMSC', N'Computer Sciences'),
      (N'ELCE', N'Electrical and Computer Engineering'),
      (N'CMST', N'Computer Studies'),
      (N'HSGE', N'History and Geography'),
      (N'ANTH', N'Anthropology'),
      (N'BIOL', N'Biology and Biotechnology'),
      (N'HLTH', N'Health Care and Gerontology'),
      (N'MATH', N'Mathematics and Statistics'),
      (N'GVPS', N'Government and Political Sciences');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, MiddleName, LastName, DepartmentCode, Title, Gender)
VALUES(N'279227', N'Donald',  N'Henry',  N'Leighton',  N'ADMN', N'President', N'M'),
      (N'502494', N'Anthony', N'Robert', N'Parrish',   N'ADMN', N'Provost', N'M'),
      (N'247591', N'Leonid',  N'George', N'Hawthorne', N'HSGE', N'Dean of History, Geography, and Political Sciences', N'M');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, LastName, DepartmentCode, Title, Gender)
VALUES(N'400384', N'Jennifer',  N'Palermo', N'HRMN', N'Dean of Human Resources and Management Studies', N'F');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, MiddleName, LastName, DepartmentCode, Title, Gender)
VALUES(N'274039', N'Joyce',    N'Denise',   N'Blue',      N'CHEM', N'Dean of Chemistry Studies', N'F'),
      (N'409260', N'Edmond',   N'Gabriel',  N'Harrington', N'CJLE', N'Dean of Criminal Justice Studies', N'M'),
      (N'828347', N'Robert',   N'Elie',     N'Marsden',   N'MATH', N'Dean of Mathematics, statistics, and Physics', N'M'),
      (N'640207', N'Kimberly', N'Carlette', N'Edelman',   N'PSOP', N'Dean of Psychology, Sociology, and Philosophy', N'F'),
      (N'161138', N'Laura',    N'Fannie',   N'Joansen',   N'ADMN', N'Dean of Litterary Studies', N'F'),
      (N'605924', N'Phillipe', N'Ernest',   N'Portman',   N'BIOL', N'Dean of Biological and Biotechnology Studies', N'M'),
      (N'908047', N'Ann',      N'Laura',    N'Tenney',    N'FINA', N'Cashier',  N'F');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, LastName, DepartmentCode, Title, Gender)
VALUES(N'582007', N'Alexander', N'Nolan',  N'CMSC', N'Dean of Computer Sciences and Computer Engineering', N'M'),
      (N'697300', N'Albert',    N'Harney', N'FINA', N'Dean of Financial and Accounting Studies', N'M');
GO
INSERT INTO Administration.Employees(EmployeeNumber, FirstName, MiddleName, LastName, DepartmentCode, Title, Gender)
VALUES(N'702048', N'Laurentine', N'Felicit�', N'Avrilien', N'EDUC', N'Dean of Educational Studies',  N'F'),
      (N'927486', N'Robert',     N'John',     N'Preston',  N'CMST', N'Dean of Computer Studies', N'M'),
      (N'930248', N'Jeannette',  N'Veronica', N'Holms',    N'ADMN', N'Vice President for Government Relations', N'F');
GO

INSERT INTO Academics.UndergraduateMajors(Major, Dean)
VALUES(N'English',      N'161138'),
      (N'Linguistics',  N'161138'),
      (N'History',      N'247591'),
      (N'Geography',    N'247591'),
      (N'Finance',      N'697300'),
      (N'Sociology',    N'640207'),
      (N'Psychology',   N'640207'),
      (N'Economics',    N'908047'),
      (N'Marketing',    N'908047'),
      (N'Statistics',   N'828347'),
      (N'Accounting',   N'697300'),
      (N'Gerontology',  N'640207'),
      (N'Biology',      N'605924'),
      (N'Chemistry',    N'274039'),
      (N'Anthropology', N'247591'),
      (N'Political Science',	     N'247591'),
      (N'Criminal Justice and Law Enforcement', N'409260'),
      (N'Emergency Management',	     N'400384'),
      (N'Business Administration',   N'908047'),
      (N'Human Resource Management', N'400384'),
      (N'Computer Science',                 N'582007'),
      (N'Computer Networks and Security',   N'927486'),
      (N'Information Systems Management',   N'927486'),
      (N'Computer and Information Science', N'927486'),
      (N'Health Care Management and Policy', N'927486');
GO

INSERT INTO Academics.Minors(Minor)
VALUES(N'English'), 
      (N'Spanish'),
      (N'Finance'),
      (N'Economics'),
      (N'Computing'),
      (N'Marketing'), 
      (N'Sociology'),
      (N'Psychology'),
      (N'Philosophy'),
      (N'Accounting'),
      (N'Geography'),
      (N'Gerontology'),
      (N'Art History'),
      (N'Biology'),
      (N'History'),
      (N'Journalism'),
      (N'Chemistry'),
      (N'Linguistics'),
      (N'Anthropology'),
      (N'Criminal Justice'),
      (N'Political Science'),
      (N'Mathematical Sciences'),
      (N'Speech Communication'),
      (N'Communication Studies'),
      (N'Emergency Management'),
      (N'Business Administration'),
      (N'Health Care Management'),
      (N'Human Resource Management'),
      (N'Customer Service Management'),
      (N'Computer Science'),
      (N'Cybersecurity'),
      (N'Physics'),
      (N'Theology');
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender, MajorID, MinorID)
VALUES(N'88130480', N'Marie', N'Annette', N'Robinson', Administration.SetDateOfBirth(-6817), N'F', 1021, 1004),
      (N'24795711', N'Roger', N'Dermot',  N'Baker',    Administration.SetDateOfBirth(-6570), N'M', 1005, 1002);
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender, MajorID, MinorID)
VALUES(N'18073572', N'Patrick', N'Wisne', Administration.SetDateOfBirth(-11012), N'M', 1001, 1008);
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender, MajorID, MinorID)
VALUES(N'22803048', N'Gary', N'Jonathan', N'Jones', Administration.SetDateOfBirth(-19926), N'M', 1019, 1007),
      (N'97394285', N'Jessica', N'Danielle', N'Weisburgh', Administration.SetDateOfBirth(-12081), N'F', 1009, 1001),
      (N'97596002', N'Laurent', N'Frank', N'Simonson', Administration.SetDateOfBirth(-17503), N'M', 1016, 1004),
      (N'94708257', N'Christopher', N'Sheldon', N'Dale', Administration.SetDateOfBirth(-6570),  N'M', 1006, 1008),
      (N'48009520', N'Diane', N'Kathy', N'Paglia', Administration.SetDateOfBirth(-13840), N'F', 1006, 1009),
      (N'13048039', N'Joseph', N'Christian', N'Riback', Administration.SetDateOfBirth(-7909),  N'M', 1011, 1006),
      (N'92270397', N'Patrick', N'Jonathan', N'Brzeniak', Administration.SetDateOfBirth(-17361), N'M', 1021, 1022);
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender, MajorID, MinorID)
VALUES(N'70840584', N'Tracy', N'Sikorowski', Administration.SetDateOfBirth(-11650), N'M', 1006, 1015);
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, MiddleName, LastName, BirthDate, Gender, MajorID, MinorID)
VALUES(N'29480759', N'Hank',    N'Peter', N'Newport',  Administration.SetDateOfBirth(-7606), N'M',  1007, 1004),
      (N'72938479', N'Marc',    N'Kenny', N'Dunder',   Administration.SetDateOfBirth(-14333), N'M', 1009, 1005),
      (N'61824668', N'Stephen', N'David', N'Weisberg', Administration.SetDateOfBirth(-11324), N'M', 1006, 1002);
GO
INSERT INTO Academics.UndergraduateStudents(StudentNumber, FirstName, LastName, BirthDate, Gender, MajorID, MinorID)
VALUES(N'20384025', N'Manoah',  N'Hall', Administration.SetDateOfBirth(-16427), N'M', 1011, 1004),
      (N'80284060', N'Timothy', N'Wray', Administration.SetDateOfBirth(-9000), N'M',  1001, 1010);
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, LastName, DepartmentCode, Gender)
VALUES(N'820384', N'Marianne', N'Oslin', N'EDUC', N'F');
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, MiddleName, LastName, [Degrees], DepartmentCode, Gender)
VALUES(N'160205', N'Steve', N'Alxeander', N'Rosner', N'MA, PhD', N'CMSC', N'M');
GO
INSERT INTO Academics.Teachers(TeacherNumber, LastName, Gender)
VALUES(N'280385', N'Thomas', N'M');
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, LastName, [Degrees], DepartmentCode, Gender)
VALUES(N'520203', N'Anne',   N'Wine',     N'MS, PhD',     N'MATH', N'F'),
      (N'297940', N'Thomas', N'Phillips', N'BS, MS, PhD', N'MATH', N'M');
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, MiddleName, LastName, [Degrees], Gender)
VALUES(N'700800', N'Zachary', N'Philemon', N'Jurgens', N'MA, PhD', N'M');
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, LastName, [Degrees], DepartmentCode, Gender)
VALUES(N'640840', N'Maryam',  N'Whittaker',  N'MA, MS, PhD', N'MATH', N'F'),
      (N'339429', N'Lisa',    N'Williamson', N'PhD',         N'EBCM', N'F');
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, MiddleName, LastName, [Degrees], Gender)
VALUES(N'704807', N'Joan', N'Darlene', N'Leighton', N'MA, PhD', N'F');
GO
INSERT INTO Academics.Teachers(TeacherNumber, FirstName, LastName, [Degrees], DepartmentCode, Gender)
VALUES(N'249382', N'Johanna', N'Possemato',  N'PhD', N'GVPS', N'F');
GO

To execute, press F5

Joins Fundamentals

Using a Shared Field to Join Tables

One of the most important features of a relational database consists of combining records from various tables to get a single list. The SQL provides two main options: Applying a condition on a common field or creating a join.

The primary way to join two or more tables to create a common list that combines their records is to match the records they have in common. Before doing this, the lists must have a field used as the primary key on one table and a foreign key on the other table. The formula to follow is:

SELECT Field(s) [, Field(s)] FROM Table1, Table2
WHERE Condition

You use a SELECT statement to select fields from one or all tables, then you use a WHERE condition to specify how the records will be matched.

Practical Learning: Using a Shared Field to Join Tables

Right-click inside the Query Editor and press Ctrl + A

To see a list of apartments, type the following:

USE University7;
GO

SELECT Students.StudentNumber,
       Students.FirstName,
       Students.MiddleName,
       Students.LastName,
       Students.BirthDate,
       Students.Gender,
       Students.MajorID
FROM Academics.UndergraduateStudents Students;
GO

Right-click inside the Query Editor and click Execute

To join the records from another table, change the statement as follows:

USE University7;
GO

SELECT Students.StudentNumber,
       Students.FirstName,
       Students.MiddleName,
       Students.LastName,
       Students.BirthDate,
       Students.Gender,
       Majors.Major
FROM Academics.UndergraduateStudents Students,Academics.UndergraduateMajors Majors
WHERE Students.MajorID = Majors.MajorID;
GO

Right-click inside the Query Editor and click Execute

Change the statement as follows:

USE University7;
GO

SELECT Students.StudentNumber,
       Students.FirstName,
       Students.MiddleName,
       Students.LastName,
       Students.BirthDate,
       Gdrs.GenderName,
       Majors.Major
FROM Academics.UndergraduateStudents Students,
     Academics.UndergraduateMajors Majors,
     Administration.Genders Gdrs
WHERE (Students.MajorID = Majors.MajorID) AND
      (Students.Gender = Gdrs.GenderLetter);
GO

To execute, press F5
Click inside the Query Editor and press Ctrl + A
Press Delete to clear the Query Editor

Introducing Joins

A data join is a technique of creating a list of records from more than one table, using all columns from all tables involved, or selecting only the desired columns from one or all of the tables involved. This means that a data join is essentially created in three steps:

Selecting the tables that will be involved in the join
Selecting a column that will create the link in each table
Writing a SQL statement that will create the records
In the Object Explorer, right-click Databases and click Refresh
Expand University7 and expand its Tables node
Right-click Academics.UndergraduateStudents and click Edit Top 200 Rows

Practical Learning: Introducing Joins

On the main menu, click Query -> Design Query in Editor...
In the Add Table dialog box, click UndergraduateStudents (Academics) and click Add
In the Add Table dialog box, double-click UndergraduateMajors (Academics)
On the Add Table dialog box, click Close

Introducing Joins

The Tables of a Join

Before creating a join, you must have the tables that would be involved. The tables are created using the techniques we have seen in previous lessons. It is also important to create a primary key for each table. The parent table would usually need only this primary key that would be used to "link" it to a child table. If needed, you can then create the necessary records for the table. Here is an example:

CREATE TABLE Genders
(
    GenderID int identity(1, 1) not null,
    Gender nchar(15),
    CONSTRAINT PK_Genders PRIMARY KEY(GenderID)
);
GO

INSERT INTO Genders(Gender)
VALUES(N'Male'),(N'Female'),(N'Unknown');
GO

When creating the child table, remember to create a column that would serve as the link with the parent table. By a (good) habit as we saw when studying relationships, the name and the data type of this column are the same as the primary key of the parent table. Here is an example:

USE master;
GO

CREATE DATABASE People;
GO

USE People;
GO

IF OBJECT_ID('Genders', 'U') IS NOT NULL
  DROP TABLE Genders
GO

CREATE TABLE Genders
(
    GenderID int identity(1, 1) not null,
    Gender nchar(15),
    CONSTRAINT PK_Genders PRIMARY KEY(GenderID)
);
GO

INSERT INTO Genders(Gender)
VALUES(N'Male'),(N'Female'),(N'Unknown');
GO

CREATE TABLE Persons
(
    PersonID int identity(1, 1) not null,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    GenderID int,
    CONSTRAINT PK_Persons PRIMARY KEY(PersonID)
);
GO

INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'John', N'Franks', 1), (N'Peter', N'Sonnens', 1);
GO
INSERT INTO Persons(FirstName, LastName)
VALUES(N'Leslie',N'Aronson');
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'Mary', N'Shamberg', 2), (N'Chryssa', N'Lurie', 2),
      (N'Hellah', N'Zanogh', 3), (N'Olympia', N'Sumners', 2),
      (N'Roberta', N'Jerseys', 2);
GO
INSERT INTO Persons(FirstName, LastName)
VALUES(N'Hel�ne', N'Campo');
GO
INSERT INTO Persons(LastName, GenderID)
VALUES(N'Millam', 1), (N'Hessia', 2);
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'Stanley', N'Webbs', 2), (N'Arnie', N'Ephron', 3),
      (N'Mike', N'Pastore', 1);
GO
INSERT INTO Persons(FirstName) VALUES(N'Salim');
GO
INSERT INTO Persons(FirstName, LastName, GenderID)
VALUES(N'Mary', N'Shamberg', 2), (N'Chryssa', N'Lurie', 2);
GO
INSERT INTO Persons(LastName) VALUES(N'Millers');
GO
INSERT INTO Persons(FirstName, GenderID) VALUES(N'Robert', 1);
GO

Join Creation

Equipped with the necessary tables and their columns, you can create the join. To do this in the SQL Server Management Studio, in the Object Explorer, right-click the database and click open a Query Editor. Then:

On the main menu, click Query -> Query Design in Editor...
Right-click somewhere in the Query Editor and click Query Design in Editor

Any of these actions would display the Table window:

Query Designer

Because the foundation of a join lies on at least two tables, you should add them. To do this, you use the Add Table dialog box. If you had closed the Add Table dialog box, you can right-click the top section of the Query Designer and click Add Table...

Query Designer

On the Add Table dialog box:

You can click the table's name and click Add
You can double-click a table

After adding the tables, click Close.

Here is an example of two tables that have been added:

Query Designer

If a relationship was already established between the tables, a joining line would show it.

In the SQL, the basic formula to create a join is:

SELECT WhatColumn(s)
FROM ChildTable
TypeOfJoin ParentTable
ON Condition

ChildTable specifies the table that holds the records that will be retrieved. It can be represented as follows:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin ParentTable
ON Condition

ParentTable specifies the table that holds the column with the primary key that will control what records, related to the child table that will display. This would be represented as follows:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin Genders
ON Condition

Condition is a logical expression used to validate the records that will be isolated. The condition can be created using the following formula:

Table1Column Operator Table2Column

To create the condition, you start with the ON keyword. You can assign the primary key column of the parent table to the foreign key column of the child table. Because both columns likely have the same name, to distinguish them, their names should be qualified. This would be done as follows:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

Although we used the assignment operator "=", another operator, such as LIKE, can also be used, as long as it can be used to assign one column to another. Here is an example:

SELECT WhatColumn(s)
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID LIKE Genders.GenderID

The WhatColumn(s) of our formula allows you to make a list of the columns you want to include in your statement. As you should be aware, you can include all columns by using the * operator. Here is an example:

SELECT *
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

In this case, all columns from all tables would be included in the result. Instead of all columns, you may want a restricted list. In this case, create the list after the SELECT keyword separating them with commas. You can use the name of a column normally if that name is not duplicated in more than one column. Here is an example:

SELECT LastName, FirstName, Gender
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

If the same name of a column is found in more than one table, as is the case for a primary-foreign key combination, you should qualify the name. Here is an example:

SELECT LastName, FirstName, Persons.GenderID,
       Genders.GenderID, Gender
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

In fact, to make your code easier to read, you should qualify the name of each column of your SELECT statement. Here is an example:

SELECT Persons.LastName, Persons.FirstName, Persons.GenderID,
       Genders.GenderID, Genders.Gender
FROM Persons
TypeOfJoin Genders
ON Persons.GenderID = Genders.GenderID

If you have a schema, you can use it to qualify a (each) table. Here is an example:

SELECT dbo.Persons.LastName, dbo.Persons.FirstName, dbo.Persons.GenderID,
       dbo.Genders.GenderID, dbo.Genders.Gender
FROM dbo.Persons
TypeOfJoin dbo.Genders
ON dbo.Persons.GenderID = dbo.Genders.GenderID

You can also use an alias name for each table. Here is an example:

SELECT pers.LastName, pers.FirstName, pers.GenderID,
       Genders.GenderID, Genders.Gender
FROM Persons pers
TypeOfJoin Genders
ON pers.GenderID = Genders.GenderID

Cross and Inner Joins

Introduction

When studying data relationships, we saw the role of the primary and foreign keys in maintaining the exchange of information between two tables. This technique of linking tables plays a major role when creating a join. It allows you to decide whether you want to include all records or only isolate some of them. To respect the direction of a relationship between two tables as it is applied to a query, Transact-SQL supports three types of joins.

Cross Joins

A cross join creates a list of all records from both tables as follows: the first record from the parent table is associated to each record from the child table, then the second record from the parent table is associated to each record from the child table, and so on. In this case also, there is no need of a common column between both tables. In other words, you will not use the ON clause.

To create a cross join, you can replace the TypeOfJoin of our formula with CROSS JOIN or CROSS OUTER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
       Genders.GenderID, Genders.Gender
FROM   Persons
CROSS  JOIN Genders
GO

By default, in the SQL Server Management Studio, after you have just added a table to another one (if no relationship was already established between both tables), the query would be automatically made a cross join. All you have to do is to select the necessary columns:

After selecting the columns, you can click OK and execute the query to see the result:

Join

Inner Joins

Imagine you have two tables that can be linked through one's primary key and another's foreign key:

Genders

Notice that some records in the Persons table don't have an entry for the GenderID column and were marked with NULL by the database engine. When creating a query of records of the Persons table, if you want your list to include only records that have an entry, you can create it as inner join.

By default, from the SQL Server Management Studio, when creating a new query, if a relationship was already established between both tables, the query is made an inner join. If there was no relationship explicitly established between both tables, you would have to create it or edit the SQL statement. Consider the following:

Notice that, because no relationship is established between both tables, the join is crossed.

To create an inner join, you have two options. You can drag the primary key from the parent table and drop it on the foreign key in the child table. Here is an example:

Join

Alternatively, you can edit the SQL statement manually to make it an inner join. To do this, you would specify the TypeOfJoin factor of our formula with the expression INNER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, Persons.GenderID,
       Genders.GenderID AS [Gender ID], Genders.Gender
FROM   Persons INNER JOIN Genders ON Persons.GenderID = Genders.GenderID

After creating the join, in the Diagram pane, a line would be created to join the tables:

Join

You can then execute the query to see the result. This would produce:

Join

We mentioned earlier that you could include all columns in the query. In our result, since we are more interested in the gender of each Persons record, we would not need the GenderID column from the Genders table. Here is an example:

Join

As mentioned earlier, notice that the result includes only records that have an entry (a non-NULL entry) in the GenderID foreign key column of the Persons table.

An alternative to the INNER JOIN expression is to simply type JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
       Genders.Gender
FROM Persons
JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO

To destroy a join between two tables, if you are working in the Table window, you can right-click the line that joins the tables and click Remove. In SQL, you must modify the expressions that make up the join (the JOIN and the ON expressions).

Practical Learning: Creating an Inner Join

To create an inner join, from the UndergraduateMajors (Academics) table, drag MajorID and drop it on top of MajorID on UndergraduateStudents (Academics):
Release the mouse
On the tables, click the check boxes of the following fields: StudentNumber, FirstName, LastName, and Major
Click OK
On the SQL Editor toolbar, click the Execute button to see the result
Click in the top section of the Query Editor where the code was written
On the main menu, click Edit -> Select All
On the main menu, click Edit -> Delete
On the main menu, click Query -> Design Query in Editor...
To get a list of only teachers whose department is not known, in the Add Table dialog box, double-click Teachers (Academics) and Departments (Administration)
Click Close
Drag DepartmentCode from Department (Administration) and drop it on DepartmentCode on Departments (Administration):
Release the mouse
On the tables, click the check boxes of the following fields: TeacherNumber, FirstName, LastName, Degrees, and DepartmentName
Click OK
To execute, press F5
Click inside the Query Editor, press Ctrl + A, and press Delete
Right-click inside click Query Editor and click Design Query in Editor...
In the Add Table dialog box, double-click Teachers (Academics) and Departments (Administration)
Click Close
Drag DepartmentCode from Department (Administration) and drop it on DepartmentCode on Departments (Administration)
Release the mouse
On the tables, click the check boxes of the following fields: TeacherNumber, FirstName, MiddleName, LastName, Degrees, and DepartmentName

Outer Joins

Introduction

Instead of showing only records that have entries in the child table, you may want your query to include all records, including those that are null. To get this result, you would create an outer join. You have three options.

Left Outer Joins

A left outer join produces all records of the child table, also called the right table. The records of the child table that don't have an entry in the foreign key column are marked as NULL.

To create a left outer join, if you are working in the Table window, in the Diagram pane, right-click the line that joins the tables and click the option that would select all records from the child table (in this case, that would be Select All Rows From Persons):

Left Outer Joins

Alternatively, you can replace the TypeOfJoin factor of our formula with either LEFT JOIN or LEFT OUTER JOIN. 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
GO

In both cases, the button in the middle of the line would be added an arrow that points to the parent table. You can then execute the query to see the result. Here is an example:

Join

Left Outer Joins

Notice that the result includes all records of the Persons (also called the right) table and the records that don't have an entry in the GenderID column of the Persons (the right) table are marked with NULL.

Practical Learning: Creating a Left Outer Join

To get a list of all teachers, including those whose department is not known, right-click the line between the tables and click Select All Rows from Departments (Administration)
Click OK
On the SQL Editor toolbar, click the Execute button

Right Outer Joins

A right outer join considers all records from the parent table and finds a matching record in the child table. To do this, it starts with the first record of the parent table (in this case the Genders table) and shows each record of the child table (in this case the Persons table) that has a corresponding entry. This means that, in our example, a right outer join would first create a list of the Persons records that have a 1 (Female) value for the GenderID column. After the first record, the right outer join moves to the second record, and so on, each time listing the records of the child table that have a corresponding entry for the primary key of the parent table.

To visually create a right outer join in the Table window, after establishing a join between both tables, if you had previously created a left outer join, you should remove it by right-clicking the line between the tables and selecting the second option under Remove. Then, you can right-click the line that joins them and click the option that would select all records from the parent table. In our example, you would click Select All Rows From Genders.

To create a right outer join in SQL, you can replace the TypeOfJoin factor of our formula with RIGHT JOIN or RIGHT OUTER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
       Genders.GenderID, Genders.Gender
FROM Persons
RIGHT OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO

In both cases, the button on the joining line between the tables would have an arrow that points to the child table. You can then run the query. Here is an example:

Join

Notice that the query result starts with the first record of the parent table, also called the left table (in this case the Genders table), and lists the records of the child table, also called the right table (in this case the Persons table), that have the entry corresponding to that first record. Then it moves to the next GenderID value. Also, notice that there are no NULL records in the Gender.

Full Outer Joins

A full outer join produces all records from both the parent and the child tables. If a record from one table doesn't have a corresponding value in the other table, the value of that record is marked as NULL.

To visually create a full outer join, in the Table window, right-click the line between the tables and select each option under Remove so that both would be checked. To create a full outer join in SQL, replace the TypeOfJoin factor of our formula with FULL JOIN or FULL OUTER JOIN. Here is an example:

SELECT Persons.PersonID, Persons.FirstName, Persons.LastName, 
       Genders.GenderID, Genders.Gender
FROM Persons
FULL OUTER JOIN Genders
ON Persons.GenderID = Genders.GenderID
GO

The button on the line between the tables would now appear as a square. You can then execute the query. Here is an example:

Join

Just as we have involved only two tables in our joins so far, you can create a join that includes many tables.

Practical Learning: Closing Microsoft SQL Server

Close the Query Editor
Close Microsoft SQL Server


Previous	Copyright © 2005-2022, FunctionX	Next