-- ##########################################################
-- SQL is in some extent, just as any other computer language
-- it consists of STATEMENTS and KEYWORDS.
-- SELECT is the most commonly used statement
-- it is to pick some data (could be constant) from a table 
-- or any equivalents of tables (such as views, or subqueries, etc):
-- the following is the simplest query:
-- ##########################################################
SELECT 5;  -- the simpliest query, does nothing but return the constant '5'
SELECT 5*7;  -- it will return the calculation result '35'

-- Better to use alias:
SELECT 5 AS V1;  -- the simpliest query, does nothing but return the constant '5'
SELECT 5*7 AS V2;  -- it will return the calculation result '35'



-- ##########################################################
-- SELECT Everything: 
-- ******************
-- Let's do one example:
-- Please find me the entire list of the Customers in ecomm database:
-- ##########################################################

SELECT * FROM Customer;

-- you may try the following (uncomment first):
-- SELECT * FROM OrderHead;
-- SELECT * FROM OrderItem;
-- SELECT * FROM Product;
-- SELECT * FROM Supplier;

-- ##########################################################
-- Use 'ORDER BY' to sort:
-- ***********************
-- let's order the Customers by Country:
-- ##########################################################

-- by default, order in ascending order:
SELECT * FROM Customer ORDER BY Country ASC;
-- the above is equivalent to the following:
SELECT * FROM Customer ORDER BY Country;
-- sort by descent order: 
SELECT * FROM Customer ORDER BY Country DESC;


-- ##########################################################
-- we can order by multiple columns:
-- *********************************
-- Let's order by Country and then City: 
-- ##########################################################
SELECT * FROM Customer ORDER BY Country, City;


-- ##########################################################
-- FILTER DATA:
-- ************
-- so far we are selecting all the contents of the table
-- how about we apply some filters?
-- ##########################################################


-- ##########################################################
-- FILTER DATA vertically (by column):
-- ***********************************
-- You can pick the columns of interest, rather than asking 
-- for everything:
--
-- Example 1: Can you find me the names and the Phone number 
--            for all Customers?
-- ##########################################################

SELECT FirstName, LastName, Phone FROM Customer;  


-- ##########################################################
-- FILETER DATA horizontally (by row): 
-- **************************************************
-- We can use WHERE clause to filter the result by 
-- only returning the rows that satisfy the criteria
-- stated in WHERE clause:
--
-- Example 2: Can you get me a list of all Customers from Germany? 
-- ##########################################################

SELECT * FROM Customer WHERE Country="Germany";
-- ATTENTION: 
-- We can take this time to explore the case sensitivity:
-- KEYWORDS are NOT case sensitive
-- But VALUES stored in the database may be case sensitive
-- (depending on configuration)
-- the following query doesn't return anything

SELECT * FROM Customer WHERE Country="GERMANY";

-- common way to avoid this sensitivity to data abnormalty:
-- approach 1: modify database setting to make it CASE-INSENSITIVE
-- in SQLite, there are two ways to do so:
-- a. specify case insensitivity when table is created.
-- b. specify case insensitivity when query data
-- Here is the example for option b:

SELECT * FROM Customer WHERE Country="GERMANY" COLLATE NOCASE;

-- approach 2: use string function UPPER() or LOWER() to normalize data
-- for example:

SELECT * FROM Customer WHERE UPPER(Country)="GERMANY";

-- ##########################################################
-- FILETER DATA horizontally (by row): 
-- **************************************************
-- WHERE clause can have multiple criteria combination:
-- Logical Operators in SQL: AND, OR, NOT 
-- Example 3: how about search for me the Customers from Köln, Germany? 
-- ##########################################################

SELECT * FROM Customer WHERE Country="Germany" AND (City="Köln" OR City="Berlin");

-- you can use () to regroup the priority:
SELECT * FROM Customer WHERE Country="Germany" AND NOT (City="Köln" OR City="Berlin");

-- ##########################################################
-- FILTER DATA both horizontally AND vertically: (Exercise)
-- **********************************************
-- We can apply filters both on row AND on column simutaneously
--
-- Example 4: Can you find me the Phone number for Customer named "Alexander Feuer"?
-- ##########################################################

-- Please try to write the query by yourself. 




SELECT FirstName, LastName, Phone FROM Customer 
WHERE FirstName="Alexander" AND LastName="Feuer";

