SQL Lab 01 - Exploring a Database in SQLite

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 1.1: 

comment 

1. Writing comment in SQL: 

    Type in two dashes (--) before writing a comment. 

    Type in forward slash (/*) followed by a star (*) to write a block of comments 

2. Insert a comma to separate each fields with a comma (,). 

3. Insert a semicolon (;) to signify the end of an SQL statement. 

--This is a line of comment
/*
Created by: Peter
Created on: 2024/01/26
Description: This is a query to select all the records from albums table
*/
	SELECT *
	FROM albums;

Lab 1.2:

Basic SQL Query: 

1. Select some specific fields from customer table.

2. Insert a comma to separate each fields with a comma (,). 

3. Insert a semicolon (;) to signify the end of an SQL statement. 

/*
Created by: Peter
Created on: 2024/01/26
Description: This is a query to select first name, last name and email from customers table
*/
SELECT 
	FirstName,
	LastName,
	Email
FROM 
	customers;

Lab 1.3:

AS

We can rename the column name easily by use 'AS'.

Use the AS Command to rename a field, also know as adding an alias to the field name. 

/*
Created by: Peter
Created on: 2024/01/26
Description: This is a query to select first name, last name and email from customers table and rename the field names accordingly
*/
SELECT
	FirstName AS "FName",
	LastName AS [LName],
	Email AS EMAIL
FROM 
	customers;

Lab 1.4:

ORDER BY

Use ORDER BY clause to order records.

1. ASC: The ASC command is used to sort the data returned in ascending order.

2. DESC: Sorts the result set in descending order

/*
Created by: Peter
Created on: 2024/01/26
Description: This is a query to select first name, last name and email from customers table and rename the field names accordingly
Order them by first name (asc) and last name (desc)
*/
SELECT
	FirstName AS "FName",
	LastName AS [LName],
	Email AS EMAIL
FROM 
	customers
ORDER BY
	FirstName ASC,
	LastName DESC;

Lab 1.5:

LIMIT

The LIMIT command is very useful for extracting specific date.

/*
Created by: Peter
Created on: 2024/01/26
Description: Use the LIMIT clause to extracct an exact number of records.
*/
SELECT
	FirstName AS "FName",
	LastName AS [LName],
	Email AS EMAIL
FROM 
	customers
ORDER BY
	FirstName ASC,
	LastName DESC
LIMIT
	10;

Reference:

1. SQLite Home Page

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

2. sTunes.db

https://drive.google.com/file/d/1w_BuPeJmWEzloZmIUEpicuX_JnqskLC7/view?usp=sharing

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