Grasp PL/SQL/pgSQL Programming with Free Books

Practical PostgreSQL

By Joshua D. Drake, John C. Worsley (916 pages)

Practical PostgreSQL is a comprehensive book that shows the reader how to compile PostgreSQL from source, create a database, and configure PostgreSQL to accept client-server connections. It also covers the many advanced features, such as transactions, versioning, replication, and referential integrity that enable developers and DBAs to use PostgreSQL for serious business applications.

The thorough introduction to PostgreSQL’s PL/pgSQL programming language explains how you can use this very useful but under-documented feature to develop stored procedures and triggers. The book includes a complete command reference, and database administrators will benefit from the chapters on user management, database maintenance, and backup & recovery. With Practical PostgreSQL, the reader will discover quickly why this open source database is a great open source alternative to proprietary products from Oracle, IBM, and Microsoft.

Chapters cover:

• Introduction and Installation
• Using PostgreSQL – cover understanding SQL, using SQL with PostgreSQL, operators and functions, PostgreSQL clients (psql and PgAccess), and advanced features
• Administrating PostgreSQL – authentication and encryption, database management, user and group management
• Programming with PostgreSQL – PL/pgSQL, JDBC, and LXP
• PostgreSQL Command Reference
• Appendixes: Multibyte Encoding Types, Backend Options for Postgres, Binary COPY Format, and Internal pyql Variables

This book may be distributed only subject to the terms and conditions set forth in the Open Publication License, v1.0 or later.

PostgreSQL 9.0 Reference Manuals

By The PostgreSQL Global Development Group (454 pages)

These manuals are part of the official reference documentation for the PostgreSQL RDBMS (version 9.0.1).

• Volume 1a – the SQL Language – describes the SQL language specification as implemented by PostgreSQL, including syntax, data types, functions and operators, indexes and transactions. The new XML datatypes and full-text index support of PostgreSQL 9 are also fully documented
• Volume 1b – SQL Command Reference – contains the documentation for every PostgreSQL command from ABORT to VALUES. PostgreSQL client and server command-line tools are also documented
• Volume 2 – Programming Guide – covers the PostgreSQL client and server interfaces to PostgreSQL: the C/C++ interface libpq, the embedded SQL/C compiler ECPG and the server-side languages PL/pgSQL, PL/Tcl, PL/Perl and PL/Python. Each chapter includes reference documentation and examples. In particular, the use of triggers, rules and views is described in detail. The PL/pgSQL chapter also includes a guide to porting Oracle PL/SQL functions to PostgreSQL
• Volume 3 – Server Administration Guide – covers the installation, configuration and maintenance of PostgreSQL database servers. Topics covered include backups, security, tuning and upgrade procedures, as well as routine tasks such as creating and deleting databases. Later chapters provide a detailed explanation of advanced PostgreSQL features, including file-based and record-based log-shipping, continuous archiving and point-in-time recovery

The books are freely redistributable, under the same license as PostgreSQL.

“Use The Index, Luke!”

By Markus Winand (HTML)

Use The Index, Luke is the free web-edition of SQL Performance Explained. This resource helps developers to improve database performance. The focus is on SQL; it covers all major SQL databases without getting lost in the details of any one specific product.

Starting with the basics of indexing and the WHERE clause, the book guides developers through all parts of an SQL statement and explains the pitfalls of object-relational mapping (ORM) tools like Hibernate.

Topics covered include:

• Anatomy of an Index – the fundamental structure of an index
• The Where Clause – explains all aspects of the where clause, from very simple single column lookups to complex clauses for ranges and special cases such as LIKE
• Performance and Scalability – looks at performance and scalability
• The Join Operation – an explanation of how to use indexes to perform a fast table join
• Clustering Data – the difference between selecting a single column and all columns
• Sorting and Grouping – even order by and group by can use indexes
• Partial Results – explains how to benefit from a “pipelined” execution if you don’t need the full result set
• Insert, Delete and Update
• Appendices: Execution Plans, Myth Directory, and Example Schema

“Use The Index, Luke!” is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.

Structured Query Language (SQL): a Practical Introduction

By Akeel I Din (179 pages)

Chapters cover:

• Introduction
• A Rough Guide to SQL – presents an overview of the SQL language. The major commands are described from a functional point of view
• Creating and Maintaining Tables – starts by considering the DDL as defined by the ANSI/ISO standard. The later sections of this chapter describe how to create, alter and delete SQL tables
• Querying SQL Tables – the method of interrogating an SQL database
• Adding and Updating Data –
• Data Integrity – looks at the concepts used by SQL to restrict the information that can be added to the database
• Views – introduce the reader to another database object; the view
• Database Security – looks at the security features that are built into SQL itself and also addresses some of the wider aspects of database security
• Transaction Processing
• The Database System Catalog – describes the structure and content of a typical system catalog by looking at the system tables of some popular commercial
  SQL DBMSs
• Embedding SQL in a Host Language – looks at the other method of using SQL, programmatic SQL

The book is released under a free to use non-commercial license.

Database design with UML and SQL, 4th edition

By Alvaro Monge (540 pages)

Database Design with UML and SQL covers basic UML & SQL, UML design, and SQL technique.

The book is licensed under a Creative Commons Attribution-NonCommercial-NoDerivatives 4.0 International License.

A Primer on SQL

By Rahul Batra (HTML)

As the title suggests, this book offers a gentle introduction to the basics of SQL.

The book is not a reference work.

This guide is open source, published under the Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported (CC BY-NC-ND 3.0) license.