-- ##########################################################
-- EXTRA WAYS TO FILTER DATA: 
-- **************************
-- use 'LIMIT' 
-- We can also use keyword 'LIMIT' to limit the returned rows to 
-- the specified lines in LIMIT: 
-- 
-- Example 5: Can you give me the first 10 Customers' information? 
-- ##########################################################

SELECT * FROM Customer LIMIT 10;


-- ##########################################################
-- 'LIMIT' does not need to be from the very beginning: 
-- ****************************************************
-- Example 6: Can you give me the 10 Customers after the 50th Customer?
-- ##########################################################

SELECT * FROM Customer LIMIT 50, 10;


-- ##########################################################
-- ATTENTION: 'LIMIT' does not belong to ANSI/ISO SQL standard.
-- so it is NOT ALWAYS supported. It is supported in SQLite 
-- and MySQL, but not in SQL Server and Oracle. There might be
-- some other keywords for the similar function. For example,
-- in SQL Server, it is 'TOP', and in Oracle, it is 'ROWNUM'
-- THIS IS WHERE things get ugly.
-- ##########################################################


-- ##########################################################
-- Now we move on to aggregation. 
-- Most commonly used aggregation functions in SQL:
-- ************************************************
-- COUNT() : get the numbers of lines
-- MIN(): get the minimum value
-- MAX() : get the maximum value
-- AVG() : get average value  
-- SUM() : get the sum of each line's values of the interest column

-- NOTE: the aggregation function can apply not only to actual 
-- single column of the table, but also to calculated column or other expression
-- COUNT() can apply to all individual columns OR to column combination. 
-- MIN(), MAX(), AVG(), or SUM() can only be applied for single column/calculated value column
-- Example 1: Do you know how many total Customers we have? 
-- ##########################################################

SELECT COUNT(*) FROM Customer;
-- use alias to make the result more readable:
SELECT COUNT(*) AS total_customer_count FROM Customer;

-- ##########################################################
-- Use 'DISTINCT' to remove duplicates:
-- ************************************
-- Example 2: How many countries do our Customer scatter? Can you list them all? 
-- ##########################################################

-- ##########################################################
-- Step 1: How to get the country list?
-- ##########################################################

SELECT DISTINCT Country FROM Customer;

-- ##################################################################
-- Step 2: Count the unique country list? using COUNT() with DISTINCT
-- ##################################################################

SELECT COUNT(DISTINCT Country) AS total_country_count FROM Customer;

-- ##########################################################
-- ##########################################################
-- Exercise:
-- Can you apply the above technique to Supplier table?
-- 1. can you get me the full list of them?  
-- 2. how many suppliers do we have?
-- ##########################################################

-- Please try to write the query by yourself. 
-- hints: query on Supplier table using CompanyName column










-- Here is my query answer:

-- get all the suppliers:
SELECT CompanyName FROM Supplier;
-- get unique list of suppliers:
SELECT DISTINCT CompanyName FROM Supplier;
-- get counts of the unique suppliers:
SELECT COUNT(DISTINCT CompanyName) AS total_supplier_count FROM Supplier; 



-- ##########################################################
-- ATTENTION: In the above case, we got 29 records from both
-- query - that is, the Supplier table contains no duplicate
-- data for same company.
-- but in reality, how can we find out if there are duplicates
-- in the table and if so, how is the data like?
-- ##########################################################


-- ##########################################################
-- 'GROUP BY' to count on each group according to the column(s) of interest:
-- ****************************************************************
--
-- Example 3: I'd like to know the customer distribution by Country.
--            Can you help me? 
-- ##########################################################

SELECT Country, COUNT(*) AS customer_count FROM Customer GROUP BY Country;

-- sort by Customer_count:
SELECT Country, COUNT(*) AS customer_count FROM Customer GROUP BY Country 
ORDER BY customer_count DESC;

-- from the result of the above query, we will know for each country how many customers we have

-- ##########################################################
-- So far, we are only dealing with single table
-- that is, everything we ask can get the answer from one table. 
-- What about sometimes we need to combine multiple tables to get the answer?
-- For examples, I'd like to know:
-- 1.	Who is the biggest market for our company sales? 
-- We need JOIN tables
-- ##########################################################
 
