Ramgopal Prajapat:

Learnings and Views

Setting up MYSQL instance on GCP and Learning SQL

By: Ram on Jan 22, 2021

In this blog, we will set up MYSQL instance on Google Cloud (GCP) and connect MySQL Workbench to GCP MYSQL Instance.

In part two, we will design a simple database schema for a retail scenario and create a list of tables. Also, we will load CSV files to the tables created.

In part 3, we will learn a few SQL scenarios using the setup. 

 

Part 1: Setting my MYSQL Working Environment

  1. Go to cloud.google.com/sql and follow the steps to launce MYSQL instance
  2. Enable access from a particular IP Address (you can get the IP address of your laptop/network using http://checkip.amazonaws.com/)
  3. Install MYSQL workbench on the local system by downloading from https://dev.mysql.com/downloads/workbench/
  4. Connect MYSQL workbench to the Google MYSQL Instance that you have created in step 1 (you will need the IP address of the MYSQL instance, username, and password)

https://youtu.be/XGAkqacrHDQ

 

 

Part 2: Database Design and Creating Tables

Retail Context and Subject Areas

Retail Scenario: Normalized Data Model and Database

Part 3: 10 SQL Scenarios for Learning

We will learn some of these statements/clauses using scenarios and examples.

  • SELECT
  • FROM
  • WHERE
  • ORDER BY
  • GROUP BY
  • LIMIT
  • Aggregate Function: COUNT
  • CASE WHEN

Scenario1: Show all attributes (column) and values (rows).

For example, we may want to see the list of stores and various information available about these stores.

SQL Code:

SELECT * FROM stores;

OUTPUT

 

Explanation:  SELECT clause suggests the attributes (columns) we want to show and the “*” option tells that we want all the columns.

FROM clause helps us to give the name of the table from which it should pull all the information. For this example, the table name is “stores” hence it is given after FROM keyword.

 

Scenario 2: Find information (for example, the column names and their type) about a table.

For example, want to find the name of columns of the order table.

SQL Code:

DESC orders;

OUTPUT

Explanation:  DESC (describe) clause helps in finding information about a table.  The DESC keyword followed up by a table name gives information about the table.

 

Scenario 3: Show only selected attributes (column) but all the values (rows).

For example, we want to see order ids, order value, and quantity.

SQL Code:

SELECT id, amount, qty FROM orders;

OUTPUT

Explanation:  We can use the SELECT clause to select columns and the requirement was to show order id, amount, and quantity, we can list the columns separated by a comma.

FROM clause helps us to give the name of the table from which it should pull all the information. For the above order details, we have the table “orders” to show the information from.

 

Scenario 4: Filter rows based on a condition (or multiple conditions)  

For example, we want to see all the items (or products) purchased on a particular order.

SQL Code:

SELECT * FROM order_items WHERE order_id=9;

OUTPUT

Explanation:  The WHERE clause helps in applying filter condition. Since we want to select items of a particular order (order id of 9), and the order id variable in the table “order_items” is “order_id”, hence we can use order_id =9.

 

Scenario 5: Rename or show different names for the columns while the display

For example, we want to show the id as “order No”, amount as “Order Amount” and Qty as “Order Quantity”.

SQL Code:

SELECT id as 'Order No', amount as "Order Amount", qty as 'Order Quantity'

FROM orders;

OUTPUT

Explanation:  In the SELECT clause after the input column name we can use AS to give a new name. If the new name has blank, we should use quotes.

 

Scenario 6: Create a new column (for display or creating in the output table) using existing column/s

For example, we want to create an offer amount column using MRP and Selling Price (selling_price) columns.

SQL Code:

select a.*,  (mrp-selling_price ) as offer_amount

from order_items a;

Explanation:  In the FROM clause, we can give an alias to the table after giving a character (for example “a” in the above code).  In the SELECT  clause,  we can keep all the columns of the input table that have been given alias as “a” by a.*  and then after separating with comma we can give a new column expression.

mrp-selling_price is the calculation and give the name as “offer_amount” using “as”.

 

Scenario 7: Show the ordered values

For example, show the latest or the oldest orders on the top from the list of the orders.

SQL Code:

SELECT * FROM orders

ORDER BY date;

OUTPUT

Explanation: The ORDER BY clause help in sorting the result by one or more columns. In the above example, the output is sorted by the “date” column.  If we want to sort by multiple columns you can list, the columns separated by a comma. The default sorting is done in ascending order (small to large).

 

If we want to sort in the order where a large value comes first or the oldest date to the latest date, we can use “DESC” after the column name.

Show the oldest orders first.

SELECT * FROM orders

ORDER BY date DESC;

Scenario 8: Show the few rows after sorting

For example, we want to show the oldest 5 orders.

SQL Code:

SELECT * FROM orders

ORDER BY date DESC

LIMIT 5;

 

OUTPUT

Explanation:  LIMIT clause or statement helps in limiting the number of rows to be printed or shown as output. In the above example ORDER BY with DESC help in sorting the values in descending order and LIMIT 5 controls the output to 5 rows. ORACLE has ROWNUM option/statement.

 

Scenario 9: Get summary statistics for categorical variable values.

For example, we want to find the number of orders processed by each of the cashiers.

SQL Code:

SELECT cashier_id, COUNT(*) as orders

FROM orders

GROUP BY cashier_id;

OUTPUT

Explanation: The GROUP BY clause along with the SELECT statement can arrange identical data into groups. Then we can use aggregate functions such as COUNT, SUM, etc to find the summary statistics for each of these groups.

For the above example, we want to group rows based on cashier_id values, and then the COUNT function will calculate the number of rows.

 

Scenario 10: Create a new variable/column and assign values based on conditions

For example, we want to create a flag that will indicate if a product is on an offer or not

SQL Code:

SELECT

id as item_no,

mrp,

selling_price,

(CASE WHEN (mrp-selling_price )>0 THEN 'On Offer'

ELSE 'No Offer'  END) as if_offer

FROM order_items;

 

OUTPUT

Explanation:  The CASE statement checks each condition and returns a value when a first condition is met.

In the above example, when MRP is greater than the selling price then it returns ‘ON OFFER’ otherwise returns ‘NO OFFER’ and gives this column as ‘IF_OFFER’.

Leave a comment