My attempt (8) at learning a coding language (SQL) in my mid 30s

This is the last post for SQL coding as I finished the 9 hours of video recording in the Udemy course. Very impressed that I had been so motivated to complete the course such that I had been attempting to do an hour or two everyday. 

Harlsten, CC BY-SA 4.0 <https://creativecommons.org/licenses/by-sa/4.0>, via Wikimedia Commons

This section was about: CASE, COALESCE, CAST, NULLIF, VIEW

1) CASE - only execute SQL code when certain conditions are met. Similar to IF/ELSE statements. There are two ways to use CASE as shown in the general syntax or an expression syntax. 

eg1. (general syntax)
SELECT column1,
CASE
       WHEN condition1 THEN result1
       WHEN condition2 THEN result2
       ELSE other_result AS new_name(this will rename the returned result column when the conditions are met)
END
FROM table;

eg1a. (general syntax)
CASE 
    WHEN (id < = 5) THEN 'Gold member'
    WHEN (id between 5 and 10 THEN 'Silver member'
    ELSE 'Green member'
END AS customer_class

eg2. (expression syntax)
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE other_result
END

It will straight away check if the value is equal to the value you specify and return the value correlated as the result, 

eg2a. (expression syntax)
CASE column1 WHEN a then 'other'
                           ELSE 'other'

So general syntax has more use since there are more variations to set the condition. 

We can also pass the CASE clause into mathematical operation.

eg3. Sum up the number of people in a database with John as their names.
SUM (CASE name
                WHEN 'John' then 1
                ELSE 0
END) AS No_of_John  

2) COALESCE - accepts an unlimited number of arguments and it will return the first non-null argument. If all arguments are null then COALESCE will return Null. 

eg1. SELECT COALESCE (1,2) 
Return 1
eg2. SELECT COALESCE (null,2,3)
Return 2

Useful when data contains null values and when we are substituting null value with another value. 

eg3. SELECT gift, (price - COALESCE(discount, 0))AS priceafterdiscount FROM table;

This will return the gift and the priceafterdiscount so if there is no discount given to the gift(which is null), the null value will be replaced with 0 and the calculation can proceeds.  This will not change the values in the data table but only make the substitution at the point of calculations. It is important to make the substitution since the calculation will not be performed with a null value as discount.  

3) CAST - let you convert one data type into another using the CAST or ::

eg1. Using the word CAST
SELECT CAST ('5' AS INTEGER)

eg2. Using the shortform operator ::
SELECT '5' :: INTEGER 
This will return 5 in string form into 5 as an integer however cast can only be used for reasonable conversion so trying to cast 'five' as INTEGER will prompt an error. 

eg3. To cast to a column
SELECT CAST ( column_name as TYPE)  

4) NULLIF - takes two inputs and returns NULL if both are equal, otherwise it will return the first argument. 

eg1. NULLIF(10,12) 
return 10 since they are not equal.

eg2. NULLIF(SUM(CASE WHEN name = 'John' THEN 1 ELSE 0 END),0)
This will compare if there are any John in the data, if there is then it will return 1 else it will return null since it is equal to 0. 

5) VIEW - a database object that is of a stored query and can be accessed as a virtual table. This allows us to quickly see a query that is used often.

eg1. to create a view
CREATE VIEW nameofview AS
SELECT QUERY

eg2. to recall the view
SELECT * FROM nameofview 

eg3. to replace the view
CREATE OR REPLACE VIEW nameofview AS
NEW CHANGED SELECT QUERY

eg4. to remove views
DROP VIEW IF EXISTS nameofview

eg5. to rename views
ALTER VIEW nameofview RENAME TO abetterview
SELECT * FROM abetterview

6) Import and Export Functionality - allows us to import data from a .csv file to an already existing table. The import command does not create the table automatically. There is no automated way to create a table directly from a .csv file. Very important that the file is compatible and the file path must be correct. 

Step 1) Ensure the csv filename does not have any spacing.
Step 2) CREATE TABLE with the correct number of headings as the csv table. 
Step 3) Rightclick on the tablename >> Import/Export data >> Filename(type in filename pathway). 
Step 4) Option on the Columns tab to deselect the columns that you do not need to import into SQL. 
Step 5) Miscellaneous >> Select if there is Header to ignore the first row header inside the csv table >> Delimiter and select ',' 
Step 6) SELECT * FROM table to all the imported data.  

Woohooo AND I completed my Udemy course! I find myself sucked into the discount period and had been purchasing courses after I am about to finish one...Paying for someone's work at making the videos and thinking about teaching material and sequence should be reasonable enough. Next up I am back to python practice or perhaps I will write on other topics for a while. ^^ See Ya.