SQL Lab 04 - Using Functions

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 4.1: Adding Calculations to queries

Count: The COUNT() function returns the number of rows that matches a specified criterion.

SELECT
	count(LastName) AS [NameCount]
FROM
	customers
WHERE
	LastName like 'B%';

Lab 4.2: String concatenation

-- Lab 4.2.1
SELECT
	FirstName,
	LastName,
	FirstName || ' ' || LastName AS 'FULL Name'
FROM
	customers
WHERE
	Country = 'USA';

-- Lab 4.2.2	
SELECT
	FirstName || ' ' || LastName || ' ' || Address || ', ' || City || ', ' || State || ' ' || PostalCode AS 'Mailling Address'
FROM
	customers
WHERE
	Country = 'USA';

Lab 4.3: String truncation

length: The LENGTH() function returns the length of a string (in bytes).

substr: The SUBSTR() function extracts a substring from a string (starting at any position).

-- Lab 4.3.1
SELECT
	PostalCode,
	length(PostalCode) AS [length of Postal Code]
FROM
	customers
WHERE
	Country = 'USA';

-- Lab 4.3.2	
SELECT
	PostalCode,
	substr(PostalCode, 1, 5) AS [Five Digit Postal Code]
FROM
	customers
WHERE
	Country = 'USA';

Lab 4.4: Additional string function

upper: The UPPER() function converts a string to upper-case.

lower: The LOWER() function converts a string to lower-case.

SELECT
	FirstName AS [First Name Unmodified],
	upper(FirstName) AS [First Name in Uppercase],
	lower(FirstName) AS [First Name in Lowercase],
	upper(FirstName) || ' ' || upper (LastName) AS [Full Name in Uppercase]
FROM
	customers;

Lab 4.5: Data function

strftime: The strftime() function is used to format a datetime value based on a specified format.

SELECT 
	strftime('The Year is: %Y, The Month is: %m, The Day is: %d', '2011-05-22') AS [Text with conversion specifications];
SELECT
	LastName,
	FirstName,
	strftime('%Y-%m-%d', BirthDate) AS [Birthday NO Timecode],
	strftime('%Y-%m-%d', 'now') AS [Now],
	strftime('%Y-%m-%d', 'now') - strftime('%Y-%m-%d', BirthDate) AS [Age]
FROM
	employees
ORDER BY
	Age;	

Lab 4.6: Round() function

round: The ROUND() function rounds a number to a specified number of decimal places.

SELECT
	avg (total) AS AveSales,
	round (AVG(total), 2) AS [Rounder Average Sales]
FROM
	invoices;	

Lab 4.7: Round() function

round: The ROUND() function rounds a number to a specified number of decimal places.

SELECT
	avg (total) AS AveSales,
	round (AVG(total), 2) AS [Rounder Average Sales]
FROM
	invoices;	

Lab 4.8: GROUP BY clause

GROUP BY: The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

-- Lab 4.8.1
SELECT
	BillingCity,
	avg (Total)
FROM
	invoices
GROUP BY
	BillingCity
ORDER BY
	BillingCity;

-- Lab 4.8.2
SELECT
	BillingCity,
	avg (Total)
FROM
	invoices
WHERE
	BillingCity like 'L%'
GROUP BY
	BillingCity
ORDER BY
	BillingCity;

-- Lab 4.8.3
SELECT
	BillingCity,
	avg (Total)
FROM
	invoices
GROUP BY
	BillingCity
HAVING -- cannot use where
	AVG (Total) > 5
ORDER BY
	BillingCity;
	
-- Lab 4.8.4
SELECT
	BillingCity,
	avg (Total)
FROM
	invoices
WHERE
	BillingCity like 'B%'
GROUP BY
	BillingCity
HAVING -- cannot use where
	AVG (Total) > 5
ORDER BY
	BillingCity;
	
-- Lab 4.8.5
SELECT
	BillingCountry,
	BillingCity,
	avg (Total)
FROM
	invoices
GROUP BY
	BillingCity,
	BillingCountry
ORDER BY
	BillingCountry;

Reference:

1. SQLite Home Page

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

2. SQL 教程

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

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