Relearning SQL

There’s no argument that SQL is a must-learn language if you want to work with data. Like with any language, you need to keep notes to refresh yourself for future projects.

–

A POTPOURRI OF SQL

can use WHERE NOT COL = VAL for !=
Check if column is empty WHERE COL IS (NOT) NULL

–

Insert (into, values) – COLUMN FIRST THEN VALUES:

   INSERT INTO Customers (
   CustomerName, 
   Address, 
   City, 
   PostalCode,
   Country)
   VALUES(
   'Hekkan Burger',
   'Gateveien 15',
   'Sandnes',
   '4306',
   'Norway');

Update (include SET to change value)

 UPDATE Customers
 SET City = 'Oslo', 
 COUNTRY = 'Norway'
 WHERE CustomerID = 32;

Delete (Deletes all records from X – can be filtered)

 DELETE FROM Customers
 WHERE Country = 'Norway';

Like (can also use = instead of LIKE I believe but no regex)
- % = includes anything after like .* for regex
- example: so %a = ends with a; while%a% contains letter a
- can also do NOT LIKE
```
SELECT * FROM Customers
WHERE City LIKE 'a%'
```
Other wildcards with LIKE similar to regex:

LIKE '_a%' where second letter is an “a”
LIKE '[acs]%' where the first letter is an “a” or a “c” or an “s”.
[a-f] anything betwen a and f
[^b-x]anything not between b and x

boolean conditions:
- WHERE COUNTY IN ('NORWAY', ' FRANCE') using IN
- WHERE PRICE NOT BETWEEN 10 and 20 using BETWEEN
  - can use letters as alphabetically be between
CREATE VIEW, virtual table that is stored as a variable in a database
Distinctively, a view is saved in the database and can be reused by any query, whereas a WITH clause (or Common Table Expression) is tied to one specific query.
- In tandem, they can be used together in the following query:

CREATE VIEW q4iv(playerid, namefirst, namelast, salary, yearid)
AS
  WITH st(m00) AS (
    SELECT MAX(salary)
    FROM salaries
    WHERE yearid = 2000
    GROUP BY yearid),
  sh AS (
    SELECT MAX(salary) as m01
    FROM salaries
    WHERE yearid = 2001
    GROUP BY yearid)

  SELECT s.playerid, p.namefirst, p.namelast, s.salary, s.yearid
  FROM people p, salaries s, st a, sh b
  WHERE s.playerid = p.playerid AND (
  (yearid = 2000 and salary = a.m00)
  OR (yearid = 2001 and salary = b.m01))

when using WITH or VIEW you can order and name the variables of the table that is selected from SELECT inside a parantheses, so you do not have to use AS inside the SELECT statement.
- example:
```
WITH st(m00) AS (
    SELECT MAX(salary)
      
#VS: 
  
WITH sh AS (
    SELECT MAX(salary) as m00
```

HAVING or WHERE can have boolean conditions that include subqueries:

  HAVING s.yearid > (SELECT yearid
                  FROM salaries
                  ORDER BY yearid asc
                  LIMIT 1)
  #FILTERS GROUPS WITH YEARID > THE SMALLEST, 
  #AKA INCLUDE EVERYTHING BUT THE SMALLEST

OR:

#EXAMPLE OF CASTING TO A FLOAT
HAVING SUM(b.AB) > 50 and lslg >  (
    
    SELECT (CAST(SUM(b.H) - SUM(b.H2B) - SUM(b.H3B) - SUM(b.HR) + 2*SUM(b.H2B)
     + 3*SUM(b.H3B) + 4*SUM(b.HR) AS float)/CAST(SUM(b.AB) AS float)) AS lslg
     FROM batting b
     JOIN people p ON b.playerid = p.playerid
     WHERE b.playerid = 'mayswi01'
     GROUP BY b.playerid
  )

–

Some Examples:

Getting the first or last value for each user in a table, we will use transactions as an example, inner join on the id’s that were filtered out so it includes additional variables that were removed from groupby:

      SELECT t.user_id, t.created_at, t.product
      FROM transactions AS t
      INNER JOIN (
          SELECT user_id, MIN(created_at) AS min_created_at
          FROM transactions
          GROUP BY 1
      ) AS t1
          ON t.user_id = t1.user_id
            t.created_at = t1.min_created_at
                
 =========================================================================
#Initial:
         user_id | created_at | product  
       --------+------------+--------
        123    | 2019-01-01 | apple    
        456    | 2019-01-02 | banana   
        123    | 2019-01-05 | pear    
        456    | 2019-01-10 | apple   
        789    | 2019-01-11 | banana  
    
#Solution:
       user_id | created_at | product   
      ---------+------------+--------
       123     | 2019-01-01 | apple      
       456     | 2019-01-02 | banana     
       789     | 2019-01-11 | banana

Reminder, left join allows null values to exist for variables (so not omitted from inner joins):

      INNER JOIN: returns rows when there is a match in both tables. 
      LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.
            
      cities.name  | users.id
      _____________|__________
      seattle      | 123
      seattle      | 124
      portland     | null #no users in users table that live in portland, but still included
      san diego    | 534
      san diego    | 564

For more resources (that are take more digestion to learn):

Recursive CTE or WITH statements: here
Create grouping sets through CUBE: here
COALESCE: here
Create grouping sets with assumed hiearchy through ROLLUP: here
Future notes here