SQL

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

Francisco Olvera

We are getting data from a table.

  SELECT field1,
    field2,
    field3
  FROM table1;

We are getting data from tables.

  SELECT table1.common_field1, -- field/data on both tables
    table1.field2,
    table2.field3,
    table2.field4
  FROM table1 -- left table (main if `LEFT JOIN`)
  INNER|LEFT|RIGHT JOIN table2 -- right table (main if `RIGHT JOIN`)
    ON table1.common_field1 = table2.common_field1; -- relation between tables

Download the class notes for day 2.

Installing SQL Server Express in macOS #4

Francisco Olvera

Free:

  1. Install Docker Desktop on Mac
    https://docs.docker.com/desktop/setup/install/mac-install/
  2. How To Install SQL Server on Mac (M1 | M2 | M3)
    https://www.youtube.com/watch?v=3BFxALltQaM
  3. DBeaver – Microsoft SQL Server
    https://dbeaver.com/docs/dbeaver/Database-driver-Microsoft-SQL-Server/

Paid:

  1. Buying Parallels
    https://www.parallels.com/
  2. Buying a copy of Windows
    https://www.microsoft.com/en-us/software-download/windows11

More information:

  1. Installing SQL Server Express in macOS
  2. Installing SQL Server Express in macOS #2
  3. Installing SQL Server Express in macOS #3

For reference purposes only, not responsible for external resources.

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.