SQL Data Retrieval Project

Fuka Ono / 小野楓華
Student of McGill University
OVERVIEW

MySQLを使用し、8問のプロジェクト課題に取り組みました。 English description below Japanese.

YEAR 2024 - 2024

SQLのスキルを活用し、Udemyから提供されたデータベースのさまざまなテーブルからデータを抽出・分析しました。

1問目は、全てのスタッフのfirst, last nameを含んだリストを作成　

(SELECT, FROM)


2問目は、COUNTを使い、二つのストアにおける在庫個数をリスト化 

(SELECT, COUNT, AS, FROM, GROUP BY)

3問目は、active customersをテーブルからCOUNT

(SELECT, COUNT, AS, FROM, WHERE, GROUP BY)

4問目は、emailアドレスをCOUNT

(SELECT, COUNT, AS, FROM)

5問目は、ユニークな題名の映画をCOUNTし、ユニークなカテゴリーをCOUNT

(SELECT, COUNT, DISTINCT, AS, FROM, GROUP BY)

6問目は、replacement costの最も低いもの、最も高いもの、平均をリスト化

(SELECT, MIN, MAX, AVG, AS, FROM)

7問目は、payment tableにあるpaymentで最も高いもの、平均を抽出

(SELECT, AVG, MAX, AS, FROM)

8問目は、customer identificationをリスト化し、今までの記録にあるレンタル回数をCOUNTし、降順にリスト化 

(SELECT, COUNT, AS, FROM, GROUP BY, ORDER BY, DESC)


English Description

- Conducted comprehensive data retrieval tasks using SQL to support business operations and decision-making.

- Generated detailed lists of staff members with their respective store assignments.

- Analyzed inventory data to provide separate counts of items for each store.

- Determined the number of active customers for each store.

- Assessed potential liability by counting customer email addresses stored in the database.

- Evaluated the diversity of film offerings by counting unique film titles and categories in inventory.

- Calculated replacement costs for films, identifying the least, most expensive, and average costs.

- Monitored payment processing to detect fraud by determining average and maximum payment amounts.

- Analyzed customer rental history to identify top customers by volume of rentals.


Key SQL Skills Demonstrated:

  • SELECT statements for data extraction
  • Aggregation functions (COUNT, MIN, MAX, AVG)
  • GROUP BY and ORDER BY clauses
  • Data filtering with WHERE conditions


WEBSITE