Types and Typos in SQL [1]

Almost anyone who has spent time working with SQL made — or had to fix — this kind of bug:

-- two tables (sketch)
--
users {id, user_name}

trans {id, amt, cur, tx_time, user_id}
-- query with a typo
--
SELECT user_name
     , tx_time
     , amt
     , cur
 FROM users as u
 JOIN trans as t ON u.id = t.id -- < - typo !!
WHERE user_name = 'jack' ;

Try the query, here is the DDL with some data; note the wrong result. Continue reading "Types and Typos in SQL [1]"

Eight Queens in SQL [4]

This is the fourth and final part of the series showing how to solve the “Eight Queens” chess puzzle, using a database design and SQL. The main idea is to design using natural language and concepts of domains, predicates, constraints, sets, set operations, and relations.

Links to all previous and this post’s code.
Part Main Topic Code (pgSQL)
1 Domains, Types part 1
2 Predicates, Constraints, Relations part 2
3 Propositions and Facts part 3
4 The Algorithm and the Solution part 4

Algorithm

Say we place a queen, Q1, on square c5, Continue reading “Eight Queens in SQL [4]”

Eight Queens in SQL [3]

The third article in the series.

Links to all previous and this post’s code.
Part Main Topic Code (pgSQL)
1 Domains, Types part 1
2 Predicates, Constraints, Relations part 2
3 Propositions and Facts part 3

Relation Values

To populate tables simply run the code; it is self explanatory. You may want to keep an eye on the model while reading the code.

Propositions and Facts

A proposition is a declarative sentence, Continue reading “Eight Queens in SQL [3]”

Eight Queens in SQL [2]

This is the second article in the series; the previous one introduced the problem and specified domains.

Links to all previous and this post’s code.
Part Main Topic Code (pgSQL)
1 Domains, Types part 1
2 Predicates, Constraints, Relations part 2

The Essence

The idea is to describe the problem using natural language and concepts of domains, predicates, constraints, relations and keys; here are a few simple rules to keep in mind.

From predicates and constraints to relations and keys
  • A predicate variable — from a specific domain — maps to an attribute of the relation.
  • Internal (to predicate) uniqueness constraints map to keys of the relation.
  • External (to predicate) inclusion constraints map to foreign keys between two relations.
  • A predicate and the matching relation represent — evaluate to — a set of facts about the universe of discourse (the problem).
  • A predicate and the matching relation should not be separated, otherwise information will be lost, for all practical purposes.

Continue reading “Eight Queens in SQL [2]”