SQL Lab 02 - Turning Data into Information

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 2.1 SQL Arithmetic operations "+", "-", "*", "/", "%"

/*
Created by: Peter
Created on: 2024/01/26
Description: With arithmetic operations
*/
SELECT
	Total AS [Original Amount],
	Total + 10 AS [Addition Operator],
	Total - 10 AS [Subtraction Operator],
	Total / 10 AS [Division Operator],
	Total * 10 AS [Multiplicuation Operator],
	Total % 10 AS [Modulo Operator]
FROM
	invoices
ORDER BY
	Total DESC;

Lab 2.2

WHERE

The WHERE clause is used to extract records that fulfil a specified condition.

Total = 1.98: Find out the Total vaule = 1.98

Total BETWEEN 1.98 AND 5.00: Find out the all vaules from 1.98 to 5.00 in Total.

Total IN (1.98, 3.96): Find out the two vaules 1.98 or 3.96 in Total.

/*
Created by: Peter
Created on: 2024/01/26
Description: Filter records by numbers
*/
SELECT
	InvoiceDate,
	BillingAddress,
	BillingCity,
	Total
FROM
	invoices
WHERE 
	-- Total = 1.98
	-- Total BETWEEN 1.98 AND 5.00
	Total IN (1.98, 3.96)
ORDER BY
	InvoiceDate;

Lab 2.3

Filter data in SQL using texts.

/*
Created by: Peter
Created on: 2024/01/26
Description: Filter records by test
*/
SELECT
	InvoiceDate,
	BillingAddress,
	BillingCity,
	Total
FROM
	invoices
WHERE
	-- BillingCity = 'Tucson'
	BillingCity IN ('Tucson', 'Paris', 'London')
ORDER BY
	InvoiceDate;

Lab 2.4

Filter data in SQL using wildcards.

LIKE

Use the LIKE operator to search for wildcards.

%P%: Filter any city using the letter "P" or "p"

T%: Filter any city that starts with "T" or "t"

%T: Filter any city that ends with "T" or "t"

/*
Created by: Peter
Created on: 2024/01/26
Description: Using the LIKE operator to search for wildcards
*/
SELECT
	InvoiceDate,
	BillingAddress,
	BillingCity,
	Total
FROM
	invoices
WHERE
	-- BillingCity = 'Tucson'
	-- T% = filter any city thst starts with "T" or "t"
	-- %T = filter any city that ends with "T" or "t"
	-- BillingCity LIKE '%P%'
	-- BillingCity LIKE '%t'
	BillingCity NOT LIKE 't%'
ORDER BY
	InvoiceDate;

Lab 2.5

Filter data in SQL using dates.

Date: YYYY-MM-DD HH:MM:SS

AND: The AND operator satisfies both conditions.

OR: The OR operator satisfies either conditions.

/*
Created by: Peter
Created on: 2024/01/26
Description: Filter records by date
*/
SELECT
	InvoiceDate,
	BillingAddress,
	BillingCity,
	Total
FROM
	invoices
WHERE
	-- InvoiceDate > '2010-01-02'
	-- date (InvoiceDate) > '2010-01-02' AND Total < 3
	date (InvoiceDate) > '2010-01-02' OR Total < 3
ORDER BY
	InvoiceDate;

Lab 2.6

Use parentheses if two or more conditions are needed in a statement.

/*
Created by: Peter
Created on: 2024/01/26
Description: Filter records by text
*/
SELECT
	InvoiceDate,
	BillingAddress,
	BillingCity,
	Total
FROM
	invoices
WHERE
	BillingCity LIKE "p%" OR BillingCity LIKE "D%"
ORDER BY
	InvoiceDate;

Lab 2.7

Use parentheses if two or more conditions are needed in a statement.

Total > 1.98 AND BillingCity LIKE "p%" OR BillingCity LIKE "D%":

(Total > 1.98 AND BillingCity LIKE "p%") OR (BillingCity LIKE "D%")

/*
Created by: Peter
Created on: 2024/01/26
Description: Using the parentheses
*/
SELECT
	InvoiceDate,
	BillingAddress,
	BillingCity,
	Total
FROM
	invoices
WHERE
	-- Total > 1.98 AND BillingCity LIKE "p%" OR BillingCity LIKE "D%"
	Total > 1.98 AND (BillingCity LIKE "p%" OR BillingCity LIKE "D%")
ORDER BY
	InvoiceDate;

Lab 2.8

In this example, write the CASE statement under the SELECT clause.

CASE

The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement)

/*
Created by: Peter
Created on: 2024/01/26
Description: Case statment
*/
SELECT
	InvoiceDate,
	BillingAddress,
	BillingCity,
	Total,
	CASE
	WHEN Total < 2.00 THEN 'Baseline Purchase'
	WHEN Total BETWEEN 2.00 AND 6.99 THEN 'Low Purchase'
	WHEN Total BETWEEN 7.00 AND 15.00 THEN 'Target Purchase'
	ELSE 'Top Performers'
	END AS PurchaseType
FROM
	invoices
WHERE
	PurchaseType = 'Top Performers'
ORDER BY
	Total DESC;

Reference:

1. SQLite Home Page

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

2. SQL 教程

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

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