SQL Lab 03 - Working with Multiple Tables

In this lab, I will follow my National University of Singapore (NUS) course: "Machine Learning and Data Analytics using Python - December 2023" and shows how to use basic SQL queries in DB Browser (SQLite).

Lab 3.1: A simple Example on JOINs

INNER JOIN: The INNER JOIN keyword selects records that have matching values in both tables.

SELECT 
	*
FROM
	invoices
INNER JOIN
	customers
ON
	invoices.CustomerId = customers.CustomerId;

Lab 3.2: Using JOINs with Alias

AS (Alias): SQL aliases are used to give a table, or a column in a table, a temporary name.

ORDER BY: Using the ORDER BY keyword to sort the customers by their last name.

SELECT 
	c.LastName,
	c.FirstName,
	i.InvoiceId,
	i.InvoiceDate,
	i.total
FROM
	invoices AS i
INNER JOIN
	customers AS c
ON
	i.CustomerId = c.CustomerId
ORDER BY
	c.LastName;

Lab 3.3: The INNER JOIN with more than two tables

1. Join the "invoices" table with the "Customers" table using the "CustomerId" column.

2. Join the "customers" table with the "Employees" table using the "EmployeeId" column.

SELECT
	e.FirstName,
	e.LastName,
	e.EmployeeId,
	c.FirstName,
	c.LastName,
	c.SupportRepId,
	i.total
FROM
	invoices AS i
INNER JOIN
	customers AS c
ON
	i.CustomerId = c.CustomerId
INNER JOIN
	employees AS e
ON
	c.SupportRepId = e.EmployeeId
ORDER BY
	i.total DESC
LIMIT
	10;

Lab 3.4: Using the LEFT OUTER JOIN with IS NULL

LEFT JOIN: The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

In some databases LEFT JOIN is called LEFT OUTER JOIN.

SELECT
	ar.ArtistId AS [ArtistId from Artists Table],
	al.ArtistId AS [ArtistId from Albums Table],
	ar.name AS [Artists Name],
	al.Title AS [Alubm]
FROM
	artists AS ar
LEFT OUTER JOIN
	albums AS al
ON
	ar.ArtistId = al.ArtistId
WHERE
	al.ArtistId is NULL;

Lab 3.5: Turning the RIGHT JOIN into the LEFT JOIN

RIGHT JOIN: The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SELECT
	t.TrackId,
	t.Composer,
	t.name,
	al.AlbumId,
	al.Title
FROM
	-- albums AS al
	tracks AS t
-- RIGHT OUTER JOIN
LEFT OUTER JOIN
	-- tracks AS t
	albums AS al
ON
	al.AlbumId = t.AlbumId;

Reference:

1. SQLite Home Page

https://www.sqlite.org/index.html

2. SQL 教程

https://www.runoob.com/sql/sql-tutorial.html

最初發表 / 最後更新: 2024-02-14 / 2024-02-14