neulatuntimittari.2.05 SQL

26.10.11: Continuing sqlbolt tutorial

Lesson 6 is where real-world things start

https://sqlbolt.com/lesson/select_queries_with_joins

Database normalization
Multi-table queries with JOINs
Outer joins

https://sqlbolt.com/lesson/select_queries_with_outer_joins

End for now with lesson 10

https://sqlbolt.com/lesson/select_queries_with_aggregates

26.10.12: sqlbolt continue 2

Starting with lesson 10

https://sqlbolt.com/lesson/select_queries_with_aggregates

How annoying that there is no examples of usage for parts of the SQL query before the exercises.

Ie. This doesn’t help with learning:

SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

Each query begins with finding the data that we need in a database, and then filtering that data down into something that can be processed and understood as quickly as possible. Because each part of the query is executed sequentially, it’s important to understand the order of execution so that you know what results are accessible where.

I feel that this is not intuitive. For example: Here the aggregate function at the top defines the end behaviour:

SELECT role, SUM(years_employed)
FROM employees
GROUP BY role
HAVING role = "Engineer";

The previous query result is “The total number of years employed by all Engineers”. Why is the SUM at the top, when it should be the last operation in functional programming.

I might get it now. The SELECT statement defines the column that are shown. The SUM column defines behaviour for that column. It shows the sum for years employed relevant for the line, as in the other column. GROUP BY further defines that behaviour by grouping the data into distinct roles, affecting the sum to be not of all years employed, but years employed by role title. HAVING filters that result.

^ from section 11 of sqlbolt

26.10.13: Section 12

Here is a listing of the order of execution from section 12: https://sqlbolt.com/lesson/select_queries_order_of_execution#:~:text=Query%20order%20of%20execution

Actually. I was right! The select statement aggregate functions are executed at the end, not sequentially.

5. SELECT

Any expressions in the SELECT part of the query are finally computed.

Section 13: Inserting new data

Schema= sql for declating variable types

[!NOTE] Insert When inserting data into a database, we need to use an INSERT statement, which declares which table to write into, the columns of data that we are filling, and one or more rows of data to insert. In general, each row of data you insert should contain values for every corresponding column in the table. You can insert multiple rows at a time by just listing them sequentially.

16: creating tables, datatypes, table constraints

https://sqlbolt.com/lesson/creating_tables

18: done

SQLBolt have also “intermediate topics”, but I don’t think I will be going into them. This was good enough primer on how SQL works to figure out the rest on my own, I think. Regardless, here are the rest of the topics: topics

Core SQL

Subqueries

Set operations

Coming soon: Foreign Keys, Dates and Times, Views, and more.

The site was last updated in 2024, so I don’t think they are coming back to the 3rd topic. Anyways, here are some paths to seek out from reddit: reddit post

neulatuntimittari.2.04 PostgreSQL

neulatuntimittari.2.03 data format

26.10.11: Continuing sqlbolt tutorial

Lesson 6 is where real-world things start

Outer joins