Practicum 2 - Introduction to SQL

Prerequisite

Before starting this practicum, please complete the following steps:

  1. Install DB Browser for SQLite from the following link:
    Download DB Browser for SQLite

  2. Follow the installation video at the following link:
    Installation Video

  3. Run the installed DB Browser for SQLite application.

Database

Download Database

Please download the Chinook database from the following link:

Download Database

Chinook Database

Chinook is a sample database available for SQLite, SQL Server, Oracle, MySQL, and others.

This database can be created by running a single SQL script. The Chinook database is an alternative to the Northwind database and is ideal for demonstrating and testing ORM tools that target single and multiple database servers.

Supported Database Servers

The Chinook database supports the following database servers:

  • SQLite
  • MySQL
  • SQL Server
  • SQL Server Compact
  • PostgreSQL
  • Oracle
  • DB2

Data Model

The Chinook data model represents a digital media store, including tables for artists, albums, media tracks, invoices, and customers.

The database contains several related tables, including:

  • media_types
  • playlists
  • playlist_track
  • tracks
  • genres
  • artists
  • albums
  • invoices
  • invoice_items
  • customers
  • employees

Insert the Chinook database model diagram here.

Example Data

Media-related data were created using real data from an iTunes library.

Customer and employee information was manually created using fictitious names, addresses that can be found on Google Maps, and other properly formatted information such as telephone numbers, fax numbers, email addresses, and others.

Sales information was automatically generated using random data over a four-year period.

Why is it Called Chinook?

The name of this sample database is based on the Northwind database.

Chinook is the name of a wind found in inland western North America, where the Canadian Prairies and the Great Plains meet several mountain ranges.

Chinook winds are most common in southern Alberta, Canada.

Chinook is therefore an appropriate name for a database intended to serve as an alternative to Northwind.

What is Inside Chinook?

The Chinook database contains multiple interconnected tables representing a digital media store.

The database structure includes information about:

  • media types,

  • playlists,

  • tracks,

  • genres,

  • artists,

  • albums,

  • invoices,

  • invoice items,

  • customers, and

  • employees.

Import Database

The following steps can be used to import the database into DB Browser for SQLite.

Step 1

Click Open Database.

Step 2

Find and select the file:

Chinook_Sqlite.sqlite

in your directory.

Then click Open.

Step 3

The DB Browser interface should then display the database structure.

Introduction to SQL

SELECT Command

The SELECT command is used to select data or tables from a database.

In general, the SELECT command can be written as follows:

SELECT column1, column2, ...
FROM table_name;

column1, column2, and so on represent the field names or column names that will be selected from the data or table.

If all columns or fields from a table need to be selected, the following syntax can be used:

SELECT *
FROM table_name;

Example 1

Suppose we want to select the trackid, name, composer, and unitprice columns from the tracks table in the Chinook database.

SELECT Trackid, Name, Composer, Unitprice
FROM Track;

ORDER BY Command

Data sorting can be performed using the ORDER BY command.

In general, the syntax can be written as follows:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

The column names written after the ORDER BY command are used to sort the data.

The keywords ASC and DESC indicate:

  • ASC: ascending order, from smaller to larger values.
  • DESC: descending order, from larger to smaller values.

By default, SQLite sorts data in ascending order.

Example 2

Suppose we want to display the name, milliseconds, and albumid columns and sort the data based on the albumid column.

SELECT Name, Milliseconds, Albumid
FROM Track
ORDER BY Albumid;

Example 3

Another example is when we want to sort the data based on Milliseconds in descending order and Albumid in ascending order.

SELECT Name, Milliseconds, Albumid
FROM Track
ORDER BY Albumid;

DISTINCT Command

DISTINCT is used to display unique rows or values in a data table.

In general, it can be written as follows:

SELECT DISTINCT column1, column2, ...
FROM table_name;

Example 4

Suppose we want to identify the locations of the customers.

SELECT DISTINCT City
FROM Customer
ORDER BY City;

WHERE Command

WHERE is used to filter rows based on one or more conditions or logical statements.

In general, it can be written as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example 5

Suppose we want to identify tracks from album 1 that have a duration greater than 25000.

SELECT Name, Milliseconds, Bytes, Albumid
FROM Track
WHERE albumid = 1 AND milliseconds > 250000;

LIMIT Command

LIMIT is used to restrict the number of rows displayed.

In general, it can be written as follows:

SELECT column1, column2, ...
FROM table_name
LIMIT row_count;

Example 6

Suppose we want to display the first 10 rows from the trackid and name columns.

SELECT TrackId, Name
FROM Track
LIMIT 10;

BETWEEN Command

BETWEEN is used to determine whether a value is within a particular range.

In general, it can be written as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example 7

Suppose we want to use the InvoiceId, BillingAddress, and Total information to identify invoices whose total value is between 14.96 and 18.86.

