repro

date

X

5-12

The Basics

Quotation Marks

UPPERCASE

Uppercase indicates SQL language or commands

lowercase

Lowercase indicates user defined items such as tables and columns specific to BaBase.

SELECT * 
  FROM biograph 
  WHERE matgrp=1.1
        AND sex=’M’
        AND birth >= ‘2005-01-01’;

This line was added in GUI mode. And it was formatted in GUI mode.

The next paragraph was added in text mode.

SQL (Structured Query Language) is the standard programming language for creating and retrieving information that is stored in a relational database. The user interface or front-end (what you see online) is called phpPgAdmin. The following pages are meant as a basic introduction to SQL, phpPgAdmin, and query writing. Please see BaBase documentation for specifics about the database. There are many resources online for more detailed information about SQL. There are links to SQL tutorials and documentation through the Babase Wiki

Retrieving Data from a Table

• The SELECT statement forms the basis of every question you pose to the database. • When you create and execute a SELECT statement you are ‘querying’ the database. • A SELECT statement is composed of several distinct keywords known as clauses. You define a SELECT statement using various configurations of the clauses to retrieve the information you require. Some clauses are required while others are optional. The main clauses in a SELECT statement are INTO, FROM, WHERE, GROUP BY, and ORDER BY. • You may need multiple statements or queries to retrieve the desired information. • Every SELECT statement must end in a semi colon, ; Retrieving columns with SELECT and FROM

In its simplest form the SELECT statement retrieves columns FROM a table. The SELECT and FROM clauses are always required if you retrieve columns from tables. All other clauses are optional.

• To retrieve one column from a table

SELECT name FROM biograph;

This query will return every row in the name column of the biograph table.

• To retrieve multiple columns from a table

SELECT name, birth FROM biograph;

This statement will return every name and birthdate in the biograph table.

• To retrieve all columns from a table without writing them all out you can use an asterisk “*”.

SELECT * FROM biograph;

This will return all of the rows and columns contained in the biograph table.

Eliminating duplicate rows with DISTINCT

Although DISTINCT is an aggregate function (discussed below) it is useful to introduce this one earlier on.

• Columns often contain duplicate values, and it is common to want a result that lists each duplicate only once.

SELECT DISTINCT(sname) FROM members;

The members table has multiple entries for most individuals. In this case the DISTINCT command returns each sname only once. Filtering rows with WHERE

TabbyTest (last edited 2012-02-07 19:17:17 by localhost)

Wiki content based upon work supported by the National Science Foundation under Grant Nos. 0323553 and 0323596. Any opinions, findings, conclusions or recommendations expressed in this material are those of the wiki contributor(s) and do not necessarily reflect the views of the National Science Foundation.