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.
https://youtu.be/XGAkqacrHDQ
Retail Context and Subject Areas
Retail Scenario: Normalized Data Model and Database
We will learn some of these statements/clauses using scenarios and examples.
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’.