SELECT InvoiceId, BillingAddress, Total
FROM Invoice
WHERE Total BETWEEN 14.91 and 18.86
ORDER BY Total;

IN Command

IN is used to check whether a value matches at least one value in a list.

In general, it can be written as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IN (value1, value2, ...);

Example 8

Suppose we use the tracks data in the database and want to filter tracks with media types 1 and 2.

SELECT TrackId, Name, Mediatypeid
FROM Track
WHERE MediaTypeId IN (1, 2)
ORDER BY Name ASC;

IS NULL Command

IS NULL is used to check whether a value is null.

In general, it can be written as follows:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

Example 9

Suppose we use the tracks table in the Chinook database and want to filter records where the Composer column is null.

SELECT TrackId, Name, Composer
FROM Track
WHERE Composer IS NULL
ORDER BY Name ASC;

GROUP BY Command

The GROUP BY command is used to group identical values into a summary.

This command is usually used together with aggregate functions such as:

  • COUNT
  • MAX
  • MIN
  • SUM
  • AVG

These functions can be applied to each group.

In general, the GROUP BY command can be written as follows:

SELECT column1, column2, ...
FROM table_name
WHERE condition
GROUP BY column1, column2, ...;

Example 10

Suppose we use the tracks data in the database and want to calculate the number of tracks in each album.

This can be done using the albumid and trackid columns.

To calculate the number of tracks, the COUNT command can be applied to the trackid column.

SELECT Albumid, COUNT(Trackid)
FROM Track
GROUP BY Albumid;

HAVING Command

The HAVING statement is similar to the WHERE statement.

WHERE is used to filter data before grouping with GROUP BY, while HAVING is used to exclude data after grouping.

In general, the HAVING command can be written as follows:

SELECT column1, column2, aggregate_function(column_3), ...
FROM table_name
GROUP BY column1, column2,
HAVING search_condition;

Example 11

Suppose that, based on the previous GROUP BY example, we want to see how many tracks belong to AlbumID = 1.

SELECT Albumid, COUNT(Trackid)
FROM Track
GROUP BY Albumid
HAVING Albumid=1;

Alternatively, we can use WHERE as follows:

SELECT Albumid, COUNT(Trackid)
FROM Track
WHERE Albumid = 1
GROUP BY Albumid;

Creating a Table Based on SQL Operation Results

To store the result of an SQL operation as a table in the database, the following command can be used:

CREATE TABLE Track_Media12 AS
SELECT TrackId, Name, Mediatypeid
FROM Track
WHERE MediaTypeId IN (1, 2)
ORDER BY Name ASC;

The resulting table is named:

Track_Media12

Export to CSV

To export a table from the database into a CSV file using DB Browser, follow these steps.

Step 1

Click File.

Step 2

Click Export.

Step 3

Click:

Table(s) as CSV file…

Step 4

Find the table named:

Track_Media12

Scroll down if the table is not immediately visible.

Then click Save.

Step 5

Choose the directory and folder that will be used to save the file.

Then click Select Folder.

In the illustration, the folder used to save the file is:

New folder(2)

Step 6

Click OK.

Independent Exercise

Use the Mental Health database, which can be downloaded from the following link:

Download Mental Health Database

Dataset Information

This data is from the Open Source Mental Illness (OSMI) using survey data from the years:

  • 2014
  • 2016
  • 2017
  • 2018
  • 2019

Each survey measures attitudes towards mental health and the frequency of mental health disorders in the tech workplace.

The raw data was processed using Python, SQL, and Excel for cleaning and manipulation.

Steps involved in cleaning were:

  • Similar questions were grouped together.
  • Values for answers were made consistent, for example 1 == 1.0.
  • Spelling errors were fixed.

Content

The SQLite database contains three tables:

  • Survey
  • Question
  • Answer

Survey Table

Survey (
  PRIMARY KEY INT SurveyID,
  TEXT Description
)

Question Table

Question (
  PRIMARY KEY QuestionID,
  TEXT QuestionText
)

Answer Table

Answer (
  PRIMARY/FOREIGN KEY SurveyID,
  PRIMARY KEY UserID,
  PRIMARY/FOREIGN KEY QuestionID,
  TEXT AnswerText
)

SuveyID represents the survey year, for example:

  • 2014
  • 2016
  • 2017
  • 2018
  • 2019

The same question can be used for multiple surveys.

The Answer table is a composite table with multiple primary keys.

SurveyID and QuestionID are foreign keys.

Some questions can contain multiple answers. Therefore, the same user can appear more than once for a particular QuestionID.

Questions

Based on the Mental Health database, answer the following questions using SQL.

Question 1

Show the gender categories of the survey respondents.

Which gender has the smallest number of respondents?

Hint:

COUNT(*) as N

Question 2

Show the countries of origin of the survey respondents.

Sort the countries based on the largest number of respondents.

Hint:

COUNT(*) as N