You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
Real-world databases store data across multiple related tables. This lesson covers how to combine data using joins, subqueries, common table expressions (CTEs), APPLY, and PIVOT.
CREATE TABLE Authors (
AuthorId INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Country NVARCHAR(50)
);
CREATE TABLE Books (
BookId INT IDENTITY(1,1) PRIMARY KEY,
Title NVARCHAR(200) NOT NULL,
AuthorId INT NOT NULL REFERENCES Authors(AuthorId),
Genre NVARCHAR(50),
Price DECIMAL(8,2) NOT NULL,
Published DATE
);
CREATE TABLE Orders (
OrderId INT IDENTITY(1,1) PRIMARY KEY,
BookId INT NOT NULL REFERENCES Books(BookId),
Quantity INT NOT NULL,
OrderDate DATE NOT NULL DEFAULT GETDATE()
);
Returns rows that have matching values in both tables:
SELECT b.Title, a.FirstName, a.LastName, b.Price
FROM Books b
INNER JOIN Authors a ON b.AuthorId = a.AuthorId;
Only books with a matching author (and vice versa) appear in the result.
Returns all rows from the left table plus matches from the right:
SELECT a.FirstName, a.LastName, b.Title
FROM Authors a
LEFT JOIN Books b ON a.AuthorId = b.AuthorId;
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.