Honeymoon

Feelings cafe, last day
Diablo drink Kat
Segways
Pug named googly
Art
Monument with no hands or junk
Insectarium security
Movie
Breakfast at jazz cafe, sent it for bloody Mary mix

Carmo dinner
Hotel bar ahoy fuckers

Advertisements

Data Types (an aside)

exact numeric (INT, NUMERIC), approximate numeric (FLOAT, REAL), character strings (CHAR, VARCHAR), unicode character strings (NCHAR, NVARCHAR), binary strings (BINARY, VARBINARY), date and time (DATE, TIME, DATETIME2, SMALLDATETIME, DATETIME, DATETIMEOFFSET).

Chapter 2 – Lesson 1 – Using the FROM and SELECT Clauses

Principle clauses that appear in almost every query that retrieves data.

The FROM clause

First clause to be logically evaluated.

Has two main roles:

  • Indicate tables to query
  • Apply table operators like joins

Refer to table as schemaname.tablename – in HR.Employees, HR is the schema and Employees is the table.

Schema name can be omitted in which case SQL uses an implicit schema name resolution process, which has a minor cost. If not stated, you run the risk of using the wrong schema/table.

Can use aliases, eg HR.Employees E or HR.Employees AS E, where E is the alias for HR.Employees. Typically these are one letter.

Once an alias has been applied to a table, the original name is no longer valid as a reference and the alias must be used at all times.

The SELECT clause

Has two main roles:

  • Evaluates expressions that define the attributes in the query’s result, assigning them with aliases if needed.
  • Using DISTINCT clause, eliminates duplicate rows in the result if needed

SELECT empid, firstname, lastname
FROM HR.Employees;

The FROM clause indicates that the HR.Employees table is the input table of the query.
The SELECT clause then projects only three of the attributes from the input as the returned
attributes in the result of the query.

Can use * to indicate all attributes are to be returned, but this is considered bad practice because for example:

  1. Normally you only need a subset, so using * and returning all is just laziness.
  2. Sends more data over network than needed, which has cost.
  3. If table definition changes over time, your explicit attributes will be more accurate than just getting everything using *.

Assigning aliases

  • AS –  <expression> AS <alias> eg empid AS employeeID
  • blank – <expression> <alias> eg empid employeeID
  • = – <expression> = <alias> eg empid = employeeID

Using AS is the standard/preferred form as it’s readable making spotting bugs easier.

Reasons to use aliases:

  • Renaming when you need the result attribute to be named differently to the source attribute
  • Assigning a name to an attribute which is the result of an expression and therefore would otherwise be undefined.

Using DISTINCT

T-SQL will not attempt to remove duplicates from a result unless explicitly instructed to do so. A result with duplicates is considered nonrelational as relations, as sets, are not supposed to have duplicates.

If duplicates are possible in a result, use DISTINCT to eliminate them and make the result relational.

SELECT DISTINCT country, region, city
FROM HR.Employees;

Delimiting Identifiers

Can use single quotes, double quotes or brackets

  1. ‘Table Of Contents’
  2. “Table Of Contents”
  3. [Table Of Contents]

With regular identifiers, delimiting is optional.

A regular identifiers first character can be

  • in range A through Z (lower or uppercase)
  • underscore (_)
  • at sign (@)
  • number sign (#)

Subsequent characters can include letters, decimal numbers, at sign, dollar sign ($) number sign or underscore. Can not be a reserved keyword or have spaces, and must not include supplementary characters.

Where the identifier doesn’t comply with these rules, it must be delimited.

To be learned by heart


Keyed In order of main query clauses:

SELECT – FROM – WHERE – GROUP BY – HAVING – ORDER BY

Logical query processing order of main query clauses

FROM – WHERE – GROUP BY – HAVING – SELECT – ODER BY


A regular identifiers first character can be

  • in range A through Z (lower or uppercase)
  • underscore (_)
  • at sign (@)
  • number sign (#)

Subsequent characters can include letters, decimal numbers, at sign, dollar sign ($) number sign or underscore. Can not be a reserved keyword or have spaces, and must not include supplementary characters.

Chapter 1 – Lesson 2 – Understanding Logical Query Processing

SQL is a declarative language rather than an imperitive language – that is it says what it wants and where from, and leaves it up to the database engine to decide how it does so.

Declarative language: Describes what the program should accomplish but not how it accomplishes it.

Keyed In order of main query clauses:

SELECT – FROM – WHERE – GROUP BY – HAVING – ORDER BY

Logical query processing order of main query clauses

FROM – WHERE – GROUP BY – HAVING – SELECT – ODER BY

When processing the query, the engine will create a virtual table populated with the results of each clause, and operate the following clause on this table. Taking the following example:

  • SELECT country, YEAR(hiredate) AS yearhired, COUNT(*) AS numemployees
    FROM HR.Employees
    WHERE hiredate >= ‘20030101’
    GROUP BY country, YEAR(hiredate)
    HAVING COUNT(*) > 1
    ORDER BY country , yearhired DESC;

This query is issued against the HR.Employees table. It filters only employees that were
hired in or after the year 2003. It groups the remaining employees by country and the hire
year. It keeps only groups with more than one employee. For each qualifying group, the
query returns the hire year and count of employees, sorted by country and hire year, in descending order.

There following is a brief outline of what happens with each operator in logical query processing order:

1: FROM – Here the table you want to pull your data from (including any joins) is specified. The output of this phase is a table of all the rows from the input table.

2: WHERE – this phase filters the results of the FROM table based on the predicate of there WHERE clause. Only rows for which the predicate evaluates to TRUE are returned. NOTE: You cannot refer to an alias defined in the SELECT clause at this point in the query as SELECT has not yet been processed so the engine is not aware of the alias at this point. The output of this phase is a table of all the rows from the FROM table which evaluate to TRUE (FALSE and unknown are not returned)

3: GROUP BY – this phase defines a group for each distinct combination of values in the grouped elements from the input table, and then associates each input row to it’s respective group.

4: HAVING – this phase also filters data based on a predicate, but is evaluated after data has been grouped, hence it filters groups as a whole. Only results that evaluate TRUE are returned.

5: SELECT – this phase consists of two steps – evaluating the the expressions in the SELECT clause/producing the result attributes (eg applying aliases) and in the second step removing duplicates when the DISTINCT clause has been included.

6: ORDER BY – this phase is responsible for returning the result in a specific presentation order as specified in the ORDER BY list.