-- ##########################################################
-- NOW Let's move on to 'JOIN'
-- ***************************
-- JOIN is the most important and most powerful strength SQL lies
-- to accomplish complicated query logic. 
-- There are several different types of JOIN. 
-- The most commonly used is the INNER JOIN, often considered the 
-- 'default' JOIN type, so 'INNER' keyword can be omitted 
-- when you write inner join of two tables. 
-- But this will not be the cases for OUTER JOIN - you must 
-- specify either it's a LEFT JOIN or a RIGHT JOIN or a FULL JOIN.
-- INNER JOIN (JOIN): grab all the records that appear in both tables
-- OUTER JOIN:
-- 1) LEFT JOIN: grab all the records from the left table, merge
--            the right table's data if they satisfy the JOIN 
--            condition
-- 2) RIGHT JOIN: similar to LEFT JOIN, but full set is from right
--             table.
-- 3) FULL JOIN: get all the data records from both tables, merge 
--               those can be merged
-- We will only do an INNER JOIN example below. 


-- Let's go through the JOIN example 1 step by step to demonstrate 
-- what JOIN does and how to use it
-- 
-- Example 1: 
-- Question: which country is the biggest market for our company sales? 
-- To answer this question, we need to look at 'Customer' and 
-- 'OrderHead' table and connect the two based on 'CustomerId', 
-- which connects the two tables. We don't need OrderItem table
-- since we are only interested in total sales.
-- ##########################################################

-- step 1: 
-- JOIN Customer and OrderHead
SELECT * FROM Customer AS c INNER JOIN OrderHead AS oh ON c.Id=oh.CustomerId;

-- step 2: 
-- We know we are interested in TotalAmount field of each line
-- with information of associated Country, we don't care all the else 
-- (vertical filter).

SELECT Country, TotalAmount 
FROM Customer AS c INNER JOIN OrderHead AS oh 
ON c.Id=oh.CustomerId;

-- step 3:
-- now I need to calculate the sum of the TotalAmount field grouped by
-- each Country
 
SELECT Country, SUM(totalAmount) AS totalSales 
FROM Customer AS c INNER JOIN OrderHead AS oh 
ON c.Id=oh.CustomerId 
GROUP BY Country;

-- step 4: 
-- finally we just sort the result in descending order and take the top 1

SELECT Country, SUM(totalAmount) AS totalSales 
FROM Customer AS c INNER JOIN OrderHead AS oh 
ON c.Id=oh.CustomerId 
GROUP BY Country 
ORDER BY totalSales DESC
LIMIT 1;




-- ##########################################################
-- ##########################################################
-- Example 2: (Exercise)
-- Now we add some constraint:
-- Which foreign Country is the biggest market for our company sales? 
-- (assume the company is a US company).
-- To answer this question, we do the same AS in Example 1, 
-- but need to exclude our domestic orders.
-- ##########################################################

-- Please try to write the query by yourself. 













-- HERE IS MY QUERY:
SELECT Country, SUM(totalAmount) AS totalSales 
FROM Customer AS c INNER JOIN OrderHead AS oh on c.Id=oh.CustomerId
WHERE Country != "USA"  -- everything keeps same as above, except for this line
GROUP BY Country 
ORDER BY totalSales DESC
LIMIT 1;




-- ##########################################################
-- DATA MODIFICATION LANGUAGE: INSERT/UPDATE/DELETE:
-- *************************************************
-- Up to this point, we have only mentioned 'SELECT' statement. 
-- There are a lot more in SQL. 
-- Here are three other commonly used ones, and since they are all able to 
-- change the table content, they are categorized as 'DML', that is, 
-- 'Data Modification Language'.
-- INSERT : is used to add new record to database table;
-- DELETE : is used to remove record from database table;
-- UPDATE : is used to modify existing record in database table;
-- ##########################################################

-- ##########################################################
-- Before we proceed, let's first make a copy of our original
-- table and we will practice these new DML skills upon the
-- copy of the tables. So the original tables are NOT touched.
-- We need to use 'CREATE' STATEMENT 
-- ##########################################################


-- ##########################################################
-- CREATE TABLE:
-- *************
--
-- Example 1 : -- Create a Customer table copy 'Customer_test'
-- Check in DB Browser's structure table to verify that 'Customer_test'
-- table doesn't exists or
-- Use the following SQL statement to guarantee this:
-- ##########################################################

CREATE TABLE Customer_test AS SELECT * FROM Customer;

-- Now check in DBBrowser's structure table to verify that 'Customer_test'
-- table now does exist and have the content

