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.

It has been great experience teaching this class and I hope you had fun — aside from learning SQL, of course.

    CREATE PROCEDURE final.message_sp
    AS
    BEGIN
      DECLARE @yourName VARCHAR(50) = 'your_name',   -- param to hold your name
              @CourseCd VARCHAR(15) = 'WS23SQL1001'; -- code for the SQL course
      PRINT CONCAT ( CHAR(084), CHAR(104), CHAR(097), CHAR(110), CHAR(107),
          CHAR(032), CHAR(121), CHAR(111), CHAR(117), CHAR(044), CHAR(032),
          @yourName, CHAR(044), CHAR(032), CHAR(102), CHAR(111), CHAR(114),
          CHAR(032), CHAR(116), CHAR(097), CHAR(107), CHAR(105), CHAR(110),
          CHAR(103), CHAR(032), CHAR(099), CHAR(108), CHAR(097), CHAR(115),
          CHAR(115), CHAR(032), @CourseCd, CHAR(046), CHAR(013), CHAR(083),
          CHAR(101), CHAR(101), CHAR(032), CHAR(121), CHAR(111), CHAR(117),
          CHAR(032), CHAR(105), CHAR(110), CHAR(032), CHAR(116), CHAR(104),
          CHAR(101), CHAR(032), CHAR(105), CHAR(110), CHAR(116), CHAR(101),
          CHAR(114), CHAR(109), CHAR(101), CHAR(100), CHAR(105), CHAR(097),
          CHAR(116), CHAR(101), CHAR(032), CHAR(099), CHAR(108), CHAR(097),
          CHAR(115), CHAR(115), CHAR(046), CHAR(013), CHAR(013), CHAR(070),
          CHAR(046), CHAR(079), CHAR(108), CHAR(118), CHAR(101), CHAR(114),
          CHAR(097), CHAR(032), CHAR(040), CHAR(102), CHAR(111), CHAR(108),
          CHAR(118), CHAR(101), CHAR(114), CHAR(097), CHAR(064), CHAR(098),
          CHAR(109), CHAR(099), CHAR(099), CHAR(046), CHAR(099), CHAR(117),
          CHAR(110), CHAR(121), CHAR(046), CHAR(101), CHAR(100), CHAR(117),
          CHAR(041), CHAR(013), CHAR(104), CHAR(116), CHAR(116), CHAR(112),
          CHAR(058), CHAR(047), CHAR(047), CHAR(102), CHAR(111), CHAR(108),
          CHAR(118), CHAR(101), CHAR(114), CHAR(097), CHAR(046), CHAR(099),
          CHAR(111), CHAR(109), CHAR(109), CHAR(111), CHAR(110), CHAR(115),
          CHAR(046), CHAR(103), CHAR(099), CHAR(046), CHAR(099), CHAR(117),
          CHAR(110), CHAR(121), CHAR(046), CHAR(101), CHAR(100), CHAR(117),
          CHAR(047) );
    END;

    EXECUTE final.message_sp;

Download the class notes for day 10.

We have finished all new material and we are getting ready for the final.

Download the class notes for day 9.

We are two sessions away from finishing the course.

Download the class notes for day 8.

We continue creating, dropping and altering objects as well as inserting, deleting and truncating tables.

Download the class notes for day 7.

We are now seeing how to CREATE, ALTER and DROP data objects. This is the really fun part of the course.

Download the class notes for day 6.