SQL

Importance of Learning SQL & Related Technologies

Francisco Olvera

Many new programmers don’t dedicate time to learning SQL (regardless of vendor and its extensions) and/or understanding how data is stored, yet expect to call and write data using an external or third-party language.

As a programmer using data, you should know where that data lives (flat file, table, schema, database, cube, etc.) and how to access it (SELECT, INSERT, UPDATE, etc.). You should understand relational database concepts as well as the behavior of the database — characteristics given by the vendor (for example, auto-commit in Microsoft T-SQL or the need to use COMMIT in order to write data into a table in Oracle PL/SQL).

Some modern languages like Ruby can write SQL code for you in the back-end hidden from the programmer (for example, db/structure.sql when using Active Record, the default method in Ruby), but there’s no assurance that the code is correct regardless of language or implementation. Even if you decide to depend on the language environment to write the SQL code for you, you should be able to edit and improve the code as needed.

You must have full control of your data as well as your code. This is not limited to accessing data, but it also demands that you should control the security of such data in your program. If you can control data security in the database, it’s even better, but this is usually in the hands of the database administrator (DBA). Always think like a black hat hacker and understand how your data can be compromised and stolen. As much as you don’t want hackers to break your program, you don’t want them to break your data.

In other words, learn SQL and know your data.

Introduction to Structured Query Language for Data Analytics (WS24SQL10001) — Day 6

Francisco Olvera

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.

Introduction to Structured Query Language for Data Analytics (WS24SQL10001) — Day 5

Francisco Olvera

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.

Introduction to Structured Query Language for Data Analytics (WS24SQL10001) — Day 3

Francisco Olvera

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.

Introduction to Structured Query Language for Data Analytics (WS24SQL10001) — Day 2

Francisco Olvera

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.