guh.me - gustavo's personal blog

Book review – Discovering SQL: A Hands-On Guide for Beginners

⸻

I’ve been hidding behind ORMs for a long time, and I finally decided (honestly, I needed ;) to learn basic SQL from a “formal source”. After searching and sorting lots of books, I chose Discovering SQL: A Hands-On Guide for Beginners, by Alex Kriegel.

The book itself is very short, concise and it doesn’t try to teach you relational theory, only pure SQL (the almost standardized SQL). Besides basic SQL syntax, the book explains the nuances between different RDBMs (MySQL, PostgreSQL, SQL Server, IBM DB2, Oracle 11g, and others), with a little more focus on SQL Server (I guess the author is more familiarized with it). Furthermore, Alex has a nice conversational style, and that makes the book much more enjoyable.

I really liked this book, and it’s certainly a great resource to beginners or to whom wants to remember about SQL syntax. By the way, I’ve already recommended it to a disciple of mine who had never heard about SQL, and he is doing great. ;)

Book on Amazon: Discovering SQL: A Hands-On Guide for Beginners (Wrox Programmer to Programmer)

My personal notes

Breaking and entering structured information

Components of SQL

Refactoring database tables

SQL operators: agents of change

Designing a relational database

CREATE TABLE books (
  id INTEGER NOT NULL,
  title VARCHAR(50) NOT NULL,
  CONSTRAINT bk_pk PRIMARY KEY (id));

ALTER TABLE books
  ADD CONSTRAINT bk_pk PRIMARY KEY (id);

CREATE TABLE authors (
  id INTEGER NOT NULL,
  name VARCHAR(20) NOT NULL,
  CONSTRAINT au_pk PRIMARY KEY (id)
);

CREATE TABLE books (
  id INTEGER NOT NULL,
  title VARCHAR(50) NOT NULL,
  author_id INTEGER NOT NULL,
  CONSTRAINT bk_pk PRIMARY KEY (id),
  CONSTRAINT fk_authors FOREIGN KEY (author_id) REFERENCES authors(id));

ALTER TABLE books
  ADD CONSTRAINT fk_authors FOREIGN KEY (author_id) REFERENCES authors(id);

Overcoming the limitations of SQL

SELECT CAST('10/11/2012' AS DATETIME);
SELECT CAST(<EXPRESSION>  AS <TYPE>);

SELECT title
FROM books
WHERE type = (
  CASE publication_year
    WHEN 2000 THEN 'old'
    WHEN 2001 THEN 'new'
    ELSE NULL
  end
)

Grouping and aggregation

SELECT COUNT(DISTINCT publisher) as pub_count
FROM books;

SELECT
  publisher,
  SUM(price) AS total
FROM books
GROUP BY publisher;

SELECT
  publisher,
  AVG(price) as total
FROM books
GROUP BY publisher
  HAVING AVG(price) > 20;

A query within a query

SELECT
  title,
  publisher
FROM
  books
WHERE id IN (
  SELECT
    fk_bk_loc
  FROM
    location
  WHERE
    shelf = 5
);

SELECT
  title,
  (SELECT MAX(price) FROM books) AS max_price
FROM
  books

Combining data sets

SELECT *
FROM books
INNER JOIN location
  ON location.id = books.location_id;

SELECT
  books.title,
  authors.last_name
FROM books
INNER JOIN books_authors
  ON books.id = books_authors.book_id
INNER JOIN authors
  ON authors.id = books_authors.author_id;

SELECT
  title,
  shelf
FROM books
LEFT OUTER JOIN location
  ON location.fk_bok_loc = books.id;

SELECT
  shelf,
  book_title
FROM location
RIGHT OUTER JOIN books
  ON location.fk_book_loc = books.id;

CREATE VIEW new_view
AS
  <SELECT query>;

CREATE VIEW comedies
AS
  SELECT *
  FROM films
  WHERE kind = 'Comedy';

SELECT * FROM new_view;

DROP VIEW new_view;

What else is there, and why?

CREATE INDEX <name> ON <table>(<columns>);

DROP INDEX <name>;

Multiuser environment