-- ##########################################################
-- Exercise (optional): 
-- create a copy 'Supplier_test' for Supplier table
-- ****************************************************** 









-- Here is my query: 
CREATE TABLE Supplier_test AS SELECT * FROM Supplier;


-- ##########################################################
-- use 'UPDATE' to modify data:
-- ****************************
--
-- Example 1: Now let's update some Customer information:
-- first try to find some Customer record whose Phone contains ';'
-- we can use 'LIKE' to do the pattern match
-- "%" is the string wild match 
-- ##########################################################

SELECT * FROM Customer_test WHERE Phone LIKE "%;%";


-- ##########################################################
-- use 'UPDATE' to modify data:
-- ****************************
--
-- Example 1: Now let's update some Customer information:
-- let's do the actual removal of ';' by using 'UPDATE' 
-- ##########################################################

UPDATE Customer_test
SET Phone=REPLACE(Phone, ";", "");


-- ##########################################################
-- check if the data has been updated
-- ##########################################################

SELECT * FROM Customer_test
WHERE Phone LIKE "%;%";
-- ##########################################################
-- we see that there is no record contains ';' in Phone column
-- ##########################################################


-- ##########################################################
-- use 'DELETE' to remove data horizontally:
-- *****************************************
--
-- Example 1: Let's delete the first Customer (Id=1):
-- ##########################################################

DELETE FROM Customer_test
WHERE Id=1;

-- Verify the first Customer (Id=1) was removed:
-- the following query shall NOT return anything
SELECT * FROM Customer_test
WHERE Id=1;



-- ##########################################################
-- use 'DELETE' to remove entire table data:
-- *****************************************
--
-- Example 2: Let's delete all the record in Customer_test table
-- ##########################################################

DELETE FROM Customer_test;


-- ##########################################################
-- Example 3: now the table is empty, but the table itself does
-- exist still (see the DB schema view)
-- When you use 'DELETE FROM' to delete data from table,
-- you only remove the data from it, you don't touch the TABLE
-- schema. Table is still there (Otherwise, the query will report
-- table not exist error)
-- TAKE AWAY POINT: 
-- Always bear in mind, data value and schema are two different 
-- things in database logics.

-- ##########################################################

SELECT * FROM Customer_test;
-- return 0 line, but no ERROR!


-- ##########################################################
-- use 'INSERT' to populate table with data:
-- *****************************************
--
-- Example 4: reload the Customer table from the original 
-- ##########################################################

INSERT INTO Customer_test SELECT * FROM Customer;
 
-- Let's see if the Customer_test is repopulated: 
-- Check if the record is inserted correctly:

SELECT * FROM Customer_test;

-- ##########################################################
-- BE CAREFUL: use 'INSERT INTO' STATEMENT as above may not 
-- be ideal  - the schema information may be lost (check DB
-- Schema to compare 'Customer' and 'Customer_test' tables)
-- ##########################################################



-- ##########################################################
-- populate table with data with individual record:
-- ************************************************
--
-- what if we have a brand new user
-- Example 5: INSERT new Customer by providing values:
-- ##########################################################

INSERT INTO Customer_test (Id, FirstName, LastName, City, Country, Phone)
VALUES (92, "John", "Smith", "Boston", "USA", "(617)123-4888");

-- Check if the record is inserted correctly: 

SELECT * FROM Customer_test
WHERE FirstName="John" AND LastName="Smith";


-- ##########################################################
-- use 'DROP' to remove structure elements:
-- *****************************************
-- SQL has many 'DROP' statement:
--   DROP TABLE: remove table
--   DROP VIEW: remove view
--   DROP COLUMN: remove column
--   DROP INDEX: remove index
--   ...
--   many more
-- 'DROP' syntax is a bit complicated with a lot of variations
-- it often combines with 'ALTER ...' 
-- the rule of thumb - anything that you can create by using 
-- 'CREATE ....' would be able to use 'DROP ...' to remove it
--
-- Example 6: This is the last example.
-- Finally, let's clean up the database by 
-- removing Customer_test
-- Here we can use 'DROP TABLE' statement
-- ##########################################################

DROP TABLE Customer_test;

-- drop any other copies of tables (if you ever created):
-- DROP TABLE Supplier_test;

-- ##########################################################
-- THIS IS THE END OF TUTORIAL SQL SCRIPT
-- ##########################################################


