We have finished the course. I hope you enjoyed the course. Thank you for taking the course.

Download the class notes for day 10.

We have finished course WS24SQL10001. Now it is time to have fun working on the final (WS24SQL10001_FINAL.TXT).

Download the class notes for day 9.

We are getting for the final next week. We have covered how to retrieve (SELECT) data from tables or views as well as making (CREATE), modifying (ALTER) and destroying (DROP) data objects.

Download the class notes for day 8.

We are almost done with this course. Before we continue with data objects, we had a review of all material covered so far.

Download the class notes for day 7.

We have finally gotten our feet wet creating database objects.

    CREATE data_obj data_name [code];

    CREATE DATABASE db_name; -- no extra code

    CREATE SCHEMA schema_name; -- no extra code

    CREATE TABLE table_name -- code for structure
      (
      field1 data_type [arguments],
      field2 data_type [arguments]
      );

    CREATE VIEW view_name -- code for structure
    AS
    SELECT field1,
      field2
    FROM table1;

Download the class notes for day 6.

We are half-way done with the course. We have covered how to query data and manipulate the output using functions.

    SELECT FUNCTION(field1) AS alias,
      field2,
      field3
    FROM table1 AS t1
    INNER|LEFT|RIGHT JOIN table2 AS t2
      ON t1.shared_data = t2.shared_data
    GROUP BY field2, -- needed for aggregate functions
      field3
    ORDER BY field1;

We are almost ready to work with database objects.

Download the class notes for day 5.

We continue working with functions and have introduced aggregate functions. We have also started adding logic to queries using CASE clauses.

    CASE
      WHEN condition1
        THEN action1
      WHEN condition2
        THEN action2
      -- more conditions + actions
      ELSE escape_action
    END

Download the class notes for day 4.

We have started working with functions to manipulate strings.

    SELECT phone AS original_phone,  -- from `8005551205` to `(800) 555-1205`
      LEFT(phone, 3) AS area_code,  -- extracting `800`
      SUBSTRING(phone, 4, 3) AS branch_exchange,  -- extracting `555`
      RIGHT(phone, 4) AS subscriber_number,  -- extracting `1205`
      CONCAT (  -- concatenating values 1 to 6
        `(`,  -- value 1, hard-coded the opening parenthesis
          LEFT(phone, 3),  -- value 2, area code from `phone`
          `) `,  -- value 3, hard-coded closing parenthesis + space
        SUBSTRING(phone, 4, 3),  -- value 4, branch from `phone`
        ` - `,  -- value 5, hard-coded hyphen
        RIGHT(phone, 4)  -- value 6, subscriber number from `phone`
        ) AS legible_phone
    FROM table1;

Download the class notes for day 3.

We’ve started writing code.

    SELECT field1, field2...
    FROM table1 AS t1
    INNER|LEFT|RIGHT JOIN table2 AS t2
      ON t1.common_data = t2.common_data;

Do not forget to always make your code look good, formatted and legible — in other word, good etiquette. You might want to use a product like PoorSQL (free) either on-line or as a plug-in for a local program like Notepad++.

Download the class notes for day 2.

We have started a new course. We first talked about cookies and tomorrow we will start coding.

Download the class notes for day 9.